Mybatis中动态SQL语句
1.selectKey标签：插入数据时候返回主键
<!-- 插入学生 自动主键--> <insert id="createStudentAutoKey" parameterType="liming.student.manager.data.model.StudentEntity" keyProperty="studentId"> <selectKey keyProperty="studentId" resultType="String" order="BEFORE"> select nextval('student') </selectKey> INSERT INTO STUDENT_TBL(STUDENT_ID, STUDENT_NAME ) VALUES (#{studentId}, #{studentName}) </insert> <insert id="createStudentAutoKey" useGeneratedKeys="true" keyProperty="studentId" parameterType="liming.student.manager.data.model.StudentEntity"> INSERT INTO STUDENT_TBL(STUDENT_ID, STUDENT_NAME ) VALUES (#{studentId}, #{studentName}) </insert>
//Java代码 StudentEntity entity = new StudentEntity(); entity.setStudentName("黎明你好"); entity.setStudentSex(1); entity.setStudentBirthday(DateUtil.parse("1985-05-28")); entity.setClassId("20000001"); entity.setPlaceId("70000001"); this.dynamicSqlMapper.createStudentAutoKey(entity); System.out.println("新增学生ID: " + entity.getStudentId());
2.if 标签
如果传入的参数为null，此语句很可能报错或查询结果为空。此时我们使用if动态sql语句先进行判断，如果值为null或等于空字符串，我们就不进行此条件的判断，增加灵活性。
2.1 if+where标签
“where”标签会知道如果它包含的标签中有返回值的话，它就插入一个‘where’。此外，如果标签返回的内容是以AND 或OR 开头的，则它会剔除掉
<!-- select - where/if(判断参数) - 将实体类不为空的属性作为where条件 --> <select id="getStudentList_whereIf" resultMap="resultMap_studentEntity" parameterType="liming.student.manager.data.model.StudentEntity"> SELECT ST.STUDENT_ID, ST.STUDENT_NAME FROM STUDENT_TBL ST <where> <if test="studentName !=null "> ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName, jdbcType=VARCHAR}),'%') </if> <if test="studentSex != null and studentSex != '' "> AND ST.STUDENT_SEX = #{studentSex, jdbcType=INTEGER} </if> </where> </select>
2.2 if+set标签
使用set标签可以将动态的配置SET 关键字，和剔除追加到条件末尾的任何不相关的逗号
<!-- if/set(判断参数) - 将实体类不为空的属性更新 --> <update id="updateStudent_if_set" parameterType="liming.student.manager.data.model.StudentEntity"> UPDATE STUDENT_TBL <set> <if test="studentName != null and studentName != '' "> STUDENT_TBL.STUDENT_NAME = #{studentName}, </if> <if test="studentSex != null and studentSex != '' "> STUDENT_TBL.STUDENT_SEX = #{studentSex}, </if> </set> WHERE STUDENT_TBL.STUDENT_ID = #{studentId}; </update>
3.if+trim代替where/set标签
3.1trim代替where
<!-- if/trim代替where(判断参数) - 将实体类不为空的属性作为where条件 --> <select id="getStudentList_whereIf" resultMap="resultMap_studentEntity" parameterType="liming.student.manager.data.model.StudentEntity"> SELECT ST.STUDENT_ID, ST.STUDENT_NAME FROM STUDENT_TBL ST <trim prefix="WHERE" prefixOverrides="AND|OR"> <if test="studentName !=null "> ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName, jdbcType=VARCHAR}),'%') </if> <if test="studentSex != null and studentSex != '' "> AND ST.STUDENT_SEX = #{studentSex, jdbcType=INTEGER} </if> </trim> </select>
3.2trim代替set
<!-- if/trim代替set(判断参数) - 将实体类不为空的属性更新 --> <update id="updateStudent_if_trim" parameterType="liming.student.manager.data.model.StudentEntity"> UPDATE STUDENT_TBL <trim prefix="SET" suffixOverrides=","> <if test="studentName != null and studentName != '' "> STUDENT_TBL.STUDENT_NAME = #{studentName}, </if> <if test="studentSex != null and studentSex != '' "> STUDENT_TBL.STUDENT_SEX = #{studentSex}, </if> </trim> WHERE STUDENT_TBL.STUDENT_ID = #{studentId} </update>
4.choose（when，otherwise）
有时候我们并不想应用所有的条件，而只是想从多个选项中选择一个。而使用if标签时，只要test中的表达式为true，就会执行if标签中的条件。MyBatis提供了choose 元素。if标签是与(and)的关系，而choose标识或（or）的关系。
choose标签是按顺序判断其内部when标签中的test条件出否成立，如果有一个成立，则choose结束。当choose中所有when的条件都不满则时，则执行otherwise中的sql。类似于Java 的switch 语句，choose为switch，when为case，otherwise则为default。
<!-- choose(判断参数) - 按顺序将实体类第一个不为空的属性作为where条件 --> <select id="getStudentList_choose" resultMap="resultMap_studentEntity" parameterType="liming.student.manager.data.model.StudentEntity"> SELECT ST.STUDENT_ID, ST.STUDENT_NAME FROM STUDENT_TBL ST <where> <choose> <when test="studentName !=null "> ST.STUDENT_NAME LIKE CONCAT(CONCAT('%', #{studentName, jdbcType=VARCHAR}),'%') </when > <when test="studentSex != null and studentSex != '' "> AND ST.STUDENT_SEX = #{studentSex, jdbcType=INTEGER} </when > <otherwise> </otherwise> </choose> </where> </select>
5.foreach
对于动态SQL 非常必须的，主是要迭代一个集合，通常是用于IN 条件。List 实例将使用“list”做为键，数组实例以“array” 做为键。
foreach元素是非常强大的，它允许你指定一个集合，声明集合项和索引变量，它们可以用在元素体内。它也允许你指定开放和关闭的字符串，在迭代之间放置分隔符。这个元素是很智能的，它不会偶然地附加多余的分隔符。
注意：你可以传递一个List实例或者数组作为参数对象传给MyBatis。当你这么做的时候，MyBatis会自动将它包装在一个Map中，用名称在作为键。List实例将会以“list”作为键，而数组实例将会以“array”作为键。
collection ：要遍历的对象，类型：集合或数组；
item ：每次遍历的集合元素；
open ：在所拼接片段前拼接字符串；
separator ：多次遍历后拼接的内容之间的分割符；
close ：在所拼接片段最后追加字符串；
5.1 参数为array的示例
//java代码
public void test_foreach() { String[] classIds = { "20000001", "20000002" }; List<StudentEntity> list = this.dynamicSqlMapper.getStudentListByClassIds_foreach_array(classIds); for (StudentEntity e : list) { System.out.println(e.toString()); } }
//dao层方法 public List<StudentEntity> getStudentListByClassIds_foreach_array(String[] classIds);
<!— foreach(循环array参数) - 作为where中in的条件 --> <select id="getStudentListByClassIds_foreach_array" resultMap="resultMap_studentEntity"> SELECT ST.STUDENT_ID, ST.STUDENT_NAME FROM STUDENT_TBL ST WHERE ST.CLASS_ID IN <foreach collection="array" item="classIds" open="(" separator="," close=")"> #{classIds} </foreach> </select>
5.2 参数为list的示例
//java代码 public void test2_foreach() { ArrayList<String> classIdList = new ArrayList<String>(); classIdList.add("20000001"); classIdList.add("20000002"); List<StudentEntity> list = this.dynamicSqlMapper.getStudentListByClassIds_foreach_list(classIdList); for (StudentEntity e : list) { System.out.println(e.toString()); } }
//dao层方法 public List<StudentEntity> getStudentListByClassIds_foreach_list(List<String> classIdList);
<!-- foreach(循环List<String>参数) - 作为where中in的条件 --> <select id="getStudentListByClassIds_foreach_list" resultMap="resultMap_studentEntity"> SELECT ST.STUDENT_ID, ST.STUDENT_NAME FROM STUDENT_TBL ST WHERE ST.CLASS_ID IN <foreach collection="list" item="classIdList" open="(" separator="," close=")"> #{classIdList} </foreach> </select>
5.3 传入实体类（属性是list或者是array）
需要实现的sql语句：
1 SELECT * FROM USERS WHERE username LIKE '%张%' AND (id =10 OR id =89 OR id=16) 2 SELECT * FROM USERS WHERE username LIKE '%张%' id IN (10,89,16)
实体类：
public class QueryVo{ private User user; private UserCustom userCustom; //传递多个用户id private List<Integer> ids; set()/get() ... }
foreach语句：
<select id="findUserList" parameterType="UserQueryVo" resultType="UserCustom"> SELECT * FROM USER <where> <if test="ids!=null and ids.size>0"> <!-- 使用实现下边的sql拼接： AND (id=1 OR id=10 OR id=16) --> <foreach collection="ids" item="user_id" open="AND (" close=")" separator="or"> id=#{user_id} </foreach> <!-- 使用实现下边的sql拼接： and id IN(1,10,16)—> <foreach collection="ids" item="user_id" open="and id IN(" close=")" separator=","> #{user_id} </foreach> </if> </where> </select>
5.4 传入实体类数组
参数含义：index为数组的下标，item为数组每个元素的名称，名称随意，open循环开始，close循环结束，separator中间分隔输出。
//调用sql语句的方法 1 //构造查询条件List 2 Object[] userlist = new Object[2]; 3 User user = new User(); 4 user.setId(1); 5 userlist[0]=user; 6 7 user = new User(); 8 user.setId(2); 9 userlist[1]=user; 10 11 //传递user对象查询用户列表 12 List<User>list = userMapper.selectUserByArray(userlist);
//dao层接口 public List<User> selectUserByArray(Object[] userlist)
<!-- 遍历实体类数组中id的sql语句 --> <select id="selectUserByArray" parameterType="Object[]" resultType="user"> select * from user <where> <!-- 传递pojo类数组 --> <if test="array!=null"> <foreach collection="array" index="index" item="item" open="and id in("separator=","close=")"> #{item.id} </foreach> </if> </where> </select>
6. 自定义 sql
<!-- 自定义 --> <sql id="userColumns"> id,username,password </sql>
<!-- 调用sql --> <select id="selectUsers" parameterType="int" resultType="hashmap"> select <include refid="userColumns"/> from some_table where id = #{id} </select>
