【Java/线程】用线程池避免线程争抢有限的数据库连接资源

数据库连接资源是有限的，当一次性放出大量线程，而每个线程都需要获得数据库连接资源时，争取不到资源的线程必会抛出异常，如以下程序：

package com.hy.lab.gotminusone;

import com.zaxxer.hikari.HikariDataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

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

class Emp{
    long id;
    String name;

    public String toString(){
        return "Emp id:"+id+" name="+name;
    }
}

public class ConnectThread extends Thread{
    public void run(){
        HikariDataSource ds=new HikariDataSource();
        ds.setDriverClassName("oracle.jdbc.OracleDriver");
        ds.setJdbcUrl("jdbc:oracle:thin:@127.0.0.1:1521:orcl");
        ds.setUsername("luna");
        ds.setPassword("1234");
        ds.setMinimumIdle(10);
        ds.setMaximumPoolSize(10);

        JdbcTemplate jdbcTplt = new JdbcTemplate(ds);
        jdbcTplt.execute("select sysdate from dual");

        List<Emp> emps = jdbcTplt.query("select id,name from emp0426", new RowMapper<Emp>() {
            @Override
            public Emp mapRow(ResultSet rs, int rowNum) throws SQLException {
                Emp emp=new Emp();
                emp.id=rs.getInt("id");
                emp.name=rs.getString("name");
                return emp;
            }});

        for(Emp emp:emps){
            System.out.println(emp);
        }

        ds.close();
    }

    public static void main(String[] args){
        int thCnt=1000;
        for(int i=0;i<thCnt;i++) {
            ConnectThread ct = new ConnectThread();
            ct.start();
        }
    }
}

争抢不到连接资源的线程抛出的异常是这样的：

ORA-12519, TNS:no appropriate service handler found
  (CONNECTION_ID=cR3t33g4RYG/yMrJvOolsg==)
    at oracle.jdbc.driver.T4CConnection.handleLogonNetException(T4CConnection.java:870)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:675)
    at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:1032)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:90)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:681)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:602)
    at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:138)
    at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:364)
    at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:206)
    at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:476)
    at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:561)
    at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115)
    at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:112)
    at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:158)
    at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:116)
    at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:79)
    ... 3 more
Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12519, TNS:no appropriate service handler found
  (CONNECTION_ID=cR3t33g4RYG/yMrJvOolsg==)
    at oracle.net.ns.NSProtocolNIO.createRefusePacketException(NSProtocolNIO.java:816)
    at oracle.net.ns.NSProtocolNIO.handleConnectPacketResponse(NSProtocolNIO.java:396)
    at oracle.net.ns.NSProtocolNIO.negotiateConnection(NSProtocolNIO.java:207)
    at oracle.net.ns.NSProtocol.connect(NSProtocol.java:350)
    at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1967)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:640)
    ... 17 more

 

为了避免大批线程同时争竞，可以用线程池限制同时进行的线程数目，如以下程序：

package com.hy.lab.gotminusone;

import com.zaxxer.hikari.HikariDataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

public class ConnectThread2 implements Runnable{
    public void run(){
        HikariDataSource ds=new HikariDataSource();
        ds.setDriverClassName("oracle.jdbc.OracleDriver");
        ds.setJdbcUrl("jdbc:oracle:thin:@127.0.0.1:1521:orcl");
        ds.setUsername("luna");
        ds.setPassword("1234");
        ds.setMinimumIdle(10);
        ds.setMaximumPoolSize(10);

        JdbcTemplate jdbcTplt = new JdbcTemplate(ds);
        jdbcTplt.execute("select sysdate from dual");

        List<Emp> emps = jdbcTplt.query("select id,name from emp0426", new RowMapper<Emp>() {
            @Override
            public Emp mapRow(ResultSet rs, int rowNum) throws SQLException {
                Emp emp=new Emp();
                emp.id=rs.getInt("id");
                emp.name=rs.getString("name");
                return emp;
            }});

        for(Emp emp:emps){
            System.out.println(emp);
        }

        ds.close();
    }

    public static void main(String[] args){
        // 一次执行一个线程的线程池
        //ExecutorService es= Executors.newSingleThreadExecutor();

        // 固定允许同时执行10个线程的线程池
        ExecutorService es= Executors.newFixedThreadPool(10);
        int thCnt=1000;
        for(int i=0;i<thCnt;i++) {
            es.execute(new ConnectThread2());
        }
        es.shutdown();
    }
}

这样就不会出现上面的异常了。

END