Getting Exception : Error configuring AutoCommit. Your driver may not support getAutoCommit()

[Gopalakrishnan]

Hi MyBatis Team,

I'm getting the below exception:

Exception in thread "main"
org.apache.ibatis.exceptions.PersistenceException:
### Error opening session. Cause:
org.apache.ibatis.transaction.TransactionException: Error configuring
AutoCommit. Your driver may not support getAutoCommit() or
setAutoCommit(). Requested setting: false. Cause:
java.sql.SQLException: Closed Connection
### Cause: org.apache.ibatis.transaction.TransactionException: Error
configuring AutoCommit. Your driver may not support getAutoCommit()
or setAutoCommit(). Requested setting: false. Cause:
java.sql.SQLException: Closed Connection
at
org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:
8)
at
org.apache.ibatis.session.defaults.DefaultSqlSessionFactory.openSessionFromDataSource(DefaultSqlSessionFactory.java:
83)
at
org.apache.ibatis.session.defaults.DefaultSqlSessionFactory.openSession(DefaultSqlSessionFactory.java:
32)
at
org.apache.ibatis.session.SqlSessionManager.openSession(SqlSessionManager.java:
98)
at org.apache.ibatis.session.SqlSessionManager
$SqlSessionInterceptor.invoke(SqlSessionManager.java:267)
at $Proxy16.insert(Unknown Source)
at
org.apache.ibatis.session.SqlSessionManager.insert(SqlSessionManager.java:
182)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:
59)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:25)
at $Proxy27.insert(Unknown Source)
at
com.metricstream.itgrc.ucf.service.AssetsList.processBasicInforType(AssetsList.java:
101)
at
com.metricstream.itgrc.ucf.service.AssetsList.processAssetItemType(AssetsList.java:
50)
at com.metricstream.itgrc.ucf.service.AssetsList.load(AssetsList.java:
35)
at
com.metricstream.itgrc.ucf.service.AssetLoader.process(AssetLoader.java:
24)
at
com.metricstream.itgrc.ucf.service.AssetLoader.main(AssetLoader.java:
31)
Caused by: org.apache.ibatis.transaction.TransactionException: Error
configuring AutoCommit. Your driver may not support getAutoCommit()
or setAutoCommit(). Requested setting: false. Cause:
java.sql.SQLException: Closed Connection
at
org.apache.ibatis.transaction.jdbc.JdbcTransaction.setDesiredAutoCommit(JdbcTransaction.java:
51)
at
org.apache.ibatis.transaction.jdbc.JdbcTransaction.<init>(JdbcTransaction.java:
19)
at
org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory.newTransaction(JdbcTransactionFactory.java:
15)
at
org.apache.ibatis.session.defaults.DefaultSqlSessionFactory.openSessionFromDataSource(DefaultSqlSessionFactory.java:
78)
... 13 more
Caused by: java.sql.SQLException: Closed Connection
at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:
112)
at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:
146)
at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:
208)
at
oracle.jdbc.driver.PhysicalConnection.setAutoCommit(PhysicalConnection.java:
1057)
at
org.apache.ibatis.transaction.jdbc.JdbcTransaction.setDesiredAutoCommit(JdbcTransaction.java:
46)
... 16 more

DataSourceProvider Code:
package com.metricstream.itgrc.core.service;

import java.sql.SQLException;

import javax.sql.DataSource;

import com.google.inject.Inject;
import com.google.inject.Provider;
import com.google.inject.name.Named;

public class ItgrcDataSourceProvider implements Provider<DataSource> {
private final DataSource dataSource;

public ItgrcDataSourceProvider() {
dataSource = new ItgrcDataSource();
}

@Inject(optional = true)
public void setLoginTimeout(@Named("JDBC.loginTimeout") final int
loginTimeout) {
try {
this.dataSource.setLoginTimeout(loginTimeout);
} catch (SQLException e) {
throw new RuntimeException("Impossible to set login
timeout '" + loginTimeout + "' to Unpooled Data Source", e);
}
}

@Inject(optional = true)
public void setAutoCommit(@Named("JDBC.autoCommit=false") final
boolean autoCommit) {
setAutoCommit(autoCommit);
}

@Override
public DataSource get() {
return dataSource;
}
}

DataSource Code:
package com.metricstream.itgrc.core.service;

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import javax.sql.DataSource;

import com.metricstream.itgrc.logger.AppLog;

public class ItgrcDataSource implements DataSource {
private Connection con = null;
private static final String CLASS_ID =
ItgrcDataSource.class.getName();

@Override
public PrintWriter getLogWriter() throws SQLException {
return null;
}

@Override
public void setLogWriter(PrintWriter out) throws SQLException {
}

@Override
public void setLoginTimeout(int seconds) throws SQLException {
}

@Override
public int getLoginTimeout() throws SQLException {
return 0;
}

@Override
public <T> T unwrap(Class<T> iface) throws SQLException {
return null;
}

@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
return false;
}

@Override
public Connection getConnection() throws SQLException {
synchronized (this) {
if (con == null) {
try {
String driverName =
"oracle.jdbc.driver.OracleDriver";
Class.forName(driverName);
String serverName = "server.metricstream.com";
String portNumber = "1521";
String sid = "dev11g";
String url = "jdbc:oracle:thin:@" + serverName +
":" + portNumber + ":" + sid;
String username = "user";
String password = "password";
con = DriverManager.getConnection(url, username,
password);
con.setAutoCommit(true);
} catch (SQLException e) {
AppLog.log(CLASS_ID, "SQLException", e);
throw e;
} catch (ClassNotFoundException e) {
AppLog.log(CLASS_ID, "ClassNotFoundException", e);
}
}
}

return con;
}

@Override
public Connection getConnection(String username, String password)
throws SQLException {
return null;
}
}

MyBatisModule Code:
package com.metricstream.itgrc.ucf.guice.modules;

import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;
import org.mybatis.guice.MyBatisModule;

import com.metricstream.itgrc.core.service.ItgrcDataSourceProvider;

public class UcfMybatisModule extends MyBatisModule {

@Override
protected void initialize() {
environmentId("development");
bindTransactionFactoryType(JdbcTransactionFactory.class);
bindDataSourceProviderType(ItgrcDataSourceProvider.class);
addMapperClasses("com.metricstream.itgrc.ucf.abator.dao");
}
}

PropertyModule Code:
package com.metricstream.itgrc.ucf.guice.modules;

import java.util.Properties;

import com.google.inject.Binder;
import com.google.inject.Module;
import com.google.inject.name.Names;

public class UcfPropertyModule implements Module {

@Override
public void configure(Binder arg0) {
Names.bindProperties(arg0, createTestProperties());
}

static Properties createTestProperties() {
final Properties myBatisProperties = new Properties();
myBatisProperties.setProperty("JDBC.autoCommit", "false");
myBatisProperties.setProperty("poolPingQuery", "select 1 from
dual");
myBatisProperties.setProperty("poolPingEnabled", "true");
return myBatisProperties;
}
}

Main Method:
package com.metricstream.itgrc.ucf.service;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;

import com.google.inject.Inject;
import com.google.inject.Injector;
import com.metricstream.itgrc.core.exceptions.AppException;
import com.metricstream.itgrc.core.service.IParser;
import com.metricstream.itgrc.ucf.annotation.AssetParser;
import com.metricstream.itgrc.ucf.domain.assets.UCFAssetsList;
import com.metricstream.itgrc.ucf.utils.UcfSession;

public class AssetLoader {
@Inject
@AssetParser
private IParser parser;

public void process(InputStream xmlStream) throws AppException {
UCFAssetsList ucfAssetsList = (UCFAssetsList)
parser.parse(xmlStream);
Injector injector = UcfSession.getInjector();
AssetsList assetsList =
injector.getInstance(AssetsList.class);
assetsList.load(ucfAssetsList);
}

public static void main(String args[]) throws
FileNotFoundException, AppException {
Injector injector = UcfSession.getInjector();
AssetLoader assetLoader =
injector.getInstance(AssetLoader.class);
FileInputStream xmlStream = new
FileInputStream("UCF_Assets_List.xml");
assetLoader.process(xmlStream);
}
}

POM File:
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.0.6</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-guice</artifactId>
<version>3.2</version>
</dependency>
<dependency>
<groupId>com.google.code.guice</groupId>
<artifactId>guice</artifactId>
<version>3.0</version>
</dependency>

When I use only MyBatis, the code works. When I club it with Guice,
I'm getting the above said exception. Please let me know if I'm
missing anything in the property settings.

Thanks,
GP.

[Gopalakrishnan]

Did some more analysis on this and realized that the issue is not with
the AutoCommit option, but rather with MyBatis closing the connection
immediately. How can I stop this. Is there a property or setting that
I can use to prevent such connection closures.
Note: As per the project, I cannot set MyBatis to manage the
connections. System will get a connection from a Pool (that is
internally managed) and once the db operation is done, then the
connection has to be returned back to the pool.

Please help.

Thanks
GP

[Gopalakrishnan]

Solved the issue.
Instead of binding JdbcTransactionFactory.class in MyBatisModule,
wrote a custom TransactionFactory which in turn called a Transaction
whose connection close method is handled properly (not to close the
connection).

Transaction Factory Code:
package com.metricstream.itgrc.core.mybatis;

import java.sql.Connection;
import java.util.Properties;

import org.apache.ibatis.transaction.Transaction;
import org.apache.ibatis.transaction.TransactionFactory;

public class ItgrcJdbcTransactionFactory implements TransactionFactory
{
@Override
public void setProperties(Properties props) {
}

@Override
public Transaction newTransaction(Connection conn, boolean
autoCommit) {
return new ItgrcJdbcTransaction(conn, autoCommit);
}
}

Transaction Code:
package com.metricstream.itgrc.core.mybatis;

import org.apache.ibatis.transaction.Transaction;
import org.apache.ibatis.transaction.TransactionException;

import java.sql.Connection;
import java.sql.SQLException;

public class ItgrcJdbcTransaction implements Transaction {

protected Connection connection;

public ItgrcJdbcTransaction(Connection connection, boolean
desiredAutoCommit) {
this.connection = connection;
setDesiredAutoCommit(desiredAutoCommit);
}

@Override
public Connection getConnection() {
return connection;
}

@Override
public void commit() throws SQLException {
if (!connection.getAutoCommit()) {
connection.commit();
}
}

@Override
public void rollback() throws SQLException {
if (!connection.getAutoCommit()) {
connection.rollback();
}
}

@Override
public void close() throws SQLException {
}

protected void setDesiredAutoCommit(boolean desiredAutoCommit) {
try {
if (connection.getAutoCommit() != desiredAutoCommit) {
connection.setAutoCommit(desiredAutoCommit);
}
} catch (SQLException e) {
// Only a very poorly implemented driver would fail here,
// and there's not much we can do about that.
throw new TransactionException("Error configuring
AutoCommit. " + "Your driver may not support getAutoCommit() or
setAutoCommit(). " + "Requested setting: " + desiredAutoCommit + ".
Cause: " + e, e);
}
}

protected void resetAutoCommit() {
try {
if (!connection.getAutoCommit()) {
// for compatibility we always use true, as some
drivers don't like being left in "false" mode.
connection.setAutoCommit(true);
}
} catch (SQLException e) {
throw new TransactionException("Error configuring
AutoCommit. " + "Your driver may not support getAutoCommit() or
setAutoCommit(). Cause: " + e, e);
}
}
}

You can notice, the close method does nothing.
Please let me know if you see any potential flaw in this code.

Thanks
GP

> ...
>
> read more »

[Eduardo]

Yep. That error is when getting a new connection, not when closing it.

But... why does MyBatis would close a connection that has just got
from the driver?

Where did you find that close() ?

[Gopalakrishnan]

@ Transaction interface. As part of guice/mybatis, the
TransactionFactory has to be bind in MyBatisModules.
Earlier I was using the inbuilt JdbcTransactionFactory which in turn
calls the JdbcTransaction class (which is an implementation of
Transaction).
JdbcTransaction close method closes the connection which is not
desired for my use-case. So I had to write my own custom Transaction
implementation which does nothing when the close method is called.

- GP