JDBC Interview Questions-2

21) Will a call to PreparedStatement.executeQuery() always close the ResultSet from the previous executeQuery()?

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.

22) How do I upload SQL3 BLOB & CLOB data to a database?

Although one may simply extract BLOB & CLOB data from the database using the methods of the java.sql.CLOB and java.sql.BLOB, one must upload the data as normal java datatypes. The example below inserts a BLOB in the form of a byte[] and a CLOB in the form of a String into the database

Inserting SQL3 type data [BLOB & CLOB]

private void runInsert() { try { // Log this.log("Inserting values ... "); // Open a new Statement PreparedStatement stmnt = conn.prepareStatement( "insert Lobtest (image, name) values (?, ?)"); // Create a timestamp to measure the insert time Date before = new java.util.Date(); for(int i = 0; i <> // Set parameters stmnt.setBytes(1, blobData); stmnt.setString(2, "i: " + i + ";" + clobData); // Perform insert int rowsAffected = stmnt.executeUpdate(); } // Get another timestamp to complete the time measurement Date after = new java.util.Date(); this.log(" ... Done!"); log("Total run time: " + ( after.getTime() - before.getTime())); // Close database resources stmnt.close(); } catch(SQLException ex) { this.log("Hmm... " + ex); } }

23) What is the difference between client and server database cursors?

What you see on the client side is the current row of the cursor which called a Result (ODBC) or ResultSet (JDBC). The cursor is a server-side entity only and remains on the server side.

24) Are prepared statements faster because they are compiled? if so, where and when are they compiled?

Prepared Statements aren't actually compiled, but they are bound by the JDBC driver. Depending on the driver, Prepared Statements can be a lot faster - if you re-use them. Some drivers bind the columns you request in the SQL statement. When you execute Connection.prepareStatement(), all the columns bindings take place, so the binding overhead does not occur each time you run the Prepared Statement. For additional information on Prepared Statement performance and binding see JDBC Performance Tips on IBM's website.

25) Is it possible to connect to multiple databases simultaneously? Can one extract/update data from multiple databases with a single statement?

In general, subject, as usual, to the capabilities of the specific driver implementation, one can connect to multiple databases at the same time. At least one driver ( and probably others ) will also handle commits across multiple connections. Obviously one should check the driver documentation rather than assuming these capabilities.

As to the second part of the question, one needs special middleware to deal with multiple databases in a single statement or to effectively treat them as one database. DRDA ( Distributed Relational Database Architecture -- I, at least, make it rhyme with "Gerta" ) is probably most commonly used to accomplish this.

Oracle has a product called Oracle Transparent Gateway for IBM DRDA and IBM has a product called DataJoiner that make multiple databases appear as one to your application. No doubt there are other products available

26) 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.

27) 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.

28) What is JDBC, anyhow?

JDBC is Java's means of dynamically accessing tabular data, and primarily data in relational databases, in a generic manner, normally using standard SQL statements.

29) 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.

30) What is a three-tier architecture?

A three-tier architecture is any system which enforces a general separation between the following three parts:

1. Client Tier or user interface
2. Middle Tier or business logic
3. Data Storage Tier

Applied to web applications and distributed programming, the three logical tiers usually correspond to the physical separation between three types of devices or hosts: