動態sql · JAVA

[TOC] # 簡介基于OGNL表達式來簡化操作. - if - choose(when, otherwise) - trim(where, set) - foreach **注意:xml中特殊符號如",>,<等這些都需要使用轉義字符** # 內置參數 mybatis有兩個內置參數 * `_parameter`: 代表整個參數 - 單個參數: `_parameter`就是這個參數 - 多個參數: 參數會封裝為一個map, `_parameter`代表就是這個map,用.key獲取值 * `_databaseId`: 如果配置了databaseIdProvider標簽,這個就代表別名mysql/oracle這些 ~~~ <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> ~~~ # if標簽 **注意** ~~~ <if test="status !=null and status != '' or status==0"> AND u.status = #{status} </if> status這個字段如果值為0,那么mybatis會默認status為空,就不會有添加里面的AND 語句了, 所有在if里面加上or status ==0 ,就解決了 ~~~ mybatis會把number類型的0當成false，非0為true，類似于JavaScript中`0!=''`結果為false，從而導致=0的時候判斷總是不成立。其實只有String類型才需要判斷`!=''`,number類型根本不需要。 ## 數值 ~~~ <if test="value=0"></if> ~~~ ## 字符串 ~~~ <if test='str!=null and str!="" '></if> ~~~ 記得是外面是單引號，里面是雙引號。 ## boolean 第一種 ~~~ <if test="boolvalue"> //boolvalue=true 時 </if> ~~~ 第二種 ~~~ <if test="boolvalue==true"> //boolvalue=true 時 </if> ~~~ ## 使用 if標簽中不能寫`&&`要寫and sql ~~~  <select id="selectUserBySexAndUsername" parameterType="pojo.User" resultType="pojo.User"> select * from user where 1=1 <if test="sex != null and sex != ''"> sex = #{sex} </if> <if test="username != null and username != ''"> and username = #{username} </if> </select> ~~~ 另外要注意 where 1=1 的作用~! 防止sql拼接問題的`where and` 1. `where 1=1` 以后條件都`and xxx` 2. 用where標簽包裹接口 ~~~ // 根據性別和名稱查詢用戶 public List<User> selectUserBySexAndUsername(User user); ~~~ 測試 ~~~ // 加載核心配置文件 String resource = "SqlMapConfig.xml"; InputStream in = Resources.getResourceAsStream(resource); // 創建SqlSessionFactory SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in); // 創建SqlSession SqlSession sqlSession = sqlSessionFactory.openSession(); // SqlSession幫我生成一個實現類 UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user = new User(); user.setSex("1"); user.setUsername("張小明"); List<User> users = mapper.selectUserBySexAndUsername(user); for (User user2 : users) { System.out.println(user2); } ~~~ # choose(when,otherwise)標簽 **choose、when、otherwise** 類似于Java中的`switch case default` ~~~ <select id="getEmpByChoose" resultType="Emp" parameterType="Emp"> select * from emp where 1 = 1 <choose> <when test="job != null"> and job = #{job} </when> <when test="deptno != null"> and deptno = #{deptno} </when> <otherwise> and mgr = #{mgr} </otherwise> </choose> </select> ~~~ # set標簽 ~~~ <update id="updateEmpBySet" parameterType="Emp"> update emp <set> <if test="ename != null and ename != ''"> ename = #{ename}, </if> <if test="job != null and job != ''"> job = #{job}, </if> </set> where empno = #{empno} </update> ~~~ # trim * prefix：前綴，增加一些指定的內容 * prefixOverrides：前綴重寫，刪除指定的內容 * suffixOverrides：后綴重寫，刪除指定的內容 * suffix：后綴，增加一些指定的內容 **注意4個屬性值的作用位置** 依次是 prefix、prefixOverrides、suffixOverrides、suffix **例子一** ~~~ select * from user <trim prefix="where" prefixOverrides=" and | or " > <if test=" name!=null and name.length()>0 "> and name=#{name} </if> <if test=" gender!=null and gender.length()>0 " > and gender=#{gender} </if> </trim> ~~~ > prefix：在前面增加 where > prefixOverrides：刪除第一個and 或者 or 假如說name和gender的值都不為null的話打印的SQL為： ~~~ select * from user where name = 'xx' and gender = 'xx' ~~~ **例子二** ~~~ update user <trim prefix="set" suffixOverrides="," suffix=" where id = #{id} "> <if test=" name != null and name.length()>0 " > name=#{name} , </if> <if test=" gender != null and gender.length()>0 " > gender=#{gender} , </if> </trim> ~~~ > prefix：在前端增加 set > suffixOverrides：刪除最后一個逗號（也可以是其他的標記，就像是上面前綴中的and一樣） > suffix：在最后面增加 where id = #{id} 假如說name和gender的值都不為null的話打印的SQL為： ~~~ update user set name='xx' , gender='xx' where id='x' ~~~ # where標簽我們把sql改造下 ~~~  <select id="selectUserBySexAndUsername" parameterType="pojo.User" resultType="pojo.User"> select * from user <where> <if test="sex != null and sex != ''"> sex = #{sex} </if> <if test="username != null and username != ''"> and username = #{username} </if> </where> </select> ~~~ # foreach標簽 **配合in的時候,要判斷in里面是不是空** 標簽用于遍歷集合，它的屬性: * collection:代表要遍歷的集合元素，注意編寫時不要寫#{} * open:代表語句的開始部分 * close:代表結束部分 * item:代表遍歷集合的每個元素，生成的變量名 * sperator:代表分隔符 * index: 索引,遍歷list是索引,item是當前值.遍歷map是key,item是值 **一種`List<Object>`** 向sql傳遞數組或list,mybatis使用foreach解析根據多個id查詢用戶信息,查詢sql,select * from user where id in (1,10,24) 接口 ~~~ public List<Orders> selectUserByIds(QueryVo vo); ~~~ QueryVo這個pojo類中添加 ~~~ List<Integer> idsList; public List<Integer> getIdsList() { return idsList; } public void setIdsList(List<Integer> idsList) { this.idsList = idsList; } ~~~ sql ~~~ <sql id="selector"> select * from orders </sql>  <select id="selectUserByIds" parameterType="pojo.QueryVo" resultType="pojo.Orders"> <include refid="selector" /> <where> id in <foreach collection="idsList" item="id" separator="," open="(" close=")" index="i"> #{id} </foreach> </where> </select> ~~~ 或者寫成這樣 ~~~ <select id="selectUserByIds" parameterType="pojo.QueryVo" resultType="pojo.Orders"> <include refid="selector" /> <where> <if test="idList != null and idList.size()>0"> <foreach collection="idsList" item="id" separator="," open="and id in (" close=")"> #{id} </foreach> </if> </where> </select> ~~~ 測試 ~~~ // 加載核心配置文件 String resource = "SqlMapConfig.xml"; InputStream in = Resources.getResourceAsStream(resource); // 創建SqlSessionFactory SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in); // 創建SqlSession SqlSession sqlSession = sqlSessionFactory.openSession(); // SqlSession幫我生成一個實現類 // UserMapper mapper = sqlSession.getMapper(UserMapper.class); OrderMapper mapper = sqlSession.getMapper(OrderMapper.class); List<Integer> ids = new ArrayList<>(); ids.add(3); ids.add(4); QueryVo vo = new QueryVo(); vo.setIdsList(ids); List<Orders> rel = mapper.selectUserByIds(vo); for (Orders order : rel) { System.out.println(order); } ~~~ **另一種Array** 寫接口 ~~~ public List<Orders> selectOrdersByIds(Integer[] ids); ~~~ 配置文件 ~~~  <select id="selectOrdersByIds" parameterType="pojo.QueryVo" resultType="pojo.Orders"> <include refid="selector" /> <where> id in <foreach collection="array" item="id" separator="," open="(" close=")"> #{id} </foreach> </where> </select> ~~~ 這邊要寫array的測試 ~~~ // 加載核心配置文件 String resource = "SqlMapConfig.xml"; InputStream in = Resources.getResourceAsStream(resource); // 創建SqlSessionFactory SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in); // 創建SqlSession SqlSession sqlSession = sqlSessionFactory.openSession(); // SqlSession幫我生成一個實現類 OrderMapper mapper = sqlSession.getMapper(OrderMapper.class); Integer[] ids = new Integer[3]; ids[0] = 3; ids[1] = 4; ids[2] = 5; List<Orders> rel = mapper.selectOrdersByIds(ids); for (Orders order : rel) { System.out.println(order); } ~~~ # bind bind 元素可以從 OGNL 表達式中創建一個變量并將其綁定到上下文。比如: ~~~ <select id="selectBlogsLike" resultType="Blog"> <bind name="pattern" value="'%' + _parameter.getTitle() + '%'" /> SELECT * FROM BLOG WHERE title LIKE #{pattern} </select> ~~~ 值是在value取,取出拼接好,name那做個別名,然后下面就可以用這個別名. 可能這個`value->name->sql的#{}`很多人不適應這個順序. # sql片段 ~~~  <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> ~~~