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 FashionCount {
	private static String url = "jdbc:mysql://localhost:3306/ItemsInfo2";
	private static String user = "root";
	private static String password = "2027";
	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) throws SQLException {
		insert_conn = getConnection();
		insert_sql = "insert into fashion_count set Fashion = ? , Num = 1";
		insert_ps = conn.prepareStatement(insert_sql);
		insert_ps.setString(1, Prop);
		insert_ps.execute();
		free(null, insert_ps, insert_conn);
	}
	private static void update(String Prop) throws SQLException {
		update_conn = getConnection();
		update_sql = "update fashion_count set num = num+1 where fashion = ?";
		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 fashion from fashion_count where fashion = ?;";
			select_ps = conn.prepareStatement(select_sql);
			select_ps.setString(1, 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);
			}
			free(select_rs, select_ps, select_conn);
		}
		free(rs, ps, conn);
	}
	public static void main(String[] args) throws SQLException {
		generate();
	}
}