Java Web基础入门，Springboot入门(10)

语言都是相通的，只要搞清楚概念后就可以编写代码了。而概念是需要学习成本的。

本文首发于博客园-Ryan Miao. 由于限制2000字，只能分多篇。

Spring-JDBC提供了简化版的数据库连接操作。对于简单的连接数据库来说，spring-jdbc已经足够提供orm能力。当然，现在国内流行的orm还是Mybatis。不过，随着微服务拆分的盛行，jpa的优势更加明显。不管用什么框架，原理都是差不多的，就是封装复杂的映射逻辑，简化操作。

什么是JDBC?
JDBC即Java DataBase Connectivity，Java数据库连接，JDK自带了JDBC。

什么是Mybatis？
以下来自百度百科

MyBatis 是一款优秀的持久层框架，它支持定制化 SQL、存储过程以及高级映射。MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集。MyBatis 可以使用简单的 XML 或注解来配置和映射原生信息，将接口和 Java 的 POJOs(Plain Old Java Objects,普通的 Java对象)映射成数据库中的记录。

什么是JPA?
JPA是Java Persistence API的简称，中文名Java持久层API.

什么是ORM?

对象关系映射（英语：(Object Relational Mapping，简称ORM，或O/RM，或O/R mapping），是一种程序技术，用于实现面向对象编程语言里不同类型系统的数据之间的转换[1] 。从效果上说，它其实是创建了一个可在编程语言里使用的--“虚拟对象数据库”。

面向对象是从软件工程基本原则（如耦合、聚合、封装）的基础上发展起来的，而关系数据库则是从数学理论发展而来的，两套理论存在显著的区别。为了解决这个不匹配的现象，对象关系映射技术应运而生。

对象关系映射（Object-Relational Mapping）提供了概念性的、易于理解的模型化数据的方法。ORM方法论基于三个核心原则：

1. 简单：以最基本的形式建模数据。
2. 传达性：数据库结构被任何人都能理解的语言文档化。
3. 精确性：基于数据模型创建正确标准化的结构。
   典型地，建模者通过收集来自那些熟悉应用程序但不熟练的数据建模者的人的信息开发信息模型。建模者必须能够用非技术企业专家可以理解的术语在概念层次上与数据结构进行通讯。建模者也必须能以简单的单元分析信息，对样本数据进行处理。ORM专门被设计为改进这种联系。
   简单的说：ORM相当于中继数据, 即通过操作对象来完成sql语句，自动提供了对象和sql的映射。

为什么明明标题是JDBCTemplate, 却说了一堆别的？实际生产中，对关系型数据库的操作多是用Mybatis或Hibernate这样的ORM框架。而ORM框架的根源还是jdbc，因此，学习jdbc是学习其他ORM框架的第一步。

为什么不直接讲jdk自带的jdbc？当Java基础掌握好之后，jdbc也就是多一个library，学习jdbc也就是学习这个lib的用法而已。那么，既然有简化的spring-jdbc，自然可以先跳过原生。

下面开始简单使用spring-jdbc。

插入一条数据

在上一步的新建的com.test.demo.config.DBConfigurationTest中继续开发。添加一个新的测试：

@Transactional
@Test
public void testInsert() {
    final RoomTable room = new RoomTable("Doule Bed", "no", new Date(), new Date());

    final String sql = "INSERT INTO room(`name`, `comment`, `create_date`, `update_date`) VALUES (?,?,?,?)";
    final int rs = jdbcTemplate.update(sql,
            room.getName(), room.getComment(), room.getCreateDate(), room.getUpdateDate());
    System.out.println(rs);
}

1. @Transactional是spring提供的事物注解，标注这个在测试类中的含义是：每次运行完该测试类后，回滚(rollback).
2. jdbcTemplate.update(sql, 参数) 提供了占位符的数据操纵语句的执行。为什么要使用占位符(PreparedStatement)而不是直接拼接字符串？防止sql注入。
3. RoomTable是一个新建Entity，关于什么是Entity后面分层架构中将讲到。
4. rs是执行sql结束后，数据返回的一个数字，含义成功了多少行。

新建com.test.demo.domain.entity.RoomTable

package com.test.demo.domain.entity;

import java.util.Date;

/**
 * Created by Ryan Miao on 12/2/17.
 */
public class RoomTable {
    private Integer id;
    private String name;
    private String comment;
    private Date createDate;
    private Date updateDate;

    public RoomTable() {
    }

    public RoomTable(String name, String comment, Date createDate, Date updateDate) {
        this.name = name;
        this.comment = comment;
        this.createDate = createDate;
        this.updateDate = updateDate;
    }

    public RoomTable(Integer id, String name, String comment, Date createDate, Date updateDate) {
        this.id = id;
        this.name = name;
        this.comment = comment;
        this.createDate = createDate;
        this.updateDate = updateDate;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getComment() {
        return comment;
    }

    public void setComment(String comment) {
        this.comment = comment;
    }

    public Date getCreateDate() {
        return createDate;
    }

    public void setCreateDate(Date createDate) {
        this.createDate = createDate;
    }

    public Date getUpdateDate() {
        return updateDate;
    }

    public void setUpdateDate(Date updateDate) {
        this.updateDate = updateDate;
    }

    @Override
    public String toString() {
        return "RoomTable{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", comment='" + comment + '\'' +
                ", createDate=" + createDate +
                ", updateDate=" + updateDate +
                '}';
    }
}

RoomTable是一个Entity类，对应数据库的表。字段类型要一致。关于Java类型和SQL的数据库表映射规则，请查阅官网。

插入一条数据并返回主键

我们新建的表RoomTable是有ID的，我们创建了一个Room后要知道生成的id，来返回给前端。不然前端不知道id就无法进行修改之类的操作了。

@Transactional
@Test
public void testInsertAndGetKey() {
    final RoomTable room = new RoomTable("Doule Bed", "no", new Date(), new Date());
    final KeyHolder keyHolder = new GeneratedKeyHolder();

    final int update = jdbcTemplate.update((Connection con) -> {
        final String sql = "INSERT INTO room(`name`, `comment`, `create_date`, `update_date`) VALUES (?,?,?,?)";
        PreparedStatement preparedStatement = con.prepareStatement(sql,
                Statement.RETURN_GENERATED_KEYS);
        preparedStatement.setString(1, room.getName());
        preparedStatement.setString(2, room.getComment());
        preparedStatement.setObject(3, new Timestamp(room.getCreateDate().getTime()));
        preparedStatement.setObject(4, new Timestamp(room.getUpdateDate().getTime()));
        return preparedStatement;
    }, keyHolder);
    System.out.println("The number of success:"+update);
    System.out.println("The primary key of insert row: "+keyHolder.getKey().intValue());

    final List<Map<String, Object>> maps = jdbcTemplate.queryForList("SELECT * FROM room");
    System.out.println(maps);
}

1. KeyHolder用来接收自动生成的主键.
2. PreparedStatement用来创建一个占位符的sql语句.
3. 需要注意日期类型的映射规则，需要将java.util.Date转换为java.sql.*
4. queryForList可以查询当前数据中的内容

查询--findById

首先，修改下Date类型为datetime, 因为需要直到修改的具体时间。因此，room的scheme修改如下：

create database if not exists springboot_demo charset utf8 collate utf8_general_ci;
use springboot_demo;

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for room
-- ----------------------------
DROP TABLE IF EXISTS `room`;
CREATE TABLE `room` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(80) NOT NULL,
  `comment` varchar(200) DEFAULT NULL,
  `create_date` datetime NOT NULL,
  `update_date` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of room
-- ----------------------------
INSERT INTO `room` VALUES ('1', '大床房', '无窗', '2017-11-26 00:00:00', '2017-11-26 00:00:00');
INSERT INTO `room` VALUES ('2', 'Double Bed', 'no', '2017-12-06 00:00:00', '2017-12-06 00:00:00');
INSERT INTO `room` VALUES ('3', 'Big Bed', '', '2017-12-06 00:00:00', '2017-12-06 10:00:00');

默认添加3条记录。

在resources下新建schema.sql,填入上述内容。当springboot启动时，会自动加载这个sql。那么就会重新初始化数据库。

我们的测试类会真实启动springboot的，因此每个测试都会重新初始化数据库一遍。下面可以测试根据id查询内容。

@Transactional
@Test
public void testSelectOne(){
    String sql = "select `id`,`name`,`comment`,`create_date`,`update_date` from room WHERE id=?";
    RoomTable roomTable = jdbcTemplate.queryForObject(sql, (rs, rowNum) -> new RoomTable(rs.getInt("id"),
            rs.getString("name"),
            rs.getString("comment"),
            rs.getTime("create_date"),
            rs.getTime("update_date")), 3);
    System.out.println(roomTable);
    Assert.assertTrue(3== roomTable.getId());
    Assert.assertNotNull(roomTable.getCreateDate());
}

1. 注意要使用select 字段列表来获取想要的字段，不要用*
2. varchar的映射为String
3. int的映射为Integer
4. datetime的映射为time
5. 此处的映射为一个lambda表达式，从结果集中选择想要的字段来创建我们的映射关系
6. 最后一个参数是占位符的值，防止sql注入。

然后，可以观察到控制台重新启动springboot，并且运行了schema.sql。接下来需要注意的地方到了：

RoomTable{id=3, name='Big Bed', comment='', createDate=08:00:00, updateDate=18:00:00}

打印出查询的时间比我们插入的时间多了8h。很容易猜测到时区问题。因为我们是北京时间，UTC+8。所以，在从数据库中取出时间的时候，做了下时区转换。我们的项目把数据的时区当作是UTC了。事实上，在生产环境中确实应该把数据库的时区设置为UTC。因为我们是全球性的项目。当然，设置为UTC+8也是可以的。但为了防止困扰，设置为UTC是最佳选择。

然而，真正的问题还不是这个。我们数据库当前的timezone是多少？

mysql>  show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone |        |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set, 1 warning (0.00 sec)

系统时区，显然应该是北京时间，即UTC+8的。那么，我们为什么查询的时候会把数据库当作0时区呢？

因为Java里的北京时间对应的时区为Asia/Shanghai,修改配置文件：

spring.datasource.url=jdbc:mysql://localhost:3306/springboot_demo?serverTimezone=Asia/Shanghai&characterEncoding=utf-8

然后，重新运行测试。结果正常了。此时，我们的项目时区为系统时区，我们的数据时区为系统时区。我们连接的驱动转换也标记了数据库为北京时间。这样就不会出现时区问题。如果是生产环境，就要把数据库/服务器/驱动参数设置为UTC.

查询返回list

除了最常用的findbyId, 最常用的查询是返回一个list。因为我们的搜索是返回条件匹配的值，而匹配条件的item通常很多个，即list。

@Test
public void testSelectList(){
    String sql = "select `id`,`name`,`comment`,`create_date`,`update_date` from room WHERE id>? LIMIT 0,2";
    List<RoomTable> roomTableList = jdbcTemplate.query(sql, (rs, rowNum) -> new RoomTable(rs.getInt("id"),
            rs.getString("name"),
            rs.getString("comment"),
            rs.getTime("create_date"),
            rs.getTime("update_date")), 1);

    System.out.println(roomTableList);
    Assert.assertEquals(2, roomTableList.size());
}

1. 同样要做结果集映射
2. 同样需要传入占位符value
3. 返回值是一个list

删除一条数据

更新一条数据

批量添加数据

批量删除数据

批量更新数据