Mybatis关联查询
一对多：一个用户对应多个角色
实体：
package com.smbms.entity;
import javax.persistence.*;
import java.sql.Timestamp;
import java.util.Date;
import java.util.List;
public class SmbmsRoleEntity {
private long rid;
private String roleCode;
private String roleName;
private Long createdBy;
private Timestamp creationDate;
private Long modifyBy;
private Timestamp modifyDate;
//植入多的一方 集合
private List<SmbmsUserEntity> userList;
public long getRid() {
return rid;
}
public void setRid(long rid) {
this.rid = rid;
}
public String getRoleCode() {
return roleCode;
}
public void setRoleCode(String roleCode) {
this.roleCode = roleCode;
}
public String getRoleName() {
return roleName;
}
public void setRoleName(String roleName) {
this.roleName = roleName;
}
public Long getCreatedBy() {
return createdBy;
}
public void setCreatedBy(Long createdBy) {
this.createdBy = createdBy;
}
public Timestamp getCreationDate() {
return creationDate;
}
public void setCreationDate(Timestamp creationDate) {
this.creationDate = creationDate;
}
public Long getModifyBy() {
return modifyBy;
}
public void setModifyBy(Long modifyBy) {
this.modifyBy = modifyBy;
}
public Timestamp getModifyDate() {
return modifyDate;
}
public void setModifyDate(Timestamp modifyDate) {
this.modifyDate = modifyDate;
}
public List<SmbmsUserEntity> getUserList() {
return userList;
}
public void setUserList(List<SmbmsUserEntity> userList) {
this.userList = userList;
}
}
接口：
package com.smbms.dao;
import com.smbms.entity.SmbmsRoleEntity;
public interface ISmbmsRoleDao {
//查询经理角色 以及 该角色下对应的员工集合
public SmbmsRoleEntity getRoleAndUser(Integer id);
}
小配置：
<?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">
<!-- namespace属性代表该Mapper文件的唯一标识，通常习惯设置mapper接口名-->
<mapper namespace="com.smbms.dao.ISmbmsRoleDao">
<!--由于是关联查询 返回的是多张表中的结果集，必须定义resultMap映射-->
<resultMap id="countryMap" type="SmbmsRoleEntity">
<id property="rid" column="rid"></id>
<result property="roleName" column="roleName"></result>
<!--此处使用的是collection节点，由于在Country类中插入的是List集合
ofType:为集合中的泛型
-->
<collection property="userList" ofType="SmbmsUserEntity" select="gerRoleAndUserMutilSQL" column="rid">
<!--在collection中声明Provincial中的属性与表中列的映射-->
<id column="id" property="id"></id>
<result column="userName" property="userName"></result>
</collection>
</resultMap>
<!-- <select id="getRoleAndUser" parameterType="int" resultMap="countryMap">
select u.id,u.userName,u.userRole,r.rid,r.roleName from smbms_user as u ,smbms_role as r where u.userRole=r.rid and r.rid=#{id}
</select>-->
<select id="getRoleAndUser" resultMap="countryMap">
select * from smbms_role where rid=#{id}
</select>
<select id="gerRoleAndUserMutilSQL" resultType="SmbmsUserEntity">
select * from smbms_user where userRole=#{rid}
</select>
</mapper>
测试：
package com.smbms.test;
import com.smbms.dao.ISmbmsRoleDao;
import com.smbms.entity.SmbmsRoleEntity;
import com.smbms.entity.SmbmsUserEntity;
import com.smbms.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
public class Demo01 {
@Test
public void getRoleAndUserTest(){
SqlSession sqlSession=MybatisUtil.getSqlSession();
ISmbmsRoleDao mapper=sqlSession.getMapper(ISmbmsRoleDao.class);
SmbmsRoleEntity role=mapper.getRoleAndUser(3);
System.out.println("角色"+role.getRoleName());
for (SmbmsUserEntity user:role.getUserList()){
System.out.println("用户"+user.getUserName());
}
}
}
多对一：多个角色对应一个用户
实体：
package com.smbms.entity;
import javax.persistence.*;
import java.sql.Date;
import java.sql.Timestamp;
public class SmbmsUserEntity {
private long id;
private String userCode;
private String userName;
private String userPassword;
private Integer gender;
private Date birthday;
private String phone;
private String address;
private Integer userRole;
private Long createdBy;
private Timestamp creationDate;
private Long modifyBy;
private Timestamp modifyDate;
private SmbmsRoleEntity role;
public SmbmsRoleEntity getRole() {
return role;
}
public void setRole(SmbmsRoleEntity role) {
this.role = role;
}
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String getUserCode() {
return userCode;
}
public void setUserCode(String userCode) {
this.userCode = userCode;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getUserPassword() {
return userPassword;
}
public void setUserPassword(String userPassword) {
this.userPassword = userPassword;
}
public Integer getGender() {
return gender;
}
public void setGender(Integer gender) {
this.gender = gender;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public Integer getUserRole() {
return userRole;
}
public void setUserRole(Integer userRole) {
this.userRole = userRole;
}
public Long getCreatedBy() {
return createdBy;
}
public void setCreatedBy(Long createdBy) {
this.createdBy = createdBy;
}
public Timestamp getCreationDate() {
return creationDate;
}
public void setCreationDate(Timestamp creationDate) {
this.creationDate = creationDate;
}
public Long getModifyBy() {
return modifyBy;
}
public void setModifyBy(Long modifyBy) {
this.modifyBy = modifyBy;
}
public Timestamp getModifyDate() {
return modifyDate;
}
public void setModifyDate(Timestamp modifyDate) {
this.modifyDate = modifyDate;
}
}
接口：
package com.smbms.dao;
import com.smbms.entity.SmbmsUserEntity;
import java.util.List;
public interface ISmbmsUserDao {
//查询所有用户信息 包含角色信息
public List<SmbmsUserEntity> getUserList();
}
小配置：
<?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">
<!-- namespace属性代表该Mapper文件的唯一标识，通常习惯设置mapper接口名-->
<mapper namespace="com.smbms.dao.ISmbmsUserDao">
<resultMap id="ProvincialMap" type="SmbmsUserEntity">
<id column="id" property="id"></id>
<result column="userName" property="userName"></result>
<!--当关联对象为普通对象时，使用association标签
javaType:为关联对象的类型
-->
<association property="role" javaType="SmbmsRoleEntity">
<id column="rid" property="rid"></id>
<result column="roleName" property="roleName"></result>
</association>
</resultMap>
<select id="getUserList" resultMap="ProvincialMap">
select u.id,u.userName,u.userRole,r.rid,r.roleName from smbms_user as u,smbms_role as r where u.userRole=r.rid
</select>
</mapper>
测试：
package com.smbms.test;
import com.smbms.dao.ISmbmsRoleDao;
import com.smbms.dao.ISmbmsUserDao;
import com.smbms.entity.SmbmsRoleEntity;
import com.smbms.entity.SmbmsUserEntity;
import com.smbms.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class Demo {
@Test
public void getRoleAndUserTest(){
SqlSession sqlSession= MybatisUtil.getSqlSession();
ISmbmsUserDao mapper=sqlSession.getMapper(ISmbmsUserDao.class);
List<SmbmsUserEntity> userList=mapper.getUserList();
for (SmbmsUserEntity user:userList){
System.out.println("用户"+user.getUserName()+"\t角色"+user.getRole().getRoleName() );
}
}
}
多对多：多个教师对应多个学生
实体：教师
package com.smbms.entity;
import javax.persistence.*;
import java.util.ArrayList;
import java.util.List;
public class StudentinfoEntity {
private int stuid;
private String studentname;
private String sex;
private String address;
private String birthday;
//多的一方的教师
private List<TeacherEntity> teachers=new ArrayList<>();
public int getStuid() {
return stuid;
}
public void setStuid(int stuid) {
this.stuid = stuid;
}
public String getStudentname() {
return studentname;
}
public void setStudentname(String studentname) {
this.studentname = studentname;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getBirthday() {
return birthday;
}
public void setBirthday(String birthday) {
this.birthday = birthday;
}
public List<TeacherEntity> getTeachers() {
return teachers;
}
public void setTeachers(List<TeacherEntity> teachers) {
this.teachers = teachers;
}
}
学生：
package com.smbms.entity;
import javax.persistence.*;
import java.util.ArrayList;
import java.util.List;
public class TeacherEntity {
private int tid;
private String tname;
//多的一方的学生实体
private List<StudentinfoEntity> students=new ArrayList<>();
public int getTid() {
return tid;
}
public void setTid(int tid) {
this.tid = tid;
}
public String getTname() {
return tname;
}
public void setTname(String tname) {
this.tname = tname;
}
public List<StudentinfoEntity> getStudents() {
return students;
}
public void setStudents(List<StudentinfoEntity> students) {
this.students = students;
}
}
接口：
package com.smbms.dao;
import com.smbms.entity.TeacherEntity;
import java.util.List;
//关联查询多对多
public interface TeacherMapper {
//查询所以教师(包括所教学员)
List<TeacherEntity> getAllTeacher();
}
小配置：
<?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">
<!-- namespace属性代表该Mapper文件的唯一标识，通常习惯设置mapper接口名-->
<mapper namespace="com.smbms.dao.TeacherMapper">
<resultMap id="teacherMap" type="TeacherEntity">
<id column="tid" property="tid"></id>
<result column="tname" property="tname"></result>
<!--为students集合设置关联属性-->
<collection property="students" ofType="StudentinfoEntity">
<id column="stuid" property="stuid"></id>
<result column="studentname" property="studentname"></result>
<result column="sex" property="sex"></result>
<result column="address" property="address"></result>
<result column="birthday" property="birthday"></result>
</collection>
</resultMap>
<select id="getAllTeacher" resultMap="teacherMap">
SELECT s.*,t.* FROM teacher t,studentinfo s,teastu ts
WHERE t.`tid`=ts.`tid` AND s.`stuid`=ts.`stuid`
</select>
</mapper>
测试：
package com.smbms.test;
import com.smbms.dao.TeacherMapper;
import com.smbms.entity.TeacherEntity;
import com.smbms.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.util.List;
public class Demo05 {
SqlSession session=null;
TeacherMapper mapper=null;
@Before
public void before(){
session= MybatisUtil.getSqlSession();
mapper=session.getMapper(TeacherMapper.class);
}
@Test
public void test01(){
List<TeacherEntity> teachers = mapper.getAllTeacher();
//重写Teacher类和Student类中的toString方法 直接打印
for (TeacherEntity item:teachers){
System.out.println(item);
}
}
@After
public void after() {
session.commit();//事务提交
session.close();//释放se
}
}
自关联：一个分类下存在多个子分类
实体：
package com.smbms.entity;
import javax.persistence.*;
import java.util.ArrayList;
import java.util.List;
public class ProductCategoryEntity {
private int id;
private String name;
private int type;
private List<ProductCategoryEntity> lists=new ArrayList<>();
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getType() {
return type;
}
public void setType(int type) {
this.type = type;
}
public List<ProductCategoryEntity> getLists() {
return lists;
}
public void setLists(List<ProductCategoryEntity> lists) {
this.lists = lists;
}
}
接口：
package com.smbms.dao;
import com.smbms.entity.ProductCategoryEntity;
//自关联
public interface CategoryMapper {
//查询一级分类(包括其下的子分类)
ProductCategoryEntity getOneById(int categoryid);
}
小配置：
<?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">
<!-- namespace属性代表该Mapper文件的唯一标识，通常习惯设置mapper接口名-->
<mapper namespace="com.smbms.dao.CategoryMapper">
<resultMap id="cateMap" type="ProductCategoryEntity">
<id column="id" property="id"></id>
<result column="name" property="name"></result>
<result column="type" property="type"></result>
<!--
参照实体类，lists变量的类型是集合(典型的1:N的关系)
select:指定查询关联对象的select语句(getOneByParent)
column:查询关联对象select语句的条件值
本例:根据id获取对应的分类后，以此id为条件获取子级分类(parentId=父级分类ID)
-->
<collection property="lists" ofType="ProductCategoryEntity" select="getOneByParent" column="id"/>
</resultMap>
<select id="getOneById" parameterType="int" resultMap="cateMap">
select * from product_category where id=#{cateid}
</select>
<select id="getOneByParent" parameterType="int" resultMap="cateMap">
select * from product_category where parentId=#{id}
</select>
</mapper>
测试：
package com.smbms.test;
import com.smbms.dao.CategoryMapper;
import com.smbms.entity.ProductCategoryEntity;
import com.smbms.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
public class Demo06 {
SqlSession session=null;
CategoryMapper mapper=null;
@Before
public void before(){
session= MybatisUtil.getSqlSession();
mapper=session.getMapper(CategoryMapper.class);
}
@Test
public void test01(){
ProductCategoryEntity cate = mapper.getOneById(548);
//重写Category类中的toString方法 直接打印
System.out.println(cate);
}
@After
public void after(){
session.commit();//事务提交
session.close();//释放session
}
}
浙公网安备 33010602011771号