Spring -- MyBatis

Posted on | In , | Views:
| Symbols count in article: 15k | Reading time ≈ 28 mins.

概述

  1. MyBatis是一款优秀的持久层框架
  2. MyBatis支持定制化SQL、存储过程和高级映射
  3. JPA Or MyBatis
    • JPA：数据操作都比较简单
    • MyBatis：DBA需要对SQL进行审核，复杂SQL
  4. Spring + MyBatis
    • MyBatis Spring Adapter
    • MyBatis Spring-Boot-Starter

简单使用

pom.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>1.3.2</version>
</dependency>
<dependency>
    <groupId>org.joda</groupId>
    <artifactId>joda-money</artifactId>
    <version>LATEST</version>
</dependency>
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
</dependency>

schema.sql

1
2
3
4
5
6
7
8
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)
);

application.properties

1
2
mybatis.type-handlers-package=me.zhongmingmao.mybatis.handler
mybatis.configuration.map-underscore-to-camel-case=true

Coffee

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

MoneyTypeHandler

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
package me.zhongmingmao.mybatis.handler;

/**
 * 在 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);
    }
}

CoffeeMapper

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
package me.zhongmingmao.mybatis.mapper;

@Mapper
public interface CoffeeMapper {

    @Insert("insert into t_coffee (name, price, create_time, update_time) values (#{name}, #{price}, now(), now())")
    @Options(useGeneratedKeys = true)
    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
18
19
20
21
22
23
24
25
26
@Slf4j
@MapperScan("me.zhongmingmao.mybatis.mapper")
@SpringBootApplication
public class MybatisApplication implements ApplicationRunner {

    @Autowired
    private CoffeeMapper coffeeMapper;

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

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

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

        coffee = coffeeMapper.findById(coffee.getId());
        log.info("Find Coffee: {}", coffee);
    }
}
1
2
3
Save 1 Coffee: Coffee(id=1, name=espresso, price=CNY 20.00, createTime=null, updateTime=null)
Save 1 Coffee: Coffee(id=2, name=latte, price=CNY 25.00, createTime=null, updateTime=null)
Find Coffee: Coffee(id=2, name=latte, price=CNY 25.00, createTime=Sun Sep 15 13:50:32 CST 2019, updateTime=Sun Sep 15 13:50:32 CST 2019)

MyBatis Generator

  1. MyBatis代码生成器
  2. 根据数据表来生成相关代码
    • POJO
    • Mapper接口
    • SQL Map XML

pom.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.0</version>
</dependency>
<dependency>
    <groupId>org.mybatis.generator</groupId>
    <artifactId>mybatis-generator-core</artifactId>
    <version>1.3.7</version>
</dependency>
<dependency>
    <groupId>org.joda</groupId>
    <artifactId>joda-money</artifactId>
    <version>LATEST</version>
</dependency>
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
</dependency>

generatorConfig.xml

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
<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="org.h2.Driver"
                        connectionURL="jdbc:h2:mem:testdb"
                        userId="sa"
                        password="">
        </jdbcConnection>

        <javaModelGenerator targetPackage="me.zhongmingmao.mybatis.generator.model"
                            targetProject="./src/main/java">
            <property name="enableSubPackages" value="true"/>
            <property name="trimStrings" value="true"/>
        </javaModelGenerator>

        <sqlMapGenerator targetPackage="me.zhongmingmao.mybatis.generator.mapper"
                         targetProject="./src/main/resources/mapper">
            <property name="enableSubPackages" value="true"/>
        </sqlMapGenerator>

        <!-- 混合模式 -->
        <javaClientGenerator type="MIXEDMAPPER"
                             targetPackage="me.zhongmingmao.mybatis.generator.mapper"
                             targetProject="./src/main/java">
            <property name="enableSubPackages" value="true"/>
        </javaClientGenerator>

        <table tableName="t_coffee" domainObjectName="Coffee">
            <generatedKey column="id" sqlStatement="CALL IDENTITY()" identity="true"/>
            <columnOverride column="price" javaType="org.joda.money.Money" jdbcType="BIGINT"
                            typeHandler="me.zhongmingmao.mybatis.generator.handler.MoneyTypeHandler"/>
        </table>
    </context>
</generatorConfiguration>

目录结构

1
2
3
4
5
6
7
8
9
10
11
12
13
14
src/main
├── java
│   └── me
│       └── zhongmingmao
│           └── mybatis
│               └── generator
│                   ├── MybatisGeneratorApplication.java
│                   └── handler
│                       └── MoneyTypeHandler.java
└── resources
    ├── application.properties
    ├── generatorConfig.xml
    ├── mapper
    └── schema.sql

生成MyBatis样板代码

1
2
3
4
5
6
List<String> warnings = new ArrayList<>();
ConfigurationParser cp = new ConfigurationParser(warnings);
Configuration config = cp.parseConfiguration(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
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
src/main
├── java
│   └── me
│       └── zhongmingmao
│           └── mybatis
│               └── generator
│                   ├── MybatisGeneratorApplication.java
│                   ├── handler
│                   │   └── MoneyTypeHandler.java
│                   ├── mapper
│                   │   └── CoffeeMapper.java
│                   └── model
│                       ├── Coffee.java
│                       └── CoffeeExample.java
└── resources
    ├── application.properties
    ├── generatorConfig.xml
    ├── mapper
    │   └── me
    │       └── zhongmingmao
    │           └── mybatis
    │               └── generator
    │                   └── mapper
    │                       └── CoffeeMapper.xml
    └── schema.sql

application.properties

1
2
3
4
mybatis.mapper-locations=classpath*:/mapper/**/*.xml
mybatis.type-aliases-package=me.zhongmingmao.mybatis.generator.model
mybatis.type-handlers-package=me.zhongmingmao.mybatis.generator.handler
mybatis.configuration.map-underscore-to-camel-case=true

主程序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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");
example.setOrderByClause("id desc");
List<Coffee> list = coffeeMapper.selectByExample(example);
list.forEach(e -> log.info("selectByExample: {}", e));
1
2
Coffee Coffee [Hash = 981159997, id=1, name=espresso, price=CNY 20.00, createTime=Sun Sep 15 15:32:52 CST 2019, updateTime=Sun Sep 15 15:32:52 CST 2019]
selectByExample: Coffee [Hash = 653345773, id=2, name=latte, price=CNY 30.00, createTime=Sun Sep 15 15:32:52 CST 2019, updateTime=Sun Sep 15 15:32:52 CST 2019]

MyBatis PageHelper

  1. 支持多种数据库
  2. 支持多种分页方式
  3. SpringBoot支持（pagehelper-spring-boot-starter）

pom.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.0</version>
</dependency>
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.2.12</version>
</dependency>
<dependency>
    <groupId>org.joda</groupId>
    <artifactId>joda-money</artifactId>
    <version>LATEST</version>
</dependency>
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
</dependency>

data.sql

1
2
3
4
5
insert into t_coffee (name, price, create_time, update_time) values ('espresso', 2000, now(), now());
insert into t_coffee (name, price, create_time, update_time) values ('latte', 2500, now(), now());
insert into t_coffee (name, price, create_time, update_time) values ('capuccino', 2500, now(), now());
insert into t_coffee (name, price, create_time, update_time) values ('mocha', 3000, now(), now());
insert into t_coffee (name, price, create_time, update_time) values ('macchiato', 3000, now(), now());

CoffeeMapper

1
2
3
4
5
6
7
8
9
@Mapper
public interface CoffeeMapper {

    @Select("select * from t_coffee order by id")
    List<Coffee> findAllWithRowBounds(RowBounds rowBounds);

    @Select("select * from t_coffee order by id")
    List<Coffee> findAllWithParam(@Param("pageNum") int pageNum, @Param("pageSize") int pageSize);
}

application.properties

1
2
3
4
5
6
7
8
# mybatis
mybatis.type-handlers-package=me.zhongmingmao.mybatis.pagehelper.handler
mybatis.configuration.map-underscore-to-camel-case=true
# pagehelper
pagehelper.offset-as-page-num=true
pagehelper.reasonable=true
pagehelper.page-size-zero=true
pagehelper.support-methods-arguments=true

主程序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
coffeeMapper.findAllWithRowBounds(new RowBounds(1, 3))
        .forEach(coffee -> log.info("Page(1) Coffee {}", coffee));
coffeeMapper.findAllWithRowBounds(new RowBounds(2, 3))
        .forEach(coffee -> log.info("Page(2) Coffee {}", coffee));

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

// 获取所有记录
coffeeMapper.findAllWithRowBounds(new RowBounds(1, 0))
        .forEach(coffee -> log.info("Page(1) Coffee {}", coffee));

coffeeMapper.findAllWithParam(1, 3)
        .forEach(coffee -> log.info("Page(1) Coffee {}", coffee));
List<Coffee> list = coffeeMapper.findAllWithParam(2, 3);
PageInfo<Coffee> pageInfo = new PageInfo<>(list);
log.info("PageInfo: {}", pageInfo);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Page(1) Coffee Coffee(id=1, name=espresso, price=CNY 20.00, createTime=Sun Sep 15 16:12:50 CST 2019, updateTime=Sun Sep 15 16:12:50 CST 2019)
Page(1) Coffee Coffee(id=2, name=latte, price=CNY 25.00, createTime=Sun Sep 15 16:12:50 CST 2019, updateTime=Sun Sep 15 16:12:50 CST 2019)
Page(1) Coffee Coffee(id=3, name=capuccino, price=CNY 25.00, createTime=Sun Sep 15 16:12:50 CST 2019, updateTime=Sun Sep 15 16:12:50 CST 2019)
Page(2) Coffee Coffee(id=4, name=mocha, price=CNY 30.00, createTime=Sun Sep 15 16:12:50 CST 2019, updateTime=Sun Sep 15 16:12:50 CST 2019)
Page(2) Coffee Coffee(id=5, name=macchiato, price=CNY 30.00, createTime=Sun Sep 15 16:12:50 CST 2019, updateTime=Sun Sep 15 16:12:50 CST 2019)
==============
Page(1) Coffee Coffee(id=1, name=espresso, price=CNY 20.00, createTime=Sun Sep 15 16:12:50 CST 2019, updateTime=Sun Sep 15 16:12:50 CST 2019)
Page(1) Coffee Coffee(id=2, name=latte, price=CNY 25.00, createTime=Sun Sep 15 16:12:50 CST 2019, updateTime=Sun Sep 15 16:12:50 CST 2019)
Page(1) Coffee Coffee(id=3, name=capuccino, price=CNY 25.00, createTime=Sun Sep 15 16:12:50 CST 2019, updateTime=Sun Sep 15 16:12:50 CST 2019)
Page(1) Coffee Coffee(id=4, name=mocha, price=CNY 30.00, createTime=Sun Sep 15 16:12:50 CST 2019, updateTime=Sun Sep 15 16:12:50 CST 2019)
Page(1) Coffee Coffee(id=5, name=macchiato, price=CNY 30.00, createTime=Sun Sep 15 16:12:50 CST 2019, updateTime=Sun Sep 15 16:12:50 CST 2019)
Page(1) Coffee Coffee(id=1, name=espresso, price=CNY 20.00, createTime=Sun Sep 15 16:12:50 CST 2019, updateTime=Sun Sep 15 16:12:50 CST 2019)
Page(1) Coffee Coffee(id=2, name=latte, price=CNY 25.00, createTime=Sun Sep 15 16:12:50 CST 2019, updateTime=Sun Sep 15 16:12:50 CST 2019)
Page(1) Coffee Coffee(id=3, name=capuccino, price=CNY 25.00, createTime=Sun Sep 15 16:12:50 CST 2019, updateTime=Sun Sep 15 16:12:50 CST 2019)
PageInfo: PageInfo{pageNum=2, pageSize=3, size=2, startRow=4, endRow=5, total=5, pages=2, list=Page{count=true, pageNum=2, pageSize=3, startRow=3, endRow=6, total=5, pages=2, reasonable=true, pageSizeZero=true}[Coffee(id=4, name=mocha, price=CNY 30.00, createTime=Sun Sep 15 16:12:50 CST 2019, updateTime=Sun Sep 15 16:12:50 CST 2019), Coffee(id=5, name=macchiato, price=CNY 30.00, createTime=Sun Sep 15 16:12:50 CST 2019, updateTime=Sun Sep 15 16:12:50 CST 2019)], prePage=1, nextPage=0, isFirstPage=false, isLastPage=true, hasPreviousPage=true, hasNextPage=false, navigatePages=8, navigateFirstPage=1, navigateLastPage=2, navigatepageNums=[1, 2]}
Related Posts
0%