Created

Embed URL

HTTPS clone URL

SSH clone URL

You can clone with HTTPS or SSH.

Download Gist

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

View FashionGenerate.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();
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.