h2dabase基于内存的数据库，更常见于嵌入式数据库的使用场景，依赖小，功能齐全；一般来讲，正常的商业项目用到它的场景不多，但是在一些特殊的case中，还是比较有用的，比如用于单元测试，业务缓存，一些简单的示例demo等；本文将手把手教你创建一个继承h2dabase的项目，并支持从sql中导入预定好的schema和data

I. 项目创建

本文对应的示例demo，采用SpringBoot 2.2.1.RELEASE + maven 3.5.3 + IDEA进行开发

1. pom配置

关于如何创建一个springboot项目本文就不介绍了，在我们创建好的项目中，pom.xml文件如下

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.2.1.RELEASE</version>
    <relativePath/> <!-- lookup parent from repository -->
</parent>

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
    </dependency>
</dependencies>
    
<build>
    <pluginManagement>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </pluginManagement>
</build>

<repositories>
    <repository>
        <id>spring-snapshots</id>
        <name>Spring Snapshots</name>
        <url>https://repo.spring.io/libs-snapshot-local</url>
        <snapshots>
            <enabled>true</enabled>
        </snapshots>
    </repository>
    <repository>
        <id>spring-milestones</id>
        <name>Spring Milestones</name>
        <url>https://repo.spring.io/libs-milestone-local</url>
        <snapshots>
            <enabled>false</enabled>
        </snapshots>
    </repository>
    <repository>
        <id>spring-releases</id>
        <name>Spring Releases</name>
        <url>https://repo.spring.io/libs-release-local</url>
        <snapshots>
            <enabled>false</enabled>
        </snapshots>
    </repository>
</repositories>

重点关注一下dependency中的com.h2database，另外两个非必须，只是在后面的测试用例中会用到，推荐加上

从上面的引入也可以知道，我们将借助JPA来操作数据库

2. 属性配置

既然是连接数据库，当然少不了数据库的相关配置，在项目的资源路径下，新建配置文件application.properties

# 数据库的相关配置
spring.datasource.url=jdbc:h2:~/h2-db
spring.datasource.username=test
spring.datasource.password=
spring.datasource.driverClassName=org.h2.Driver

上面的配置方式，和我们的mysql数据库配置没有什么特别的，这里的url请注意一下

jdbc:h2:~/h2-db: 嵌入式使用姿势，会在用户根目录下生成一个名为h2-db.mv.db的文件（数据库的schema 和d column就存在里面）
jdbc:h2:mem:DBName;DB_CLOSE_DELAY=-1: 内存模式，应用重启之后数据库会清空，所以在测试用例中，可以考虑用这种

除了上面嵌入式的使用姿势之外，h2-dabase还支持通过tcp方式，指定一个远程的目录

jdbc:h2:tcp://localhost/~/test

上面是h2dabase的基本配置，为了更友好的展示，我们开启了h2dabase的web console控制台

##h2 web console设置
spring.datasource.platform=h2
#进行该配置后，h2 web consloe就可以在远程访问了。否则只能在本机访问。
spring.h2.console.settings.web-allow-others=true
#进行该配置，你就可以通过YOUR_URL/h2访问h2 web consloe
spring.h2.console.path=/h2
#进行该配置，程序开启时就会启动h2 web consloe
spring.h2.console.enabled=true

最好开启一下jpa的sql语句

spring.jpa.show-sql=true
spring.jpa.generate-ddl=true

II. 实例测试

上面配置搞完之后，基本上就可以说是完成了h2dabase的集成了

0. 入口

@SpringBootApplication
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class);
    }
}

SpringBoot应用的启动入口，上面执行之后，我们就可以通过http://localhost:8080/h2访问h2dabase的控制台，注意下面框处的内容，与前面的配置文件保持一致

登录之后，就是一个建议的数据库操作控制台了

1. Entity定义

下面这个属于JPA的知识点，对于jpa有兴趣的小伙伴，可以看一下前面的《JPA系列教程》

@Entity
@Table(name = "test")
public class TestEntity {
    @Id
    private Integer id;
    @Column
    private String name;

    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;
    }
}

2. Repository接口

数据库操作接口，直接使用默认的curd即可，并没有额外的添加方法

@Repository
public interface TestRepository extends CrudRepository<TestEntity, Integer> {
}

3. 测试case

接下来给几个CURD的测试case，来演示一下我们的集成效果

@RestController
public class TestController {
    @Autowired
    private TestRepository testRepository;

    @GetMapping("/save")
    public TestEntity save(Integer id, String name) {
        TestEntity testEntity = new TestEntity();
        testEntity.setId(id);
        testEntity.setName(name);
        return testRepository.save(testEntity);
    }

    @GetMapping("/update")
    public TestEntity update(Integer id, String name) {
        Optional<TestEntity> entity = testRepository.findById(id);
        TestEntity testEntity = entity.get();
        testEntity.setName(name);
        return testRepository.save(testEntity);
    }

    @GetMapping("/list")
    public Iterable list() {
        return testRepository.findAll();
    }

    @GetMapping("/get")
    public TestEntity get(Integer id) {
        return testRepository.findById(id).get();
    }

    @GetMapping("/del")
    public boolean del(Integer id) {
        testRepository.deleteById(id);
        return true;
    }
}

实测case如下

# 新增一条记录
curl 'http://localhost:8080/save?id=1&name=一灰灰'

# 查询记录
curl 'http://localhost:8080/get?id=1'

# 修改记录
curl 'http://localhost:8080/update?id=1&name=一灰灰Blog'

# 查询全部
curl 'http://localhost:8080/list'

# 删除记录
curl 'http://localhost:8080/del?id=1'

4. sql文件导入

注意我们前面的所有步骤，没有任何一个地方有说明需要主动去创建一个名为test的表，这一点和我们熟悉的mysql是不一样的；

某些时候我们可能希望将准备好的sql文件来初始化数据库，这个时候可以如下操作

对应的sql文件

表结构 schema-h2.sql

DROP TABLE IF EXISTS book_to_book_store;
DROP TABLE IF EXISTS book_store;
DROP TABLE IF EXISTS book;
DROP TABLE IF EXISTS author;

DROP SEQUENCE IF EXISTS s_author_id;
CREATE SEQUENCE s_author_id START WITH 1;

CREATE TABLE author (
  id INT NOT NULL,
  first_name VARCHAR(50),
  last_name VARCHAR(50) NOT NULL,
  date_of_birth DATE,
  year_of_birth INT,
  address VARCHAR(50),

  CONSTRAINT pk_t_author PRIMARY KEY (ID)
);

CREATE TABLE book (
  id INT NOT NULL,
  author_id INT NOT NULL,
  co_author_id INT,
  details_id INT,
  title VARCHAR(400) NOT NULL,
  published_in INT,
  language_id INT,
  content_text CLOB,
  content_pdf BLOB,

  rec_version INT,
  rec_timestamp TIMESTAMP,

  CONSTRAINT pk_t_book PRIMARY KEY (id),
  CONSTRAINT fk_t_book_author_id FOREIGN KEY (author_id) REFERENCES author(id),
  CONSTRAINT fk_t_book_co_author_id FOREIGN KEY (co_author_id) REFERENCES author(id)
);

CREATE TABLE book_store (
  name VARCHAR(400) NOT NULL,

  CONSTRAINT uk_t_book_store_name PRIMARY KEY(name)
);

CREATE TABLE book_to_book_store (
  book_store_name VARCHAR(400) NOT NULL,
  book_id INTEGER NOT NULL,
  stock INTEGER,

  CONSTRAINT pk_b2bs PRIMARY KEY(book_store_name, book_id),
  CONSTRAINT fk_b2bs_bs_name FOREIGN KEY (book_store_name)
                             REFERENCES book_store (name)
                             ON DELETE CASCADE,
  CONSTRAINT fk_b2bs_b_id    FOREIGN KEY (book_id)
                             REFERENCES book (id)
                             ON DELETE CASCADE
);

数据文件 data-h2.sql

INSERT INTO author VALUES (next value for s_author_id, 'George', 'Orwell', '1903-06-25', 1903, null);
INSERT INTO author VALUES (next value for s_author_id, 'Paulo', 'Coelho', '1947-08-24', 1947, null);

INSERT INTO book VALUES (1, 1, null, null, '1984', 1948, 1, 'To know and not to know, to be conscious of complete truthfulness while telling carefully constructed lies, to hold simultaneously two opinions which cancelled out, knowing them to be contradictory and believing in both of them, to use logic against logic, to repudiate morality while laying claim to it, to believe that democracy was impossible and that the Party was the guardian of democracy, to forget, whatever it was necessary to forget, then to draw it back into memory again at the moment when it was needed, and then promptly to forget it again, and above all, to apply the same process to the process itself -- that was the ultimate subtlety; consciously to induce unconsciousness, and then, once again, to become unconscious of the act of hypnosis you had just performed. Even to understand the word ''doublethink'' involved the use of doublethink..', null, 1, '2010-01-01 00:00:00');
INSERT INTO book VALUES (2, 1, null, null, 'Animal Farm', 1945, 1, null, null, null, '2010-01-01 00:00:00');
INSERT INTO book VALUES (3, 2, null, null, 'O Alquimista', 1988, 4, null, null, 1, null);
INSERT INTO book VALUES (4, 2, null, null, 'Brida', 1990, 2, null, null, null, null);

INSERT INTO book_store (name) VALUES
	('Orell Füssli'),
	('Ex Libris'),
	('Buchhandlung im Volkshaus');

INSERT INTO book_to_book_store VALUES
	('Orell Füssli', 1, 10),
	('Orell Füssli', 2, 10),
	('Orell Füssli', 3, 10),
	('Ex Libris', 1, 1),
	('Ex Libris', 3, 2),
	('Buchhandlung im Volkshaus', 3, 1);

上面两个文件准备好，接下来我们如何导入呢？

借助sql-maven-plugin方式，在pom配置文件中，添加下面这段

<plugins>
    <plugin>
        <groupId>org.codehaus.mojo</groupId>
        <artifactId>sql-maven-plugin</artifactId>

        <executions>
            <execution>
                <id>create-database-h2</id>
                <phase>generate-sources</phase>
                <goals>
                    <goal>execute</goal>
                </goals>
            </execution>
        </executions>

        <configuration>
            <driver>org.h2.Driver</driver>
            <url>jdbc:h2:~/h2-db</url>
            <username>test</username>
            <password></password>
            <autocommit>true</autocommit>
            <srcFiles>
                <srcFile>src/main/resources/schema-h2.sql</srcFile>
                <srcFile>src/main/resources/data-h2.sql</srcFile>
            </srcFiles>
        </configuration>

        <dependencies>
            <dependency>
                <groupId>com.h2database</groupId>
                <artifactId>h2</artifactId>
                <version>1.4.200</version>
            </dependency>
        </dependencies>
    </plugin>
</plugins>

然后如下操作即可

导入成功之后，再到h2控制台，就可以查看对应的数据

II. 其他

0. 项目

工程：https://github.com/liuyueyi/spring-boot-demo
项目源码: https://github.com/liuyueyi/spring-boot-demo/tree/master/spring-boot/100-h2database

2 - Hibernate

据说国外的大仓更喜欢使用Hibernate操作数据库，本手册将手把手教你完全使用Hibernate/JPA方式支撑业务需求

2.1 - 1.JPA之基础环境搭建

JPA（Java Persistence API）Java持久化API，是 Java 持久化的标准规范，Hibernate是持久化规范的技术实现，而Spring Data JPA是在 Hibernate 基础上封装的一款框架。JPA作为标准，实际上并没有说局限于某个固定的数据源，事实上mysql，mongo， solr都是ok的。接下来我们将介绍下springboot结合jpa 来实现mysql的curd以及更加复杂一点的sql支持

jpa系列教程将包含以下几块

环境搭建
基础的插入、修改、删除数据的使用姿势
基础的单表查询，如(>, <, = , in, like, between)，分页，排序等
多表关联查询
事物使用

本篇为开始第一篇，先搭建一个可以愉快玩耍的jpa项目

I. 环境搭建

我们选择的数据库为mysql，所以有必要先安装一下，这里跳过mysql的安装教程，直接进入springboot项目的搭建

1. pom依赖

我们这里选择的是2.0.4.RELEASE版本进行演示

 <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.0.4.RELEASE</version>
    <relativePath/> <!-- lookup parent from update -->
</parent>

<properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
    <spring-cloud.version>Finchley.RELEASE</spring-cloud.version>
    <java.version>1.8</java.version>
</properties>

<dependencies>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>fastjson</artifactId>
        <version>1.2.45</version>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter</artifactId>
    </dependency>

    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>
    
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
</dependencies>

<build>
    <pluginManagement>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </pluginManagement>
</build>

<repositories>
    <repository>
        <id>spring-milestones</id>
        <name>Spring Milestones</name>
        <url>https://repo.spring.io/milestone</url>
        <snapshots>
            <enabled>false</enabled>
        </snapshots>
    </repository>
</repositories>

上面的pom依赖中，关键的是下面两个，第一个引入的是jpa相关包，后面那个则是mysql的连接依赖，相当于指定操作mysql数据库

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

2. 数据准备

创建一个测试表进行后续的读写操作，为了后续的事物讲解方便，我们创建一个表，里面存了每个人的钱

CREATE TABLE `money` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '用户名',
  `money` int(26) NOT NULL DEFAULT '0' COMMENT '钱',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0',
  `create_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

在表中随意插入几条数据，以方便后面使用

INSERT INTO `money` (`id`, `name`, `money`, `is_deleted`, `create_at`, `update_at`)
VALUES
	(1, '一灰灰blog', 100, 0, '2019-04-18 17:01:40', '2019-04-18 17:01:40'),
	(2, '一灰灰2', 200, 0, '2019-04-18 17:01:40', '2019-04-18 17:01:40');

dbdata

3. 属性配置

创建springboot工程之后，添加mysql的相关配置，在resources目录下，新建文件 application.properties

## DataSource
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/story?useUnicode=true&characterEncoding=UTF-8&useSSL=false
#spring.datasource.driver-class-name= com.mysql.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=

config

4. 项目构建并测试

根据JPA的一般使用姿势，我们需要针对上面的表，创建一个对应的POJO对象，将它们关联起来，代码如下：

注意下几个注解 @Entity, @Table, @Column, @Id, @GeneratedValue
注意下POJO中字段的类型，这里保证了和db的字段定义类型一致
（关于上面两点的更多知识点，后面的文章会给出更详细用法说明，欢迎持续跟进）

package com.git.hui.boot.jpa.entity;

import lombok.Data;

import javax.persistence.*;
import java.sql.Date;

/**
 * Created by @author yihui in 21:01 19/6/10.
 */
@Data
@Entity
@Table(name = "money")
public class MoneyPO {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    @Column(name = "name")
    private String name;

    @Column(name = "money")
    private Long money;

    @Column(name = "is_deleted")
    private Byte isDeleted;

    @Column(name = "create_at")
    private Date createAt;

    @Column(name = "update_at")
    private Date updateAt;
}

表结构定义完毕之后，接下来就是定义db的操作api，jpa的使用，通过方法名来解析出对应的sql，我们这里定义一个简单的Money表的操作API:

MoneyDemoRepository 继承自 JpaRepository
两个泛型参数，第一个表示这个repository操作的表绑定的POJO，第二个表示自增id类型

package com.git.hui.boot.jpa.repository;

import com.git.hui.boot.jpa.entity.MoneyPO;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

/**
 * Created by @author yihui in 21:01 19/6/10.
 */
public interface MoneyDemoRepository extends JpaRepository<MoneyPO, Integer> {
}

上面两个定义完毕之后，不需要其他的操作，就可以进行测试环境了，上面这个Repository提供了一些简单的操作

package com.git.hui.boot.jpa;

import com.git.hui.boot.jpa.demo.JpaQueryDemo;
import com.git.hui.boot.jpa.entity.MoneyPO;
import com.git.hui.boot.jpa.repository.MoneyDemoRepository;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

/**
 * Created by @author yihui in 20:58 19/6/10.
 */
@SpringBootApplication
public class Application {
    public Application(MoneyDemoRepository moneyDemoRepository) {
        MoneyPO moneyPO = moneyDemoRepository.findById(1).get();
        System.out.println(moneyPO);
    }

    public static void main(String[] args) {
        SpringApplication.run(Application.class);
    }
}

针对上面的测试case进行简单的说明，前面定义了一个POJO对象和一个RepositoryAPI，我们想直接操作对应的表，需要借助这个RepositoryAPI对象，但是它是接口类型，我们没法直接使用的，因为我们是在Spring生态体系中，所以可以直接通过IoC注入方式使用

所以上面的测试中，MoneyDemoRepository 对象实际上是由框架生成的一个代理对象，下面我们看下执行结果

test

5. 小结

从上面的步骤下来，会发现搭建一个jpa的项目工程属于比较简单的过程，添加必要的依赖，稍微注意的是两个

创建一个POJO 与我们实际的表关联起来
创建一个RepositoryApi继承自org.springframework.data.repository.CrudRepository
通过IoC/DI方式注入RepositoryApi对象，然后可以愉快的进行db操作

II. 其他

0. 项目

2.2 - 2.JPA之新增记录使用姿势

上一篇文章介绍了如何快速的搭建一个JPA的项目环境，并给出了一个简单的演示demo，接下来我们开始业务教程，也就是我们常说的CURD，接下来进入第一篇，如何添加数据

通过本篇文章，你可以get到以下技能点

POJO对象如何与表关联
如何向DB中添加单条记录
如何批量向DB中添加记录
save 与 saveAndFlush的区别

I. 环境准备

实际开始之前，需要先走一些必要的操作，如安装测试使用mysql，创建SpringBoot项目工程，设置好配置信息等，关于搭建项目的详情可以参考前一篇文章 190612-SpringBoot系列教程JPA之基础环境搭建

下面简单的看一下演示添加记录的过程中，需要的配置

1. 表准备

沿用前一篇的表，结构如下

CREATE TABLE `money` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '用户名',
  `money` int(26) NOT NULL DEFAULT '0' COMMENT '钱',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0',
  `create_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

2. 项目配置

配置信息，与之前有一点点区别，我们新增了更详细的日志打印；本篇主要目标集中在添加记录的使用姿势，对于配置说明，后面单独进行说明

## DataSource
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/story?useUnicode=true&characterEncoding=UTF-8&useSSL=false
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=
## jpa相关配置
spring.jpa.database=MYSQL
spring.jpa.hibernate.ddl-auto=none
spring.jpa.show-sql=true
spring.jackson.serialization.indent_output=true
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

II. Insert使用教程

在开始之前，先声明一下，因为个人实际项目中并没有使用到JPA，对JPA的原则和hibernate的一些特性了解的也不多，目前处于学习探索阶段，主要是介绍下使用姿势，下面的东西都是经过测试得出，有些地方描述可能与规范不太一样，或者有些差错，请发现的大佬指正

接下来我们进入正题，如何通过JPA实现我们常见的Insert功能

1. POJO与表关联

首先第一步就是将POJO对象与表关联起来，这样就可以直接通过java的操作方式来实现数据库的操作了；

我们直接创建一个MoneyPo对象，包含上面表中的几个字段

@Data
public class MoneyPO {
    private Integer id;

    private String name;

    private Long money;

    private Byte isDeleted;

    private Timestamp createAt;

    private Timestamp updateAt;
}

自然而然地，我们就有几个问题了

这个POJO怎么告诉框架它是和表Money绑定的呢？
Java中变量命令推荐驼峰结构，那么 isDeleted 又如何与表中的 is_deleted 关联呢？
POJO中成员变量的类型如何与表中的保持一致呢，如果不一致会怎样呢？

针对上面的问题，一个一个来说明

对hibernate熟悉的同学，可能知道我可以通过xml配置的方式，来关联POJO与数据库表（当然mybatis也是这么玩的），友情链接一下hibernate的官方说明教程；我们使用SpringBoot，当然是选择注解的方式了，下面是通过注解的方式改造之后的DO对象

package com.git.hui.boot.jpa.entity;

import lombok.Data;
import org.springframework.data.annotation.CreatedDate;

import javax.persistence.*;
import java.sql.Timestamp;

/**
 * Created by @author yihui in 21:01 19/6/10.
 */
@Data
@Entity(name="money")
public class MoneyPO {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Integer id;

    @Column(name = "name")
    private String name;

    @Column(name = "money")
    private Long money;

    @Column(name = "is_deleted")
    private Byte isDeleted;

    @Column(name = "create_at")
    @CreatedDate
    private Timestamp createAt;

    @Column(name = "update_at")
    @CreatedDate
    private Timestamp updateAt;
}

有几个有意思的地方，需要我们注意

a. entity注解

@Entity 这个注解比较重要，用于声明这个POJO是一个与数据库中叫做 money 的表关联的对象；

@Entity注解有一个参数name，用于指定表名，如果不主动指定时，默认用类名，即上面如果不指定那么，那么默认与表 moneypo 绑定

另外一个常见的方式是在类上添加注解 @Table，然后指定表名，也是可以的

@Data
@Entity
@Table(name = "money")
public class MoneyPO {
}

b. 主键指定

我们可以看到id上面有三个注解，我们先看下前面两个

@Id 顾名思义，用来表明这家伙是主键，比较重要，需要特殊关照
@GeneratedValue 设置初始值，谈到主键，我们一般会和”自增“这个一起说，所以你经常会看到的取值为 strategy = GenerationType.IDENTITY (由数据库自动生成）

这个注解主要提供了四种方式，分别说明如下

取值	说明
`GenerationType.TABLE`	使用一个特定的数据库表格来保存主键
`GenerationType.SEQUENCE`	根据底层数据库的序列来生成主键，条件是数据库支持序列
`GenerationType.IDENTITY`	主键由数据库自动生成（主要是自动增长型）
`GenerationType.AUTO`	主键由程序控制

关于这几种使用姿势，这里不详细展开了，有兴趣的可以可以看一下这博文: @GeneratedValue

c. Column注解

这个注解就是用来解决我们pojo成员名和数据库列名不一致的问题的，这个注解内部的属性也不少，相对容易理解，后面会单开一章来记录这些常用注解的说明查阅

d. CreateDate注解

这个注解和前面不一样的是它并非来自jpa-api包，而是spring-data-common包中提供的，表示会根据当前时间创建一个时间戳对象

e. 其他

到这里这个POJO已经创建完毕，后续的表中添加记录也可以直接使用它了，但是还有几个问题是没有明确答案的，先提出来，期待后文可以给出回答

POJO属性的类型与表中类型
mysql表中列可以有默认值，这个在POJO中怎么体现
一个表包含另一个表的主键时（主键关联，外键）等特殊的情况，POJO中有体现么？

2. Repository API声明

jpa非常有意思的一点就是你只需要创建一个接口就可以实现db操作，就这么神奇，可惜本文里面见不到太多神奇的用法，这块放在查询篇来见证奇迹

我们定义的API需要继承自org.springframework.data.repository.CrudRepository，如下

package com.git.hui.boot.jpa.repository;

import com.git.hui.boot.jpa.entity.MoneyPO;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.repository.CrudRepository;

/**
 * 新增数据
 * Created by @author yihui in 11:00 19/6/12.
 */
public interface MoneyCreateRepository extends CrudRepository<MoneyPO, Integer> {
}

好的，到这里就可以直接添加数据了（感觉什么都没干，你居然告诉我可以插入数据？？？）

3. 使用姿势

a. 基础使用case

常规的使用姿势，无非单个插入和批量插入，我们先来看一下常规操作

@Component
public class JpaInsertDemo {
    @Autowired
    private MoneyCreateRepository moneyCreateRepository;
    public void testInsert() {
        addOne();
        addMutl();
    }

    private void addOne() {
        // 单个添加
        MoneyPO moneyPO = new MoneyPO();
        moneyPO.setName("jpa 一灰灰");
        moneyPO.setMoney(1000L);
        moneyPO.setIsDeleted((byte) 0x00);
        Timestamp now = new Timestamp(System.currentTimeMillis());
        moneyPO.setCreateAt(now);
        moneyPO.setUpdateAt(now);

        MoneyPO res = moneyCreateRepository.save(moneyPO);
        System.out.println("after insert res: " + res);
    }

    private void addMutl() {
        // 批量添加
        MoneyPO moneyPO = new MoneyPO();
        moneyPO.setName("batch jpa 一灰灰");
        moneyPO.setMoney(1000L);
        moneyPO.setIsDeleted((byte) 0x00);
        Timestamp now = new Timestamp(System.currentTimeMillis());
        moneyPO.setCreateAt(now);
        moneyPO.setUpdateAt(now);

        MoneyPO moneyPO2 = new MoneyPO();
        moneyPO2.setName("batch jpa 一灰灰");
        moneyPO2.setMoney(1000L);
        moneyPO2.setIsDeleted((byte) 0x00);
        moneyPO2.setCreateAt(now);
        moneyPO2.setUpdateAt(now);

        Iterable<MoneyPO> res = moneyCreateRepository.saveAll(Arrays.asList(moneyPO, moneyPO2));
        System.out.println("after batchAdd res: " + res);
    }
}

看下上面的两个插入方式，就这么简单，

通过IoC/DI注入 repository
创建PO对象，然后调用save, saveAll方法就ok了

上面是一般的使用姿势，那么非一般使用姿势呢？

b. 插入时默认值支持方式

在创建表的时候，我们知道字段都有默认值，那么如果PO对象中某个成员我不传，可以插入成功么？会是默认的DB值么？

private void addWithNull() {
    // 单个添加
    try {
        MoneyPO moneyPO = new MoneyPO();
        moneyPO.setName("jpa 一灰灰 ex");
        moneyPO.setMoney(2000L);
        moneyPO.setIsDeleted(null);
        MoneyPO res = moneyCreateRepository.save(moneyPO);
        System.out.println("after insert res: " + res);
    } catch (Exception e) {
        System.out.println("addWithNull field: " + e.getMessage());
    }
}

当看到上面的try/catch可能就有预感，上面的执行多半要跪（😏😏😏），下面是执行截图，也是明确告诉了我们这个不能为null

那么有办法解决么？难道就这么向现实放弃，向大佬妥协么？

默认值嘛，一个很容易想到的方法，我直接在PO对象中给一个默认值，是不是也可以，然后我们的PO改造为

@Data
@Entity
@Table(name = "money")
public class MoneyPO {
    // ... 省略其他
    
    @Column(name = "is_deleted")
    private Byte isDeleted = (byte) 0x00;
}

测试代码注释一行,变成下面这个

private void addWithNull() {
    // 单个添加
    try {
        MoneyPO moneyPO = new MoneyPO();
        moneyPO.setName("jpa 一灰灰 ex");
        moneyPO.setMoney(2000L);
//            moneyPO.setIsDeleted(null);
        MoneyPO res = moneyCreateRepository.save(moneyPO);
        System.out.println("after insert res: " + res);
    } catch (Exception e) {
        System.out.println("addWithNull field: " + e.getMessage());
    }
}

再次执行看下结果如何，顺利走下去，没有报错，喜大普奔

这样我就满足了吗？要是手抖上面测试注释掉的那一行忘了注释，岂不是依然会跪？而且我希望是表中的默认值，直接在代码中硬编码会不会不太优雅？这个主动设置的默认值，在后面查询的时候会不会有坑？

作为一个有追求的新青年，当然对上面的答案say no了

我们的解决方法也简单，在PO类上，加一个注解 @DynamicInsert，表示在最终创建sql的时候，为null的项就不要了哈

然后我们的新的PO，在原始版本上变成如下(注意干掉上一次的默认值）

@Data
@DynamicInsert
@Entity
@Table(name = "money")
public class MoneyPO {
  // ... 省略
}

再来一波实际的测试，完美了，没有抛异常，插入成功，而且控制台中输出的sql日志也验证了我们上面说的@DynamicInsert注解的作用（日志输出hibernate的sql，可以通过配置application.properties文件，添加参数spring.jpa.show-sql=true）

c. 类型关联

针对上面的PO对象，有几个地方感觉不爽，isDelete我想要boolean，true表示删除false表示没删除，搞一个byte用起来太不方便了，这个要怎么搞？

这个并不怎么复杂，因为直接将byte类型改成boolean就可以了，如果db中时0对应的false；1对应的true，下面是验证结果，并没有啥问题

在JPA规范中，并不是所有的类型的属性都可以持久化的，下表列举了可映射为持久化的属性类型:

分类	类型
基本类型	byte、int、short、long、boolean、char、float、double
基本类型封装类	Byte、Integer、Short、Long、Boolean、Character、Float、Double
字节和字符数组	byte[]、Byte[]、char[]、Character[]
大数值类型	BigInteger、BigDecimal
字符串类型	String
时间日期类	java.util.Date、java.util.Calendar、java.sql.Date、java.sql.Time、java.sql.Timestamp
集合类	java.util.Collection、java.util.List、java.util.Set、java.util.Map
枚举类型
嵌入式

关于类型关联，在查询这一篇会更详细的进行展开说明，比如有个特别有意思的点

如db中is_delete为1，需要映射到PO中的false，0映射到true，和我们上面默认的是个反的，要怎么搞？

d. 插入时指定ID

再插入的时候，我们上面的case都是没有指定id的，但是如果你指定了id，会发生什么事情？

我们将po恢复到之前的状态，测试代码如下

private void addWithId() {
    // 单个添加
    MoneyPO moneyPO = new MoneyPO();
    moneyPO.setId(20);
    moneyPO.setName("jpa 一灰灰 ex");
    moneyPO.setMoney(2200L + ((long) (Math.random() * 100)));
    moneyPO.setIsDeleted((byte) 0x00);
    MoneyPO res = moneyCreateRepository.save(moneyPO);
    System.out.println("after insert res: " + res);
}

看下输出结果，惊讶的发现，这个指定id并没有什么卵用，最终db中插入的记录依然是自增的方式来的

为什么会这样子呢，我们看下sql是怎样的

直接把id给丢了，也就是说我们设置的id不生效，我们知道@GeneratedValue 这个注解指定了id的增长方式，如果我们去掉这个注解会怎样

从输出结果来看：

如果这个id对应的记录不存在，则新增
如果这个id对应的记录存在，则更新

不然这个注解可以主动指定id方式进行插入or修改，那么如果没有这个注解，插入时也不指定id，会怎样呢？

很遗憾的是直接抛异常了，没有这个注解，就必须手动赋值id了

4. 小结

本文主要介绍了下如何使用JPA来实现插入数据，单个or批量插入，也抛出了一些问题，有的给出了回答，有的等待后文继续跟进，下面简单小结一下主要的知识点

POJO与表关联方式
- 注意几个注解的使用
- 如 @Entity, @Table 用于指定这个POJO对应哪张表
- 如 @Column 用于POJO的成员变量与表中的列进行关联
- 如 @Id @GeneratedValue来指定主键
- POJO成员变量类型与DB表中列的关系
db插入的几种姿势
- save 单个插入
- saveAll 批量插入
- 插入时，如要求DO中成员为null时，用mysql默认值，可以使用注解 @DynamicInsert，实现最终拼接部分sql方式插入
- 指定id查询时的几种case

此外本文还留了几个坑没有填

POJO成员类型与表列类型更灵活的转换怎么玩？
save 与 saveAndFlush 之间的区别（从命名上，前者保存，可能只保存内存，不一定落库；后者保存并落库，但是没有找到验证他们区别的实例代码，所以先不予评价）
注解的更详细使用说明

II. 其他

-1. 相关博文

0. 项目

2.3 - 3.JPA之update使用姿势

上面两篇博文拉开了jpa使用姿势的面纱一角，接下来我们继续往下扯，数据插入db之后，并不是说就一层不变了，就好比我在银行开了户，当然是准备往里面存钱了，有存就有取（特别是当下银行利率这么低还不如买比特币屯着，截止19年6月22日，btc已经突破1.1w$，可惜没钱买😭）这就是我们今天的主题，数据更新–update的使用姿势

通过本篇博文，您至少可以选到

save() 直接根据id来修改记录
利用jpl 实现查询修改的使用姿势
初识事物的神秘面纱

I. 环境准备

在开始之前，当然得先准备好基础环境，如安装测试使用mysql，创建SpringBoot项目工程，设置好配置信息等，关于搭建项目的详情可以参考前一篇文章

下面简单的看一下演示添加记录的过程中，需要的配置

1. 表准备

沿用前一篇的表，结构如下

CREATE TABLE `money` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '用户名',
  `money` int(26) NOT NULL DEFAULT '0' COMMENT '钱',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0',
  `create_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

2. 项目配置

配置信息，与之前有一点点区别，我们新增了更详细的日志打印；本篇主要目标集中在添加记录的使用姿势，对于配置说明，后面单独进行说明

## DataSource
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/story?useUnicode=true&characterEncoding=UTF-8&useSSL=false
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=
## jpa相关配置
spring.jpa.database=MYSQL
spring.jpa.hibernate.ddl-auto=none
spring.jpa.show-sql=true
spring.jackson.serialization.indent_output=true
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

3. 数据准备

数据修改嘛，所以我们先向表里面插入两条数据，用于后面的操作

INSERT INTO `money` (`id`, `name`, `money`, `is_deleted`, `create_at`, `update_at`)
VALUES
	(21, 'jpa 修改->一灰灰', 1212, 0, '2019-06-22 21:41:13', '2019-06-22 21:41:13'),
	(22, 'jpa 修改->一灰灰', 6666, 0, '2019-06-22 21:41:13', '2019-06-22 21:41:13');

II. Update使用教程

下面开始进入正题，为方便初看的同学（没空或者没兴趣瞅前面几个博文的同学）会有部分内容和前面的博文相同，看过的请无视

1. 表关联POJO

前面插入篇已经介绍了POJO的逐步创建过程，已经对应的注解含义，下面直接贴出成果

@Data
@DynamicInsert
@Entity
@Table(name = "money")
public class MoneyPO {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")

    private Integer id;

    @Column(name = "name")
    private String name;

    @Column(name = "money")
    private Long money;

    @Column(name = "is_deleted")
    private Byte isDeleted;

    @Column(name = "create_at")
    @CreatedDate
    private Timestamp createAt;

    @Column(name = "update_at")
    @CreatedDate
    private Timestamp updateAt;
}

上面类中的几个注解，说明如下

@Data 属于lombok注解，与jpa无关，自动生成getter/setter/equals/hashcode/tostring等方法
@Entity, @Table jpa注解，表示这个类与db的表关联，具体匹配的是表 money
@Id @GeneratedValue 作用与自增主键
@Column表明这个属性与表中的某列对应
@CreateDate根据当前时间来生成默认的时间戳

2. Repository API声明

接下来我们新建一个api继承自CurdRepository，然后通过这个api来与数据库打交道

public interface MoneyUpdateRepository extends CrudRepository<MoneyPO, Integer> {
}

3. 使用姿势

a. save

在前面一篇插入博文中，我们知道当POJO的id存在时，调用save方法可能有两种情况

若db中这个id对应的字段不存在，则插入
若db中这个id对应的字段存在，则更新

我们来试一下更新的效果，下面的代码演示了两块，一个是当po中的所有成员值有效，更新其中的一个时，会怎样；另外一个演示的是部分更新时会怎样（name为空，表示我不希望更新name）

public void simpleUpdateById() {
    MoneyPO record = moneyUpdateRepository.findById(21).get();
    // 直接修改这个record的内容
    record.setMoney(3333L);
    moneyUpdateRepository.save(record);

    record = moneyUpdateRepository.findById(21).get();
    System.out.println("after updateMoney record: " + record);


    record.setName(null);
    record.setMoney(6666L);
    moneyUpdateRepository.save(record);
    
    record = moneyUpdateRepository.findById(21).get();
    System.out.println("after updateMoney record: " + record);
}

输出结果发现，前面执行成功，后面执行失败

上面为第一个执行结果，从拼接的sql可以知道，是全量的修改；输出结果也如我们预期

后面将name设置为空之后，再次更新，发现抛出异常，如下，这个是因为我们的db限制，字段不允许有null的存在

从拼接的sql上看，我们知道是因为每个成员都作为了update sql家族的一员，在insert这一篇中我们也遇到了类似的问题，当时是在POJO上添加注解@DynamicInsert，根据实际需要选择插入，那么在更新这里是不是也有类似的注解呢

@Data
@DynamicUpdate
@DynamicInsert
@Entity
@Table(name = "money")
public class MoneyPO {
}

在pojo上添加注解@DynamicUpdate之后，再次进行尝试，结果如下

居然还是失败了，从输出的sql来看，居然把name和money都当成了sql的一部分，难道是因为我们调用了setter方法的原因么。待着猜测，再来一次

MoneyPO toUpdate = new MoneyPO();
toUpdate.setId(21);
toUpdate.setMoney(6666L);
moneyUpdateRepository.save(toUpdate);
record = moneyUpdateRepository.findById(21).get();
System.out.println("after updateMoney record: " + record);

输出结果如下，看来我们上面的猜测并不对，拼接sql应该是根据哪个字段发生了变化，就把哪个做为sql的一部分来做的

上面这个使用姿势看完之后，会有一个明显的感觉，这个更新的支持，必须先获取目标对象再进行修改，很难满足我们的日常业务场景；

b. 查询更新

根据某个条件来更新对应的数据，这个就比较常见了，在jpa中，没有找到根据方法名来支撑这种场景的方式，但是发现了另外一个有意思的东西–jql

直接在方法方面，添加注解，注解内部写sql

/**
 * 根据金钱来修改状态
 *
 * @param money
 * @param state
 */
@Modifying
@Query("update MoneyPO m set m.isDeleted=?2 where  m.money=?1")
void updateStateByMoney(Long money, Byte state);

/**
 * 表达式计算
 *
 * @param id
 * @param money
 */
@Modifying
@Query("update MoneyPO m set m.money=m.money + ?2 where m.id=?1")
void addMoneyById(Integer id, Long money);

上面就是一个查询更新的case，注意两个注解

@Modifying 这个必须有，告诉框架我们执行的是更新/删除操作
@Query 内部是正常的sql语句，但是需要注意的是表名，不是实际的表，而是我们前面定义的POJO

然后来测试一下使用

public void updateByQuery() {
    // 通过查询修改
    moneyUpdateRepository.updateStateByMoney(6666L, (byte) 0x01);

    MoneyPO record = moneyUpdateRepository.findById(21).get();
    System.out.println("after update record: " + record);


    moneyUpdateRepository.addMoneyById(21, 3333L);
    record = moneyUpdateRepository.findById(21).get();
    System.out.println("after addMoney record: " + record);
}

执行上面的代码，悲催的发现居然报错了 Caused by: javax.persistence.TransactionRequiredException: Executing an update/delete query

从堆栈的描述上来看，更新/删除必须要开启事务，那么事务是什么东西呢？下面推荐几篇博文

关于jpa中更多事务相关的，后面再引入，我们先回到本文主题，如何解决问题: 在调用上面的方法上面添加事务注解即可

@Transactional
public void testUpdate() {
    simpleUpdateById();
    updateByQuery();
}

再次执行之后，结果如下

看上面的结果，发现money+3333之后的输出结果居然还是6666；但是我们再看db的结果，已经是9999了，为什么会这样呢？

上面这个问题比较有意思了，初步猜测与事物已经jpa的内部缓存机制有关系，至于具体是不是这样，有必要专门开坑来验证一二

4. 小结

利用JPA实现表数据的更新，上面主要介绍了两种方式， save + jql

save

通过save更新时，需要指定id来实现单条记录的修改

jql

语法与sql差不多，配合两个注解 @Modifying, @Query来使用，下面是一个实例，两点需要注意

表名是我们定义的与db中表关联的POJO
参数传递格式为?index， index为参数位置

@Modifying
@Query("update MoneyPO m set m.isDeleted=?2 where  m.money=?1")
void updateStateByMoney(Long money, Byte state);

数据修改和删除，需要显示声明事务，否则会报错，一个是在调用的方法上添加注解 @Transactional，或者直接在repository api的接口上添加注解 @Transactional

II. 其他

0. 源码与相关博文

源码

相关博文

2.4 - 4.JPA之delete使用姿势详解

常见db中的四个操作curd，前面的几篇博文分别介绍了insert，update，接下来我们看下delete的使用姿势，通过JPA可以怎样删除数据

一般来讲是不建议物理删除（直接从表中删除记录）数据的，在如今数据就是钱的时代，更常见的做法是在表中添加一个表示状态的字段，然后通过修改这个字段来表示记录是否有效，从而实现逻辑删除；这么做的原因如下

物理删除，如果出问题恢复比较麻烦
无法保证代码一定准确，在出问题的时候，删错了数据，那就gg了
删除数据，会导致重建索引
Innodb数据库对于已经删除的数据只是标记为删除，并不真正释放所占用的磁盘空间，这就导致InnoDB数据库文件不断增长，也会导致表碎片
逻辑删除，保留数据，方便后续针对数据的挖掘或者分析

I. 环境准备

在开始之前，当然得先准备好基础环境，如安装测试使用mysql，创建SpringBoot项目工程，设置好配置信息等，关于搭建项目的详情可以参考前一篇文章

190612-SpringBoot系列教程JPA之基础环境搭建

下面简单的看一下演示添加记录的过程中，需要的配置

1. 表准备

沿用前一篇的表，结构如下

CREATE TABLE `money` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '用户名',
  `money` int(26) NOT NULL DEFAULT '0' COMMENT '钱',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0',
  `create_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

2. 项目配置

配置信息，与之前有一点点区别，我们新增了更详细的日志打印；本篇主要目标集中在添加记录的使用姿势，对于配置说明，后面单独进行说明

## DataSource
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/story?useUnicode=true&characterEncoding=UTF-8&useSSL=false
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=
## jpa相关配置
spring.jpa.database=MYSQL
spring.jpa.hibernate.ddl-auto=none
spring.jpa.show-sql=true
spring.jackson.serialization.indent_output=true
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

3. 数据准备

数据修改嘛，所以我们先向表里面插入两条数据，用于后面的操作

INSERT INTO `money` (`id`, `name`, `money`, `is_deleted`, `create_at`, `update_at`)
VALUES
	(20, 'jpa 一灰灰5', 2323, 0, '2019-07-02 08:42:41', '2019-07-02 08:42:41'),
	(21, 'jpa 一灰灰6', 2333, 0, '2019-07-02 08:42:41', '2019-07-02 08:42:41'),
	(22, 'jpa 一灰灰7', 6666, 0, '2019-07-02 08:42:41', '2019-07-02 08:42:41'),
	(23, 'jpa 一灰灰8', 2666, 0, '2019-07-02 08:42:41', '2019-07-02 08:42:41');

II. Delete使用教程

下面谈及到的删除，都是物理删除，可以理解为直接将某些记录从表中抹除掉（并不是说删了就完全没有办法恢复）针对CURD四种操作而言，除了read之外，另外三个insert,update,delete都会加写锁（一般来将会涉及到行锁和gap锁，从后面也会看到，这三个操作要求显示声明事物）

1. 表关联POJO

前面插入篇已经介绍了POJO的逐步创建过程，已经对应的注解含义，下面直接贴出成果

@Data
@DynamicUpdate
@DynamicInsert
@Entity
@Table(name = "money")
public class MoneyPO {
    @Id
    // 如果是auto，则会报异常 Table 'mysql.hibernate_sequence' doesn't exist
    // @GeneratedValue(strategy = GenerationType.AUTO)
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Integer id;

    @Column(name = "name")
    private String name;

    @Column(name = "money")
    private Long money;

    @Column(name = "is_deleted")
    private Byte isDeleted;

    @Column(name = "create_at")
    @CreatedDate
    private Timestamp createAt;

    @Column(name = "update_at")
    @CreatedDate
    private Timestamp updateAt;

}

上面类中的几个注解，说明如下

@Data 属于lombok注解，与jpa无关，自动生成getter/setter/equals/hashcode/tostring等方法
@Entity, @Table jpa注解，表示这个类与db的表关联，具体匹配的是表 money
@Id @GeneratedValue 作用与自增主键
@Column表明这个属性与表中的某列对应
@CreateDate根据当前时间来生成默认的时间戳

2. Repository API声明

接下来我们新建一个api继承自CurdRepository，然后通过这个api来与数据库打交道

public interface MoneyDeleteRepository extends CrudRepository<MoneyPO, Integer> {
    /**
     * 查询测试
     * @param id
     * @return
     */
    List<MoneyPO> queryByIdGreaterThanEqual(int id);
}

3. 使用姿势

先写一个用于查询数据的方法，用于校验我们执行删除之后，是否确实被删除了

private void showLeft() {
    List<MoneyPO> records = moneyDeleteRepository.queryByIdGreaterThanEqual(20);
    System.out.println(records);
}

在执行下面操作之前，先调用上面的，输出结果如

[MoneyPO(id=20, name=jpa 一灰灰5, money=2323, isDeleted=0, createAt=2019-07-02 08:42:41.0, updateAt=2019-07-02 08:42:41.0), MoneyPO(id=21, name=jpa 一灰灰6, money=2333, isDeleted=0, createAt=2019-07-02 08:42:41.0, updateAt=2019-07-02 08:42:41.0), MoneyPO(id=22, name=jpa 一灰灰7, money=6666, isDeleted=0, createAt=2019-07-02 08:42:41.0, updateAt=2019-07-02 08:42:41.0), MoneyPO(id=23, name=jpa 一灰灰8, money=2666, isDeleted=0, createAt=2019-07-02 08:42:41.0, updateAt=2019-07-02 08:42:41.0)]

a. 根据主键id进行删除

这种应该属于最常见的删除方式了，为了避免误删，通过精确的主键id来删除记录，是一个非常好的使用姿势，CrudRepository这个接口已经提供了对应的方法，所以我们可以直接使用

private void deleteById() {
    // 直接根据id进行删除
    moneyDeleteRepository.deleteById(21);
    showLeft();
}

执行完毕之后，输出结果如下，对比前面的输出可以知道 id=21 的记录被删除了

[MoneyPO(id=20, name=jpa 一灰灰5, money=2323, isDeleted=0, createAt=2019-07-02 08:42:41.0, updateAt=2019-07-02 08:42:41.0), MoneyPO(id=22, name=jpa 一灰灰7, money=6666, isDeleted=0, createAt=2019-07-02 08:42:41.0, updateAt=2019-07-02 08:42:41.0), MoneyPO(id=23, name=jpa 一灰灰8, money=2666, isDeleted=0, createAt=2019-07-02 08:42:41.0, updateAt=2019-07-02 08:42:41.0)]

然后一个疑问自然而然的来了，如果这个id对应的记录不存在，会怎样?

把上面代码再执行一次，发现抛了异常

为什么会这样呢？我们debug进去，调用的实现是默认的 SimpleJpaRepository，其源码如

// 类为: org.springframework.data.jpa.repository.support.SimpleJpaRepository
@Transactional
public void deleteById(ID id) {

	Assert.notNull(id, ID_MUST_NOT_BE_NULL);

	delete(findById(id).orElseThrow(() -> new EmptyResultDataAccessException(
			String.format("No %s entity with id %s exists!", entityInformation.getJavaType(), id), 1)));
}

@Transactional
public void delete(T entity) {

	Assert.notNull(entity, "The entity must not be null!");
	em.remove(em.contains(entity) ? entity : em.merge(entity));
}

从源码可以看出，这个是先通过id进行查询，如果对应的记录不存在时，直接抛异常；当存在时，走remove逻辑；

如果我们希望删除一个不存在的数据时，不要报错，可以怎么办？

自定义实现一个继承SimpleJpaRepository的类，覆盖删除方法

@Repository
@Transactional(readOnly = true)
public class MoneyDeleteRepositoryV2 extends SimpleJpaRepository<MoneyPO, Integer> {

    @Autowired
    public MoneyDeleteRepositoryV2(EntityManager em) {
        this(JpaEntityInformationSupport.getEntityInformation(MoneyPO.class, em), em);
    }

    public MoneyDeleteRepositoryV2(JpaEntityInformation<MoneyPO, ?> entityInformation, EntityManager entityManager) {
        super(entityInformation, entityManager);
    }

    public MoneyDeleteRepositoryV2(Class<MoneyPO> domainClass, EntityManager em) {
        super(domainClass, em);
    }

    @Override
    public void deleteById(Integer id) {
        Optional<MoneyPO> rec = findById(id);
        rec.ifPresent(super::delete);
    }
}

然后再调用上面的方法就可以了，不演示具体的测试case了，源码可以到项目工程中查看 👉 源码

b. 条件判断删除

虽然根据id进行删除比较稳妥，但也无法避免某些情况下需要根据其他的字段来删除，比如我们希望删除名为 jpa 一灰灰7的数据，这时则需要我们在MoneyDeleteRepository新增一个方法

/**
 * 根据name进行删除
 *
 * @param name
 */
void deleteByName(String name);

这里比较简单的提一下这个方法的命名规则，后面在查询这一篇会更加详细的说明；

delete 表示执行的是删除操作
By 表示根据某个字段来进行条件限定
Name 这个有POJO中的属性匹配

上面这个方法，如果翻译成sql，相当于 delete from money where name=xx

调用方式和前面一样，如下

private void deleteByName() {
    moneyDeleteRepository.deleteByName("jpa 一灰灰7");
    showLeft();
}

然后我们执行上面的测试，发现并不能成功，报错了

通过前面update的学习，知道需要显示加一个事物的注解，我们这里直接加在Repository中

/**
 * 根据name进行删除
 *
 * @param name
 */
@Transactional
void deleteByName(String name);

然后再次执行输出如下，这里我们把sql的日志也打印了

Hibernate: select moneypo0_.id as id1_0_, moneypo0_.create_at as create_a2_0_, moneypo0_.is_deleted as is_delet3_0_, moneypo0_.money as money4_0_, moneypo0_.name as name5_0_, moneypo0_.update_at as update_a6_0_ from money moneypo0_ where moneypo0_.name=?
Hibernate: delete from money where id=?
Hibernate: select moneypo0_.id as id1_0_, moneypo0_.create_at as create_a2_0_, moneypo0_.is_deleted as is_delet3_0_, moneypo0_.money as money4_0_, moneypo0_.name as name5_0_, moneypo0_.update_at as update_a6_0_ from money moneypo0_ where moneypo0_.id>=?
[MoneyPO(id=20, name=jpa 一灰灰5, money=2323, isDeleted=0, createAt=2019-07-02 08:42:41.0, updateAt=2019-07-02 08:42:41.0), MoneyPO(id=23, name=jpa 一灰灰8, money=2666, isDeleted=0, createAt=2019-07-02 08:42:41.0, updateAt=2019-07-02 08:42:41.0)]

从最终剩余的记录来看，name为jpa 一灰灰7的被删除了，再看一下前面删除的sql，会发现一个有意思的地方，deleteByName 这个方法，翻译成sql变成了两条

select * from money where name=xxx 先根据name查询记录
delete from money where id = xxx 根据前面查询记录的id，删除记录

c. 比较删除

接下来演示一个删除money在[2000,3000]区间的记录，这时我们新增的放入可以是

/**
 * 根据数字比较进行删除
 *
 * @param low
 * @param big
 */
@Transactional
void deleteByMoneyBetween(Long low, Long big);

通过方法命名也可以简单知道上面这个等同于sql delete from money where money between xxx and xxx

测试代码为

private void deleteByCompare() {
    moneyDeleteRepository.deleteByMoneyBetween(2000L, 3000L);
    showLeft();
}

输出日志

Hibernate: select moneypo0_.id as id1_0_, moneypo0_.create_at as create_a2_0_, moneypo0_.is_deleted as is_delet3_0_, moneypo0_.money as money4_0_, moneypo0_.name as name5_0_, moneypo0_.update_at as update_a6_0_ from money moneypo0_ where moneypo0_.money between ? and ?
Hibernate: delete from money where id=?
Hibernate: delete from money where id=?
Hibernate: select moneypo0_.id as id1_0_, moneypo0_.create_at as create_a2_0_, moneypo0_.is_deleted as is_delet3_0_, moneypo0_.money as money4_0_, moneypo0_.name as name5_0_, moneypo0_.update_at as update_a6_0_ from money moneypo0_ where moneypo0_.id>=?
[]

从拼接的sql可以看出，上面的逻辑等同于，先执行了查询，然后根据id一个一个进行删除….

4. 小结

我们通过声明方法的方式来实现条件删除；需要注意

删除需要显示声明事物 @Transactional
删除一个不存在的记录，会抛异常
声明删除方法时，实际等同于先查询记录，然后根据记录的id进行精准删除

II. 其他

源码

相关博文

2.5 - 5.JPA之query使用姿势详解之基础篇

前面的几篇文章分别介绍了CURD中的增删改，接下来进入最最常见的查询篇，看一下使用jpa进行db的记录查询时，可以怎么玩

本篇将介绍一些基础的查询使用姿势，主要包括根据字段查询，and/or/in/like/between 语句，数字比较，排序以及分页

I. 环境准备

在开始之前，当然得先准备好基础环境，如安装测试使用mysql，创建SpringBoot项目工程，设置好配置信息等，关于搭建项目的详情可以参考前一篇文章

190612-SpringBoot系列教程JPA之基础环境搭建

下面简单的看一下演示添加记录的过程中，需要的配置

1. 表准备

沿用前一篇的表，结构如下

CREATE TABLE `money` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '用户名',
  `money` int(26) NOT NULL DEFAULT '0' COMMENT '钱',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0',
  `create_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

2. 项目配置

配置信息，与之前有一点点区别，我们新增了更详细的日志打印；本篇主要目标集中在添加记录的使用姿势，对于配置说明，后面单独进行说明

## DataSource
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/story?useUnicode=true&characterEncoding=UTF-8&useSSL=false
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=
## jpa相关配置
spring.jpa.database=MYSQL
spring.jpa.hibernate.ddl-auto=none
spring.jpa.show-sql=true
spring.jackson.serialization.indent_output=true
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

3. 数据准备

数据修改嘛，所以我们先向表里面插入两条数据，用于后面的操作

INSERT INTO `money` (`id`, `name`, `money`, `is_deleted`, `create_at`, `update_at`)
VALUES
	(1, '一灰灰blog', 100, 0, '2019-04-18 17:01:40', '2019-04-18 17:01:40'),
	(2, '一灰灰2', 200, 0, '2019-04-18 17:01:40', '2019-04-18 17:01:40'),
	(3, '一灰灰3', 300, 0, '2019-04-18 17:01:40', '2019-04-18 17:01:40'),
	(4, '一灰灰4', 400, 0, '2019-04-18 17:01:40', '2019-04-18 17:01:40'),
	(5, '一灰灰5', 500, 0, '2019-04-18 17:01:40', '2019-04-18 17:01:40'),
	(6, 'Batch 一灰灰blog', 100, 0, '2019-04-18 17:01:40', '2019-04-18 17:01:40'),
	(7, 'Batch 一灰灰blog 2', 100, 0, '2019-04-18 17:01:40', '2019-04-18 17:01:40'),
	(8, 'Batch 一灰灰 3', 200, 0, '2019-04-18 17:01:40', '2019-04-18 17:01:40'),
	(9, 'Batch 一灰灰 4', 200, 0, '2019-04-18 17:01:40', '2019-04-18 17:01:40'),
	(10, 'batch 一灰灰5', 1498, 0, '2019-04-18 17:01:40', '2019-04-18 17:01:58'),
	(11, 'batch 一灰灰6', 1498, 0, '2019-04-18 17:01:40', '2019-04-18 17:01:58'),
	(12, 'batch 一灰灰7', 400, 0, '2019-04-18 17:01:40', '2019-04-18 17:01:40'),
	(13, 'batch 一灰灰8', 400, 0, '2019-04-18 17:01:40', '2019-04-18 17:01:40');

II. Query基本使用姿势

下面进入简单的查询操作姿势介绍，单表的简单and/or/in/compare查询方式

1. 表关联POJO

查询返回的记录与一个实体类POJO进行绑定，借助前面的分析结果，如下

@Data
@DynamicUpdate
@DynamicInsert
@Entity
@Table(name = "money")
public class MoneyPO {
    @Id
    // 如果是auto，则会报异常 Table 'mysql.hibernate_sequence' doesn't exist
    // @GeneratedValue(strategy = GenerationType.AUTO)
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Integer id;

    @Column(name = "name")
    private String name;

    @Column(name = "money")
    private Long money;

    @Column(name = "is_deleted")
    private Byte isDeleted;

    @Column(name = "create_at")
    @CreatedDate
    private Timestamp createAt;

    @Column(name = "update_at")
    @CreatedDate
    private Timestamp updateAt;

}

上面类中的几个注解，说明如下

@Data 属于lombok注解，与jpa无关，自动生成getter/setter/equals/hashcode/tostring等方法
@Entity, @Table jpa注解，表示这个类与db的表关联，具体匹配的是表 money
@Id @GeneratedValue 作用与自增主键
@Column表明这个属性与表中的某列对应
@CreateDate根据当前时间来生成默认的时间戳

2. Repository API声明

接下来我们新建一个api继承自CurdRepository，然后通过这个api来与数据库打交道，后面会在这个类中添加较多的查询方法

public interface MoneyBaseQueryRepository extends CrudRepository<MoneyPO, Integer> {
}

3. 使用姿势

a. 根据id查询

CrudRepository已经提供的功能，根据主键id进行查询，对于使用者而言，没有什么需要额外操作的，直接访问即可

private void queryById() {
    // 根据主键查询，直接使用接口即可
    Optional<MoneyPO> res = moneyCurdRepository.findById(1);
    System.out.println("queryById return: " + res.get());
}

b. 根据字段查询

除了根据主键查询，实际的业务场景中，根据某个字段进行查询的case，简直不要更多，在jpa中可以怎么做呢?

在Repository接口中声明一个方法，命名规则为
findByXXX 或者 queryByXXX (注意这里的xxx用POJO中的成员名替换,表示根据这个成员进行查询）

一个简单的case，如果我希望实现根据name进行查询，那么在MoneyBaseQueryRepository中添加下面两个方法中的任意一个都可以

/**
 * 根据用户名查询
 *
 * @param name
 * @return
 */
List<MoneyPO> findByName(String name);

List<MoneyPO> queryByName(String name);

如果需要多个成员的查询呢？也简单，形如findByXxxAndYyyy相当于sql中的where xxxx=? and yyy=?

如我们也可以增加下面两个方法(一个and、一个or查询）

/**
 * 根据用户名 + money查询
 *
 * @param name
 * @param money
 * @return
 */
List<MoneyPO> findByNameAndMoney(String name, Long money);


/**
 * 根据用户名 or id查询
 *
 * @param name
 * @param id
 * @return
 */
List<MoneyPO> findByNameOrId(String name, Integer id);

一个简单的测试case可以如下

private void queryByField() {
    // 根据内部成员进行查询，需要自己定义新的接口
    String name = "一灰灰blog";
    Iterable<MoneyPO> res = moneyCurdRepository.findByName(name);
    System.out.println("findByName return: " + res);

    res = moneyCurdRepository.queryByName(name);
    System.out.println("queryByName return: " + res);

    Long money = 100L;
    res = moneyCurdRepository.findByNameAndMoney(name, money);
    System.out.println("findByNameAndMoney return: " + res);

    Integer id = 5;
    res = moneyCurdRepository.findByNameOrId(name, id);
    System.out.println("findByNameOrId return: " + res);
}

执行之后输出结果如下，下面也包括了对应的sql，便于理解

Hibernate: select moneypo0_.id as id1_0_, moneypo0_.create_at as create_a2_0_, moneypo0_.is_deleted as is_delet3_0_, moneypo0_.money as money4_0_, moneypo0_.name as name5_0_, moneypo0_.update_at as update_a6_0_ from money moneypo0_ where moneypo0_.name=?
findByName return: [MoneyPO(id=1, name=一灰灰blog, money=100, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:40.0)]
-------- 人工拆分 -----------
Hibernate: select moneypo0_.id as id1_0_, moneypo0_.create_at as create_a2_0_, moneypo0_.is_deleted as is_delet3_0_, moneypo0_.money as money4_0_, moneypo0_.name as name5_0_, moneypo0_.update_at as update_a6_0_ from money moneypo0_ where moneypo0_.name=?
queryByName return: [MoneyPO(id=1, name=一灰灰blog, money=100, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:40.0)]
-------- 人工拆分 -----------
Hibernate: select moneypo0_.id as id1_0_, moneypo0_.create_at as create_a2_0_, moneypo0_.is_deleted as is_delet3_0_, moneypo0_.money as money4_0_, moneypo0_.name as name5_0_, moneypo0_.update_at as update_a6_0_ from money moneypo0_ where moneypo0_.name=? and moneypo0_.money=?
findByNameAndMoney return: [MoneyPO(id=1, name=一灰灰blog, money=100, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:40.0)]
-------- 人工拆分 -----------
Hibernate: select moneypo0_.id as id1_0_, moneypo0_.create_at as create_a2_0_, moneypo0_.is_deleted as is_delet3_0_, moneypo0_.money as money4_0_, moneypo0_.name as name5_0_, moneypo0_.update_at as update_a6_0_ from money moneypo0_ where moneypo0_.name=? or moneypo0_.id=?
findByNameOrId return: [MoneyPO(id=1, name=一灰灰blog, money=100, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:40.0), MoneyPO(id=5, name=一灰灰5, money=500, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:40.0)]

c. like查询

上面的查询方式为等值查询，当在sql中除了等值查询（即=查询）之外，还有各种比较查询，不等查询以及like语句，在jpa中也比较简单，在repository定义的方法名，加一个like即可

/**
 * like查询
 *
 * @param name
 * @return
 */
List<MoneyPO> findByNameLike(String name);

使用的时候，需要稍微注意一下，根据实际情况决定要不要加上 ‘%’

private void queryByLike() {
    // like 语句查询
    String name = "一灰灰%";
    Iterable<MoneyPO> res = moneyCurdRepository.findByNameLike(name);
    System.out.println("findByName like: " + res);
}

输出结果为

Hibernate: select moneypo0_.id as id1_0_, moneypo0_.create_at as create_a2_0_, moneypo0_.is_deleted as is_delet3_0_, moneypo0_.money as money4_0_, moneypo0_.name as name5_0_, moneypo0_.update_at as update_a6_0_ from money moneypo0_ where moneypo0_.name like ?
findByName like: [MoneyPO(id=1, name=一灰灰blog, money=100, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:40.0), MoneyPO(id=2, name=一灰灰2, money=200, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:40.0), MoneyPO(id=3, name=一灰灰3, money=300, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:40.0), MoneyPO(id=4, name=一灰灰4, money=400, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:40.0), MoneyPO(id=5, name=一灰灰5, money=500, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:40.0)]

d. in查询

对于in查询，CurdRepository提供了根据主键id的查询方式，直接调用findAllById即可，如果是其他的，可以通过声明一个接口的方式来支持

/**
 * in查询
 *
 * @param moneys
 * @return
 */
List<MoneyPO> findByMoneyIn(List<Long> moneys);

测试case如下

// in 查询
List<Integer> ids = Arrays.asList(1, 2, 3);
Iterable<MoneyPO> res = moneyCurdRepository.findAllById(ids);
System.out.println("findByIds return: " + res);

res = moneyCurdRepository.findByMoneyIn(Arrays.asList(400L, 300L));
System.out.println("findByMoneyIn return: " + res);

输出结果

Hibernate: select moneypo0_.id as id1_0_, moneypo0_.create_at as create_a2_0_, moneypo0_.is_deleted as is_delet3_0_, moneypo0_.money as money4_0_, moneypo0_.name as name5_0_, moneypo0_.update_at as update_a6_0_ from money moneypo0_ where moneypo0_.id in (? , ? , ?)
findByIds return: [MoneyPO(id=1, name=一灰灰blog, money=100, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:40.0), MoneyPO(id=2, name=一灰灰2, money=200, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:40.0), MoneyPO(id=3, name=一灰灰3, money=300, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:40.0)]
------ 手动拆分 ----------
Hibernate: select moneypo0_.id as id1_0_, moneypo0_.create_at as create_a2_0_, moneypo0_.is_deleted as is_delet3_0_, moneypo0_.money as money4_0_, moneypo0_.name as name5_0_, moneypo0_.update_at as update_a6_0_ from money moneypo0_ where moneypo0_.money in (? , ?)
findByMoneyIn return: [MoneyPO(id=3, name=一灰灰3, money=300, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:40.0), MoneyPO(id=4, name=一灰灰4, money=400, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:40.0), MoneyPO(id=12, name=batch 一灰灰7, money=400, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:40.0), MoneyPO(id=13, name=batch 一灰灰8, money=400, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:40.0)]

e. 比较查询

数字的比较查询，比如大于等于，大于，小于，小于等于，between，下面的三个方法声明，应该能直观表示这种方式可以如何写

/**
 * 查询大于or等于指定id的所有记录
 *
 * @param id
 * @return
 */
List<MoneyPO> findByIdGreaterThanEqual(Integer id);

/**
 * 查询小于or等于指定id的所有记录
 *
 * @param id
 * @return
 */
List<MoneyPO> findByIdLessThanEqual(Integer id);

/**
 * between查询
 *
 * @param low
 * @param high
 * @return
 */
List<MoneyPO> findByIdIsBetween(Integer low, Integer high);

下面是简单的映射关系

> : xxGreaterThan
>=: xxGreaterThanEqual
<: xxLessThan
<=: xxLessThanEqual
!=: xxNot
between a and b : xxIsBetween

测试case如下

private void queryByCompare() {
    Integer id1 = 3;
    Iterable<MoneyPO> res = moneyCurdRepository.findByIdLessThanEqual(id1);
    System.out.println("findByIdLessThan 3 return: " + res);


    Integer id2 = 10;
    res = moneyCurdRepository.findByIdGreaterThanEqual(id2);
    System.out.println("findByIdGreaterThan 10 return: " + res);

    id1 = 4;
    id2 = 6;
    res = moneyCurdRepository.findByIdIsBetween(id1, id2);
    System.out.println("findByIdsWBetween 3, 10 return: " + res);
}

输出结果为

Hibernate: select moneypo0_.id as id1_0_, moneypo0_.create_at as create_a2_0_, moneypo0_.is_deleted as is_delet3_0_, moneypo0_.money as money4_0_, moneypo0_.name as name5_0_, moneypo0_.update_at as update_a6_0_ from money moneypo0_ where moneypo0_.id<=?
findByIdLessThan 3 return: [MoneyPO(id=1, name=一灰灰blog, money=100, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:40.0), MoneyPO(id=2, name=一灰灰2, money=200, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:40.0), MoneyPO(id=3, name=一灰灰3, money=300, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:40.0)]
------ 手动拆分 ----------
Hibernate: select moneypo0_.id as id1_0_, moneypo0_.create_at as create_a2_0_, moneypo0_.is_deleted as is_delet3_0_, moneypo0_.money as money4_0_, moneypo0_.name as name5_0_, moneypo0_.update_at as update_a6_0_ from money moneypo0_ where moneypo0_.id>=?
findByIdGreaterThan 10 return: [MoneyPO(id=10, name=batch 一灰灰5, money=1498, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:58.0), MoneyPO(id=11, name=batch 一灰灰6, money=1498, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:58.0), MoneyPO(id=12, name=batch 一灰灰7, money=400, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:40.0), MoneyPO(id=13, name=batch 一灰灰8, money=400, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:40.0)]
------ 手动拆分 ----------
Hibernate: select moneypo0_.id as id1_0_, moneypo0_.create_at as create_a2_0_, moneypo0_.is_deleted as is_delet3_0_, moneypo0_.money as money4_0_, moneypo0_.name as name5_0_, moneypo0_.update_at as update_a6_0_ from money moneypo0_ where moneypo0_.id between ? and ?
findByIdsWBetween 3, 10 return: [MoneyPO(id=4, name=一灰灰4, money=400, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:40.0), MoneyPO(id=5, name=一灰灰5, money=500, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:40.0), MoneyPO(id=6, name=Batch 一灰灰blog, money=100, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:40.0)]

f. 排序

排序也属于基本查询的case了，jpa的实现中，通过加上OrderByXxxAsc/Desc的方式来决定根据什么进行升序or降序

/**
 * 根据money查询，并将最终的结果根据id进行倒排
 *
 * @param money
 * @return
 */
List<MoneyPO> findByMoneyOrderByIdDesc(Long money);

/**
 * 根据多个条件进行排序
 *
 * @param id
 * @return
 */
List<MoneyPO> queryByIdGreaterThanEqualOrderByMoneyDescIdAsc(Integer id);

在根据多个列进行排序时，需要注意的是不能写多个 OrderBy 而是直接在OrderBy后面加上对应的xxxAscyyyDesc

测试代码如

private void queryWithSort() {
    // 排序
    Long money = 400L;
    Iterable<MoneyPO> res = moneyCurdRepository.findByMoneyOrderByIdDesc(money);
    System.out.println("findByMoneyAndOrderByIdDesc return: " + res);
  
    Integer startId = 7;
    res = moneyCurdRepository.queryByIdGreaterThanEqualOrderByMoneyDescIdAsc(startId);
    System.out.println("queryByIdGreaterThanEqualOrderByMoneyDescIdAsc return: " + res);
}

输出结果如下

Hibernate: select moneypo0_.id as id1_0_, moneypo0_.create_at as create_a2_0_, moneypo0_.is_deleted as is_delet3_0_, moneypo0_.money as money4_0_, moneypo0_.name as name5_0_, moneypo0_.update_at as update_a6_0_ from money moneypo0_ where moneypo0_.money=? order by moneypo0_.id desc
findByMoneyAndOrderByIdDesc return: [MoneyPO(id=13, name=batch 一灰灰8, money=400, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:40.0), MoneyPO(id=12, name=batch 一灰灰7, money=400, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:40.0), MoneyPO(id=4, name=一灰灰4, money=400, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:40.0)]
------------- 人工拆分 --------
Hibernate: select moneypo0_.id as id1_0_, moneypo0_.create_at as create_a2_0_, moneypo0_.is_deleted as is_delet3_0_, moneypo0_.money as money4_0_, moneypo0_.name as name5_0_, moneypo0_.update_at as update_a6_0_ from money moneypo0_ where moneypo0_.id>=? order by moneypo0_.money desc, moneypo0_.id asc
queryByIdGreaterThanEqualOrderByMoneyDescIdAsc return: [MoneyPO(id=10, name=batch 一灰灰5, money=1498, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:58.0), MoneyPO(id=11, name=batch 一灰灰6, money=1498, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:58.0), MoneyPO(id=12, name=batch 一灰灰7, money=400, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:40.0), MoneyPO(id=13, name=batch 一灰灰8, money=400, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:40.0), MoneyPO(id=8, name=Batch 一灰灰 3, money=200, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:40.0), MoneyPO(id=9, name=Batch 一灰灰 4, money=200, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:40.0), MoneyPO(id=7, name=Batch 一灰灰blog 2, money=100, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:40.0)]

g. 分页查询

分页有两种方式，一个是查询最大的多少条数据，一个是正常的limit/offset方式，下面是一个简单的实例demo

/**
* 分页查询，获取前面三个数据
*
* @param id
* @return
*/
List<MoneyPO> findTop3ByIdGreaterThan(Integer id);

/**
* 分页查询
*
* @param id
* @param pageable page 从0开始表示查询第0页，即返回size个正好>id数量的数据
* @return
*/
List<MoneyPO> findByIdGreaterThan(Integer id, Pageable pageable);

对于分页而言，通过传入参数Pageable来表明即可

测试case如

private void queryWithPageSize() {
    // 分页查询
    Iterable<MoneyPO> res = moneyCurdRepository.findTop3ByIdGreaterThan(3);
    System.out.println("findTop3ByIdGreaterThan 3 return: " + res);

    // id>3，第2页，每页3条，如果id递增时，则返回的第一条id=4 + 2 * 3 = 10
    res = moneyCurdRepository.findByIdGreaterThan(3, PageRequest.of(2, 3));
    System.out.println("findByIdGreaterThan 3 pageIndex 2 size 3 return: " + res);
}

输出结果为

Hibernate: select moneypo0_.id as id1_0_, moneypo0_.create_at as create_a2_0_, moneypo0_.is_deleted as is_delet3_0_, moneypo0_.money as money4_0_, moneypo0_.name as name5_0_, moneypo0_.update_at as update_a6_0_ from money moneypo0_ where moneypo0_.id>? limit ?
findTop3ByIdGreaterThan 3 return: [MoneyPO(id=4, name=一灰灰4, money=400, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:40.0), MoneyPO(id=5, name=一灰灰5, money=500, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:40.0), MoneyPO(id=6, name=Batch 一灰灰blog, money=100, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:40.0)]
---------- 人工拆分 ------------
Hibernate: select moneypo0_.id as id1_0_, moneypo0_.create_at as create_a2_0_, moneypo0_.is_deleted as is_delet3_0_, moneypo0_.money as money4_0_, moneypo0_.name as name5_0_, moneypo0_.update_at as update_a6_0_ from money moneypo0_ where moneypo0_.id>? limit ?, ?
findByIdGreaterThan 3 pageIndex 2 size 3 return: [MoneyPO(id=10, name=batch 一灰灰5, money=1498, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:58.0), MoneyPO(id=11, name=batch 一灰灰6, money=1498, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:58.0), MoneyPO(id=12, name=batch 一灰灰7, money=400, isDeleted=0, createAt=2019-04-18 17:01:40.0, updateAt=2019-04-18 17:01:40.0)]

4. 小结

主要介绍了jpa的查询的最基本使用方式,主要是根据规则定义方法名的方式来实现sql的效果, 下表示一个简单的对比小结

方法名	说明	等效sql
`findByXxx`	表示根据列`Xxx`等于传参构建sql	`where xxx= ?`
`findByXxxAndYyy`	根据多个列进行查询	`where xxx=? and yyy=?`
`findByXxxOrYyy`	根据多个列实现or查询	`where xxx=? or yyy=?`
`findByXxxLike`	like查询，需要注意查询条件中加%	`where xxx like`
`findByXxxIn`	in查询	`where Xxx in ()`
`findByXxxGreaterThan`	大于	`where xxx > ?`
`findByXxxGreaterThanEqual`	大于等于	`where xxx >= ?`
`findByXxxLessThan`	小于	`where xxx < ?`
`findByXxxLessThanEqual`	小于等于	`where xxx <= ?`
`findByXxxNot`	不等于	`where xxx != ?`
`findByXxxIsBetween`	between查询	`where xxx between ? and ?`
`OrderByXxxDesc`	排序	`order by xxx desc`
`topN`	分页，表示获取最前面的n条	`limit n`

此外还有一个分页的方式是传参Pageable，来指定具体的分页

我们常见的查询操作中，除了上面的一些case之外，还有一些是我们没有提到的，如下面的一些使用姿势，则会在后面的文章中引入

group by
distinct
join
各种函数的支持(sum,max,min,avg…)
查询部分表中部分字段时
统计查询

II. 其他

0. 源码&相关博文

源码

相关博文

2.6 - 6.JPA之指定id保存

前几天有位小伙伴问了一个很有意思的问题，使用JPA保存数据时，即便我指定了主键id，但是新插入的数据主键却是mysql自增的id；那么是什么原因导致的呢？又可以如何解决呢?

本文将介绍一下如何使用JPA的AUTO保存策略来指定数据库主键id

I. 环境准备

下面简单的看一下后续的代码中，需要的配置（我们使用的是mysql数据库）

1. 表准备

沿用前一篇的表，结构如下

CREATE TABLE `money` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '用户名',
  `money` int(26) NOT NULL DEFAULT '0' COMMENT '钱',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0',
  `create_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

2. 项目配置

配置信息，与之前有一点点区别，我们新增了更详细的日志打印；本篇主要目标集中在添加记录的使用姿势，对于配置说明，后面单独进行说明

## DataSource
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/story?useUnicode=true&characterEncoding=UTF-8&useSSL=false
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=
## jpa相关配置
spring.jpa.database=MYSQL
spring.jpa.hibernate.ddl-auto=none
spring.jpa.show-sql=true
spring.jackson.serialization.indent_output=true
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

II. Insert教程

首先简单的看一下，我们一般使用默认的数据库自增生成主键的使用方式，以便后面的自定义主键生成策略的对比

对于jpa的插入数据的知识点不太清楚的同学，可以看一下之前的博文： 190614-SpringBoot系列教程JPA之新增记录使用姿势

1. 自增主键

首先我们需要定义PO，与数据库中的表绑定起来

@Data
@DynamicUpdate
@DynamicInsert
@Entity
@Table(name = "money")
public class MoneyPO {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Integer id;

    @Column(name = "name")
    private String name;

    @Column(name = "money")
    private Long money;

    @Column(name = "is_deleted")
    private Byte isDeleted;

    @Column(name = "create_at")
    @CreatedDate
    private Timestamp createAt;

    @Column(name = "update_at")
    @CreatedDate
    private Timestamp updateAt;
}

注意上面的主键生成策略用的是 GenerationType.IDENTITY，配合mysql的使用就是利用数据库的自增来生成主键id

/**
 * 新增数据
 * Created by @author yihui in 11:00 19/6/12.
 */
public interface MoneyCreateRepositoryV2 extends JpaRepository<MoneyPO, Integer> {
}

接下来保存数据就很简单了

private void addWithId() {
    MoneyPO po1 = new MoneyPO();
    po1.setId(20);
    po1.setName("jpa 一灰灰 1x");
    po1.setMoney(2200L + ((long) (Math.random() * 100)));
    po1.setIsDeleted((byte) 0x00);
    MoneyPO r1 = moneyCreateRepositoryV2.save(po1);
    System.out.println("after insert res: " + r1);
}

强烈建议实际的体验一下上面的代码执行

首次执行确保数据库中不存在id为20的记录，虽然我们的PO对象中，指定了id为20，但是执行完毕之后，新增的数据id却不是20

Hibernate: select moneypo0_.id as id1_0_0_, moneypo0_.create_at as create_a2_0_0_, moneypo0_.is_deleted as is_delet3_0_0_, moneypo0_.money as money4_0_0_, moneypo0_.name as name5_0_0_, moneypo0_.update_at as update_a6_0_0_ from money moneypo0_ where moneypo0_.id=?
Hibernate: insert into money (is_deleted, money, name) values (?, ?, ?)
after insert res: MoneyPO(id=104, name=jpa 一灰灰 1x, money=2208, isDeleted=0, createAt=null, updateAt=null)

上面是执行的sql日志，注意插入的sql，是没有指定id的，所以新增的记录的id就会利用mysql的自增策略

当我们的db中存在id为20的记录时，再次执行，查看日志发现实际执行的是更新数据

Hibernate: select moneypo0_.id as id1_0_0_, moneypo0_.create_at as create_a2_0_0_, moneypo0_.is_deleted as is_delet3_0_0_, moneypo0_.money as money4_0_0_, moneypo0_.name as name5_0_0_, moneypo0_.update_at as update_a6_0_0_ from money moneypo0_ where moneypo0_.id=?
Hibernate: update money set create_at=?, money=?, name=?, update_at=? where id=?
after insert res: MoneyPO(id=20, name=jpa 一灰灰 1x, money=2234, isDeleted=0, createAt=null, updateAt=null)

大胆猜测，save的执行过程逻辑如

首先根据id到数据库中查询对应的数据
如果数据不存在，则新增（插入sql不指定id）
如果数据存在，则判断是否有变更，以确定是否需要更新

2. 指定id

那么问题来了，如果我希望当我的po中指定了数据库id时，db中没有这条记录时，就插入id为指定值的记录；如果存在记录，则更新

要实现上面这个功能，自定义主键id，那么我们就需要修改一下主键的生成策略了，官方提供了四种

取值	说明
`GenerationType.TABLE`	使用一个特定的数据库表格来保存主键
`GenerationType.SEQUENCE`	根据底层数据库的序列来生成主键，条件是数据库支持序列
`GenerationType.IDENTITY`	主键由数据库自动生成（主要是自动增长型）
`GenerationType.AUTO`	主键由程序控制

从上面四种生成策略说明中，很明显我们要使用的就是AUTO策略了，我们新增一个PO，并指定保存策略

@Data
@DynamicUpdate
@DynamicInsert
@Entity
@Table(name = "money")
public class AutoMoneyPO {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "myid")
    @GenericGenerator(name = "myid", strategy = "com.git.hui.boot.jpa.generator.ManulInsertGenerator")
    @Column(name = "id")
    private Integer id;

    @Column(name = "name")
    private String name;

    @Column(name = "money")
    private Long money;

    @Column(name = "is_deleted")
    private Byte isDeleted;

    @Column(name = "create_at")
    @CreatedDate
    private Timestamp createAt;

    @Column(name = "update_at")
    @CreatedDate
    private Timestamp updateAt;
}

采用自定义的生成策略，需要注意，@GenericGenerator(name = "myid", strategy = "com.git.hui.boot.jpa.generator.ManulInsertGenerator")这个需要有，否则执行会抛异常

这一行代码的意思是，主键id是由ManulInsertGenerator来生成

/**
 *  自定义的主键生成策略，如果填写了主键id，如果数据库中没有这条记录，则新增指定id的记录；否则更新记录
 *
 *  如果不填写主键id，则利用数据库本身的自增策略指定id
 *
 * Created by @author yihui in 20:51 19/11/13.
 */
public class ManulInsertGenerator extends IdentityGenerator {

    @Override
    public Serializable generate(SharedSessionContractImplementor s, Object obj) throws HibernateException {
        Serializable id = s.getEntityPersister(null, obj).getClassMetadata().getIdentifier(obj, s);

        if (id != null && Integer.valueOf(id.toString()) > 0) {
            return id;
        } else {
            return super.generate(s, obj);
        }
    }
}

具体的主键生成方式也比较简单了，首先是判断PO中有没有主键，如果有则直接使用PO中的主键值；如果没有，就利用IdentityGenerator策略来生成主键（而这个主键生成策略，正好是GenerationType.IDENTITY利用数据库自增生成主键的策略）

接下来我们再次测试插入

// 使用自定义的主键生成策略
AutoMoneyPO moneyPO = new AutoMoneyPO();
moneyPO.setId(20);
moneyPO.setName("jpa 一灰灰 ex");
moneyPO.setMoney(2200L + ((long) (Math.random() * 100)));
moneyPO.setIsDeleted((byte) 0x00);
AutoMoneyPO res = moneyCreateRepositoryWithId.save(moneyPO);
System.out.println("after insert res: " + res);

moneyPO.setMoney(3200L + ((long) (Math.random() * 100)));
res = moneyCreateRepositoryWithId.save(moneyPO);
System.out.println("after insert res: " + res);

moneyPO = new AutoMoneyPO();
moneyPO.setName("jpa 一灰灰 2ex");
moneyPO.setMoney(2200L + ((long) (Math.random() * 100)));
moneyPO.setIsDeleted((byte) 0x00);
res = moneyCreateRepositoryWithId.save(moneyPO);
System.out.println("after insert res: " + res);

上面的代码执行时，确保数据库中没有主键为20的数据，输出sql日志如下

# 第一次插入
Hibernate: select automoneyp0_.id as id1_0_0_, automoneyp0_.create_at as create_a2_0_0_, automoneyp0_.is_deleted as is_delet3_0_0_, automoneyp0_.money as money4_0_0_, automoneyp0_.name as name5_0_0_, automoneyp0_.update_at as update_a6_0_0_ from money automoneyp0_ where automoneyp0_.id=?
Hibernate: insert into money (is_deleted, money, name, id) values (?, ?, ?, ?)
after insert res: AutoMoneyPO(id=20, name=jpa 一灰灰 ex, money=2238, isDeleted=0, createAt=null, updateAt=null)

# 第二次指定id插入
Hibernate: select automoneyp0_.id as id1_0_0_, automoneyp0_.create_at as create_a2_0_0_, automoneyp0_.is_deleted as is_delet3_0_0_, automoneyp0_.money as money4_0_0_, automoneyp0_.name as name5_0_0_, automoneyp0_.update_at as update_a6_0_0_ from money automoneyp0_ where automoneyp0_.id=?
Hibernate: update money set create_at=?, money=?, update_at=? where id=?
after insert res: AutoMoneyPO(id=20, name=jpa 一灰灰 ex, money=3228, isDeleted=0, createAt=null, updateAt=null)

# 第三次无id插入
Hibernate: insert into money (is_deleted, money, name) values (?, ?, ?)
after insert res: AutoMoneyPO(id=107, name=jpa 一灰灰 2ex, money=2228, isDeleted=0, createAt=null, updateAt=null)

注意上面的日志输出

第一次插入时拼装的写入sql是包含id的，也就达到了我们指定id新增数据的要求
第二次插入时，因为id=20的记录存在，所以执行的是更新操作
第三次插入时，因为没有id，所以插入的sql中也没有指定id，使用mysql的自增来生成主键id

II. 其他

0. 项目&博文

2.7 - 7.JPA 错误姿势之环境配置问题

又回到jpa的教程上了，这一篇源于某个简单的项目需要读写db，本想着直接使用jpa会比较简单，然而悲催的是实际开发过程中，发现了不少的坑；本文为错误姿势第一篇，Repository接口无法注入问题

I. 配置问题

新开一个jpa项目结合springboot可以很方便的实现，但是在某些环境下，可能会遇到自定义的JpaRepository接口无法注入问题

1. 基本配置

在spring-boot环境中，需要在pom.xml文件中，指定下面两个依赖

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

接下来需要修改一下配置文件(application.properties)，指定数据库的配置信息

## DataSource
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/story?useUnicode=true&characterEncoding=UTF-8&useSSL=false
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=

spring.jpa.database=MYSQL
spring.jpa.hibernate.ddl-auto=none
spring.jpa.show-sql=true
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

2. 注入失败case复现

首先在mysql的story库中，新增一个表

CREATE TABLE `meta_group` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `group` varchar(32) NOT NULL DEFAULT '' COMMENT '分组',
  `profile` varchar(32) NOT NULL DEFAULT '' COMMENT 'profile 目前用在应用环境 取值 dev/test/pro',
  `desc` varchar(64) NOT NULL DEFAULT '' COMMENT '解释说明',
  `deleted` int(4) NOT NULL DEFAULT '0' COMMENT '0表示有效 1表示无效',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`),
  KEY `group_profile` (`group`,`profile`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COMMENT='业务配置分组表';

然后定义这个表对应的Entity

@Data
@Entity
@Table(name = "meta_group")
public class MetaGroupPO {
    @Id
    @Column(name = "`id`")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(name = "`group`")
    private String group;

    @Column(name = "`profile`")
    private String profile;

    @Column(name = "`desc`")
    private String desc;

    @Column(name = "`deleted`")
    private Integer deleted;

    @Column(name = "`create_time`")
    @CreatedDate
    private Timestamp createTime;

    @Column(name = "`update_time`")
    @CreatedDate
    private Timestamp updateTime;
}

对应的repository接口

public interface GroupJPARepository extends JpaRepository<MetaGroupPO, Integer> {

    List<MetaGroupPO> findByProfile(String profile);

    MetaGroupPO findByGroupAndProfileAndDeleted(String group, String profile, Integer deleted);

    @Modifying
    @Query("update MetaGroupJpaPO m set m.desc=?2 where m.id=?1")
    int updateDesc(int groupId, String desc);

    @Modifying
    @Query("update MetaGroupJpaPO m set m.deleted=1 where m.id=?1")
    int logicDeleted(int groupId);
}

一个简单的数据操作封装类GroupManager

@Component
public class GroupManager {
    @Autowired
    private GroupJPARepository groupJPARepository;

    public MetaGroupPO getOnlineGroup(String group, String profile) {
        return groupJPARepository.findByGroupAndProfileAndDeleted(group, profile, 0);
    }

    public Integer addGroup(String group, String profile, String desc) {
        MetaGroupPO jpa = new MetaGroupPO();
        jpa.setGroup(group);
        jpa.setDesc(desc);
        jpa.setProfile(profile);
        jpa.setDeleted(0);
        Timestamp timestamp = Timestamp.from(Instant.now());
        jpa.setCreateTime(timestamp);
        jpa.setUpdateTime(timestamp);
        MetaGroupPO res = groupJPARepository.save(jpa);
        return res.getId();
    }
}

接下来重点来了，当我们的启动类，不是在外面时，可能会出现问题；项目结构如下

我们看一下配置类，和错误的启动应用类

@Configuration
@ComponentScan("com.git.hui.boot.jpacase")
public class JpaCaseAutoConfiguration {
}

@SpringBootApplication
public class ErrorApplication {

    public static void main(String[] args) {
        SpringApplication.run(ErrorApplication.class);
    }

}

直接启动失败，异常如下图，提示找不到GroupJPARepository这个bean，而这个bean在正常启动方式中，会由spring帮我们生成一个代理类；而这里显然是没有生成了

3. case分析

上面的case可能有点极端了，一般来讲项目启动类，我们都会放在最外层；基本上不太会出现上面这种项目结构，那么分析这个case有毛用？

一个典型的case

我们将db操作的逻辑放在一个module（如dao.jar）中封装起来
然后有一个启动的module，通过maven引入上dao.jar
这是入口的默认扫描范围，可能就无法包含dao.jar，因此极有可能导致注入失败

4. 解决方案

那么该怎么解决这个问题呢？

在配置类中，添加两个注解EnableJpaRepositories与EntityScan，并制定对应的包路径

@Configuration
@EnableJpaRepositories("com.git.hui.boot.jpacase")
@EntityScan("com.git.hui.boot.jpacase.entity")
public class TrueJpaCaseAutoConfiguration {
}

然后再次测试

@SpringBootApplication
public class TrueApplication {

    public TrueApplication(GroupManager groupManager) {
        int groupId = groupManager.addGroup("true-group", "dev", "正确写入!!!");
        System.out.println("add groupId: " + groupId);
        MetaGroupPO po = groupManager.getOnlineGroup("true-group", "dev");
        System.out.println(po);
    }

    public static void main(String[] args) {
        SpringApplication.run(ErrorApplication.class);
    }
}

5. 小结

最后小结一下，当我们发现jpa方式的Repository无法注入时，一般是因为接口不再我们的扫描路径下，需要通过@EntityScan与@EnableJpaRepositories来额外指定

（因为篇幅问题，其他的问题拆分到其他的博文）

II. 其他

0. 项目

2.8 - 8.JPA错误姿势之Entity映射

本篇为JPA错误使用姿势第二篇，java的POJO类与数据库表结构的映射关系，除了驼峰命名映射为下划线之外，还会有什么别的坑么？

I. 映射问题

1. 项目基本配置

首先搭建基本的springboot + jpa项目，我们使用的springboot版本为2.2.1.RELEASE，mysql版本5+

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

项目配置文件application.properties

## DataSource
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/story?useUnicode=true&characterEncoding=UTF-8&useSSL=false
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=
spring.jpa.database=MYSQL
spring.jpa.hibernate.ddl-auto=none
spring.jpa.show-sql=true
spring.jackson.serialization.indent_output=true
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

表结构

CREATE TABLE `meta_group` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `group` varchar(32) NOT NULL DEFAULT '' COMMENT '分组',
  `profile` varchar(32) NOT NULL DEFAULT '' COMMENT 'profile 目前用在应用环境 取值 dev/test/pro',
  `desc` varchar(64) NOT NULL DEFAULT '' COMMENT '解释说明',
  `deleted` int(4) NOT NULL DEFAULT '0' COMMENT '0表示有效 1表示无效',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`),
  KEY `group_profile` (`group`,`profile`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COMMENT='业务配置分组表';

2. 错误case

java变量命名推荐的是驼峰命名方式，因此与数据库中字段的下划线方式需要关联映射，通过jpa的相关知识学习，我们知道可以使用@Column注解来处理，所以有下面这种写法

@Data
@Entity
@Table(name = "meta_group")
public class ErrorMetaGroupPo {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(name = "group")
    private String group;

    @Column(name = "profile")
    private String profile;

    @Column(name = "desc")
    private String desc;

    @Column(name = "deleted")
    private Integer deleted;

    @Column(name = "create_time")
    private Timestamp createTime;

    @Column(name = "update_time")
    private Timestamp updateTime;
}

从命名上就可以看出上面这种case是错误的，那么到底是什么问题呢？

先写一个对应的Repository来实测一下

public interface ErrorGroupJPARepository extends JpaRepository<ErrorMetaGroupPo, Integer> {
}

测试代码

@Component
public class GroupManager {
    @Autowired
    private ErrorGroupJPARepository errorGroupJPARepository;

    public void test() {
        String group = UUID.randomUUID().toString().substring(0, 4);
        String profile = "dev";
        String desc = "测试jpa异常case!";
        try {
            int id = addGroup1(group, profile, desc);
            System.out.println("add1: " + id);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public Integer addGroup1(String group, String profile, String desc) {
        ErrorMetaGroupPo jpa = new ErrorMetaGroupPo();
        jpa.setGroup("add1: " + group);
        jpa.setDesc(desc);
        jpa.setProfile(profile);
        jpa.setDeleted(0);
        Timestamp timestamp = Timestamp.from(Instant.now());
        jpa.setCreateTime(timestamp);
        jpa.setUpdateTime(timestamp);
        ErrorMetaGroupPo res = errorGroupJPARepository.save(jpa);
        return res.getId();
    }
}

从输出结果来看，提示的是sql异常，why?

group,desc 为关键字，拼sql的时候需要用反引号包裹起来

3. 正确姿势一

第一种正确使用姿势，直接在@column的name中，添加反引号包裹起来

@Data
@Entity
@Table(name = "meta_group")
public class MetaGroupPO {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(name = "`group`")
    private String group;

    @Column(name = "`profile`")
    private String profile;

    @Column(name = "`desc`")
    private String desc;

    @Column(name = "`deleted`")
    private Integer deleted;

    @Column(name = "`create_time`")
    private Timestamp createTime;

    @Column(name = "`update_time`")
    private Timestamp updateTime;
}

4. 正确姿势二

除了上面的case之外，还有另外一种通用的方式，实现自定义的PhysicalNamingStrategy，实现字段映射

比如我们自定义JpaNamingStrategyStandardImpl继承自默认的PhysicalNamingStrategyStandardImpl策略，然后在字段名中，对于没有引号的包裹的字段名主动添加一个反引号

public class JpaNamingStrategyStandardImpl extends PhysicalNamingStrategyStandardImpl {
    @Setter
    private static int mode = 0;

    @Override
    public Identifier toPhysicalColumnName(Identifier name, JdbcEnvironment context) {
        if (mode == 1) {
            if (name.isQuoted()) {
                return name;
            } else {
                return Identifier.toIdentifier("`" + name.getText() + "`", true);
            }
        } else {
            return name;
        }
    }
}

注意使用上面的映射策略，需要修改配置文件(application.properties)

spring.jpa.hibernate.naming.physical-strategy=com.git.hui.boot.jpacase.strategy.JpaNamingStrategyStandardImpl

测试case

@SpringBootApplication
public class Application {
    public Application(GroupManager groupManager) {
        groupManager.test();
    }

    public static void main(String[] args) {
        JpaNamingStrategyStandardImpl.setMode(1);
        SpringApplication.run(Application.class, args);
    }
}

@Component
public class GroupManager {
    @Autowired
    private ErrorGroupJPARepository errorGroupJPARepository;

    @Autowired
    private GroupJPARepository groupJPARepository;


    public void test() {
        String group = UUID.randomUUID().toString().substring(0, 4);
        String profile = "dev";
        String desc = "测试jpa异常case!";
        try {
            int id = addGroup1(group, profile, desc);
            System.out.println("add1: " + errorGroupJPARepository.findById(id));
        } catch (Exception e) {
            e.printStackTrace();
        }

        try {
            int id2 = addGroup2(group, profile, desc);
            System.out.println("add2: " + groupJPARepository.findById(id2));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public Integer addGroup1(String group, String profile, String desc) {
        ErrorMetaGroupPo jpa = new ErrorMetaGroupPo();
        jpa.setGroup("add1: " + group);
        jpa.setDesc(desc);
        jpa.setProfile(profile);
        jpa.setDeleted(0);
        Timestamp timestamp = Timestamp.from(Instant.now());
        jpa.setCreateTime(timestamp);
        jpa.setUpdateTime(timestamp);
        ErrorMetaGroupPo res = errorGroupJPARepository.save(jpa);
        return res.getId();
    }

    public Integer addGroup2(String group, String profile, String desc) {
        MetaGroupPO jpa = new MetaGroupPO();
        jpa.setGroup("add2: " + group);
        jpa.setDesc(desc);
        jpa.setProfile(profile);
        jpa.setDeleted(0);
        Timestamp timestamp = Timestamp.from(Instant.now());
        jpa.setCreateTime(timestamp);
        jpa.setUpdateTime(timestamp);
        MetaGroupPO res = groupJPARepository.save(jpa);
        return res.getId();
    }
}

执行之后输出:

II. 其他

0. 项目&关联博文

推荐博文

源码

3 - JdbcTemplate

摆脱ORM框架，直接JdbcTemplate来操作数据库，数据扫描、脚本开发者都应该掌握的知识点，享受直写Sql的魅力

3.1 - 1.数据插入-Insert

db操作可以说是java后端的必备技能了，实际项目中，直接使用JdbcTemplate的机会并不多，大多是mybatis，hibernate，jpa或者是jooq，然后前几天写一个项目，因为db操作非常简单，就直接使用JdbcTemplate，然而悲催的发现，对他的操作并没有预期中的那么顺畅，所以有必要好好的学一下JdbcTemplate的CURD；本文为第一篇，插入数据

I. 环境

1. 配置相关

使用SpringBoot进行db操作引入几个依赖，就可以愉快的玩耍了，这里的db使用mysql，对应的pom依赖如

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
</dependencies>

接着就是db的配置信息,下面是连接我本机的数据库配置

## DataSource
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/story?useUnicode=true&characterEncoding=UTF-8&useSSL=false
spring.datasource.driver-class-name= com.mysql.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=

2. 测试db

创建一个测试db

CREATE TABLE `money` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '用户名',
  `money` int(26) NOT NULL DEFAULT '0' COMMENT '钱',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0',
  `create_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

II. 使用姿势

直接引入jdbcTemplate，注入即可，不需要其他的操作

@Autowired
private JdbcTemplate jdbcTemplate;

1. sql直接插入一条数据

直接写完整的插入sql，这种方式比较简单粗暴

private boolean insertBySql() {
    // 简单的sql执行
    String sql = "INSERT INTO `money` (`name`, `money`, `is_deleted`) VALUES ('一灰灰blog', 100, 0);";
    return jdbcTemplate.update(sql) > 0;
}

2. 参数替换方式插入

这种插入方式中，sql使用占位符？，然后插入值通过参数传入即可

private boolean insertBySqlParams() {
    String sql = "INSERT INTO `money` (`name`, `money`, `is_deleted`) VALUES (?, ?, ?);";
    return jdbcTemplate.update(sql, "一灰灰2", 200, 0) > 0;
}

3. 通过Statement方式插入

通过Statement可以指定参数类型，这种插入方式更加安全，有两种常见的方式，注意设置参数时，起始值为1，而不是通常说的0

private boolean insertByStatement() {
    String sql = "INSERT INTO `money` (`name`, `money`, `is_deleted`) VALUES (?, ?, ?);";
    return jdbcTemplate.update(sql, new PreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement preparedStatement) throws SQLException {
            preparedStatement.setString(1, "一灰灰3");
            preparedStatement.setInt(2, 300);
            byte b = 0;
            preparedStatement.setByte(3, b);
        }
    }) > 0;
}

private boolean insertByStatement2() {
    String sql = "INSERT INTO `money` (`name`, `money`, `is_deleted`) VALUES (?, ?, ?);";
    return jdbcTemplate.update(new PreparedStatementCreator() {
        @Override
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, "一灰灰4");
            preparedStatement.setInt(2, 400);
            byte b = 0;
            preparedStatement.setByte(3, b);
            return preparedStatement;
        }
    }) > 0;
}

4. 插入并返回主键id

这个属于比较常见的需求了，我希望获取插入数据的主键id，用于后续的业务使用; 这时就需要用KeyHolder

/**
 * 新增数据，并返回主键id
 *
 * @return
 */
private int insertAndReturnId() {
    String sql = "INSERT INTO `money` (`name`, `money`, `is_deleted`) VALUES (?, ?, ?);";
    KeyHolder keyHolder = new GeneratedKeyHolder();
    jdbcTemplate.update(new PreparedStatementCreator() {
        @Override
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            // 指定主键
            PreparedStatement preparedStatement = connection.prepareStatement(sql, new String[]{"id"});
            preparedStatement.setString(1, "一灰灰5");
            preparedStatement.setInt(2, 500);
            byte b = 0;
            preparedStatement.setByte(3, b);
            return preparedStatement;
        }
    }, keyHolder);
    return keyHolder.getKey().intValue();
}

看上面的实现，和前面差不多，但是有一行需要额外注意，在获取Statement时，需要制定主键，否则会报错

// 指定主键
PreparedStatement preparedStatement = connection.prepareStatement(sql, new String[]{"id"});

5. 批量插入

基本插入看完之后，再看批量插入，会发现和前面的姿势没有太大的区别，无非是传入一个数组罢了，如下面的几种使用姿势

private void batchInsertBySql() {
    String sql = "INSERT INTO `money` (`name`, `money`, `is_deleted`) VALUES " +
            "('Batch 一灰灰blog', 100, 0), ('Batch 一灰灰blog 2', 100, 0);";
    int[] ans = jdbcTemplate.batchUpdate(sql);
    System.out.println("batch insert by sql: " + JSON.toJSONString(ans));
}

private void batchInsertByParams() {
    String sql = "INSERT INTO `money` (`name`, `money`, `is_deleted`) VALUES (?, ?, ?);";

    Object[] param1 = new Object[]{"Batch 一灰灰 3", 200, 0};
    Object[] param2 = new Object[]{"Batch 一灰灰 4", 200, 0};
    int[] ans = jdbcTemplate.batchUpdate(sql, Arrays.asList(param1, param2));
    System.out.println("batch insert by params: " + JSON.toJSONString(ans));
}

private void batchInsertByStatement() {
    String sql = "INSERT INTO `money` (`name`, `money`, `is_deleted`) VALUES (?, ?, ?);";

    int[] ans = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
            if (i == 0) {
                preparedStatement.setString(1, "batch 一灰灰5");
            } else {
                preparedStatement.setString(1, "batch 一灰灰6");
            }
            preparedStatement.setInt(2, 300);
            byte b = 0;
            preparedStatement.setByte(3, b);
        }

        @Override
        public int getBatchSize() {
            return 2;
        }
    });
    System.out.println("batch insert by statement: " + JSON.toJSONString(ans));
}

6. 测试

接下来我们测试下上面的代码执行情况

@Component
public class InsertService {
    /**
     * 简单的新增一条数据
     */
    public void basicInsert() {
        System.out.println("basic insert: " + insertBySql());
        System.out.println("insertBySqlParams: " + insertBySqlParams());
        System.out.println("insertByStatement: " + insertByStatement());
        System.out.println("insertByStatement2: " + insertByStatement2());
        System.out.println("insertAndReturn: " + insertAndReturnId());

        List<Map<String, Object>> result = jdbcTemplate.queryForList("select * from money");
        System.out.println("after insert, the records:\n" + result);
    }
    
    /**
     * 批量插入数据
     */
    public void batchInsert() {
        batchInsertBySql();
        batchInsertByParams();
        batchInsertByStatement();
    }
}

@SpringBootApplication
public class Application {

    public Application(InsertService insertService) {
        insertService.basicInsert();
        insertService.batchInsert();
    }

    public static void main(String[] args) {
        SpringApplication.run(Application.class);
    }
}

输出结果如

basic insert: true
insertBySqlParams: true
insertByStatement: true
insertByStatement2: true
insertAndReturn: 5
after insert, the records:
[{id=1, name=一灰灰blog, money=100, is_deleted=false, create_at=2019-04-08 10:22:50.0, update_at=2019-04-08 10:22:50.0}, {id=2, name=一灰灰2, money=200, is_deleted=false, create_at=2019-04-08 10:22:55.0, update_at=2019-04-08 10:22:55.0}, {id=3, name=一灰灰3, money=300, is_deleted=false, create_at=2019-04-08 10:22:55.0, update_at=2019-04-08 10:22:55.0}, {id=4, name=一灰灰4, money=400, is_deleted=false, create_at=2019-04-08 10:22:55.0, update_at=2019-04-08 10:22:55.0}, {id=5, name=一灰灰5, money=500, is_deleted=false, create_at=2019-04-08 10:22:55.0, update_at=2019-04-08 10:22:55.0}]
batch insert by sql: [2]
batch insert by params: [1,1]
batch insert by statement: [1,1]

执行结果

II. 扩展

1. 批量插入并返回主键id

上面还漏了一个批量插入时，也需要返回主键id，改怎么办？

直接看JdbcTemplate的接口，并没有发现类似单个插入获取主键的方式，是不是意味着没法实现呢？

当然不是了，既然没有提供，我们完全可以依葫芦画瓢，自己实现一个 ExtendJdbcTemplate，首先看先单个插入返回id的实现如

接下来，我们自己的实现可以如下

public class ExtendJdbcTemplate extends JdbcTemplate {
    public ExtendJdbcTemplate(DataSource dataSource) {
        super(dataSource);
    }

    public int[] batchUpdate(final String sql, final BatchPreparedStatementSetter pss,
            final KeyHolder generatedKeyHolder) throws DataAccessException {
        return execute(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
                return conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            }
        }, ps -> {
            try {
                int batchSize = pss.getBatchSize();
                int totalRowsAffected = 0;
                int[] rowsAffected = new int[batchSize];
                List generatedKeys = generatedKeyHolder.getKeyList();
                generatedKeys.clear();
                ResultSet keys = null;
                for (int i = 0; i < batchSize; i++) {
                    pss.setValues(ps, i);
                    rowsAffected[i] = ps.executeUpdate();
                    totalRowsAffected += rowsAffected[i];
                    try {
                        keys = ps.getGeneratedKeys();
                        if (keys != null) {
                            RowMapper rowMapper = new ColumnMapRowMapper();
                            RowMapperResultSetExtractor rse = new RowMapperResultSetExtractor(rowMapper, 1);
                            generatedKeys.addAll(rse.extractData(keys));
                        }
                    } finally {
                        JdbcUtils.closeResultSet(keys);
                    }
                }
                if (logger.isDebugEnabled()) {
                    logger.debug("SQL batch update affected " + totalRowsAffected + " rows and returned " +
                            generatedKeys.size() + " keys");
                }
                return rowsAffected;
            } finally {
                if (pss instanceof ParameterDisposer) {
                    ((ParameterDisposer) pss).cleanupParameters();
                }
            }
        });
    }
}

封装完毕之后，我们的使用姿势可以为

@Autowired
private ExtendJdbcTemplate extendJdbcTemplate;

private void batchInsertAndReturnId() {
    String sql = "INSERT INTO `money` (`name`, `money`, `is_deleted`) VALUES (?, ?, ?);";

    GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();
    extendJdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
            if (i == 0) {
                preparedStatement.setString(1, "batch 一灰灰7");
            } else {
                preparedStatement.setString(1, "batch 一灰灰8");
            }
            preparedStatement.setInt(2, 400);
            byte b = 0;
            preparedStatement.setByte(3, b);
        }

        @Override
        public int getBatchSize() {
            return 2;
        }
    }, generatedKeyHolder);

    System.out.println("batch insert and return id ");
    List<Map<String, Object>> objectMap = generatedKeyHolder.getKeyList();
    for (Map<String, Object> map : objectMap) {
        System.out.println(map.get("GENERATED_KEY"));
    }
}

然后测试执行，输出结果如下

批量插入返回id

2. 小结

本篇主要介绍使用JdbcTemplate插入数据的几种常用姿势，分别从单个插入和批量插入进行了实例演示，包括以下几种常见姿势

update(sql)
update(sql, param1, param2...)
update(sql, new PreparedStatementCreator(){})
update(new PreparedStatementSetter(){})
update(new PreparedStatementCreator(){}, new GeneratedKeyHolder())

批量插入姿势和上面差不多，唯一需要注意的是，如果你想使用批量插入，并获取主键id，目前我没有找到可以直接使用的接口，如果有这方面的需求，可以参考下我上面的使用姿势

IV. 其他

0. 项目

3.2 - 2.数据查询Query-上

前面一篇介绍如何使用JdbcTemplate实现插入数据，接下来进入实际业务中，最常见的查询篇。由于查询的姿势实在太多，对内容进行了拆分，本篇主要介绍几个基本的使用姿势

queryForMap
queryForList
queryForObject

I. 环境准备

环境依然借助前面一篇的配置，链接如： 190407-SpringBoot高级篇JdbcTemplate之数据插入使用姿势详解

或者直接查看项目源码： https://github.com/liuyueyi/spring-boot-demo/blob/master/spring-boot/101-jdbctemplate

我们查询所用数据，正是前面一篇插入的结果，如下图

db mysql

II. 查询使用说明

1. queryForMap

queryForMap，一般用于查询单条数据，然后将db中查询的字段，填充到map中，key为列名，value为值

a. 基本使用姿势

最基本的使用姿势，就是直接写完整的sql，执行

String sql = "select * from money where id=1";
Map<String, Object> map = jdbcTemplate.queryForMap(sql);
System.out.println("QueryForMap by direct sql ans: " + map);

这种用法的好处是简单，直观；但是有个非常致命的缺点，如果你提供了一个接口为

public Map<String, Object> query(String condition) {
  String sql = "select * from money where name=" + condition;
  return jdbcTemplate.queryForMap(sql);
}

直接看上面代码，会发现问题么？？？

有经验的小伙伴，可能一下子就发现了sql注入的问题，如果传入的参数是 '一灰灰blog' or 1=1 order by id desc limit 1, 这样输出和我们预期的一致么？

b. 占位符替换

正是因为直接拼sql，可能到只sql注入的问题，所以更推荐的写法是通过占位符 + 传参的方式

// 使用占位符替换方式查询
sql = "select * from money where id=?";
map = jdbcTemplate.queryForMap(sql, new Object[]{1});
System.out.println("QueryForMap by ? ans: " + map);

// 指定传参类型, 通过传参来填充sql中的占位
sql = "select * from money where id =?";
map = jdbcTemplate.queryForMap(sql, 1);
System.out.println("QueryForMap by ? ans: " + map);

从上面的例子中也可以看出，占位符的使用很简单，用问好（?）来代替具体的取值，然后传参

传参有两种姿势，一个是传入Object[]数组；另外一个是借助java的不定长参数方式进行传参；两个的占位替换都是根据顺序来的，也就是如果你有一个值想替换多个占位符，那就得血多次

如：

sql = "select * from money where (name=? and id=?) or (name=? and id=?)";
map = jdbcTemplate.queryForMap(sql, "一灰灰blog", 1, "一灰灰blog", 2);

c. 查不到的case

使用queryForMap有个不得不注意的事项，就是如果查不到数据时，会抛一个异常出来，所以需要针对这种场景进行额外处理

// 查不到数据的情况
try {
    sql = "select * from money where id =?";
    map = jdbcTemplate.queryForMap(sql, 100);
    System.out.println("QueryForMap by ? ans: " + map);
} catch (EmptyResultDataAccessException e) {
    e.printStackTrace();
}

查询不到异常

2. queryForList

前面针对的主要是单个查询，如果有多个查询的场景，可能就需要用到queryForList了，它的使用姿势和上面其实差别不大；

a. 基本使用姿势

最基本的使用姿势当然是直接写sql执行了

System.out.println("============ query for List! ==============");
String sql =
        "select id, `name`, money, is_deleted as isDeleted, unix_timestamp(create_at) as created, unix_timestamp(update_at) as updated from money limit 3;";

// 默认返回 List<Map<String, Object>> 类型数据，如果一条数据都没有，则返回一个空的集合
List<Map<String, Object>> res = jdbcTemplate.queryForList(sql);
System.out.println("basicQueryForList: " + res);

注意返回的结果是List<Map<String, Object>>，如果一条都没有命中，会返回一个空集合, 和 QueryForMap 抛异常是不一样的

b. 占位符替换

直接使用sql的查询方式，依然和前面一样，可能有注入问题，当然优先推荐的使用通过占位来传参方式

String sql2 = "select id, `name`, money, is_deleted as isDeleted, unix_timestamp(create_at) as created, " +
        "unix_timestamp(update_at) as updated from money where id=? or name=?;";
res = jdbcTemplate.queryForList(sql2, 2, "一灰灰2");
System.out.println("queryForList by template: " + res);

3. queryForObject

如果是简单查询，直接用上面两个也就够了，但是对于使用过mybatis，Hibernate的同学来说，每次返回Map<String, Object>，就真的有点蛋疼了, 对于mysql这种数据库，表的结构基本不变，完全可以和POJO进行关联，对于业务开发者而言，当然是操作具体的POJO比Map要简单直观多了

下面将介绍下，如何使用 queryForObject 来达到我们的目标

a. 原始使用姿势

首先介绍下利用 RowMapper 来演示下，最原始的使用姿势

第一步是定义对应的POJO类

@Data
public static class MoneyPO implements Serializable {
    private static final long serialVersionUID = -5423883314375017670L;
    private Integer id;
    private String name;
    private Integer money;
    private boolean isDeleted;
    private Long created;
    private Long updated;
}

然后就是使用姿势

// sql + 指定返回类型方式访问
// 使用这种sql的有点就是方便使用反射方式，实现PO的赋值
String sql =
        "select id, `name`, money, is_deleted as isDeleted, unix_timestamp(create_at) as created, unix_timestamp(update_at) as updated from money limit 1;";
// 需要注意，下标以1开始
MoneyPO moneyPO = jdbcTemplate.queryForObject(sql, new RowMapper<MoneyPO>() {
    @Override
    public MoneyPO mapRow(ResultSet rs, int rowNum) throws SQLException {
        MoneyPO po = new MoneyPO();
        po.setId(rs.getInt(1));
        po.setName(rs.getString(2));
        po.setMoney(rs.getInt(3));
        po.setDeleted(rs.getBoolean(4));
        po.setCreated(rs.getLong(5));
        po.setUpdated(rs.getLong(6));
        return po;
    }
});
System.out.println("queryFroObject by RowMapper: " + moneyPO);

从使用姿势上看，RowMapper 就是一个sql执行之后的回调，实现结果封装，这里需要注意的就是 ResultSet 封装了完整的返回结果，可以通过下标方式指定，下标是从1开始，而不是我们常见的0，需要额外注意

这个下标从1开始，感觉有点蛋疼，总容易记错，所以更推荐的方法是直接通过列名获取数据

// 直接使用columnName来获取对应的值，这里就可以考虑使用反射方式来赋值，减少getter/setter
moneyPO = jdbcTemplate.queryForObject(sql, new RowMapper<MoneyPO>() {
    @Override
    public MoneyPO mapRow(ResultSet rs, int rowNum) throws SQLException {
        MoneyPO po = new MoneyPO();
        po.setId(rs.getInt("id"));
        po.setName(rs.getString("name"));
        po.setMoney(rs.getInt("money"));
        po.setDeleted(rs.getBoolean("isDeleted"));
        po.setCreated(rs.getLong("created"));
        po.setUpdated(rs.getLong("updated"));
        return po;
    }
});
System.out.println("queryFroObject by RowMapper: " + moneyPO);

b. 高级使用

当sql返回的列名和POJO的属性名可以完全匹配上的话，上面的这种写法就显得非常冗余和麻烦了，我需要更优雅简洁的使用姿势，最好就是直接传入POJO类型，自动实现转换

如果希望得到这个效果，你需要的就是下面这个了: BeanPropertyRowMapper

// 更简单的方式，直接通过BeanPropertyRowMapper来实现属性的赋值，前提是sql返回的列名能正确匹配
moneyPO = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(MoneyPO.class));
System.out.println("queryForObject by BeanPropertyRowMapper: " + moneyPO);

c. 易错使用姿势

查看JdbcTemplate提供的接口时，可以看到下面这个接口

@Override
public <T> T queryForObject(String sql, Class<T> requiredType, @Nullable Object... args) throws DataAccessException {
  return queryForObject(sql, args, getSingleColumnRowMapper(requiredType));
}

自然而然的想到，直接传入POJO的类型进去，是不是就可以得到我们预期的结果了?

String sql =
                "select id, `name`, money, is_deleted as isDeleted, unix_timestamp(create_at) as created, unix_timestamp(update_at) as updated from money limit 1;";
try {
    MoneyPO po = jdbcTemplate.queryForObject(sql, MoneyPO.class);
    System.out.println("queryForObject by requireType return: " + po);
} catch (Exception e) {
    e.printStackTrace();
}

执行上面的代码，抛出异常

从上面的源码也可以看到，上面的使用姿势，适用于sql只返回一列数据的场景，即下面的case

// 下面开始测试下 org.springframework.jdbc.core.JdbcTemplate.queryForObject(java.lang.String, java.lang.Class<T>, java.lang.Object...)
// 根据测试，这个类型，只能是基本类型
String sql2 = "select id from money where id=?";
Integer res = jdbcTemplate.queryForObject(sql2, Integer.class, 1);
System.out.println("queryForObject by requireId return: " + res);

show

4. 测试

上面所有代码可以查看: https://github.com/liuyueyi/spring-boot-demo/blob/master/spring-boot/101-jdbctemplate/src/main/java/com/git/hui/boot/jdbc/query/QueryService.java

简单的继承调用下上面的所有方法

@SpringBootApplication
public class Application {
    private QueryService queryService;

    public Application(QueryService queryService) {
        this.queryService = queryService;

        queryTest();
    }
    public void queryTest() {
        queryService.queryForMap();
        queryService.queryForObject();
        queryService.queryForList();
    }

    public static void main(String[] args) {
        SpringApplication.run(Application.class);
    }
}

输出结果如下

result

III. 小结

本篇博文主要介绍了JdbcTemplate查询的简单使用姿势，主要是queryForMap, queryForList, queryForObject三种方法的调用

1. 根据返回结果数量

单条记录查询

queryForMap ：返回一条记录，返回的结果塞入Map<String, Object>， key为固定的String对应查询的列名；value为实际值
queryForObject ：同样返回一条数据，与上面的区别在于可以借助RowMapper来实现返回结果转换为对应的POJO

需要注意的是，上面的查询，必须有一条记录返回，如果查不到，则抛异常

批量查询

queryForList ：一次查询>=0条数据，返回类型为 List<Map<String, Object>>

2. 根据sql类型

有两种sql传参方式

一个是写完整的sql语句，就和我们普通的sql查询一样；问题是存在注入的风险
其次是使用占位符(?), 实际的值通过参数方式传入

IV. 其他

0. 项目

3.3 - 3.数据查询Query-下

SpringBoot高级篇JdbcTemplate之数据查询上篇讲了如何使用JdbcTemplate进行简单的查询操作，主要介绍了三种方法的调用姿势 queryForMap, queryForList, queryForObject 本篇则继续介绍剩下的两种方法使用说明

queryForRowSet
query

I. 环境准备

环境依然借助前面一篇的配置，链接如： 190407-SpringBoot高级篇JdbcTemplate之数据插入使用姿势详解

或者直接查看项目源码： https://github.com/liuyueyi/spring-boot-demo/blob/master/spring-boot/101-jdbctemplate

我们查询所用数据，正是前面一篇插入的结果，如下图

data

II. 查询使用说明

1. queryForRowSet

查询上篇中介绍的三种方法，返回的记录对应的结构要么是map，要么是通过RowMapper进行结果封装；而queryForRowSet方法的调用，返回的则是SqlRowSet对象，这是一个集合，也就是说，可以查询多条记录

使用姿势也比较简单，如下

public void queryForRowSet() {
    String sql = "select * from money where id > 1 limit 2";
    SqlRowSet result = jdbcTemplate.queryForRowSet(sql);
    while (result.next()) {
        MoneyPO moneyPO = new MoneyPO();
        moneyPO.setId(result.getInt("id"));
        moneyPO.setName(result.getString("name"));
        moneyPO.setMoney(result.getInt("money"));
        moneyPO.setDeleted(result.getBoolean("is_deleted"));
        moneyPO.setCreated(result.getDate("create_at").getTime());
        moneyPO.setUpdated(result.getDate("update_at").getTime());

        System.out.println("QueryForRowSet by DirectSql: " + moneyPO);
    }
}

对于使用姿势而言与之前的区别不大，还有一种就是sql也支持使用占位方式，如

// 采用占位符方式查询
sql = "select * from money where id > ? limit ?";
result = jdbcTemplate.queryForRowSet(sql, 1, 2);
while (result.next()) {
    MoneyPO moneyPO = new MoneyPO();
    moneyPO.setId(result.getInt("id"));
    moneyPO.setName(result.getString("name"));
    moneyPO.setMoney(result.getInt("money"));
    moneyPO.setDeleted(result.getBoolean("is_deleted"));
    moneyPO.setCreated(result.getDate("create_at").getTime());
    moneyPO.setUpdated(result.getDate("update_at").getTime());

    System.out.println("QueryForRowSet by ? sql: " + moneyPO);
}

重点关注下结果的处理，需要通过迭代器的方式进行数据遍历，获取每一列记录的值的方式和前面一样，可以通过序号的方式获取(序号从1开始)，也可以通过制定列名方式（db列名）

2. query

对于query方法的使用，从不同的结果处理方式来看，划分了四种，下面逐一说明

a. 回调方式 `queryByCallBack`

这种回调方式，query方法不返回结果，但是需要传入一个回调对象，查询到结果之后，会自动调用

private void queryByCallBack() {
    String sql = "select * from money where id > 1 limit 2";
    // 这个是回调方式，不返回结果；一条记录回调一次
    jdbcTemplate.query(sql, new RowCallbackHandler() {
        @Override
        public void processRow(ResultSet rs) throws SQLException {
            MoneyPO moneyPO = result2po(rs);
            System.out.println("queryByCallBack: " + moneyPO);
        }
    });
}

上面的实例代码中，可以看到回调方法中传入一个ResultSet对象，简单封装一个转换为PO的方法

private MoneyPO result2po(ResultSet result) throws SQLException {
    MoneyPO moneyPO = new MoneyPO();
    moneyPO.setId(result.getInt("id"));
    moneyPO.setName(result.getString("name"));
    moneyPO.setMoney(result.getInt("money"));
    moneyPO.setDeleted(result.getBoolean("is_deleted"));
    moneyPO.setCreated(result.getDate("create_at").getTime());
    moneyPO.setUpdated(result.getDate("update_at").getTime());
    return moneyPO;
}

在后面的测试中，会看到上面会输出两行数据，也就是说

返回结果中每一条记录都执行一次上面的回调方法，即返回n条数据，上面回调执行n次

b. 结果批量处理 `ResultSetExtractor`

前面回调方式主要针对的是不关系返回结果，这里的则是将返回的结果，封装成我们预期的对象，然后返回

private void queryByResultSet() {
    String sql = "select * from money where id > 1 limit 2";
    // extractData 接收的是批量的结果，因此可以理解为一次对所有的结果进行转换，可以和 RowMapper 方式进行对比
    List<MoneyPO> result = jdbcTemplate.query(sql, new ResultSetExtractor<List<MoneyPO>>() {
        @Override
        public List<MoneyPO> extractData(ResultSet rs) throws SQLException, DataAccessException {
            List<MoneyPO> list = new ArrayList<>();
            while (rs.next()) {
                list.add(result2po(rs));
            }
            return list;
        }
    });

    System.out.println("queryByResultSet: " + result);
}

额外注意下上面你的使用，如果返回的是多条数据，注意泛型参数类型为List<?>, 简单来说这是一个对结果进行批量转换的使用场景

因此在上面的extractData方法调用时，传入的是多条数据，需要自己进行迭代遍历，而不能像第一种那样使用

c. 结果单行处理 `RowMapper`

既然前面有批量处理，那当然也就有单行的转换方式了，如下

private void queryByRowMapper() {
    String sql = "select * from money where id > 1 limit 2";
    // 如果返回的是多条数据，会逐一的调用 mapRow方法，因此可以理解为单个记录的转换
    List<MoneyPO> result = jdbcTemplate.query(sql, new RowMapper<MoneyPO>() {
        @Override
        public MoneyPO mapRow(ResultSet rs, int rowNum) throws SQLException {
            return result2po(rs);
        }
    });
    System.out.println("queryByRowMapper: " + result);
}

在实际使用中，只需要记住RowMapper方式传入的是单条记录，n次调用；而ResultSetExtractor方式传入的全部的记录，1次调用

d. 占位sql

前面介绍的几种都是直接写sql，这当然不是推荐的写法，更常见的是占位sql，通过传参替换，这类的使用前一篇博文介绍得比较多了，这里给出一个简单的演示

private void queryByPlaceHolder() {
    String sql = "select * from money where id > ? limit ?";
    // 占位方式，在最后面加上实际的sql参数，第二个参数也可以换成 ResultSetExtractor
    List<MoneyPO> result = jdbcTemplate.query(sql, new RowMapper<MoneyPO>() {
        @Override
        public MoneyPO mapRow(ResultSet rs, int rowNum) throws SQLException {
            return result2po(rs);
        }
    }, 1, 2);
    System.out.println("queryByPlaceHolder: " + result);
}

e. `PreparedStatement` 方式

在插入记录的时候，PreparedStatement这个我们用得很多，特别是在要求返回主键id时，离不开它了，在实际的查询中，也是可以这么用的，特别是在使用PreparedStatementCreator，我们可以设置查询的db连接参数

private void queryByPreparedStatement() {
    // 使用 PreparedStatementCreator查询，主要是可以设置连接相关参数, 如设置为只读
    List<MoneyPO> result = jdbcTemplate.query(new PreparedStatementCreator() {
        @Override
        public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
            con.setReadOnly(true);
            PreparedStatement statement = con.prepareStatement("select * from money where id > ? limit ?");
            // 表示 id > 1
            statement.setInt(1, 1);
            // 表示 limit 2
            statement.setInt(2, 2);
            return statement;
        }
    }, new RowMapper<MoneyPO>() {
        @Override
        public MoneyPO mapRow(ResultSet rs, int rowNum) throws SQLException {
            return result2po(rs);
        }
    });

    System.out.println("queryByPreparedStatement: " + result);
}

上面是一个典型的使用case，当然在实际使用JdbcTemplate时，基本不这么玩

f. 查不到数据场景

前面一篇查询中，在单个查询中如果没有结果命中sql，会抛出异常，那么这里呢？

private void queryNoRecord() {
    // 没有命中的情况下，会怎样
    List<MoneyPO> result = jdbcTemplate
            .query("select * from money where id > ? limit ?", new Object[]{100, 2}, new RowMapper<MoneyPO>() {
                @Override
                public MoneyPO mapRow(ResultSet rs, int rowNum) throws SQLException {
                    return result2po(rs);
                }
            });

    System.out.println("queryNoRecord: " + result);
}

从后面的输出结果会看出，没有记录命中时，并没有什么关系，上面会返回一个空集合

III. 测试&小结

1. 测试

接下来测试下上面的输出

package com.git.hui.boot.jdbc;

import com.git.hui.boot.jdbc.insert.InsertService;
import com.git.hui.boot.jdbc.query.QueryService;
import com.git.hui.boot.jdbc.query.QueryServiceV2;
import com.git.hui.boot.jdbc.update.UpdateService;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

/**
 * Created by @author yihui in 11:04 19/4/4.
 */
@SpringBootApplication
public class Application {
    private QueryServiceV2 queryServiceV2;

    public Application(QueryServiceV2 queryServiceV2) {
        this.queryServiceV2 = queryServiceV2;
        queryTest2();
    }

    public void queryTest2() {
        // 第三个调用
        queryServiceV2.queryForRowSet();
        queryServiceV2.query();
    }

    public static void main(String[] args) {
        SpringApplication.run(Application.class);
    }
}

上面执行输出结果如下

test output

2. 小结

本文主要介绍了另外两种查询姿势, queryForRowSet 与 query

queryForRowSet

返回SqlRowSet对象，需要遍历获取所有的结果

query

提供三种结果处理方式
- 不返回结果的回调姿势
- 对结果批量处理的方式 ResultSetExtractor
- 对结果单个迭代处理方式 RowMapper
可以返回>=0条数据
如果需要对查询的连接参数进行设置，使用PreparedStatementCreator来创建PreparedStatement方式处理

IV. 其他

相关博文

0. 项目

3.4 - 4.数据更新与删除

前面介绍了JdbcTemplate的插入数据和查询数据，占用CURD中的两项，本文则将主要介绍数据更新和删除。从基本使用上来看，姿势和前面的没啥两样

I. 环境准备

环境依然借助前面一篇的配置，链接如： 190407-SpringBoot高级篇JdbcTemplate之数据插入使用姿势详解

或者直接查看项目源码： https://github.com/liuyueyi/spring-boot-demo/blob/master/spring-boot/101-jdbctemplate

我们查询所用数据，正是前面一篇插入的结果，如下图

data

II. 更新使用说明

对于数据更新，这里会分为两种进行说明，单个和批量；这个单个并不是指只能一条记录，主要针对的是sql的数量而言

1. update 方式

看过第一篇数据插入的童鞋，应该也能发现，新增数据也是用的这个方法，下面会介绍三种不同的使用姿势

先提供一个数据查询的转换方法，用于对比数据更新前后的结果

private MoneyPO queryById(int id) {
    return jdbcTemplate.queryForObject(
            "select id, `name`, money, is_deleted as isDeleted, unix_timestamp(create_at) as " +
                    "created, unix_timestamp(update_at) as updated from money where id=?",
            new BeanPropertyRowMapper<>(MoneyPO.class), id);
}

a. 纯sql更新

这个属于最基本的方式了，前面几篇博文中大量使用了，传入一条完整的sql，执行即可

 int id = 10;

// 最基本的sql更新
String sql = "update money set money=money + 999 where id =" + id;
int ans = jdbcTemplate.update(sql);
System.out.println("basic update: " + ans + " | db: " + queryById(id));

b. 占位sql

问好占位，实际内容通过参数传递方式

// 占位方式
sql = "update money set money=money + ? where id = ?";
ans = jdbcTemplate.update(sql, 888, id);
System.out.println("placeholder update: " + ans + " | db: " + queryById(id));

c. statement

从前面的几篇文章中可以看出，使用statement的方式，最大的好处有几点

可以点对点的设置填充参数
PreparedStatementCreator 方式可以获取db连接，主动设置各种参数

下面给出两个常见的使用方式

// 通过 PreparedStatementCreator 方式更新
ans = jdbcTemplate.update(new PreparedStatementCreator() {
    @Override
    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
        // 设置自动提交，设置100ms的超时，这种方式最大的好处是可以控制db连接的参数
        try {
            connection.setAutoCommit(true);
            connection.setNetworkTimeout(Executors.newSingleThreadExecutor(), 10);
            PreparedStatement statement =
                    connection.prepareStatement("update money set money=money + ? where id " + "= ?");
            statement.setInt(1, 777);
            statement.setInt(2, id);
            return statement;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }
});
System.out.println("statementCreator update: " + ans + " | db: " + queryById(id));


// 通过 PreparedStatementSetter 来设置占位参数值
ans = jdbcTemplate.update(sql, new PreparedStatementSetter() {
    @Override
    public void setValues(PreparedStatement preparedStatement) throws SQLException {
        preparedStatement.setInt(1, 666);
        preparedStatement.setInt(2, id);
    }
});
System.out.println("statementSetter update: " + ans + " | db: " + queryById(id));

注意下第一种调用中，设置了超时时间，下面给出一个动图，演示超时的使用姿势

show

在上图中，

首先是一个开启一个事物，并修改了一条记录，这个时候这条记录会加上写锁
然后JdbcTemplate中修改上面的这条记录，尝试加写锁，但是会失败，所以一直阻塞，当超时之后，抛出异常

2. batchUpdate 方式

批量方式，执行多个sql，从使用上看和前面没有太大的区别，先给出一个查询的通用方法

private List<MoneyPO> queryByIds(List<Integer> ids) {
    StringBuilder strIds = new StringBuilder();
    for (Integer id : ids) {
        strIds.append(id).append(",");
    }
    return jdbcTemplate.query("select id, `name`, money, is_deleted as isDeleted, unix_timestamp(create_at) as " +
            "created, unix_timestamp(update_at) as updated from money where id in (" +
            strIds.substring(0, strIds.length() - 1) + ")", new BeanPropertyRowMapper<>(MoneyPO.class));
}

a. 纯sql更新

// 批量修改，
// 执行多条sql的场景
int[] ans = jdbcTemplate
        .batchUpdate("update money set money=1300 where id =10", "update money set money=1300 where id = 11");
System.out.println(
        "batch update by sql ans: " + Arrays.asList(ans) + " | db: " + queryByIds(Arrays.asList(10, 11)));

b. 占位sql

// 占位替换方式
ans = jdbcTemplate.batchUpdate("update money set money=money + ? where id = ?",
        Arrays.asList(new Object[]{99, 10}, new Object[]{99, 11}));
System.out.println("batch update by placeHolder ans: " + Arrays.asList(ans) + " | db: " +
        queryByIds(Arrays.asList(10, 11)));

c. statement

// 通过 statement
ans = jdbcTemplate
        .batchUpdate("update money set money=money + ? where id = ?", new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
                preparedStatement.setInt(1, 99);
                preparedStatement.setInt(2, i + 10);
            }

            @Override
            public int getBatchSize() {
                return 2;
            }
        });
System.out.println(
        "batch update by statement ans: " + Arrays.asList(ans) + " | db: " + queryByIds(Arrays.asList(10, 11)));

注意下上面的方法中，getBatchSize返回实际的sql条数，setValues中的i从0开始

3. 测试

原始数据中，money都是300，通过一系列的修改，输出如下

test result

III. 数据删除

删除的操作姿势和上面基本一样，也就是sql的写法不同罢了，因此没有太大的必要重新写一篇，下面给出一个简单的demo

@Component
public class DeleteService {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    public void delete() {
        int ans = jdbcTemplate.update("delete from money where id = 13");
        System.out.println("delete: " + ans);
    }
}

IV. 其他

相关博文

0. 项目

3.5 - 5.多数据源配置与使用

虽然我们前面的db系列教程更多的是基于单数据源的db操作，但是实际的业务开发中，难免会遇到一个项目配置多个数据源的情况，接下来本文将介绍一下多个数据源可以怎么配置，我们的JdbcTemplate又应该如何获取

I. 环境准备

1. 数据库相关

以mysql为例进行演示说明，因为需要多数据源，一个最简单的case就是一个物理库上多个逻辑库，本文是基于本机的mysql进行操作

创建数据库test 与 story，两个库下都存在一个表money (同名同结构表，但是数据不同哦)

CREATE TABLE `money` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '用户名',
  `money` int(26) NOT NULL DEFAULT '0' COMMENT '钱',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0',
  `create_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

data

2. 项目环境

本项目借助SpringBoot 2.2.1.RELEASE + maven 3.5.3 + IDEA进行开发

下面是核心的pom.xml（源码可以再文末获取）

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
</dependencies>

配置文件信息application.yml

# 数据库相关配置
spring:
  datasource:
    story:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://127.0.0.1:3306/story?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
      username: root
      password:
    test:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
      username: root
      password:


# 日志相关
logging:
  level:
    root: info
    org:
      springframework:
        jdbc:
          core: debug

请注意上面的数据库配置，我们前面介绍的但数据库配置如下，它们层级并不一样，上面的配置需要我们自己额外进行加载解析

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/story?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
    username: root
    password:

II. 多数据源支持

1. 数据源配置

SpringBoot帮我们省略了很多操作，单数据源时，在yaml文件中配置数据库相关信息之后，我们不需要任何其他操作，Spring会帮我们实例对应的DataSource，然后借助它来创建JdbcTemplate

而多数据源则需要我们自己来额外处理了，请注意上面的配置信息，和默认的配置没什么差别，只是前缀多了一层，我们可以借助@ConfigurationProperties来加载配置文件

@Configuration
public class DataSourceConfiguration {

    @Primary
    @Bean(name = "storyDataSourceProperties")
    @ConfigurationProperties(prefix = "spring.datasource.story")
    public DataSourceProperties storyDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Primary
    @Bean(name = "storyDataSource")
    public DataSource storyDataSource(@Qualifier("storyDataSourceProperties") DataSourceProperties storyDataSourceProperties) {
        return storyDataSourceProperties.initializeDataSourceBuilder().build();
    }

    @Bean(name = "testDataSourceProperties")
    @ConfigurationProperties(prefix = "spring.datasource.test")
    public DataSourceProperties testDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean(name = "testDataSource")
    public DataSource testDataSource(@Qualifier("testDataSourceProperties") DataSourceProperties testDataSourceProperties) {
        return testDataSourceProperties.initializeDataSourceBuilder().build();
    }
}

配置代码如上，一个是将数据源配置映射为bean DataSourceProperties, 一个是根据数据源配置实例化DataSource；

此外两个@Primary注解，主要是为了设置默认的数据源

2. JdbcTemplate实例化

数据源已经获取到之后，再实例化JdbcTemplate就很简单了，除了直接声明bean之外，也可以基于DataSource来手动创建临时对象，下面给出两种使用姿势

定义对应的bean，对于业务使用更友好

@Bean("storyJdbcTemplate")
public JdbcTemplate storyJdbcTemplate(@Qualifier("storyDataSource") DataSource storyDataSource) {
    return new JdbcTemplate(storyDataSource);
}

@Bean("testJdbcTemplate")
public JdbcTemplate testJdbcTemplate(@Qualifier("testDataSource") DataSource testDataSource) {
    return new JdbcTemplate(testDataSource);
}

借助ApplicationContext来实例化JdbcTemplate，使用更灵活

@Service
public class JdbcServer implements ApplicationContextAware {

    private JdbcTemplate storyJdbcTemplate;

    private JdbcTemplate testJdbcTemplate;

    @Override
    public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
        Map<String, DataSource> map = applicationContext.getBeansOfType(DataSource.class);
        System.out.println(map);
        storyJdbcTemplate = new JdbcTemplate(map.get("storyDataSource"));
        testJdbcTemplate = new JdbcTemplate(map.get("testDataSource"));
    }
}

3. 测试case

最后简单测试一下上面创建的两个JdbcTemplate是否访问不同的数据库

public void query() {
    List<Map<String, Object>> storyRes = storyJdbcTemplate.queryForList("select * from money where id in (1, 1000)");
    List<Map<String, Object>> testRes = testJdbcTemplate.queryForList("select * from money where id in (1, 1000)");
    System.out.println(storyRes);
    System.out.println("--------------");
    System.out.println(testRes);
}

启动类如下

@SpringBootApplication
public class Application {

    public Application(JdbcServer jdbcServer) {
        jdbcServer.query();
    }

    public static void main(String[] args) {
        SpringApplication.run(Application.class);
    }
}

testcase

相同的sql，获取的结果并不一样，分别从两个库中获取的数据

4. 小结

使用多数据源，比较简单的思路就是自定义配置的加载方式，获取不同的DataSourceProperties对象，然后基于它来创建DataSource实例

利用JdbcTemplate来操作db的应用场景，直接选择不同的数据源Datasource就行了

当然实际的项目中，我们一般会借助Herbernate、Myabtis、Jooq等orm框架，那么使用orm时，多数据源又应该怎么处理呢? （请持续关注，相关博文即将上线）

II. 其他

0. 项目

系列博文

项目源码

4 - Jooq

享受链式的sql写法，可以说是我用过最顺滑的orm框架，通过一些列博文让您体验下这个好用到要收费的ORM是怎么玩耍的

4.1 - 1.Jooq初体验

java环境中，说到数据库的操作，我们通常会想到的是mybatis或者hibernate，今天给大家介绍一个国内可能用得不太多的操作方式JOOQ，一款基于Java访问关系型数据库的工具包，轻量，简单，并且足够灵活的ORM框架

本文将各位小伙伴演示一下jooq集成springboot的姿势

I. 项目搭建

我们这里借助h2dabase来搭建演示项目，因此有兴趣的小伙伴在文末可以直接获取项目地址启动即可体验，不需要额外的安装和配置mysql了

本文采用SpringBoot 2.2.1.RELEASE + maven 3.5.3 + IDEA进行开发

1. pom依赖

下面给出核心的依赖配置

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jooq</artifactId>
</dependency>
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
</dependency>

2. 配置

接下来设置一下数据库相关的配置信息，在资源目录resources下，新建配置文件application.properties

#Database Configuration
spring.datasource.url=jdbc:h2:~/h2-jooq-db
spring.datasource.username=test
spring.datasource.password=
spring.datasource.driverClassName=org.h2.Driver

3. 数据库初始化

jooq有一个特点，是需要我们自己来生成表结构对象，所以我们先初始化一下h2dabase的数据结构，详情可以参考博文【DB系列h2databse集成示例demo】

表结构定义文件schema-h2.sql, 请注意表结构与mysql的表创建姿势不太一样哦

DROP TABLE IF EXISTS poet;

CREATE TABLE poet (
  `id` int NOT NULL,
  `name` varchar(20) NOT NULL default '',
  CONSTRAINT pk_t_poet PRIMARY KEY (ID)
);

数据初始化data-h2.sql

INSERT INTO `poet` (`id`, `name`)
VALUES
	(1, '李白'),
	(2, '艾可翁'),
	(3, '敖陶孙'),
	(4, '安稹'),
	(5, '艾性夫'),
	(6, '奥敦周卿'),
	(7, '安鏖'),
	(8, '阿鲁威'),
	(9, '安鸿渐'),
	(10, '安邑坊女');

我们接下来借助maven插件来初始化数据, pom.xml文件中，添加如下配置

<!-- The H2 test schema is loaded here -->
<plugin>
    <groupId>org.codehaus.mojo</groupId>
    <artifactId>sql-maven-plugin</artifactId>

    <executions>
        <execution>
            <id>create-database-h2</id>
            <phase>generate-sources</phase>
            <goals>
                <goal>execute</goal>
            </goals>
        </execution>
    </executions>

    <configuration>
        <driver>org.h2.Driver</driver>
        <url>jdbc:h2:~/h2-jooq-db</url>
        <username>test</username>
        <password></password>
        <autocommit>true</autocommit>
        <srcFiles>
            <srcFile>src/main/resources/schema-h2.sql</srcFile>
            <srcFile>src/main/resources/data-h2.sql</srcFile>
        </srcFiles>
    </configuration>

    <dependencies>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>1.4.200</version>
        </dependency>
    </dependencies>
</plugin>

如下图的case，完成数据的初始化

II. 体验case

在实际开始jooq的curd之前，需要先生成对应的表结构对象，这里也是借助maven插件来完成

1. 代码自动生成

同样在pom.xml中添加如下配置

<plugin>
      <groupId>org.jooq</groupId>
      <artifactId>jooq-codegen-maven</artifactId>

      <executions>
          <execution>
              <id>generate-h2</id>
              <phase>generate-sources</phase>
              <goals>
                  <goal>generate</goal>
              </goals>
          </execution>
      </executions>
      <configuration>
          <jdbc>
              <!-- 数据库相关配置 -->
              <driver>org.h2.Driver</driver>
              <url>jdbc:h2:~/h2-jooq-db</url>
              <username>test</username>
              <password></password>
          </jdbc>
          <generator>
              <database>
                  <!-- 数据库的基本信息 -->
                  <name>org.jooq.meta.h2.H2Database</name>
                  <includes>.*</includes>
                  <excludes></excludes>
                  <inputSchema>PUBLIC</inputSchema>
              </database>
              <generate>
                  <deprecated>false</deprecated>
                  <instanceFields>true</instanceFields>
                  <pojos>true</pojos>
              </generate>
              <target>
                  <!-- 自动生成的类的包名，以及路径 -->
                  <packageName>com.git.hui.boot.jooq.h2</packageName>
                  <directory>src/main/java</directory>
              </target>
          </generator>
      </configuration>
  </plugin>

如上图的方式执行完毕之后，会得到生成的代码

2. CURD

接下来我们给出CURD的基本使用姿势

import static com.git.hui.boot.jooq.h2.tables.Poet.POET;

@Service
public class PoetService {

    @Autowired
    DSLContext dsl;

    public int create(int id, String author) {
        return dsl.insertInto(POET).set(POET.ID, id).set(POET.NAME, author).execute();
    }

    public PoetRecord get(int id) {
        return dsl.selectFrom(POET).where(POET.ID.eq(id)).fetchOne();
    }

    public int update(int id, String author) {
        return dsl.update(POET).set(POET.NAME, author).where(POET.ID.eq(id)).execute();
    }

    public int delete(int id) {
        return dsl.delete(POET).where(POET.ID.eq(id)).execute();
    }

    public List<PoetRecord> getAll() {
        return dsl.selectFrom(POET).fetch();
    }
}

注意上面的使用，很好理解了，基本上能愉快的写sql，就可以愉快的使用jooq，上面的这种链式写法，对于sql的阅读是非常友好的；这里的重点是DSLContext，它是JooqAutoConfiguration自动加载的，这里直接拿来使用了（关于更多的配置与多数据源的问题，后面介绍）

3. 测试case

在pom中引入web依赖，设计一些基本的测试case

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>

增删改查case

@RestController
public class PoetRest {
    @Autowired
    private PoetService poetService;

    @RequestMapping(path = "add")
    public int add(Integer id, String name) {
        return poetService.create(id, name);
    }

    @GetMapping(path = "get")
    public String get(Integer id) {
        PoetRecord record = poetService.get(id);
        return r2str(record);
    }

    @GetMapping(path = "list")
    public List<String> list() {
        List<PoetRecord> list = poetService.getAll();
        return list.stream().map(this::r2str).collect(Collectors.toList());
    }


    @GetMapping(path = "update")
    public int update(int id, String author) {
        return poetService.update(id, author);
    }

    @GetMapping(path = "del")
    public int delete(int id) {
        return poetService.delete(id);
    }


    private String r2str(PoetRecord record) {
        return record.getId() + " # " + record.getName();
    }
}

实测结果如下

4. 小结

到此，SpringBoot集成jooq的demo已经完成，并提供了基础的CURD，整体来看，集成比较简单，需要注意的是代码自动生成，我们这里是借助maven插件来实现代码自动生成的，此外也可以通过官方提供的jooq-xx.jar + xml配置文件来自动生成；后面单独捞一篇博文给与介绍

从jooq的使用姿势来看，最大的感官就是类sql的链式写法，比较的直观，阅读友好；此外需要注意的是自动生成的实体PoetRecord，不要暴露出去哦，一般推荐使用jooq包下面的Poet来代替PoetRecord来作为BO对象使用，可以通过RecordMapper来实现转换，如下

public Poet getById(int id) {
    PoetRecord record = dsl.selectFrom(POET).where(POET.ID.eq(id)).fetchOne();
    RecordMapper<PoetRecord, Poet> mapper =
            dsl.configuration().recordMapperProvider().provide(POET.recordType(), POET.getClass());
    return mapper.map(record);
}

II. 其他

0. 项目

工程：https://github.com/liuyueyi/spring-boot-demo
项目源码: https://github.com/liuyueyi/spring-boot-demo/tree/master/spring-boot/107-jooq-aop

4.2 - 2.Jooq代码自动生成

Jooq提供了非常简单的方式来自动生成对应的代码，就我个人的使用感触，比mybatis-plus用起来爽，下面介绍两种殊途同归的自动生成方式

jooq jar包生成方式
maven插件生成方式

I. 前期准备

在前面体验篇中介绍的是maven插件 + h2database的代码自动生成方式；本文则将数据库替换为最常见的mysql，使用姿势上差别也不会太大

1. 数据表准备

不管使用前面说的两种方式的哪一种，前提是mysql中必须存在对应的表结构，我们这里指定两个简单的表

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(80) NOT NULL DEFAULT '' COMMENT '用户名',
  `pwd` varchar(26) NOT NULL DEFAULT '' COMMENT '密码',
  `isDeleted` tinyint(1) NOT NULL DEFAULT '0',
  `created` varchar(13) NOT NULL DEFAULT '0',
  `updated` varchar(13) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `money` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '用户名',
  `money` int(26) NOT NULL DEFAULT '0' COMMENT '钱',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0',
  `create_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 数据库相关信息

数据库连接信息

spring:
  datasource:
    # 注意指定时区
    url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
    username: root
    password:

II. 代码自动生成

本文主要介绍的是基于SpringBoot的jooq集成，本文使用 SpringBoot 2.2.1.RELEASE

1. maven插件

首先需要创建一个SpringBoot项目，在pom.xml配置文件中，借助jooq-codegen-maven来实现代码生成

一个完整的配置示例如下

<build>
    <plugins>
        <plugin>
            <groupId>org.jooq</groupId>
            <artifactId>jooq-codegen-maven</artifactId>
            <!--下面这段不注释时，可以通过 mvn clean install 来生成代码-->
            <executions>
                <execution>
                    <id>convergence</id>
                    <phase>generate-sources</phase>
                    <goals>
                        <goal>generate</goal>
                    </goals>
                </execution>
            </executions>

            <dependencies>
                <dependency>
                    <groupId>mysql</groupId>
                    <artifactId>mysql-connector-java</artifactId>
                    <version>8.0.18</version>
                </dependency>
            </dependencies>

            <configuration>
                <jdbc>
                    <!-- 数据库相关配置 -->
                    <driver>com.mysql.cj.jdbc.Driver</driver>
                    <url>jdbc:mysql://127.0.0.1:3306/test</url>
                    <user>root</user>
                    <password></password>
                </jdbc>

                <generator>
                    <name>org.jooq.codegen.JavaGenerator</name>

                    <database>
                        <!-- 数据库的基本信息 -->
                        <name>org.jooq.meta.mysql.MySQLDatabase</name>
                        <inputSchema>test</inputSchema>
                        <!-- 所有的表都包含进来，用于自动生成代码 -->
                        <includes>user|money</includes>
                        <excludes></excludes>
                    </database>

                    <generate>
                        <pojos>true</pojos>
                    </generate>

                    <target>
                        <!-- 自动生成的类的包名，以及路径 -->
                        <packageName>com.git.hui.boot.jooq.dao</packageName>
                        <directory>src/main/java</directory>
                    </target>

                    <strategy>
                        <matchers>
                            <tables>
                                <table>
                                    <expression>^(.*)$</expression>
                                    <tableClass>
                                        <!-- table的后缀为TB -->
                                        <transform>PASCAL</transform>
                                        <expression>$1_T_B</expression>
                                    </tableClass>
                                    <recordClass>
                                        <!-- record的后缀为PO，表示实体类 -->
                                        <transform>PASCAL</transform>
                                        <expression>$1_P_O</expression>
                                    </recordClass>
                                    <pojoClass>
                                        <!-- pojo后缀为BO，作为内部使用的简单对象-->
                                        <transform>PASCAL</transform>
                                        <expression>$1_B_O</expression>
                                    </pojoClass>
                                </table>
                            </tables>
                        </matchers>
                    </strategy>
                </generator>
            </configuration>
        </plugin>
    </plugins>
</build>

上面的配置虽然长，但是结构比较清晰，下面拆分说明一下

a. 驱动器

因为数据源是mysql，所以我们加的是如下配置，如果是其他的数据源，替换成对应的依赖即可

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.18</version>
    </dependency>
</dependencies>

b. jdbc配置

这个配置很重要，连接数据库读取表结构信息

<jdbc>
    <!-- 数据库相关配置 -->
    <driver>com.mysql.cj.jdbc.Driver</driver>
    <url>jdbc:mysql://127.0.0.1:3306/test</url>
    <user>root</user>
    <password></password>
</jdbc>

c. generate配置

generate中指定jooq相关的一些配置

<database> 标签

指定数据库基本信息，如数据库名，哪些表需要自动生成对应的代码，哪些可以排除掉

<database>
    <!-- 数据库的基本信息 -->
    <name>org.jooq.meta.mysql.MySQLDatabase</name>
    <!-- 数据库名 -->
    <inputSchema>test</inputSchema>
    <!-- 所有的表都包含进来，用于自动生成代码 -->
    <includes>user|money</includes>
    <excludes></excludes>
</database>

如果我们希望数据库中所有的表都生成对应的代码，可以在include标签中填写.*；上面的写法表示 user, money这两张表需要生成相应的代码

pojos

下面这个非必要，因为默认生成DO对象包含较多的表结构信息，所以我们可以指定生成简单的对象，用于更友好的业务传递

<generate>
    <pojos>true</pojos>
</generate>

target

自动生成类的包名以及输出地址

<target>
    <!-- 自动生成的类的包名，以及路径 -->
    <packageName>com.git.hui.boot.jooq.dao</packageName>
    <directory>src/main/java</directory>
</target>

strategy

可以给生成的表名，实体名，pojo名指定特定的后缀

<strategy>
    <matchers>
        <tables>
            <table>
                <expression>^(.*)$</expression>
                <tableClass>
                    <!-- table的后缀为TB -->
                    <transform>PASCAL</transform>
                    <expression>$1_T_B</expression>
                </tableClass>
                <recordClass>
                    <!-- record的后缀为PO，表示实体类 -->
                    <transform>PASCAL</transform>
                    <expression>$1_P_O</expression>
                </recordClass>
                <pojoClass>
                    <!-- pojo后缀为BO，作为内部使用的简单对象-->
                    <transform>PASCAL</transform>
                    <expression>$1_B_O</expression>
                </pojoClass>
            </table>
        </tables>
    </matchers>
</strategy>

代码自动生成

执行maven插件在idea中比较简单了，如下图，执行完毕之后，就可以在上面定义的路径下看到生成的类

2. jar包生成

除了上面介绍的maven插件自动生成代码之外，还可以借助jooq-xx.jar来自动生成代码，最大的好处是不需要创建项目工程，可以直接使用

这种用法需要我们下载对应的jar包，有需要的小伙伴可以到官网去获取，同时我也传了一份到百度网盘，关注微信公众号：一灰灰blog之后，回复 jooq-code-gen 即可获取下载信息（我个人不太建议使用这种方式，maven插件是真香）

下面假设各位小伙伴已经get到了所需的信息，对应的jar包和启动脚本(deploy.sh)如下

最后一个参数是我们的配置文件, mysql.xml内容和上面的基本一致，没有太多的区别

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.9.0.xsd">
    <!-- Configure the database connection here -->
    <jdbc>
        <driver>com.mysql.jdbc.Driver</driver>
        <url>jdbc:mysql://127.0.0.1:3306/test</url>
        <user>root</user>
        <password></password>
    </jdbc>

    <generator>
        <!-- java -classpath jooq-3.12.1.jar;jooq-meta-3.12.1.jar;jooq-codegen-3.12.1.jar;reactive-streams-1.0.3.jar;mysql-connector-java-5.1.41.jar;. org.jooq.codegen.GenerationTool mysql.xml -->
        <!-- The default code generator. You can override this one, to generate your own code style.
             Supported generators:
             - org.jooq.util.JavaGenerator
             - org.jooq.util.ScalaGenerator
             Defaults to org.jooq.util.JavaGenerator -->
        <name>org.jooq.codegen.JavaGenerator</name>

        <database>
            <!-- The database marketType. The format here is:
                 org.util.[database].[database]Database -->
            <name>org.jooq.meta.mysql.MySQLDatabase</name>

            <!-- The database schema (or in the absence of schema support, in your RDBMS this
                 can be the owner, user, database name) to be generated -->
            <inputSchema>test</inputSchema>

            <!-- All elements that are generated from your schema
                 (A Java regular expression. Use the pipe to separate several expressions)
                 Watch out for case-sensitivity. Depending on your database, this might be important! -->
            <includes>
               user|money
            </includes>

            <!-- All elements that are excluded from your schema
                 (A Java regular expression. Use the pipe to separate several expressions).
                 Excludes match before includes, i.e. excludes have a higher priority -->
            <excludes></excludes>
        </database>

        <generate>
            <!-- Generation flags: See advanced configuration properties -->
            <pojos>true</pojos>
            <daos>false</daos>
        </generate>

        <target>
            <!-- The destination package of your generated classes (within the destination directory) -->
            <packageName>com.git.hui.boot.jooq.dao</packageName>

            <!-- The destination directory of your generated classes. Using Maven directory layout here -->
            <directory>../src/main/java</directory>
        </target>

        <strategy>
            <matchers>
                <tables>
                    <table>
                        <expression>^(.*)$</expression>
                        <tableClass>
                            <!-- table的后缀为TB -->
                            <transform>PASCAL</transform>
                            <expression>$1_T_B</expression>
                        </tableClass>
                        <recordClass>
                            <!-- record的后缀为PO，表示实体类 -->
                            <transform>PASCAL</transform>
                            <expression>$1_P_O</expression>
                        </recordClass>
                        <pojoClass>
                            <!-- pojo后缀为BO，作为内部使用的简单对象-->
                            <transform>PASCAL</transform>
                            <expression>$1_B_O</expression>
                        </pojoClass>
                    </table>
                </tables>
            </matchers>
        </strategy>
    </generator>
</configuration>

说明

上面的生成命令，适用于mac + linux操作系统，如果是widowns的童鞋，可以把-classpath参数中的冒号换成分号

3. 小结

本文主要介绍了jooq代码自动生成的两种方式，各自的优缺点比较明显

maven: 简单，简洁，高效，缺点是需要依托项目来执行
jar包: 独立运行，缺点是需要下载配套的jar包

II. 其他

0. 项目

系列博文

【SpringBoot DB系列】Jooq初体验

源码

工程：https://github.com/liuyueyi/spring-boot-demo
项目源码: https://github.com/liuyueyi/spring-boot-demo/tree/master/spring-boot/108-jooq-mysql

4.3 - 3.Jooq之insert

接下来我们开始进入jooq的增删改查的使用姿势系列，本篇将主要介绍如何利用jooq来实现添加数据

I. 项目搭建

本项目借助SpringBoot 2.2.1.RELEASE + maven 3.5.3 + IDEA进行开发

1. 项目依赖

关于如何创建一个SpringBoot的项目工程，不再本文的描述范围内，如有兴趣可以到文末的个人站点获取

在这个示例工程中，我们的选用h2dabase作为数据库（方便有兴趣的小伙伴直接获取工程源码之后，直接测试体验），因此对应的pom核心依赖如下

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jooq</artifactId>
    </dependency>
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
    </dependency>
</dependencies>

2. 数据库初始化

我们借助jooq-codegen-maven插件来自动生成数据库相关的代码，对这一段逻辑感兴趣的小伙伴可以参考博文：【DB系列】Jooq代码自动生成

后文中使用的表结构如下

DROP TABLE IF EXISTS poet;

CREATE TABLE poet (
  `id` int NOT NULL,
  `name` varchar(20) NOT NULL default '',
  CONSTRAINT pk_t_poet PRIMARY KEY (ID)
);

DROP TABLE IF EXISTS poetry;
CREATE TABLE poetry (
  `id` int NOT NULL,
  `poet_id` int NOT NULL default '0',
  `title` varchar(128) not null default '',
  `content` varchar(128) not null default '',
  CONSTRAINT pk_t_poetry PRIMARY KEY (ID)
);

3. 配置文件

h2database的连接配置如 application.properties

#Database Configuration
spring.datasource.url=jdbc:h2:~/h2-jooq-poet
spring.datasource.username=test
spring.datasource.password=
spring.datasource.driverClassName=org.h2.Driver


#jOOQ Configuration
spring.jooq.sql-dialect=H2


spring.datasource.initialization-mode=never
spring.datasource.continueOnError=true


##h2 web console设置
spring.datasource.platform=h2
#进行该配置后，h2 web consloe就可以在远程访问了。否则只能在本机访问。
spring.h2.console.settings.web-allow-others=true
#进行该配置，你就可以通过YOUR_URL/h2访问h2 web consloe
spring.h2.console.path=/h2
#进行该配置，程序开启时就会启动h2 web consloe
spring.h2.console.enabled=true

II. 新增记录

接下来我们进入正式的数据插入的使用姿势介绍，一般来说新增数据会区分单个和批量两种方式，下面我们分别进行介绍

1. Record实体类新增方式

在jooq中，借助自动生成的Record类来实现新增是最简单的case，如下


private static final PoetTB table = PoetTB.POET;
@Autowired
private DSLContext dsl;

/**
 * 新增记录
 *
 * @param id
 * @param name
 * @return
 */
public boolean save(int id, String name) {
    PoetPO record = dsl.newRecord(table);
    record.setId(id);
    record.setName(name);
    return record.insert() > 0;
}

注意:

实体类的创建方式：PoetPO record = dsl.newRecord(table);，不要直接new一个对象出来使用

2. 链式写法

下面介绍的这种写法和sql非常相似，也是我个人用的比较多的方式，特点就是一目了然

public boolean save2(int id, String name) {
    return dsl.insertInto(table).set(table.ID, id).set(table.NAME, name).execute() > 0;