文章目录
一对一关联
环境
USE mybatis;
CREATE TABLE IF NOT EXISTS boy (
bid INT NOT NULL AUTO_INCREMENT,
bname VARCHAR(50) NOT NULL,
PRIMARY KEY (bid)
);
CREATE TABLE IF NOT EXISTS girl (
gid INT NOT NULL AUTO_INCREMENT,
gname VARCHAR(50) NOT NULL,
bbid INT UNIQUE,
PRIMARY KEY (gid),
CONSTRAINT fk_girl_boy FOREIGN KEY (bbid) REFERENCES boy(bid)
);
INSERT INTO boy VALUES(NULL, '周杰伦'), (NULL, '张杰');
INSERT INTO girl VALUES(NULL, '昆凌', 1), (NULL, '谢娜', 2);
实体类
public class Boy implements Serializable {
private static final long serialVersionUID = 1589521560109475017L;
private Integer bid;
private String bname;
private Girl girl;
}
public class Girl implements Serializable {
private static final long serialVersionUID = 1611265720249960439L;
private Integer gid;
private String gname;
private Integer bbid;
private Boy boy;
}
BoyMapper
public interface BoyMapper {
Boy selectByBid(Integer bid);
}
非延迟加载(复杂sql)
第一种配置:没有使用automapping和延迟加载的association标签
<mapper namespace="org.westos.mapper.BoyMapper">
<resultMap id="resultMapBoy" type="Boy">
<id property="bid" column="bid"/>
<result property="bname" column="bname"/>
<result property="girl.gid" column="gid"/>
<result property="girl.gname" column="gname"/>
<result property="girl.bbid" column="bbid"/>
</resultMap>
<select id="selectByBid" resultMap="resultMapBoy">
SELECT b.*, g.*
FROM boy b
INNER JOIN girl g
ON b.`bid` = g.`bbid`
WHERE b.`bid` = #{bid};
</select>
</mapper>
测试:数据封装完好
{
"bid":1,
"bname":"周杰伦",
"girl":{
"bbid":1,
"gid":1,
"gname":"昆凌"
}
}
第二种配置:使用automapping自动映射
<mapper namespace="org.westos.mapper.BoyMapper">
<resultMap id="resultMapBoy" type="Boy" autoMapping="true">
<result property="girl.gid" column="gid"/>
<result property="girl.gname" column="gname"/>
<result property="girl.bbid" column="bbid"/>
</resultMap>
<select id="selectByBid" resultMap="resultMapBoy">
SELECT b.*, g.*
FROM boy b
INNER JOIN girl g
ON b.`bid` = g.`bbid`
WHERE b.`bid` = #{bid};
</select>
</mapper>
测试:数据封装完整
{
"bid":1,
"bname":"周杰伦",
"girl":{
"bbid":1,
"gid":1,
"gname":"昆凌"
}
}
第三种配置:使用automapping + association关联对象
<mapper namespace="org.westos.mapper.BoyMapper">
<resultMap id="resultMapBoy" type="Boy" autoMapping="true">
<!--association关联对象-->
<association property="girl" javaType="Girl" autoMapping="true"/>
</resultMap>
<select id="selectByBid" resultMap="resultMapBoy">
SELECT b.*, g.*
FROM boy b
INNER JOIN girl g
ON b.`bid` = g.`bbid`
WHERE b.`bid` = #{bid};
</select>
</mapper>
测试:数据封装完整
{
"bid":1,
"bname":"周杰伦",
"girl":{
"bbid":1,
"gid":1,
"gname":"昆凌"
}
}
延迟加载
Boy selectByPrimaryBid(Integer bid);
<resultMap id="resultMapBoy2" type="Boy" autoMapping="true">
<id property="bid" column="bid"/>
<association property="girl" column="bid"
select="selectByBBid"
fetchType="lazy"/>
</resultMap>
<select id="selectByBBid" resultType="Girl">
select * from girl where bbid = #{bbid}
</select>
<!--延迟加载-->
<select id="selectByPrimaryBid" resultMap="resultMapBoy2">
select * from boy where bid = #{bid}
</select>
测试:
{
"bid":1,
"bname":"周杰伦",
"girl":{
"bbid":1,
"gid":1,
"gname":"昆凌"
}
}
GirlMapper
public interface GirlMapper {
Girl selectByGid(Integer gid);
}
非延迟加载(复杂sql)
第一种方式:
<mapper namespace="org.westos.mapper.GirlMapper">
<resultMap id="resultMapGirl" type="Girl">
<id column="gid" property="gid"/>
<result column="gname" property="gname"/>
<result property="bbid" column="bbid"/>
<result property="boy.bid" column="bid"/>
<result property="boy.bname" column="bname"/>
</resultMap>
<select id="selectByGid" resultMap="resultMapGirl">
SELECT g.*, b.*
FROM girl g
INNER JOIN boy b
ON g.`bbid` = b.`bid`
WHERE g.`gid` = #{gid}
</select>
</mapper>
测试:封装数据完整
{
"bbid":2,
"boy":{
"bid":2,
"bname":"张杰"
},
"gid":2,
"gname":"谢娜"
}
第二种方式:
<mapper namespace="org.westos.mapper.GirlMapper">
<resultMap id="resultMapGirl" type="Girl" autoMapping="true">
<result property="boy.bid" column="bid"/>
<result property="boy.bname" column="bname"/>
</resultMap>
<select id="selectByGid" resultMap="resultMapGirl">
SELECT g.*, b.*
FROM girl g
INNER JOIN boy b
ON g.`bbid` = b.`bid`
WHERE g.`gid` = #{gid}
</select>
</mapper>
测试:封装数据完整。
第三种方式:
<mapper namespace="org.westos.mapper.GirlMapper">
<resultMap id="resultMapGirl" type="Girl" autoMapping="true">
<association property="boy" autoMapping="true" javaType="Boy"/>
</resultMap>
<select id="selectByGid" resultMap="resultMapGirl">
SELECT g.*, b.*
FROM girl g
INNER JOIN boy b
ON g.`bbid` = b.`bid`
WHERE g.`gid` = #{gid}
</select>
</mapper>
延迟加载
Girl selectByPrimaryGid(Integer gid);
<resultMap id="resultMapGirl2" type="Girl" autoMapping="true">
<id property="gid" column="gid"/>
<result property="bbid" column="bbid"/>
<association property="boy" column="bbid"
select="selectBoyById"
fetchType="lazy"/>
</resultMap>
<select id="selectBoyById" resultType="Boy">
select * from boy where bid = #{bid}
</select>
<select id="selectByPrimaryGid" resultMap="resultMapGirl2">
select * from girl where gid = #{gid}
</select>
当配置了autoMapping为true,有些字段仍然无法封装到对象时,可以使用id,result标签手动封装。
全局设置setting
1、设置全局懒加载
即,所有关联对象都为懒加载。
2、因为log4j的日志信息过多,如果我们只想查看mybatis中sql的生成过程时,我们可以在mybatis-config.xml全局配置中配置。
设置名 | 描述 | 有效值 | 默认值 |
---|---|---|---|
lazyLoadingEnabled | 延迟加载的全局开关。当开启时,所有关联对象都会延迟加载。 特定关联关系中可通过设置 fetchType 属性来覆盖该项的开关状态。 | true|false | false |
logImpl | 指定 MyBatis 所用日志的具体实现,未指定时将自动查找。 | STDOUT_LOGGING | 未设置 |
多对多关联
环境
-- student表和course表为多对多的关系
USE mybatis;
CREATE TABLE IF NOT EXISTS student(
sid INT NOT NULL AUTO_INCREMENT,
sname VARCHAR(50) NOT NULL,
sgender VARCHAR(50) NOT NULL,
PRIMARY KEY (sid)
);
CREATE TABLE IF NOT EXISTS course (
cid INT NOT NULL AUTO_INCREMENT,
cname VARCHAR(50) NOT NULL,
PRIMARY KEY (cid)
);
-- 创建关联表
CREATE TABLE IF NOT EXISTS stu_cour (
id INT NOT NULL AUTO_INCREMENT,
ssid INT NOT NULL,
ccid INT NOT NULL,
CONSTRAINT fk_stu_cour_student FOREIGN KEY (ssid) REFERENCES student(sid),
CONSTRAINT fk_stu_cout_course FOREIGN KEY (ccid) REFERENCES course(cid)
);
INSERT INTO student VALUES (NULL, '张三', '男'), (NULL, '李梅', '女');
INSERT INTO course VALUES (NULL, 'Java从入门到精通'), (NULL, 'Linux从入门到精通');
INSERT INTO stu_cour VALUES (NULL, 1, 1), (NULL, 1, 2), (NULL, 2, 1), (NULL, 2, 2);
实体类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student implements Serializable {
private Integer sid;
private String sname;
private String sgender;
private List<Course> courses;
}
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Course implements Serializable {
private static final long serialVersionUID = -7418471653366978677L;
private Integer cid;
private String cname;
private List<Student> students;
}
StudentMapper
延迟加载
1、查询单个Student对象
public interface StudentMapper {
Student selectBySid(Integer sid);
}
<mapper namespace="org.westos.mapper.StudentMapper">
<resultMap id="resultMapStudent" type="Student">
<id property="sid" column="sid"/>
<result property="sname" column="sname"/>
<result property="sgender" column="sgender"/>
<!--关联集合-->
<collection property="courses" column="sid"
select="selectCourseBySid"/>
</resultMap>
<select id="selectCourseBySid" resultType="Course">
SELECT c.*
FROM course c
INNER JOIN stu_cour s
ON c.`cid` = s.`ccid`
WHERE s.`ssid` = #{sid};
</select>
<!--全局开启懒加载-->
<select id="selectBySid" resultMap="resultMapStudent">
select *
from student
where sid = #{sid}
</select>
</mapper>
@Test
public void testStudentMapper() {
try (SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSessionFactory().openSession()) {
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
Student student = mapper.selectBySid(1);
System.out.println(JSON.toJSONString(student, true));
} catch (IOException e) {
e.printStackTrace();
}
}
==> Preparing: select * from student where sid = ?
==> Parameters: 1(Integer)
<== Columns: sid, sname, sgender
<== Row: 1, 张三, 男
<== Total: 1
==> Preparing: SELECT c.* FROM course c INNER JOIN stu_cour s ON c.`cid` = s.`ccid` WHERE s.`ssid` = ?;
==> Parameters: 1(Integer)
<== Columns: cid, cname
<== Row: 1, Java从入门到精通
<== Row: 2, Linux从入门到精通
<== Total: 2
{
"courses":[
{
"cid":1,
"cname":"Java从入门到精通"
},
{
"cid":2,
"cname":"Linux从入门到精通"
}
],
"sgender":"男",
"sid":1,
"sname":"张三"
}
2、查询所有Student对象
List<Student> selectAll();
<resultMap id="resultMapStudent2" type="Student">
<id property="sid" column="sid"/>
<result property="sname" column="sname"/>
<result property="sgender" column="sgender"/>
<collection property="courses" column="sid"
select="selectCourseBySid"/>
</resultMap>
<select id="selectCourseBySid" resultType="Course">
SELECT c.*
FROM course c
INNER JOIN stu_cour s
ON c.`cid` = s.`ccid`
WHERE s.`ssid` = #{sid};
</select>
<select id="selectAll" resultMap="resultMapStudent2">
select * from student
</select>
@Test
public void testStudentMapper() {
try (SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSessionFactory().openSession()) {
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> students = mapper.selectAll();
System.out.println(JSON.toJSONString(students, true));
} catch (IOException e) {
e.printStackTrace();
}
}
==> Preparing: select * from student
==> Parameters:
<== Columns: sid, sname, sgender
<== Row: 1, 张三, 男
<== Row: 2, 李梅, 女
<== Total: 2
==> Preparing: SELECT c.* FROM course c INNER JOIN stu_cour s ON c.`cid` = s.`ccid` WHERE s.`ssid` = ?;
==> Parameters: 1(Integer)
<== Columns: cid, cname
<== Row: 1, Java从入门到精通
<== Row: 2, Linux从入门到精通
<== Total: 2
==> Preparing: SELECT c.* FROM course c INNER JOIN stu_cour s ON c.`cid` = s.`ccid` WHERE s.`ssid` = ?;
==> Parameters: 2(Integer)
<== Columns: cid, cname
<== Row: 1, Java从入门到精通
<== Row: 2, Linux从入门到精通
<== Total: 2
[
{
"courses":[
{
"cid":1,
"cname":"Java从入门到精通"
},
{
"cid":2,
"cname":"Linux从入门到精通"
}
],
"sgender":"男",
"sid":1,
"sname":"张三"
},
{
"courses":[
{
"cid":1,
"cname":"Java从入门到精通"
},
{
"cid":2,
"cname":"Linux从入门到精通"
}
],
"sgender":"女",
"sid":2,
"sname":"李梅"
}
]
CourseMapper
延迟加载
1、查询单个Course对象
public interface CourseMapper {
Course selectByCid(Integer cid);
}
<mapper namespace="org.westos.mapper.CourseMapper">
<resultMap id="resultMapCourse" type="Course">
<id property="cid" column="cid"/>
<result property="cname" column="cname"/>
<collection property="students" column="cid"
select="selectStudentByCid"/>
</resultMap>
<select id="selectStudentByCid" resultType="Student">
SELECT s.*
FROM student s
INNER JOIN stu_cour st
ON s.`sid` = st.`ssid`
WHERE st.`ccid` = #{cid};
</select>
<select id="selectByCid" resultMap="resultMapCourse">
select * from course where cid = #{cid}
</select>
</mapper>
@Test
public void testCourseMapper() {
try (SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSessionFactory().openSession()) {
CourseMapper mapper = sqlSession.getMapper(CourseMapper.class);
Course course = mapper.selectByCid(1);
System.out.println(JSON.toJSONString(course, true));
} catch (IOException e) {
e.printStackTrace();
}
}
==> Preparing: select * from course where cid = ?
==> Parameters: 1(Integer)
<== Columns: cid, cname
<== Row: 1, Java从入门到精通
<== Total: 1
==> Preparing: SELECT s.* FROM student s INNER JOIN stu_cour st ON s.`sid` = st.`ssid` WHERE st.`ccid` = ?;
==> Parameters: 1(Integer)
<== Columns: sid, sname, sgender
<== Row: 1, 张三, 男
<== Row: 2, 李梅, 女
<== Total: 2
{
"cid":1,
"cname":"Java从入门到精通",
"students":[
{
"sgender":"男",
"sid":1,
"sname":"张三"
},
{
"sgender":"女",
"sid":2,
"sname":"李梅"
}
]
}
2、查询所有Course对象
List<Course> selectAll();
<select id="selectStudentByCid" resultType="Student">
SELECT s.*
FROM student s
INNER JOIN stu_cour st
ON s.`sid` = st.`ssid`
WHERE st.`ccid` = #{cid};
</select>
<resultMap id="resultMapCourse2" type="Course">
<id property="cid" column="cid"/>
<result property="cname" column="cname"/>
<collection property="students" column="cid"
select="selectStudentByCid"/>
</resultMap>
<select id="selectAll" resultMap="resultMapCourse2">
select * from course
</select>
@Test
public void testCourseMapper() {
try (SqlSession sqlSession = SqlSessionFactoryUtil.getSqlSessionFactory().openSession()) {
CourseMapper mapper = sqlSession.getMapper(CourseMapper.class);
/*Course course = mapper.selectByCid(1);
System.out.println(JSON.toJSONString(course, true));*/
List<Course> courses = mapper.selectAll();
System.out.println(JSON.toJSONString(courses, true));
} catch (IOException e) {
e.printStackTrace();
}
}
==> Preparing: select * from course
==> Parameters:
<== Columns: cid, cname
<== Row: 1, Java从入门到精通
<== Row: 2, Linux从入门到精通
<== Total: 2
==> Preparing: SELECT s.* FROM student s INNER JOIN stu_cour st ON s.`sid` = st.`ssid` WHERE st.`ccid` = ?;
==> Parameters: 1(Integer)
<== Columns: sid, sname, sgender
<== Row: 1, 张三, 男
<== Row: 2, 李梅, 女
<== Total: 2
==> Preparing: SELECT s.* FROM student s INNER JOIN stu_cour st ON s.`sid` = st.`ssid` WHERE st.`ccid` = ?;
==> Parameters: 2(Integer)
<== Columns: sid, sname, sgender
<== Row: 1, 张三, 男
<== Row: 2, 李梅, 女
<== Total: 2
[
{
"cid":1,
"cname":"Java从入门到精通",
"students":[
{
"sgender":"男",
"sid":1,
"sname":"张三"
},
{
"sgender":"女",
"sid":2,
"sname":"李梅"
}
]
},
{
"cid":2,
"cname":"Linux从入门到精通",
"students":[
{
"sgender":"男",
"sid":1,
"sname":"张三"
},
{
"sgender":"女",
"sid":2,
"sname":"李梅"
}
]
}
]