mybatis 动态SQL

• if:判断
• choose (when, otherwise):分支选择；带了break的swtich-case
如果带了id就用id查，如果带了lastName就用lastName查;只会进入其中一个
• trim 字符串截取(where(封装查询条件), set(封装修改条件))
• foreach 遍历集合

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260

<?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.atguigu.mybatis.dao.EmployeeMapperDynamicSQL">     <!-- • if:判断 • choose (when, otherwise):分支选择；带了break的swtich-case     如果带了id就用id查，如果带了lastName就用lastName查;只会进入其中一个 • trim 字符串截取(where(封装查询条件), set(封装修改条件)) • foreach 遍历集合      -->      <!-- 查询员工，要求，携带了哪个字段查询条件就带上这个字段的值 -->      <!-- public List<Employee> getEmpsByConditionIf(Employee employee); -->      <select id="getEmpsByConditionIf" resultType="com.atguigu.mybatis.bean.Employee">         select * from tbl_employee         <!-- where -->         <where>             <!-- test：判断表达式（OGNL）             OGNL参照PPT或者官方文档。                  c:if  test             从参数中取值进行判断                           遇见特殊符号应该去写转义字符：             &&：             -->             <if test="id!=null">                 id=#{id}             </if>             <if test="lastName!=null && lastName!=""">                 and last_name like #{lastName}             </if>             <if test="email!=null and email.trim()!=""">                 and email=#{email}             </if>             <!-- ognl会进行字符串与数字的转换判断  "0"==0 -->             <if test="gender==0 or gender==1">                 and gender=#{gender}             </if>         </where>      </select>             <!--public List<Employee> getEmpsByConditionTrim(Employee employee);  -->      <select id="getEmpsByConditionTrim" resultType="com.atguigu.mybatis.bean.Employee">         select * from tbl_employee         <!-- 后面多出的and或者or where标签不能解决         prefix="":前缀：trim标签体中是整个字符串拼串 后的结果。                 prefix给拼串后的整个字符串加一个前缀         prefixOverrides="":                 前缀覆盖： 去掉整个字符串前面多余的字符         suffix="":后缀                 suffix给拼串后的整个字符串加一个后缀         suffixOverrides=""                 后缀覆盖：去掉整个字符串后面多余的字符                           -->         <!-- 自定义字符串的截取规则 -->         <trim prefix="where" suffixOverrides="and">             <if test="id!=null">                 id=#{id} and             </if>             <if test="lastName!=null && lastName!=""">                 last_name like #{lastName} and             </if>             <if test="email!=null and email.trim()!=""">                 email=#{email} and             </if>             <!-- ognl会进行字符串与数字的转换判断  "0"==0 -->             <if test="gender==0 or gender==1">                 gender=#{gender}             </if>          </trim>      </select>             <!-- public List<Employee> getEmpsByConditionChoose(Employee employee); -->      <select id="getEmpsByConditionChoose" resultType="com.atguigu.mybatis.bean.Employee">         select * from tbl_employee         <where>             <!-- 如果带了id就用id查，如果带了lastName就用lastName查;只会进入其中一个 -->             <choose>                 <when test="id!=null">                     id=#{id}                 </when>                 <when test="lastName!=null">                     last_name like #{lastName}                 </when>                 <when test="email!=null">                     email = #{email}                 </when>                 <otherwise>                     gender = 0                 </otherwise>             </choose>         </where>      </select>             <!--public void updateEmp(Employee employee);  -->      <update id="updateEmp">         <!-- Set标签的使用 -->         update tbl_employee         <set>             <if test="lastName!=null">                 last_name=#{lastName},             </if>             <if test="email!=null">                 email=#{email},             </if>             <if test="gender!=null">                 gender=#{gender}             </if>         </set>         where id=#{id} <!--                 Trim：更新拼串         update tbl_employee         <trim prefix="set" suffixOverrides=",">             <if test="lastName!=null">                 last_name=#{lastName},             </if>             <if test="email!=null">                 email=#{email},             </if>             <if test="gender!=null">                 gender=#{gender}             </if>         </trim>         where id=#{id}  -->      </update>             <!--public List<Employee> getEmpsByConditionForeach(List<Integer> ids);  -->      <select id="getEmpsByConditionForeach" resultType="com.atguigu.mybatis.bean.Employee">         select * from tbl_employee         <!--             collection：指定要遍历的集合：                 list类型的参数会特殊处理封装在map中，map的key就叫list             item：将当前遍历出的元素赋值给指定的变量             separator:每个元素之间的分隔符             open：遍历出所有结果拼接一个开始的字符             close:遍历出所有结果拼接一个结束的字符             index:索引。遍历list的时候是index就是索引，item就是当前值                           遍历map的时候index表示的就是map的key，item就是map的值                           #{变量名}就能取出变量的值也就是当前遍历出的元素           -->         <foreach collection="ids" item="item_id" separator=","             open="where id in(" close=")">             #{item_id}         </foreach>      </select>             <!-- 批量保存 -->      <!--public void addEmps(@Param("emps")List<Employee> emps);  -->      <!--MySQL下批量保存：可以foreach遍历   mysql支持values(),(),()语法-->     <insert id="addEmps">         insert into tbl_employee(             <include refid="insertColumn"></include>         )         values         <foreach collection="emps" item="emp" separator=",">             (#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})         </foreach>      </insert><!--   -->             <!-- 这种方式需要数据库连接属性allowMultiQueries=true；         这种分号分隔多个sql可以用于其他的批量操作（删除，修改） -->      <!-- <insert id="addEmps">         <foreach collection="emps" item="emp" separator=";">             insert into tbl_employee(last_name,email,gender,d_id)             values(#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})         </foreach>      </insert> -->             <!-- Oracle数据库批量保存：         Oracle不支持values(),(),()         Oracle支持的批量方式         1、多个insert放在begin - end里面             begin                 insert into employees(employee_id,last_name,email)                 values(employees_seq.nextval,'test_001','test_001@atguigu.com');                 insert into employees(employee_id,last_name,email)                 values(employees_seq.nextval,'test_002','test_002@atguigu.com');             end;         2、利用中间表：             insert into employees(employee_id,last_name,email)                select employees_seq.nextval,lastName,email from(                       select 'test_a_01' lastName,'test_a_e01' email from dual                       union                       select 'test_a_02' lastName,'test_a_e02' email from dual                       union                       select 'test_a_03' lastName,'test_a_e03' email from dual                )         -->      <insert id="addEmps" databaseId="oracle">         <!-- oracle第一种批量方式 -->         <!-- <foreach collection="emps" item="emp" open="begin" close="end;">             insert into employees(employee_id,last_name,email)                 values(employees_seq.nextval,#{emp.lastName},#{emp.email});         </foreach> -->                   <!-- oracle第二种批量方式  -->         insert into employees(             <!-- 引用外部定义的sql -->             <include refid="insertColumn">                 <property name="testColomn" value="abc"/>             </include>         )                 <foreach collection="emps" item="emp" separator="union"                     open="select employees_seq.nextval,lastName,email from("                     close=")">                     select #{emp.lastName} lastName,#{emp.email} email from dual                 </foreach>      </insert>             <!-- 两个内置参数：         不只是方法传递过来的参数可以被用来判断，取值。。。         mybatis默认还有两个内置参数：         _parameter:代表整个参数             单个参数：_parameter就是这个参数             多个参数：参数会被封装为一个map；_parameter就是代表这个map                   _databaseId:如果配置了databaseIdProvider标签。             _databaseId就是代表当前数据库的别名oracle       -->               <!--public List<Employee> getEmpsTestInnerParameter(Employee employee);  -->       <select id="getEmpsTestInnerParameter" resultType="com.atguigu.mybatis.bean.Employee">             <!-- bind：可以将OGNL表达式的值绑定到一个变量中，方便后来引用这个变量的值 -->             <bind name="_lastName" value="'%'+lastName+'%'"/>             <if test="_databaseId=='mysql'">                 select * from tbl_employee                 <if test="_parameter!=null">                     where last_name like #{lastName}                 </if>             </if>             <if test="_databaseId=='oracle'">                 select * from employees                 <if test="_parameter!=null">                     where last_name like #{_parameter.lastName}                 </if>             </if>       </select>               <!--         抽取可重用的sql片段。方便后面引用         1、sql抽取：经常将要查询的列名，或者插入用的列名抽取出来方便引用         2、include来引用已经抽取的sql：         3、include还可以自定义一些property，sql标签内部就能使用自定义的属性                 include-property：取值的正确方式${prop},                 #{不能使用这种方式}       -->       <sql id="insertColumn">             <if test="_databaseId=='oracle'">                 employee_id,last_name,email             </if>             <if test="_databaseId=='mysql'">                 last_name,email,gender,d_id             </if>       </sql>         </mapper>