Summary

The main purpose of this article is to help users get started using BDUtils quickly, so there will not be many articles on the principles and related concepts, and only introduce some commonly used classes and methods., start.

premise

Learn about the basic operations of JDBC.

Introduction to BDUtils

• commons-dbutils is an open source JDBC tool class library provided by the Apache organization
• It is a simple encapsulation of JDBC
• Using dbutils greatly simplifies the workload of jdbc encoding without affecting program performance

Common Classes

DbUtils class

• Summary
  Provides tool classes for general work, such as closing connections and loading JDBC drivers, where all methods are static.

• Main methods

  １. public static void close(...) throws java.sql.SQLException
  The DBUtils class provides three overloaded closing methods.These methods check to see if the parameters provided are NULL, and if not, they turn off Connection, Statement, and ResultSet.
  ２. public static void closeQuietly(...)
  This type of method not only avoids closing when Connection, Statement, and ResultSet are NULL, but also hides some SQLEeception s thrown in the program.
  ３. public static void commitAndCloseQuietly(Connection conn)
  Used to commit a connection and then close it without throwing a SQL exception when closing the connection.
  ４. public static boolean loadDriver(java.lang.String driverClassName)
  This party loads and registers the JDBC driver and returns true if successful.With this method, you do not need to catch the exception ClassNotFoundException.

QueryRunner class

• Summary
  This class simplifies SQL queries and, when used in combination with ResultSetHandler, accomplishes most database operations, greatly reducing encoding.
• Construction method

• Main methods
  
  １. public Object query(Connection conn, String sql, ResultSetHandler rsh, Object... params) throws SQLException
  Performs a query in which each element value in the object array is used as a replacement parameter for the query statement.This method handles the creation and closing of PreparedStatement and ResultSet on its own.
  ２. public Object query(String sql, ResultSetHandler rsh, Object... params) throws SQLException
  Almost the same as the first method; the only difference is that it does not provide a database Connection to the method, and it retrieves the Connection from either the data source provided to the construction method (DataSource) or the setDataSource method used.
  ３. public Object query(Connection conn, String sql, ResultSetHandler rsh) throws SQLException
  Perform a query operation that does not require a replacement parameter.
  ４. public int update(Connection conn, String sql, Object... params) throws SQLException
  Used to perform an update (insert, update, or delete) operation.
  ５. public int update(Connection conn, String sql) throws SQLException
  Used to perform an update operation that does not require a replacement parameter.

ResultSetHandler interface

• Summary
  This interface is used to process java.sql.ResultSet to convert data to another form as required.

• Implementation Class of Interface

  1. ArrayHandler: Converts the first row of data in the result set to an array of objects.
  2. ArrayListHandler: Turn each row of data in the result set into an array and store it in the List.
  3. BeanHandler: Encapsulates the first row of data in the result set into a corresponding JavaBean instance.
  4. BeanListHandler: Encapsulate each row of data in the result set into a corresponding JavaBean instance and store it in the List.
  5. ColumnListHandler: Store data from a column in the result set in a List.
  6. KeyedHandler(name): Encapsulate each row of data in the result set into a Map, then save the maps into a map with the key specified.
  7. MapHandler: Encapsulate the first row of data in the result set into a Map, where key is the column name and value is the corresponding value.
  8. MapListHandler: Encapsulate each row of data in the result set into a Map and store it in a List.

Code Samples

JDBCTools

It includes methods such as getting a database connection, closing database resources, and so on.

package com.test.jdbc;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

/**
 * JDBC Tool class
 * 
 * It includes methods such as getting a database connection, closing database resources, and so on.
 */
public class JDBCTools {

    private static DataSource dataSource = null;

    //The database connection pool should only be initialized once. 
    static{
        dataSource = new ComboPooledDataSource("helloc3p0");
    }

    public static Connection getConnection() throws Exception {
        return dataSource.getConnection();
    }

    public static void releaseDB(ResultSet resultSet, Statement statement,
            Connection connection) {

        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (connection != null) {
            try {
                //When closing the Connection object of the database connection pool
                //Instead of actually closing, the database connection is returned to the database connection pool. 
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

JavaBean

package com.test.jdbc;

import java.sql.Date;

public class Customer {

    private Integer id;
    private String name;
    private String email;
    private Date birth;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getCustomerName() {
        return name;
    }

    public void setCustomerName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Date getBirth() {
        return birth;
    }

    public void setBirth(Date birth) {
        this.birth = birth;
    }

    @Override
    public String toString() {
        return "Customer [id=" + id + ", name=" + name + ", email=" + email
                + ", birth=" + birth + "]";
    }

    public Customer(Integer id, String name, String email, Date birth) {
        super();
        this.id = id;
        this.name = name;
        this.email = email;
        this.birth = birth;
    }

    public Customer() {
        // TODO Auto-generated constructor stub
    }

}

DBUtilsTest

Test DBUtils Tool Class

package com.test.jdbc;

import java.io.IOException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryLoader;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

/**
 * Test DBUtils Tool Class
 *
 */
public class DBUtilsTest {

    /**
     * QueryLoader: It can be used to load resource files that hold SQL statements.
     * Use this class to externalize SQL statements into a resource file. To provide better decoupling
     * @throws IOException 
     */
    @Test
    public void testQueryLoader() throws IOException{
        // /The root directory representing the class path. 
        Map<String, String> sqls = 
                QueryLoader.instance().load("/sql.properties");

        String updateSql = sqls.get("UPDATE_CUSTOMER");
        System.out.println(updateSql); 
    }

    /**
     * 1. ResultSetHandler Role: The return value of QueryRunner's query method ultimately depends on
     * query The return value of the hanlde method for the ResultHandler parameter of the method. 
     * 
     * 2. BeanListHandler: Converts the result set to a List of Beans and returns. Bean's type is
     * BeanListHanlder object is created as a Class object. It can be mapped to aliases of columns 
     * JavaBean Property name of: 
     * String sql = "SELECT id, name customerName, email, birth " +
     *          "FROM customers WHERE id = ?";
     * 
     * BeanListHandler(Class<T> type)
     * 
     * 3. BeanHandler: Convert the result set to a Bean and return. Bean's type is creating a BeanHandler
     * When an object is passed in as a Class object
     * BeanHandler(Class<T> type) 
     * 
     * 4. MapHandler: Turn the result set into a Map object and return it. If there are multiple records in the result set, return only
     * Map object for the first record. Map's key: column name (not alias of column), value: value of column
     * 
     * 5. MapListHandler: Turn the result set into a collection of Map objects and return. 
     * Map Key: Column name (not alias of column), Value: Value of column
     * 
     * 6. ScalarHandler: You can return a value from a specified column or a value from a statistical function. 
     */

    @Test
    public void testScalarHandler(){
        Connection connection = null;
        QueryRunner queryRunner = new QueryRunner();

        String sql = "SELECT name FROM customers " +
                "WHERE id = ?";

        try {
            connection = JDBCTools.getConnection();
            Object count = queryRunner.query(connection, sql, 
                    new ScalarHandler(), 6);

            System.out.println(count); 
        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            JDBCTools.releaseDB(null, null, connection);
        }
    }

    @Test
    public void testMapListHandler(){
        Connection connection = null;
        QueryRunner queryRunner = new QueryRunner();

        String sql = "SELECT id, name, email, birth " +
                "FROM customers";

        try {
            connection = JDBCTools.getConnection();
            List<Map<String, Object>> mapList = queryRunner.query(connection, 
                    sql, new MapListHandler());

            System.out.println(mapList); 
        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            JDBCTools.releaseDB(null, null, connection);
        }
    }

    @Test
    public void testMapHandler(){
        Connection connection = null;
        QueryRunner queryRunner = new QueryRunner();

        String sql = "SELECT id, name customerName, email, birth " +
                "FROM customers WHERE id = ?";

        try {
            connection = JDBCTools.getConnection();
            Map<String, Object> map = queryRunner.query(connection, 
                    sql, new MapHandler(), 4);

            System.out.println(map); 
        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            JDBCTools.releaseDB(null, null, connection);
        }
    }

    /**
     * Test BeanListHandler implementation class for ResultSetHandler
     * BeanListHandler: Convert the result set to a List of Bean s.
     * The type of the is passed in when the BeanListHandler object is created:
     * 
     * new BeanListHandler<>(Customer.class)
     * 
     */
    @Test
    public void testBeanListHandler(){
        String sql = "SELECT id, name customerName, email, birth " +
                "FROM customers";

        //1. Create QueryRunner Object
        QueryRunner queryRunner = new QueryRunner();

        Connection conn = null;

        try {
            conn = JDBCTools.getConnection();

            Object object = queryRunner.query(conn, sql, 
                    new BeanListHandler<>(Customer.class));             

            System.out.println(object); 
        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            JDBCTools.releaseDB(null, null, conn);
        }
    }

    /**
     * Testing QueryRunner's query method
     */
    @SuppressWarnings({ "unchecked", "rawtypes" })
    @Test
    public void testResultSetHandler(){
        String sql = "SELECT id, name, email, birth " +
                "FROM customers";

        //1. Create QueryRunner Object
        QueryRunner queryRunner = new QueryRunner();

        Connection conn = null;

        try {
            conn = JDBCTools.getConnection();
            /**
             * 2. Call the query method:
             * ResultSetHandler Role of the parameter: The return value of the query method depends directly on the 
             * ResultSetHandler How hanlde (ResultSets rs) is implemented. In fact, in
             * QueryRunner The handle() of the ResultSetHandler is also called in the query method of the class
             * Method as return value.
             */
            Object object = queryRunner.query(conn, sql, 
                    new ResultSetHandler(){
                        @Override
                        public Object handle(ResultSet rs) throws SQLException {
                            List<Customer> customers = new ArrayList<>();

                            while(rs.next()){
                                int id = rs.getInt(1);
                                String name = rs.getString(2);
                                String email = rs.getString(3);
                                Date birth = rs.getDate(4);

                                Customer customer = 
                                        new Customer(id, name, email, birth);
                                customers.add(customer);
                            }

                            return customers;
                        }
                    }

                    );          

            System.out.println(object); 
        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            JDBCTools.releaseDB(null, null, conn);
        }

    }

    /**
     * Testing the update method of the QueryRunner class
     * This method can be used in INSERT, UPDATE and DELETE
     */
    @Test
    public void testQueryRunnerUpdate() {
        //1. Create an implementation class for QueryRunner
        QueryRunner queryRunner = new QueryRunner();

        String sql = "DELETE FROM customers " +
                "WHERE id IN (?,?)";

        Connection connection = null;

        try {
            connection = JDBCTools.getConnection();
            //2. Use its update method
            queryRunner.update(connection, 
                    sql, 12, 13);
        } catch (Exception e) {
            e.printStackTrace();
        } finally{
            JDBCTools.releaseDB(null, null, connection);
        }

    }

}