What the standard way in jdbc to manage lost conne

Question

My application sometimes can lost connection to MySQL database. I think that good solution will be schedule some timer to try to recconect after some time. How better it can be done? May be separate thread that try to connecto to db? Or exist the stardard practices ? Thanks.

Answer 1

JDBC is a simple API to abstract the operations of different database system. It makes something uniform, such different native types to java type.

However, the lost connection is another big issue. Using the connection pool library is better than writing a new one by yourself. It is too many details to implement a connection pool from scratch without bug.

Consider to use the mature library:

• Commons-DBCP
• bonecp
• etc

Commons DBCP is based on the Commons Pool. You should understand the configurable options both of them. bonecp is another new connection pool, no lock is its advantage.

Validated SQL String is important to check a connection is dead or alive. Lost connection checking enable by the validation string set.

Here is the dbcp configuration page: http://commons.apache.org/dbcp/configuration.html

It says:

NOTE - for a true value to have any effect, the validationQuery parameter must be set to a non-null string.

For example:

    dataSource.setValidationQuery(isDBOracle() ? "select 1 from dual" : "select 1");
    dataSource.setTestWhileIdle(true);
    dataSource.setTestOnReturn(true);
    dataSource.setRemoveAbandoned(true);
    dataSource.setRemoveAbandonedTimeout(60 * 3 /* 3 mins */);

    dataSource.setMaxIdle(30);
    dataSource.setMaxWait(1000 * 20 /* 20 secs*/);

Remind: If you use the Common DBCP in weblogic, don't forget the older Commons library in the server, it will drive your application use the different version. The prefer-web-inf-classes setting will help you.

Answer 2

JDBC is a great way to start building a java database application, but managing object mappings and connections / transactions can very rapidly lead to alot of boiler plate and rewriting of logic which has already been written many times by many programmers.

It is expected that you should lose/close a connection normally, unless you have a high throughput application, in which case you might keep several connections alive (this is known as connection pooling).

There are essentially 3 "high-level" approaches to maintaining efficient connections and transactions :

1) The simplest solution is to check when you are reusing a connection to make sure it is valid, or reopen it every time.

2) A more sophisticated solution is to use a connection pooling mechanism, such as the apache http://commons.apache.org/dbcp/ dbcp library.

3) Finally, in my opinion, the most maintainable solution is to use a JDBC framework like ibatis/hibernate which will , providing you a simple , declarative interface for managing object relational mapping / transactions / database state ---- while also transparently maintaining connection logic for you.

ALSO : If object relational mapping is not your thing, then you can use a framework such as DBUtils by apache to manage querying and connections, without the heavy weight data mapping stuff getting in the way.