Use of DBUtils Framework

Yesterday, I did so much work, of course, to introduce today's DBUtils framework. Its implementation principle is similar to the simple framework we wrote.
Say little and get to the point.
commons-dbutils is an open source JDBC tool class library provided by Apache. It is a simple encapsulation of JDBC with low learning cost. Using dbutils can greatly simplify the workload of JDBC coding without affecting the performance of the program. So dbutils is the first choice for many companies that don't like hibernate.

jar package download address: link: https://pan.baidu.com/s/1BKtq_VoBw52qyIJr6c_CZg
Extraction code: skrf
Duplicate this content and open Baidu Disk Mobile App. It's more convenient to operate.

API Introduction:

  • org.apache.commons.dbutils.QueryRunner -- Core Operations Class
  • org.apache.commons.dbutils.ResultSetHandler
  • org.apache.commons.dbutils.DbUtils -- Tool class

The QueryRunner class simplifies the SQL query, which can be combined with ResultSetHandler to perform most of the database operations and greatly reduce the amount of coding.
The QueryRunner class provides two constructions:
Default construction method
A javax.sql.DataSource is needed to construct the parameters.

The QueryRunner class has four overloaded construction methods. If QueryRunner (Data Sources) is used to construct QueryRunner objects, database transactions will be managed by the DBUtils framework. By default, each sql has a single transaction. The method without the Connection parameter should be used at this point.
If you use QueryRunner() to construct QueryRunner objects, you need to manage transactions by yourself, because the framework has no connection pool and can not access database connections, so you should use the method with Connection parameters.

Dbutils tool class can load drivers, close resources and control transactions, so some previous tedious work can be achieved with this tool class.

Write a case to demonstrate.
Create a new web project and don't forget to import the jar package.
New Test Class Query Runner Test
Define a member variable in the class to facilitate subsequent testing

    private static ComboPooledDataSource dataSource = new ComboPooledDataSource();

Writing Inserted Test Code

@Test
public void testInsert() throws SQLException{
    //1. Create QueryRunner objects
    QueryRunner queryRunner = new QueryRunner(dataSource);
    //2. Preparing Method Parameters
    String sql ="insert into account values(null,?,?)";
    Object[] param = {"fff",1000};
    //3. Calling methods
    queryRunner.update(sql, param);
}

The QueryRunner object constructed with this constructor does not need to release resources manually, because the connection is managed by the DBUtils toolkit, which helps us release resources.
Then run the test code

The insertion was successful.

Next, test the update sql and write the test code

    @Test
    public void testUpdate() throws SQLException{
        QueryRunner queryRunner = new QueryRunner(dataSource);
        String sql = "update account set money = ? where name = ?";
        Object[] param = {2000,"fff"};
        queryRunner.update(sql,param);
    }

Running test code

The modification was successful.

Next, test the deletion of sql and write the test code

@Test
public void testDelete() throws SQLException{
    QueryRunner queryRunner = new QueryRunner(dataSource);
    String sql = "delete from account where id = ?";
    queryRunner.update(sql,2);
}

Running test code

Delete successfully.

Now let's simulate a transfer scenario.

@Test
public void testTransfer() throws SQLException{
    double  money = 100;
    String outAccount = "aaa";
    String inAccount = "bbb";
    String sql1 = "update account set money = money - ? where name = ?";
    String sql2 = "update account set money = money + ? where name = ?";
    
    QueryRunner queryRunner = new QueryRunner(dataSource);
    queryRunner.update(sql1,money,outAccount);
    //Produce an error
    int d = 1 / 0;
    queryRunner.update(sql2,money,inAccount);
}

Running test code

Procedure error, aaa account less than 100, and bbb account amount is not much, because you put the connection to DBUtils management, the default sql is a transaction, so we should manage the transaction ourselves, in order to avoid this situation.

Modify transfer code

    @Test
    public void testTransfer() throws SQLException{
        double  money = 100;
        String outAccount = "aaa";
        String inAccount = "bbb";
        String sql1 = "update account set money = money - ? where name = ?";
        String sql2 = "update account set money = money + ? where name = ?";
        
        //Manual transaction management
        QueryRunner queryRunner = new QueryRunner();
        Connection conn = JDBCUtils.getConnection();
        conn.setAutoCommit(false);
        try {
            queryRunner.update(conn,sql1,money,outAccount);
            //Produce an error
            int d = 1 / 0;
            queryRunner.update(conn,sql2,money,inAccount);
            
            DbUtils.commitAndCloseQuietly(conn);
        } catch (Exception e) {
            DbUtils.rollbackAndCloseQuietly(conn);
            e.printStackTrace();
        }
    }

Run the code at this point

The table data has not changed, indicating that the update operation has been rolled back.

Tags: Java SQL Apache JDBC Database

Posted on Sun, 25 Aug 2019 22:16:48 -0700 by chelerblondi