Use of DBubtil, use of DBubtil

Use of DBubtil, use of DBubtil

1. What is O-R Mapping (Object-link ing)

Commonly used O-R Mapping tools

Hibernate (fully automated framework)

Ibatis (semi-automatic framework/SQL)

Commons dbutils (simple encapsulation of JDBC)

There are also JPA and so on, this is not particularly known, so far it has come into contact with Hibernate and DbUtils, Hiabernate give people do not need to write SQL statements, directly use the configuration file to map the relationship, duUtils still needs to write SQL statements, but it simplifies crud operations (my opinion)

2. dbutils Introduction

Commons-dbutils is an open source JDBC tool library provided by the Apache organization. It is a simple encapsulation of JDBC and has very low learning costs,In addition, using dbutils can greatly simplify the workload of jdbc encoding without affecting program performance. The core class of the DBUtils framework is the QueryRunner class, which is also an important interface ResultSetHandler (interface ).

3. The QueryRunner class provides two constructor methods:

1> default constructor

2> A javax. SQL. DataSource is required for parameter construction.

3> public Object query (Connection conn, String SQL, Object [] params, ResultSetHandler rsh) throws

SQLException: executes a query operation. In this query, each element value in the object array is used as a replacement parameter of the query statement.

Number. This method automatically handles the creation and closure of PreparedStatement and ResultSet.

4> 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 a data source provided to the constructor.

(DataSource) or use the setDataSource method to obtain the Connection again.
5> public Object query (Connection conn, String SQL, ResultSetHandler rsh) throws SQLException: executes a query operation without replacing parameters.
6> public int update (Connection conn, String SQL, Object [] params) throws SQLException: used to perform an update (insert, update, or delete) operation.
7> public int update (Connection conn, String SQL) throws SQLException: used to perform an update operation without replacing parameters.

4. ResultSetHandler Interface

1> this interface is used to process java. SQL. ResultSet and convert data into another form as required.

2> the ResultSetHandler interface provides a separate method: Object handle (java. SQL. ResultSet. rs ).

3> Implementation class of the ResultSetHandler Interface

A> BeanHandler: encapsulate the first row of data in the result set into a corresponding JavaBean instance. (This is for javabean)

B> BeanListHandler: encapsulate each row of data in the result set into a corresponding JavaBean instance and store it in the List. (This is for javabean)

C> ArrayHandler: converts the first row of data in the result set into an array of objects. (This is for arrays)

D> ArrayListHandler: converts each row of data in the result set into an object array and stores it in the List. (This is for arrays)

E> 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. (This is for Map)

F> MapListHandler: encapsulate each row of data in the result set into a Map and store it in the List. (This is for Map)

H> ScalarHandler: The result set contains only one row and one column of data. (This is for Long)

5. 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 java. SQL. SQLException: The DbUtils class provides three methods for disabling heavy loads. These methods check whether the provided parameters are NULL. If not, they disable Connection, Statement, and ResultSet.

2> public static void closeQuietly (...) : This type of method not only avoids closing when Connection, Statement, and ResultSet are NULL, but also hides SQLException thrown in the program.
3> public static void commitAndCloseQuietly (Connection conn): Used to submit a Connection, close the Connection, and do not throw an SQL exception when closing the Connection.

4> public static boolean loadDriver (java. lang. String driverClassName): this party loads and registers the JDBC driver. If the driver succeeds, true is returned. With this method, you do not need to catch this exception ClassNotFoundException.

6. Note:
1> the update () method of the DBUtils object. The related connection object has been closed internally.

2> If the update (Connection) method has a Connection object, it must be manually closed. Other objects are automatically closed.

If the update () method does not have a Connection object, the DBUtils framework will automatically close it.

3> the reason for doing so is: We mainly consider the need to use the same Connection in the layered structure.

7. Code exercises

?

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101

package cn.wwh.www.web.jdbc.dao; 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.MapHandler;import org.apache.commons.dbutils.handlers.MapListHandler;import org.apache.commons.dbutils.handlers.ScalarHandler;import org.junit.Test; import cn.wwh.www.web.jdbc.domain.User;import cn.wwh.www.web.jdbc.util.JdbcUtils; /** * Function of Class: simple usage of various implementation classes of the ResultSetHandler Interface *  * @ Author *@version 1.0 * @ Creation Time: 04:16:43 */ public class Demo4 {     @Test    public void testBeanHandler() throws SQLException {        QueryRunner run = new QueryRunner(JdbcUtils.getDataSource());        String sql = "select * from UserInfo";        User user = run.query(sql, new BeanHandler(User.class));        System.out.println("beanHandler" + user.toString());     }     @Test    public void testBeanListHandler() throws SQLException {        QueryRunner run = new QueryRunner(JdbcUtils.getDataSource());        String sql = "select * from UserInfo";        List<User> users = run.query(sql, new BeanListHandler(User.class));        for (User user : users) {            System.out.println(user.toString());            System.out.println();         }     }     @Test    public void testArrayHandler() throws SQLException {        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());        String sql = "select * from userInfo";        Object[] array = (Object[]) runner.query(sql, new ArrayHandler());        System.out.println("No :" + array[0]);        System.out.println("User name :" + array[1]);    }     @Test    public void testArrayListHandler() throws SQLException {        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());        String sql = "select * from userInfo";        List<Object[]> list = (List<Object[]>) runner.query(sql,                new ArrayListHandler());        for (Object[] array : list) {            System.out.print("No :" + array[0] + "\t");            System.out.println("User name :" + array[1]);        }    }     @Test    public void testMapHandler() throws SQLException {        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());        String sql = "select * from userInfo";        Map<String, Object> map = runner.query(sql, new MapHandler());        System.out.println("User name :" + map.get("username"));    }     @Test    public void testMapListHandler() throws SQLException {        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());        String sql = "select * from userInfo";        List<Map<String, Object>> list = runner                .query(sql, new MapListHandler());        for (Map<String, Object> map : list) {            System.out.println("User name :" + map.get("username"));            System.out.println("Salary :" + map.get("salary"));        }    }     @Test    public void testScalarHandler() throws SQLException {        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());        String sql = "select count(*) from userInfo";        Long sum = (Long) runner.query(sql, new ScalarHandler());        System.out.println("Total" + sum + "Person");    } }