Introduction to DBUtils result set Processor and dbutils result Processor

Introduction to DBUtils result set Processor and dbutils result Processor

Common-dbutils.jar is an open-source tool library provided by the Apache organization for simple encapsulation of JDBC, which can simplify the development of JDBC applications without affecting program performance.

1. QueryRunner class

① Update method:

• Int update (String SQL, Object... params) --> execute the add, delete, and modify statement.
• Int update (Connection con, String SQL, Object... params) --> the caller must provide Connection. This means that Connection is not managed in this method. Supports transactions.

② Query method:

• T query (String SQL, ResultSetHandler rsh, Object... params) --> executable query
  • It will first obtain the ResultSet, and then call the rsh handle () to convert rs to the required type.
• T query (Connection con, String SQL, ResultSetHandler rsh, Object... params); supports transactions.

2. ResultSetHandler interface:

• BeanHandler (single row) --> the constructor requires a Class type parameter to convert a row of results to a specified type of javabean object.
• BeanListHandler (multi-row) --> the constructor also requires a Class-type parameter to convert a row of result set into a javabean, So multiple rows are converted into a List object, a bunch of javabean
• MapHandler (single row) --> converts a result set of a row to a Map object
  • One record:
  • Sid sname age gender
  • 1001 zs 99 male
  • A Map:
  • {Sid: 1001, sname: zs, age: 99, gender: male}
• MapListHandler (multiple rows) --> converts a row of records into a Map. Multiple rows are multiple maps, that is, List <Map>.
• ScalarHandler (single-row Single-Column) --> it is usually used with the "select count (*) FROM t_stu;" statement. The result set is a single-row single-column and it returns an Object.

3. Example:

1 import cn. itcast. jdbc. jdbcUtils; 2 import org. apache. commons. dbutils. queryRunner; 3 import org. apache. commons. dbutils. handlers. *; 4 import org. junit. test; 5 import java. SQL. SQLException; 6 import java. util. list; 7 import java. util. map; 8 9 public class Demo3 {10 @ Test11 public void fun1 () throws SQLException {12 QueryRunner qr = new QueryRunner (JdbcUtils. getDataSource (); 13 String SQL = "INSERT I NTO t_stu VALUES (?,?,?,?) "; 14 Object [] params = {1002," lisi ", 99," female "}; 15 qr. update (SQL, params); 16} 17 @ Test18 public void fun2 () throws SQLException {19 // create QueryRunner and provide the database connection pool object 20 QueryRunner qr = new QueryRunner (JdbcUtils. getDataSource (); 21 // The SQL template 22 String SQL = "SELECT * FROM t_stu WHERE sid =? "; 23 // specify the parameter 24 Object [] params = {1002}; 25 // run the query () method. The set processor is required, that is, the Implementation Class Object of ResultSetHandler 26 // we need to give BeanHandler, which implements ResultSetHandler27 // it needs a type, then, it encapsulates the data in rs into a javabean object of the specified type, and then returns the javabean object 28 Stu stu = qr. query (SQL, new BeanHandler <Stu> (Stu. class), params); 29 System. out. println (stu); 30} 31 // BeanListHandler application, which is a multiline processor 32 // each row object one Stu object 33 @ Test34 public void fun3 () throws Exception {35 QueryRunner qr = New QueryRunner (JdbcUtils. getDataSource (); 36 String SQL = "SELECT * FROM t_stu"; 37 List <Stu> stuList = qr. query (SQL, new BeanListHandler <Stu> (Stu. class); 38 System. out. println (stuList); 39} 40 41 // MapHandler application, which is a single-line processor that converts a row into a Map object 42 @ Test43 public void fun4 () throws Exception {44 QueryRunner qr = new QueryRunner (JdbcUtils. getDataSource (); 45 String SQL = "SELECT * FROM t_stu WHERE sid =? "; 46 Object [] params = {1001}; 47 Map map = qr. query (SQL, new MapHandler (), params); 48 System. out. println (map); 49} 50 51 // MapListHandler, a multiline processor that converts each line into a Map, that is, List <Map> 52 @ Test53 public void fun5 () throws Exception {54 QueryRunner qr = new QueryRunner (JdbcUtils. getDataSource (); 55 String SQL = "SELECT * FROM t_stu"; 56 List <Map <String, Object> mapList = qr. query (SQL, new MapListHandler (); 57 System. out. Println (mapList); 58} @ Test59 // ScalarHandler, which is most suitable for single row and single column. 60 public void fun6 () throws Exception {61 QueryRunner qr = new QueryRunner (JdbcUtils. getDataSource (); 62 String SQL = "SELECT COUNT (*) FROM t_stu"; 63 Number cnt = (Number) qr. query (SQL, new ScalarHandler (); 64 long c = cnt. longValue (); 65 System. out. println (c); 66} 67}

1 import com. mchange. v2.c3p0. comboPooledDataSource; 2 import java. SQL. connection; 3 import java. SQL. SQLException; 4 5 public class JdbcUtils {6 // default configuration file, requires that you have to give the 7 private static combooleddatasource from the c3p0-config.xml dataSource = new combooleddatasource (); 8 // use the Connection pool to return a Connection object 9 public static Connection getConnection () throws SQLException {10 return dataSource. getConnection (); 11} 12 // return connection pool object 13 public static ComboPooledDataSource getDataSource () {14 return dataSource; 15} 16}