Use of Dbubtil

1. What is O-r Mapping (object-relational mapping)

Common O-R Mapping Mapping tool

Hibernate (fully automatic frame)

Ibatis (semi-automatic frame/sql)

Commons Dbuti ls (just for JDBC simple encapsulation)

There are JPA, and so on, this is not a special understanding, so far also touched hibernate and dbutils,hiabernate to people without writing SQL statements, directly with the configuration file to map the relationship, Duutils still write SQL statements, He simply simplifies the operation of crud (personal view)

Introduction of 2.dbutils

Commons-dbutils is an open source JDBC Tool class library provided by Apache, which is a simple package for JDBC, with very low learning costs, and using Dbutils can greatly simplify the work of JDBC coding without affecting the performance of the program. The core class of the Dbutils framework is the Queryrunner class, which also has an important interface Resultsethandler (interface).

The 3.QueryRunner class provides two construction methods:

1> Default constructor method

2> requires a javax.sql.DataSource to construct the parameters.

3>public Object Query (Connection conn, String sql, object[] params, Resultsethandler rsh) throws

SQLException: Performs a query operation in which each element value in the object array is used as the replacement parameter for the query statement

Number. This method handles the creation and shutdown of PreparedStatement and ResultSet itself.

4>public Object query (String sql, object[] params, Resultsethandler rsh) throws SQLException: almost

Same as the first method; the only difference is that it does not provide a database connection to the method, and it is from the data source that is provided to the construction method

(DataSource) or Setdatasource method used to regain Connection.
5>public Object Query (Connection conn, String sql, Resultsethandler rsh) throws SQLException: Performs a query operation that does not require displacement 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 that does not require displacement parameters.

4.ResultSetHandler interface

1> This interface is used to process java.sql.ResultSet, converting data to another form as required.

The 2>resultsethandler interface provides a separate method: Object handle (Java.sql.ResultSet. rs).

Implementation class for the 3>resultsethandler interface

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

B>beanlisthandler: Each row of data in the result set is encapsulated in a corresponding JavaBean instance and stored in the list. (This is for JavaBean)

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

D>arraylisthandler: Each row of data in the result set is converted into an array of objects and then stored in the list. (This is for arrays)

E>maphandler: Encapsulates the first row of data in the result set into a map, where key is the column name, and value is the corresponding value. (This is for map)

F>maplisthandler: Each row of data in the result set is encapsulated in a map and then stored in the list. (This is for map)

H>scalarhandler: There is only one row of data in the result set. (This is for long)

5.DbUtils class

Dbutils: A tool class that provides general work such as closing a connection, loading a JDBC driver, and all of the methods are static. The main methods are as follows:

The 1>public static void Close (...) throws Java.sql.SQLException:DbUtils class provides three overloads for the shutdown method. These methods check that the supplied arguments are null, and if not, they close connection, statement, and ResultSet.

2>public static void closequietly (...): This class of methods can not only avoid closing in connection, statement, and resultset null cases, You can also hide some of the sqlexception that are thrown in the program.
3>public static void commitandclosequietly (Connection conn): Used to submit the connection and then close the connection, and does not throw a SQL exception when the connection is closed.

4>public Static Boolean loaddriver (Java.lang.String driverclassname): This side loads and registers the JDBC driver and returns true if successful. Using this method, you do not need to catch this exception classnotfoundexception.

6. Note:
The update () method of the 1>dbutils object that has the associated connection object closed internally

2>update (Connection) method with Connection object, need to be closed manually, other objects automatically closed

Update () method no Connection object, Dbutils frame auto-close

3> The above reasons are: the main consideration in the hierarchical structure, the need to use the same connection problem

7. Code Exercise

?

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465 66676869707172737475767778798081828384858687888990919293949596979899100101

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;/** *类的作用： ResultSetHandler接口的各种实现类的简单用法 *  *@author 一叶扁舟 *@version 1.0 *@创建时间： 2014-9-6 下午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("编号 : " + array[0]);        System.out.println("用户名 : " + 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("编号 : " + array[0] + "\t");            System.out.println("用户名 : " + 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("用户名：" + 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("用户名：" + map.get("username"));            System.out.println("薪水：" + 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("共有" + sum + "人");    }}

Use of Dbubtil