MyBatis映射文件
1、增删查改
<?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="com.qut.mybatis.dao.EmployeeMapper">
<select id="getEmpId" resultType="employee" >
select * from tbl_employee where id = #{id}
</select>
<insert id="addEmp" parameterType = "com.qut.mybatis.bean.Employee" useGeneratedKeys = "true" keyProperty = "id">
insert into tbl_employee(lastName,email,gender)
values(#{lastName},#{email},#{gender})
</insert>
<update id = "updateEmp">
update tbl_employee
set lastName = #{lastName},email = #{email},gender = #{gender}
where id = #{id}
</update>
<delete id = "deleteEmpById">
delete from tbl_employee where id = #{id}
</delete>
</mapper>
2、insert获取自增主键
<insert id="addEmp" parameterType = "com.qut.mybatis.bean.Employee" useGeneratedKeys = "true" keyProperty = "id">
insert into tbl_employee(lastName,email,gender)
values(#{lastName},#{email},#{gender})
</insert>
keyProperty属性指定数值放到Employee的id属性中
3、insert获取非自增主键
这里我们使用的是oracle数据库的序列生成主键
<insert id="addEmp_oracle_before" parameterType = "com.qut.mybatis.bean.Employee">
<selectKey keyProperty = "id" order= "BEFORE" resultType = "Integer">
select EMPLOYEE_SEQ.nextval from dual
</selectKey>
insert into tbl_employee(id,lastName,email,gender) values(#{id},#{lastName},#{email},#{gender})
</insert>
4、mybatis参数处理
1)单个参数:mybatis不会做特殊处理,使用 #{参数名}取出参数;即使参数名不对应也无所谓。
2)多个参数:mybatis对于多个参数进行处理,将多个参数封装成一个map,#{}就是从map中取值。#{param1}、#{param2}
我们推荐使用使用命名参数来明确指定封装成map的key,方式如下:
public Employee getTmpByIdAndLastName(@Param("id") Integer id,@Param("lastName") String lastName);
此时可以使用#{id}来获取参数值。
除此之外
如果多个参数正好是业务逻辑的数据模型,直接传递pojo对象,#{属性名} 是pojo的属性名。
如果多个参数不是业务逻辑的数据模型,不经常使用,为了方便可以传入map。
#{属性名} 取出map中对应的值
public Employee getTmpByMap(Map<String,Integer> map)
如果多个参数不是业务模型,但是经常使用,推荐创建一个TO(Transfer Object)对象。
特别注意的是,Collection类型或者是数组mybatis会进行特殊处理,会将list或者数组封装在map中
Key:Collection(collection),如果是List还可以使用key(list),数组key(array)
public Employee getTmpByMap(List<Integer> ids)
取出第一个id的值#{list[0]}
5、参数值的获取
#{} 与 ${}
区别:#以预编译的形式将参数设置到语句中,$取出的值直接拼装在sql语句中,会有安全问题不能防止sql注入。
大多数情况我们使用#去取得参数的值,但是对于一下情况我们使用$去获取参数的值
分表操作:select * from salary_2016 ==> 不能预编译
原生sql不支持占位符的时候就可以使用$进行取值
分表操作:select * from salary_${year}
6、select返回List(不需要指定返回值类型为List)
public List<Employee> getEmpsByLastName(String lastName);
<select id = "getEmpsByLastName" resultType = "com.qut.mybatis.bean.Employee">
select * from tbl_employee where lastName like #{id}
</select>
7、select返回Map
//Map key:字段名 value:属性值
public Map<String ,Object> getEmpByIdReturnMap(Integer id);
<select id = "getEmpByIdReturnMap" resultType = "map">
select * from tbl_employee where id = #{id}
</select>
//Map key:id value:employee对象
@MapKey("id")//告诉mybatis封装map使用那个属性作为主键
public Map<Integer,Employee> getEmpByLastNameReturnMap(String lastName);
<select id = "getEmpByLastNameReturnMap" resultType = "com.qut.mybatis.bean.Employee">
select * from tbl_employee where lastName like #{id}
</select>
8、select resultMap自定义映射规则
public Employee getEmpById(Integer id);
<!-- 自定义javaBean的封装规则,type:自定义的java类型,id:-方便引用 -->
<resultMap type="com.qut.mybatis.bean.Employee" id="MyEmp">
<!-- 定义主键 -->
<id column = "id" property = "id"/>
<result column = "lastName" property = "lastName"/>
<!-- 其他不指定的列,会自动封装 -->
</resultMap>
<select id ="getEmpById" resultMap = "MyEmp">
select * from tbl_employee where id = #{id}
</select>
9、select resultMap关联查询,属性封装
1)employee类
public class Employee {
private Integer id;
private String lastName;
private String email;
private String gender;
private Department dept;
}
2)department类
public class Department {
private Integer id;
private String DepartmentName;
}
3)自定义映射规则
<!-- 联合查询的记录使用级联属性进行封装 -->
<resultMap type="com.qut.mybatis.bean.Employee" id="MyEmpPlus">
<id column = "id" property = "id"/>
<result column = "lastName" property = "lastName"/>
<result column ="gender" property = "gender"/>
<result column = "d_id" property = "dept.id"/>
<result column = "dept_name" property = "dept.DepartmentName"/>
</resultMap>
4)
<select id = "getEmpAndDept" resultMap = "MyEmpPlus">
select * from tbl_employee ,tbl_dept where tbl_employee.d_id = tbl_dept.id AND tbl_employee.id = #{id};
</select>
10、使用association指定联合的javabean对象
<resultMap type="com.qut.mybatis.bean.Employee" id="MyEmpAss">
<id column = "id" property = "id"/>
<result column = "lastName" property = "lastName"/>
<result column ="gender" property = "gender"/>
<association property = "dept" javaType = "com.qut.mybatis.bean.Department">
<id column = "d_id" property = "id"/>
<result column = "dept_name" property = "DepartmentName"/>
</association>
</resultMap>
11、association分步查询以及延迟加载
1)分步查询
//select:表明当前属性是调用select这方法查处的结果
//column:将指定的哪一列的值传给这个方法
<resultMap type="com.qut.mybatis.bean.Employee" id="MyEmpAss">
<id column = "id" property = "id"/>
<result column = "lastName" property = "lastName"/>
<result column ="gender" property = "gender"/>
<association property = "dept" select = "com.qut.mybatis.bean.DepartmenMapper.getDeptById" column = "d_id">
</association>
</resultMap>
2)延迟加载==》
Employee中包含dept对象,每次查询employee时,都将dept一起查询处理出来,如果希望部门信息在使用时再去查询,使用延迟加载策略,分步查询加上两个配置可以实现延迟加载(全局配置文件中)。
<settings>
<setting name= "lazyLoadingEnabled" value = "true"></setting>
<setting name = "aggressiveLazyLoading" value = "false"></setting>
</settings>
12、collection查询
1)嵌套结果集
<resultMap type="com.qut.mybatis.bean.Department" id="MyEmpAss">
<id column = "id" property = "id"/>
<result column = "lastName" property = "lastName"/>
<collection property = "emps" ofType = "com.qut.mybatis.bean.Employee">
<id column = "eid" property = "id"/>
<result column = "email" property = "email">
</collection>
</resultMap>
2)分步查询&延迟加载
<collection property = "emps" select = "com.qut.mybatis.bean.DepartmenMapper.XXXX" column = "id" >
</collection>
【注】
1、分步查询时如果传递多列的值
将多列的值封装成map column = "{key1 = column,key2 = column}"
fetchType = "lazy" lazy-延迟加载,eager-立即加载
13、discriminator 鉴别器
mybatis使用discriminator判断某列的值,改变封装行为
<!--
鉴别器discriminator mybatis可以使用discriminator判断某列的值,然后根据某列的值改变封装行为
封装Employee: 如果查出的是女生,就把部门信息查询出来,否则不查询 如果是男生,把last_name这一列的值赋值给email;
-->
<resultMap type="com.cn.zhu.bean.Employee" id="MyEmpDis">
<id column="id" property="id" />
<result column="last_name" property="lastName" />
<!-- 把last_name 赋值给email -->
<result column="email" property="email" />
<result column="gender" property="gender" />
<!--
association 定义管理对象的封装规则 select: 表明当前属性是调用select 指定的方法 colum:
指定将哪一列的值传给这个方法 流程 : 使用select 指定的方法(传入column指定的这列参数的值)查出对象
并封装给property 指定的属性
-->
<!-- column 指定判定的列名
javaType:列值对应的java类型
-->
<discriminator javaType="String" column="gender">
<!--
0 是女生 resultType 指定封装的结果类型 如果是女性,就查询到employee的部门查询出来
-->
<case value="0" resultType="com.cn.zhu.bean.Employee">
<association property="dept"
select="com.cn.mybatis.dao.DepartmentMapper.getDeptById" column="d_id">
</association>
</case>
<!--
1是男生 resultType 指定封装的结果类型 如果是女性,就查询到employee的部门查询出来 把last_name
赋值给email
-->
<case value="1" resultType="com.cn.zhu.bean.Employee">
<id column="id" property="id" />
<result column="last_name" property="lastName" />
<!-- 把last_name 赋值给email -->
<result column="last_name" property="email" />
<result column="gender" property="gender" />
</case>
</discriminator>