Spring - Subclasses of SqlQuery

[Updated: Mar 4, 2017, Created: Feb 25, 2017]

In the last tutorial we saw how to use SqlQuery as reusable compiled sql object. In this tutorial we are going to quickly explore the subclasses of SqlQuery.


Using GenericSqlQuery

Generic Sql Query is a concrete subclass of SqlQuery. Functionally, it achieves the same outcome as SqlQuery. We would prefer to use it in situations where a RowMapper is easier to pass to a setter method rather than subclassing SqlQuery class and overriding newRowMapper(..) method.

Following code demonstrates how to use GenericSqlQuery:

public class GenericSqlQueryExample {
  private DataSource dataSource;
  private SqlQuery<Person> personSqlQuery;

  private void postConstruct() {
      GenericSqlQuery<Person> genericSqlQuery = new GenericSqlQuery<>();
      genericSqlQuery.setSql("select * from Person where id = ?");
      genericSqlQuery.declareParameter(new SqlParameterValue(Types.BIGINT, "id"));
      this.personSqlQuery = genericSqlQuery;

Check out the complete code example here.

Using MappingSqlQueryWithParameters

This is also a direct subclass of SqlQuery but it is abstract. This class provides a specialized abstract method:

protected abstract T mapRow(ResultSet rs, int rowNum,
                            Object[] parameters, Map<?,?> context)
                                                       throws SQLException

The main difference with SqlQuery is, we don't have to create a RowMapper, but need to work with ResultSet directly. Check out a complete example here.

Using MappingSqlQuery

Mapping Sql Query is a subclass of Mapping Sql Query With Parameters. This class replaces a new abstract mapRow(..) method, which simplifies the things by dropping parameters and context.

protected abstract T mapRow(ResultSet rs, int rowNum)
                                           throws SQLException

Check out a complete example here.

Using SqlFunction

This is a concrete subclass which can be used to call SQL functions that return a single value result.

public class SqlFunctionExample {

  private DataSource dataSource;
  private SqlFunction sqlFunction;

  private void postConstruct() {
      this.sqlFunction =
              new SqlFunction<>(dataSource, "select CURRENT_DATE()");

  public void runExample() {
      Object result = sqlFunction.runGeneric();

Note that we are using H2 database specific function CURRENT_DATE(). Check out the complete example here

Using UpdatableSqlQuery

The abstract class UpdatableSqlQuery can be subclassed and can be used to update rows. We have to work with ResultSet#updateXYZ(..) methods.

public class PersonUpdatableSqlQuery extends UpdatableSqlQuery<Person> {

  public PersonUpdatableSqlQuery(DataSource dataSource, String sql) {
      super(dataSource, sql);

  protected Person updateRow(ResultSet resultSet, int rowNum, Map<?, ?> context) throws SQLException {

      Person person = UpdatableSqlQueryExample.toPerson(resultSet);
      if (person.getId() == 1) {
          resultSet.updateString("ADDRESS", "XYZ");
      return null;

Complete example here.

See Also