java - 从连接池获取连接的静态类

Question

我想了解连接池的概念。例如，我在 META-INF 中有以下 xml 文件以及我的数据库设置

 <Resource name="jdbc/appname"
              auth="Container"
              type="javax.sql.DataSource"
              maxActive="100" 
              maxIdle="30"
              maxWait="10000"
              minIdle="10"
              username="postgres" 
              password="123"
              driverClassName="org.postgresql.Driver"
              url="jdbc:postgresql://localhost:5432/Lab4"/>

要使用连接池，我使用以下类

    public class DataBaseConnection {

        private static DataSource dataSource;

        static {
            try {
                InitialContext initContext = new InitialContext();
                dataSource = (DataSource) initContext.lookup("java:comp/env/jdbc/appname");
            } catch (NamingException ex) {
                throw new RuntimeException(ex);
            }
        }

        public static Connection getConnection() {
            try {
                return dataSource.getConnection();
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
        }

这是用法

public class UserQueries{ 
 public User selectUserByLoginAndPassword(final String login, final String password) {
        try(Connection connection=DataBaseConnection.getConnection())
        try (PreparedStatement st = connection.prepareStatement(SELECT_QUERY)) {
            st.setString(1, login);
            st.setString(2, password);
            ResultSet result = st.executeQuery();
            while (result.next()) {
                final User user = User.newBuilder()
                        .setId(result.getInt("id"))
                        .setAge(result.getInt("age"))
                        .setName(result.getString("name"))
                        .setPassword(result.getString("password"))
                        .setLogin(login)
                        .build();
                return user;

            }
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
        throw new NullPointerException("Nu such user in db");
    }

   }

问题是，如果我按以下方式修改DataBaseConnection，

public class DataBaseConnection {

        private  DataSource dataSource;

        public DataBaseConnection()
            try {
                InitialContext initContext = new InitialContext();
                dataSource = (DataSource) initContext.lookup("java:comp/env/jdbc/appname");
            } catch (NamingException ex) {
                throw new RuntimeException(ex);                
        }

        public  Connection getConnection() {
            try {
                return dataSource.getConnection();
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
        }

我将在每个使用数据库连接的类中创建新的 DataBaseConnection 对象，这是否意味着每个类(例如 UserQueries)将创建单独的连接池以供使用？

示例

public class UserQueries{ 
  private DataBaseConnection dbCon = new DataBaseConnection();
 public User selectUserByLoginAndPassword(final String login, final String password) {
        try(Connection connection = dbCon.getConnection())
        try (PreparedStatement st = connection.prepareStatement(SELECT_QUERY)) {
            st.setString(1, login);
            st.setString(2, password);
            ResultSet result = st.executeQuery();
            while (result.next()) {
                final User user = User.newBuilder()
                        .setId(result.getInt("id"))
                        .setAge(result.getInt("age"))
                        .setName(result.getString("name"))
                        .setPassword(result.getString("password"))
                        .setLogin(login)
                        .build();
                return user;

            }
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        }
        throw new NullPointerException("Nu such user in db");
    }

   }

Answer 1

每次 JNDI 查找都会返回一个新的 DataSource 实例。每个 DataSource 实例都维护自己的连接池。

请参阅下面的 Datasource.getConnection() 的 tomcat 实现:,

public Connection getConnection() throws SQLException {
    if (pool == null)
        return createPool().getConnection();
    return pool.getConnection();
}

这将创建连接池:

private synchronized ConnectionPool pCreatePool() throws SQLException {
    if (pool != null) {
        return pool;
    } else {
        pool = new ConnectionPool(poolProperties);
        return pool;
    }
}

总而言之，

• 每次 JNDI 查找都会返回新的 DataSource 实例。
• 将为 DataSource 的每个实例创建新的连接池