从连接池获取数据库连接

I am refactoring others code. The one thing I notice is that of the manner on how the system is getting a connection from the connection pool.

我正在重构别人的代码。我注意到的一点是系统如何从连接池获取连接的方式。

Sample is like this. On every call of the service method, the system is making a context lookup on the JNDI for the datasource.

样品是这样的。在服务方法的每次调用中，系统都会对数据源的JNDI进行上下文查找。

public class CheckinServlet extends HttpServlet {

    public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        try {
            //Obtain Connection
            InitialContext initialContext = new InitialContext();
            javax.sql.DataSource ds = (javax.sql.DataSource) initialContext
                    .lookup("jdbc/mysqldb");
            java.sql.Connection conn = ds.getConnection();
            //business logic
            //redirect
        } finally {
            conn.close();
        }
    }
}

I do think that there is a performance hit on doing this every time. I am thinking of another way around these on how to retrieve a connection from a connection pool.

我确实认为每次都有这样的表演。我正在考虑另一种方法，关于如何从连接池中检索连接。

I am thinking about using the servlet's init() method but I think that is not optimal.

我正在考虑使用servlet的init()方法，但我认为这不是最佳方法。

4 个解决方案

#1

Do it once in a ServletContextListener instead of everytime in init() of many servlets. The contextInitialized() method is executed only once during webapp's startup.

在ServletContextListener中执行一次，而不是在许多servlet的init()中执行一次。在webapp的启动过程中，contextinitialize()方法只执行一次。

public class Config implements ServletContextListener {
    private static final String ATTRIBUTE_NAME = "config";
    private DataSource dataSource;

    @Override
    public void contextInitialized(ServletContextEvent event) {
        ServletContext servletContext = event.getServletContext();
        String databaseName = servletContext.getInitParameter("database.name");
        try {
            dataSource = (DataSource) new InitialContext().lookup(databaseName);
        } catch (NamingException e) {
            throw new RuntimeException("Config failed: datasource not found", e);
        }
        servletContext.setAttribute(ATTRIBUTE_NAME, this);
    }

    @Override
    public void contextDestroyed(ServletContextEvent event) {
        // NOOP.
    }

    public DataSource getDataSource() {
        return dataSource;
    }

    public static Config getInstance(ServletContext servletContext) {
        return (Config) servletContext.getAttribute(ATTRIBUTE_NAME);
    }
}

Configure it as follows in web.xml:

在web.xml中配置如下:

<context-param>
    <param-name>database.name</param-name>
    <param-value>jdbc/mysqldb</param-value>
</context-param>
<listener>
    <listener-class>com.example.Config</listener-class>
</listener>

You can obtain it in your servlet as follows (init() or doXXX() method, you choose):

您可以在servlet中获取它，如下所示(init()或doXXX()方法，您可以选择):

DataSource dataSource = Config.getInstance(getServletContext()).getDataSource();

I'd however refactor it a step further, JDBC code should preferably be placed in its own classes, not in servlets. Lookup the DAO pattern.

但是我将进一步重构它，JDBC代码最好放在它自己的类中，而不是servlet中。查找DAO模式。

#2

The method I have used in the past is to create a singleton class that holds the datasource

我过去使用的方法是创建一个保存数据源的单例类

E.g.

如。

public class DatabaseConnectionManager {

    DataSource ds;

    public void init() {
        InitialContext initialContext = new InitialContext();
        ds = (javax.sql.DataSource)initialContext.lookup("jdbc/mysqldb");
    }

    public Connection getConnection() {
        if(ds == null) init();

        return ds.getConnection();
    }
}

This means that you have a shared reference to your datasource, taking away the jndi lookup overhead.

这意味着您拥有对数据源的共享引用，从而减少了jndi查找开销。

#3

I just did some testing with this, and found that jndi lookup time is not that heavy. 50.000 lookups in about 1 sec here.

我只是用它做了一些测试，发现jndi查找时间并没有那么长。在1秒内进行50000次查询。

So in many cases, I don't see a reason for caching the DataSource at all.

所以在很多情况下，我根本看不到缓存数据源的原因。

Problem with caching is that you might end up with a stale DataSource, forcing you to restart your app if you change anything related to the datasource definition.

缓存的问题是，您可能会以一个陈旧的数据源告终，如果您更改了与数据源定义相关的任何内容，就会迫使您重新启动应用程序。

#4

Adding to what's said, there's a design pattern called Service Locator, which is a basically a singleton containing a registry called "service" that holds your JNDI objects.

除此之外，还有一个名为Service Locator的设计模式，它基本上是一个包含一个名为“Service”的注册表的单例，该注册表保存了您的JNDI对象。

Basically, if the object isn't found in the registry, the service is taken from the JNDI pool and registered in the registry. The next call will simply pull the object from registry.

基本上，如果在注册中心中没有找到对象，则从JNDI池中获取服务并在注册中心中注册。下一个调用将简单地从注册表中拉出对象。

Hope this helps.

希望这个有帮助。

#1