Database independency rules
Overview
Table B.3. Database independency rules
JDBC_001: Do Not Hard Code A JDBC Driver Class Name
Don't hard code the database driver class name when loading it using the Class.forName method. It is better store the driver class name in an external configuration file that is read when the database connection must be created.
JDBC_002: Do Not Hard Code A JDCB Connection URL
Don't hard code the database connection URL. It is better to store this URL in an external configuration file that is read when the database connection must be created.
If possible try not include the username and password in the URL but store them separately. This allows you to encrypt your password and to reuse the same username password for different URLs (in the case where you have to support more than one database).
JDBC_003: Do Not Use A Driver Managment Method Of
java.sql.DriverManager
The java.sql.DriverManager is responsible for managing the known JDBC driver classes. Although the DriverManager has some method related to the management of drivers (the register and deregister methods) they should never be called directly.
The JDBC API states that all driver implementation should have a static initializer that calls the DriverManager.register method when the driver class is loaded. This means that when a driver is loaded there is no need to register it with the DriverManager.
Calling DriverManager.deregister is even more dangerous since this can seriously disrupt the DriverManager's internal bookkeeping.
JDBC_004: Do Not Import A JDBC Vendor Specific Class
If your code needs to be database independent it is important that you do not import classes that are specific to one JDBC driver vendor. Using these imports means that you probably use database specific features that will prevent you from moving to another type of database without changing your code.
Stick to the classes in the java.sql and javax.sql packages.
JDBC_005: Close Connection, Statement And ResultSet
All JDBC resources (Connection, Statement, ResultSet) must be closed when they are no longer used. If you don't do this you can introduce memory leaks and possible disrupt the functionality of the database. For example you can have
too many open connections on your database, to many open cursors in your database, etc.
Always close you resources in a finally clause of a try block so you know that they always get closed. See rule
JAC_068: Close a connection in a finally block (enforced).
WRONG
public String[] getPersons(DataSource ds) throws SQLException {
Connection conn = datasource.getConnection();
Statement stat = conn.createStatement();
ResultSet rs =
stat.executeQuery("Select * from Person");
while (rs.hasNext()) {
// ...
}
rs.close();
stat.close();
conn.close();
// return
something
}
RIGHT
public String[] getPersons(DataSource ds) throws SQLException {
Connection conn = null;
Statement stat = null;
ResultSet rs = null
;
try {
conn = datasource.getConnection();
stat = conn.createStatement();
rs = stat.executeQuery("Select * from Person");
while
(rs.hasNext()) {
// ...
}
} finally {
if (rs != null) {
rs.close();
}
if
(stat != null) {
stat.close();
}
if (conn != null) {
conn.close();
}
}
// return something
}
|
The code in the finally clause can become messy if you also have to catch the SQLException which is thrown when you close a resource. One technique to get rid of the messy code in the finally block is to use provide methods that will take care of closing these resources for you.
For example:
public void close(Connection conn, Statement stat, ResultSet rs)
throws SQLException {
if (rs != null) {
rs.close();
}
if
(stat != null) {
stat.close();
}
if (conn != null) {
conn.close();
}
}
This method can be made available in for example a JDBC util class. This method also guarantees that the resources are closed in the correct order (see
JDBC_006: Close JDBC resources in the correct order) |
JDBC_006: Close JDBC Resources In The Correct Order
The JDBC resources must be closed in the correct order:
- ResultSet
- Statement
- Connection
JDBC_007: Use PreparedStatement Instead Of Statement When Possible
Always try to use java.sql.PreparedStement instead of the standard java.sql.Statement. Especially when executing SQL statements accepting arguments. Besides the fact that prepared statements get precompiled there is the fact the prepared statements handle the formatting of argument values for you. For example one database could format its string using single quotes where another one may use double quotes.
Another added value is the fact that you don't have to bother about writing long, error prone and complex string concatenation code.
WRONG
Date date = new Date();
// Format the date using the correct pattern (Note: the pattern
// used by SimpleDateFormat is not equal to that used by SQL-99)
SimpleDateFormat sdf =
new SimpleDateFormat("dd-MM-yyyy");
String dateString = sdf.format(date);
// Build the SQL statement (using a StringBuffer is more
// efficient)
StringBuffer sql = new StringBuffer("update Person ");
sql.append("set dob=to_date('");
sql.append(dateString);
sql.append("', 'dd-mm-yyyy') where name=");
sql.append("'");
sql.append(pName);
sql.append("'");
// execute the statement
connection.createStatement();
statement.executeUpdate(sql.toString());
Using a PreparedStatement, we don't need that much code and we delegate the formatting to the driver.
RIGHT
Date date = new Date();
// prepare the statement
statement = connection.prepareStatement("update person set dob=? where name=?");
// bind the parameters
statement.setDate(1, date);
statement.setString(2, pName);
// execute the statement
statement.executeUpdate();
JDBC_008: Check The Return value Of A Navigation Method Of ResultSet
Always look at the return value after calling one of the ResultSet navigation methods; never assume that results will be available.
ResultSet navigation methods include: next, first, last, previous
WRONG
Statement stat = conn.createStatement();
ResultSet rs = Stat.executeQuery("SELECT name FROM person");
rs.next();
String firstName = rs.getString(1);
RIGHT
Statement stat = conn.createStatement();
ResultSet rs = stat.executeQuery("SELECT name FROM person");
if (rs.next()) {
String firstName = rs.getString
(1);
} else {
// error handling
}
JDBC_009: Use Standard SQL Only
Stick to the most common SQL standard when writing SQL statements. Don't use vendor specific extensions. If you do so you will be stuck to the vendor's database.
The most common standard (at the time this document was written) is SQL-92.
JDBC_010: Check For A Nested SQLException
The java.sql.SQLException has always been chainable (even before the exception chaining introduced in JDK1.4). The exception chain can help you in understanding what went wrong when some JDBC API method threw a SQLException. Walking trough the exception chain is done using the
SQLException.getNextException method.
WRONG
try {
Statement stat = connection.createStatement();
ResultSet rs = stat.executeUpdate("SELECT name FROM person");
// process rs
}
catch (SQLException e) {
// printStackTace used as an example
e.printStackTrace();
}
RIGHT
try {
Statement stat = connection.createStatement();
ResultSet rs = stat.executeUpdate("SELECT name FROM person");
// process rs
}
catch (SQLException e) {
// printStackTace used as an example
while (e != null) {
e.printStackTrace();
e = e.getNextException
();
}
}
JDBC_011: Check For A SQLWarning
The main JDBC API interfaces provide access to a possible warning (SQLWarning) that was generated. Besides logging the warning there is not much you can do with it.
A suggestion is to check for warnings when you close the resources. See rule
