20160410javaweb JDBC---dbutils framework

dbutils

1.DbUtils
Tool Class

2.QueryRunner--Two lines of code to fix additions and deletions

(1) Queryrunner ()--use this set of methods when you need to control transactions
Intupdate (Connection conn, String SQL)
Execute an SQL INSERT, UPDATE, or DELETE query without replacement parameters.
Intupdate (Connection conn, String sql, Object ... params)
Execute an SQL INSERT, UPDATE, or DELETE query.
Intupdate (Connection conn, String sql, Object param)
Execute an SQL INSERT, UPDATE, or DELETE query with a single replacement parameter.

<T> T Query (Connection conn, String sql, resultsethandler<t> rsh)
Execute an SQL SELECT query without any replacement parameters.
<T> T Query (Connection conn, String sql, resultsethandler<t> rsh, Object ... params)
Execute an SQL SELECT query with replacement parameters.

(2) Queryrunner (DataSource DS)--No need to control transactions with this set of methods
Intupdate (String sql)
Executes the given INSERT, UPDATE, or DELETE SQL statement without any replacement parameters.
Intupdate (String sql, Object ... params)
Executes the given INSERT, UPDATE, or DELETE SQL statement.
Intupdate (String sql, Object param)
Executes the given INSERT, UPDATE, or DELETE SQL statement with a single replacement parameter.

<T> T query (String sql, resultsethandler<t> rsh)
Executes the given SELECT SQL without any replacement parameters.
<T> T query (String sql, resultsethandler<t> rsh, Object ... params)
Executes the given SELECT SQL query and returns a result object.

 Packagecom.dzq.dbutils;Importjava.sql.SQLException;ImportOrg.apache.commons.dbutils.QueryRunner;Importorg.junit.Test;ImportCom.mchange.v2.c3p0.ComboPooledDataSource; Public classdbutilsupdate {@Test Public voidAdd ()throwssqlexception{Queryrunner Runner=NewQueryrunner (NewCombopooleddatasource ()); Runner.update ("Update account set money=?" Where Name=? ", 888," a "); }}

 Packagecom.dzq.dbutils;ImportJava.sql.ResultSet;Importjava.sql.SQLException;Importjava.util.ArrayList;Importjava.util.List;ImportOrg.apache.commons.dbutils.QueryRunner;ImportOrg.apache.commons.dbutils.ResultSetHandler;Importorg.junit.Test;ImportCom.dzq.domain.Account;ImportCom.mchange.v2.c3p0.ComboPooledDataSource; Public classDbutilsquery {@Test Public voidFind ()throwssqlexception{Queryrunner Runner=NewQueryrunner (NewCombopooleddatasource ()); String SQL= "SELECT * from account where money>?"; List<Account> list= runner.query (SQL,NewResultsethandler<list<account>>() {@Override PublicList<account> handle (ResultSet RS)throwsSQLException {List<Account> list =NewArraylist<account>();  while(Rs.next ()) {Account ACC=NewAccount (); Acc.setid (Rs.getint ("id")); Acc.setname (Rs.getstring ("Name")); Acc.setmoney (Rs.getdouble ("Money"));            List.add (ACC); }            returnlist; }           },500);   SYSTEM.OUT.PRINTLN (list); }}

3.ResultSetHandler Implementation Class

Arrayhandler: Turns the first row of data in the result set into an array of objects.
Arraylisthandler: Each row of data in the result set is converted into an array of objects and then stored in the list.
!!!! Beanhandler: Encapsulates 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 the list.
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.
Maplisthandler: Each row of data in the result set is encapsulated in a map and then stored in the list
Columnlisthandler: The data from a column in the result set is stored in the list.
Keyedhandler (name): Encapsulates each row of data in the result set into a map (list<map>), and then saves the map to a map with the specified column key.
!!!!! Scalarhandler: Gets the value of the first row of data specified column in the result set, commonly used for single-value queries

Example code:

 Packagecom.itheima.dbutils;Importjava.sql.SQLException;Importjava.util.List;ImportJava.util.Map;ImportOrg.apache.commons.dbutils.QueryRunner;ImportOrg.apache.commons.dbutils.handlers.ArrayHandler;ImportOrg.apache.commons.dbutils.handlers.ArrayListHandler;ImportOrg.apache.commons.dbutils.handlers.BeanHandler;ImportOrg.apache.commons.dbutils.handlers.BeanListHandler;ImportOrg.apache.commons.dbutils.handlers.ColumnListHandler;ImportOrg.apache.commons.dbutils.handlers.KeyedHandler;ImportOrg.apache.commons.dbutils.handlers.MapHandler;ImportOrg.apache.commons.dbutils.handlers.MapListHandler;ImportOrg.apache.commons.dbutils.handlers.ScalarHandler;Importorg.junit.Test;ImportCom.itheima.domain.Account;ImportCom.mchange.v2.c3p0.ComboPooledDataSource; Public classRshanlderdemo {//Scalarhandler: Gets the value of the first row of data specified column in the result set, commonly used for single-value queries@Test Public voidTES9 ()throwssqlexception{Queryrunner Runner=NewQueryrunner (NewCombopooleddatasource ()); Long Count= (Long) runner.query ("SELECT count (*) from account",NewScalarhandler ());    System.out.println (count); }        //Keyedhandler (name): Encapsulates each row of data in the result set into a map (list<map>), and then saves the map to a map with the specified column key. @Test Public voidTES8 ()throwssqlexception{Queryrunner Runner=NewQueryrunner (NewCombopooleddatasource ()); Map<object, map<string, object>> Map = Runner.query ("SELECT * from account where money>?",NewKeyedhandler ("id"), 500);    SYSTEM.OUT.PRINTLN (map); }    //Columnlisthandler: The data from a column in the result set is stored in the list. @Test Public voidTes7 ()throwssqlexception{Queryrunner Runner=NewQueryrunner (NewCombopooleddatasource ()); List<object>list = Runner.query ("SELECT * from account where money>?",NewColumnlisthandler (3), 500);    SYSTEM.OUT.PRINTLN (list); }    //Maplisthandler: Each row of data in the result set is encapsulated in a map and then stored in the list@Test Public voidTES6 ()throwssqlexception{Queryrunner Runner=NewQueryrunner (NewCombopooleddatasource ()); List<map<string, object>> list = Runner.query ("SELECT * from account where money>?",NewMaplisthandler (), 500);    SYSTEM.OUT.PRINTLN (list); }        //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. @Test Public voidTES5 ()throwssqlexception{Queryrunner Runner=NewQueryrunner (NewCombopooleddatasource ()); Map<string, object> map = Runner.query ("SELECT * from account where money>?",NewMaphandler (), 500);    SYSTEM.OUT.PRINTLN (map); }        //Beanlisthandler: Each row of data in the result set is encapsulated in a corresponding JavaBean instance and stored in the list. @Test Public voidTes4 ()throwssqlexception{Queryrunner Runner=NewQueryrunner (NewCombopooleddatasource ()); List<account>list = Runner.query ("SELECT * from account where money>?",NewBeanlisthandler<account> (account.class), 500);    SYSTEM.OUT.PRINTLN (list); }        //Beanhandler: Encapsulates the first row of data in the result set into a corresponding JavaBean instance. @Test Public voidTes3 ()throwssqlexception{Queryrunner Runner=NewQueryrunner (NewCombopooleddatasource ()); Account ACC= Runner.query ("SELECT * from account where money>?",NewBeanhandler<account> (account.class), 500);    SYSTEM.OUT.PRINTLN (ACC); }    //Arraylisthandler: Each row of data in the result set is converted into an array of objects and then stored in the list. @Test Public voidTes2 ()throwssqlexception{Queryrunner Runner=NewQueryrunner (NewCombopooleddatasource ()); List<Object[]> list = Runner.query ("SELECT * from account where money>?",NewArraylisthandler (), 500);    SYSTEM.OUT.PRINTLN (list); }        //Arrayhandler: Turns the first row of data in the result set into an array of objects. @Test Public voidTest1 ()throwssqlexception{Queryrunner Runner=NewQueryrunner (NewCombopooleddatasource ()); Object[] Objs= Runner.query ("SELECT * from account where money>?",NewArrayhandler (), 500);    System.out.println (OBJS); }}

20160410javaweb JDBC---dbutils framework