package mysql;

import java.sql.*;

public class JDBCUtils {
	
	private Connection con;
	private PreparedStatement ps;
	
	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public JDBCUtils() {
		con = getConnection();
	}
	
	public void close() {
		try {
			if (con != null) {
				con.close();
				con = null;
			}
		} catch(SQLException e) {
			e.printStackTrace();
		}
	}
	
	private Connection getConnection(
			String ip, String port, String db, String user, String password) {
		Connection con = null;
		String url = "jdbc:mysql://" + ip.concat(":") + port.concat("/") + db;
		try {
			con = DriverManager.getConnection(url, user, password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return con;
	}
	
	private Connection getConnection() {
		Connection con = getConnection(
				"211.149.156.144", "3306", "test", "remote", "remote");
		return con;
	}
	
	public ResultSet executeQuery(String sql) {
		try {
			ps = con.prepareStatement(sql);
			return ps.executeQuery();
		} catch(Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public void closeQuery(ResultSet ret) {
		try {
			if (ret != null) {
				ret.close();
				ret = null;
			}
			if (ps != null) {
				ps.close();
				ps = null;
			}
		} catch(SQLException e) {
			e.printStackTrace();
		}
	}
	
	public void displayResultSet(ResultSet ret) {
		try {
			ResultSetMetaData meta = ret.getMetaData();
			int cols = meta.getColumnCount();
			while (ret.next()) {
				for (int idx = 1; idx <= cols; ++idx) {
					System.out.print(meta.getColumnName(idx) + "=");
					System.out.print(ret.getObject(idx));
					if (idx != cols)
						System.out.print("\t");
				}
				System.out.println();
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
	}
	
	public void querySample(String sql) {
		ResultSet ret = executeQuery(sql);
		displayResultSet(ret);
		closeQuery(ret);
	}
	
	public int excuteUpdate(String sql) {
		try {
			ps = con.prepareStatement(sql);
			int updateCnt = ps.executeUpdate();
			ps.close();
			return updateCnt;
		} catch(Exception e) {
			e.printStackTrace();
		}
		return 0;
	}
	
	public static void main(String[] args) {
		int updateCnt;
		JDBCUtils jdbc = new JDBCUtils();
		
		jdbc.querySample("show tables;");
		jdbc.querySample("select * from KeyTest;");
		
		updateCnt = jdbc.excuteUpdate("drop table if exists test1;");
		System.out.println("updateCnt is " + updateCnt + ".");
		
		updateCnt = jdbc.excuteUpdate("drop table if exists test2;");
		System.out.println("updateCnt is " + updateCnt + ".");
		
		jdbc.close();
	}
}