Java operation database - use connection pool to connect to the database

Traditional method and connection pooling method

Steps of traditional method

Use traditional method in Using JDBC in Java to connect to the database and complete a database operation generally involves the following steps:

1. Load the driver.

2. Establish a connection.

3. Execute SQL statement.

4. Release the connection.

5. Disadvantages of the traditional method

Every operation on the database requires a connection to be established, and the obtained Connection object will be loaded into memory, which consumes a lot of memory and time. . If there are many operations that require connection establishment in a short period of time, it will occupy a lot of system resources and even cause the server to crash.

Corresponding to establishing a connection, you need to manually release the connection every time you use it. If you forget to release the connection or the program fails to release it due to an exception, it will cause a memory leak.

In addition, the traditional method cannot control the number of connections. If too many people connect, it will lead to unlimited creation of connection objects, resulting in excessive memory overhead and server crash.

(Recommended video: java video tutorial)

Steps of connection pool

1. Create a connection pool and configure the connection Attributes.

2. Use a connection pool to obtain a connection.

Advantages of connection pool

Every time you need to connect to the database, you do not need to establish a connection, but obtain it through the connection pool, which provides the connection.

After using the connection, you do not need to manually release the connection, but let the connection pool release the connection.

The number of connections can be controlled through the connection pool. The connections in the connection pool can be reused multiple times, avoiding the problem of unlimited connection creation.

Use connection pool

Use C3P0 database connection pool

Import jar package:

c3p0-0.9.5.2.jar

In the code root directory of the current project Create a new configuration file named c3p0-config.xml under src. Note that the file name cannot be changed. The content is as follows:

<c3p0-config>
    <!-- 连接名称 -->
    <named-config name="mysql">
        <!-- 接数据库的驱动类名 -->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <!-- 连接属性 -->
        <property name="jdbcUrl">jdbc:mysql://192.168.35.128:3306/demo</property>
        <property name="user">root</property>
        <property name="password">123456</property>
        <!-- 当连接池用完时等待获取新连接的时间，超时后将抛出SQLException，单位毫秒，如设为0则无限期等待。默认为0。 -->
        <property name="checkoutTimeout">5000</property>
        <!-- 当连接用尽后，一次获取的连接个数 -->
        <property name="acquireIncrement">2</property>
        <!-- 初始连接数 -->
        <property name="initialPoolSize">1</property>
        <!-- 最小连接数 -->
        <property name="minPoolSize">3</property>
        <!-- 最大连接数 -->
        <property name="maxPoolSize">5</property>
    </named-config>
</c3p0-config>

Program code:

public class TestDataPool {
    // 根据配置文件里的名称创建连接池
    public static ComboPooledDataSource cpds = new ComboPooledDataSource("mysql");
    
    /**
     * 主程序
     */
    public static void main(String[] args) {
        // 模拟多次对数据库的查询操作
        for (int i = 0; i < 6; i++) {
            new Thread(new Runnable() {
                @Override
                public void run() {
                    select();
                }
            }, "线程" + i).start();
        }
    }
    
    /**
     * 查询程序
     */
    public static void select() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        // 获取连接并执行SQL
        try {
            conn = cpds.getConnection();
            pstmt = conn.prepareStatement("select * from student where id = 906");
            rs = pstmt.executeQuery();
            while (rs.next()) {
                System.out.println(Thread.currentThread().getName() + "\t" + rs.getString(1) + "\t" + rs.getString(2) + "\t" + rs.getString("address"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 释放资源
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Use DBCP database connection pool

Import jar package:

 commons-dbcp-1.4.jar2 commons-pool-1.5.5.jar

Create a new configuration file named dbcp.properties under the code root directory src of the current project. The file name needs to be the same as the file name referenced in the code. The content is as follows:

# 接数据库的驱动类名
driverClassName=com.mysql.jdbc.Driver
# 连接属性
url=jdbc:mysql://192.168.35.128:3306/demo
username=root
password=123456
# 初始化连接数
initialSize=10
# 最大连接数
maxActive=15

Program code:

public class TestDBCP {
    // 根据配置文件里的名称创建连接池
    private static DataSource source = null;
    static {
        Properties pros = new Properties();
        InputStream is = TestDBCP.class.getClassLoader().getResourceAsStream("dbcp.properties");
        try {
            pros.load(is);
            source = BasicDataSourceFactory.createDataSource(pros);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 主程序
     */
    public static void main(String[] args) {
        // 模拟多次对数据库的查询操作
        for (int i = 0; i < 6; i++) {
            new Thread(new Runnable() {
                @Override
                public void run() {
                    select();
                }
            }, "线程" + i).start();
        }
    }

    /**
     * 查询程序
     */
    public static void select() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        // 获取连接并执行SQL
        try {
            conn = source.getConnection();
            pstmt = conn.prepareStatement("select * from student where id = 906");
            rs = pstmt.executeQuery();
            while (rs.next()) {
                System.out.println(Thread.currentThread().getName() + "\t" + rs.getString(1) + "\t" + rs.getString(2) + "\t" + rs.getString("address"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 释放资源
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

The above is the detailed content of Java operation database - use connection pool to connect to the database. For more information, please follow other related articles on the PHP Chinese website!