Backstage of Guge's Primary School (21) - DBUtils

Exploring the Difficulties of Android Soft Keyboard
Deep Exploration of Android Asynchronous Essence Handler
Detailing the indispensable cornerstones of Android's mainstream framework
Stand on the shoulder of source code and fully understand Scroller's working mechanism

Android Multiresolution Adaptation Framework (1) - Core Foundation
Android Multiresolution Adaptation Framework (2) - Principle Analysis
Android Multiresolution Adaptation Framework (3) - Guidelines for Use

Custom View Series Tutorial 00 - Overthrow Yourself and Past, Re-learn Custom View
Custom View Series Tutorial 01 - Introduction to Common Tools
Detailed Source Analysis of Custom View Series Tutorial 02-onMeasure
Custom View Series Tutorial 03 - onLayout Source Detailed Analysis
Source Code Analysis and Practice of Custom View Series 04-Draw
Custom View Series Tutorial 05 - Example Analysis
Custom View Series Tutorial 06 - Detailed View Touch Event Processing
Custom View Series Tutorial 07 - Detailed ViewGroup Distribution Touch Events
Custom View Series Tutorial 08 - The Generation and Handling of Sliding Conflict

Copyright notice

Introduction to DBUtils

DBUtils is an open source database operation tool provided by Apache. It is a simple encapsulation of JDBC and greatly simplifies the workload of JDBC coding. For example, when querying data, it can convert the results into lists, Array, Set s and other collections, which is very convenient for developers to operate.

Three Core Objects of DBUtils

  • QueryRunner class
    Mainly used for adding, deleting, modifying, checking

  • ResultSetHandler interface
    Mainly used for processing result sets

  • Class DBUtils
    This tool class is mainly used for closing connections, loading JDBC drivers, and so on.

Well, we'll focus on the QueryRunner class and the ResultSetHandler interface in the next section, so keep looking.

QueryRunner

The QueryRunner class provides two constructive methods

  • new QueryRunner()
    If we use this method to create Query Runner, then the transaction of the database can be controlled manually by ourselves. Because the constructor has no parameters, you need to pass in the parameter Connection when calling query, update, batch of the object.

  • new QueryRunner(DataSource ds);
    If this method is used to create Query Runner, then the transactions of the database are automatically controlled by DBUtils. Because the constructor passes in the parameter QueryRunner, there is no need to pass in the parameter Connection when calling query, update, batch of the object.

Query Runner's common methods are as follows:

public Object query(Connection conn, String sql, Object[] params, ResultSetHandler rsh) throws SQLException
Perform a query in which each value in the Object array is used as a replacement parameter for the query statement. This method handles the creation and closure of PreparedStatement and ResultSet by itself.

public Object query(String sql, Object[] params, ResultSetHandler rsh) throws SQLException
This method is basically the same as the one above; the difference is that it does not need to provide Connection to this method.

public Object query(Connection conn, String sql, ResultSetHandler rsh) throws SQLException
This method is basically the same as the above two methods. It is used to perform a query operation without permutation parameters.

public int update(Connection conn, String sql, Object[] params) throws SQLException
Perform an update operation in which each element value in the Object array is used as a replacement parameter for the update statement.

public int update(Connection conn, String sql) throws SQLException
This method is basically the same as the above method, which is used to perform an update operation without permutation parameters.

Now, let's look at a simple use of DBUtils with an example

/**
 * Author: Gu Ge's younger brother
 * Blog address: http://blog.csdn.net/lfdfhl
 */
package cn.com;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;

public class TestCRUD {

    @Test
    public void testInsert() throws SQLException{
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        qr.update("insert into student(studentid,studentname) values(?,?)", 5,"Ji Ze dark step");
    }

    @Test
    public void testUpdate() throws SQLException{
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        qr.update("update student set studentname=? where studentid=?", "Ming Bu Yoshizawa",5);
    }


    @Test
    public void testDelete() throws SQLException{
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        qr.update("delete from student where studentid=?",2);
    }

    @Test
    public void testSelect()throws SQLException{
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        BeanListHandler<Student> beanListHandler=new BeanListHandler<Student>(Student.class);
        List<Student> list = qr.query("select * from student",beanListHandler);
        for(Student s: list){
            System.out.println(s);
        }
    }

}

Well, see, we only use very little code to add, delete and modify. Especially in the last example, the query results are directly converted into List, which brings great convenience to development.

ResultSetHandler

The commonly used implementation classes of the ResultSetHandler interface are as follows:

ArrayHandler
Converting the first row of data in the result set to an array of objects

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
Each row of data in the result set is encapsulated in a corresponding JavaBean instance and stored in a List.

ColumnListHandler
Store data from a column in the result set in a List

KeyedHandler(name)
Each row of data in the result set is encapsulated in a Map < column name, column value > and then these maps are stored in a map whose key is the specified key.

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

MapListHandler
Encapsulate each row of data in the result set into a Map and store it in List

For the use of ResultSetHandler interface implementation classes, see the following examples:

/**
 * Author: Gu Ge's younger brother
 * Blog address: http://blog.csdn.net/lfdfhl
 */
package cn.com;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.KeyedHandler;
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;

public class TestResultSetHandler {
    @Test
    public void teseArrayHandler() throws SQLException{
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        Object[] arr  = qr.query("select * from student", new ArrayHandler());
        for (Object o : arr) {
            System.out.println(o);
        }
    }

    @Test
    public void teseArrayListHandler() throws SQLException{
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        List<Object[]> query = qr.query("select * from student", new ArrayListHandler());
        for (Object[] os : query) {
            System.out.println(os);
            for (Object o : os) {
                System.out.println(o);
            }
            System.out.println("----------");
        }
    }

    @Test
    public void teseColumnListHandler() throws SQLException{
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        List<Object> list = qr.query("select * from student", new ColumnListHandler(1));
        for (Object o : list) {
            System.out.println(o);
        }
    }

    @Test
    public void testKeyedHandler() throws SQLException{
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        Map<Object,Map<String,Object>> map = qr.query("select * from student", new KeyedHandler(1));
        for (Map.Entry<Object, Map<String,Object>> m : map.entrySet()) {
            System.out.println(m.getKey());
            for (Map.Entry<String, Object> mm : m.getValue().entrySet()) {
                System.out.println(mm.getKey()+"\t"+mm.getValue());
            }
            System.out.println("----------");
        }

    }


    @Test
    public void teseMapHandler() throws SQLException{
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        Map<String,Object> map = qr.query("select * from student where studentid=?", new MapHandler(),3);
        for (Map.Entry<String, Object> m : map.entrySet()) {
            System.out.println(m.getKey()+"\t"+m.getValue());
        }

    }


    @Test
    public void teseMapListHandler() throws SQLException{
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        List<Map<String,Object>> list = qr.query("select * from student", new MapListHandler());
        for (Map<String, Object> map : list) {
            for (Map.Entry<String, Object> m : map.entrySet()) {
                System.out.println(m.getKey()+"\t"+m.getValue());
            }
            System.out.println("----------");
        }
    }

    @Test
    public void teseScalarHandler() throws SQLException{
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        Object o = qr.query("select count(*) from student", new ScalarHandler(1));
        System.out.println(o.getClass().getName());
    }

    @Test
    public void teseBeanHandler() throws SQLException{
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        BeanHandler<Student> beanHandler=new BeanHandler<Student>(Student.class);
        Student student = qr.query("select * from student where studentid=?",beanHandler,3);
        System.out.println(student);
    }


    @Test 
    public void teseBeanListHandler() throws SQLException{
        QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
        BeanListHandler<Student> beanListHandler=new BeanListHandler<Student>(Student.class);
        List<Student> list = qr.query("select * from student where studentid=?", beanListHandler,3);
        for(Student s:list){
            System.out.println(s);
        }
    }
}

DBUtils

The DBUtils class provides resource-related operations such as closing connections, loading JDBC drivers, and so on.

The main methods of DBUtils are as follows:

public static void close( ) throws java.sql.SQLException
Used to close Connection, Statement, and ResultSet

Public static void close Quietly (): This kind of method can not only close Connection, Statement and ResultSet, but also hide some SQLException thrown in the program. If you don't want to catch these exceptions, it's a good choice.

public static void commitAndCloseQuietly(Connection conn): Commit and close the connection without throwing an SQL exception when closing the connection.

public static boolean loadDriver(java.lang.String driverClassName)
Load and register the JDBC driver and return true if successful

Tags: Apache SQL Java Android

Posted on Sat, 30 Mar 2019 00:12:29 -0400 by paulmo