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.

java.lang.ObjectObjectorg.springframework.jdbc.object.RdbmsOperationRdbmsOperationorg.springframework.jdbc.object.SqlOperationSqlOperationorg.springframework.jdbc.object.SqlQuerySqlQueryorg.springframework.jdbc.object.MappingSqlQueryWithParametersMappingSqlQueryWithParametersorg.springframework.jdbc.object.GenericSqlQueryGenericSqlQueryorg.springframework.jdbc.object.UpdatableSqlQueryUpdatableSqlQueryorg.springframework.jdbc.object.MappingSqlQueryMappingSqlQueryorg.springframework.jdbc.object.SqlFunctionSqlFunctionLogicBig

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 {
  @Autowired
  private DataSource dataSource;
  private SqlQuery<Person> personSqlQuery;

  @PostConstruct
  private void postConstruct() {
      GenericSqlQuery<Person> genericSqlQuery = new GenericSqlQuery<>();
      genericSqlQuery.setDataSource(dataSource);
      genericSqlQuery.setSql("select * from Person where id = ?");
      genericSqlQuery.setRowMapper(createPersonRowMapper());
      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 {

  @Autowired
  private DataSource dataSource;
  private SqlFunction sqlFunction;

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

  public void runExample() {
      Object result = sqlFunction.runGeneric();
      System.out.println(result);
  }
}

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);
  }

  @Override
  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