Spring - Data manipulation using SqlUpdate

[Updated: Feb 27, 2017, Created: Feb 27, 2017]
java.lang.ObjectObjectorg.springframework.jdbc.object.RdbmsOperationRdbmsOperationorg.springframework.beans.factory.InitializingBeanInitializingBeanorg.springframework.jdbc.object.SqlOperationSqlOperationorg.springframework.jdbc.object.SqlUpdateSqlUpdateLogicBig

For loading data from database, we can use SqlQuery as reusable object. For performing data manipulation operations like update, insert and delete we will use SqlUpdate.

SqlUpdate is a concrete subclass of SqlOperation. It provides a number of update methods, analogous to the execute methods of SqlQuery. There's absolutely no need to extend this class unless we want to modify or add a new update method.

Example using SqlUpdate

public class SqlUpdateExample {

  @Autowired
  private DataSource dataSource;
  private SqlUpdate updateSql;
  private SqlUpdate insertSql;
  private GenericSqlQuery<Person> loadSqlQuery;

  @PostConstruct
  private void postConstruct() {
      initLoadSql();
      initInsertSql();
      initUpdateSql();
  }

  private void initInsertSql() {
      insertSql = new SqlUpdate();
      insertSql.setDataSource(dataSource);
      insertSql.setSql("insert into PERSON (first_Name, last_Name, address) values (?, ?, ?)");
      insertSql.declareParameter(new SqlParameterValue(Types.VARCHAR, "first_Name"));
      insertSql.declareParameter(new SqlParameterValue(Types.VARCHAR, "last_Name"));
      insertSql.declareParameter(new SqlParameterValue(Types.VARCHAR, "address"));
  }

  private void initUpdateSql() {
      updateSql = new SqlUpdate();
      updateSql.setDataSource(dataSource);
      updateSql.setSql("update PERSON set address = ? where id = ?");
      updateSql.declareParameter(new SqlParameterValue(Types.VARCHAR, "address"));
      updateSql.declareParameter(new SqlParameterValue(Types.BIGINT, "id"));
  }

  private void initLoadSql() {
      loadSqlQuery = new GenericSqlQuery<>();
      loadSqlQuery.setDataSource(dataSource);
      loadSqlQuery.setSql("select * from Person");
      loadSqlQuery.setRowMapper(createPersonRowMapper());
  }

  private RowMapper<Person> createPersonRowMapper() {
      return (rs, rowNum) -> {
          return toPerson(rs);
      };
  }

  private Person toPerson(ResultSet rs) throws SQLException {
      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 runExmaple() {
      //inserting records
      insertSql.update("Dana", "Whitley", "464 Gorsuch Drive");
      insertSql.update("Robin", "Cash", "64 Zella Park");
      System.out.println("Records inserted");

      //loading all person
      List<Person> list = loadSqlQuery.execute();
      System.out.printf("loaded: %s%n", list);

      //updating
      updateSql.update("XYZ", 1);
      System.out.println("Record updated");

      //loading again
      System.out.println("Loading again after update");
      List<Person> list2 = loadSqlQuery.execute();
      System.out.printf("loaded: %s%n", list2);
  }
}

In above example, we are using two instances of SqlUpdate: one to insert records and other to update records. We also used GenericSqlQuery to load records.

src/main/resources/createPersonTable.sql

CREATE TABLE PERSON
(
ID BIGINT  PRIMARY KEY AUTO_INCREMENT,
FIRST_NAME VARCHAR(255),
LAST_NAME VARCHAR(255),
ADDRESS VARCHAR(255)
);

Configuration and main class

@Configuration
public class AppConfig {

  @Bean
  public DataSource h2DataSource() {
      return new EmbeddedDatabaseBuilder()
              .setType(EmbeddedDatabaseType.H2)
              .addScript("createPersonTable.sql")
              .build();
  }

  @Bean
  public SqlUpdateExample sqlUpdateExample() {
      return new SqlUpdateExample();
  }

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

Output

-- Running SqlUpdate example --
Records inserted
loaded: [Person{id=1, firstName='Dana', lastName='Whitley', address='464 Gorsuch Drive'}, Person{id=2, firstName='Robin', lastName='Cash', address='64 Zella Park'}]
Record updated
Loading again after update
loaded: [Person{id=1, firstName='Dana', lastName='Whitley', address='XYZ'}, 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 14 ms.

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 Update Example Select All Download
  • spring-sql-update
    • src
      • main
        • java
          • com
            • logicbig
              • example
        • resources

See Also