|
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.sqlCREATE 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 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
|
|
