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

Copyright notice

• The original author of this article: Guge's younger brother
• Author's blog address: http://blog.csdn.net/lfdfhl

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

Add new comment

0 comments