Spring - Reusable queries using SqlQuery

[Updated: Feb 27, 2017, Created: Feb 24, 2017]

The package org. springframework. jdbc. object defines classes which represent sql queries, updates, and stored procedures as threadsafe, reusable objects. In this tutorial we are going to learn the usage of SqlQuery.


The abstract class Rdbms Operation encapsulates a compiled reusable query, and defines methods to set a DataSource and query related parameters. Internally it uses an instance of JdbcTemplate to execute SQL.

SqlQuery is also an abstract class and we need to implement it's method new Row Mapper(). The middle super class SqlOperation generally represents a sql operation.

Example using SqlQuery

Implementing SqlQuery

public class PersonSqlQuery extends SqlQuery<Person> {

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

  protected RowMapper<Person> newRowMapper(Object[] parameters, Map<?, ?> context) {
      System.out.printf("newRowMapper params: %s context: %s%n", Arrays.toString(parameters), context);
      return (ResultSet resultSet, int rowNum) -> {
          Person person = new Person();
          return person;

The Person class

public class Person {
  private long id;
  private String firstName;
  private String lastName;
  private String address;
  public String toString() {
      return "Person{" +
              "id=" + id +
              ", firstName='" + firstName + '\'' +
              ", lastName='" + lastName + '\'' +
              ", address='" + address + '\'' +
  public static Person create(String firstName, String lastName, String address) {
      Person person = new Person();
      return person;

Using SqlQuery to load Person objects

public class SqlQueryExample {

  private DataSource dataSource;
  private SqlQuery<Person> personSqlQuery;

  private void postConstruct() {
      personSqlQuery = new PersonSqlQuery(dataSource, "select * from Person where id = ?");
      personSqlQuery.declareParameter(new SqlParameterValue(Types.BIGINT, "id"));

  public void runExmaple() {
      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;

  public void savePersons(Person[] persons) {
      SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(dataSource)

      SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(persons);
      int[] ints = simpleJdbcInsert.executeBatch(batch);
      System.out.printf("Batch Rows inserted: %s%n", Arrays.toString(ints));


Configuration and main class

public class AppConfig {

  public DataSource h2DataSource() {
      return new EmbeddedDatabaseBuilder()
              .addScript("createPersonTable.sql")//script to create person table

  public SqlQueryExample dataHandler() {
      return new SqlQueryExample();

  public static void main(String[] args) {
      AnnotationConfigApplicationContext context =
              new AnnotationConfigApplicationContext(AppConfig.class);
      System.out.println("-- Running SqlQuery example --");


-- Running SqlQuery example --
Batch Rows inserted: [1, 1]
newRowMapper params: [1] context: null
Person loaded: Person{id=1, firstName='Dana', lastName='Whitley', address='464 Gorsuch Drive'}
newRowMapper params: [2] context: null
Person loaded: Person{id=2, firstName='Robin', lastName='Cash', address='64 Zella Park'}
INFO: Starting embedded database: url='jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=false', username='sa'
INFO: Executing SQL script from class path resource [createPersonTable.sql]
INFO: Executed SQL script from class path resource [createPersonTable.sql] in 12 ms.

In the next tutorial, we will go through the subclasses of SqlQuery.

Example Project

Dependencies and Technologies Used :

  • spring-context 4.3.6.RELEASE: Spring Context.
  • spring-jdbc 4.3.6.RELEASE: Spring JDBC.
  • h2 1.4.193: H2 Database Engine.
  • JDK 1.8
  • Maven 3.3.9

Sql Query Example Select All Download
  • spring-sql-query
    • src
      • main
        • java
          • com
            • logicbig
              • example
        • resources

See Also