K'隨手記

使用JTDS存取資料庫發生下列錯誤:

[java] java.sql.SQLException: commit() should not be called while in auto-commit mode.
[java] at net.sourceforge.jtds.jdbc.JtdsConnection.commit(JtdsConnection.java:2132)
[java] at com.jolbox.bonecp.ConnectionHandle.commit(ConnectionHandle.java:411)
[java] at tw.com.myApp.init.check.CheckInitParam.getMetadbListResult(CheckInitParam.java:43)
[java] at tw.com.myApp.init.check.CheckInitParam.isMetaDBValid(CheckInitParam.java:74)
[java] at tw.com.myApp.init.check.CheckUtil.systemCheck(CheckUtil.java:84)
[java] at tw.com.myApp.service.listener.myAppContextListener.setAppSystemInfo(myAppContextListener.java:100)
[java] at tw.com.myApp.service.listener.myAppContextListener.contextInitialized(myAppContextListener.java:196)
[java] at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4797)
[java] at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5291)
[java] at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)
[java] at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:901)
[java] at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:877)
[java] at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:633)
[java] at org.apache.catalina.startup.HostConfig.deployDescriptor(HostConfig.java:657)
[java] at org.apache.catalina.startup.HostConfig$DeployDescriptor.run(HostConfig.java:1637)
[java] at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
[java] at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)
[java] at java.util.concurrent.FutureTask.run(FutureTask.java:166)
[java] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
[java] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
[java] at java.lang.Thread.run(Thread.java:722)

原因是資料庫連線Autocommit為true，但卻又呼叫Commit()方法

程式範例如下:

//刪除會員編號為memberIDXXXX的會員

Connection conn = null;
MemberService mService = null;

try
{
   conn = GetConn.getConnection();

   //conn.getAutoCommit()  此處的值為true
   //conn.setAutoCommit(false);
   mService = new MemberService(conn);
   mService.delMember("memberIDXXXX");
   conn.commit();//當conn.getAutoCommit()=true時，造成JTDS發生錯誤，conn.rollback()也會引起相同錯誤
}
catch(NamingException e)
{
   e.printStackTrace();
}
catch(SQLException e)
{
   e.printStackTrace();
}
catch(Exception e)
{
   e.printStackTrace();
}
finally
{
   DbUtils.closeQuietly(conn);
   mService = null;
}

解決辦法:

1.設定資料連線autocommit為false，方法如上列紅字程式碼conn.setAutoCommit(false);

2.若是有使用Connection Pool時，請檢查資料連線的AutoCommit的屬性是否已設定為false