使用resultMap实现高级结果映射
resultMap的属性:
1.属性
id:resultMap的唯一标识。
type:resulMap的映射结果类型(一般为Java实体类)。
2.子节点
id:一般对应数据库的主键 id,设置此项可以提升数据库性能。
result:映射到JavaBean的某个 “ 简单类型 ” 属性,如基础数据类型,包装类等。子节点 id 和 result 均可以实现最基本的结果集映射,将列映射到简单数据类型的属性。。这两者唯一不同的是:在比较对象实例时 id 将作为结果集的标识属性。这有助于提高总体性能,特别是应用缓存的嵌套结果映射的时候,若需要实现高级结果映射,就需要下面两个配置项:association 和 collection
association(仅处理一对一的关联关系)
<resultMap type="User" id="userRoleResult">
<id property="id" column="id"/>
<result property="userName" column="userName"/>
<association property="role" javaType="Role">
<id property="roleName" column="roleName"/>
</association>
</resultMap>
javaType:完整Java类名或者别名。若映射到一个JavaBean,则MyBatis通常会自行检测到其类型;若映射到一个HashMap,则应该明确指定javaType,类确保所需行为。此处为Role。
property:映射到数据库列的实体对象的属性,此处为在User实体类里定义的JavaBean对象属性(role)。association的子元素如下:
id。
result。
property:映射到数据库列的实体类对象的属性。此处为Role的属性。
column:数据库列名或者别名。
在做结果映射的过程中,要确保所有的列名都是唯一无歧义的。
collection(属性是一个集合列表)
<resultMap type="User" id="userRoleResult">
<id property="id" column="id"/>
<result property="userName" column="userName"/>
<collection property="List" ofType="Address">
<id property="id" column="id"/>
<result property="postCode" column="postCode"/>
</collection>
</resultMap>
ofType:完整Java类名或者别名,即集合所包含的类型,此处为Address。
property:映射数据库列的实体类对象的属性。此处为在User里定义的属性:List(可以理解为一个名为List,元素类型为Address的ArrayList集合)
collection的子元素与association基本一致。
一对多
实体
package com.smbms.entity; import java.math.BigInteger; import java.util.Date; import java.util.List; /** * 角色 */ public class SmbmsRole {
private Integer rid;
private String roleCode;
private String roleName;
private BigInteger createdBy;
private Date creationDate;
private BigInteger modifyBy;
private Date modifyDate; //植入多的一方 集合
private List<SmbmsUser> userList; public List<SmbmsUser> getUserList() {
return userList;
} public void setUserList(List<SmbmsUser> userList) {
this.userList = userList;
} public Integer getRid() {
return rid;
} public void setRid(Integer 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 BigInteger getCreatedBy() {
return createdBy;
} public void setCreatedBy(BigInteger createdBy) {
this.createdBy = createdBy;
} public Date getCreationDate() {
return creationDate;
} public void setCreationDate(Date creationDate) {
this.creationDate = creationDate;
} public BigInteger getModifyBy() {
return modifyBy;
} public void setModifyBy(BigInteger modifyBy) {
this.modifyBy = modifyBy;
} public Date getModifyDate() {
return modifyDate;
} public void setModifyDate(Date modifyDate) {
this.modifyDate = modifyDate;
}
}
DAO层
package com.smbms.dao; import com.smbms.entity.SmbmsUser; import java.util.List; public interface ISmbmsUserDao {
//查询所有用户信息 包含角色信息
public List<SmbmsUser> getUserList();
}
DAO层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" select="getRoleAndUserMutilSQL" column="rid"> </collection>
</resultMap> <!--写成一条sql-->
<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>
<!--写成两条sql-->
<select id="getRoleAndUser" resultMap="roleAndUserMapper">
select * from smbms_role where rid=#{id}
</select>
<select id="getRoleAndUserMutilSQL" resultType="SmbmsUser">
select * from smbms_user where userRole=#{rid}
</select>
</mapper>
测试
package com.smbms.test; import com.smbms.dao.ISmbmsRoleDao;
import com.smbms.entity.SmbmsRole;
import com.smbms.entity.SmbmsUser;
import com.smbms.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test; public class CollectionTest {
@Test
public void getRoleAndUserTest(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
ISmbmsRoleDao mapper = sqlSession.getMapper(ISmbmsRoleDao.class); SmbmsRole role = mapper.getRoleAndUser(3);
System.out.println("角色:"+role.getRoleName());
for(SmbmsUser user : role.getUserList()){
System.out.print("\t用户:"+user.getUserName());
}
}
}
多对一
实体
package com.smbms.entity; import java.math.BigInteger;
import java.util.Date; /**
*
*/
public class SmbmsUser {
private Integer 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 BigInteger createdBy;
private Date creationDate;
private BigInteger modifyBy;
private Date modifyDate; //关联一的一方
private SmbmsRole role; public SmbmsRole getRole() {
return role;
} public void setRole(SmbmsRole role) {
this.role = role;
} public Integer getId() {
return id;
} public void setId(Integer 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 BigInteger getCreatedBy() {
return createdBy;
} public void setCreatedBy(BigInteger createdBy) {
this.createdBy = createdBy;
} public Date getCreationDate() {
return creationDate;
} public void setCreationDate(Date creationDate) {
this.creationDate = creationDate;
} public BigInteger getModifyBy() {
return modifyBy;
} public void setModifyBy(BigInteger modifyBy) {
this.modifyBy = modifyBy;
} public Date getModifyDate() {
return modifyDate;
} public void setModifyDate(Date modifyDate) {
this.modifyDate = modifyDate;
}
}
DAO层
package com.smbms.dao; import com.smbms.entity.SmbmsUser; import java.util.List; public interface ISmbmsUserDao {
//查询所有用户信息 包含角色信息
public List<SmbmsUser> getUserList();
}
DAO层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.ISmbmsUserDao">
<resultMap id="userListAndRole" type="SmbmsUser">
<id column="id" property="id"></id>
<result column="userName" property="userName"/>
<association property="role" javaType="SmbmsRole" select="getRole" column="userRole">
<id column="rid" property="rid"></id>
<result column="roleName" property="roleName"/>
</association>
</resultMap> <!--<select id="getUserList" resultMap="userListAndRole">
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>--> <select id="getUserList" resultMap="userListAndRole">
select * from smbms_user
</select>
<select id="getRole" resultType="SmbmsRole">
select * from smbms_role where rid=#{userRole}
</select>
</mapper>
测试
package com.smbms.test; import com.smbms.dao.ISmbmsUserDao;
import com.smbms.entity.SmbmsUser;
import com.smbms.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test; import java.util.List; public class AssociationTest {
@Test
public void getUserListTest(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
ISmbmsUserDao mapper = sqlSession.getMapper(ISmbmsUserDao.class); List<SmbmsUser> userList = mapper.getUserList();
for(SmbmsUser user:userList){
System.out.println("用户:"+user.getUserName()+"\t角色:"+user.getRole().getRoleName());
}
}
}
多对多
实体
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;
}
} package com.smbms.entity; import java.util.List; public class Student {
private Integer stuid;
private String stuname;
private String stuaddress; //植入Teacher集合,代表一名学员可以被多名教员教授
private List<Teacher> teachers; public List<Teacher> getTeachers() {
return teachers;
} public void setTeachers(List<Teacher> teachers) {
this.teachers = teachers;
} public Integer getStuid() {
return stuid;
} public void setStuid(Integer stuid) {
this.stuid = stuid;
} public String getStuname() {
return stuname;
} public void setStuname(String stuname) {
this.stuname = stuname;
} public String getStuaddress() {
return stuaddress;
} public void setStuaddress(String stuaddress) {
this.stuaddress = stuaddress;
}
}
DAO层
package com.smbms.dao; import com.smbms.entity.Student; import java.util.List; public interface IStudentDao {
//查询所有学生信息 以及授课教员
public List<Student> getStudentInfo();
}
DAO层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>
测试
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);
}
DAO层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>
测试
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());
}
}