3.ORM实践

3.1 介绍Spring Data JPA

JPA（Java Persistence API）标准

• 2006年，JPA 1.0作为JSP 220的一部分正式发布
• JPA为对象关系映射提供了一种基于POJO的持久化模型
  • 简化持久化代码开发工作
  • 屏蔽不同持久化API的差异

Hibernate

• 一款开源的ORM（Object Relational Mapping）框架
• 屏蔽了底层数据库的各种细节
• 2006年，Hiberbate 3.2 实现 JPA 规范

Spring Data

Spring Data是Spring 的一个子项目。用于简化数据库访问，支持NoSQL和关系数据库存储。其主要目标是使数据库的访问变得方便快捷。

Spring Data 项目所支持NoSQL存储：

• Spring Data MongoDB（文档数据库）
• Spring Data Neo4j （图形数据库）
• Spring Data Redis（键/值存储）
• Spring Data Hbase（列族数据库）

Spring Data 项目所支持的关系数据存储技术：

• Spring Data JDBC
• Spring Data JPA

Spring Data JPA

Spring Data JPA是Spring Data大家庭的一部分，它使得那些以JPA接口为规范的应用更加方便， 致力于减少数据访问层（DAO）的开发量。

Spring Data JPA 底层默认的使用的是 Hibernate 来做的 JPA 实现。

引入依赖

Spring：

<dependency>
    <groupId>org.springframework.datagroupId>
    <artifactId>spring-data-releasetrainartifactId>
    <version>Lovelace-SR4version>
    <scope>importscope>
    <type>pomtype>
dependency>
1
2
3
4
5
6
7

SpringBoot：

<dependency>
	<groupId>org.springframework.bootgroupId>
	<artifactId>spring-boot-starter-data-jpaartifactId>
dependency>
1
2
3
4

3.2 定义JPA的实体对象

常用JPA注解

实体

• @Entity(name)：注明这个类是一个实体
• @Table(name) ：实体和对应表关联起来
• @MappedSuperclass ：在多个实体的父类上标注

主键

https://www.cnblogs.com/lich/archive/2011/11/29/2268253.html

• @Id
  • @GeneratedValue(strategy)
    • strategy：AUTO:自动选择合适的策略，IDENTITY:mysql数据库ID自增长的方式来生成主键值。默认是GenerationType.AUTO，就是说可以简单写成这样@GeneratedValue。

映射

• @Column(name, nullable, length, insertable, updatable)
• @JoinTable(name) 、@JoinColumn(name)：表的关联

关系

• @OneToOne 、@OneToMany 、@ManyToOne 、@ManyToMany
• @OrderBy

常用lombok注解

• @Data
  • @Getter
  • @Setter
  • @ToString
  • @RequiredArgsConstructor
• @NoArgsConstructor：如果存在没有被初始化的final属性，使用@NoArgsConstructor注解会报错

@NoArgsConstructor
pulic class Entity {
	private final int i;  // 未被初始化
}
1
2
3
4

• @RequiredArgsConstructor：生成一个由所有final与@NotNull属性组成的构造方法。

@Data // 包含@RequiredArgsConstructor
pulic class Entity {
	private final int i; 
	private final String name;
	private int age;
	
	// lombok生成的构造方法
	public Entity(int i, String name) {
		this.i = i;
	}
}
1
2
3
4
5
6
7
8
9
10
11

• @AllArgsConstructor
• @Builder：生成build方法

@Builder
public class Product {
    @Id
    private long id;

    public static void main(String[] args) {
        ProductBuilder builder = Product.builder();
        Product build = builder.id(111).build();
    }
}
1
2
3
4
5
6
7
8
9
10

• @Slf4j、@CommonsLog、@Log4j

3.3 SpringBucks线上咖啡馆实战项目

（1）项目目标

通过一个完整的例子演示Spring全家桶各主要成员的用法。

• 咖啡的菜单放到缓存中（redis）
• RabbitMQ
  

（2）项目中的对象实体

（1）实体：顾客、服务员、咖啡师、订单、咖啡
（2）实体之间的关系：

（3）订单生成及其状态机流转

（3）导入所需的依赖

<dependencies>
	<dependency>
		<groupId>org.springframework.bootgroupId>
		<artifactId>spring-boot-starter-data-jpaartifactId>
	dependency>
	
	<dependency>
		<groupId>org.jodagroupId>
		<artifactId>joda-moneyartifactId>   // 不用浮点数，用Money类定义金额，也便于处理货币单位，货币转换，汇率之类的问题
		<version>1.0.1version>
	dependency>
	
	<dependency>
		<groupId>org.jadira.usertypegroupId>
		<artifactId>usertype.coreartifactId>  // joda-money映射需要
		<version>6.0.1.GAversion>
	dependency>
	
	<dependency>
		<groupId>com.h2databasegroupId>
		<artifactId>h2artifactId>
		<scope>runtimescope>
	dependency>
	
	<dependency>
		<groupId>org.projectlombokgroupId>
		<artifactId>lombokartifactId>
		<optional>trueoptional>
	dependency>
	
	<dependency>
		<groupId>org.springframework.bootgroupId>
		<artifactId>spring-boot-starter-testartifactId>
		<scope>testscope>
	dependency>
dependencies>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

（4）application.properties

# 启动时检查表是否存在，存在则删除，然后创建表结构，结束之后删除
spring.jpa.hibernate.ddl-auto=create-drop
# 打印sql并格式化输出
spring.jpa.properties.hibernate.show_sql=true
spring.jpa.properties.hibernate.format_sql=true

# 默认的h2数据库
# spring.datasource.url=jdbc:h2:mem:testdb
# spring.datasource.username=sa
# spring.datasource.password=

# 数据库连接池
# spring.datasource.hikari.maximum-pool-size=5
# spring.datasource.hikari.minimum-idle=5
# spring.datasource.hikari.idleTimeout=600000
# spring.datasource.hikari.connectionTimeout=30000
# spring.datasource.hikari.maxLifetime=1800000

# 启用h2控制台
spring.h2.console.enabled=true
# h2控制台访问地址，http://localhost:8080/h2
spring.h2.console.path=/h2
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

（5）实体对象初定义

Coffee

@Entity
@Table(name = "T_MENU")
@Builder
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Coffee implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column
    private String name;

    @Column
    @Type(type = "org.jadira.usertype.moneyandcurrency.joda.PersistentMoneyAmount",
            parameters = {@org.hibernate.annotations.Parameter(name = "currencyCode", value = "CNY")})
    /* PersistentMoneyAmount，数据库类型为decimal(19,2)*/
    /* PersistentMoneyMinorAmount，数据库类型为bigint，20.0存为2000*/
    private Money price;

    @Column(updatable = false)
    @CreationTimestamp
    private Date createTime;

    @Column
    @UpdateTimestamp
    private Date updateTime;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

CoffeeOrder

@Entity
@Table(name = "T_ORDER")
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class CoffeeOrder implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column
    private String customer;

    @ManyToMany
    @JoinTable(name = "T_ORDER_COFFEE")
    private List<Coffee> items;

    @Column(nullable = false)
    private Integer state;

    @Column(updatable = false)
    @CreationTimestamp
    private Date createTime;

    @Column
    @UpdateTimestamp
    private Date updateTime;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

执行的SQL

create table t_menu (
   id bigintnot null,
    create_time timestamp,
    name varchar(255),
    price decimal(19,2),
    update_time timestamp,
    primary key (id)
)
1
2
3
4
5
6
7
8

create table t_order (
   	id bigintnot null,
	create_time timestamp,
    customer varchar(255),
    state integer not null,
    update_time timestamp,
    primary key (id)
)
1
2
3
4
5
6
7
8

create table t_order_coffee (
    coffee_order_id bigint not null,
	items_id bigint not null
)
1
2
3
4

alter table t_order_coffee add constraint FKj2swxd3y69u2tfvalju7sr07q 
foreign key (items_id) references t_menu
1
2

alter table t_order_coffee add constraint FK33ucji9dx64fyog6g17blpx9v 
foreign key (coffee_order_id) references t_order
1
2

（6）将实体共有属性抽出组成BaseEntity

@MappedSuperclass   // 实体类的父类
@Data
@NoArgsConstructor
@AllArgsConstructor
public class BaseEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(updatable = false)
    @CreationTimestamp
    private Date createTime;

    @UpdateTimestamp
    private Date updateTime;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

@Entity
@Table(name = "T_MENU")
@Data
@ToString(callSuper = true)  // 父类的属性也会打印
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class Coffee extends BaseEntity implements Serializable {
    private String name;

    @Type(type = "org.jadira.usertype.moneyandcurrency.joda.PersistentMoneyMinorAmount",
            parameters = {@org.hibernate.annotations.Parameter(name = "currencyCode", value = "CNY")})
    private Money price;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14

@Entity
@Table(name = "T_ORDER")
@Data
@Builder
@ToString(callSuper = true)
@NoArgsConstructor
@AllArgsConstructor
public class CofferOrder extends BaseEntity implements Serializable {

    private String customer;

    @ManyToMany
    @JoinTable(name = "T_ORDER_COFFEE")
    @OrderBy("id")
    private List<Coffee> items;

    @Enumerated
    @Column(nullable = false)
    private OrderState state;  // 会映射为数据库的integer类型
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

public enum OrderState {
    INIT, PAID, BREWING, BREWED, TAKEN, CANCELLED
}
1
2
3

（7）通过Spring Data JPA操作数据库

保存实体

• 启动类上加@EnableJpaRepositories ，自动发现CrudRepository等接口拓展
• 自定义接口实现Repository接口，泛型中指定实体对象和主键类型
  • CrudRepository，PagingAndSortingRepository，JpaRepository
    

public interface CoffeeOrderRepository extends CrudRepository<CoffeeOrder, Long> {
}
1
2

public interface CoffeeRepository extends CrudRepository<Coffee, Long> {
}
1
2

@SpringBootApplication
@Slf4j
@EnableJpaRepositories    // 自动发现CrudRepository等接口拓展
@RestController
public class SpringBucksApplication {
	@Autowired
	CoffeeRepository coffeeRepository;

	@Autowired
	CoffeeOrderRepository orderRepository;

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

	@RequestMapping("/initOrders")
	private void initOrders() {
		// 意式浓缩咖啡
		// CNY：Chinese yuan
		Coffee espresso = Coffee.builder().name("espresso") 
				.price(Money.of(CurrencyUnit.of("CNY"), 20.0))
				.build();
		coffeeRepository.save(espresso);
		// 在做完save操作之后会把生成的值赋到对象里
		log.info("Coffee: {}", espresso);
		
		// 拿铁本指牛奶，抹茶拿铁中没有咖啡
		// 意式拿铁：Espresso + 牛奶
		// 美式拿铁：Espresso + 牛奶 + 奶沫
		// 玛奇朵：Espresso + 奶沫
		Coffee latte = Coffee.builder().name("latte") 
				.price(Money.of(CurrencyUnit.of("CNY"), 30.0))
				.build();
		// 在做完save操作之后会把生成的值赋到对象里
		coffeeRepository.save(latte);
		log.info("Coffee: {}", latte);

		CoffeeOrder order = CoffeeOrder.builder()
				.customer("Li Lei")
				.items(Collections.singletonList(espresso))
				.state(OrderState.INIT)
				.build();
		// 在做完save操作之后会把生成的值赋到对象里
		orderRepository.save(order);
		log.info("Order: {}", order);

		order = CoffeeOrder.builder()
				.customer("Li Lei")
				.items(Arrays.asList(espresso, latte))
				.state(OrderState.INIT)
				.build();
		// 在做完save操作之后会把生成的值赋到对象里
		orderRepository.save(order);
		log.info("Order: {}", order);
	}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

查询实体

根据方法名定义查询

• find…By…，read…By…，query…By，get…By…
• count…By…
• …OrderBy…[Asc / Desc]
• And ，Or ， IgnoreCase
• Top，First ， Distinct

涉及到分页查询

• PagingAndSortingRepository
• Pageable ，Sort
• Slice ， Page

@NoRepositoryBean  // 告诉Spring不需要为BaseRepository创建一个Bean
public interface BaseRepository<T, Long> extends PagingAndSortingRepository<T, Long> {
    List<T> findTop3ByOrderByUpdateTimeDescIdAsc();
}
1
2
3
4

public interface CoffeeOrderRepository extends BaseRepository<CoffeeOrder, Long> {
    List<CoffeeOrder> findByCustomerOrderById(String customer);
    List<CoffeeOrder> findByItems_Name(String name);
}
1
2
3
4

public interface CoffeeRepository extends BaseRepository<Coffee, Long> {
}
1
2

@SpringBootApplication
@Slf4j
@EnableJpaRepositories // 自动发现CrudRepository等接口拓展
@RestController
public class SpringBucksApplication {
	@Autowired
	CoffeeRepository coffeeRepository;

	@Autowired
	CoffeeOrderRepository orderRepository;

	@Autowired
	DataSource dataSource;

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

	@RequestMapping("/test")
	@Transactional
	public String test() throws Exception {
		initOrders();
		findOrders();
		Connection();
		return "访问成功";
	}

	public void Connection() throws Exception{
		log.info("数据源： {}", dataSource.toString());
		Connection connection = dataSource.getConnection();
		log.info("数据源： {}", connection.toString());
	}

	public void initOrders() {
		Coffee espresso = Coffee.builder().name("espresso")
				.price(Money.of(CurrencyUnit.of("CNY"), 20.0))
				.build();
		coffeeRepository.save(espresso);
		log.info("Coffee: {}", espresso);

		Coffee latte = Coffee.builder().name("latte")
				.price(Money.of(CurrencyUnit.of("CNY"), 30.0))
				.build();
		coffeeRepository.save(latte);
		log.info("Coffee: {}", latte);

		Coffee macchiato = Coffee.builder().name("Macchiato")
				.price(Money.of(CurrencyUnit.of("CNY"), 35.0))
				.build();
		coffeeRepository.save(macchiato);
		log.info("Coffee: {}", macchiato);

		Coffee americano = Coffee.builder().name("Americano")
				.price(Money.of(CurrencyUnit.of("CNY"), 20.0))
				.build();
		coffeeRepository.save(americano);
		log.info("Coffee: {}", americano);

		CoffeeOrder order = CoffeeOrder.builder()
				.customer("Li Lei")
				.items(Collections.singletonList(espresso))
				.state(OrderState.INIT)
				.build();
		orderRepository.save(order);
		log.info("Order: {}", order);

		order = CoffeeOrder.builder()
				.customer("Li Lei")
				.items(Arrays.asList(espresso, latte))
				.state(OrderState.INIT)
				.build();
		orderRepository.save(order);
		log.info("Order: {}", order);
	}

	public void findOrders() throws InterruptedException {
		// （1）菜单：所有咖啡种类，按id做降序排列
		coffeeRepository
				.findAll(Sort.by(Sort.Direction.DESC, "id"))
				.forEach(c -> log.info("=====================Menu {}", c));

		// （2）最新出品：所有咖啡种类，更新时间降序，id升序，前3条
		List<Coffee> coffeeList = coffeeRepository.findTop3ByOrderByUpdateTimeDescIdAsc();
		log.info("=====================Top3 Coffee: {}", getName(coffeeList));

		// （3）根据用户名查询用户的所有订单
		List<CoffeeOrder> list = orderRepository.findByCustomerOrderById("Li Lei");
		log.info("=====================Li Lei的订单: {}", list);
		list.forEach(e -> log.info("=====================items" + e.getItems()));
	}

	public String getName(List<Coffee> list) {
		return list.stream().map(o -> o.getName().toString())
				.collect(Collectors.joining(","));
	}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96

（8）Spring Data JPA中Repository怎么从接口变成Bean？为什么方法可以不具体实现？

Repository Bean的创建

接口中的方法是如何被解释的

3.4 通过MyBatis操作数据库

MyBatis

• 一款优秀的持久层框架
• 支持定制化SQL、存储过程和高级映射
• JPA中SQL都是框架生成的，MyBatis中SQL都是手写的（也可以用工具生成SQL）。
• 如果SQL操作简单使用JPA，如果DBA对SQL有较高的把控要求、加了很多join、大厂用MyBatis。

引入依赖

• Spring

<dependency>
  	<groupId>org.mybatisgroupId>
	<artifactId>mybatisartifactId>
	<version>3.5.1version>
dependency>
<dependency>
	<groupId>org.mybatisgroupId>
	<artifactId>mybatis-springartifactId>
	<version>1.3.1version>
dependency>
1
2
3
4
5
6
7
8
9
10

• Spring-Boot

<dependency>
    <groupId>org.mybatis.spring.bootgroupId>
    <artifactId>mybatis-spring-boot-starterartifactId>
    <version>2.1.0version>
dependency>
1
2
3
4
5

简单配置

• mybatis.mapper-locations = classpath*:mapper/**/*.xml
• mybatis.type-aliases-package = com.example.entity
• mybatis.type-handlers-package = TypeHandler的包前缀：不用再指定TypeHandler
• mybatis.configuration.map-underscore-to-camel-case = true ：将带有下划线的表字段映射为驼峰格式的实体类属性，省去了在mapper.xml文件中编写表字段列表与表实体类属性的映射关系，即resultMap。

Mapper扫描、定义、使用

• @MapperScan(“com.mapper”)：加在启动类上配置Mapper接口扫描位置。也可以写个配置类，加在上面。

@Configuration
@MapperScan("com.mapper")
public class MapperConfig {

}
1
2
3
4
5

• @Mapper：加在定义的Mapper接口上面
• 映射的定义：
  • @MapperScan配置下的@Mapper接口
  • mybatis.mapper-locations目录下的xml文件

Mapper接口示例

• @Options的作用是在执行完sql之后把生成的id回填到Coffee对象中
• insert、update、delete方法返回的是影响的条数
• @Results指定结果集的映射

@Mapper
public interface CoffeeMapper {
    @Insert("insert into t_coffee (name, price, create_time, update_time)"
            + "values (#{name}, #{price}, now(), now())")
    @Options(useGeneratedKeys = true, keyColumn = "id", keyProperty = "id")
    int save(Coffee coffee);

    @Select("select * from t_coffee where id = #{id}") 
    @Results({
            // 默认会根据名字映射
            @Result(id = true, column = "id", property = "id"),  // 主键
            @Result(column = "create_time", property = "createTime"),
            // map-underscore-to-camel-case = true 可以实现一样的效果
            // @Result(column = "update_time", property = "updateTime"),
    })
    Coffee findById(@Param("id") Long id);
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

3.5 Mybatis实例

（1）引入依赖

<dependency>
	<groupId>org.springframework.bootgroupId>
	<artifactId>spring-boot-starterartifactId>
dependency>

<dependency>
	<groupId>org.mybatis.spring.bootgroupId>
	<artifactId>mybatis-spring-boot-starterartifactId>
	<version>1.3.2version>
dependency>

<dependency>
	<groupId>org.jodagroupId>
	<artifactId>joda-moneyartifactId>
	<version>LATESTversion>
dependency>

<dependency>
	<groupId>com.h2databasegroupId>
	<artifactId>h2artifactId>
	<scope>runtimescope>
dependency>
<dependency>
	<groupId>org.projectlombokgroupId>
	<artifactId>lombokartifactId>
	<optional>trueoptional>
dependency>
<dependency>
	<groupId>org.springframework.bootgroupId>
	<artifactId>spring-boot-starter-testartifactId>
	<scope>testscope>
dependency>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

（2）schema.sql建表

create table t_coffee (
    id bigint not null auto_increment,
    name varchar(255),
    price bigint not null,
    create_time timestamp,
    update_time timestamp,
    primary key (id)
);
1
2
3
4
5
6
7
8

（3）配置文件

# 不用指定TypeHandler
mybatis.type-handlers-package=geektime.spring.data.mybatisdemo.handler
mybatis.configuration.map-underscore-to-camel-case=true
1
2
3

（4）实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class Coffee {
    private Long id;
    private String name;
    private Money price;
    private Date createTime;
    private Date updateTime;
}
1
2
3
4
5
6
7
8
9
10
11

（5）Mapper接口

@Mapper
public interface CoffeeMapper {
    @Insert("insert into t_coffee (name, price, create_time, update_time)"
            + "values (#{name}, #{price}, now(), now())")
    // 因为配置了mybatis.type-handlers-package，所以不用指定typeHandler
    // #{price, typeHandler=geektime.spring.data.mybatisdemo.handler.MoneyTypeHandler}
    @Options(useGeneratedKeys = true, keyColumn="id", keyProperty="id")
    int save(Coffee coffee);
	
    @Select("select * from t_coffee where id = #{id}")
    @Results({
            // 默认会根据名字映射
            // map-underscore-to-camel-case = true 可以实现一样的效果
            // 因为配置了mybatis.type-handlers-package，所以不用指定typeHandler
            @Result(id = true, column = "id", property = "id"),
            @Result(column = "create_time", property = "createTime"),
            @Result(column = "update_time", property = "updateTime")
            // 因为配置了mybatis.type-handlers-package，所以不用指定typeHandler
            // @Result(column = "price", property = "price", typeHandler = geektime.spring.data.mybatisdemo.handler.MoneyTypeHandler.class),
    })
    Coffee findById(@Param("id") Long id);
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

（6）自定义TypeHandler

/**
 * 在 Money 与 Long 之间转换的 TypeHandler，处理 CNY 人民币
 */
public class MoneyTypeHandler extends BaseTypeHandler<Money> {
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, Money parameter, JdbcType jdbcType) throws SQLException {
    	// 获得一个代表分的金额
        ps.setLong(i, parameter.getAmountMinorLong());
    }

    @Override
    public Money getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return parseMoney(rs.getLong(columnName));
    }

    @Override
    public Money getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return parseMoney(rs.getLong(columnIndex));
    }

    @Override
    public Money getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return parseMoney(cs.getLong(columnIndex));
    }

    private Money parseMoney(Long value) {
    	// 转成人民币类型
    	return Money.ofMinor(CurrencyUnit.of("CNY"), value);
        // return Money.of(CurrencyUnit.of("CNY"), value / 100.0);
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

（7）使用Mapper保存和查询数据

@SpringBootApplication
@Slf4j
@MapperScan("geektime.spring.data.mybatisdemo.mapper")
public class MybatisDemoApplication implements ApplicationRunner {
	@Autowired
	private CoffeeMapper coffeeMapper;

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

	@Override
	public void run(ApplicationArguments args) throws Exception {
		Coffee c = Coffee.builder().name("espresso")
				.price(Money.of(CurrencyUnit.of("CNY"), 20.0)).build();
		int count = coffeeMapper.save(c);
		log.info("Save {} Coffee: {}", count, c);

		c = Coffee.builder().name("latte")
				.price(Money.of(CurrencyUnit.of("CNY"), 25.0)).build();
		count = coffeeMapper.save(c);
		log.info("Save {} Coffee: {}", count, c);

		c = coffeeMapper.findById(c.getId());
		log.info("Find Coffee: {}", c);
	}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

3.6 让MyBatis更好用的工具

MyBatis Generator介绍

• MyBatis官方提供的一个生成器
• 根据数据库表自动生成 POJO、Mapper接口、SQL Map XML

使用MyBatis Generator

配置generatorConfig.xml 结构

• 有顺序
• generatorConfiguration
  • context
    • plugin：内置插件，生成POJO时自带Builder、toString方法、Serializable、分页
    • jdbcConnection：JDBC连接的信息
    • javaModelGenerator：Model生成配置
    • sqlMapGenerator：Mapper生成配置
    • javaClientGenerator：选择通过注解（简单的）或者XML（有Example参与的）配置
    • table：给哪些表生成映射

<generatorConfiguration>
    <context id="H2Tables" targetRuntime="MyBatis3">
        <plugin type="org.mybatis.generator.plugins.FluentBuilderMethodsPlugin" />
        <plugin type="org.mybatis.generator.plugins.ToStringPlugin" />
        <plugin type="org.mybatis.generator.plugins.SerializablePlugin" />
        <plugin type="org.mybatis.generator.plugins.RowBoundsPlugin" />

        <jdbcConnection driverClass="com.mysql.cj.jdbc.Driver"
                        connectionURL="jdbc:mysql://127.0.0.1:3306/cpmp?characterEncoding=utf8&autoReconnect=true&serverTimezone=PRC"
                        userId="root"
                        password="123456">
        jdbcConnection>

        <javaModelGenerator targetPackage="geektime.spring.data.mybatis.model"
                            targetProject="./src/main/java">
            <property name="enableSubPackages" value="true" />
            <property name="trimStrings" value="true" />
        javaModelGenerator>

        <sqlMapGenerator targetPackage="geektime.spring.data.mybatis.mapper"
                         targetProject="./src/main/resources/mapper">
            <property name="enableSubPackages" value="true" />
        sqlMapGenerator>

        <javaClientGenerator type="MIXEDMAPPER"  # MIXEDMAPPER, ANNOTATEDMAPPER, XMLMAPPER
                             targetPackage="geektime.spring.data.mybatis.mapper"
                             targetProject="./src/main/java">
            <property name="enableSubPackages" value="true" />
        javaClientGenerator>

        <table tableName="t_coffee" domainObjectName="Coffee" >
            <columnOverride column="price" javaType="org.joda.money.Money" jdbcType="BIGINT"
                            typeHandler="geektime.spring.data.mybatis.handler.MoneyTypeHandler"/>
        table>
        
        <table tableName="t_order" domainObjectName="Order" >
        table>
        
        <table tableName="t_coffee_order" domainObjectName="CoffeeOrder" >
        table>
    context>
generatorConfiguration>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

（1）命令行

官网（https://mvnrepository.com/）下载 mybatis-generator-core-1.3.7.jar，mysql-connector-java-8.0.19.jar。

java -cp mybatis-generator-core-1.3.7.jar;mysql-connector-java-8.0.19.jar org.mybatis.generator.api.ShellRunner -configfile generatorConfig.xml
1

DOCTYPE generatorConfiguration
        PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">

<generatorConfiguration>
    <context id="H2Tables" targetRuntime="MyBatis3">
        <plugin type="org.mybatis.generator.plugins.FluentBuilderMethodsPlugin" />
        <plugin type="org.mybatis.generator.plugins.ToStringPlugin" />
        <plugin type="org.mybatis.generator.plugins.SerializablePlugin" />
        <plugin type="org.mybatis.generator.plugins.RowBoundsPlugin" />

		
		
        <jdbcConnection driverClass="com.mysql.cj.jdbc.Driver"
                        connectionURL="jdbc:mysql://127.0.0.1:3306/learnjdbc?serverTimezone=GMT&useUnicode=true&characterEncoding=UTF-8"
                        userId="root"
                        password="123456">
        jdbcConnection>

        <javaModelGenerator targetPackage="model"
                            targetProject="./">
            <property name="enableSubPackages" value="true" />
            <property name="trimStrings" value="true" />
        javaModelGenerator>

        <sqlMapGenerator targetPackage="mapper1"
                         targetProject="./">
            <property name="enableSubPackages" value="true" />
        sqlMapGenerator>

        <javaClientGenerator type="MIXEDMAPPER"
                             targetPackage="mapper2"
                             targetProject="./">
            <property name="enableSubPackages" value="true" />
        javaClientGenerator>

        <table tableName="t_coffee" domainObjectName="Coffee" >
            <columnOverride column="price" javaType="org.joda.money.Money" jdbcType="BIGINT"
                            typeHandler="geektime.spring.data.mybatis.handler.MoneyTypeHandler"/>
        table>
        <table tableName="t_order" domainObjectName="Order" >
        table>
        <table tableName="t_coffee_order" domainObjectName="CoffeeOrder" >
        table>
    context>
generatorConfiguration>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

（2）Maven Pugin（mybatis-generator-maven-plugin）

<plugin>
	<groupId>org.mybatis.generatorgroupId>
	<artifactId>mybatis-generator-maven-pluginartifactId>
	<version>1.3.7version>
	<configuration>
		<configurationFile>src/main/resources/generatorConfig.xmlconfigurationFile>
		<verbose>trueverbose>
		<overwrite>trueoverwrite>
	configuration>
	
	<dependencies>
		<dependency>
			<groupId>org.mybatis.generatorgroupId>
			<artifactId>mybatis-generator-coreartifactId>
			<version>1.3.7version>
		dependency>
		<dependency>
			<groupId>mysqlgroupId>
			<artifactId>mysql-connector-javaartifactId>
			<version>8.0.19version>
		dependency>
	dependencies>
plugin>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

在idea右侧maven标签中的Plugins中运行mybatis-generator即可生成相对文件

（3）Java程序

private void generateArtifacts() throws Exception {
	List<String> warnings = new ArrayList<>();
	ConfigurationParser cp = new ConfigurationParser(warnings);
	Configuration config = cp.parseConfiguration(
			this.getClass().getResourceAsStream("/generatorConfig.xml"));
	DefaultShellCallback callback = new DefaultShellCallback(true);
	MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config, callback, warnings);
	myBatisGenerator.generate(null);
}
1
2
3
4
5
6
7
8
9

生成的文件

• 实体类 + Example实体类
  
• Mapper接口
  
• Mapper的xml文件
  

使用生成的文件

• 简单操作：使用Mapper + 实体类
• 复杂查询：使用Mapper + Example实体类

Coffee espresso = new Coffee()
		.withName("espresso")
		.withPrice(Money.of(CurrencyUnit.of("CNY"), 20.0))
		.withCreateTime(new Date())
		.withUpdateTime(new Date());
coffeeMapper.insert(espresso);

Coffee latte = new Coffee()
		.withName("latte")
		.withPrice(Money.of(CurrencyUnit.of("CNY"), 30.0))
		.withCreateTime(new Date())
		.withUpdateTime(new Date());
coffeeMapper.insert(latte);

Coffee s = coffeeMapper.selectByPrimaryKey(1L);
log.info("Coffee {}", s);

CoffeeExample example = new CoffeeExample();
example.createCriteria().andNameEqualTo("latte");
List<Coffee> list = coffeeMapper.selectByExample(example);
list.forEach(e -> log.info("selectByExample: {}", e));
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

实例

（1）引入依赖

<dependencies>
	
	<dependency>
		<groupId>org.mybatis.spring.bootgroupId>
		<artifactId>mybatis-spring-boot-starterartifactId>
		<version>2.0.0version>
	dependency>
	
	<dependency>
		<groupId>org.mybatis.generatorgroupId>
		<artifactId>mybatis-generator-coreartifactId>
		<version>1.3.7version>
	dependency>
	<dependency>
		<groupId>org.jodagroupId>
		<artifactId>joda-moneyartifactId>
		<version>1.0.1version>
	dependency>
	<dependency>
		<groupId>mysqlgroupId>
		<artifactId>mysql-connector-javaartifactId>
		<version>8.0.19version>
	dependency>
	<dependency>
		<groupId>org.projectlombokgroupId>
		<artifactId>lombokartifactId>
		<optional>trueoptional>
	dependency>
	<dependency>
		<groupId>org.springframework.bootgroupId>
		<artifactId>spring-boot-starter-testartifactId>
		<scope>testscope>
	dependency>
	<dependency>
		<groupId>org.springframework.bootgroupId>
		<artifactId>spring-boot-starter-webartifactId>
	dependency>
dependencies>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

（2）创建表

create table t_coffee (
    id bigint not null auto_increment,
    name varchar(255),
    price bigint not null,
    create_time timestamp,
    update_time timestamp,
    primary key (id)
);


create table t_order (
    id bigint not null auto_increment,
    create_time timestamp,
    customer varchar(255),
    state integer not null,
    update_time timestamp,
    primary key (id)
);

create table t_coffee_order (
    order_id bigint not null,
    coffee_id bigint not null
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

（3）generatorConfig.xml

DOCTYPE generatorConfiguration
        PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">

<generatorConfiguration>
    <context id="H2Tables" targetRuntime="MyBatis3">
        <plugin type="org.mybatis.generator.plugins.FluentBuilderMethodsPlugin" />
        <plugin type="org.mybatis.generator.plugins.ToStringPlugin" />
        <plugin type="org.mybatis.generator.plugins.SerializablePlugin" />
        <plugin type="org.mybatis.generator.plugins.RowBoundsPlugin" />

        <jdbcConnection driverClass="com.mysql.cj.jdbc.Driver"
                        connectionURL="jdbc:mysql://127.0.0.1:3306/learnjdbc?serverTimezone=GMT&useUnicode=true&characterEncoding=UTF-8"
                        userId="root"
                        password="123456">
        jdbcConnection>

        <javaModelGenerator targetPackage="geektime.spring.data.mybatis.model"
                            targetProject="./src/main/java">
            <property name="enableSubPackages" value="true" />
            <property name="trimStrings" value="true" />
        javaModelGenerator>

        <sqlMapGenerator targetPackage="geektime.spring.data.mybatis.mapper"
                         targetProject="./src/main/resources/mapper">
            <property name="enableSubPackages" value="true" />
        sqlMapGenerator>

        <javaClientGenerator type="MIXEDMAPPER"
                             targetPackage="geektime.spring.data.mybatis.mapper"
                             targetProject="./src/main/java">
            <property name="enableSubPackages" value="true" />
        javaClientGenerator>

        <table tableName="t_coffee" domainObjectName="Coffee" >
            <columnOverride column="price" javaType="org.joda.money.Money" jdbcType="BIGINT"
                            typeHandler="geektime.spring.data.mybatis.handler.MoneyTypeHandler"/>
        table>
        
        <table tableName="t_order" domainObjectName="Order" >
        table>
        
        <table tableName="t_coffee_order" domainObjectName="CoffeeOrder" >
        table>
    context>
generatorConfiguration>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

（4）调用

@SpringBootApplication
@Slf4j
@MapperScan("geektime.spring.data.mybatis.mapper")
public class MybatisGeneratorDemoApplication implements ApplicationRunner {
	@Autowired
	private CoffeeMapper coffeeMapper;

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

	@Override
	public void run(ApplicationArguments args) throws Exception {
		generateArtifacts();
		playWithArtifacts();
	}

	private void generateArtifacts() throws Exception {
		List<String> warnings = new ArrayList<>();
		ConfigurationParser cp = new ConfigurationParser(warnings);
		Configuration config = cp.parseConfiguration(
				this.getClass().getResourceAsStream("/generatorConfig.xml"));
		DefaultShellCallback callback = new DefaultShellCallback(true);
		MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config, callback, warnings);
		myBatisGenerator.generate(null);
	}

	private void playWithArtifacts() {
		Coffee espresso = new Coffee()
				.withName("espresso")
				.withPrice(Money.of(CurrencyUnit.of("CNY"), 20.0))
				.withCreateTime(new Date())
				.withUpdateTime(new Date());
		coffeeMapper.insert(espresso);

		Coffee latte = new Coffee()
				.withName("latte")
				.withPrice(Money.of(CurrencyUnit.of("CNY"), 30.0))
				.withCreateTime(new Date())
				.withUpdateTime(new Date());
		coffeeMapper.insert(latte);

		Order hanfei_order1 = new Order()
				.withCustomer("HanFei")
				.withState(1)
				.withCreateTime(new Date())
				.withUpdateTime(new Date());
		orderMapper.insert(hanfei_order1);

		Order zhujiahua_order1 = new Order()
				.withCustomer("ZhuJiaHua")
				.withState(1)
				.withCreateTime(new Date())
				.withUpdateTime(new Date());
		orderMapper.insert(zhujiahua_order1);

		CoffeeExample example1 = new CoffeeExample();
		example1.createCriteria().andNameEqualTo("espresso");
		example1.setOrderByClause("id desc");
		List<Coffee> coffees = coffeeMapper.selectByExample(example1);
		System.out.println(coffees);
		coffees.forEach(re -> log.info("============Coffee: {}", re));
	}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64

（5）将自动生成的和手写的分开放

@Mapper
public interface MyCofferMapper {

    @Select("select * from t_coffee where id = #{id}")
    @Results({
            // 默认会根据名字映射
            // map-underscore-to-camel-case = true 可以实现一样的效果
            @Result(id = true, column = "id", property = "id"),
            @Result(column = "create_time", property = "createTime"),
            @Result(column = "update_time", property = "updateTime"),
            @Result(column = "price", property = "price", typeHandler = geektime.spring.data.mybatis.handler.MoneyTypeHandler.class),
    })
    Coffee findById(@Param("id") Long id);
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14

Coffee myCofferMapperById = myCofferMapper.findById(1L);
log.info("==========myCofferMapperById: {}" , myCofferMapperById);
1
2

MyBatis PageHelper

• 支持多种数据库
• 支持多种分页方式
• SpringBoot支持
  • pagehelper-spring-boot-starter

引入依赖

<dependency>
	<groupId>com.github.pagehelpergroupId>
	<artifactId>pagehelper-spring-boot-starterartifactId>
	<version>1.2.10version>
dependency>
1
2
3
4
5

application.properties

# 使用RowBounds里面的offset作为页码使用
pagehelper.offset-as-page-num=true
# 页码小于零时
# pagehelper.reasonable=true
# PageSize为零时查找所有记录
pagehelper.page-size-zero=true
pagehelper.support-methods-arguments=true
1
2
3
4
5
6
7

常用的分页方式

@Mapper
public interface CoffeeMapper {
    @Select("select * from t_coffee order by id")
    // RowBounds方式的调用
    List<Coffee> findAllWithRowBounds(RowBounds rowBounds);

    @Select("select * from t_coffee order by id")
    // 参数方法调用
    List<Coffee> findAllWithParam(@Param("pageNum") int pageNum,
                                  @Param("pageSize") int pageSize);
}
1
2
3
4
5
6
7
8
9
10
11

@SpringBootApplication
@Slf4j
@MapperScan("geektime.spring.data.mybatisdemo.mapper")
public class MybatisDemoApplication implements ApplicationRunner {
	@Autowired
	private CoffeeMapper coffeeMapper;

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

	@Override
	public void run(ApplicationArguments args) throws Exception {
		coffeeMapper.findAllWithRowBounds(new RowBounds(1, 3))
				.forEach(c -> log.info("Page(1) Coffee {}", c));
		coffeeMapper.findAllWithRowBounds(new RowBounds(2, 3))
				.forEach(c -> log.info("Page(2) Coffee {}", c));

		log.info("==================================================================");

		coffeeMapper.findAllWithRowBounds(new RowBounds(1, 0))
				.forEach(c -> log.info("Page(1) Coffee {}", c));

		log.info("==================================================================");

		coffeeMapper.findAllWithParam(1, 3)
				.forEach(c -> log.info("Page(1) Coffee {}", c));

		log.info("==================================================================");

		List<Coffee> list = coffeeMapper.findAllWithParam(2, 3);
		PageInfo page = new PageInfo(list);
		log.info("PageInfo: {}", page);
	}
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

3.7 项目小结

配置文件

# 将hibernate的ddl功能关上,通过schema.sql初始化
spring.jpa.hibernate.ddl-auto=none
spring.jpa.properties.hibernate.show_sql=true
spring.jpa.properties.hibernate.format_sql=true

# 默认的h2数据库
spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.username=sa
spring.datasource.password=123456

# 启用h2控制台
spring.h2.console.enabled=true
# h2控制台访问地址，http://localhost:8080/h2
spring.h2.console.path=/h2
1
2
3
4
5
6
7
8
9
10
11
12
13
14

调用Repository接口

CoffeeOrderService

@Slf4j
@Service
@Transactional
public class CoffeeOrderService {
    @Autowired
    private CoffeeOrderRepository orderRepository;

    /* 创建Order */
    public CoffeeOrder createOrder(String customer, Coffee...coffee) {
        CoffeeOrder order = CoffeeOrder.builder()
                .customer(customer)
                .items(new ArrayList<>(Arrays.asList(coffee)))
                .state(OrderState.INIT)
                .build();
        CoffeeOrder saved = orderRepository.save(order);
        log.info("==================New Order: {}", saved);
        return saved;
    }

    /* 更新Order的状态 */
    public boolean updateState(CoffeeOrder order, OrderState state) {
        if (state.compareTo(order.getState()) <= 0) {
            log.warn("=================Wrong State order: {}, {}", state, order.getState());
            return false;
        }
        order.setState(state);
        orderRepository.save(order);
        log.info("====================Updated Order: {}", order);
        return true;
    }
    
    /* 根据用户名查询所有Order */
    public List<CoffeeOrder> selectOrder(String customer) {
        List<CoffeeOrder> coffeeOrders = orderRepository.findAll(Example.of(CoffeeOrder.builder().customer(customer).build()));
        return coffeeOrders;
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

CoffeeService

@Slf4j
@Service
public class CoffeeService {
    @Autowired
    private CoffeeRepository coffeeRepository;

	/* 根据咖啡名查询 */
    public Optional<Coffee> findOneCoffee(String name) {
        Optional<Coffee> coffee1 = coffeeRepository.findOne(
                Example.of(Coffee.builder().name(name).build()));
        log.info("===================Coffee Found: {}", coffee1);

		/* 完全匹配，忽略大小写 */
        ExampleMatcher matcher = ExampleMatcher.matching()
                .withMatcher("name", exact().ignoreCase());
        Optional<Coffee> coffee2 = coffeeRepository.findOne(
                Example.of(Coffee.builder().name(name).build(), matcher));
        log.info("===================Coffee Found: {}", coffee2);
        return coffee2;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

SpringBucksApplication

@Slf4j
@EnableTransactionManagement
@SpringBootApplication
@EnableJpaRepositories
public class SpringBucksApplication implements ApplicationRunner {
	@Autowired
	private CoffeeRepository coffeeRepository;
	@Autowired
	private CoffeeService coffeeService;
	@Autowired
	private CoffeeOrderService orderService;

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

	@Override
	public void run(ApplicationArguments args) throws Exception {
		log.info("===================All Coffee: {}", coffeeRepository.findAll());

		Optional<Coffee> latte = coffeeService.findOneCoffee("Latte");
		if (latte.isPresent()) {
			CoffeeOrder order1 = orderService.createOrder("Li Lei", latte.get());
			CoffeeOrder order2 = orderService.createOrder("Li Lei", latte.get());
			log.info("===================Update INIT to PAID: {}", orderService.updateState(order2, OrderState.PAID));
			log.info("===================Update PAID to INIT: {}", orderService.updateState(order2, OrderState.INIT));
			List<CoffeeOrder> coffeeOrders = orderService.selectOrder("Li Lei");
			// 开启及时加载：@ManyToMany(fetch = FetchType.EAGER)，否则会报错
			log.info("================================CoffeeOrders" + coffeeOrders);
		}
	}
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

注意

CoffeeOrder

CoffeeOrder的items属性设为FetchType.EAGER，及时加载。

@Entity
@Table(name = "T_ORDER")
@Data
@ToString(callSuper = true)
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class CoffeeOrder extends BaseEntity implements Serializable {
    private String customer;
    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(name = "T_ORDER_COFFEE")
    @OrderBy("id")
    private List<Coffee> items;
    @Enumerated
    @Column(nullable = false)
    private OrderState state;
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18