Butterfly Persistence JDBC DAO
Jakob Jenkov |
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();
| Tweet | |
Jakob Jenkov | |
