发布于2021-06-12 14:17 阅读(451) 评论(0) 点赞(21) 收藏(1)
动态sql,顾名思义,就是动态的在xml中拼接sql语句。
- <select id="getUserById" resultType="com.guor.bean.User">
- SELECT * FROM user
- <where>
- <if test="position != null">
- AND position like #{position}
- </if>
- <if test="school != null">
- AND school like #{school}
- </if>
- </where>
- </select>
注:where元素只有至少一个条件存在时,才会有where,而且,如果开头是and或者or时,where元素会自动将其舍弃。
其实where也可以用trim来改写:
- <select id="getUserById" resultType="com.guor.bean.User">
- SELECT * FROM user
- <trim prefix="WHERE" prefixOverrides="AND |OR ">
- <if test="position != null">
- AND position like #{position}
- </if>
- <if test="school != null">
- AND school like #{school}
- </if>
- </trim>
- </select>
set元素会动态前置set关键字,同时也会删除无关的逗号。
- <update id="updateUserById">
- update user
- <set>
- <if test="username != null">username=#{username},</if>
- <if test="password != null">password=#{password},</if>
- <if test="email != null">email=#{email},</if>
- <if test="address != null">ddress=#{ddress}</if>
- </set>
- where id=#{id}
- </update>
set也可以用trim进行改写:
- <update id="updateUserById">
- update user
- <trim prefix="SET" suffixOverrides=",">
- <if test="username != null">username=#{username},</if>
- <if test="password != null">password=#{password},</if>
- <if test="email != null">email=#{email},</if>
- <if test="address != null">ddress=#{ddress}</if>
- </trim>
- where id=#{id}
- </update>
- <select id="getUserById" resultType="com.guor.bean.User">
- SELECT * FROM user WHERE 1 = 1
- <if test="position != null">
- AND position like #{position}
- </if>
- </select>
- <select id="getUsersById" resultType="com.guor.bean.User">
- SELECT * FROM user WHERE 1 = 1
- <choose>
- <when test="position != null">
- AND position like #{position}
- </when>
- <when test="department != null and department.name != null">
- AND department_name like #{department.name}
- </when>
- <otherwise>
- AND department_name = '公司'
- </otherwise>
- </choose>
- </select>
- <select id="selectUserById" resultType="com.guor.bean.User">
- SELECT id,name,age,sex,address,school FROM user u
- WHERE id in
- <foreach item="item" index="index" collection="list"
- open="(" separator="," close=")">
- #{item}
- </foreach>
- </select>
当使用可迭代对象或者数组时,index 是当前迭代的次数,item 的值是本次迭代获取的元素。
当使用 Map 对象(或者 Map.Entry 对象的集合)时,index 是键,item 是值。
- <select id="selectUserByDept" resultType="com.guor.bean.User">
- <bind name="dept" value="'%' + _department.getId() + '%'" />
- SELECT * FROM user
- WHERE dept_id = #{dept}
- </select>
- <insert id="getCurrentTime">
- <if test="_databaseId == 'oracle'">
- select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual
- </if>
- <if test="_databaseId == 'mysql'">
- select now() as Systemtime
- </if>
- </insert>
往期精彩内容:
原文链接:https://blog.csdn.net/guorui_java/article/details/117792065
作者:天花灯
链接:http://www.javaheidong.com/blog/article/222118/34a6e0c976bc7b951a7a/
来源:java黑洞网
任何形式的转载都请注明出处,如有侵权 一经发现 必将追究其法律责任
昵称:
评论内容:(最多支持255个字符)
---无人问津也好,技不如人也罢,你都要试着安静下来,去做自己该做的事,而不是让内心的烦躁、焦虑,坏掉你本来就不多的热情和定力
Copyright © 2018-2021 java黑洞网 All Rights Reserved 版权所有,并保留所有权利。京ICP备18063182号-2
投诉与举报,广告合作请联系vgs_info@163.com或QQ3083709327
免责声明:网站文章均由用户上传,仅供读者学习交流使用,禁止用做商业用途。若文章涉及色情,反动,侵权等违法信息,请向我们举报,一经核实我们会立即删除!