An overview,

1.1 introduction

DbUtils middleware of Apache may not be well known. Most developers rely on persistence layer technology solutions provided by Hibernate, Ibatis, Spring JDBC, JPA and other large manufacturers in the generation environment, or the persistence layer technology developed by the enterprise itself. However, the intent and essence of using these technologies is to reduce enterprise development costs, increase productivity, and reduce coupling. For enterprise projects, ORM products such as Hibernate are preferred, while in the Internet domain, most developers tend not to use these ORM technologies in the production environment, for the simple reason that efficiency is the most important and secondary. For developers who are new to SQL and JDBC, they are most proud of the fact that they want to write complex SQL statements in the future, and will use third-party persistence layer technologies such as Hibernate and Ibatis, and try to distance themselves from traditional JDBC technologies. However, I have to think that, This is a kind of general industry existence “morbid”! For enterprise-level projects, especially finance-related businesses, SQL statements can be very complex and related to things. But the Internet project is not the case, in the Internet project, you do not depend on whether you can write a complex SQL statement, but whether you can break up a complex SQL statement into a single SQL, sentence by sentence execution; Besides, without ORM products like Hibernate, you can use traditional JDBC technology to complete a simple CRUD operation, which is awesome! Yes, you heard right, the Internet is really playing this way, restore the essence of things, is the pursuit of performance is the only choice.

1.1 introduction of DbUtils

In order to simplify JDBC development, we will use a member of the Apache Commons component: Apache Commons DbUtils. Apache Commons DbUtils tool is a lightweight persistence layer solution, is a small JDBC lightweight packaging toolkit, its core feature is based on JDBC to do a layer of packaging, Enables developers to use JDBC technology in a high-level API way to accomplish otherwise complex CRUD operations. Moreover, it also encapsulates the result set, which can be directly encapsulated into JavaBean. DBUtils is designed to simplify JDBC code clutter and duplication, thus helping to extract duplicate code so that developers can focus only on database-related operations.

1.2 Getting Started Applications

Building a JDBC application involves the following six steps

1. Import packages
   • You need to include a package that contains the JDBC classes required for database programming. Most of the time, useimport java.sql.*That’s enough.
2. Register the JDBC driver
   • The driver needs to be initialized so that the communication channel with the database can be opened.
3. Open the connection
   • You need to use the DriverManager. GetConnection () method to create a Connection object, the object represents a physical Connection with database.
4. Execute the query
   • You need an object of type Statement to build and submit SQL statements to the database.
5. Extract data from the result set
   • You are required to retrieve data from the ResultSet using the appropriate resultset.getxxx () method.
6. Clean up the environment
   • You need to explicitly shut down all database resources without relying on the JVM for garbage collection.

Ii. Details of important categories

Before we start, let’s do the preparatory work, first create a table to use, SQL as follows:

create table users(
    id int auto_increment primary key COMMENT 'primary key ID',
    userCode varchar(40) null COMMENT 'user ID',
    userName varchar(40) null COMMENT 'User name',
    email    varchar(60) null COMMENT 'email',
    createTime date COMMENT 'Creation time'
);
Copy the code

2.1 DbUtils

DbUtils provides utility classes for general work such as closing connections, loading JDBC drivers, and classes for useful methods, all of which are static. The important methods in this class are:

Once you have a basic understanding of DbUtils, start using it! Mysql > connect to DbUtils; mysql > connect to DbUtils;

public class CommonsDbutilsTest {
    // Database Connection object Connection
    private Connection connection = null;
    // DbUtils core tool class object
    QueryRunner queryRunner;
    // Database address
    private static final String URL = "jdbc:mysql://localhost:3306/dbutils";
    // The driver to connect to the database
    private static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    // Database user name
    private static final String USER = "test";
    // Database password
    private static final String PASSWORD = "test";

    @Before
    public void before(a) throws SQLException {
        Step 1: Load the database driver
        DbUtils.loadDriver(JDBC_DRIVER);
        // Step 2: Get the database connection object
        connection = DriverManager.getConnection(URL, USER, PASSWORD);
        // Step 3: Create the DbUtils core tool class object
        queryRunner = new QueryRunner();
    }

    /** * Close the Connection database Connection object */
    @After
    public void after(a) { DbUtils.closeQuietly(connection); }}Copy the code

Let’s start with the basic operation of the database.

@Test
public void insert(a) throws SQLException {
    String sql = "insert into users(userCode,userName,email,createTime) values(? ,? ,? ,?) ";
    Object[] params = {"dllwh"."No trace of tears."."[email protected]".new Date()};
    queryRunner.update(connection, sql, params);
    queryRunner.insert(connection, sql, new MapHandler(), params);
}

@Test
public void delete(a) throws SQLException {
    String sql = "delete from users where id=?";
    queryRunner.update(sql, 1);
}

@Test
public void update(a) throws SQLException {
    String sql = "update users set name=? where id=?";
    Object[] params = {"ddd".5};
    queryRunner.update(connection, sql, params);
}

@Test
public void testBatch(a) throws SQLException {
    String sql = "insert into users(userCode,userName,email,createTime) values(? ,? ,? ,?) ";
    Object[][] params = new Object[10] [];for (int i = 0; i < 10; i++) {
        params[i] = new Object[]{"aa" + i, "123"."[email protected]".new Date()};
    }
    queryRunner.batch(connection, sql, params);
}
Copy the code

2.2 ResultSetHandler

ResultSetHandler is an interface in DbUtils whose implementation class can be used to transform and process the results returned by JDBC query statements (i.e., a ResultSet) into any form. The ResultSetHandler interface provides a separate method: Object Handle (java.sql.resultSet.rs). So any execution of a ResultSetHandler requires a ResultSet to be passed in as a parameter before it can process the ResultSet and return an object. Because the return type is java.lang.object, there is no restriction on returning any type other than a raw Java type. Since there is only one abstract method in the ResultSetHandler interface, Lambda expressions can also be used for Java 8 versions to simplify the code:

/** * The implementation of this interface converts a ResultSet to another object * T: the target type (type parameter), that is, the type of the object to which the ResultSet is converted */
public interface ResultSetHandler<T> {
    /** * Convert a ResultSet to an object **@paramRs ResultSet * * to convert@returnIt is also legal for the implementation to return null * if the ResultSet contains 0 rows@throwsA database access error will throw an SQLException */
    T handle(ResultSet rs) throws SQLException;
}
Copy the code

DbUtils provides a number of commonly used ResultSetHandler implementation classes that can simplify queries. In general, you do not need to implement the ResultSetHandler interface yourself as described above. ResultSetHandler interface implementation class:

2.2.1 ArrayHandler

The ArrayHandler returns an array that converts the first row of the result set into an array.

@Test
public void arrayHandler(a) throws SQLException {
    String sql = "select * from users";
    Object[] result = queryRunner.query(connection, sql, new ArrayHandler());
    System.out.println(Arrays.asList(result));
}
Copy the code

2.2.2 ArrayListHandler

ArrayListHandler returns a collection that converts each row in the result set into an array of objects and stores it in a List.

public void arrayListHandler(a) throws SQLException {
    String sql = "select * from users";
    List<Object[]> resultList = queryRunner.query(connection, sql, new ArrayListHandler());
    resultList.forEach(object -> System.out.println(Arrays.asList(object)));
}
Copy the code

2.2.3 BeanHandler

BeanHandler transforms the first row of the result set into a Bean object, meaning that the result of a database query has only one record, which is very convenient in practice.

@Test
public void beanHandler(a) throws SQLException {
    String sql = "SELECT userName FROM users where id = ?";
    Object[] params = {1};
    Person person = queryRunner.query(connection, sql, new BeanHandler<Person>(Person.class), params);
    System.out.println(person);
}
Copy the code

2.2.4 BeanListHandler

The BeanListHandler query object encapsulates each row of data in the result set into a corresponding JavaBean instance, and then adds the JavaBean to the List.

@Test
public void beanListHandler(a) throws SQLException {
    QueryRunner queryRunner = new QueryRunner();
    String sql = "SELECT userName FROM users";
    List<Person> resultList = (List<Person>) queryRunner.query(connection, sql, new BeanListHandler(Person.class));
    resultList.forEach(person -> System.out.println(person.getUserName()));
}
Copy the code

2.2.5 BeanMapHandler

BeanMapHandler encapsulates each row of data in the result set into a JavaBean and stores the JavaBean into a Map. Each data in the Map corresponds to a data in the query result. Key is the primary key or unique index of the data. Value is a Java object that data is converted into through reflection.

@Test
public void beanMapHandler(a) throws SQLException {
    String sql = "select * from users";
    // Use the userCode column as the Map key
    Map<String, Person> result = queryRunner.query(connection, sql, new BeanMapHandler<String, Person>(Person.class, "userCode"));
    System.out.println(result);
}
Copy the code

2.2.6 ColumnListHandler

ColumnListHandler returns a collection of data for the specified column in the result set, the default being the first column.

@Test
public void columnListHandler(a) throws SQLException {
    String sql = "select * from users";
    List resultList = (List) queryRunner.query(connection, sql, new ColumnListHandler());
    resultList.forEach(column -> System.out.println(column));
    
    List resultList2 = (List) queryRunner.query(connection, sql, new ColumnListHandler("userCode"));
    resultList2.forEach(column -> System.out.println(column));
}
Copy the code

2.2.7 KeyedHandler

KeyedHandler encapsulates each row of data in the result set into a MapA(key is the row number,value is the row number), and stores the MapA into a MapB(key is the specified column,value is the row value)

@Test
public void keyedHandler(a) throws SQLException {
    String sql = "select * from users";
    // Key specifies the id column
    Map<Integer, Map> resultMap = (Map) queryRunner.query(connection, sql, new KeyedHandler("id"));
    for (Map.Entry<Integer, Map> me : resultMap.entrySet()) {
        int id = me.getKey();
        Map<String, Object> innermap = me.getValue();
        for (Map.Entry<String, Object> innerme : innermap.entrySet()) {
            String columnName = innerme.getKey();
            Object value = innerme.getValue();
            System.out.println(columnName + "=" + value);
        }
        System.out.println("-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -"); }}Copy the code

2.2.8 MapHandler

The MapHandler encapsulates the first row of data in the result set into a Map, where the key is the column name and the value is the corresponding value.

@Test
public void mapHandler(a) throws SQLException {
    String sql = "select * from users";
    Map<String, Object> resultMap = (Map) queryRunner.query(connection, sql, new MapHandler());
    for (Map.Entry<String, Object> me : resultMap.entrySet()) {
        System.out.println(me.getKey() + "="+ me.getValue()); }}Copy the code

2.2.9 MapListHandler

The MapListHandler encapsulates each row in the result set into a Map and stores it in a List. The data in the collection is the key-value pair transformed for the corresponding row, and the key is the column name.

@Test
public void mapListHandler(a) throws SQLException {
    QueryRunner queryRunner = new QueryRunner();
    String sql = "SELECT * FROM users";
    List<Map> resultList = (List) queryRunner.query(connection, sql, new MapListHandler());
    resultList.forEach(map -> System.out.println(map.get("userName")));
}
Copy the code

2.2.10 ScalarHandler

ScalarHandler returns an object that reads the first row of the result set for the specified column or returns the value of a statistical function, such as count(*). The implementation class automatically deduces the type of the data in the database, paying attention to the type conversion

@Test
public void scalarHandler(a) throws SQLException {
    String sql = "select count(*) from users";
    System.out.println(queryRunner.query(connection, sql, new ScalarHandler(1)));
    System.out.println(queryRunner.query(connection, sql, new ScalarHandler<Long>()));
}
Copy the code

2.3 QueryRunner

Org.apache.com mons. Dbutils. QueryRunner is dbutils library center in the class. It uses pluggable policies to execute SQL queries to handle ResultSets, and the class is thread-safe. This class makes it easy to execute SQL queries, and it works in tandem with the ResultSetHandler to effectively perform mundane tasks. It can greatly reduce the amount of code you have to write. The QueryRunner class provides two constructors: one is an empty constructor and the other takes a javax.sql.DataSource as an argument. So instead of supplying a database connection to a method as an argument, the DataSource supplied to the constructor is used to get a new connection and proceed. There are several ways to construct this class, but the common ones can be roughly divided into two, as shown below:

There are also several more commonly used methods in this class, which are more or less different due to their different constructors. Taking constructing new QueryRunner() without arguments as an example, see the following:

2.4 AsyncQueryRunner

Org.apache.com mons. Dbutils. AsyncQueryRunner class helps to implement the asynchronous support of long running SQL queries. This class is thread-safe. This class supports the same methods as QueryRunner, but it returns a Callable object that can be used later to retrieve results.