MyBatis的关联查询
首先要了解对象的三种关联方式
*数据库表的关联关系有三种,一对一,一对多,多对多
一对一 是通过在任意一方的主键,引入对方主键作为外键来实现的,就是说主键与外键为同一字段
一对多 是通过在“多”的一方,添加“一”的一方的主键作为外键
多对多 是通过一张中间关系表,引入两张表的主键作为外键,两个主键成为联合主键或使用新的字段作为主键
*在java类中关联关系也有三种,一对一,一对多,多对多
一对一 在本类中定义对方类型的对象,如A类中定义B类类型的属性b,B类中定义A类类型的属性a
一对多 一个A类类型对应多个B类类型的情况,需要在A类中以集合的方式引入B类类型的对象,在B类中定义A类类型的属性a
多对多 在A类中定义B类类型的集合,在B类中定义A类类型的集合
1.一对多关联查询:
搭建实体类:
1 package com.smbms.entity; 2 3 import java.math.BigInteger; 4 import java.util.Date; 5 import java.util.List; 6 7 /** 8 * 角色 9 */ 10 public class SmbmsRole { 11 private Integer rid; 12 private String roleCode; 13 private String roleName; 14 private BigInteger createdBy; 15 private Date creationDate; 16 private BigInteger modifyBy; 17 private Date modifyDate; 18 19 //植入多的一方 集合 20 private List<SmbmsUser> userList; 21 22 public List<SmbmsUser> getUserList() { 23 return userList; 24 } 25 26 public void setUserList(List<SmbmsUser> userList) { 27 this.userList = userList; 28 } 29 30 public Integer getRid() { 31 return rid; 32 } 33 public void setRid(Integer rid) { 34 this.rid = rid; 35 } 36 public String getRoleCode() { 37 return roleCode; 38 } 39 public void setRoleCode(String roleCode) { 40 this.roleCode = roleCode; 41 } 42 public String getRoleName() { 43 return roleName; 44 } 45 public void setRoleName(String roleName) { 46 this.roleName = roleName; 47 } 48 public BigInteger getCreatedBy() { 49 return createdBy; 50 } 51 public void setCreatedBy(BigInteger createdBy) { 52 this.createdBy = createdBy; 53 } 54 public Date getCreationDate() { 55 return creationDate; 56 } 57 public void setCreationDate(Date creationDate) { 58 this.creationDate = creationDate; 59 } 60 public BigInteger getModifyBy() { 61 return modifyBy; 62 } 63 public void setModifyBy(BigInteger modifyBy) { 64 this.modifyBy = modifyBy; 65 } 66 public Date getModifyDate() { 67 return modifyDate; 68 } 69 public void setModifyDate(Date modifyDate) { 70 this.modifyDate = modifyDate; 71 } 72 }
创建Dao层对象:
1 package com.smbms.dao; 2 import com.smbms.entity.SmbmsRole; 3 public interface ISmbmsRoleDao { 4 //查询经理角色 以及 该角色下对应的员工集合 5 public SmbmsRole getRoleAndUser(Integer id); 6 }
创建ISmbmsRoleDao.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"> <!--namespace需要指向接口全路径--> <mapper namespace="com.smbms.dao.ISmbmsRoleDao"> <resultMap id="roleAndUserMapper" type="SmbmsRole"> <id column="rid" property="rid"></id> <result column="roleName" property="roleName"/> <!--映射多的一方 property代表实体当中多的一方的属性名 ofType代表集合当中泛型类型--> <!-- select 代表执行查询的ID column所引用的条件列 --> <collection property="userList" ofType="SmbmsUser"> </collection> </resultMap> <select id="getRoleAndUser" resultMap="roleAndUserMapper"> 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> </mapper>
创建test测试类:
1 package com.smbms.test; 2 3 import com.smbms.dao.ISmbmsRoleDao; 4 import com.smbms.entity.SmbmsRole; 5 import com.smbms.entity.SmbmsUser; 6 import com.smbms.util.MybatisUtil; 7 import org.apache.ibatis.session.SqlSession; 8 import org.junit.Test; 9 10 public class CollectionTest { 11 @Test 12 public void getRoleAndUserTest(){ 13 SqlSession sqlSession = MybatisUtil.getSqlSession(); 14 ISmbmsRoleDao mapper = sqlSession.getMapper(ISmbmsRoleDao.class); 15 16 17 SmbmsRole role = mapper.getRoleAndUser(3); 18 System.out.println("角色:"+role.getRoleName()); 19 for(SmbmsUser user : role.getUserList()){ 20 System.out.print("\t用户:"+user.getUserName()); 21 } 22 } 23 }
多对一关联查询
创建ISmbmsRoleDao.xml文件
1 <?xml version="1.0" encoding="UTF-8" ?> 2 <!DOCTYPE mapper 3 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 4 "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> 5 <!--namespace需要指向接口全路径--> 6 <mapper namespace="com.smbms.dao.ISmbmsUserDao"> 7 <resultMap id="userListAndRole" type="SmbmsUser"> 8 <id column="id" property="id"></id> 9 <result column="userName" property="userName"/> 10 <association property="role" javaType="SmbmsRole" select="getRole" column="userRole"> 11 <id column="rid" property="rid"></id> 12 <result column="roleName" property="roleName"/> 13 </association> 14 </resultMap> 15 16 17 <select id="getUserList" resultMap="userListAndRole"> 18 select * from smbms_user 19 </select> 20 <select id="getRole" resultType="SmbmsRole"> 21 select * from smbms_role where rid=#{userRole} 22 </select> 23 </mapper>
创建Dao层接口:
1 package com.smbms.dao; 2 3 import com.smbms.entity.SmbmsUser; 4 5 import java.util.List; 6 7 public interface ISmbmsUserDao { 8 //查询所有用户信息 包含角色信息 9 public List<SmbmsUser> getUserList(); 10 }
创建ISmbmsUserDao.xml文件:
1 <?xml version="1.0" encoding="UTF-8" ?> 2 <!DOCTYPE mapper 3 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 4 "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> 5 <!--namespace需要指向接口全路径--> 6 <mapper namespace="com.smbms.dao.ISmbmsUserDao"> 7 <resultMap id="userListAndRole" type="SmbmsUser"> 8 <id column="id" property="id"></id> 9 <result column="userName" property="userName"/> 10 <association property="role" javaType="SmbmsRole" select="getRole" column="userRole"> 11 <id column="rid" property="rid"></id> 12 <result column="roleName" property="roleName"/> 13 </association> 14 </resultMap> 15 <select id="getUserList" resultMap="userListAndRole">
16 select * from smbms_user
17 </select>
18 <select id="getRole" resultType="SmbmsRole">
19 select * from smbms_role where rid=#{userRole}
20 </select>
21</mapper>
创建测试类:
1 package com.smbms.test; 2 3 import com.smbms.dao.ISmbmsUserDao; 4 import com.smbms.entity.SmbmsUser; 5 import com.smbms.util.MybatisUtil; 6 import org.apache.ibatis.session.SqlSession; 7 import org.junit.Test; 8 9 import java.util.List; 10 11 public class AssociationTest { 12 @Test 13 public void getUserListTest(){ 14 SqlSession sqlSession = MybatisUtil.getSqlSession(); 15 ISmbmsUserDao mapper = sqlSession.getMapper(ISmbmsUserDao.class); 16 17 List<SmbmsUser> userList = mapper.getUserList(); 18 for(SmbmsUser user:userList){ 19 System.out.println("用户:"+user.getUserName()+"\t角色:"+user.getRole().getRoleName()); 20 } 21 } 22 }
多对多关联查询:
student实体类:
1 package com.smbms.entity; 2 3 import java.util.List; 4 5 public class Student { 6 private Integer stuid; 7 private String stuname; 8 private String stuaddress; 9 10 //植入Teacher集合,代表一名学员可以被多名教员教授 11 private List<Teacher> teachers; 12 13 public List<Teacher> getTeachers() { 14 return teachers; 15 } 16 17 public void setTeachers(List<Teacher> teachers) { 18 this.teachers = teachers; 19 } 20 21 public Integer getStuid() { 22 return stuid; 23 } 24 25 public void setStuid(Integer stuid) { 26 this.stuid = stuid; 27 } 28 29 public String getStuname() { 30 return stuname; 31 } 32 33 public void setStuname(String stuname) { 34 this.stuname = stuname; 35 } 36 37 public String getStuaddress() { 38 return stuaddress; 39 } 40 41 public void setStuaddress(String stuaddress) { 42 this.stuaddress = stuaddress; 43 } 44 }
teacher实体类:
package com.smbms.entity; import java.util.List; public class Teacher { private Integer tid; private String tname; //植入学员集合,代表一名教员可以教授多名学员 private List<Student> students; public Integer getTid() { return tid; } public void setTid(Integer tid) { this.tid = tid; } public String getTname() { return tname; } public void setTname(String tname) { this.tname = tname; } public List<Student> getStudents() { return students; } public void setStudents(List<Student> students) { this.students = students; } }
DAO层接口:
package com.smbms.dao; import com.smbms.entity.Student; import java.util.List; public interface IStudentDao { //查询所有学生信息 以及授课教员 public List<Student> getStudentInfo(); }
创建IStudentDao.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"> <!--namespace需要指向接口全路径--> <mapper namespace="com.smbms.dao.IStudentDao"> <resultMap id="studentAndTeacherMapper" type="Student"> <id column="stuid" property="stuid"/> <result column="stuname" property="stuname"/> <collection property="teachers" ofType="Teacher"> <id column="tid" property="tid"></id> <result property="tname" column="tname"/> </collection> </resultMap> <select id="getStudentInfo" resultMap="studentAndTeacherMapper"> select * from student,teacher,stu_t where student.stuid=stu_t.stuid and teacher.tid=stu_t.tid </select> </mapper>
创建Test测试文件:
package com.smbms.test; import com.smbms.dao.IStudentDao; import com.smbms.entity.Student; import com.smbms.entity.Teacher; import com.smbms.util.MybatisUtil; import org.apache.ibatis.session.SqlSession; import org.junit.Test; import java.util.List; public class ManeyTooManey { @Test public void getStudentInfo(){ SqlSession sqlSession = MybatisUtil.getSqlSession(); IStudentDao mapper = sqlSession.getMapper(IStudentDao.class); List<Student> studentInfo = mapper.getStudentInfo(); for(Student stu:studentInfo){ System.out.println("学生:"+stu.getStuname()); for (Teacher teacher:stu.getTeachers()){ System.out.print("\t教员:"+teacher.getTname()); } System.out.println(); } } }
自关联查询:
实体类:
package com.smbms.entity; import java.util.List; public class City { private Integer cid; private String cname; private Integer pid; //自关联集合 代表的是当前City对象的子集集合 public List<City> childCitys; @Override public String toString() { return "City{" + "cid=" + cid + ", cname='" + cname + '\'' + ", pid=" + pid + ", childCitys=" + childCitys + '}'; } public List<City> getChildCitys() { return childCitys; } public void setChildCitys(List<City> childCitys) { this.childCitys = childCitys; } public Integer getCid() { return cid; } public void setCid(Integer cid) { this.cid = cid; } public String getCname() { return cname; } public void setCname(String cname) { this.cname = cname; } public Integer getPid() { return pid; } public void setPid(Integer pid) { this.pid = pid; } }
Dao层接口:
package com.smbms.dao; import com.smbms.entity.City; import java.util.List; public interface ICityDao { //查询河南省 下的所有子集 public City getCityAndChildCitys(Integer cid); }
ICityDao.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"> <!--namespace需要指向接口全路径--> <mapper namespace="com.smbms.dao.ICityDao"> <resultMap id="CityAndChildCitysMapper" type="City"> <id column="cid" property="cid"></id> <result column="cname" property="cname"/> <result column="pid" property="pid"/> <collection property="childCitys" ofType="City" select="getCityAndChildCitysMutilSQL" column="cid"> <id column="cid" property="cid"></id> <result column="cname" property="cname"/> <result column="pid" property="pid"/> </collection> </resultMap> <select id="getCityAndChildCitys" resultMap="CityAndChildCitysMapper"> select * from city where cid=#{cid} </select> <select id="getCityAndChildCitysMutilSQL" resultMap="CityAndChildCitysMapper"> select * from city where pid=#{cid} </select> </mapper>
Test测试类:
package com.smbms.test; import com.smbms.dao.ICityDao; import com.smbms.entity.City; import com.smbms.util.MybatisUtil; import org.apache.ibatis.session.SqlSession; import org.junit.Test; public class DGTest { @Test public void getCityAndChildCitysTest(){ SqlSession sqlSession = MybatisUtil.getSqlSession(); ICityDao mapper = sqlSession.getMapper(ICityDao.class); City city = mapper.getCityAndChildCitys(410000); System.out.println(city.toString()); } }