MyBatis的详细使用方法（二）

以下是在整合了SpringBoot的环境下编写的代码。

mapper详解

前面简单的实现了增删该查的基本方法，但还有一些常用的需要补充。

通过name查询User

    <select id="findByName" parameterType="java.lang.String" resultType="com.example.mybatis.entity.User">
        select * from user where name = #{name}
    </select>

多个参数，通过name和age查询User

    <select id="findByNameAndAge" resultType="com.example.mybatis.entity.User">
        select * from user where name = #{param1} and age = #{param2}
    </select>

如果你用RESTful风格的CRUD，在Controller中，你需要编写以下代码。

    @GetMapping("/findByNameAndAge/name/{name}/age/{age}")
    public User findByNameAndAge(@PathVariable String name, @PathVariable int age) {
        return userRepository.findByNameAndAge(name, age);
    }

但是参数多余两个或者参数中有中文，还是老老实实的用POST方法@RequestBody 一个对象当参数吧

及联查询

一对多

构建两张数据库表。

student：

DROP TABLE IF EXISTS student;
 
CREATE TABLE student
(
	id BIGINT(20) NOT NULL COMMENT '主键ID',
	name VARCHAR(30) NULL DEFAULT NULL COMMENT '姓名',
	cid INT(11) NULL DEFAULT NULL COMMENT '班级ID',
	PRIMARY KEY (id)
);

DELETE FROM student;
 
INSERT INTO student (id, name, cid) VALUES
(1, 'Jone', 1),
(2, 'Jack', 1),
(3, 'Tom', 2),
(4, 'Sandy', 2),
(5, 'Billie', 2);

classes:

DROP TABLE IF EXISTS classes;
 
CREATE TABLE classes
(
	id BIGINT(20) NOT NULL COMMENT '主键ID',
	name VARCHAR(30) NULL DEFAULT NULL COMMENT '班级名',
	PRIMARY KEY (id)
);

DELETE FROM classes;
 
INSERT INTO classes (id, name) VALUES
(1, '1班'),
(2, '2班'),
(3, '3班');

编写实体类

@Data
public class Student {

    private long id;
    private String name;
    private Classes classes;

}

@Data
public class Classes {

    private long id;
    private String name;
    private List<Student> students;

}

编写接口

public interface StudentRepository {

    public Student findById(long id);

}

编写mapper

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.mybatis.repository.StudentRepository">

    <select id="findById" parameterType="long" resultType="com.example.mybatis.entity.Student">
        select s.id,s.name,c.id,c.name from student s,classes c where s.id = #{id} and s.cid = c.id
    </select>

</mapper>

编写Controller

@RestController
public class StudentController {

    @Autowired
    private StudentRepository studentRepository;

    @GetMapping("/findById/{id}")
    public Student findById(@PathVariable long id) {
        return studentRepository.findById(id);
    }

}

测试运行

结果如下：

{
    "id": 1,
    "name": "Jone",
    "classes": null
}

直接运行SQL结果：
截屏20200325下午2.40.44.png

为什么 student和classe都有id和name，而返回结果恰好是student的id和name？这是由于mabatis映射是按顺序查找的，如果在sql中将student的id和name换成别名，则结果会变成classes的id和name

改写mapper

通过上面的测试结果可以看到 classes为空，在无法直接映射字段的时候，需要我们改写mapper，间接映射字段

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.mybatis.repository.StudentRepository">

    <resultMap id="studentMap" type="com.example.mybatis.entity.Student">
        <id column="id" property="id"></id>
        <result column="name" property="name"></result>
        <association property="classes" javaType="com.example.mybatis.entity.Classes">
            <id column="cid" property="id"></id>
            <result column="cname" property="name"></result>
        </association>
    </resultMap>
    
    <select id="findById" parameterType="long" resultMap="studentMap">
        select s.id,s.name,c.id as cid,c.name as cname from student s,classes c where s.id = #{id} and s.cid = c.id
    </select>

</mapper>

再次测试结果为：
截屏20200325下午2.56.38.png

截屏20200325下午2.58.06.png

用classes id来查询

其他步骤类似不再赘述，说以下mapper

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.mybatis.repository.ClassesRepository">

    <resultMap id="classesMap" type="com.example.mybatis.entity.Classes">
        <id column="cid" property="id"></id>
        <result column="cname" property="name"></result>
        <collection property="students" ofType="com.example.mybatis.entity.Student">
            <id column="id" property="id"></id>
            <result column="name" property="name"></result>
        </collection>
    </resultMap>
    
    <select id="findById" parameterType="long" resultMap="classesMap">
        select s.id,s.name,c.id as cid,c.name as cname from student s,classes c where c.id = #{id} and s.cid = c.id
    </select>

</mapper>

泛行用 collection 标签的 ofType属性

多对多

有了一对多，多对多就简单多了，因为它建立在一对多的基础上

构建数据库表

两张表，顾客和商品

截屏20200325下午4.10.44.png

截屏20200325下午3.48.09.png

商品和顾客就是典型的多对多关系，一个顾客可以买多个商品，一个商品可以被多个顾客购买

还需要一张从表，用来关联customer和goods

截屏20200325下午3.57.29.png

其他不再赘述，只说以下xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.mybatis.repository.CustomerRepository">

    <resultMap id="CustomerMap" type="com.example.mybatis.entity.Customer">
        <id column="cid" property="id"></id>
        <result column="cname" property="name"></result>
        <collection property="goods" ofType="com.example.mybatis.entity.Goods">
            <id column="gid" property="id"></id>
            <result column="gname" property="name"></result>
        </collection>
    </resultMap>
    
    <select id="findById" parameterType="long" resultMap="CustomerMap">
        select c.id cid,c.name cname,g.id gid,g.name gname from customer c,goods g,customer_goods cg where c.id = #{id} and c.id = cg.cid and g.id = cg.gid
    </select>

</mapper>