Apache DBUtils
Some of the advantages of using DbUtils are:
- No possibility for resource leaks. Correct JDBC coding isn’t difficult but it is time-consuming and tedious. This often leads to connection leaks that may be difficult to track down.
- Cleaner, clearer persistence code. The amount of code needed to persist data in a database is drastically reduced. The remaining code clearly expresses your intention without being cluttered with resource cleanup.
- Automatically populate JavaBean properties from ResultSets. You don’t need to manually copy column values into bean instances by calling setter methods. Each row of the ResultSet can be represented by one fully populated bean instance.
problem that we face with normal jdbc code
We might be using following type of code, when ever we work with JDBC.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JDBCTest {
/**
* @author Umashankar
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
Connection connection=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
Class.forName("com.mysql.jdbc.Driver");
connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/testdb", "root", "root");
System.out.println("connected to database! ready for DB operations");
ps=connection.prepareStatement("select * from Student where studentId=?");
ps.setString(1,'S-1001');
rs=ps.executeQuery();
while(rs.next()){
System.out.println("studentName:"+rs.getString(2));
}
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally{
/* rs.close();
ps.close();
connection.close(); */
}
}
}let’s assume that if we don’t close resultset ,prepared statement, connection what happens? we are saying connection leakage, we are so many times telling that connection leakage, is that so important? yes it is. you won’t find this as a big issue when you are developing applications, working with applications at home. But, actually if you go live in the production environment , things can be quite different. Connection Leakage is a serious issue for any software as it is a costlier resource.
As I stated in the advantages section, writing perfect Jdbc code isn’t difficult, but it is tedious task. DBUtils.jar provides set of methods to close the resources perfectly. In the DbUtils class all the methods are being declared as static to access these methods directly using class with no need of creating object. The methods available in the class are.
Methods in the DbUtils class
DbUtils.close(conn) DbUtils.close(rs) DbUtils.close(stmt) DbUtils.closeQuietly(conn) DbUtils.closeQuietly(rs) DbUtils.closeQuietly(conn, stmt, rs) DbUtils.commitAndClose(conn) DbUtils.commitAndCloseQuietly(arg0) DbUtils.rollbackAndClose(conn) DbUtils.rollbackAndCloseQuietly(arg0)
You will be using these methods to close the database related resources like ResultSet , PreparedStatement, Statement and Connection objects in the finally block. when you use these methods the following type of logic is not necessary.
if(rs!=null){
rs.close();
}
if(ps!=null){
ps.close();
}
if(connection!=null){
connection.close();
}you just have to write this kind of logic to handle jdbc connection leakage.
finally{
DbUtils.closeQuietly(connection, ps, rs);
}Understanding the internals of DbUtils class
/*
* Licensed to the Apache Software Foundation (ASF) under one or more
* contributor license agreements. See the NOTICE file distributed with
* this work for additional information regarding copyright ownership.
* The ASF licenses this file to You under the Apache License, Version 2.0
* (the "License"); you may not use this file except in compliance with
* the License. You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package org.apache.commons.dbutils;import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;/**
* A collection of JDBC helper methods. This class is thread safe.
*/
public final class DbUtils {/**
* Close a <code>Connection</code>, avoid closing if null.
*
* @param conn Connection to close.
* @throws SQLException if a database access error occurs
*/
public static void close(Connection conn) throws SQLException {
if (conn != null) {
conn.close();
}
}/**
* Close a <code>ResultSet</code>, avoid closing if null.
*
* @param rs ResultSet to close.
* @throws SQLException if a database access error occurs
*/
public static void close(ResultSet rs) throws SQLException {
if (rs != null) {
rs.close();
}
}/**
* Close a <code>Statement</code>, avoid closing if null.
*
* @param stmt Statement to close.
* @throws SQLException if a database access error occurs
*/
public static void close(Statement stmt) throws SQLException {
if (stmt != null) {
stmt.close();
}
}
/**
* Close a <code>Connection</code>, avoid closing if null and hide
* any SQLExceptions that occur.
*
* @param conn Connection to close.
*/
public static void closeQuietly(Connection conn) {
try {
close(conn);
} catch (SQLException e) {
// quiet
}
}
/**
* Close a <code>Connection</code>, <code>Statement</code> and
* <code>ResultSet</code>. Avoid closing if null and hide any
* SQLExceptions that occur.
*
* @param conn Connection to close.
* @param stmt Statement to close.
* @param rs ResultSet to close.
*/
public static void closeQuietly(Connection conn, Statement stmt,
ResultSet rs) {
try {
closeQuietly(rs);
} finally {
try {
closeQuietly(stmt);
} finally {
closeQuietly(conn);
}
}
}
/**
* Close a <code>ResultSet</code>, avoid closing if null and hide any
* SQLExceptions that occur.
*
* @param rs ResultSet to close.
*/
public static void closeQuietly(ResultSet rs) {
try {
close(rs);
} catch (SQLException e) {
// quiet
}
}
/**
* Close a <code>Statement</code>, avoid closing if null and hide
* any SQLExceptions that occur.
*
* @param stmt Statement to close.
*/
public static void closeQuietly(Statement stmt) {
try {
close(stmt);
} catch (SQLException e) {
// quiet
}
}
/**
* Commits a <code>Connection</code> then closes it, avoid closing if null.
*
* @param conn Connection to close.
* @throws SQLException if a database access error occurs
*/
public static void commitAndClose(Connection conn) throws SQLException {
if (conn != null) {
try {
conn.commit();
} finally {
conn.close();
}
}
}
/**
* Commits a <code>Connection</code> then closes it, avoid closing if null
* and hide any SQLExceptions that occur.
*
* @param conn Connection to close.
*/
public static void commitAndCloseQuietly(Connection conn) {
try {
commitAndClose(conn);
} catch (SQLException e) {
// quiet
}
}
/**
* Loads and registers a database driver class.
* If this succeeds, it returns true, else it returns false.
*
* @param driverClassName of driver to load
* @return boolean <code>true</code> if the driver was found, otherwise <code>false</code>
*/
public static boolean loadDriver(String driverClassName) {
try {
Class.forName(driverClassName).newInstance();
return true;
} catch (ClassNotFoundException e) {
return false;
} catch (IllegalAccessException e) {
// Constructor is private, OK for DriverManager contract
return true;
} catch (InstantiationException e) {
return false;
} catch (Throwable e) {
return false;
}
}
/**
* Print the stack trace for a SQLException to STDERR.
*
* @param e SQLException to print stack trace of
*/
public static void printStackTrace(SQLException e) {
printStackTrace(e, new PrintWriter(System.err));
}
/**
* Print the stack trace for a SQLException to a
* specified PrintWriter.
*
* @param e SQLException to print stack trace of
* @param pw PrintWriter to print to
*/
public static void printStackTrace(SQLException e, PrintWriter pw) {
SQLException next = e;
while (next != null) {
next.printStackTrace(pw);
next = next.getNextException();
if (next != null) {
pw.println("Next SQLException:");
}
}
}
/**
* Print warnings on a Connection to STDERR.
*
* @param conn Connection to print warnings from
*/
public static void printWarnings(Connection conn) {
printWarnings(conn, new PrintWriter(System.err));
}
/**
* Print warnings on a Connection to a specified PrintWriter.
*
* @param conn Connection to print warnings from
* @param pw PrintWriter to print to
*/
public static void printWarnings(Connection conn, PrintWriter pw) {
if (conn != null) {
try {
printStackTrace(conn.getWarnings(), pw);
} catch (SQLException e) {
printStackTrace(e, pw);
}
}
}
/**
* Rollback any changes made on the given connection.
* @param conn Connection to rollback. A null value is legal.
* @throws SQLException if a database access error occurs
*/
public static void rollback(Connection conn) throws SQLException {
if (conn != null) {
conn.rollback();
}
}
/**
* Performs a rollback on the <code>Connection</code> then closes it,
* avoid closing if null.
*
* @param conn Connection to rollback. A null value is legal.
* @throws SQLException if a database access error occurs
* @since DbUtils 1.1
*/
public static void rollbackAndClose(Connection conn) throws SQLException {
if (conn != null) {
try {
conn.rollback();
} finally {
conn.close();
}
}
}
/**
* Performs a rollback on the <code>Connection</code> then closes it,
* avoid closing if null and hide any SQLExceptions that occur.
*
* @param conn Connection to rollback. A null value is legal.
* @since DbUtils 1.1
*/
public static void rollbackAndCloseQuietly(Connection conn) {
try {
rollbackAndClose(conn);
} catch (SQLException e) {
// quiet
}
}
}Where to get and How to Use DbUtils.jar ?
- In order to use these methods you should add DbUtils.jar to your project class path.
- You can get this jar from here