Dbutils is used basically, and dbutils is used

Dbutils is used basically, and dbutils is used

Dbutils queries mainly use the query method, adding, modifying, and deleting all the update methods.

You only need to create different implementation class objects of the ResultSetHandler interface to obtain the desired query result.

Next we will explain some of the frequently used:

The following code throws an exception. To make it simple, I have removed the exception here.

ArrayHandler class: encapsulate the 'first row' record of the result set into an array,
Test code:

QueryRunner qr = new QueryRunner(dataSource);    String sql = "select * from user";    Object[] objArr = qr.query(sql, new ArrayHandler());}

View Code

ArrayListHandler class: encapsulate each row of the result set into an array, and then put these arrays into a collection to return
Test code:

QueryRunner qr = new QueryRunner(dataSource);    String sql = "select * from user";    List<Object[]> list = qr.query(sql, new ArrayListHandler());}

View Code

BeanHandler class: the 'first row' record of the result set is returned as a javaBean object (premise: the field name of the result set (aliases can be used) is consistent with the attribute name of the javaBean object)
Test code:

QueryRunner qr = new QueryRunner(dataSource);String sql = "select * from user";User user = qr.query(sql, new BeanHandler<User>(User.class));

View Code

BeanListHandler class: encapsulate each row of the result set into a javaBean object, and then put these objects into a List set to return
Test code:

QueryRunner qr = new QueryRunner();String sql = "select * from user";List<User> userList = qr.query(conn, sql, new BeanListHandler<User>(User.class));

View Code

ScalarHandler class: return a value in the first row of the 'result set', depending on the parameters of the constructor. the return value type depends on the field type in the table.
Three constructor methods are available for aggregate queries.
New ScalarHandler (); returns the value of the first column in the first row of the result set.
New ScalarHandler (int columnIndex); returns the value of the columnIndex column in the first row of the result set (the column starts from 1)
New ScalarHandler (String columnName); returns the value of columnName in the first row of the result set.
Test code:

QueryRunner qr = new QueryRunner (); String SQL = "select id, username, Hober as hob from user"; Integer obj1 = qr. query (conn, SQL, new ScalarHandler <Integer> (); // The value of String obj2 = qr in the first column of the First row in the result set. query (conn, SQL, new ScalarHandler <String> (2); // The value of String obj3 = qr in Column 2nd of the first row of the result set. query (conn, SQL, new ScalarHandler <String> ("hob") // the name of the first row in the result set is hob.

View Code

ColumnListHandler class: stores data from a column in the result set to the List. Similar to ScalarHandler, there are three constructor methods.
Test code:

QueryRunner qr = new QueryRunner();String sql = "select * from user";// List<Integer> idList = qr.query(conn, sql, new ColumnListHandler<Integer>());// List<String> idList = qr.query(conn, sql, new ColumnListHandler<String>("hobby"));List<String> list = qr.query(conn, sql, new ColumnListHandler<String>(4));

View Code

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.
Test code:

QueryRunner qr = new QueryRunner();String sql = "select * from user";Map<String, Object> map = qr.query(conn, sql, new MapHandler());

View Code

MapListHandler: encapsulate each row of data in the result set in a Map, and store the data in the List.
Test code:

QueryRunner qr = new QueryRunner();String sql = "select * from user";List<Map<String, Object>> listMap = qr.query(conn, sql, new MapListHandler());

View Code

Here are examples without parameters. If there are parameters, what are the parameters used in SQL? And then pass the parameters in order using the query method with Parameters

For more information about how to connect to a database, see the following article: how to connect c3p0 to a database: