MappingSqlQueryWithParameters is another abstract subclass of SqlQuery. The concrete subclasses must implement the abstract mapRow(ResultSet, int) method to map each row of the JDBC ResultSet into an object.
package com.logicbig.example;
import org.springframework.jdbc.object.MappingSqlQueryWithParameters;
import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map;
public class PersonMappingSqlQueryWithParameters extends MappingSqlQueryWithParameters<Person> {
public PersonMappingSqlQueryWithParameters(DataSource dataSource, String sql) {
super(dataSource, sql);
}
@Override
protected Person mapRow(ResultSet resultSet, int rowNum,
Object[] parameters,
Map<?, ?> context) throws SQLException {
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;
}
}
package com.logicbig.example;
import org.springframework.beans.factory.annotation.Autowired;
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.SqlQuery;
import javax.annotation.PostConstruct;
import javax.sql.DataSource;
import java.sql.Types;
import java.util.Arrays;
import java.util.List;
public class MappingSqlQueryWithParamExample {
@Autowired
private DataSource dataSource;
private SqlQuery<Person> personSqlQuery;
@PostConstruct
private void postConstruct() {
personSqlQuery = new PersonMappingSqlQueryWithParameters(dataSource,
"select * from Person where id = ?");
personSqlQuery.declareParameter(new SqlParameterValue(Types.BIGINT, "id"));
}
public void runExample() {
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));
}
}
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-query4</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")//script to create person table
.build();
}
@Bean
public MappingSqlQueryWithParamExample dataHandler() {
return new MappingSqlQueryWithParamExample();
}
public static void main(String[] args) {
AnnotationConfigApplicationContext context =
new AnnotationConfigApplicationContext(AppConfig.class);
System.out.println("-- Running MappingSqlQueryWithParameters example --");
context.getBean(MappingSqlQueryWithParamExample.class).runExample();
}
}
Output
-- Running MappingSqlQueryWithParameters example --
Batch Rows inserted: [1, 1]
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'}
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.
