Dbutils and connection pooling

Introduction of today's content
1, Dbutils
2. Connection Pool
# # #01DButils工具类的介绍个三个核心类
* Introduction of the A:dbutils Tool class three core classes
* A: Overview
* Dbutils is a database operation utility in Java programming, small and simple and practical.
* Dbutils encapsulates the operation of JDBC, simplifies JDBC operations, and can write less code.
* Dbutils is the simplified Developer Toolkit for JDBC. You need the project import Commons-dbutils-1.6.jar to be able to use the Dbutils tool normally.
* B:dbutils three core functions of the introduction
* The API for SQL statement operations is provided in Queryrunner.
* UPDATE (Connection conn, String sql, Object ... params), used to complete table data additions, deletions, update operations
* Query (Connection conn, String sql, resultsethandler<t> rsh, Object ... params) for querying operations to complete table data
* Resultsethandler interface, which defines how the result set is encapsulated after a select operation.
* Dbutils class, which is a tool class that defines methods for shutting down resources and transaction processing

# # #02事务的简单介绍 (This knowledge point simple understanding, difficult, the Employment class meeting detailed explanation)
* A: A brief introduction to the business
* A: See day32/day32_source/affairs. JGP

# # #03QueryRunner类的update方法介绍
* Introduction to the Update method of the A:queryrunner class
* A: Method introduction
* UPDATE (Connection conn, String sql, Object ... params), used to complete table data additions, deletions, update operations
* Use the Queryrunner class to implement an insert delete update to a data table
* Call the method of the Queryrunner class update (Connection con,string sql,object...param)
* Object...param variable parameter, Object type, SQL statement will appear? placeholder
* Database Connection object, custom tool class pass


# # #04QueryRunner类实现insert添加数据
* A:queryrunner class implements insert to add data
* A: Case code
public class Queryrunnerdemo {
private static Connection con = Jdbcutilsconfig.getconnection ();
public static void Main (string[] args) throws SQLException {
Insert ();
}
/*
* Define the method, use the method of the Queryrunner class to update the data table, add the data
*/
public static void Insert () throws sqlexception{
Creating a Queryrunner Class object
Queryrunner qr = new Queryrunner ();
String sql = "INSERT into sort (sname,sprice,sdesc) VALUES (?,?,?)";
Write the actual parameters of the three placeholders in the array
Object[] params = {"Sporting goods", 289.32, "buy Sporting Goods"};
Call the method of the Queryrunner class update execution SQL statement
int row = Qr.update (con, SQL, params);
System.out.println (row);
Dbutils.closequietly (con);
}
}

# # #05QueryRunner类实现update修改数据
* A:queryrunner class implements update modification data
* A: Case code
public class Queryrunnerdemo {
private static Connection con = Jdbcutilsconfig.getconnection ();
public static void Main (string[] args) throws SQLException {
Update ();
}
/*
* Define methods, use the method of the Queryrunner class update to modify data table data
*/
public static void Update () throws sqlexception{
Creating a Queryrunner Class object
Queryrunner qr = new Queryrunner ();
Write SQL statements that modify data
String sql = "UPDATE sort SET sname=?,sprice=?,sdesc=?" WHERE sid=? ";
Defines a parameter in an object array, stored?
Object[] params = {"Flower", 100.88, "Valentine's Day Rose", 4};
Call the Queryrunner method update
int row = Qr.update (con, SQL, params);
System.out.println (row);
Dbutils.closequietly (con);
}
}

# # #06QueryRunner类实现delete删除数据
* A:queryrunner class implements delete delete data
* A: Case code
public class Queryrunnerdemo {
private static Connection con = Jdbcutilsconfig.getconnection ();
public static void Main (string[] args) throws SQLException {
Delete ();
}
/*
* Define the method, use the method of the Queryrunner class to delete the data table data
*/
public static void Delete () throws sqlexception{
Creating a Queryrunner Class object
Queryrunner qr = new Queryrunner ();
Write the deleted SQL statement
String sql = "DELETE from sort WHERE sid=?";
Call the Queryrunner method update
int row = Qr.update (con, SQL, 8);
System.out.println (row);
/*
* Determine if insert,update,delete execution is successful
* Judging the return value row
* IF (row>0) successful execution
*/
Dbutils.closequietly (con);
}
}

# # #07JavaBean类
* A:javabean Class
* A: Concept
* JavaBean is a class that is used to encapsulate data in development. Has the following characteristics
1. Need to Implement Interface: Java.io.Serializable, usually implement interface this step is omitted, does not affect the program.
2. Provide private field: private Type field name;
3. Provide the Getter/setter method:
4. Provide non-parametric construction

# # #08DBUtils工具类结果集处理的方式
* A:dbutils Tool class result set processing method
* A:queryrunner implementation of query operations
*query (Connection conn, String sql, resultsethandler<t> rsh, Object ... params), query operation to complete table data
* B:resultsethandler result set processing class
* Arrayhandler encapsulates the first record in the result set into a object[] array, and each element in the array is the value of each field in the record
* Arraylisthandler encapsulates each record in the result set into a object[] array, encapsulating the arrays in the list collection.
* Beanhandler encapsulates the first record in the result set into a specified javabean.
* Beanlisthandler encapsulates each record in the result set into the specified JavaBean, encapsulating these javabean in the list collection
* Columnlisthandler encapsulates the field value of the specified column in the result set into a list collection
* Scalarhandler It is used for single data. For example, select COUNT (*) from table operation.
* Maphandler encapsulates the first row of the result set into the map collection, Key column name, Value the column data
* Maplisthandler encapsulates the first row of the result set into the map collection, the Key column name, the Value of the column data, and the map collection stored in the list collection

# # #09QueryRunner类的方法query
* Method of A:queryrunner class query
* A:queryrunner Data query operation
* Call the Queryrunner class method query (Connection con,string sql,resultsethandler R, Object: Params
* Resultsethandler r result set processing mode, pass Resultsethandler interface implementation class
* Object: The? placeholder in the params SQL statement
* Note: The Query method returns a value that returns a T generic, a return value type, followed by the result set processing mode change
* B: Case code
public class QueryRunnerDemo1 {
private static Connection con = Jdbcutilsconfig.getconnection ();
}

# # #10结果集处理ArrayHandler
* A: Result set processing Arrayhandler
* Case Code
public class QueryRunnerDemo1 {
private static Connection con = Jdbcutilsconfig.getconnection ();
public static void Main (string[] args) throws sqlexception{
Arrayhandler ();
}
/*
* The first processing method of the result set, Arrayhandler
* Store the first row of the result set in an array of objects object[]
*/
public static void Arrayhandler () throws sqlexception{
Queryrunner qr = new Queryrunner ();
String sql = "SELECT * from Sort";
Call a method query to execute queries, pass connection objects, SQL statements, and the implementation class of the result set processing method
Returns an array of objects
Object[] result = Qr.query (con, SQL, New Arrayhandler ());
for (Object Obj:result) {
System.out.print (obj);
}
}
}

# # #11结果集处理ArrayListHandler
* A: Result set processing Arraylisthandler
* A: Case code
public class QueryRunnerDemo1 {
private static Connection con = Jdbcutilsconfig.getconnection ();
public static void Main (string[] args) throws sqlexception{
Arraylisthandler ();
}
/*
* Result set the second method of processing, Arraylisthandler
* Encapsulates each row of the result set into an array of objects, with many arrays of objects
* Object arrays are stored in the list collection
*/
public static void Arraylisthandler () throws sqlexception{
Queryrunner qr = new Queryrunner ();
String sql = "SELECT * from Sort";
Call the Query method, and the result set processes the parameters on the pass implementation class Arraylisthandler
method returns the value of each row is an array of objects stored in the list
list<object[]> result= qr.query (Con, SQL, New Arraylisthandler ());

Traversal of a collection
For (object[] objs:result) {
Iterating through an array of objects
for (Object Obj:objs) {
System.out.print (obj+ "");
}
System.out.println ();
}
}
}

# # #12结果集处理BeanHandler
* A: Result set processing Beanhandler
* A: Case code
public class QueryRunnerDemo1 {
Private static Connection con = Jdbcutilsconfig.getconnection ();
public static void Main (string[] args) throws sqlexception{
Beanhandler ();
}
/*
* result set the third processing method, Beanhandler
* Encapsulates the first row of data in the result set into a JavaBean object
* Note: the sort class must have an empty argument construct
/In case the data is encapsulated into a JavaBean object.
public static void Beanhandler () throws sqlexception{
Queryrunner qr = new Queryrunner ();
String sql = "SELECT * from Sort";
//Call method, pass result set implementation class Beanhandler
//beanhandler (class<t> type)
Sort s = qr.query (con, SQL, new Beanhandler <Sort> (Sort.class));
System.out.println (s);
}
}

# # #13结果集处理BeanListHandler
* A: Result set processing Beanlisthandler
* A: Case code
public class QueryRunnerDemo1 {
private static Connection con = Jdbcutilsconfig.getconnection ();
public static void Main (string[] args) throws sqlexception{
Beanlisthander ();
}
/*
* Result set fourth processing method, Beanlisthandler
* result set each row of data, encapsulate JavaBean object
* Multiple JavaBean objects, stored in the list collection
*/
Public static void Beanlisthander () throws sqlexception{
Queryrunner qr = new Queryrunner ();
String sql = "SELECT * from Sort";
//Call method query, pass result set processing implementation class Beanlisthandler
List<sort> List = Qr.query (con, SQL, new Beanlisthandler<sort > (sort.class));
for (Sort s:list) {
System.out.println (s);
}
}
}

# # #14结果集处理ColumnListHandler
* A: Result set processing Columnlisthandler
* A: Case code
public class QueryRunnerDemo1 {
private static Connection con = Jdbcutilsconfig.getconnection ();
public static void Main (string[] args) throws sqlexception{
Columnlisthandler ();
}
/*
* Result set fifth processing method, Columnlisthandler
* result set, data of specified column, stored to list collection
* list<object> different column data type
*/
public static void Columnlisthandler () throws sqlexception{
Queryrunner qr = new Queryrunner ();
String sql = "SELECT * from Sort";
//Call method query, pass result set implementation class Columnlisthandler
//Implement class construction method, use string column name
list<object> list = Qr.query (con, SQL, New Columnlisthandler<object> ("sname"));
for (Object obj:list) {
System.out.println (obj);
}
}
}

# # #15结果集处理ScalarHandler
* A: Result set processing Scalarhandler
* A: Case code
public class QueryRunnerDemo1 {
Private static Connection con = Jdbcutilsconfig.getconnection ();
public static void Main (string[] args) throws sqlexception{
Scalarhandler ();
}
/*
* Result set sixth processing method, Scalarhandler
* For query, only 1 results
*/
public static void Scalarhandler () throws sqlexception{
Queryrunner qr = new Queryrunner ();
String sql = "Select COUNT (*) from sort";
//Call method query, pass result set processing implementation class Scalarhandler
Long Count = Qr.query (con, SQL, New scalarhandler<long> ());
System.out.println (count);
}
}

# # #16结果集处理MapHandler
* A: Result set processing Maphandler
* A: Case code
public class QueryRunnerDemo1 {
private static Connection con = Jdbcutilsconfig.getconnection ();
public static void Main (string[] args) throws sqlexception{
Maphandler ();
}
/*
* Seventh processing method of result set, Maphandler
* Encapsulates the first row of data in the result set into the map collection
* map< key, value > key: Column Name value: This column of data
*/
public static void Maphandler () throws sqlexception{
Queryrunner qr = new Queryrunner ();
String sql = "SELECT * from Sort";
Call method query, pass result set implementation class Maphandler
Return value: Map collection, map interface implementation class, generic
map<string,object> map = Qr.query (con, SQL, New Maphandler ());
Traverse the Map Collection
For (String Key:map.keySet ()) {
System.out.println (key+ "..." +map.get (key));
}
}
}

# # #17结果集处理MapListHandler
* A: Result set processing Maplisthandlerr
* A: Case code
public class QueryRunnerDemo1 {
private static Connection con = Jdbcutilsconfig.getconnection ();
public static void Main (string[] args) throws sqlexception{
Maplisthandler ();
}
/*
* Eighth processing method of result set, Maplisthandler
* Store each row of the result set in the Map collection, key: Column name, value: Data
* Map collection too many, stored in the list collection
*/
public static void Maplisthandler () throws sqlexception{
Queryrunner qr = new Queryrunner ();
String sql = "SELECT * from Sort";
Call method query, pass result set implementation class Maplisthandler
The return Value list collection, which stores the map collection
list<map<string,object>> list = Qr.query (con, SQL, New Maplisthandler ());
Iterating through the collection list
for (map<string,object> map:list) {
For (String Key:map.keySet ()) {
System.out.print (key+ "..." +map.get (key));
}
System.out.println ();
}

}
}

# # #18连接池介绍
* A: Connection Pool Introduction
* A: Connection pool introduction
* is actually the pool (container) that holds the connection
* "Get Connected" or "release resources" in development is two processes that consume system resources very much
* In order to solve this kind of performance problem, we usually use the connection pooling technology to share the connection connection.
* So we don't have to create a connection, release the connection each time, these operations are given to the connection pool

# # #19连接池概念规范和DataSource接口
* A: Connection pool concept specification and DataSource interface
* A: Connection pool concept specification
* Use pools to manage connection, so you can reuse connection.
* Instead of creating connection yourself, get connection objects through the pool
* After using connection, call connection's close () method will not really close connection, but instead connection "return" to the pool
* Connection pooling technology can complete reuse of connection objects
* B:datasource Interface
* Java provides a common interface for database connection pooling: Javax.sql.DataSource
* Each vendor needs to have its own connection pool implement this interface. This allows the application to easily switch between different vendors ' connection pools
* Common connection pools: DBCP, c3p0
# # #20DBCP连接池介绍
* A:DBCP Connection Pool Introduction
* A:DBCP Connection Pool Introduction
* DBCP is also an open source connection pool, one of the Apache common members, also more common in enterprise development, Tomcat built-in connection pool
* Tomcat Server Brief Introduction

# # #21导入jar包
* A: Import jar Package
* A:jar Package Introduction
* Mysql-connector-java-5.1.37-bin.jar: Database driver
* Commons-dbutils-1.6.jar: Provide Queryrunner class to facilitate the operation of the increase and deletion check
* Commons-dbcp-1.4.jar:
* Commons-pool-1.5.6.jar: Providing efficient database connection pooling technology
* B: Import jar Package
* Create folder Lib under the project root path
* Copy above jar package, select Copy Jar/Right/build path/add to Build Path

# # #22BasicDataSource类的使用
* Use of the A:basicdatasource class
* A: Case code
/*
* Connection pool jar package, define a class Basicdatasource
* Implementing a canonical interface for a class data source Javax.sql.DataSource
*/
public class Datasorucedemo {
public static void Main (string[] args) {
Create an implementation class object for the DataSource interface
//implementation class, ORG.APACHE.COMMONS.DBCP
Basicdatasource dataSource = new Basicdatasource ();
Connect the 4 most basic information of the database, through the object method setxxx set in
Datasource.setdriverclassname ("Com.mysql.jdbc.Driver");
Datasource.seturl ("Jdbc:mysql://localhost:3306/mybase");
Datasource.setusername ("root");
Datasource.setpassword ("123");

try{
//Call object method getconnection get connection to database
Connection con = datasource.getconnection ();
System.out.println (con);
}catch (SQLException ex) {
//system.out.println (ex);
Ex.printstacktrace ();
Throw new RuntimeException ("Database connection failed");
}
}
}

# # #23BasicDataSource类的常见配置
* Common configuration of the A:basicdatasource class
* A: Common configuration
Classification attribute Description
Required Items
Driverclassname Database driver name
Address of the URL database
Username User Name
Password Password
Base item (extension)
Maxactive Maximum number of connections
Minidle Minimum Idle connection
Maxidle Maximum idle connection
InitialSize Initializing a connection

# # #24实现数据库连接池工具类
* A: Implementing the Database connection pooling tool class
* A: Case code
/*
* Use DBCP to implement database connection pooling
* Connection pool configuration, custom classes,
* Most basic four complete
* For additional configuration of database connection pool, customize
*/

Import Javax.sql.DataSource;

Import Org.apache.commons.dbcp.BasicDataSource;
public class jdbcutils{
Create a Basicdatasource class object
private static Basicdatasource DataSource = new Basicdatasource ();

Static code block, object Basicdatasource configuration in object, custom
static{
Database connection information, required
Datasource.setdriverclassname ("Com.mysql.jdbc.Driver");
Datasource.seturl ("Jdbc:mysql://localhost:3306/day33_user");
Datasource.setusername ("root");
Datasource.setpassword ("123");
The number of connections in the object connection pool configuration, optional
Datasource.setinitialsize (10);//number of connections initialized
Datasource.setmaxactive (8);//maximum number of connections
Datasource.setmaxidle (5);//Maximum idle number
Datasource.setminidle (1);//Minimum Idle
}


Defines a static method that returns an object of the Basicdatasource class
public static DataSource Getdatasource () {
return datasource;
}
}

# # #25工具类的测试
* A: Test of Tool class
* A: Case code
/*
* Test the written tool class,
* Provides a data source for a DataSource interface
* Queryrunner class construction method, receive DataSource interface implementation class
* Back, call method Update,query, no need to pass them connection connection object
*/

Import java.sql.SQLException;
Import java.util.List;

Import Org.apache.commons.dbutils.QueryRunner;
Import Org.apache.commons.dbutils.handlers.ArrayListHandler;

Import Cn.itcast.jdbcutils.JDBCUtils;
public class queryrunnerdemo{
public static void Main (string[] args) {
Select ();
}
Define 2 methods for adding data tables, querying data tables
Queryrunner class object, written at the class member location
private static Queryrunner QR = new Queryrunner (Jdbcutils.getdatasource ());

Data Table Query
public static void Select () {
String sql = "SELECT * from Sort";
try{
list<object[]> list = qr.query (sql, New Arraylisthandler ());
For (object[] objs:list) {
for (Object Obj:objs) {
System.out.print (obj+ "\ t");
}
System.out.println ();
}
}catch (SQLException ex) {
throw new RuntimeException ("Data query failed");
}
}

Data Table Add data
public static void Insert () {
String sql = "INSERT into sort (sname,sprice,sdesc) VALUES (?,?,?)";
Object[] params = {"Fruit", 100.12, "Just Listed Walnut"};
try{
int row = qr.update (sql, params);
System.out.println (row);
}catch (SQLException ex) {
throw new RuntimeException ("Data addition failed");
}
}

}

# # #26总结
* Summarize today's knowledge points.

Dbutils and connection pooling