Back To Basics: JDBC Revisited
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.
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( )).
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.
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
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. 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. 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) ResultSet.CONCUR_READ_ONLY – read only ResultSets . ResultSet.CONCUR_UPDATABLE – updatable ResultSets move to the staging row ( rs.moveToInsertRow() ) update the staging row ( updateXXX ) insert the staging row ( rs.insertRow ) move to the new Row (rs.moveToCurrentRow) 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. 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.