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

06.06.2008
| 12124 views |
  • submit to reddit

We deal with numerous high level frameworks and abstractions in our day to day jobs, and sometimes we forget the basic basics. The little things that matter so much. I found myself curling up with a thick book only to spend hours, gain little, and forget fast. So I started jotting down the stuff that actually makes sense, with pointers to brush up my memory.

Types of Drivers

  • Type 1 : JDBC:ODBC bridge. These are to be used as a temporary solution to connect to databases and are inherently not scalable and have performance limitations. They may also be feature limited.
  • Type 2 : Native API. These Drivers depend on native libraries to communicate with the DB. They are basically a thin Java wrapper around the Native code using JNI. They perform better than Type 1 but portability is a problem as the native library may not be available for certain platforms.
  • Type3 : Net Protocol. These are fully Java drivers that expose the DB operations in a net friendly way. They also perform well and the portability is not much of an issue since there is no client side installation.
  • Type4 : Thin drivers. These are 100% Java drivers and communicate with the DB using the DB's native protocol. They perform the best nowadays and being 100% Java they are very portable.
Choosing a driver depends on the situation, the application's scalability requirements, cost, and performance expectations. As a rule of thumb, Type 4 drivers are the default choice, as practically all database vendors provide one for free and they perform really well.

DriverManager and Connections

You can register a driver by:
  • Creating an instance of the Driver (impl) class , and using DriverManager.registerDriver()
  • Setting the property jdbc.drivers (pass -Djdbc.drivers=)
  • Loading the driver class (Class.forName()).

The last one is the most popular, as this lets you keep the Driver's name as an externalized Property (e.g., in a Properties file).

For any DB operation you need a Connection to the DB. This can be got by simply calling
DriverManager.getConnection(url, username, password)
. The URL is the JDBC URL to the DB, and the user name and password have to be valid to get a connection.

Connections are limited and are heavy weight objects. Creating a Connection instance requires several round trips to the DB and is very expensive,hence they have to be reused as much as possible so we have Connection Pooling. Always close Connections. If you see unexpected SQLExceptions that complain about not having a connection, then try to trace out if you have connection leaks.

DataSources have the capability for Connection Pooling and is usually configured in a vendor specific way. DataSources are mostly accessed though a naming service like JNDI.

Application1 / app server
Create a DataSource using the vendors way of doing it.
Bind the DataSource to a JNDI context under a name.

Application 2 / client app
Look-up the DataSource from the JNDI using the name used to bind it.
call getConnection on the DS.

Statements and ResultSets

There are 3 flavors of Statement objects and they are basically a pipeline to send an SQL statements to the DB and get results back in a ResultSet object. All the Statement objects re obtained from the Connection object.

  • Statement is the basic type and represents a dynamic SQL statement. It is the parent of PreparedStatement. each time you execute a Statement, the database has to do a hard parse on the SQL and execute it. This can yeild poor performance for frequently executing SQLs. On the other hand the creation overhead for a Statement instance is considerably smaller than the PreparedStatement instance.
  • PreparedStatement represents a pre-compiled SQL statement. These statements are created with placeholders denoted by '?' where you can substitute values. The first time the PreparedStatement is executed the database precompiles it and a hard parse is not necessary to subsequent executions, as the execution plan is already created. This improves the performance for frequently executed SQLs but the creation or “preparing” overhead is more than a Statement. Some database drivers like Oracle driver can use numbered placeholders than the generic '?'. The '?' are given values and they are indexed starting from 1.
  • CallableStatements are used to execute stored procedures and the they have the capability to return output parameters for the stored procedure. The IN parameters are set using the setXX methods and the OUT parameters are registered using the registerOutParameter method. As with other types of Statements the indexes for the placeholder '?' starts at 1. INOUT parameters are handled by using the same index to set the IN parameter and registering the OUT parameter.

Remember that ResultSets are connected to their Statement objects which are connected to their Connection objects and the ResultSet represents an open cursor in the database, so failure to close the ResultSet and the statements will lead to the database eventually running out of cursors. Also you cannot have more than one open cursor associated with the same Connection (some drivers will throw SQLExceptions). If you need to re-use a Statement object, save the data contained in the ResultSet in a vector or a list first.

All these classes will have the execute(), executeUpdate() and executeQuery() methods:

  • executeUpdate() is used to send a DML like INSERT, UPDATE,ALTER or EXEC. They do not return database rows, and hence the return type for this method is an int, that gives the number of rows that were affected by the SQL. in case of ALTER or EXEC , the return will always be 0, since the SQL will not affect any rows.
  • executeQuery() is used for executing a SELECT statement and the selected rows are returned in a ResultSet.

  • The execute() method is generic and can be used for both types of SQL. But execute() is slower than the other two. Also, execute() returns a boolean , true if a ResultSet was returned and false if an update count is waiting. use getResultSet() or getUpdateCount() to get the relevant info. Generally its better to use the specific methods, as they perform better and require lesser coding.

The fetch size and fetch direction can be provided to a statement to optimize the query, but this is database dependent, and the driver may even choose to disregard and make decisions on its own. A ResultSet is always pointing to “before the first row”. the first row is obtained by calling next() on the ResultSet . The call to next returns false if a row is not available

JDBC 2.0 adds the ability of random access to ResultSet . Its controlled by setting

  • ResultSet.TYPE_FORWARD_ONLY (forward only iteration)
  • ResultSet.TYPE_SCROLL_INSENSITIVE (is a snapshot of the results)
  • ResultSet.TYPE_SCROLL_SENSITIVE.(is sensitive to the back end changes to data)

JDBC 2.0 defines updatable rows; to update a row, the ResultSet should be scrollable and the underlying SQL refers a single table. This is referred to as ResultSet concurrency.

  • ResultSet.CONCUR_READ_ONLY – read only ResultSets .
  • ResultSet.CONCUR_UPDATABLE – updatable ResultSets

Inserting rows are a 4 stage process that includes a staging row.

  1. move to the staging row ( rs.moveToInsertRow() )
  2. update the staging row ( updateXXX )
  3. insert the staging row ( rs.insertRow )
  4. move to the new Row (rs.moveToCurrentRow)

JDBC 3.0 adds ResultSet holdability that determine if the DB cursor is held even after a transaction has committed or if the cursor is closed on a commit. These can be specified when a connection is created.

  • ResultSet.HOLD_CURSORS_OVER_COMMIT – holds cursors and the ResultSet is usable even after the commit.
  • ResultSet.CLOSE_CURSORS_AT_COMMIT – closes the cursorr at a commit.

Extraction of data from the ResultSet is using the getXXX methods and you can either specify the column indexes(starting at 1) or column names(case in-sensitive)

The ResultSetMetaData object gives details about the table and columns, like the column count , the column names and the tale name. Always remember the resource utilization in jdbc. ResultSets cache data locally and they may re-query the db if it needs to.

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

Comments

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.