Butterfly Persistence JDBC DAO

Jakob Jenkov
Last update: 2021-06-30

From the IDaos instance you have access to an IJdbcDao. This IJdbcDao can help you perform most trivial JDBC tasks like inserting parameters into PreparedStatement's, iterating ResultSet's etc.

The IJdbcDao Interface

Here is the IJdbcDao interface definition. Exception declarations have been removed for clarity.

package com.jenkov.db.itf;

public interface IJdbcDao {

    public Long readLong(String sql)
    public Long readLong(String sql, Object ... parameters)

    public String readIdString(String sql)
    public String readIdString(String sql, Object ... parameters)
    public String readIdString(String sql,
                    IPreparedStatementManager statementManager)

    public Object read(String sql, IResultSetProcessor processor)
    public Object read(String sql, IResultSetProcessor processor,
                    Object ... parameters)
    public Object read(String sql,
                    IPreparedStatementManager statementManager,
                    IResultSetProcessor processor)

    public int update(String sql)
    public int update(String sql, Object ... parameters)
    public int update(String sql,
                    IPreparedStatementManager statementManager)
}

The methods will be explained in more detail in the following sections.

Reading Long's

The readLong(String sql) method can be used to read a long value from a ResultSet. This is useful when you need to read the primary key of some record in the database. It can also be useful if you need to read an aggregate value like the sum of columns. The method will execute the given SQL and return value of the first column in the first record of the ResultSet. If no records are found null is returned. Here is an example:

String sql = "select id from persons where id=5";
Long id = daos.getJdbcDao().readLong(sql);

The readLong() method comes in another version too which takes an optional list of parameters. These parameters are inserted into a PreparedStatement. They will be inserted in the sequence they are passed to the readLong() method. Here is an example:

String sql = "select id from persons where id=?";
Long id = daos.getJdbcDao().readLong(sql, 5);

Reading ID Strings

Sometimes you need to read not just one id, but a list of ids into a string which can be used in another SQL statement, like this:

select * from persons where id in (4,5,6,7)

The string (4,5,6,7) is an id string which could be returned by the readIdString() method. Here is an example:

String selectIds = "select id from users where active = true";
String ids = daos.getJdbcDao().readIdString(selectIds);

String selectPosts = "select * from forumposts where id in " + ids;
...

As you can see the id string from the first select is appended to the second select.

The readIdString() method comes in a variant that takes an optional list of parameters. Here is an example:

String selectIds = "select id from tasks where deadline >= ?";
Timestamp deadline = new Timestamp(System.currentTimeMillis());
String ids = daos.getJdbcDao().readIdString(selectIds, deadline);

String selectPosts = "select * from forumposts where id in " + ids;

As you might have figured out the deadline is inserted into a PreparedStatement and thus substititued for the ?, before the statement is executed.

Iterating ResultSet's

The IJdbcDao has a few read() methods that allows you to decide what to do with each record in the ResultSet resulting from some SQL query. To do so you must provide the read() method with an implementation of the IResultSetProcessor interface. Here is how that interface looks:

public interface IResultSetProcessor {

    public void   init     (ResultSet result, IDaos daos)
    public void   process  (ResultSet result, IDaos daos)
    public Object getResult(                )

}

The exceptions thrown have been removed here for clarity.

The init() method is called before the iteration of the ResultSet begins. The process() method is called for each record in the ResultSet The read() method returns an object. This is the object is obtained from the getResult() method of the IResultSetProcessor.

Here is an example of how to use a IResultSetProcessor with the read() methods:

String sql = "select * from persons";

List persons = (List) daos.getJdbcDao().read(sql,
    new IResultSetProcessor(){
        List list = new ArrayList();

        public void init(ResultSet result, IDaos daos){
        }

        public void process(ResultSet result, IDaos daos){
          Person person = (Person)
                daos.getObjectDao().read(Person.class, result);
          list.add(person);
        }

        public Object getResult(){
          return list;
        }
    });

It may be a good idea to create a base class for your IResultSetProcessor implementations with empty method implementations for init() and perhaps a default behaviour for getResult(). That will make your anonymous subclasses smaller and more readable.

The read() method comes in a version that takes an optional number of parameters. That is useful when the SQL statement needs to be parameterized. Here the example from before with parameters added:

String sql = "select * from persons where birth_year > ?";

List persons = (List) daos.getJdbcDao().read(sql, processor, 1975);

The processor parameter is an IResultSetProcessor implementation. The concrete implementation has been removed here for clarity.

Notice how the number 1975 is passed into the read() method at the end. This number is inserted into the PreparedStatement prepared from the given SQL.

Updates - Insert, Update, Delete

The IJdbcDao interface has an update method that can be used for all types of SQL updates (insert, update, delete). In other words, all SQL queries that do not return a ResultSet. Here is an example:

String sql = "insert into persons(name) values('John Doe')";
daos.getJdbcDao().update(sql);

Here is an example that takes parameters to be inserted into a PreparedStatement:

String sql = "insert into persons(name) values(?)";
daos.getJdbcDao().update(sql, "John Doe");

IPreparedStatementManager

Several of the methods in the IJdbcDao come in versions that take an IPreparedStatementManager instance as parameter. An IPreparedStatementManager lets you take full control of how the PreparedStatement is prepared, initialized, executed etc. Here is how the IPreparedStatement interface looks (exception declarations removed for clarity):

package com.jenkov.db.itf;

public interface IPreparedStatementManager {

    public PreparedStatement prepare (String sql, Connection connection)

    public void   init       (PreparedStatement statement)
    public Object execute    (PreparedStatement statement)
    public void   postProcess(PreparedStatement statement)
}

The prepare() method should return a new PreparedStatement.

The init() method should initialize the PreparedStatement. This usually includes setting any necessary parameters on it.

The execute() method should execute the PreparedStatement and return the result of the execution. This is normally either an int telling how many records were affected, or a ResultSet with the results of the query.

The postProcess() method is normally used to call methods on the PreparedStatement after execution. For instance, calling getGeneratedKeys to access the key generated by the database as a result of an insert.

The IPreparedStatementManager does not have to close the PreparedStatement. This is done automatically by the IJdbcDao.

JDBC DAO Examples

Below follows a list of JDBC DAO, IJdbcDao, code examples so you can get an idea about what using the IJdbcDao looks like.

Reading Long

To read a long from a ResultSet (first record, first column) you would do like this:

IDaos daos = persistenceManager.createDaos();
String sql = "select id from persons where id=?";
Long aLong = daos.getJdbcDao().readLong(sql, 22);

daos.getConnection().close();

Reading ID String

To read the ids of X records returned from a query into a string of the format (id1, id2, id3, ...) you will do like this:

IDaos daos = persistenceManager.createDaos();
String sql = "select id from persons where birth_year > ?";
String idString daos.getJdbcDao().readIdString(sql, 1975);

String sql2 = "select * from forum_posts where person_id in " +
              idString;
...
daos.getConnection().close();

Iterating a ResultSet

Iterating a ResultSet is done like this:

IDaos daos = persistenceManager.createDaos();
String sql = "select * from persons where birth_year > ?";
IResultSetProcessor = new IResultSetProcessor(){
        List list = new ArrayList();

        public void init(ResultSet result, IDaos daos){
        }

        public void process(ResultSet result, IDaos daos){
          Person person = (Person)
                daos.getObjectDao().read(Person.class, result);
          list.add(person);
        }

        public Object getResult(){
          return list;
        }
    };

List persons = (List) daos.getJdbcDao().read(sql, processor, 1975);

Updating a Record

Updating a record can be done like this:

IDaos daos = persistenceManager.createDaos();
String sql = "update person set name=? where id=?";

daos.getJdbcDao().update(sql, "John Doe", 666);

daos.getConnection().close();

Jakob Jenkov

Featured Videos

Java ConcurrentMap + ConcurrentHashMap

Java Generics

Java ForkJoinPool

P2P Networks Introduction

















Close TOC
All Tutorial Trails
All Trails
Table of contents (TOC) for this tutorial trail
Trail TOC
Table of contents (TOC) for this tutorial
Page TOC
Previous tutorial in this tutorial trail
Previous
Next tutorial in this tutorial trail
Next