Spring - Subclasses of SqlQuery

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 {
    @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;
    }

    private RowMapper<Person> createPersonRowMapper() {
        return (rs, rowNum) -> {
            Person person = new Person();
            person.setId(rs.getLong("ID"));
            person.setFirstName(rs.getString("FIRST_NAME"));
            person.setLastName(rs.getString("LAST_NAME"));
            person.setAddress(rs.getString("ADDRESS"));
            return person;
        };
    }

    public void runExample() {
        Person person1 = Person.create("Dana", "Whitley", "464 Gorsuch Drive");
        Person person2 = Person.create("Robin", "Cash", "64 Zella Park");
        savePersons(new Person[]{person1, person2});
        Person person = loadPersonById(1);
        System.out.printf("Person loaded: %s%n", person);
        person = loadPersonById(2);
        System.out.printf("Person loaded: %s%n", person);
    }

    public Person loadPersonById(long id) {
        List<Person> persons = personSqlQuery.execute(id);
        if (persons.size() == 1) {
            return persons.get(0);
        }
        return null;
    }
    .............
}

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.