Close

Spring Framework - UpdatableSqlQuery Examples

Spring Framework 

This class can be used to execute SQL updates. We have to use low-level resultSet.updateXYZ(..) methods.

package com.logicbig.example;

import org.springframework.jdbc.object.UpdatableSqlQuery;

import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map;

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;
}
}
package com.logicbig.example;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SqlParameterValue;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.object.GenericSqlQuery;
import org.springframework.jdbc.object.SqlQuery;

import javax.annotation.PostConstruct;
import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Arrays;
import java.util.List;

public class UpdatableSqlQueryExample {

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

@PostConstruct
private void postConstruct() {
personUpdateSqlQuery = new PersonUpdatableSqlQuery(dataSource,
"select * from Person where id = ?");
personUpdateSqlQuery.declareParameter(new SqlParameterValue(Types.BIGINT, "id"));
personUpdateSqlQuery.setUpdatableResults(true);

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) -> toPerson(rs);
}

public static 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 handle() {

Person person1 = Person.create("Dana", "Whitley", "464 Gorsuch Drive");

Person person2 = Person.create("Robin", "Cash", "64 Zella Park");
savePersons(new Person[]{person1, person2});

System.out.println(" -- loading before update");
Person person = loadPersonById(1);
System.out.printf("Person loaded: %s%n", person);

person = loadPersonById(2);
System.out.printf("Person loaded: %s%n", person);

personUpdateSqlQuery.execute(1);
personUpdateSqlQuery.execute(2);

System.out.println(" -- loading after update");
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));
}
}
package com.logicbig.example;

public class Person {
private long id;
private String firstName;
private String lastName;
private String address;

public long getId() {
return id;
}

public void setId(long id) {
this.id = id;
}

public String getFirstName() {
return firstName;
}

public void setFirstName(String firstName) {
this.firstName = firstName;
}

public String getLastName() {
return lastName;
}

public void setLastName(String lastName) {
this.lastName = lastName;
}

public String getAddress() {
return address;
}

public void setAddress(String address) {
this.address = address;
}

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

pom.xml:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.logicbig.example</groupId>
<artifactId>spring-sql-query5</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>4.3.6.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>4.3.6.RELEASE</version>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>1.4.193</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.5.1</version>
<configuration>
<source>1.8</source>
<target>1.8</target>
</configuration>
</plugin>
</plugins>
</build>
</project>

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

package com.logicbig.example;

import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType;

import javax.sql.DataSource;

@Configuration
public class AppConfig {

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

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

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

Output

-- Running UpdatableSqlQuery example --
Batch Rows inserted: [1, 1]
-- loading before update
Person loaded: Person{id=1, firstName='Dana', lastName='Whitley', address='464 Gorsuch Drive'}
Person loaded: Person{id=2, firstName='Robin', lastName='Cash', address='64 Zella Park'}
-- loading after update
Person loaded: Person{id=1, firstName='Dana', lastName='Whitley', address='XYZ'}
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 18 ms.




See Also