Apache Commons DbUtils integrates Spring framework to realize simple CRUD

Keywords: Database Java JDBC Spring SQL

Commons DbUtils is an open source tool class library provided by Apache, which can simply encapsulate JDBC and simplify the development of JDBC. Commons DbUtils is very convenient to integrate the Spring Framework, which is relatively lightweight. It is very convenient to execute SQL statements (especially query statements). It can replace the database access layer technologies such as Spring JdbcTemplate and MyBatis.

To configure

DbUtils executes SQL through QueryRunner class, which is very similar to the JdbcTemplate in Spring framework.

<bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/test_db" />
    <property name="username" value="root" />
    <property name="password" value="xxx" />
</bean>
<bean id="queryRunner" class="org.apache.commons.dbutils.QueryRunner">
    <constructor-arg ref="dataSource" />
</bean>

The connection pool I use here is HikariCP, which can be replaced by other connection pools as needed, such as DBCP, Druid, etc.

Because DbUtils itself does not support Spring transactions, if you want to support transactions, such as @ Transactional annotation, you need to add a layer of agent to DataSource:

<bean id="dataSource" class="org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy">
    <property name="targetDataSource">
        <bean class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
            <property name="driverClassName" value="com.mysql.jdbc.Driver" />
            <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/test_db" />
            <property name="username" value="root" />
            <property name="password" value="xxx" />
        </bean>
    </property>
</bean>
<bean id="queryRunner" class="org.apache.commons.dbutils.QueryRunner">
    <constructor-arg ref="dataSource" />
</bean>

In Java code, QueryRunner can be introduced through Autowired:

public class UserDao {

    @Autowired
    private QueryRunner queryRunner;
}

Additions and deletions

Add, DELETE and UPDATE operations, that is, INSERT, DELETE and UPDATE statements, can be executed directly through QueryRunner's execute method:

queryRunner.execute("delete from user_info");
queryRunner.execute("update user_info set user_name=?,user_age=? where user_id=?", "xxg", 28, 6);

Because DbUtils itself is based on PreparedStatement in JDBC, it also supports SQL with parameters.

query

ResultSetHandler

ResultSetHandler is an interface in DbUtils. Its implementation class can be used to convert the result returned by JDBC query statement (that is, ResultSet) to the data type you want. This is very similar to the RowMapper interface used in Spring JdbcTemplate query.

A ResultSetHandler implementation class is written below to convert a SQL query result into a list < user >:

List<User> list = queryRunner.query("select * from user_info limit 100", new ResultSetHandler<List<User>>() {
    @Override
    public List<User> handle(ResultSet rs) throws SQLException {
        List<User> l = new ArrayList<User>();
        while (rs.next()) {
            User user = new User();
            user.setUserId(rs.getInt("user_id"));
            user.setUserName(rs.getString("user_name"));
            user.setCreateTime(rs.getTimestamp("create_time"));
            l.add(user);
        }
        return l;
    }
});

Because there is only one abstract method in the ResultSetHandler interface, you can also use Lambda expressions to simplify the code if it is Java 8:

List<User> list = queryRunner.query("select * from user_info limit 100", rs -> {
    List<User> l = new ArrayList<User>();
    while (rs.next()) {
        User user = new User();
        user.setUserId(rs.getInt("user_id"));
        user.setUserName(rs.getString("user_name"));
        user.setCreateTime(rs.getTimestamp("create_time"));
        l.add(user);
    }
    return l;
});

Common ResultSetHandler implementation classes

DbUtils provides some commonly used ResultSetHandler implementation classes, which can simplify queries. In general, you do not need to implement the ResultSetHandler interface yourself as above.

ScalarHandler

Used to return the first row and the first column of the query result:

long count = queryRunner.query("select count(*) from user_info", new ScalarHandler<Long>()); // Query count
String userName = queryRunner.query("select user_name from user_info where user_id=?", new ScalarHandler<String>(), 1); // Query user name of user with id = 1

ColumnListHandler

Return the data List of the first column of the query result (all rows):

List<String> userNameList = queryRunner.query("select user_name from user_info", new ColumnListHandler<String>()); // Query user name of all users

MapHandler

Return the first row of data (all columns) of the query result and assemble it into a Map. The key of the Map is the column name and the value is the value:

Map<String, Object> userInfo = queryRunner.query("select user_id,user_name from user_info where user_id=1", new MapHandler());
long userId = (Long) userInfo.get("user_id");
String userName = (String) userInfo.get("user_name");

MapListHandler

Similar to the MapHandler mechanism, MapListHandler will convert the ResultSet to a list < map < string, Object > >

List<Map<String, Object>> dataList = queryRunner.query("select user_id,user_name from user_info", new MapListHandler());

ArrayHandler

Return the first row of data in the query result, and make all column values into one data in order:

Object[] data = queryRunner.query("select user_id,user_name from user_info where user_id=1", new ArrayHandler());
long userId = (Long) data[0];
String userName = (String) data[1];

ArrayListHandler

Similar to the ArrayHandler mechanism, ArrayListHandler will convert the ResultSet to a list < object [] >

List<Object[]> list = queryRunner.query("select user_id,user_name from user_info", new ArrayListHandler());

KeyedHandler

Turn the ResultSet to map <? Map < string, Object > > and each element of the outer map corresponds to a piece of data in the query result. The key is the primary key or unique index of the data, and the value is also a map. The content is the column name and value of a row of data, similar to the MapHandler mechanism:

Map<Long, Map<String, Object>> dataMap = queryRunner.query("select user_id,user_name from user_info", new KeyedHandler<Long>("user_id")); // Key is specified as the user ID column
Map<String, Object> data = dataMap.get(1L); // Get a record with user? Id = 1
long userId = (Long) data.get("user_id");
String userName = (String) data.get("user_name");

BeanHandler

BeanHandler is a practical class. You can map the first row of data in the query result to a Java object according to the database column name through the reflection mechanism. Define a Java object first:

public class User {

    private int userId;
    private String userName;
    private int userAge;
    private Date createTime;

    // Omit getter setter
}

Execute query:

// Query User data with User id = 1 and return User object
User user = queryRunner.query("select user_id as userId,user_name as userName,user_age as userAge,create_time as createTime from user_info where user_id=1", new BeanHandler<User>(User.class));

Many people will use underscores AS separators between words for database column names, while Java Naming specification requires variable names to be hump names, which will lead to no direct mapping, so in the above code, we use AS to convert column name underscores to hump names in SQL statements. But if there are many fields, or you want to use select * query, the above method is not easy to use. The following provides a scheme to map the column names with the underscore separator to the Java object named for the hump:

User user = queryRunner.query("select user_id,user_name,user_age,create_time from user_info where user_id=1",
        new BeanHandler<User>(User.class, new BasicRowProcessor(new GenerousBeanProcessor())));

BeanListHandler

Similar to the BeanHandler mechanism, BeanListHandler can convert multiple query results into a List of Java beans:

List<User> userList = queryRunner.query("select user_id as userId,user_name as userName,user_age as userAge,create_time as createTime from user_info",
        new BeanListHandler<User>(User.class));

You can also map column names with underscores to Java objects with hump names:

List<User> userList = queryRunner.query("select user_id,user_name,user_age,create_time from user_info",
        new BeanListHandler<User>(User.class, new BasicRowProcessor(new GenerousBeanProcessor())));

BeanMapHandler

Similar to the KeyedHandler mechanism, the ResultSet is converted into a Map. Each data in the Map corresponds to a piece of data in the query result. The key is the primary key or unique index of the data. value is the Java object that the data is converted into through the reflection mechanism.

Map<Long, User> users = queryRunner.query("select user_id as userId,user_name as userName,user_age as userAge,create_time as createTime from user_info",
        new BeanMapHandler<Long, User>(User.class, "userId")); // Use the userId column as the key of the Map
User user1 = users.get(1L); // Get user with user? Id = 1