The 04javaweb-06dbutils of Java combat

First, DBUTILS1. Dbutils's introduction

Commons Dbutils is an open Source Tool class library provided by the Apache Organization for simple JDBC encapsulation, which simplifies the development of JDBC applications without compromising program performance.

2. An overview of the API in Dbutils

Queryrunner class: The core class of SQL operations this object encapsulates the method of manipulating data inside

Resultsethandler interface: A feature that represents the mapping of result sets to Entities

Why interfaces: Specific encapsulation rules do not know to be self-operating according to the entity business

Dbutils class: A method that is primarily related to the submission of closed transactions for a database

3. Queryrunner's API explanation

Note: The jar package that was imported before using Dbutils

1) database Driver

2) Jar for connection pool

3) Dbutils jar

(1) How to create Queryrunner

There are two ways to create Queryrunner

The first type of non-parametric: New Queryrunner ();

Typically used in conjunction with transactional operations

The second type is: New Queryrunner (DataSource);

Use when transaction control is not required under normal circumstances

Note: The connection object must be used to manipulate SQL in the program, and the constructor of the data source parameter automatically obtains a connection object manipulation database from the data source. The parameterless construction method does not provide connection object information when the Queryrunner is created, and connection is passed when specifying the method to manipulate SQL.

Methods in the Queryrunner

Batch

Update

Query

(2) operation to update data (single SQL execution)

Update operation:

The Update method has three parameters that form multiple methods by combining these three parameters

Connection: The connection object for the database is used with the parameterless new Queryrunner ()

SQL: Required to execute SQL statement

Params: The actual parameters of the ghost placeholder

Which methods can be composed by the above parameters

Update (CONNECTION,SQL,PARAMS);

Update (CONNECTION,SQL);

Update (SQL,PARAMS);

Update (SQL);

1     @Test2      Public voidTest1 ()throwssqlexception{3         //Requirement: To modify the category of id=100 products in the product table for life4         //1. Create Queryrunner5Queryrunner runner =NewQueryrunner (Datasourceutils.getdatasource ());6         //2. Execute SQL7String sql = "Update products set category=?" where id=? ";8         intUpdate = runner.update (sql, "Life", "100");9 System.out.println (update);Ten}

(3) Update data manipulation (bulk SQL execution)

Batch Operations---Execute multiple statements with the same SQL structure at the same time

Parameters:

Connection: The connection object for the database is used with the parameterless new Queryrunner ()

SQL: Required to execute SQL statement

Params: two-dimensional array

One-dimensional representation is the specified SQL

Two-dimensional actual parameters that represent placeholders in SQL

For example:

Sql:insert into products values (?,?,?);

Param

{

{500,ios Primer, 15},

{600,c# Primer, 28},

{700,ruby Primer, 45},

{800,hadoop Primer, 99}

}

Insert statement executes several times: 4 times each execution of 3 parameters

Requirements: BULK INSERT 2 data into the Products table

1 //1. Create a Queryrunner object2Queryrunner runner =NewQueryrunner (Datasourceutils.getdatasource ());3         //2. Execute SQL4String sql = "INSERT into products values (?,?,?,?,?,?,?)";5         //object[][] params = new object[2][7];6         //"params[0][0]=";7Object[][] params = {8{"$", "Introduction to Ruby", 89, "sensibility", "bookcover/105.jpg", "Good book, Good book"},9{"O", "Introduction to Hadoop", 89, "sensibility", "bookcover/106.jpg", "Good book, Good book"}Ten         }; One         int[] Batch =runner.batch (SQL, params); A         //{ * *} -          for(intI:batch) { - System.out.println (i); the}

(4) Query operation queries

The Query method executes the SELECT statement

Parameters:

Connection: The connection object for the database is used with the parameterless new Queryrunner ()

SQL: Required to execute SQL statement

Params: The actual parameters of the ghost placeholder

RESULTSETHANDELR: performance of result set encapsulation

1 @Test2      Public voidTest1 ()throwssqlexception{3         //1. Create Queryrunner4Queryrunner runner =NewQueryrunner (Datasourceutils.getdatasource ());5         //2. Execute SQL statements6String sql = "SELECT * FROM Products";7list<product> query = runner.query (SQL,NewResultsethandler<list<product>>() {8 @Override9              PublicList<product> handle (ResultSet RS)throwsSQLException {Ten                 //write the logic of your own encapsulation Onelist<product> list =NewArraylist<product>(); A                  while(Rs.next ()) { -Product Pro =NewProduct (); -Pro.setid (rs.getstring ("id")); thePro.setname (rs.getstring ("name")); -Pro.setcategory (rs.getstring ("category")); -Pro.setdescription (rs.getstring ("description")); -Pro.setimgurl (rs.getstring ("Imgurl")); +Pro.setpnum (Rs.getint ("Pnum")); -Pro.setprice (rs.getdouble ("Price")); + List.add (pro); A                 } at                 returnlist; -             } -  -         }); -  - System.out.println (query); in}

Second, the implementation of the Resultsethandler interface class

Beanlisthandler: Return is list<t>

Beanhandler: The return is T

Maplisthandler: Return is list<map<string,object>>

Maphandler: Return is map<string,object>

Scalarhandler: Returns an object that obtains a value that is the value of the upper-left corner of the virtual table isolated, commonly used in aggregate function queries

---------------------------------------------------------------

Arraylisthandler:list<object[]>

Arrayhandler:object[]

Columnlisthandler:list<object> Query is a collection of data for a column of a structure

Keyedhandler: Return value map<object,map<string,object>>

{

200={id=200, category= Life, price=78.0, pnum=120, description= Good book, Name=android Primer Classic, imgurl=bookcover/102.jpg},

100={id=100, category= Life, price=88.0, pnum=100, description= Good book, Name=java Primer Classic, imgurl=bookcover/101.jpg}

}

Dbutils Tool Classes: Tool classes, including transaction commit, rollback, and resource close tool methods, use not much

The 04javaweb-06dbutils of Java combat