1. JDBC Utilities - what they are for?

JDBC Utilities are a component of OneWebSQL™ that give you an object layer over JDBC. They simplify the use of the JDBC library, which on its own is quite complex. They manage the connection for you (opening, closing, exception handling) and wrap JDBC results into Java objects.

JDBC Utilities are primarily used internally and invisibly by DAO objects. 99% of your database queries will be done using DAO methods. But when DAO methods are not enough, you can also use the JDBC Utilities directly.

The JDBC Utilities are in the package com.onewebsql.util.jdbc which is part of onewebsql-runtime-1.0.3.jar. The main classes in the package are:

2. Row handlers

The com.onewebsql.util.jdbc.RowHandler interface is used for converting database results into Java objects. The row handler is the JDBC Utility you will probably use most often.

The interface is as follows:

public interface RowHandler<T> {
    T getObject(ResultSet resultSet) throws SQLException;
}

The interface is parameterized with type T. The result types of many DAO methods depend on this parameter. Your IDE (e.g. Eclipse) and the compiler will help you fill in the correct types in your code.

2.1. Usage

2.1.1. Create JOINs

The most common use of RowHandlers is to execute a query that does not return a generated bean; for example, a JOIN query.

Example:

SelectQuery query = new SelectQuery(Arrays.asList(
		new TableExpression[] {BookDAO.TABLE_EXPRESSION, AuthorDAO.TABLE_EXPRESSION}));
query.setWhere(BookDAO.AUTHOR_ID.eq(AuthorDAO.ID));
List<BookAuthor> bookAuthors = bookDAO.selectObjectList(query, 
		new BookAuthorRowHandler()).getObjectList();

The BookAuthor class has attributes from the tables book and author.

class BookAuthor {
	protected Integer bookId;
	protected String title;
	protected String isbn;
	protected Integer authorId;
	protected String authorName;
	...
	//getters and setters
}

The BookAuthorRowHandler implements the RowHandler<BookAuthor> interface. It converts every ResultSet row into an appropriate BookAuthor object. The selectObjectList method returns a list of BookAuthor objects.

2.1.2. Create a one-column query

Another typical use is to create a one-column query.

SelectQuery query = new SelectQuery(BookDAO.TABLE_EXPRESSION, BookDAO.TITLE);
query.setWhere(BookDAO.TITLE.like("A%"));
BookDAO bookDAO = new BookDAOImpl(ds, dbAdapter);
List<String> titles = bookDAO.selectObjectList(query, new StringRowHandler())
								.getObjectList();

The StringRowHandler implements the RowHandler<String> interface. The selectObjectList method returns a list of String objects.

2.2. Write your own row handler

A RowHandler wraps a ResultSet object into an appropriate Java object, like this:

    class BookAuthorRowHandler implements com.onewebsql.util.jdbc.RowHandler<BookAuthor> {
        public Book getObject(java.sql.ResultSet rs) throws java.sql.SQLException {
            BookAuthor obj = new BookAuthor();
            obj.setBookId(new Integer(rs.getInt(1)));
            if (rs.wasNull()) {
                obj.setBookId(null);
            }

            obj.setAuthorId(new Integer(rs.getInt(2)));
            if (rs.wasNull()) {
                obj.setAuthorId(null);
            }

            obj.setAuthorName(new String(rs.getString(3)));
            if (rs.wasNull()) {
                obj.setAuthorName(null);
            }

            obj.setIsbn(rs.getString(4));
            if (rs.wasNull()) {
                obj.setIsbn(null);
            }

            obj.setTitle(rs.getString(5));
            if (rs.wasNull()) {
                obj.setTitle(null);
            }

            return obj;
        }

    }

OneWebSQL™ generates a RowHandler class for every table or view in the database. You can use this generated code as an example when writing your own handlers.

2.2.1. Anonymous row handler class

If you're not using a generated row handler or a predefined row handler (see next section), then it is most common to define a row handler in place as an anonymous class. Here's an example:

SelectQuery query = new SelectQuery(
	Arrays.asList(new TableExpression[] 
		{BookDAO.TABLE_EXPRESSION, AuthorDAO.TABLE_EXPRESSION}),
	Arrays.asList(new Column[] { AuthorDAO.NAME, BookDAO.TITLE }));

query.setWhere(BookDAO.AUTHOR_ID.eq(AuthorDAO.ID));

List<String[]> authorsTitles = bookDAO.selectObjectList(query, 
	new RowHandler<String[]>() {
		@Override
		public String[] getObject(ResultSet rs) throws SQLException {
			String[] result = new String[2];
			result[0] = rs.getString(0); 
			if (rs.wasNull()) {
				result[0] = null;
			}
			result[1] = rs.getString(1); 
			if (rs.wasNull()) {
				result[1] = null;
			}
			return result;
		} 
	}).getObjectList();

2.3. Predefined row handlers

Some commonly used row handlers are already defined in the com.onewebsql.util.jdbc package. There are two types of row handlers:

2.3.1. Single column row handlers

This table shows the predefined single column row handlers.

RowHandler Type returned by getObject() Notes
BigDecimalRowHandler java.math.BigDecimal Converts the first column of a row into a BigDecimal object
BooleanRowHandler java.lang.Boolean Converts the first column of a row into a Boolean object
ByteRowHandler java.lang.Byte Converts the first column of a row into a Byte object
BytesRowHandler bytes[] Converts the first column of a row into a byte[] array
DateRowHandler java.sql.Date Converts the first column of a row into a Date object
DoubleRowHandler java.lang.Double Converts the first column of a row into a Double object
FloatRowHandler java.lang.Float Converts the first column of a row into a Float object
IntegerRowHandler java.lang.Integer Converts the first column of a row into an Integer object
LongRowHandler java.lang.Long Converts the first column of a row into a Long object
ShortRowHandler java.lang.Short Converts the first column of a row into a Short object
SingleColumnObjectRowHandler T Converts the first column of a row into an object of type T
StringRowHandler java.lang.String Converts the first column of a row into a String object
TimeRowHandler java.sql.Time Converts the first column of a row into a Time object
TimestampRowHandler java.sql.Timestamp Converts the first column of a row into a Timestamp object

2.3.2. Whole-row row handlers

This table shows the predefined row handlers that convert a whole row into a Java object.

RowHandler Type returned by getObject() Notes
ArrayRowHandler T[] Converts a row into an array of objects of type T
ListRowHandler java.util.List<T> Converts a row into a List of objects of type T
MapRowHandler java.util.Map<String,T> Converts a row into a map. The keys in the map are column names, the values are values returned by ResultSet

3. Execute arbitrary SQL statements

Sometimes you may want to invoke an SQL statement directly using JDBC; for example, to write a query which the OneWebSQL™ API does not support. You can use the com.onewebsql.util.jdbc.JdbcUtil methods to do this. JdbcUtil methods manage the database connection: they open and close the connection and handle exceptions.

3.1. Parameter handlers

The JdbcUtil methods need to convert between Java objects and database query parameters.

Objects responsible for the conversion implement the com.onewebsql.util.jdbc.ParameterHandler interface. The ParameterHandler interface is a reverse image of the RowHandler interface - it performs the conversion in the opposite direction. Here is the interface:

public interface ParameterHandler<T> {
    void bindParameters(PreparedStatement preparedStatement, T parameters) 
	throws SQLException;
}

3.1.1. Write your own parameter handler

Here is an example parameter handler that you can use as a reference when writing your own:

public class ExampleParameterHandler implements ParameterHandler<BookAuthor> {
	public void bindParameters(PreparedStatement preparedStatement, 
			BookAuthor parameters) throws SQLException 
	{
		preparedStatement.setObject(1, parameters.getBookId());
		preparedStatement.setObject(2, parameters.getTitle());
		preparedStatement.setObject(3, parameters.getAuthorId());
	}
}

3.1.1.1. Anonymous parameter handler class

As with row handlers, the most common use of a parameter handler is to define it as an anonymous class:

String query = "select * from book where title like ? and author_id = ?";
Object[] parameters = { "programming", 17 };

new JdbcUtil(ds).query(query, parameters, new ParameterHandler<Object[]>() {
	@Override
	public void bindParameters(PreparedStatement preparedStatement, Object[] parameters)
		throws SQLException {
		preparedStatement.setObject(1, parameters[0]);
		preparedStatement.setObject(2, parameters[1]);
	} 
}, BookDAO.BOOK_ROW_HANDLER);

3.1.2. Predefined parameter handlers

This table shows commonly used parameter handlers that already defined in the com.onewebsql.util.jdbc package.

Parameter handler Type of parameter Notes
ArrayParameterHandler T[] Binds objects in the array to consecutive parameters
DefaultValueListParameterHandler java.util.List<com.onewebsql.query.Value> Binds values in the list to consecutive parameters
ListParameterHandler java.util.List<T> Binds objects in the list to consecutive parameters
NoOpParameterHandler java.lang.Object Does nothing

3.2. Exceptions

JdbcUtil methods throw all exceptions as RuntimeExceptions. The methods can throw one of two exceptions:

The RuntimeDoubleException is thrown if two exceptions are thrown: one during the normal execution and another while closing the database connection. It is the result of these two causes together.

3.3. SQL queries

To execute an arbitrary SQL query, use the method JdbcUtil.query(String query, PH parameter, ParameterHandler<PH> parameterHandler, RowHandler<RH> rowHandler).

String queryString = "SELECT title FROM book WHERE id > ?";
Integer[] parameters = { new Integer(5) } ;
ParameterHandler<Integer> parameterHandler = new ArrayParameterHandler<Integer>();
RowHandler<String> rowHandler = new StringRowHandler();

List<String> list = new JdbcUtil(dataSource)
	.query(queryString, parameters, parameterHandler, rowHandler);

An explanation of each method argument:

Note
The query method manages the database connection and handles exceptions.

3.4. Insert, update, and delete statements

To execute arbitrary SQL insert, update, or delete statements, use the method JdbcUtil.update(String query, PH parameters, ParameterHandler<PH> parameterHandler).

String queryString = "INSERT INTO book(id,title) VALUES (2, ?)";
String[] parameters = { "Programming Pearls" } ;
ParameterHandler<String> parameterHandler = new ArrayParameterHandler<String>();

Integer modified = new JdbcUtil(dataSource)
	.update(queryString, parameters, parameterHandler);

An explanation of each method argument:

Note
The update method manages the database connection and handles exceptions.

3.5. Batch updates

You can also perform batch updates. Batch updates allow you to execute several insert, update, or delete operations in one go.

To execute a batch update, use the method JdbcUtil.batchUpdate(String query, Collection<PH> parameters, ParameterHandler<PH> parameterHandler).

String queryString = "INSERT INTO book(id,title) VALUES (2, ?); "
	+ "INSERT INTO book(id,title) VALUES (3, ?)";

List<String[]> parameters = new ArrayList<String[]>();
parameters.add(new String[]{ "Programming Pearls" }) ;
parameters.add(new String[]{ "Computational Complexity" }) ;

ParameterHandler<String> parameterHandler = new ArrayParameterHandler<String>();

List<Integer> modified = new JdbcUtil(dataSource)
	.batchUpdate(queryString, parameters, parameterHandler);

An explanation of each method argument:

Note
The batchUpdate method manages the database connection and handles exceptions.

3.6. PL/SQL stored functions

To call a stored function, use the method:

JdbcUtil.call(String query, 
	IP inputParameters, 
	OP outputParameters,
	CallableStatementParameterHandler<IP, OP> callableStatementParameterHandler,
	CallableStatementHandler<T> callableStatementHandler)

Here is an example of how to use this method:

List<String> inputParameters = Collections.singletonList("book_seq");
List<Integer> outputParameters = Collections.singletonList(Types.BIGINT),

CallableStatementHandler<List<String>, List<Integer>> handler =
	 	new CallableStatementListParameterHandler<String>();
CallableStatementHandler<Long> statementHandler =
		new GetNextIdCallableStatementHandler();

Long result = JdbcUtil(ds).call("{call get_next_id(?, ?)}",
	inputParameters, outputParameters,
	handler, statementHandler);

An explanation of each method argument:

The CallableStatementParameterHandler is defined as follows:

public interface CallableStatementParameterHandler<IP, OP> {
    void bindParameters(CallableStatement callableStatement, IP inputParameters, 
					OP outputParameters) throws SQLException;
}

It works in very much the same way as ParameterHandler, but it takes CallableStatement as an argument and has two kind of parameters: input and output parameters.

The CallableStatementHandler is the equivalent of a RowHandler. The parameter statementReturnedResultSet is true if the statement returned a ResultSet. Otherwise, the statement returned an update count.

public interface CallableStatementHandler<T> {
    T getObject(CallableStatement callableStatement, 
					boolean statementReturnedResultSet)  throws SQLException;
}

Note
The call method manages the database connection and handles exceptions.

3.7. Performing other operations

Other operations can be invoked using the JdbcUtil.withDataSourceConnection(ConnectionOp<T> conn) method. The method opens the connections, invokes the given operation(s), and closes the connection correctly, regardless of whether the operations succeeded or threw an exception.

The interface ConnectionOp<T>:

public interface ConnectionOp<T> {
    public T invoke(Connection connection) throws SQLException;
}

The com.onewebsql.util.jdbc package contains some implementations of the interface ConnectionOp<T>.

3.7.1. Operations we have already seen

The table below shows ConnectionOp implementations performing operations we have already seen.

ConnectionOp class Operation performed Constructor Notes
QueryOp query <PH,RH> QueryOp(String, PH, ParameterHandler<PH>, RowHandler<RH>) Constructor arguments are the same as in method JdbcUtil.query
UpdateOp insert / update / delete <PH>UpdateOp(String, PH, ParameterHandler<PH>) Constructor arguments are the same as in method JdbcUtil.update
BatchUpdateOp batch update <PH>BatchUpdateOp(String, Collection<PH>, ParameterHandler<PH>) Constructor arguments are the same as in method JdbcUtil.batchUpdate
CallableStatementOp call stored function <IP,OP> CallableStatementOp(String, IP, OP, CallableStatementParameterHandler<IP,OP>, CallableStatementHandler<T>) Constructor arguments are the same as in method JdbcUtil.call

3.7.2. More operations

The table below shows ConnectionOp implementations performing operations we have not yet seen.

ConnectionOp class Operation performed Constructor Notes
QueryWithLimitOp query with limit <PH>QueryWithLimitOp(String, PH, ParameterHandler<PH>, RowHandler<RH>, int) Invokes the query and returns the first rowLimit elements. Constructor arguments are the same as in method JdbcUtil.query plus rowLimit
QueryResultOp query with operation <T,PH,RH>QueryResultOp(String, PH, ParameterHandler<PH>, RowHandler<RH>, QueryResultOp.Operation<T,RH>) Invokes the query and performs an extra operation on the result. Constructor arguments are the same as in method JdbcUtil.query plus QueryResultOp.Operation

An interface QueryResultOp.Operation is defined as follows:

    public interface Operation<T, R> {
        T invoke(ResultSet resultSet, List<R> results) throws SQLException;
    }

The interface can be used if you need to post-process the result in a way that cannot be done by the database; e.g., you want to use the state of your application or you want to read information from the ResultSet object while processing.