Java DBCP不断创建新连接

Question

我的 DBCP 配置一直在创建新的连接，以至于我的 MySQL 服务器因为连接太多而阻塞了它:

public class SQL {
    private final static String DRIVER_CLASS_NAME = "com.mysql.jdbc.Driver";
    private final static String USERNAME = "secret";
    private final static String PASSWORD = "secret";
    private final static String URL = "secret";

    public static Connection getConnection() {
        BasicDataSource basicDataSource = new BasicDataSource();
        basicDataSource.setDriverClassName(DRIVER_CLASS_NAME);
        basicDataSource.setUrl(URL);
        basicDataSource.setUsername(USERNAME);
        basicDataSource.setPassword(PASSWORD);
        try {
            return basicDataSource.getConnection();
        } catch (SQLException ex) {
            Logger.getLogger(SQL.class.getName()).log(Level.SEVERE, null, ex);
            throw new IllegalStateException("bf4.sql.SQL.getConnection: No connection could be made: " + ex.getMessage());
        }
    }
}

我的 xxxManager.java:

public class PlayerkillManager extends Manager<PlayerkillBean, PlayerkillConstraint> {
    public PlayerkillManager() {
        super(SQL.getConnection());
    }

    @Override
    protected PreparedStatement insertPS(final PlayerkillBean playerkill) throws SQLException {
        PreparedStatement ps = connection.prepareStatement("INSERT INTO playerkills (`date`, `playerId`, `targetId`, `weaponId`, `headshot`) VALUES(?, ?, ?, ?, ?)", PreparedStatement.RETURN_GENERATED_KEYS);
        ps.setObject(1, playerkill.getDate());
        ps.setObject(2, playerkill.getPlayerId());
        ps.setObject(3, playerkill.getTargetId());
        ps.setObject(4, playerkill.getWeaponId());
        ps.setObject(5, playerkill.getHeadshot());
        return ps;
    }

    @Override
    protected PreparedStatement updatePS(final PlayerkillBean playerkill) throws SQLException {
        throw new UnsupportedOperationException("There are no non-key columns in this table.");
    }

    @Override
    protected PreparedStatement deletePS(final PlayerkillBean playerkill) throws SQLException {
        PreparedStatement ps = connection.prepareStatement("DELETE FROM playerkills WHERE `id` = ? AND `date` = ? AND `playerId` = ? AND `targetId` = ? AND `weaponId` = ? AND `headshot` = ?");
        ps.setObject(1, playerkill.getId());
        ps.setObject(2, playerkill.getDate());
        ps.setObject(3, playerkill.getPlayerId());
        ps.setObject(4, playerkill.getTargetId());
        ps.setObject(5, playerkill.getWeaponId());
        ps.setObject(6, playerkill.getHeadshot());
        return ps;
    }

    @Override
    protected String searchQuery() {
        return "SELECT `playerkills`.`id`, `playerkills`.`date`, `playerkills`.`playerId`, `playerkills`.`targetId`, `playerkills`.`weaponId`, `playerkills`.`headshot` FROM playerkills";
    }

    @Override
    protected String tableName() {
        return "playerkills";
    }

    @Override
    protected String[] columnNames() {
        return new String[] {
            "id", 
            "date", 
            "playerId", 
            "targetId", 
            "weaponId", 
            "headshot", 
        };
    }

    @Override
    protected Map<TableField, List<List<TableField>>> getPaths() {
        //Function not interesting and too much code
    }

    @Override
    protected PlayerkillBean createBean(final ResultSet rs) throws SQLException {
        return new PlayerkillBean(rs);
    }
}

Manager.java类:

public abstract class Manager<B extends Bean, C extends AbstractConstraint> implements Closeable {
    protected final Connection connection;

    public Manager(final Connection con) {
        this.connection = con;
    }

    public final int insert(final B b) throws InsertException {
        try {
            try (PreparedStatement ps = insertPS(b)) {
                ps.executeUpdate();
                try (ResultSet rs = ps.getGeneratedKeys()) {
                    rs.last();
                    if (rs.getRow() != 0) {
                        rs.beforeFirst();
                        rs.next();
                        return rs.getInt(1);
                    }
                    else {
                        return -1;
                    }
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(Manager.class.getName()).log(Level.SEVERE, null, ex);
            throw new InsertException(ex);
        }
    }

    public final boolean update(final B b) throws UpdateException {
        try {
            try (PreparedStatement ps = updatePS(b)) {
                return ps.execute();
            }
        } catch (SQLException ex) {
            Logger.getLogger(Manager.class.getName()).log(Level.SEVERE, null, ex);
            throw new UpdateException(ex);
        }
    }

    public final boolean delete(final B b) throws DeleteException {
        try {
            try (PreparedStatement ps = deletePS(b)) {
                return ps.execute();
            }
        } catch (SQLException ex) {
            Logger.getLogger(Manager.class.getName()).log(Level.SEVERE, null, ex);
            throw new DeleteException(ex);
        }
    }

    public final B get(final AbstractConstraint... c) throws SearchException {
        List<B> beans = search(c);
        if (beans.size() == 1) {
            return beans.get(0);
        }
        throw new IllegalArgumentException("orm.Manager.get: beans.size() != 1: beans.size() = " + beans.size());
    }

    public final List<B> search(final AbstractConstraint... c) throws SearchException {
        if (c.length == 0) {
            throw new IllegalArgumentException("orm.Manager.search: c.length == 0");
        }
        try {
            List<B> beans = new ArrayList<>();

            for (AbstractConstraint constraint : c) {
                try (PreparedStatement ps = new QueryBuilder(connection, tableName(), getPaths(), searchQuery()).add(constraint).build();
                        ResultSet rs = ps.executeQuery()) {
                    while (rs.next()) {
                        beans.add(createBean(rs));
                    }
                }
            }

            if (c.length > 1) {
                boolean sorting = true;
                Field field = c[0].getField();
                Order order = c[0].getOrder();
                for (int i = 1; i < c.length; i++) {
                    Field currentField = c[i].getField();
                    Order currentOrder = c[i].getOrder();
                    if (!field.equals(currentField) || !order.equals(currentOrder)) {
                        sorting = false;
                        break;
                    }
                }
                if (sorting) {
                    //sort on field with comparator of supertype
                }
            }

            return beans;
        } catch (SQLException ex) {
            Logger.getLogger(Manager.class.getName()).log(Level.SEVERE, null, ex);
            throw new SearchException(ex);
        }
    }

    public final List<B> getAll() throws SearchException {
        return getAll(Order.NONE, null);
    }

    public final List<B> getAll(final Order order, final Field field) throws SearchException {
        try {
            List<B> beans = new ArrayList<>();
            try (
                    PreparedStatement ps = connection.prepareStatement(searchQuery() + " " + orderQuery(order, field));
                    ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    beans.add(createBean(rs));
                }
            }
            return beans;
        } catch (SQLException ex) {
            Logger.getLogger(Manager.class.getName()).log(Level.SEVERE, null, ex);
            throw new SearchException(ex);
        }
    }

    public final int getRowCount(final AbstractConstraint... c) throws SearchException {
        return search(c).size();
    }

    @Override
    public void close() {
        //was uncommented?
        try {
            connection.close();
        } catch (SQLException ex) {
            Logger.getLogger(Manager.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private String orderQuery(final Order order, final Field field) {
        if (order == Order.NONE) {
            return "";
        }
        return "ORDER BY " + field.getFieldName() + " " + order.getOrdername();
    }

    abstract protected PreparedStatement insertPS(B b) throws SQLException;

    abstract protected PreparedStatement updatePS(B b) throws SQLException;

    abstract protected PreparedStatement deletePS(B b) throws SQLException;

    abstract protected String searchQuery();

    abstract protected String tableName();

    abstract protected String[] columnNames();

    abstract protected Map<TableField, List<List<TableField>>> getPaths();

    abstract protected B createBean(ResultSet rs) throws SQLException;
}

我收集的一些统计数据:

• 最大并发连接数:152
• 失败的连接:12
• 中止连接:375

• 总连接数:844

• 插入查询数:373

我原以为会使用 1 个连接，但出了什么问题？

编辑:为了澄清，我的代码调用 xxxManager 例如这样:playerkillManager.insert(new PlayerkillBean(...));

Answer 1

每次调用 SQL.getConnection() 时都会创建一个新的连接池，这不是连接池的使用方式。

您应该在您的应用程序周围共享一个 javax.sql.DataSource ( doc )，而不是单独的连接。

所以，也许您可​​以将代码更改为:

public class SQL {
    private final static String DRIVER_CLASS_NAME = "com.mysql.jdbc.Driver";
    private final static String USERNAME = "secret";
    private final static String PASSWORD = "secret";
    private final static String URL = "secret";

    private final static DataSource dataSource;

    static {
        BasicDataSource basicDataSource = new BasicDataSource();
        basicDataSource.setDriverClassName(DRIVER_CLASS_NAME);
        basicDataSource.setUrl(URL);
        basicDataSource.setUsername(USERNAME);
        basicDataSource.setPassword(PASSWORD); 
        dataSource = basicDataSource;
    }

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

然后在其余的类(class)中，您可以使用该数据源。需要记住的重要事情是 DataSource.getConnection() 从池中借用一个连接，而 Connection.close() 实际上没有关闭连接;调用 close() 返回到池的连接。如果在借用的连接上调用 Connection.close() 失败，则连接泄漏。

您当前的代码需要编辑才能使用 try-with-resources借用连接时，例如

public void foo() {
    try (Connection conn = datasource.getConnection()) {
        //your code here
    } catch (SQLException e) {
        e.printStackTrace();
    }
}