DAO and related implementation classes

Overview: Data Access Object is a class and interface for accessing data information, including CRUD(Create, regression, Update, Delete) of data, without any business-related information. Sometimes called: BaseDAO.

Function: in order to realize the modularization of functions, it is more conducive to code maintenance and upgrading.

 

Database connection pool

Traditional patterns in developing database based programs	1. Establish database connection in the main program (such as servlet and beans) 2. Perform sql operations 3. Disconnect the database
	Existing problems	1. The database connection resources have not been well reused 2. For each database connection, disconnect it after use 3. This development cannot control the number of connection objects created
basic thought	Establish a "buffer pool" for database connections. Put a certain number of connections in the buffer pool in advance. When you need to establish a database connection, just take one out of the "buffer pool" and put it back after use
	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
	Connection pool minimum database connections	When the database connection pool is initialized, a certain number of database connections will be created and put into the connection pool. No matter whether these database connections are used or not, the connection pool will always ensure that there are at least so many connections
	Connection pool maximum database connections	It limits the maximum number of connections that can be occupied by the connection pool. When the number of connections requested by the application from the connection pool exceeds the maximum number of connections, these requests will be added to the waiting queue
working principle

Benefits:

Resource reuse
Because database connections can be reused, frequent creation is avoided and a large amount of performance overhead caused by connections is released

On the basis of reducing system consumption, on the other hand, it also increases the stability of system operation environment

Faster system response
During the initialization of the database connection pool, several database connections are often created and placed in the connection pool for standby

At this time, the initialization of the connection has been completed. For service request processing, the existing available connections are directly utilized

It avoids the time overhead of database connection initialization and release process, so as to reduce the response time of the system

New means of resource allocation
For systems where multiple applications share the same database, the configuration of database connection pool can be used at the application layer

The maximum number of available database connections of an application is limited to avoid an application monopolizing all database resources

Unified connection management

Avoid database connection leakage

In the more perfect implementation of database connection pool, it can be set according to the occupation timeout in advance

Forcibly reclaim occupied connections, thus avoiding possible resource leakage in conventional database connection operations

Classification:

C3P0, dbcp, Druid (recommended)

Code implementation:

Druid database connection pool

Configuration file under src: Druid properties

url=jdbc:mysql:///test
username=root
password=root
driverClassName=com.mysql.jdbc.Driver

 @Test
    public void getConnection() throws Exception {
        Properties pros = new Properties();
 
        InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
        pros.load(is);
 
        DataSource source = DruidDataSourceFactory.createDataSource(pros);
        Connection conn = source.getConnection();
        System.out.println(conn);
}

Complete use of JDBC

  //Complete use of JDBC
    public void testUpdateWithTx() {

        Connection conn = null;
        try {
            //1. Get connection(
            //① Handwritten connection: JDBC utils getConnection();
            //② Use database connection pool: C3P0;DBCP;Druid
            //2. Perform a series of CRUD operations on the data table
            //① Use PreparedStatement to realize general addition, deletion, modification and query operations (version 1.0 \ version 2.0)
            //version2. Public void update (connection Conn, string SQL, object... Args) {}
            //version2. Query public < T > t getInstance (connection Conn, class < T > clazz, string SQL, object... Args) {}
            //② Use the QueryRunner class provided in the jar package provided by dbutils

            //Submit data
            conn.commit();


        } catch (Exception e) {
            e.printStackTrace();
            try {
                //Undo Data 
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }

        }finally{
            //3. Close the connection
            //① JDBCUtils.closeResource();
            //② Using the dbutils class provided in the jar package provided by dbutils provides the related operation of closing

        }
    }