Java learning notes 50 (DBCP connection pool)

In actual development, connecting database is a very resource consuming operation. However, we need to connect database frequently

At this time, in order to improve efficiency, the connection pool technology will be used here:

 

Common understanding of connection pool:

There are many connections in a pool. Which one should be taken out and used? Put it back after use instead of closing the connection

Four jar packages are used here

commons-dbcp-1.4.jar

commons-pool-1.5.6.jar

mysql-connector-java-5.1.37-bin.jar

commons-dbutils-1.6.jar

 

DataSource interface: canonical interface for connection pool

BasicDataSource class: implementation class in imported jar package

Method and configuration of connecting pool to database:

package demo;

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

import org.apache.commons.dbcp.BasicDataSource;

public class DataSourceDemo {
    public static void main(String[] args) {
        BasicDataSource dataSource = new BasicDataSource();
        // General basic configuration
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/mybase");
        dataSource.setUsername("root");
        dataSource.setPassword("xuyiqing");
        // Connection pool configuration
        dataSource.setMaxActive(10);// maximum connection
        dataSource.setMaxIdle(5);// Maximum free connection
        dataSource.setMinIdle(2);// Minimum free connection
        dataSource.setInitialSize(5);// Initialize connection
        try {
            Connection con = dataSource.getConnection();
        } catch (SQLException ex) {
            ex.printStackTrace();
            throw new RuntimeException("Database connection failed");
        }
    }
}

 

Next, create a tool class of connection pool to facilitate development:

Data preparation: create table:

CREATE TABLE sort(
  sid INT PRIMARY KEY AUTO_INCREMENT,
  sname VARCHAR(100),
  sprice DOUBLE,
  sdesc VARCHAR(5000)
);

Tools:

package demo;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;

public class JDBCUtils {
    private static BasicDataSource dataSource = new BasicDataSource();

    static {
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/mybase");
        dataSource.setUsername("root");
        dataSource.setPassword("xuyiqing");
        dataSource.setInitialSize(10);
        dataSource.setMaxActive(8);
        dataSource.setMaxIdle(5);
        dataSource.setMinIdle(1);
    }

    public static DataSource getDataSource() {
        return dataSource;
    }
}

Test:

package demo;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;

public class QueryRunnerDemo {
    private static QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());

    public static void main(String[] args) {
        insert();
    }

    public static void insert() {
        String sql = "INSERT INTO sort (sname,sprice,sdesc)VALUES(?,?,?)";
        Object[] params = { "Fruits", 20, "Promotion" };
        try {
            qr.update(sql, params);
        } catch (SQLException ex) {
            throw new RuntimeException("Data add failed");
        }
    }

    public static void select() {
        String sql = "SELECT * FROM sort";
        try {
            List<Object[]> list = qr.query(sql, new ArrayListHandler());
            for (Object[] objs : list) {
                for (Object obj : objs) {
                    System.out.println(obj + "\t");
                }
            }
        } catch (SQLException ex) {
            throw new RuntimeException("Data add failed");
        }
    }
}

Tags: Java SQL MySQL Database

Posted on Mon, 04 May 2020 10:37:08 -0400 by valentin