JDK + JDBC + MySQL instances and precautions

By qx. zhongHangzhou 29 Jun 2014
Development EnvironmentOS: Win8.1 x64JDK: 1.8 SEDB: MySQL 5.5 Lib: mysql-connector-java.jar


1. Special correspondence between MySQL database data types and JDK
The following table lists only a few special value type comparison, the rest of the need can refer to the MySQL official site value type description (http://dev.mysql.com/doc/refman/5.1/zh/index.html) and JDK related information.

MySQL	JDK
Tinyint (1)	Boolean
Int unsigined	Long
Datetime	Java. SQL. Timestamp
Varchar	String

In MySQL, TINYINT (1) is BOOL, And the synonym of BOOLEAN is-128 to 127. The unsigned range is 0 to 255. BOOLEAN is not true, and zero is false. The int unsigined value range is beyond the maximum value range of JDK's int type (All Integer types in JDK are signed). Therefore, you need to load the value with long.

2. Use the JDK reflection mechanism to automatically load JDBC ResultSet to Bean class
First, make sure that the column names in MySQL correspond to the attribute names of the JavaBean class one by one. Then, you can use the reflection mechanism to call setter to assign values to the Bean. The key code is as follows:

/** *  Using reflection to storage the result from database into Bean class. *   */public static List<Object> resultSetToList(ResultSet rs, Class<?> cls) {                Method[] methods = cls.getDeclaredMethods();          int methodLength = methods.length;                int index;        Map<String, Integer> map = new HashMap<String, Integer>();        // record all methods name in a HashMap, for quickly locate.        for (index = 0; index < methodLength; index++) {        map.put(methods[index].getName().toLowerCase(), index);        }                   ResultSetMetaData meta = null;        Object obj = null;          List<Object> list = new ArrayList<Object>();                 try {        meta = rs.getMetaData();    int colCount = meta.getColumnCount();while (rs.next()) {      obj = cls.newInstance();         for (int i = 1; i <= colCount; i++) {          String colName = meta.getColumnName(i);          String setMethodName = "set" + colName;    //        System.out.println(setMethodName);        int j = map.get(setMethodName.toLowerCase()); //get index of method array        setMethodName = methods[j].getName();                Object value = rs.getObject(colName);                   if(value == null){                      continue;                  }                                 try {                        Method setMethod = obj.getClass().getMethod(setMethodName, value.getClass());                       setMethod.invoke(obj, value);                } catch (Exception e) {                     System.out.println(setMethodName + " exception");                   e.printStackTrace();                  }                      }      list.add(obj);  }} catch (InstantiationException | IllegalAccessException | SQLException e) {e.printStackTrace();}                  return list;        }  

3. Other Instructions
Releasing a database connection is the most time-consuming and system overhead in JDBC. Therefore, we recommend that you use a database connection pool for processing. Set the minimum number of connections and maximum number of connections in a pool. The minimum number of connections is the default initial connection established during connection pool startup. Too many connections may affect the startup time of the code, and insufficient connections may occur when the connection pool is established, when the connection pool detects that the number of required connections is greater than the minimum number of connections, the connection is automatically added ). Therefore, the minimum number of connections in the connection pool needs to be considered based on the actual situation of the project.

4. Example Project4.1 Database Table Design 1) Design a cake table. The details are as follows:

mysql> describe cake;+--------------+---------------------+------+-----+---------+-------+| Field        | Type                | Null | Key | Default | Extra |+--------------+---------------------+------+-----+---------+-------+| name         | varchar(20)         | NO   | PRI | NULL    |       || serialNumber | int(10) unsigned    | YES  |     | NULL    |       || buildDate    | datetime            | YES  |     | NULL    |       || isSweet      | tinyint(1) unsigned | YES  |     | NULL    |       |+--------------+---------------------+------+-----+---------+-------+

2) initialized data:

mysql> select * from cake;+--------+--------------+---------------------+---------+| name   | serialNumber | buildDate           | isSweet |+--------+--------------+---------------------+---------+| Danisa |      2021344 | 2013-11-19 10:20:00 |       1 || Orion  |      2004720 | 2014-06-29 22:00:00 |       0 |+--------+--------------+---------------------+---------+

4.2 Bean class Design 1) attributes of the Cake class:

 private String name; private long serialNumber; private Timestamp buildDate; private boolean isSweet;

2) design details of Setter for special value type variables in Bean: java is required for setter parameters of JDK Integer type. for example, long corresponds to java. lang. long, int corresponds to java. lang. integer. The motive is that the Bean attributes can conform to the Class parameter of resultSetToList described in 2 <?> Cls. In this example, the MySql int unsigned retrieved by the ResultSet will be automatically converted to the Long type in the memory, so the setter needs to use Long;

 public void setSerialNumber(Long /*long*/ serialNumber) { //Type was java.lang.Long but not 'long'.      this. serialNumber = serialNumber; }

The tinyint (1) retrieved from MySql is saved as Boolean, so the setter parameter of the Bean class is of the Boolean type. In addition, the setter name automatically generated by the attribute isSweet in eclipse is setSweet. When the reflection value is assigned, the method cannot be found, because the method for the reflection value assignment search is 'setissweet ', so we need to rename the automatically generated setter.

 public void /*setSweet*/setIsSweet( /*boolean*/Boolean isSweet) { // Type was java.lang.Boolean but not boolean      this. isSweet = isSweet; }