0

I'm stuck with MySQL connection in web service..

public class DBAccessLayer
{
    private Connection connection = null;
    private Statement statement = null;
    private String DBFormatter = "'";
    private String key;
    private static DataSource datasource = null;
    private static boolean _isInitiated = false;
    private static boolean _isInitializing = false;

    public DBAccessLayer()
    {
        key = ConstantValues.getKey();
        SetPoolSettings();
    }

    private void SetPoolSettings()
    {
        try
        {
            if (!_isInitiated && !_isInitializing)
            {
                _isInitializing = true;

                PoolProperties p = new PoolProperties();
                p.setUrl("jdbc:mysql://localhost:3306/DBName?autoReconnect=true");
                p.setDriverClassName("com.mysql.jdbc.Driver");
                p.setUsername("root");
                p.setPassword("password");
                p.setJmxEnabled(true);
                p.setTestWhileIdle(false);
                p.setTestOnBorrow(true);
                p.setValidationQuery("SELECT 1");
                p.setTestOnReturn(false);
                p.setValidationInterval(30000);
                p.setTimeBetweenEvictionRunsMillis(30000);
                p.setMaxActive(100);
                p.setInitialSize(10);
                p.setMaxWait(10000);
                p.setRemoveAbandonedTimeout(60);
                p.setMinEvictableIdleTimeMillis(30000);
                p.setMinIdle(10);
                p.setLogAbandoned(true);
                p.setRemoveAbandoned(true);
                p.setFairQueue(true);
                p
                    .setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
                datasource = new DataSource();
                datasource.setPoolProperties(p);

                _isInitiated = true;
            }
        }
        finally
        {
            _isInitializing = false;
        }
    }

    public void OpenConnection() throws SQLException
    {
        if (connection == null || connection.isClosed())
            connection = datasource.getConnection();

        if (statement == null || statement.isClosed())
            statement = connection.createStatement();

    }

    public Statement getStatement() throws Exception
    {
        if (connection == null || connection.isClosed())
            connection = datasource.getConnection();
        statement = connection.createStatement();
        return statement;
    }

    public void CloseConnection()
    {
        try
        {
            if (statement != null)
                statement.close();
        }
        catch (Exception ignore)
        {
            ignore.printStackTrace();
        }

        try
        {
            if (connection != null)
                connection.close();
        }
        catch (Exception ignore)
        {
            ignore.printStackTrace();
        }
    }
}

this is my class i'm using to connect with database in web service.. I have some quartz scheduled jobs running with same web service.

I call OpenConnection() method on each web service method call and on each job start and in finally block CloseConnection().

But i am regularly getting this error, while I'm closing connection only once in try - finally block.

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after statement closed.

so my web service method looks like

private DBAccessLayer _DBAccessLayer = null;

private DBAccessLayer getDBAccessLayer()
{
    if (_DBAccessLayer == null)
        _DBAccessLayer = new DBAccessLayer();
    rerurn _DBAccessLayer;
}

public void dummyCall()
{
    try
    {

        getDBAccessLayer.getStatement().executeQuery("Some query");

    }
    finally
    {
        getDBAccessLayer.CloseConnection();
    }
}
Naresh
  • 785
  • 1
  • 11
  • 23
  • How many instances of `DBAccessLayer` do exist during runtime? Hopefully one per call... keep in mind that a given Connection cannot be used in multiple threads in parallel. – home Sep 22 '12 at 06:34
  • Any specific reason for not using a standard connection pool? This code doesn't seem to be thread-safe and is being used in web service. – Vikdor Sep 22 '12 at 07:03
  • A new object of "DBAccessLayer" is created for each call. And i removed OpenConnection call and let it be handled by tomcat apache jdbc pool. @Vikdor that's why i use the jdbc pool,, and my web service is running on tomcat server – Naresh Sep 22 '12 at 15:22
  • Got one more error:- java.lang.NullPointerException at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:7008) with error message is null – Naresh Sep 22 '12 at 15:34
  • More narrowed the situation, these two errors are coming from one method that is our authentication module for every web service method call. In which we validate token and update date time in two queries. – Naresh Sep 24 '12 at 04:52
  • Finally, I removed the all calls to "CloseConnection", let the connection to be maintained by connection-pool itself. Remove Connection and Statement and generate them for each call because resultset is bind to statement. this solves the problem. – Naresh Nov 30 '13 at 10:03

0 Answers0