MyBatis动态SQL

关键字

if

where

trim

foreach

set

【if】

<select id="searchEmployees" parameterType="Map" resultMap="EmployeeResult">select * from t_employeewhere 1=1<if test="name!=null">and name like #{name}</if><if test="age!=nulll">and age=#{age}</if></select>

@Testpublic void testIf() {logger.info("测试动态SQL-if");Map<String,Object> map=new HashMap<String,Object>();//map.put("deptId", 2);//动态拼接//map.put("name", "%毛%");//动态拼接map.put("age", 24);//动态拼接List<Employee> employeeList=employeeMapper.searchEmployees(map);for(Employee emp:employeeList){logger.info(emp.getId()+" "+emp.getName()+" "+emp.getAge());}}

【where】

1，自动加上where；
2，如果where 子句以and 或者or 开头，则自动删除第一个and 或者or；

<select id="searchEmployees" parameterType="Map" resultMap="EmployeeResult">select * from t_employee<where><if test="deptId!=null">and dept_id like #{deptId}</if><if test="name!=null">and name like #{name}</if><if test="age!=nulll">and age=#{age}</if></where></select>

@Testpublic void testIf() {logger.info("测试动态SQL-where");Map<String,Object> map=new HashMap<String,Object>();map.put("deptId", 2);//动态拼接//map.put("name", "%毛%");//动态拼接map.put("age", 24);//动态拼接List<Employee> employeeList=employeeMapper.searchEmployees(map);for(Employee emp:employeeList){logger.info(emp.getId()+" "+emp.getName()+" "+emp.getAge());}}

【trim】

功能和where 元素类似，提供了前缀，后缀功能，更加灵活；

<select id="searchEmployees" parameterType="Map" resultMap="EmployeeResult">select * from t_employee<trim prefix="where" prefixOverrides="and|or"><if test="deptId!=null">and dept_id like #{deptId}</if><if test="name!=null">and name like #{name}</if><if test="age!=nulll">and age=#{age}</if></trim></select>

【foreach】

<select id="searchEmployees" parameterType="Map" resultMap="EmployeeResult">select * from t_employee<if test="deptIds!=null"><where>dept_id in <foreach item="deptId" collection="deptIds" open="(" separator="," close=")">#{deptId}</foreach></where></if></select>

@Testpublic void testForeach() {logger.info("测试动态SQL-foreach");Map<String,Object> map=new HashMap<String,Object>();List<Integer> deptIds=new ArrayList<Integer>();deptIds.add(2);deptIds.add(1);map.put("deptIds", deptIds);//动态拼接//map.put("name", "%毛%");//动态拼接map.put("age", 24);//动态拼接List<Employee> employeeList=employeeMapper.searchEmployees(map);for(Employee emp:employeeList){logger.info(emp.getId()+" "+emp.getName()+" "+emp.getAge());}}

【set】

1，自动加上set；
2，自动剔除最后一个逗号“，”；

<update id="updateEmployee" parameterType="Employee" >update t_employee<set><if test="name!=null">name=#{name},</if><if test="age!=null">age=#{age},</if></set>where id=#{id}</update>

@Testpublic void testUpdateEmployee() {logger.info("测试动态SQL-set");Employee employee=new Employee();employee.setId(4);employee.setName("毛毛");int ret=employeeMapper.updateEmployee(employee);sqlSession.commit();//提交事务System.out.println("受影响行数"+ret);}

分页的实现：

<select id="findEmployees" parameterType="Map" resultMap="EmployeeResult" useCache="true" flushCache="false">select * from t_employee<if test="start!=null and size!=null">limit #{start},#{size}</if></select>

@Testpublic void test3(){logger.info("测试查询雇员List");Map<String,Object> map=new HashMap<String,Object>();map.put("start", 0);map.put("size", 3);List<Employee> employeeList=employeeMapper.findEmployees(map);for(Employee emp:employeeList){System.out.println(emp.getId()+" "+emp.getName()+" "+emp.getAge());}}