7、一对一关联、多对多关联、设置延迟加载的全局开关、指定MyBatis所用日志的具体实现

一对一关联

环境

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|falsefalse
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":"李梅"
			}
		]
	}
]
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值