Chapter 25 JDBC core technology section 9: implementation of CRUD operation by Apache dbutils

Chapter 9: implementation of CRUD operation with Apache dbutils

9.1 introduction to Apache dbutils

  • Commons dbutils is an open source JDBC tool class library provided by Apache organization. It is a simple encapsulation of JDBC and has very low learning cost. Using dbutils can greatly simplify the workload of JDBC coding without affecting the performance of the program.

  • API introduction:

    • org.apache.commons.dbutils.QueryRunner
    • org.apache.commons.dbutils.ResultSetHandler
    • Tools: org apache. commons. dbutils. DbUtils
  • API package description:

9.2 use of main API s

9.2.1 DbUtils

  • DbUtils: provides tool classes for routine work such as closing connections and loading JDBC drivers. All methods in them are static. The main methods are as follows:
    • public static void close(…) throws java.sql.SQLException: the DbUtils class provides three overloaded shutdown methods. These methods check whether the supplied parameter is NULL, and if not, they close the Connection, Statement and ResultSet.
    • Public static void closequiet (...): this kind of method can not only avoid closing when the Connection, Statement and ResultSet are NULL, but also hide some SQLEeception thrown in the program.
    • public static void commitAndClose(Connection conn)throws SQLException: used to commit the connected transaction and then close the connection
    • Public static void commitandclosequiet (connection conn): used to submit a connection and then close the connection. SQL exceptions are not thrown when closing the connection.
    • public static void rollback(Connection conn)throws SQLException: conn is allowed to be null because a judgment is made inside the method
    • public static void rollbackAndClose(Connection conn)throws SQLException
    • rollbackAndCloseQuietly(Connection)
    • public static boolean loadDriver(java.lang.String driverClassName): this party loads and registers the JDBC driver, and returns true if successful. Using this method, you do not need to catch this exception ClassNotFoundException.

9.2.2 QueryRunner class

  • This class simplifies SQL query. It can be used together with ResultSetHandler to complete most database operations and greatly reduce the amount of coding.

  • The QueryRunner class provides two constructors:

    • Default constructor
    • You need a javax sql. Datasource is used as the constructor of parameters
  • Main methods of QueryRunner class:

    • to update
      • public int update(Connection conn, String sql, Object... params) throws SQLException: used to perform an update (insert, update or delete) operation.
      • ...
    • insert
      • public T insert(Connection conn,String sql,ResultSetHandler rsh, Object... params) throws SQLException: only INSERT statements are supported, where RSH - the handler used to create the result object from the resultset of auto generated keys Return value: An object generated by the handler That is, the automatically generated key value
      • ...
    • Batch processing
      • public int[] batch(Connection conn,String sql,Object[][] params)throws SQLException: INSERT, UPDATE, or DELETE statements
      • public T insertBatch(Connection conn,String sql,ResultSetHandler rsh,Object[][] params)throws SQLException: only INSERT statements are supported
      • ...
    • query
      • public Object query(Connection conn, String sql, ResultSetHandler rsh,Object... params) throws SQLException: executes a query operation. In this query, each element value in the object array is used as the replacement parameter of the query statement. This method handles the creation and closing of PreparedStatement and ResultSet by itself.
      • ...
  • test

// Test add
@Test
public void testInsert() throws Exception {
	QueryRunner runner = new QueryRunner();
	Connection conn = JDBCUtils.getConnection3();
	String sql = "insert into customers(name,email,birth)values(?,?,?)";
	int count = runner.update(conn, sql, "He Chengfei", "he@qq.com", "1992-09-08");
	System.out.println("Added" + count + "Records");
	JDBCUtils.closeResource(conn, null);
}
// Test delete
@Test
public void testDelete() throws Exception {
	QueryRunner runner = new QueryRunner();
	Connection conn = JDBCUtils.getConnection3();
	String sql = "delete from customers where id < ?";
	int count = runner.update(conn, sql,3);
	System.out.println("Deleted" + count + "Records");
	JDBCUtils.closeResource(conn, null);
}

9.2.3 ResultSetHandler interface and implementation class

  • This interface is used to process Java sql. Resultset to convert the data into another form as required.

  • The ResultSetHandler interface provides a separate method: object handle (Java. SQL. Resultset. RS).

  • Main implementation classes of the interface:

    • ArrayHandler: converts the first row of data in the result set into an object array.

    • ArrayListHandler: convert each row of data in the result set into an array and store it in the List.

    • **BeanHandler: * * encapsulate the first row of data in the result set into a corresponding JavaBean instance.

    • **BeanListHandler: * * encapsulate each row of data in the result set into a corresponding JavaBean instance and store it in the List.

    • ColumnListHandler: store the data of a column in the result set into the List.

    • KeyedHandler(name): encapsulate each row of data in the result set into a map, and then save these maps into a map. The key is the specified key.

    • **MapHandler: * * encapsulates the first row of data in the result set into a Map. key is the column name and value is the corresponding value.

    • **MapListHandler: * * encapsulate each row of data in the result set into a Map, and then store it in the List

    • **ScalarHandler: * * query single value object

  • test

/*
 * Test query: query a record
 * 
 * Implementation class using ResultSetHandler: BeanHandler
 */
@Test
public void testQueryInstance() throws Exception{
	QueryRunner runner = new QueryRunner();

	Connection conn = JDBCUtils.getConnection3();
		
	String sql = "select id,name,email,birth from customers where id = ?";
		
	//
	BeanHandler<Customer> handler = new BeanHandler<>(Customer.class);
	Customer customer = runner.query(conn, sql, handler, 23);
	System.out.println(customer);	
	JDBCUtils.closeResource(conn, null);
}
/*
 * Test query: query the set composed of multiple records
 * 
 * Implementation class using ResultSetHandler: BeanListHandler
 */
@Test
public void testQueryList() throws Exception{
	QueryRunner runner = new QueryRunner();

	Connection conn = JDBCUtils.getConnection3();
		
	String sql = "select id,name,email,birth from customers where id < ?";
		
	//
	BeanListHandler<Customer> handler = new BeanListHandler<>(Customer.class);
	List<Customer> list = runner.query(conn, sql, handler, 23);
	list.forEach(System.out::println);
		
	JDBCUtils.closeResource(conn, null);
}
/*
 * Implementation class of custom ResultSetHandler
 */
@Test
public void testQueryInstance1() throws Exception{
	QueryRunner runner = new QueryRunner();

	Connection conn = JDBCUtils.getConnection3();
		
	String sql = "select id,name,email,birth from customers where id = ?";
		
	ResultSetHandler<Customer> handler = new ResultSetHandler<Customer>() {

		@Override
		public Customer handle(ResultSet rs) throws SQLException {
			System.out.println("handle");
//			return new Customer(1,"Tom","tom@126.com",new Date(123323432L));
				
			if(rs.next()){
				int id = rs.getInt("id");
				String name = rs.getString("name");
				String email = rs.getString("email");
				Date birth = rs.getDate("birth");
					
				return new Customer(id, name, email, birth);
			}
			return null;
				
		}
	};
		
	Customer customer = runner.query(conn, sql, handler, 23);
		
	System.out.println(customer);
		
	JDBCUtils.closeResource(conn, null);
}
/*
 * How to query data related to the largest, smallest, average, sum and number of items,
 * Using ScalarHandler
 * 
 */
@Test
public void testQueryValue() throws Exception{
	QueryRunner runner = new QueryRunner();

	Connection conn = JDBCUtils.getConnection3();
		
	//Test 1:
//	String sql = "select count(*) from customers where id < ?";
//	ScalarHandler handler = new ScalarHandler();
//	long count = (long) runner.query(conn, sql, handler, 20);
//	System.out.println(count);
		
	//Test 2:
	String sql = "select max(birth) from customers";
	ScalarHandler handler = new ScalarHandler();
	Date birth = (Date) runner.query(conn, sql, handler);
	System.out.println(birth);
		
	JDBCUtils.closeResource(conn, null);
}
  • summary
@Test
public void testUpdateWithTx() {
		
	Connection conn = null;
	try {
		//1. Get connection(
		//① Handwritten connection: JDBC utils getConnection();
		//② Use database connection pool: C3P0;DBCP;Druid
		//2. Perform a series of CRUD operations on the data table
		//① Use PreparedStatement to realize general addition, deletion, modification and query operations (version 1.0 \ version 2.0)
//version2. Public void update (connection Conn, string SQL, object... Args) {}
//version2. Query public < T > t getInstance (connection Conn, class < T > clazz, string SQL, object... Args) {}
		//② Use the QueryRunner class provided in the jar package provided by dbutils
			
		//Submit data
		conn.commit();
			
	
	} catch (Exception e) {
		e.printStackTrace();
			
			
		try {
			//Undo Data 
			conn.rollback();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
			
	}finally{
		//3. Close the connection and other operations
		//① JDBCUtils.closeResource();
		//② Using the dbutils class provided in the jar package provided by dbutils provides the relevant operation of closing
			
	}
}

Tags: JavaSE

Posted by trassalg on Sat, 07 May 2022 16:10:34 +0300