DataSourcePool Service returning null/closed connection

Avatar

Avatar

anuj_pathak1

Avatar

anuj_pathak1

anuj_pathak1

14-01-2019

To connect to mysql database, we defined JDBC pool service using which we are reading datasource and getting connection to read /write data.

In the back java code finally block, we are doing connection.close(); when we are done with everything but now we are running into issue where its complaining about connection is closed or returning null connection object.

caused by: java.sql.SQLException: Connection is closed.

    at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.checkOpen(PoolingDataSource.java:175) [day.commons.datasource.jdbcpool:1.0.24]

    at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:301) [day.commons.datasource.jdbcpool:1.0.24]

before this exception, below is block of code from which i can see datasource is not null but dataSource.getConnection() call is returning null value along with above exception.

dataSourceService=dataSourceService!=null?dataSourceService:getFromBundle();

DataSource dataSource = (DataSource)dataSourceService.getDataSource(this.defaultDataSource);

logger.info(" getConnection dataSource= "+dataSource);  // here i can see not null value in log file

this.connection = this.connection!=null?this.connection:(dataSource!=null?dataSource.getConnection():null);

logger.info("data source connection= "+connection);  //null value printed here in log file

Do we need to remove connection.close(); calls which we added as best practice or it has something to with pool service OSGI config?

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar

workflowuser

Employee

Avatar

workflowuser

Employee

workflowuser
Employee

18-01-2019

Had the same problem a while back

The settings specified here seem to work for me

Tomcat JDBC Connection Pool configuration for production and development – CodingpediaOrg

Answers (5)

Answers (5)

Avatar

Avatar

smacdonald2008

Total Posts

12.7K

Likes

1.4K

Correct Reply

2.3K

Avatar

smacdonald2008

Total Posts

12.7K

Likes

1.4K

Correct Reply

2.3K
smacdonald2008

14-01-2019

That is an older community article - for 6. Please refer to this newer one -- Adobe Experience Manager Help | Querying MySQL data using an Adobe Experience Manager 6.4 DataSource...

There is also a video showing it all working.

Avatar

Avatar

smacdonald2008

Total Posts

12.7K

Likes

1.4K

Correct Reply

2.3K

Avatar

smacdonald2008

Total Posts

12.7K

Likes

1.4K

Correct Reply

2.3K
smacdonald2008

14-01-2019

See AEM product docs here too -- Connecting to SQL Databases

Avatar

Avatar

Gaurav-Behl

MVP

Avatar

Gaurav-Behl

MVP

Gaurav-Behl
MVP

14-01-2019

that doesn't make any sense to me. I would probably wait for other's feedback on it.

Avatar

Avatar

anuj_pathak1

Avatar

anuj_pathak1

anuj_pathak1

14-01-2019

thanks for response gaurav,

I think i looked at it before but if you look at here Adobe Experience Manager Help | Injecting a DataSourcePool Service into an Adobe Experience Manager ...  line # 115 of CustomerServiceImp.java, they are closing connection in finally block but not in other DB calls like in  getCustomerData().

Avatar

Avatar

Gaurav-Behl

MVP

Avatar

Gaurav-Behl

MVP

Gaurav-Behl
MVP

14-01-2019

If you are injecting DataSourcePoolService then you don't need to manage the boiler plate code to open/close the connection in your code which is the recommended way. The container would handle the lifecycle of service/connections.

Check -

Adobe Experience Manager Help | Querying MySQL data using an Adobe Experience Manager 6.4 DataSource...

Adobe Experience Manager Help | Injecting a DataSourcePool Service into an Adobe Experience Manager ...