以下是在整合了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结果:
为什么 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>
再次测试结果为:
用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属性
多对多
有了一对多,多对多就简单多了,因为它建立在一对多的基础上
构建数据库表
两张表,顾客和商品
商品和顾客就是典型的多对多关系,一个顾客可以买多个商品,一个商品可以被多个顾客购买
还需要一张从表,用来关联customer和goods
其他不再赘述,只说以下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>
测试结果
通过id查询goods是完全一样的
有错误请及时指出
Q.E.D.