review original JDBC operation for MySQL

• load driver
• get connection
• get statement and execute sql
• get resultSet and handle data
• close collections to database

---

Sample code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class OriginalConnection {
	public static void main(String[] args) throws SQLException, ClassNotFoundException {

		String url = "jdbc:mysql://localhost:3306/employee";
		String user = "root";
		String password = "alec";
		Connection conn = null;
		// avoid sql injection
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			// load driver for MySQL
			// not recommended because it depends on database type
			// DriverManager.registerDriver(new com.mysql.jdbc.Driver());
			// below coding is strongly recommended, it depends on a string, not one
			// database, good for extension and maintenance
			// reflection, like framework works
			Class.forName("com.mysql.jdbc.Driver");
			// ----------------------------------------------------------------
			// get connection
			// import jdbc API for conn, not a specified database API, it is good for
			// maintenace and switching database
			// interface-oriented programming
			conn = DriverManager.getConnection(url, user, password);
			// ----------------------------------------------------------------
			// get statement
			String sql = "select id, name, password, birthday, email from user where name=?";
			// pre-compile sql statement, can speed up execution and relieve database server
			st = conn.prepareStatement(sql);
			// 1 indicates the first "?"(placeholder)
			// setString will check the validity of input, avoid sql injection
			st.setString(1, "zhang"); 

			// execute sql and get resultset
			// note: do not set sql as a para, just run
			// * rs.st.executeQuery(sql) *
			rs = st.executeQuery();

			// handle data
			while (rs.next()) {
				System.out.println(rs.getObject("id"));
				System.out.println(rs.getObject("name"));
				System.out.println(rs.getObject("password"));
				System.out.println(rs.getObject("birthday"));
				System.out.println(rs.getObject("email"));
			}

		} finally {
			// close, reverse order compared with creating process
			// To ensure the closing operation can be executed, these statements should be
			// in clause "finally"
			// To avoid closing operation occurs exception, add if and try/catch.
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e2) {
					// catch exception, JVM go on below code
					e2.printStackTrace();
				}
				rs = null;
			}
			if (st != null) {
				try {
					st.close();
				} catch (Exception e3) {
					// catch exception, JVM go on below code
					e3.printStackTrace();
				}
				st = null;
			}

			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e4) {
					// catch exception, JVM go on below code
					e4.printStackTrace();
				}
				conn = null;
			}
		}
	}
}

---

Some tips:

• import JDBC API, not MySQL API, interface-oriented programming, for good maintanence.
• Recommend using properties files to save driver, url, username and password, then we can easily switch different database without changing code.
• using preparedStatement replace statement, 1) avoid sql injection 2) pre-compile for efficiency
• for date type: date of Util to date of sql, new java.sql.Date("object".getTime());