Java SQL PreparedStatement and maintaining connection

0
votes

I'm creating a server-side Java task that executes the same SQL UPDATE every 60-seconds forever so it is ideal for using a java.sql.PreparedStatement.

I would rather re-connect to the database every 60-seconds than assume that a single connection will still be working months into the future. But if I have to re-generate a new PreparedStatement each time I open a new connection, it seems like it is defeating the purpose.

My question is: since the PreparedStatement is created from a java.sql.Connection does it mean that the connection must be maintained in order to use the PreparedStatement efficiently or is the PreparedStatement held in the database and not re-compiled with each new connection? I'm using postgresql at the present, but may not always.

I suppose I could keep the connection open and then re-open only when an exception occurs while attempting an update.

2
javasqldatabase-connectionprepared-statement
Thanks Luiggi and Raj for the answers. After some experimentation (with both PreparedStatement and Statement), I don't see much of a difference performance wise. So I'll just keep it simple and open and close the connection each time. – kevdev

2 Answers

1
votes

Use a database connection pool. This will maintain the connections alive in sleep mode even after closing them. This approach also saves performance for your application.

Despite the connection that created the PreparedStatement, the SQL statement will be cached by the database engine and there won't be any problems when recreating the PreparedStatement object.

1
votes

Set your connection timeout to the SQL execution time+few minutes.

Now, you can take 2 different approaches here -

  1. Check before executing the update, if false is returned then open new Connection

    if( connection == null  || !connection.isValid(0)) {
// open new connection and prepared statement
}

  2. Write a stored procedure in the Db, and call it passing necessary params. This is an alternate approach.

Regarding you approach of closing and opening db connection every 60 seconds for the same prepared statement, it does not sound like a good idea.