1, Database connection pool

1. What is a database connection pool

Database connection pool is responsible for allocating, managing and releasing database connections. It allows applications to reuse an existing database connection instead of re establishing one; Release the database connection whose idle time exceeds the maximum idle time to avoid database connection omission caused by not releasing the database connection. This technology can significantly improve the performance of database operation.
principle
The basic idea of connection pool is to store the database connection as an object in memory during system initialization. When users need to access the database, they do not establish a new connection, but take out an established idle connection object from the connection pool. After use, the user does not close the connection, but puts the connection back into the connection pool for access by the next request. The establishment and disconnection of connections are managed by the connection pool itself. At the same time, you can also set the parameters of the connection pool to control the initial number of connections in the connection pool, the upper and lower limits of connections, as well as the maximum usage times and maximum idle time of each connection. You can also monitor the number and usage of database connections through its own management mechanism.

2. DataSource interface

As an alternative to the DriverManager tool, the DataSource object is the preferred method for obtaining connections. Objects that implement the DataSource interface are usually registered in a naming service based on the JavaTM Naming and Directory Interface (JNDI) API.

DataSource The interface is implemented by the driver vendor. There are three types of implementations:

Basic implementation - Generate standard Connection object
 Connection pool implementation - Generate connections that automatically participate in the connection pool Connection Object. This implementation works with the middle tier connection pool manager.
Distributed transaction implementation - Generate a Connection Object, which can be used for distributed transactions and always participates in the connection pool in most cases. This implementation is used with the middle tier transaction manager and, in most cases, always with the connection pool manager.

3.BDCP data source

DBCP depends on the Commons pool object pool, so commons DBCP is required jar , commons-pool. jar
The BasicDataSource of DBCP provides the close() method, so you need to specify the destruction method = "close" in the XML configuration file so that Spring can close the data source normally.

2, BDUtile tool

1. Introduction to dbutils

In order to use JDBC more simply and conveniently, Apache organization provides DButils tool, which is a database operation component, which realizes the simple encapsulation of JDBC and simplifies the operation of JDBC without affecting the performance. The latest version should be version 1.7

2. Introduction to queryrunner

QueryRunner The use of classes is greatly simplified SQL Statement, and ResultSetHandler The combination can complete various operations of the database, QueryRunner Class provides a constructor with parameters that javax.sql.DataSource Pass in parameters to QueryRunner Obtained from the construction method of Connection Object, and provides different methods for different database operations.
query(String sql ，ResultSetHandler rsh， Object ... params)

Query method. The second parameter here is ResultSetHandler Interface to query the result information and return JavaBean
，Generics are Bean Class, where you need to establish an anonymous implementation class of the interface, and override handle And return Bean Object. I'll probably know this first. I'll explain it later when I look at the code,
there params Is a variable parameter, according to SQL Set parameters according to the specific situation of the statement.

update(String sql, Object ... params)

Use the same method as deleting, inserting and updating database information params Is a variable parameter.

3.ResultSetHandler interface

The ResultSetHandler interface is used to process the resultset result set. It can convert the data in the result set into different forms. According to the data types of the results, ResultSetHandler provides several common implementation classes, as follows:
Beanhandler: the result set is sub packaged into a JavaBean and a piece of data is saved.
BeanListHandler: sub load each piece of data of the result into the JavaBean instance and store it in the List, that is, save multiple pieces of data

4. Basic use of queryrunner

Add data

public void testinsert() throws SQLException { ComboPooledDataSource
datasource = new ComboPooledDataSource(); QueryRunner queryrunner =
new QueryRunner(datasource); queryrunner.update("insert into balance
values(?,?)", "Bob",192); }

Update data

public void testupdate() throws SQLException { QueryRunner
queryrunner = new QueryRunner(new ComboPooledDataSource());
queryrunner.update("update balance set balance = ? where name = ?",
8160, "Bob"); }

Delete data

public void testdelete() throws SQLException { QueryRunner
queryrunner = new QueryRunner(new ComboPooledDataSource());
queryrunner.update("delete from balance where name=?", "Bob"); }