Should I persist JDBC connection or not?

Question

For my integration tests I have build common validation class which has methods like

public void countcheck(table1, table 2){ // makes a JDBC connection and run the query to check counts and then closes the connection };

public void nullcheck(Table, ColumnName) {// makes a JDBC connection and the run query to make sure there are no Nulls and then closes the connection );

and so on. I distribute these common validation methods as Jars for integration testers. My dilemma is whether I should open and close connection for each method , I don't want testers to worry about opening and closing connections and just worry about calling validation methods. I am looking for alternative design or this is something good enough. My worry is if there in a test suite there are 10 tests then there would be 10 connections going and closing which perhaps is not a good sign? I want testers to worry about makig and closing connections. Its a Junit based test framework and DB is Oracle.

Your question is not very clear. If you worry about creating/releasing connection , a connection pool will make that as pretty fast. — Jayan, Apr 02 '16 at 15:12
@BhaskarMishra Instead why not use a connection pool to get the connection? — Madhusudana Reddy Sunnapu, Apr 02 '16 at 15:14
Thanks, I am slightly new to this, connection pool makes sense, so I shall create a pool as the tester kicks of the tests I guess. — Bhaskar Mishra, Apr 02 '16 at 15:18

score 0 · Answer 1 · edited May 23 '17 at 11:50

You should not "bake in" the decision on handling DB connections into your code. Instead, you should defer this decision to people deploying your framework.

If they decide to reuse connections, they could configure connection pool for that; if they decide to open and close connections every time, they could configure the framework without connection pooling.

Your task is reduced to providing the enabling technology to them.

score 0 · Answer 2 · answered Apr 02 '16 at 15:27

I typically pass a DataSource into such Objects (constructors) and get the connection on each business Method. In standalone mode this might mean it is not using a proper pool, but when you onlyexecute a handfull of statements thats no problm.

In production you can then pass in a real pool. The advantage of keeping a data source around as opposed to a Connection is, that this scenario has better self-healing capabilities and reducesthe number of active connections. (It might be not so important in test scenarios, but whynot have generally useable validators).

Validator(DataSource ds) {
    this.ds = ds;
}
validate(String val, ...) {
    try (Connection c = ds.getConnection()) {
        ...
    }
}

Most drivers have a native DataSource, but you can also have a genericone which implements getConnection() to return DriverManager.getConnection(url);. It is also a small interface easily mockable.

If your tests get bigger, you use a simple pooling datasource like the one from Apache Commons DBCP DriverAdapterCPDS.

Should I persist JDBC connection or not?

2 Answers2