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 .
|
The abstract class Rdbms Operation encapsulates a compiled reusable query, and defines methods to set a DataSource and query related parameters. Internally it uses an instance of JdbcTemplate to execute SQL. SqlQuery is also an abstract class and we need to implement it's method new Row Mapper() . The middle super class SqlOperation generally represents a sql operation. |
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 ProjectDependencies 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
|
|