带有SQL Server 2008的Jdbc连接池失败 [英] Jdbc Connection Pool with Sql Server 2008 fails

问题描述

我正在尝试将我的jdbc代码转换为在Tomcat上使用连接池功能.我的系统是一个连接到SQL Server 2008的jsp/servlet应用程序.因此，让我们进入代码...

I am trying to turn my jdbc code to use Connection Pool functionality on Tomcat. My system is a jsp/servlet application that connects to an SQL Server 2008. So, lets get to the code...

我的Connection类如下:

My Connection class looks like this:

    import org.apache.tomcat.jdbc.pool.DataSource;
    import org.apache.tomcat.jdbc.pool.PoolProperties;
    //... some more imports
public class DbPooledConnectionToMSSQL {

    public String dbsource ;
    private Connection dbCon;
    private DataSource datasource = new DataSource();
    private PoolProperties p = new PoolProperties();

    public DbPooledConnectionToMSSQL() {
        super();

        dbsource = "jdbc:sqlserver://xxx.xxx.x.x:1433;database=xxx";
        String user = "user";
        String password = "pass";

        p.setUrl(dbsource);
        p.setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        //p.setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerConnectionPoolDataSource");
        p.setUsername(user);
        p.setPassword(password);
        p.setMaxActive(100);
        p.setInitialSize(10);
        p.setMaxWait(10000);
        p.setRemoveAbandonedTimeout(60);
        p.setMinEvictableIdleTimeMillis(30000);
        p.setMinIdle(10);
        datasource.setPoolProperties(p);
    }

    public boolean connect() throws ClassNotFoundException, SQLException {
        try {
            if (dbCon == null) {
                System.err.println("Creating Pooled Connection....");
                dbCon = datasource.getConnection(); //<-- here is the exception
                System.err.println("!!! Pooled Connection creation OK");
            } else {
                System.err.println("!!! Connection EXIST not creation");
            }
        } catch (SQLException e) {
            return false;
        } catch (Exception e) {
            return false;
        }
        return true;
    }
    //more code below
}

现在，在运行程序类中，我初始化了该类并尝试连接到数据库，但是在命令dbCon = datasource.getConnection();

Now, in my runner class I initialize this class and try to connect to my database, but I get the famous ClassNotFoundException on the command dbCon = datasource.getConnection();

我确定连接驱动程序就位，因为它已在我的常规jdbc代码(而不是连接池)中使用，并且工作正常.

I am sure that the Connection Driver is in place because it is already used in my regular jdbc code (not the Connection Pooling) and works just fine.

我也尝试将com.microsoft.sqlserver.jdbc.SQLServerConnectionPoolDataSource用作驱动程序类，但我也遇到了相同的异常:

I also tried to use com.microsoft.sqlserver.jdbc.SQLServerConnectionPoolDataSource as driver class but I also get the same exception:

java.sql.SQLException: com.microsoft.sqlserver.jdbc.SQLServerDriver
    at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:254)
    at org.apache.tomcat.jdbc.pool.PooledConnection.connect(PooledConnection.java:182)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.createConnection(ConnectionPool.java:702)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:634)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.init(ConnectionPool.java:488)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.<init>(ConnectionPool.java:144)
    at org.apache.tomcat.jdbc.pool.DataSourceProxy.pCreatePool(DataSourceProxy.java:116)
    at org.apache.tomcat.jdbc.pool.DataSourceProxy.createPool(DataSourceProxy.java:103)
    at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:127)
    at admin.db.DbPooledConnectionToMSSQL.connect(DbPooledConnectionToMSSQL.java:97)
    ......

Caused by: java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver
    at java.net.URLClassLoader$1.run(URLClassLoader.java:366)
    at java.net.URLClassLoader$1.run(URLClassLoader.java:355)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.net.URLClassLoader.findClass(URLClassLoader.java:354)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
    at java.lang.Class.forName0(Native Method)
    at java.lang.Class.forName(Class.java:270)
    at org.apache.tomcat.jdbc.pool.PooledConnection.connectUsingDriver(PooledConnection.java:246)

我在这里做什么错?还有另一个可以执行此任务的jdbc驱动程序吗?

What do I do wrong here? Is there another jdbc Driver that could do the job?

推荐答案

好，发现了问题，答案很简单.查看

Ok, found the problem and the answer is pretty simple. Looking at the specification for PoolProperties , it says that :

setDriverClassName():要使用的JDBC驱动程序的标准Java类名称. 必须从与tomcat-jdbc.jar相同的类加载器中访问驱动程序

setDriverClassName(): The fully qualified Java class name of the JDBC driver to be used. The driver has to be accessible from the same classloader as tomcat-jdbc.jar

因此，即使驱动程序可由我的应用程序访问，但Tomcat也无法访问.因此，将驱动程序放在$CATALINA_HOME/libs目录中并重新启动Tomcat即可解决此问题.

So even the driver was accessible by my application, it was not accessible by Tomcat. So putting the driver in $CATALINA_HOME/libs directory and restarting Tomcat solved this issue.

这篇关于带有SQL Server 2008的Jdbc连接池失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！