CODEWITHSUNDEEP
javajdbc
itsadok
itsadok

Reputation: 29342

Remove boilerplate from db code

It seems that every time I want to perform a db query, I have to write the following:

Connection conn = null;
Statement stmt = null;
ResultSet rset = null;

try {
    conn = dataSource.getConnection();
    stmt = conn.prepareStatement(sql);
    // ...set stmt params
    rset = stmt.executeQuery();
    while(rset.next()) {
        // Do something interesting
    }
} finally {
    try { if (rset != null) rset.close(); } catch(SQLException e) { }
    try { if (stmt != null) stmt.close(); } catch(SQLException e) { }
    try { if (conn != null) conn.close(); } catch(SQLException e) { }
}

Is this really the best way to do this? Is there a way to at least reduce some of the clutter?

Edited: as some of the comments pointed out, this code wasn't long enough.

Upvotes: 7

Views: 3360

Answers (5)

Stobor
Stobor

Reputation: 45152

Make a helper method?

public class DBHelper
{
    public static Object run(string sql, List params, ResultHandler rhandler)
    {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rset = null;

        try {
            conn = dataSource.getConnection();
            stmt = conn.prepareStatement(sql);
            int i = 0;
            for(Object p in params)
            {
                stmt.setObject(++i, p);
            }
            rset = stmt.executeQuery();
            return rhandler.handle(rset);
        } finally {
                try { rset.close(); } catch(Exception e) { }
                try { stmt.close(); } catch(Exception e) { }
                try { conn.close(); } catch(Exception e) { }
        }
    }
}

public interface ResultHandler
{
    public Object handle(ResultSet)
}

public class Test
{
    public static void main(String[] args)
    {
        String s = (String)DBHelper.run("select * from mytable where col = ?",
                Arrays.asList({"foo"}), 
                new ResultHandler
                {
                    public Object handle(ResultSet r)
                    {
                        r.first();
                        return r.getString("col2");
                    }
                }();
    }
}

Upvotes: 4

Tim R
Tim R

Reputation: 814

DbUtils is a very useful framework, I've used it for smaller projects where Spring and Hibernate are overkill. It's able to do some object mapping as well.

Upvotes: 6

Yaneeve
Yaneeve

Reputation: 4779

I would use hibernate or JPA to reduce the clutter...

Upvotes: 0

Csaba_H
Csaba_H

Reputation: 8245

If you already have a DataSource you can use Spring JdbcTemplate for:

  • greatly reduced boilerplate code
  • have a good sql exception hierarchy to handle common database problems with specific runtime exceptions
  • (later with further Spring usage) use declarative transaction management

If it seems too heavy for the moment you could implement some utility classes and methods for the 'boilerplate part'. Studying the source of JdbcTemplate should help in this case.

Upvotes: 8

Nick Holt
Nick Holt

Reputation: 34321

Yes, use the Sping JDBC Template classes (http://static.springsource.org/spring/docs/2.0.x/reference/jdbc.html).

Or if you don't use Spring copy the template pattern that they are using in your own code.

Upvotes: 11

Related Questions