K'隨手記

在Tomcat 7.0.37使用JTDS 1.3.0 JDBC Driver + BoneCP Java  Connection Pool時發生錯誤(jdk1.7.0_17 x32 、win 7 x64)

java.sql.SQLException: No suitable driver found for jdbc

而Connection Pool的設定方式是如下:

// Construct com.jolbox.bonecp.BoneCPDataSource reference
DataSourceInfo dsInfo = mydsInfo();
Reference ref = new Reference(dsInfo.getJavaDS(),dsInfo.getFactoryName(),null);
ref.add(new StringRefAddr("driverClass",dsInfo.getDriverName()));
ref.add(new StringRefAddr("username",dsInfo.getUsername()));
ref.add(new StringRefAddr("password",dsInfo.getPassword()));
ref.add(new StringRefAddr("jdbcUrl",dsInfo.getDriverURL()));
ref.add(new StringRefAddr("partitionCount",dsInfo.getPartitionCount()));
ref.add(new StringRefAddr("minConnectionsPerPartition",dsInfo.getMinConnectionsPerPartition()));
ref.add(new StringRefAddr("maxConnectionsPerPartition",dsInfo.getMaxConnectionsPerPartition()));
ref.add(new StringRefAddr("acquireIncrement",dsInfo.getAcquireIncrement()));
ref.add(new StringRefAddr("poolAvailabilityThreshold",dsInfo.getPoolAvailabilityThreshold()));
ref.add(new StringRefAddr("idleConnectionTestPeriodInMinutes",dsInfo
.getIdleConnectionTestPeriodInMinutes()));
ref.add(new StringRefAddr("connectionTestStatement",dsInfo.getValidationQuery()));
ref.add(new StringRefAddr("idleMaxAgeInMinutes",dsInfo.getIdleMaxAgeInMinutes()));
ref.add(new StringRefAddr("statementsCacheSize",dsInfo.getStatementsCacheSize()));
ref.add(new StringRefAddr("releaseHelperThreads",dsInfo.getReleaseHelperThreads()));
ref.add(new StringRefAddr("statementReleaseHelperThreads",dsInfo
.getStatementReleaseHelperThreads()));
ref.add(new StringRefAddr("maxConnectionAgeInSeconds",dsInfo.getMaxConnectionAgeInSeconds()));
ref.add(new StringRefAddr("closeConnectionWatch",dsInfo.getCloseConnectionWatch()));
ref.add(new StringRefAddr("logStatementsEnabled",dsInfo.getLogStatementsEnabled()));
ref.add(new StringRefAddr("acquireRetryDelayInMs",dsInfo.getAcquireRetryDelayInMs()));
ref.add(new StringRefAddr("lazyInit",dsInfo.getLazyInit()));
ref.add(new StringRefAddr("acquireRetryAttempts",dsInfo.getAcquireRetryAttempts()));
ref.add(new StringRefAddr("disableJMX",dsInfo.getDisableJMX()));
ref.add(new StringRefAddr("queryExecuteTimeLimitInMs",dsInfo.getQueryExecuteTimeLimitInMs()));
ref.add(new StringRefAddr("disableConnectionTracking",dsInfo.getDisableConnectionTracking()));
ref.add(new StringRefAddr("serviceOrder",dsInfo.getServiceOrder()));
ref.add(new StringRefAddr("statisticsEnabled",dsInfo.getStatisticsEnabled()));

InitialContext ic = new InitialContext();
Context ctx = null;

try
{
ctx = (Context)ic.lookup("jndi");
}
catch(NameNotFoundException nnfe)
{
// 若找不到subContextName則需要建立新的SubContext
ctx = ic.createSubcontext("jndi");
}

try
{
ctx.lookup(dsInfo.getDsName());
}
catch(NameNotFoundException nnfe)
{
// 若找不到則需要rebind reference
ctx.rebind(dsName,ref);
}

資料庫連線取法如下:

Context ctx = null;
DataSource ds = null;
Connection conn = null;

try
{
ctx = new InitialContext();
ds = (DataSource)ctx.lookup(databaseAlias);
conn = ds.getConnection();
}
catch(NamingException e)
{
throw e;
}

引起這錯誤常見原因為下列幾項:

一、ClassPath沒加入合適的JDBC Driver

     Tomcat 7 Class Loader由Tomcat啟動開始載入ClassPath順序如下

      Bootstrap
          |
       System
          |
       Common
       /     \
  Webapp1   Webapp2 ...

     各啟動順序去尋找的檔案路徑

     Bootstrap ---> $JAVA_HOME/jre/lib/ext
     System --->系統環境設定
                       但透過$CATALINA_HOME/bin/catalina.sh or %CATALINA_HOME%\bin\catalina.bat

                       指令啟動Tomcat時，常常會忽略原先的設定
     Common --->$CATALINA_BASE/lib
     WebappX --->/WEB-INF/classes和/WEB-INF/lib
                          

二、錯誤的Jdbc Url設定

     JDBC URL正確範例:

        jdbc:jtds:sqlserver://localhost:1433/myDatabase;tds=8.0;lastupdatecount=true;sendStringParametersAsUnicode=false;appName=jTDS1.3.0; 

三、使用錯誤的JDBC Driver Implementation

      例如:須使用net.sourceforge.jtds.jdbc.Driver卻設定為net.sourceforge.jtds.jdbcx.JtdsDataSource

 我檢查環境設定確認jtds jdbc driver

 存在於WebApp/WEB-INF/lib/jtds-1.3.0.jar

 便將jtds-1.3.0.jar複製一份放置於$CATALINA_BASE/lib仍然發生相同錯誤

 最後把檔案放入$JAVA_HOME/jre/lib/ext才解決

 但Apache DBCP和Tomcat JDBC Pool則只需要在WebApp/WEB-INF/lib/置入JDBC Driver

參考資料:Apache Tomcat 7 Class Loader How-To