public
Created

To Generate the count of special values with category from one field in one table of mysql

  • Download Gist
FashionGenerate.java
Java
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
public class FashionGenerate {
private static String url = "jdbc:mysql://localhost:3306/Database";
private static String user = "UserName";
private static String password = "Password";
private static Connection conn;
private static Connection update_conn;
private static Connection insert_conn;
private static Connection select_conn;
private static PreparedStatement ps;
private static PreparedStatement update_ps;
private static PreparedStatement insert_ps;
private static PreparedStatement select_ps;
private static ResultSet rs;
private static ResultSet select_rs;
private static String sql;
private static String update_sql;
private static String insert_sql;
private static String select_sql;
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
throw new ExceptionInInitializerError(e);
}
}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, user, password);
}
public static void free(ResultSet rs, Statement st, Connection conn) {
try {
if (rs != null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (st != null)
st.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (conn != null)
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
private static void insert(String Prop, String Kind) throws SQLException {
insert_conn = getConnection();
insert_sql = "insert into prop_count set Prop = ? , Kind = ? , Num = 1";
insert_ps = conn.prepareStatement(insert_sql);
insert_ps.setString(1, Prop);
insert_ps.setString(2, Kind);
insert_ps.execute();
free(null, insert_ps, insert_conn);
}
private static void update(String Prop) throws SQLException {
update_conn = getConnection();
update_sql = "update prop_count set num = num+1 where prop = ?";
update_ps = update_conn.prepareStatement(update_sql);
update_ps.setString(1, Prop);
update_ps.executeUpdate();
free(null, update_ps, update_conn);
}
private static void generate() throws SQLException {
conn = getConnection();
sql = "select fashion from item_detail;";
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()) {
String fashion = rs.getString("fashion");
System.err.println(fashion);
if((fashion==null) || fashion.equals(""))
continue;
fashion = fashion.trim();
select_conn = getConnection();
select_sql = "select prop from prop_count where prop = ? and kind = ?;";
select_ps = conn.prepareStatement(select_sql);
select_ps.setString(1, fashion);
select_ps.setString(2, "fashion");
select_rs = select_ps.executeQuery();
if(select_rs.next()) {
System.err.println("try updating");
update(fashion);
}
else {
System.err.println("try inserting");
insert(fashion, "fashion");
}
free(select_rs, select_ps, select_conn);
}
free(rs, ps, conn);
}
public static void main(String[] args) throws SQLException {
generate();
}
}

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.