[MO-java-users] Abstracting the database

[Graham K. <gr...@da...>]

 
 I have been trying to abstract away database (JDBC) access using these 
two principles: http://www.mockobjects.com/wiki/OnlyMockYourOwnInterfaces
http://www.mockobjects.com/wiki/MockableAdapter

 Not being able to find a practical example to plagiarise I tried 
writing my own. A database basically allows the 'put'ing of data and the 
'get'ing of data (save / load or executeUpdate / executeQuery).
 So first cut is (with verbosity removed):

public interface Database {  // Or should it be DatabaseAdapter ?
 ReturnType get(String sql);
 int put(String sql);  // Returns number of rows affected
}

1) GET
 What should ReturnType be ? It comes back from the database as a 
ResultSet, which is an interface and provides everything the caller 
needs. But it's java.sql.ResultSet, hence not an interface we own.

2) PUT
 With an INSERT or UPDATE we don't really want to generate the full sql 
as a string ourselves. It is up to the database vendor to decide how to 
convert / escape / quote a Date or String, so we really want a 
PreparedStatement. But again that's java.sql.PreparedStatement.

3) Exceptions
 Internally SQLException will be flying around, but we can easily wrap 
that into our own client friendly DatabaseException. But if we are 
returning ResultSet and allowing PreparedStatement then the caller has 
to deal with SQLException anyway, so why force them to have two catch 
clauses ?

 Our current best effort is (again without the ceremony of javadoc):

public interface Database {

    ResultSet get(String sql) throws DatabaseException;
    int put(String sql) throws DatabaseException;
    PreparedStatement prepareStatement(String sql) throws DatabaseException;
    void close(ResultSet rs);
    void close(PreparedStatement statement);
}

 and we mock ResultSet and PreparedStatement.

 I would love to hear how other people have solved this problem.

 Thanks in advance,
 Graham.