Spring之使用JDBC访问关系数据

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;@SpringBootApplicationpublic 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 tables2015-06-19 10:58:31.219  INFO 67731 --- [           main] hello.Application                        : Inserting customer record for John Woo2015-06-19 10:58:31.220  INFO 67731 --- [           main] hello.Application                        : Inserting customer record for Jeff Dean2015-06-19 10:58:31.220  INFO 67731 --- [           main] hello.Application                        : Inserting customer record for Josh Bloch2015-06-19 10:58:31.220  INFO 67731 --- [           main] hello.Application                        : Inserting customer record for Josh Long2015-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)