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">
        <!-- 接數(shù)據(jù)庫的驅(qū)動類名 -->
        <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>
        <!-- 當(dāng)連接池用完時等待獲取新連接的時間，超時后將拋出SQLException，單位毫秒，如設(shè)為0則無限期等待。默認(rèn)為0。 -->
        <property name="checkoutTimeout">5000</property>
        <!-- 當(dāng)連接用盡后，一次獲取的連接個數(shù) -->
        <property name="acquireIncrement">2</property>
        <!-- 初始連接數(shù) -->
        <property name="initialPoolSize">1</property>
        <!-- 最小連接數(shù) -->
        <property name="minPoolSize">3</property>
        <!-- 最大連接數(shù) -->
        <property name="maxPoolSize">5</property>
    </named-config>
</c3p0-config>

Program code:

public class TestDataPool {
    // 根據(jù)配置文件里的名稱創(chuàng)建連接池
    public static ComboPooledDataSource cpds = new ComboPooledDataSource("mysql");
    
    /**
     * 主程序
     */
    public static void main(String[] args) {
        // 模擬多次對數(shù)據(jù)庫的查詢操作
        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;
        // 獲取連接并執(zhí)行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:

# 接數(shù)據(jù)庫的驅(qū)動類名
driverClassName=com.mysql.jdbc.Driver
# 連接屬性
url=jdbc:mysql://192.168.35.128:3306/demo
username=root
password=123456
# 初始化連接數(shù)
initialSize=10
# 最大連接數(shù)
maxActive=15

Program code:

public class TestDBCP {
    // 根據(jù)配置文件里的名稱創(chuàng)建連接池
    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) {
        // 模擬多次對數(shù)據(jù)庫的查詢操作
        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;
        // 獲取連接并執(zhí)行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!