mybatis使用trim自定义动态sql字符串截取

1、新建一个员工的mapper。package com.gwolf.dao;import java.util.Map;import org.apache.ibatis.annotations.MapKey;import com.gwolf.bean.Employee;public interface EmployeeMapper { public 返回的对象 getObjectById(Integer id); }

2、现在我们的动态sql语句是这样的。<select id="getObjectById" resultMap="MyEmpByStep" databaseId="mysql"> select * from tbl_emp <where> <if test="empId!=null and empId!=''"> emp_id = #{empId} and </if> <if test="empName!=null and empName!=''"> emp_name = #{empName} </if> </where> </select>

3、上述动态sql假如我们只要员工id的话sql是有错误的。接下来我们将动态sql放在trim标签中,trim标签能够去掉后面多出的and或者or。<select id="getObjectById" resultMap="MyEmpByStep" databaseId="mysql"> select * from tbl_emp <trim prefix="" prefixOverrides="" suffix=""> <if test="empId!=null and empId!=''"> emp_id = #{empId} and </if> <if test="empName!=null and empName!=''"> emp_name = #{empName} </if> </trim> </select>

4、<trim prefix="" prefixOverrides="" suffix="">中prefix标识给拼完后的整个字符串加一个前缀。<select id="getObjectById" resultMap="MyEmpByStep" databaseId="mysql"> select * from tbl_emp <trim prefix="where" prefixOverrides="" suffix=""> <if test="empId!=null and empId!=''"> emp_id = #{empId} and </if> <if test="empName!=null and empName!=''"> emp_name = #{empName} </if> </trim> </select>

5、<trim prefix="" prefixOverrides="" suffix="">中prefixOverrides表示去掉这个字符串前面多余的字符串。我们这些不需要去除。可以不要写这个属性。<select id="getObjectById" resultMap="MyEmpByStep" databaseId="mysql"> select * from tbl_emp <trim prefix="where" suffix=""> <if test="empId!=null and empId!=''"> emp_id = #{empId} and </if> <if test="empName!=null and empName!=''"> emp_name = #{empName} </if> </trim> </select>

6、<trim prefix="" prefixOverrides="" suffixOverrides="and">中suffixOverrides标识给拼串后的整个字符串加一个后缀。我们这里是去掉and。<select id="getObjectById" resultMap="MyEmpByStep" databaseId="mysql"> select * from tbl_emp <trim prefix="where" suffixOverrides="and"> <if test="empId!=null and empId!=''"> emp_id = #{empId} and </if> <if test="empName!=null and empName!=''"> emp_name = #{empName} </if> </trim> </select>