Some fairly good JDBC Questions 24-33

[JDBC Questions 1-12], [JDBC Questions 13-24]

Q25 What advantage is there to using prepared statements if I am using connection pooling or closing the connection frequently to avoid resource/connection/cursor limitations?

The ability to choose the 'best' efficiency ( or evaluate tradeoffs, if you prefer, ) is, at times, the most important piece of a mature developer's skillset. This is YAA ( Yet Another Area, ) where that maxim applies. Apparently there is an effort to allow prepared statements to work 'better' with connection pools in JDBC 3.0, but for now, one loses most of the original benefit of prepared statements when the connection is closed. A prepared statement obviously fits best when a statement differing only in variable criteria is executed over and over without closing the statement.

However, depending on the DB engine, the SQL may be cached and reused even for a different prepared statement and most of the work is done by the DB engine rather than the driver. In addition, prepared statements deal with data conversions that can be error prone in straight ahead, built on the fly SQL; handling quotes and dates in a manner transparent to the developer, for example.


Q26 Why do I get an UnsupportedOperationException?

JDBC 2.0, introduced with the 1.2 version of Java, added several capabilities to JDBC. Instead of completely invalidating all the older JDBC 1.x drivers, when you try to perform a 2.0 task with a 1.x driver, an UnsupportedOperationException will be thrown. You need to update your driver if you wish to use the new capabilities.


Q27 Can I reuse a Statement or must I create a new one for each query?

When using a JDBC compliant driver, you can use the same Statement for any number of queries. However, some older drivers did not always "respect the spec." Also note that a Statement SHOULD automatically close the current ResultSet before executing a new query, so be sure you are done with it before re-querying using the same Statement.


Q28 Will a call to PreparedStatement.executeQuery() always close the ResultSet from the previous executeQuery() if you dont you yourself?

A ResultSet is automatically closed by the Statement that generated it when that Statement is closed, re-executed, or is used to retrieve the next result from a sequence of multiple results.


Q29 How do you convert a java.sql.Timestamp to a java.util.Date?

While Timesteamp extends Date, it stores the fractional part of the time within itself instead of within the Date superclass. If you need the partial seconds, you have to add them back in.
Date date = new Date(ts.getTime() + (ts.getNanos() / 1000000 ));

Q30.What are the data access design patterns you use while writing the JDBC layers ?
Very good and important question. the answer to the question is very long. In summary you need to describe the DAO pattern. you can use Factory + interface inheritance to write the data access layer. I would write sometime in future on this.

Q31. When I create multiple Statements on same Connection, only the current Statement appears to be executed. What's the problem?

All JDBC objects are required to be threadsafe. Some drivers, unfortunately, implement this requirement by processing Statements serially. This means that additional Statements are not executed until the preceding Statement is completed.

Q32. Can a single thread open up mutliple connections simultaneously for the same database if yes how many?

The general answer to this is yes. If that were not true, connection pools, for example, would not be possible. this is drived from the underlying database setting the max connection limit set on the database.

Q33. What do you understand by connection timeout intervaal ?

Every database connection has the timeout interval set (depends on database configuration). This is the max inactivity interval for any connection. If not statement is executed within this time the underlying database would invalidate the connection.