Accessing Relational Data using JDBC with Spring
目标:
使用JdbcTemplate访问关系数据库
添加依赖:
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
</dependency>
注:H2, an in-memory relational database engine
Spring Boot自动的创建到数据库的链接
@Autowired JdbcTemplate自动创建JdbcTemplate实例并连接到数据库
创建Customer实体类
package hello;
public class Customer {
private long id;
private String firstName, lastName;
public Customer(long id, String firstName, String lastName) {
this.id = id;
this.firstName = firstName;
this.lastName = lastName;
}
@Override
public String toString() {
return String.format(
"Customer[id=%d, firstName='%s', lastName='%s']",
id, firstName, lastName);
}
// getters & setters omitted for brevity
}
存储并取出数据
package hello;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.jdbc.core.JdbcTemplate;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;
@SpringBootApplication
public class Application implements CommandLineRunner {
private static final Logger log = LoggerFactory.getLogger(Application.class);
public static void main(String args[]) {
SpringApplication.run(Application.class, args);
}
@Autowired
JdbcTemplate jdbcTemplate;
@Override
public void run(String... strings) throws Exception {
log.info("Creating tables");
jdbcTemplate.execute("DROP TABLE customers IF EXISTS");
jdbcTemplate.execute("CREATE TABLE customers(" +
"id SERIAL, first_name VARCHAR(255), last_name VARCHAR(255))");
// Split up the array of whole names into an array of first/last names
List<Object[]> splitUpNames = Arrays.asList("John Woo", "Jeff Dean", "Josh Bloch", "Josh Long").stream()
.map(name -> name.split(" "))
.collect(Collectors.toList());
// Use a Java 8 stream to print out each tuple of the list
splitUpNames.forEach(name -> log.info(String.format("Inserting customer record for %s %s", name[0], name[1])));
// Uses JdbcTemplate's batchUpdate operation to bulk load data
jdbcTemplate.batchUpdate("INSERT INTO customers(first_name, last_name) VALUES (?,?)", splitUpNames);
log.info("Querying for customer records where first_name = 'Josh':");
jdbcTemplate.query(
"SELECT id, first_name, last_name FROM customers WHERE first_name = ?", new Object[] { "Josh" },
(rs, rowNum) -> new Customer(rs.getLong("id"), rs.getString("first_name"), rs.getString("last_name"))
).forEach(customer -> log.info(customer.toString()));
}
}
注:CommandLineRunner接口
实现该接口将在Application Context加载完成之后
执行该接口的成员方法:run(…) {… }
代码中看不懂的地方:比如“->”运算符等,属于jdk 8 中的特性。
测试:
输出信息如下:
2015-06-19 10:58:31.152 INFO 67731 --- [ main] hello.Application : Creating tables
2015-06-19 10:58:31.219 INFO 67731 --- [ main] hello.Application : Inserting customer record for John Woo
2015-06-19 10:58:31.220 INFO 67731 --- [ main] hello.Application : Inserting customer record for Jeff Dean
2015-06-19 10:58:31.220 INFO 67731 --- [ main] hello.Application : Inserting customer record for Josh Bloch
2015-06-19 10:58:31.220 INFO 67731 --- [ main] hello.Application : Inserting customer record for Josh Long
2015-06-19 10:58:31.230 INFO 67731 --- [ main] hello.Application : Querying for customer records where first_name = 'Josh':
2015-06-19 10:58:31.242 INFO 67731 --- [ main] hello.Application : Customer[id=3, firstName='Josh', lastName='Bloch']
2015-06-19 10:58:31.242 INFO 67731 --- [ main] hello.Application : Customer[id=4, firstName='Josh', lastName='Long']
2015-06-19 10:58:31.244 INFO 67731 --- [ main] hello.Application : Started Application in 1.693 seconds (JVM running for 2.054)