Database Connection Pool-C3P0 & Druid

(1) Database connection pool

Everything has its existence significance. When we first learn jdbc, we set up database connection objects and release them. But the establishment and closure of database connection consume resources very much, and frequent operation will make the performance low. Database connection pool can help us solve this problem. topic

(1) Concept

The database connection pool is responsible for allocating, managing and releasing database connections. It allows applications to reuse an existing database connection rather than re-establish it. It releases database connections with more than the maximum idle time to avoid database connection legacy caused by not releasing database connections. Leakage. This technology can significantly improve the performance of database operation.

Simple understanding: It is a container that stores database connections. When users need to access the database, the container provides connection objects for users to use, and then the user returns the connection objects to the container.

(2) Use

A: Implementing the DataSource interface under the javax.sql package

• Method: Get the connection: getConnection()
• Method: Return the connection: Connection.close()

Note: Using the connection.close() method in the connection pool does not mean closing the connection, but returning the connection.

B: We can use implementations provided by some open source database vendors.

• C3P0
• Druid (from Alibaba)

(3) C3P0

A: First import three jar packages c3p0-0.9.5.2.jar, mchange-commons-java-0.2.12.jar, database-driven jar packages

B: Modify the configuration file

C: Create database connection pool object - ComboPooled DataSource ()

D: Get the connection -- getConnection()

package cn.ideal.datasource.c3p0;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class C3P0Demo {

    private static DataSource comboPooledDataSource = null;

    static{
        //Use default XML configuration if not specified
        //Database Connection Pool Object ComboPooled DataSource
        comboPooledDataSource = new ComboPooledDataSource();
    }

    public static Connection getConnection() throws SQLException {
        //Get the connection
        return comboPooledDataSource.getConnection();
    }

    public static void main(String[] args) throws SQLException {
        System.out.println(getConnection());
    }
}

//Test Output Log and Content

(4) Druid

package cn.ideal.datasource.druid;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;

public class DruidDemo {
    public static void main(String[] args) throws Exception {
        //Loading configuration files
        Properties pro = new Properties();
        InputStream is = DruidDemo.class.getClassLoader().getResourceAsStream("druid.properties");
        pro.load(is);

        //Get the connection pool object
        DataSource ds = DruidDataSourceFactory.createDataSource(pro);

        //Get the connection
        Connection connection =ds.getConnection();

        System.out.println(connection);
    }
}

Try writing a tool class with Druid

package cn.ideal.datasource.utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JDBCUtils {

    private static DataSource ds = null;

    static {
        //Loading configuration files
        Properties properties = new Properties();
        try {
            properties.load(JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
            ds = DruidDataSourceFactory.createDataSource(properties);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * Get the connection
     */
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    /**
     * Releasing resources
     */

    public static void close(Statement statement, Connection connection) {
        close(null, statement, connection);
    }

    public static void close(ResultSet resultSet, Statement statement, Connection connection) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Tool class testing

package cn.ideal.datasource.test;

import cn.ideal.datasource.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class JDBCUtilsTest {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            //Get the connection
            connection = JDBCUtils.getConnection();
            //Define sql
            String sql = "INSERT INTO account VALUES(NULL,?,?)";
            //Get the preparedStatement object
            preparedStatement = connection.prepareStatement(sql);
            //Assignment of value to the ____________
            preparedStatement.setString(1, "lisi");
            preparedStatement.setString(2, "2000");
            //Executing sql
            int count = preparedStatement.executeUpdate();
            System.out.println(count);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(preparedStatement,connection);
        }
    }
}

Ending:

If there are any deficiencies or errors in the content, please leave a message for me, crab and crab! C

If you can help me, then pay attention to me! (All articles in this series will be updated at the first time on the Public Number.)

We don't know each other here, but we are all working hard for our dreams.

A public name that insists on pushing original Java technology: more than 20 years of ideal