DBUtils tool, dbutils

DBUtils tool, dbutils
DBUtils tool Introduction

Is a product of Apache. Is a simple encapsulation of jdbc. Provides common jdbc operation methods. Simplify the cost for developers to use jdbc.

Common APIs

|-QueryRunner class: adds, deletes, modifies, and queries jdbc.

|-Update (): used for update

|-Query (): used for query

 

|-ResultSetHandler interface: it is mainly used to encapsulate result sets in query operations. ResultSet-> Object

|-ArrayHandler class: encapsulates the first row of records in the result set into an array.

|-ArrayListHandler class: encapsulates each row of the result set into an object array, and each object array is placed in a List set.

|-BeanHandler class: encapsulate the result set into a javabean object.

|-BeanListHandler class: encapsulate each row of data in the result set into a javabean object, and then put the javabean object into the List set.

The above two methods are most frequently used !!!

|-ScalarHandler class: returns the data in the first column of the first row of the result set. Used for Aggregate Queries (for example, count, max, min, avg)

 

After introducing its core API, the following describes how to use it.

 

Steps for using DBUtils

Step 1: import package

Import commons-dbutils-1.2.jar package

 

Step 2: Use it in the code

Use DBUtils for addition, deletion, and modification

 

 

@ Test public void test1 () {// 1. create QueryRunner // Method 1: Use a DataSoutce object as the parameter ComboPooledDataSource pool = new ComboPooledDataSource (); QueryRunner qr = new QueryRunner (pool); try {// execute the SQL statement qr. update ("insert into students (id, name) values (?,?) ", New Object [] {8," JAX "});} catch (SQLException e) {e. printStackTrace (); throw new RuntimeException (e );}}

 

Let's take a look at Method 2:

@ Test public void test2 () {// 1. create QueryRunner // Method 2: ComboPooledDataSource pool = new ComboPooledDataSource (); QueryRunner qr = new QueryRunner (); Connection conn = null; try {conn = pool. getConnection (); // run the specified SQL statement qr with the specified conn. update (conn, "insert into students (id, name) values (85, 'json')");} catch (SQLException e) {e. printStackTrace (); throw new RuntimeException (e);} finally {if (conn! = Null) {try {conn. close (); // put back the connection pool} catch (SQLException e) {e. printStackTrace ();}}}}

Use DBUtils for query

The biggest use of DBUtils is to encapsulate the results after the query ~ The following is an example

 

1. ArrayHandler: returns an array of objects from the first row of the query result.

 

@ Test public void test3 () {// 1. create QueryRunner combooleddatasource pool = new ComboPooledDataSource (); QueryRunner qr = new QueryRunner (pool); try {// execute the SQL statement and use ArrayHandler Object [] obj = (Object []) qr. query ("select * from students", new ArrayHandler (); for (int I = 0; I <obj. length; I ++) {System. out. println (obj [I]);} catch (SQLException e) {e. printStackTrace (); throw new RuntimeException (e );}}

 

2. ArrayListHandler: encapsulate each row of the query results into an array, and then put these arrays into a collection to return.

 

@ Test public void test4 () {// 1. create QueryRunner combooleddatasource pool = new ComboPooledDataSource (); QueryRunner qr = new QueryRunner (pool); try {// execute the SQL statement and use ArrayListHandler () list <Object []> list = (List <Object []>) qr. query ("select * from students", new ArrayListHandler (); for (Object [] obj: list) {for (int I = 0; I <obj. length; I ++) {System. out. println (obj [I] + "\ t") ;}} catch (SQLException e) {e. printStackTrace (); throw new RuntimeException (e );}}

 

3. BeanHandler: returns a javaBean object to the first record of the result set.

Premise: the Field Names of the result set are consistent with those of the javaBean attribute.

 

@ Test public void test5 () {// 1. create QueryRunner combooleddatasource pool = new ComboPooledDataSource (); QueryRunner qr = new QueryRunner (pool); try {// execute the SQL statement and use BeanHandler Students s = (Students) qr. query ("select * from students", new BeanHandler (Students. class); System. out. println (s);} catch (SQLException e) {e. printStackTrace (); throw new RuntimeException (e );}}

 

4. BeanListHandler: encapsulate each row in the result set into a javaBean object, and then put these objects into a List set to return.

Premise: the Field Names of the result set are consistent with those of the javaBean attribute.

 

@ Test public void test6 () {// 1. create QueryRunner combooleddatasource pool = new ComboPooledDataSource (); QueryRunner qr = new QueryRunner (pool); try {// execute the SQL statement, use BeanListHandler List <Students> list = (List <Students>) qr. query ("select * from students", new BeanListHandler (Students. class); for (Students s: list) {System. out. println (s) ;}} catch (SQLException e) {e. printStackTrace (); throw new RuntimeException (e );}}

 

5. scalarHandler: return the field value in the first column of the first row of the result set.

It is often used for aggregate queries.

@ Test public void test7 () {// 1. create QueryRunner combooleddatasource pool = new ComboPooledDataSource (); QueryRunner qr = new QueryRunner (pool); try {// execute the SQL statement and use ArrayHandler Object obj = (Object) qr. query ("select count (*) from students", new ScalarHandler (); System. out. println (obj);} catch (SQLException e) {e. printStackTrace (); throw new RuntimeException (e );}}

6. Custom Field name conversion handler

If the field name and the javaBean attribute name are inconsistent, We need to write our own handler to implement the corresponding field name during encapsulation. The following shows how to write our own handler.

 

@ Test public void test8 () {// 1. create QueryRunner combooleddatasource pool = new ComboPooledDataSource (); QueryRunner qr = new QueryRunner (pool); try {// execute the SQL statement and use BeanHandler Students2 s = (Students2) qr. query ("select * from students", new MyStudentsHandler (); System. out. println (s);} catch (SQLException e) {e. printStackTrace (); throw new RuntimeException (e) ;}// manually encapsulate the result set class MyStudentsHandler implements ResultSetHandler {@ Override public Object handle (ResultSet rs) throws SQLException {while (rs. next () {// create a javaBean object Students2 s = new Students2 (); s. setId (rs. getInt ("sid"); // s. setName (rs. getString ("sname"); return s;} return null ;}}

 

The above is some usage of the DBUtils tool, mainly used to encapsulate the object of the result set ~