海阔天空

手机扫码查看

2020java框架教程之mybatis嵌套查询以及延迟加载

一对一查询

用户与简历

1.数据库表:

简历数据库表：

CREATE TABLE `resume` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `resume_name` varchar(50) NOT NULL,
  `resume_uid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `resume_uid` (`resume_uid`),
  CONSTRAINT `resume_ibfk_1` FOREIGN KEY (`resume_uid`) REFERENCES `member` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

用户数据库表：

CREATE TABLE `member` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `sex` varchar(2) NOT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

2.在两个实体类创建对方实体属性

3.配置mapper.xml

用户的mapper.xml

<resultMap id="BaseResultMap" type="com.evshou.admin.entity.Member" >
  <id column="uid" property="uid" jdbcType="INTEGER" />
  <result column="username" property="username" jdbcType="VARCHAR" />
  <result column="sex" property="sex" jdbcType="VARCHAR" />
</resultMap>
<select id="queryMemberByUid" resultMap="BaseResultMap">
  select * from member where uid=#{uid}
</select>

简历的mapper.xml

<resultMap id="BaseResultMap" type="com.evshou.admin.entity.Resume" >
  <id column="id" property="id" jdbcType="INTEGER" />
  <result column="resume_name" property="resumeName" jdbcType="VARCHAR" />
  <result column="resume_uid" property="resumeUid" jdbcType="INTEGER" />
  <association property="member" javaType="Member"
               select="com.evshou.admin.mapper.MemberMapper.queryMemberByUid"
               column="resume_uid"/>
</resultMap>
<select id="queryResumeByUId" resultMap="BaseResultMap">
  select  * from resume where resume_uid=#{uid}
</select>

4.创建接口

用户接口：

Member queryMemberByUid();

简历接口：

Resume queryResumeByUId(Integer uid);

5.测试

@Test
public void queryResumeByUId(){
    SqlSession ss = MybatisUtils.getSqlSession();
    ResumeMapper rm = ss.getMapper(ResumeMapper.class);
    Resume resume = rm.queryResumeByUId(1);
    System.out.println(resume);
    System.out.println(resume.getMember());
}

---

一对多和多对一

部门与员工

通过部门编号查询部门所在的员工，所以员工是List集合

1.数据库表

create table dept(#部门
    id int primary key  auto_increment,
    name varchar(50) ,
    loc varchar(100)
)charset=utf8;
create table emp(#员工
    id int auto_increment primary key ,
    name varchar(50),
    salary double,
    dep_id int ,
    foreign key (dep_id) references dept(id)
)charset=utf8;

2.实体类

3.接口

4.映射文件

5.测试代码

---

延迟加载(了解)

在mybatis-config.xml配置延迟加载

<settings>
    <!-- 当使用嵌套查询时，如果查询中只用到了一方的数据，则不会触发另一方的查询 -->
    <setting name="lazyLoadingEnabled" value="true"/><!--开启延迟加载(默认false)-->
</settings>