resultMap
简单应用
myBatis通常在映射<select>元素执行sql时使用returnType这只返回结果类型,有时需要对查询返回结果进行特殊处理;<select>元素体现了returnMap属性为查询返回结果进行处理的更灵活方式。 returnMap属性是对sql映射中某个returnMap元素的引用,而returnMap元素决定了如何处理查询返回结果
例子: 查询时,自定义类中的属性名和数据库中的名字不一致,导致不能一一映射,就使用resultMap: 新创建一个student类:
package com.domin;
public class Student {
private String proId;
private String proName;
private int proAge;
private String proSex;
}
在数据库中,名称与这个类不一样: student-mapper.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">
<mapper namespace="stuns">
<cache-ref namespace="global-catche"/>
<select id="selectStudent" resultMap="stuMap" statementType="PREPARED">
select * from student
</select>
<resultMap id="stuMap" type="stu" autoMapping="false">
<id column="ID" property="proId" javaType="string" jdbcType="VARCHAR"/>
<result column="NAME" property="proName" javaType="string" jdbcType="VARCHAR"/>
<result column="AGE" property="proAge" javaType="int" jdbcType="INTEGER"/>
<result column="SEX" property="proSex" jdbcType="VARCHAR" javaType="string"/>
</resultMap>
</mapper>
(省略在configuration配置别名和加入mapper了) (省略在dao中使用这个函数了) 最后servlet验证:
List<Student> list5 = StudentDao.selectStudent();
for(Student s:list5){
System.out.println(s.getProName());
}
结果: 当然还可以更简单的方式: 修改上面的select语句,resultType为student类,只不过查询时别名改成student中的属性名,就可以了
<select id="selectStudent" resultType="stu" statementType="PREPARED">
select id proId,name proName,age ProAge,sex proSex from student
</select>
高级复杂结果关联映射(一对多)
用于实现数据库中的多表查询,如左连接右连接之类的 现有三个表,他们的id有外键关联: 表department、表emp、表salary数据分别如下:
需要用MyBatis实现的查询语句如下:
select d.id did,d.name dname,e.id eid,e.name ename,e.age eage,e.sex esex,s.id sid,s.name sname,s.money smoney
from department d inner join emp e on d.id = e.depid
inner join salary s on e.id = s.empid
结果如下: ok,开始敲代码 先建立department,emp,salary类,并且还要为department类加上List<Emp>,给Emp类加上List<Salary>
public class Department {
private String id;
private String name;
private String code;
private Date newDate;
private String descs;
private List<Emp> empList;
}
public class Emp {
private String id;
private String name;
private int age;
private String sex;
private String depId;
private List<Salary> salList;
}
package com.domin;
public class Salary {
private String id;
private String name;
private String empId;
private double money;
}
然后为了方便,在configuration.xml中弄一个别名
<!--创建类的别名-->
<typeAliases>
<typeAlias type="com.domin.Department" alias="dept"/>
<typeAlias type="com.domin.Student" alias="stu"/>
<typeAlias type="com.domin.Emp" alias="emp"/>
<typeAlias type="com.domin.Salary" alias="sal"/>
</typeAliases>
在department-mapper实现这功能
<select id="strcutTreeMap" resultMap="dep_emp_sal_map" statementType="PREPARED">
select d.id did,d.name dname,e.id eid,e.name ename,e.age eage,e.sex esex,s.id sid,s.name sname,s.money smoney
from department d inner join emp e on d.id = e.depid
inner join salary s on e.id = s.empid
</select>
<resultMap id="dep_emp_sal_map" type="dept" autoMapping="false">
<id column="did" property="id" javaType="string" jdbcType="VARCHAR"/>
<result column="dname" property="name" javaType="string" jdbcType="VARCHAR"/>
<collection property="empList" autoMapping="false" ofType="emp">
<id column="eid" property="id" javaType="string" jdbcType="VARCHAR"/>
<result column="ename" property="name" javaType="string" jdbcType="VARCHAR"/>
<result column="esex" property="sex" javaType="string" jdbcType="VARCHAR"/>
<collection property="salList" autoMapping="false" ofType="sal">
<id column="sid" property="id" javaType="string" jdbcType="VARCHAR"/>
<result column="sname" property="name" javaType="string" jdbcType="VARCHAR"/>
<result column="smoney" property="money" javaType="double" jdbcType="DOUBLE"/>
</collection>
</collection>
</resultMap>
在departmentDao.java的函数:
public static List<Department> strcutTreeMap(){
SqlSession sqlSession = MySqlSessionManager.getSqlSession();
List<Department> list = sqlSession.selectList("depns.strcutTreeMap");
sqlSession.commit();
sqlSession.close();
return list;
}
servlet:
List<Department> list = DepartmentDao.strcutTreeMap();
for(Department d:list){
System.out.println("部门id"+d.getId() + "部门名称" + d.getName());
List<Emp> empList = d.getEmpList();
for(Emp e:empList){
System.out.println("员工id" + e.getId() + "员工姓名" + e.getName());
List<Salary> salaryList = e.getSalList();
for(Salary s:salaryList){
System.out.println("奖金类型" + s.getName() + "奖金金额" + s.getMoney());
}
}
}
结果:
部门idDEPID1部门名称钓鱼部
员工idEMPID1员工姓名叶问
奖金类型辛勤工作奖金奖金金额1000.0
部门idDEPID3部门名称睡眠部
员工idempid2员工姓名成乘
奖金类型激励奖金奖金金额2000.0
员工idempid3员工姓名张飞
奖金类型工资奖金金额1200.0
部门idDEPID2部门名称信息部
员工idempid4员工姓名莫少
奖金类型额为奖金奖金金额10000.0
也可以用Map集合代替实体bean的使用,就是获取map中的元素略有些麻烦。
高级复杂结果关联映射(一对一)
还是用上面的表来举例子: 这里的薪水和员工一一对应。现在我要得到员工信息附带其相关的薪水信息! 在Emp.java中加上salary类属性
private Salary sal;
emp-mapper.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">
<mapper namespace="empns">
<select id="findEmp" resultMap="findEmpMap" statementType="PREPARED">
select * from emp
</select>
<resultMap id="findEmpMap" type="emp" autoMapping="false">
<id column="ID" property="id" javaType="string" jdbcType="VARCHAR"/>
<result column="NAME" property="name" javaType="string" jdbcType="VARCHAR"/>
<result column="AGE" property="age" javaType="int"/>
<result column="sex" property="sex" javaType="string" jdbcType="VARCHAR"/>
<result column="depId" property="depId" javaType="string" jdbcType="VARCHAR"/>
<association property="sal" autoMapping="false" column="id" select="findSelMap"/>
</resultMap>
<select id="findSelMap" resultType="sal" parameterType="string" statementType="PREPARED">
select * from salary where empid = #{empId}
</select>
</mapper>
EmpDao:
public static List<Emp> findselMap(){
SqlSession sqlSession = MySqlSessionManager.getSqlSession();
List<Emp> list = sqlSession.selectList("empns.findEmp");
sqlSession.commit();
sqlSession.close();
return list;
}
servlet:(不输出那么多了)
List<Emp> list = EmpDao.findselMap();
for(Emp e:list){
System.out.println(e.getName());
System.out.println(e.getSal().getName());
System.out.println(e.getSal().getMoney());
System.out.println();
}
结果: 不是很懂,有点神奇
高级复杂结果关联映射——方式二 还是以上面功能作为例子,使用两个resultMap完成: emp-mapper.xml
<select id="findEmp2" resultMap="findEmpMap2" statementType="PREPARED">
select emp.id,emp.name,salary.id,salary.name sname,salary.money from emp inner join salary on emp.id = salary.empid
</select>
<resultMap id="findEmpMap2" type="emp" autoMapping="false">
<id column="ID" property="id" javaType="string" jdbcType="VARCHAR"/>
<result column="NAME" property="name" javaType="string" jdbcType="VARCHAR"/>
<association column="EMPID" property="sal" javaType="sal" resultMap="salMap" autoMapping="false"/>
</resultMap>
<resultMap id="salMap" type="sal" autoMapping="false">
<id column="ID" property="id" javaType="string" jdbcType="VARCHAR"/>
<result column="sname" property="name" javaType="string" jdbcType="VARCHAR"/>
<result column="MONEY" property="money" javaType="double" jdbcType="DOUBLE"/>
</resultMap>
dao.java
public static List<Emp> findselMap2(){
SqlSession sqlSession = MySqlSessionManager.getSqlSession();
List<Emp> list = sqlSession.selectList("empns.findEmp2");
sqlSession.commit();
sqlSession.close();
return list;
}
servlet
List<Emp> list = EmpDao.findselMap2();
for(Emp e:list){
System.out.println(e.getName());
System.out.println(e.getSal().getId());
System.out.println(e.getSal().getName());
System.out.println(e.getSal().getMoney());
System.out.println();
}
结果:
动态SQL
if
简单用来拼接:
<select id="queryDeplist" resultType="dept" fetchSize="2">
select * from department
<if test="code != null">
where code = 100
</if>
</select>
choose-when使用
有一张表:
<select id="findChooseResult" resultType="emp">
select * from emp where 1 = 1
<choose>
<when test="AGE != null">and age > 20</when>
<when test="NAME != null">and name like '${叶%}'</when>
<otherwise>
<if test="SEX != null">
and sex = '男'
</if>
</otherwise>
</choose>
</select>
servlet:
List<Emp> list = EmpDao.findChooseResult();
System.out.println(list.get(0).getName());
where
用<where>可以省略手写sql语句中的where 例如,上一个例子的语句可以改写成:
<select id="findChooseResult" resultType="emp">
select * from emp
<where>
<choose>
<when test="AGE != null">and age > 20</when>
<when test="NAME != null">and name like '${叶%}'</when>
<otherwise>
<if test="SEX != null">
and sex = '男'
</if>
</otherwise>
</choose>
</where>
</select>
|