SpringBoot2下的Mybatis初体验

2020-10-17 | 0 评论 | 0 浏览

背景

从零开始，使用Mybatis。SpringBoot2下的MyBatis使用注释的方式，使用更方便优雅一些。

One2Many初体验

一个One2Many的case。一个User属于一个Company，一个Company有多个User。

pom

使用SpringBoot2创建一个新的工程，生成的pom如下：

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.6.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>test</groupId>
    <artifactId>test-mybatis</artifactId>
    <version>1.0.0-SNAPSHOT</version>
    <name>test-mybatis</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.2</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

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

创建数据表

这里直接复用了文末参考中的建表语句。

用户表

CREATE TABLE IF NOT EXISTS `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `company_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `t_company` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `t_account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,  
  `name` varchar(200) NOT NULL,
  `user_id` int(11) NOT NULL, 
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO
    `t_user`
VALUES
    (1, 'aa', 1),
    (2, 'bb', 2);

INSERT INTO
    `t_company`
VALUES
    (1, 'xx公司'),
    (2, 'yy公司');

INSERT INTO
    `t_account`
VALUES
    (1, '中行', 1),
    (2, '工行', 1),
    (3, '中行', 2);

Bean/Domain层

@Data
public class User {
    private int id;
    private String name;
    private Company company;
    private List<Account> accounts;
}

@Data
public class Company {
    private Integer id;
    private String name;
}

@Data
public class Account {
    private Integer id;
    private String name;
}

DAO/Mapper层

@Mapper
public interface CompanyMapper {

    @Select("SELECT * FROM t_company WHERE id = #{id}")
    Company getCompanyById(Long id);
}

@Mapper
public interface AccountMapper {

    @Select("SELECT * FROM `t_account` WHERE id = #{id}")
    List<Account> getAccountByUserId(Long id);
}


@Mapper
public interface UserMapper {
    /*
     * 一对一查询
     * property：查询结果赋值给此实体属性
     * column：对应数据库的表字段，做为下面@One(select方法的查询参数
     * one：一对一的查询
     * @One(select = 方法全路径) ：调用的方法
     */
    @Select("SELECT * FROM t_user WHERE id = #{id}")
    @Results({
        @Result(property = "company", column = "company_id",
            one = @One(select = "test.testmybatis.mapper.CompanyMapper.getCompanyById"))
    })
    User getUserWithCompany(@Param("id") Long id);

    /*
     * 一对多查询
     * property：查询结果赋值给此实体属性
     * column：对应数据库的表字段，可做为下面@One(select方法)的查询参数
     * many：一对多的查询
     * @Many(select = 方法全路径) ：调用的方法
     */
    @Select("SELECT * FROM t_user WHERE id = #{id}")
    @Results({
        @Result(property = "id", column = "id"),//加此行，否则id值为空
        @Result(property = "accounts", column = "id",
            many = @Many(select = "test.testmybatis.mapper.AccountMapper.getAccountByUserId"))
    })
    User getUserWithAccount(@Param("id")Long id);

    /*
     * 同时用一对一、一对多查询
     */
    @Select("SELECT * FROM t_user")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "company", column = "company_id",
            one = @One(select = "test.testmybatis.mapper.CompanyMapper.getCompanyById")),
        @Result(property = "accounts", column = "id",
            many = @Many(select = "test.testmybatis.mapper.AccountMapper.getAccountByUserId"))
    })
    List<User> getAll();
}

Controller层


@RestController
public class UserController {

    @Autowired
    private UserMapper userMapper;

    @RequestMapping("/getUserWithCompany/{id}")
    public User getUserWithCompany(@PathVariable("id") Long id) {
        User user = userMapper.getUserWithCompany(id);
        return user;
    }

    @RequestMapping("/getUserWithAccount/{id}")
    public User getUserWithAccount(@PathVariable("id") Long id) {
        User user = userMapper.getUserWithAccount(id);
        return user;
    }

    @RequestMapping("/getUsers")
    public List<User> getUsers() {
        List<User> users = userMapper.getAll();
        return users;
    }
}

效果

getUsers

curl "http://localhost:8080/getUsers"
[{"id":1,"name":"aa","company":{"id":1,"name":"xx公司"},"accounts":[{"id":1,"name":"中行"}]},{"id":2,"name":"bb","company":{"id":2,"name":"yy公司"},"accounts":[{"id":2,"name":"工行"}]}]

getUserWithAccount

结果中没有 company，这个是符合预期的，见 UserMapper中的逻辑。

curl "http://localhost:8080/getUserWithAccount/1"
{"id":1,"name":"aa","company":null,"accounts":[{"id":1,"name":"中行"}]}

getUserWithCompany

结果中没有 accounts，这个也是符合预期的，同样见 UserMapper中的逻辑。

curl "http://localhost:8080/getUserWithCompany/1"
{"id":1,"name":"aa","company":{"id":1,"name":"xx公司"},"accounts":null}

Many2Many初体验(未完成)

建表

用户组表

CREATE TABLE `t_group` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(254) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO `t_group` VALUES ('1', 'Group-1');
INSERT INTO `t_group` VALUES ('2', 'Group-2');

用户组映射表

CREATE TABLE `user_group` (
  `user_id` int(10) unsigned NOT NULL DEFAULT '0',
  `group_id` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `user_group` VALUES ('1', '1');
INSERT INTO `user_group` VALUES ('2', '1');
INSERT INTO `user_group` VALUES ('1', '2');

Bean/Domain层

新的 User

@Data
public class User {
    private int id;
    private String name;
    private Company company;
    private List<Account> accounts;

    private List<Group> groups;
}

增加 Group

public class Group {
    private int id;
    private String name;
    private List<User> users;
}

参考

spring boot 2使用Mybatis多表关联查询