Apache Commons DbUtils User Manual

Apache Commons DbUtils User Manual
Apache Commons DbUtils user manual I. Introduction

DBUtils is a lightweight JDBC package. Its core feature is to encapsulate the result set on the basis of JDBC, the query result set can be directly encapsulated into a JavaBean to simplify the confusion and repetition of JDBC code.
JDBC code development has many difficulties:
1) the operation process is complex. The Code operation mode is large and repetitive.
2) The result set is hard to process.
3) SQLException is mandatory everywhere, which affects the appearance and readability of the Code.

2. Be familiar with DBUtils

Before using DBUtils, pay attention to the following issues:
1) DBUtils is a simple encapsulation of JDBC and can be used together with JDBC.
2) DBUtils has strict requirements on automatic result set encapsulation as a JavaBean:
Must meet the specifications of JavaBean;
Secondly, the getter and setter methods of Bean must have one-to-one correspondence with the names of result set columns;
Private Members of JavaBean are not required to correspond to the table result set column names one by one.
3) DBUtils can encapsulate result sets into various types
Mainly include: Bean/List <Bean>, Map/List <Map>/Map <Map>, array/List <array>, and column/List <column>.
For the Map <Map> type, KeyedHandler is used as the result set Processor. The inner Map is the "column name-value" pair, and the outer Map is the "reference of the primary key-Inner Map ", however, the primary key here is not necessarily the primary key of the database and can be specified at will.
4) When DBUtils performs the insert operation, it cannot return the auto-incrementing primary key. This is a very serious problem. Of course, DBUtils cannot be blamed. It can be implemented through a work ing method, for example, in MySQL, after executing an insert SQL statement, run the SELECT LAST_INSERT_ID () statement to obtain the auto-incrementing primary key.
5) The performance of DBUtils is the same as that of JDBC. During the test, no performance loss is found, and the performance is high, without losing the flexibility of JDBC.
6) for the definition of the member type of JavaBean, there is a principle that: Use the packaging type as much as possible, rather than the basic type.

Iii. API Introduction

1. org. apache. commons. dbutils package
DbUtils: a small class library to simplify JDBC operations
AbstractQueryRunner: it is the base class of the abstract class, QueryRunner and AsyncQueryRunner classes.
AsyncQueryRunner: Execute SQL queries in a pluggable manner to process the result set. Is a thread-safe class.
BaseResultSetHandler: converts a result set to an extension of another object.
BeanProcessor: BeanProcessor matches the column name of the Bean attribute and converts the result set column to the attribute of the Bean object.
Dbutils: a set of JDBC auxiliary tools.
GenerousBeanProcessor: Provides intelligent matching between the database column name and the JavaBean attribute.
ProxyFactory: the proxy implementation that generates the JDBC interface.
QueryLoader: The property file loader, which is mainly used to load SQL statements in the property file to the memory.
QueryRunner: Execute SQL queries and process result sets using pluggable policies.
ResultSetHandler: a tool that converts a ResultSet to another object.
ResultSetIterator: The encapsulated result set is an iterator.
RowProcessor: A Tool for converting ResultSet rows to other objects.

2. org. apache. commons. dbutils. handlers package
AbstractKeyedHandler: the abstract class of KeyedHandler.
AbstractListHandler: simplifies the abstract class developed by the ResultSetHandler class and converts the result set to List.
ArrayHandler: converts the first row of data in the result set into an array of objects.
ArrayListHandler: converts each row of data in the result set into an object array and stores it in the List.
BeanHandler: encapsulate the first row of data in the result set into a corresponding JavaBean instance.
BeanListHandler: encapsulate each row of data in the result set into a corresponding JavaBean instance and store it in the List.
BeanMapHandler: implements the Bean return Map set. All rows in the result set are converted to beans and stored in Map based on the specified Key.
ColumnListHandler: stores the data of a column in the result set to the List.
KeyedHandler: encapsulate each row of data in the result set into a Map, and store each Map in a Map based on 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 the List.
ScalarHandler: stores data in a column of a record in the result set as an Object.

3. org. apache. commons. dbutils. wrappers package
SqlNullCheckedResultSet: checks the ResultSet packaging class of SQL NULL values on each getXXX method.
StringTrimmedResultSet: deletes the ResultSet packaging class with spaces before and after the string in the result set.

Iv. Important categories

1. DbUtils class
DbUtils provides tools such as shutting down connections and loading JDBC drivers. All the methods in the tool are static.
The main method is as follows:
1) public static void close (...) throws SQLException
The DbUtils class provides three overload close methods. These methods check whether the provided parameters are NULL. If they are NULL, disable Connection, Statement, and ResultSet.
2) public static void closeQuietly (...)
The bUtils class provides four overloaded closeQuietly methods. This method can not only avoid closing when Connection, Statement, and ResultSet are NULL, but also hide SQLEeception thrown in the program.
3) public static void commitAndCloseQuietly (Connection conn)
Submit SQL statements in the connection, close the connection, and do not throw an SQL exception when closing the connection.
4) public static boolean loadDriver (String driverClassName)
This method loads and registers the JDBC driver. If it succeeds, true is returned. If it fails, false is returned. This method does not need to capture ClassNotFoundException exceptions.

2. QueryRunner class
The QueryRunner class simplifies SQL queries. It can be combined with ResultSetHandler to complete most database operations and greatly reduce the amount of code.
The QueryRunner class provides two constructor methods.
1) default constructor
2) A javax. SQL. DataSource is required for parameter construction.

3. Main Methods of the QueryRunner class
1) public Object query (Connection conn, String SQL, Object [] params, ResultSetHandler rsh) throws SQLException
Execute a query operation. In this query, each element value in the object array is used as a replacement parameter of the query statement. This method will automatically process the creation and closure of PreparedStatement and ResultSet.
2) public Object query (String SQL, Object [] params, ResultSetHandler rsh) throws SQLException
Almost the same as the first method; the only difference is that it does not provide the database connection to the method, and it is from the data source (DataSource) provided to the constructor) or use the setDataSource method to obtain the Connection again.
3) public Object query (Connection conn, String SQL, ResultSetHandler rsh) throws SQLException
Perform a query operation that does not require parameter replacement.
4) public int update (Connection conn, String SQL, Object [] params) throws SQLException
Used to perform an update (insert, update, or delete) operation.
5) public int update (Connection conn, String SQL) throws SQLException
It is used to perform an update operation without replacing parameters.
6) insert Method
7) batch Processing Method