Description of DBUtils and connection pool, and description of DBUtils connection pool

Description of DBUtils and connection pool, and description of DBUtils connection pool

### 01DButils tool class: three core classes

* A: The DButils tool class introduces three core classes.

* A: overview

* DBUtils is a practical tool for database operations in java programming. It is small, simple, and practical.

* DBUtils encapsulates JDBC operations, which simplifies JDBC operations and allows you to write less code.

* DBUtils is the simplified development kit for JDBC. The DBUtils tool works properly only when the project import commons-dbutils-1.6.jar is required.

* B: three core functions of Dbutils

* QueryRunner provides APIs for SQL statement operations.

* Update (Connection conn, String SQL, Object... params) is used to add, delete, and update table data.

* Query (Connection conn, String SQL, ResultSetHandler Rsh, Object... params), used to query table data

* The ResultSetHandler interface is used to define how the result set is encapsulated after the select Operation.

* DbUtils class, which is a tool class that defines how to close resources and transactions

### 04QueryRunner 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 ();}/** defines the method and uses the QueryRunner class method to update the data table, add data */public static void insert () throws SQLException {// create QueryRunner Class Object QueryRunner qr = new QueryRunner (); String SQL = "INSERT INTO sort (sname, sprice, sdesc) VALUES (?,?,?) "; // Set three? The actual parameters of the placeholder are written in the array Object [] params = {"Sporting Goods", 289.32, "purchasing Sporting Goods "}; // call the QueryRunner class method update to execute the SQL statement int row = qr. update (con, SQL, params); System. out. println (row); DbUtils. closeQuietly (con );}}

### Update and modify data in the 05QueryRunner class * A: QueryRunner class update and modify data * a: Case code: public class QueryRunnerDemo {private static Connection con = JDBCUtilsConfig. getConnection (); public static void main (String [] args) throws SQLException {update ();}/** definition method, use the QueryRunner class method update to modify data in the data table */public static void update () throws SQLException {// create the QueryRunner Class Object QueryRunner qr = new QueryRunner (); // write the SQL statement for data modification String SQL = "UPDATE sort SET sname = ?, Sprice = ?, Sdesc =? WHERE sid =? "; // Define the Object array, storage? In the parameter Object [] params = {"Flowers", 100.88, "Valentine's Day Rose", 4}; // call the QueryRunner method updateint row = qr. update (con, SQL, params); System. out. println (row); DbUtils. closeQuietly (con );}}

### 06QueryRunner class: delete data deletion * A: QueryRunner class: delete data deletion * a: Case code: public class QueryRunnerDemo {private static Connection con = JDBCUtilsConfig. getConnection (); public static void main (String [] args) throws SQLException {delete ();}/** definition method, use the QueryRunner class method delete to delete data from the data table */public static void delete () throws SQLException {// create the QueryRunner Class Object QueryRunner qr = new QueryRunner (); // write the deleted SQL statement String SQL = "D Elete from sort WHERE sid =? "; // Call the QueryRunner method updateint row = qr. update (con, SQL, 8); System. out. println (row);/** determines whether the insert, update, and delete operations are successful * determines whether the return value row * if (row> 0) is successfully executed */DbUtils. closeQuietly (con );}}

### How to process the result set of the 08DBUtils Tool

* A: how to process the result set of the DBUtils Tool

* A: QueryRunner performs query operations.

* Query (Connection conn, String SQL, ResultSetHandler Rsh, Object... params), used to query table data

* B: ResultSetHandler result set processing class

* ArrayHandler encapsulates the first record in the result set into an Object [] array. Each element in the array is the value of each field in the record.

* ArrayListHandler encapsulates each record in the result set into an Object [] array and encapsulates these arrays in the List set.

* BeanHandler encapsulates the first record in the result set into a specified javaBean.

* BeanListHandler encapsulates each record in the result set into a specified javaBean and encapsulates these javaBean in the List set.

* ColumnListHandler encapsulates the Field Values of the specified columns in the result set into a List set.

* ScalarHandler is used for single data. For example, select count (*) from table operation.

* MapHandler encapsulates the first row of the result set into the Map set, and the Key column name and Value column data

* MapListHandler encapsulates the first row of the result set into the Map set, and stores the Key column name and Value in the column data. The Map set is stored in the List set.

### 18 connection pool Introduction

* A: Connection Pool Introduction

* A: Connection Pool Introduction

* In fact, it is used to store connected pools (containers)

* In development, "getting connections" or "releasing resources" are two processes that consume very much system resources.

* To solve such performance problems, we usually use the Connection pool technology to share the Connection.

* In this way, we do not need to create and release connections Every time. These operations are handed over to the connection pool.

### Use of 22BasicDataSource class * A: Use of the BasicDataSource class * a: Case code/** in the jar package of the Connection Pool, define A class BasicDataSource * to implement the standard interface javax for class data sources. 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. dbcpBasicDataSource dataSource = new BasicDataSource (); // the four most basic information for connecting to the database. The dataSource is set through the object method setXXX. setDriverClassName ("com. mysql. jdbc. driver "); dataSource. setUrl ("jdbc: mysql: // localhost: 3306/mybase"); dataSource. setUsername ("root"); dataSource. setPassword ("123"); try {// call the object method getConnection to obtain the database Connection con = dataSource. getConnection (); System. out. println (con);} catch (SQLException ex) {// System. out. println (ex); ex. printStackTrace (); throw new RuntimeException ("database connection failed ");}}}

### 24 database connection pool tool class * A: database connection pool tool class * a: Case code/** use DBCP to implement database connection pool * connection pool configuration, custom class, * The most basic four items are complete * for other database connection pool configurations, custom */import javax. SQL. dataSource; import org. apache. commons. dbcp. basicDataSource; public class JDBCUtils {// create the BasicDataSource class Object private static BasicDataSource datasource = new BasicDataSource (); // static code block, which is the configuration in the object BasicDataSource, 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"); // Number of connections in the object connection pool, optional datasource. setInitialSize (10); // Number of initialized connections datasource. setMaxActive (8); // maximum number of connections datasource. setMaxIdle (5); // maximum number of idle data sources. setMinIdle (1); // minimum idle} // defines the static method and returns the object of the BasicDataSource class public static DataSource getDataSource () {return datasource ;}} ### 25 tool-type testing * A: tool-type testing * a: Case code/** the tool class written in the test, * provides a data source of the DataSource interface * QueryRunner class constructor. After receiving the implementation class * of the DataSource interface, call the update, query, no need to pass their 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 () ;}// defines two methods to add data tables, data Table query // QueryRunner Class Object, written in 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
 
  
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") ;}// add public static void insert () to the data table () {String SQL = "INSERT INTO sort (sname, sprice, sdesc) VALUES (?,?,?) "; Object [] params = {" Fruit ", 100.12," walnut just listed "}; try {int row = qr. update (SQL, params); System. out. println (row);} catch (SQLException ex) {throw new RuntimeException ("Data addition failed ");}}}