Tomcat-dbcp database connection pool configuration and some pitfalls when using it

1. Database connection pool

When developing, you often need to perform some operations on the database, such as common additions, deletions, modifications, and searches. When the amount of data is small, It can be operated directly, but when the amount of data increases, each connection and release of the database will take a certain amount of time. At this time, the database connection pool can be used to maintain the database link and reduce the overhead caused by connecting to the database on the program, and It can reduce the pressure on the database, so what is the database connection pool? As the name suggests, it is a pond. What is placed in the pond is a link to the database. For example, a fish pond is a pond for raising fish. If you want to eat fish, you can directly fish it. You don’t have to buy fry and raise fish yourself. The database The connection pool is used to store links to the database. All links are established uniformly. When used, you can directly take them from it. After use, you can put them back into the pool. Since you use this thing, then We don’t need to write the code ourselves. Some open source ones can be used directly. There are three common open source connection pools, c3p0, dbcp, and proxool. I have never used c3p0 and proxool, just simple I have used dbcp pool, here I will talk about how to use dbcp database connection pool, and some pitfalls encountered when using it

Figure 1, before using connection pool

Figure 2 After using the connection pool

As shown in Figure 1 above, before using the connection pool, you need to A link is established to the database every time and needs to be released at any time. When the amount of data is large, it requires a lot of overhead to connect to the database, and frequent access and release of the database will also put a lot of pressure on the database. , Figure 2 shows that after using the database connection pool, all links are placed in the pool without releasing them. When used, they are taken directly from the pool and put back into the pool after use. The pool maintains long links to the database. If the link is disconnected, it will automatically reconnect. If the connection is not enough, subsequent users will need to wait.

2. Use the jar package used by tomcat-dbcp

contains tomcat-dbcp.jar That’s it, the rest are some basic packages

3. Configuration used

dbname.Driver=com.mysql.jdbc.Driver
dbname.Url=jdbc:mysql://<your ip>/<your dbname>?useUnicode=true&characterEncoding=UTF-8
&autoReconnect=true&failOverReadOnly=false&maxReconnects=10&autoReconnectForPools=true&zeroDateTimeBehavior=convertToNull&connectTimeout=3000
dbname.Username=<your username>
dbname.Password=<your password>
dbname.InitialSize=15
dbname.MinIdle=10
dbname.MaxIdle=20
dbname.MaxWait=5000
dbname.MaxActive=20
dbname.validationQuery=select 1

These configurations only need to be placed in .properties, about each The meaning

where driver, url, username, and password are common database connection configurations

InitialSize为初始化建立的连接数

minidle为数据库连接池中保持的最少的空闲的链接数

maxidle数据库连接池中保持的最大的连接数

maxwait等待数据库连接池分配连接的最长时间，超出之后报错

maxactivite最大的活动链接数，如果是多线程可以设置为超出多线程个数个链接数

<pre name="code" class="java">validationQuery测试是否连接是有效的sql语句

3. Connection pool code

public abstract class DB {

    private static HashMap<String, DataSource> dsTable = new HashMap<String, DataSource>();//此处记得用static
    private BasicDataSource ds;
    private PreparedStatement stmt = null;

    private DataSource getDataSource(String n) {
        if (dsTable.containsKey(n)) {
            return dsTable.get(n);//如果不同的数据库，多个连接池
        } else {
        	synchronized (dsTable) {
        		ds = new BasicDataSource();
                ds.setDriverClassName(DBConfig.getString("db", n.concat(".Driver")));//将<yourname>.properties的值读进来
                ds.setUrl(DBConfig.getString("db", n.concat(".Url")));
                ds.setUsername(DBConfig.getString("db", n.concat(".Username")));
                ds.setPassword(DBConfig.getString("db", n.concat(".Password")));
                ds.setInitialSize(DBConfig.getInteger("db", n.concat(".InitialSize")));
                ds.setMinIdle(DBConfig.getInteger("db", n.concat(".MinIdle")));
                ds.setMaxIdle(DBConfig.getInteger("db", n.concat(".MaxIdle")));
                ds.setMaxWait(DBConfig.getInteger("db", n.concat(".MaxWait")));
                ds.setMaxActive(DBConfig.getInteger("db", n.concat(".MaxActive")));
                ds.setValidationQuery(DBConfig.getString("db", n.concat(".validationQuery")));
                dsTable.put(n, ds);

                return ds;
			}
        }
    }

    protected Connection conn;

    public boolean open() throws SQLException {
    	BasicDataSource bds=(BasicDataSource)this.getDataSource(this.getConnectionName());
    	System.out.println("connection_number:"+bds.getNumActive()+"dsTable:"+dsTable);
        this.conn = this.getDataSource(this.getConnectionName()).getConnection();
        return true;
    }

    public void close() throws SQLException {
    	
        if (this.conn != null)
            this.conn.close();
    }

    protected abstract String getConnectionName();//此函数可以根据自己的需求，将数据库的名字传进来即可

    public void prepareStatement(String sql) throws SQLException {
        this.stmt = this.conn.prepareStatement(sql);
    }

    public void setObject(int index, Object value, int type) throws SQLException {
        this.stmt.setObject(index, value, type);
    }

    public void setObject(int index, Object value) throws SQLException {
        this.stmt.setObject(index, value);
    }

    public int execute() throws SQLException {
        return this.stmt.executeUpdate();
    }
}

The above is the code used when using the thread pool. It only gives a rough writing method. The specific DBDAO part needs to be implemented by yourself according to your own needs, such as batch processing, query, and update. Functions such as these can be modified according to personal needs, so how do you judge whether the link you create is what you want? There are two ways to check

1. Create an empty database and check the number of links

2. Check the number of links under linux

Get the processid

ps aux|grep <your java name>

View the links to the database

netstat -apn|grep <your processid>

You can see the specific number of links to check whether your connection pool is correct

4. Some pitfalls encountered

Because it is used in multi-threaded form, the most important pitfall encountered is the usage of static. Because I am not too familiar with it, I don’t use static. , resulting in each thread establishing a database connection pool, and a "too many files open" error occurred. This was caused by the fact that static was not used in the thread pool.

The above is the tomcat-dbcp database connection pool configuration and some pitfalls when using it. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!