SQL Zone is brought to you in partnership with:

Jeevan has posted 2 posts at DZone. View Full User Profile

Back To Basics: JDBC Revisited

  • submit to reddit
Transactions and Batch Processing

The most primitive way to control transactions is to use the setAutoCommit() method. enabling auto commits forces a database commit after each operation. Its usually best to set this to false and deal with commits , though its more coding. It gives more control and often better performance. But remember to invoke the commit method once the db operation is done and rollback if an exception was caught.

Batch processing is essentially like transactions, but they are limited to a single Statement. use the method Statement.addBatch() and executeBatch()

RowSets are a new spec and they can be seen as ResultSets that can be used like JavaBeans. Some kinds of RowSets can be disconnected, ie, they cache all the data and they need not be backed by a live connection to the database, making them ideal to encapsulate data and pass them around.

  • How to find the number of rows returned?

    If the ResultSet is scrollable, move to the last row and call getRow, which returns the row number.
    In most cases a better soln will be to issue a second SQL with a count(*).
    ResultSet navigation does not seem to work.
    If using JDBC 2.0 navigation methods , make sure that the driver supports the methods.
  • Why is it unsafe to pass Resultset objects around?

    The ResultSet objects are backed by the Statement and Connection objects. if they go out of scope or get closed, the ResultSet cannot get access the data., except for the data it cached. Also since they represent open cursors on the database, passing the ResultSet objects around causes the database to run out of cursors.
  • What are invalid cursor state error messages?

    You get these when the ResultSet is out of a valid range., like trying to get data from a ResultSet before calling next(), or when the ResultSet has scrolled beyond last row.
  • How to handle nulls?

    Database nulls are different from the Java 'null' value. DB nulls for an INTEGER column may be converted to 0. To properly handle this, get the data with the getXXX method and then invoke the wasNull() method on the ResultSet. The method works only after the data has been extracted from the ResultSet.
  • How to handle BLOBS and CLOBS ?

    BLOB requires java.sql.Blob but CLOB is provided by java.sql.Clob. You get data as raw bytes, with a BLOB, and characters in a CLOB

    The new MySQL and Oracle 10 drivers provide the getString() method that can return the whole CLOB .
  • What is the best collection class to store ResultSet data ?

    ArrayList or LinkedList.
    ArrayList has a constant 'average' cost for appending to the end of the list as the backing array may sometimes require re-allocation. Linked lists have the constant cost.Insertions in random locations are better with LinkedLists as the cost is constant. ArrayLists require to shift the following elements.

    ArrayLists incur space overhead in the form of reserve space at the end of the array. Linked Lists incur even more space overhead for Entry objects(this is per element).

    Depending on the usage, a Map may be a relevant choice too.
Published at DZone with permission of its author, Jeevan Joseph.

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)


Francis Perreault replied on Fri, 2008/06/13 - 11:52am

Thanks for this, it's good to remember that JPA is not the only way to access a database!

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.