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.

java.lang.ObjectObjectorg.springframework.jdbc.object.RdbmsOperationRdbmsOperationorg.springframework.beans.factory.InitializingBeanInitializingBeanorg.springframework.jdbc.object.SqlOperationSqlOperationorg.springframework.jdbc.object.SqlQuerySqlQueryLogicBig

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

  @Override
  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();
          person.setId(resultSet.getLong("ID"));
          person.setFirstName(resultSet.getString("FIRST_NAME"));
          person.setLastName(resultSet.getString("LAST_NAME"));
          person.setAddress(resultSet.getString("ADDRESS"));
          return person;
      };
  }
}

The Person class

public class Person {
  private long id;
  private String firstName;
  private String lastName;
  private String address;
  //getters/setters
    .............
 @Override
  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();
      person.setFirstName(firstName);
      person.setLastName(lastName);
      person.setAddress(address);
      return person;
  }
}

Using SqlQuery to load Person objects

public class SqlQueryExample {

  @Autowired
  private DataSource dataSource;
  private SqlQuery<Person> personSqlQuery;

  @PostConstruct
  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)
              .withTableName("PERSON")
              .usingGeneratedKeyColumns("id");

      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

@Configuration
public class AppConfig {

  @Bean
  public DataSource h2DataSource() {
      return new EmbeddedDatabaseBuilder()
              .setType(EmbeddedDatabaseType.H2)
              .addScript("createPersonTable.sql")//script to create person table
              .build();
  }

  @Bean
  public SqlQueryExample dataHandler() {
      return new SqlQueryExample();
  }

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

Output

-- 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'}

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