JDBC Interview Questions and Answers

How do I create a java.sql.Date object?
java.sql.Date descends from java.util.Date, but uses only the year, month and day values. There are two methods to create a Date object. The first uses a Calendar object, setting the year, month and day portions to the desired values. The hour, minute, second and millisecond values must be set to zero. At that point, Calendar.getTime().getTime() is invoked to get the java.util.Date milliseconds. That value is then passed to a java.sql.Date constructor:

Calendar cal = Calendar.getInstance();
// set Date portion to January 1, 1970
cal.set( cal.YEAR, 1970 );
cal.set( cal.MONTH, cal.JANUARY );
cal.set( cal.DATE, 1 );

cal.set( cal.HOUR_OF_DAY, 0 );
cal.set( cal.MINUTE, 0 );
cal.set( cal.SECOND, 0 );
cal.set( cal.MILLISECOND, 0 );

java.sql.Date jsqlD =
new java.sql.Date( cal.getTime().getTime() );

The second method is java.sql.Date's valueOf method. valueOf() accepts a String, which must be the date in JDBC time escape format - "yyyy-mm-dd". For example,

java.sql.Date jsqlD = java.sql.Date.valueOf( "2010-01-31" );
creates a Date object representing January 31, 2010. To use this method with a Calendar object, use:

java.sql.Date jsqlD = java.sql.Date.valueOf(
cal.get(cal.YEAR) + ":" +
cal.get(cal.MONTH) + ":" +
cal.get(cal.DATE) );

which produces a Date object with the same value as the first example.

How do I create a java.sql.Time object?
java.sql.Time descends from java.util.Date, but uses only the hour, minute and second values. There are two methods to create a Time object. The first uses a Calendar object, setting the year, month and day portions to January 1, 1970, which is Java's zero epoch. The millisecond value must also be set to zero. At that point, Calendar.getTime().getTime() is invoked to get the time in milliseconds. That value is then passed to a Time constructor:

Calendar cal = Calendar.getInstance();
// set Date portion to January 1, 1970
cal.set( cal.YEAR, 1970 );
cal.set( cal.MONTH, cal.JANUARY );
cal.set( cal.DATE, 1 );

cal.set( cal.MILLISECOND, 0 );
java.sql.Time jsqlT =
new java.sql.Time( cal.getTime().getTime() );

The second method is Time's valueOf method. valueOf() accepts a String, which must be the time in JDBC time escape format - "hh:mm:ss". For example,
java.sql.Time jsqlT = java.sql.Time.valueOf( "18:05:00" );
creates a Time object representing 6:05 p.m. To use this method with a Calendar object, use:
java.sql.Time jsqlT = java.sql.Time.valueOf(
cal.get(cal.HOUR_OF_DAY) + ":" +
cal.get(cal.MINUTE) + ":" +
cal.get(cal.SECOND) );

which produces a Time object with the same value as the first example.

What scalar functions can I expect to be supported by JDBC?
JDBC supports numeric, string, time, date, system, and conversion functions on scalar values. For a list of those supported and additional information, see section A.1.4 Support Scalar Functions in the JDBC Data Access API For Driver Writers. Note that drivers are only expected to support those scalar functions that are supported by the underlying DB engine.

What does setFetchSize() really do?
The API documentation explains it pretty well, but a number of programmers seem to have a misconception of its functionality. The first thing to note is that it may do nothing at all; it is only a hint, even to a JDBC Compliant driver. setFetchSize() is really a request for a certain sized blocking factor, that is, how much data to send at a time.
Because trips to the server are expensive, sending a larger number of rows can be more efficient. It may be more efficient on the server side as well, depending on the particular SQL statement and the DB engine. That would be true if the data could be read straight off an index and the DB engine paid attention to the fetch size. In that case, the DB engine could return only enough data per request to match the fetch size. Don't count on that behavior. In general, the fetch size will be transparent to your program and only determines how often requests are sent to the server as you traverse the data.
Also, both Statement and ResultSet have setFetchSize methods. If used with a Statement, all ResultSets returned by that Statement will have the same fetch size. The method can be used at any time to change the fetch size for a given ResultSet. To determine the current or default size, use the getFetchSize methods.

Is there a practical limit for the number of SQL statements that can be added to an instance of a Statement object
While the specification makes no mention of any size limitation for Statement.addBatch(), this seems to be dependent, as usual, on the driver. Among other things, it depends on the type of container/collection used. I know of at least one driver that uses a Vector and grows as needed. I've seen questions about another driver that appears to peak somewhere between 500 and 1000 statements. Unfortunately, there doesn't appear to be any metadata information regarding possible limits. Of course, in a production quality driver, one would expect an exception from an addBatch() invocation that went beyond the command list's limits.

How can I determine whether a Statement and its ResultSet will be closed on a commit or rollback?
Use the DatabaseMetaData methods supportsOpenStatementsAcrossCommit() and supportsOpenStatementsAcrossRollback().

How do I get runtime information about the JDBC Driver?
Use the following DatabaseMetaData methods:
getDriverMajorVersion()
getDriverMinorVersion()
getDriverName()
getDriverVersion()

How do I create an updatable ResultSet?
Just as is required with a scrollable ResultSet, the Statement must be capable of returning an updatable ResultSet. This is accomplished by asking the Connection to return the appropriate type of Statement using Connection.createStatement(int resultSetType, int resultSetConcurrency). The resultSetConcurrency parameter must be ResultSet.CONCUR_UPDATABLE. The actual code would look like this:

Statement stmt = con.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE );

Note that the spec allows a driver to return a different type of Statement/ResultSet than that requested, depending on capabilities and circumstances, so the actual type returned should be checked with ResultSet.getConcurrency().

How can I connect to an Oracle database not on the web server from an untrusted applet?
You can use the thin ORACLE JDBC driver in an applet (with some extra parameters on the JDBC URL). Then, if you have NET8, you can use the connection manager of NET8 on the web server to proxy the connection request to the database server.

How can I insert multiple rows into a database in a single transaction?
//turn off the implicit commit
Connection.setAutoCommit(false);
//..your insert/update/delete goes here
Connection.Commit();
a new transaction is implicitly started.
JDBC 2.0 provides a set of methods for executing a batch of database commands. Specifically, the java.sql.Statement interface provides three methods: addBatch(), clearBatch() and executeBatch(). Their documentation is pretty straight forward.
The implementation of these methods is optional, so be sure that your driver supports these.

Page Numbers : 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

Have a Question ? post your questions here. It will be answered as soon as possible.

Check Java Interview Questions for more Java Interview Questions with answers

Check Structs Interview Questions for more Structs Interview Questions with answers

Check Servlet Interview Questions for more Servlet Interview Questions with answers