Spring - Reusable queries using SqlQuery

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.

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;
    .............
  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;
  }
  //getters/setters
}

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