Close

Spring Framework - MappingSqlQueryWithParameters Examples

Spring Framework 

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.




See Also