Blog of Bruce: 動態SQL Mybatis3.x(四)

※動態SQL

有個要注意的地方
要判斷大小用"<"和">"，但"<"在XML有特殊的意義，它會以為是標籤，如<h1><head><configuration>…等，有兩種方式可解決

一．CDATA

<![CDATA[
程式碼…
]]>
這種方式是包起來的部分不會對它做認何的轉譯之類的

二．實體參照

如 就是空格，"就是"，這裡有較詳細的表可參考

----------------------------------------------------------------------------------------------------------
以下的標籤都是用這個且標籤一定要是小寫
<select id="testDynamicSQL" parameterType="Chess" resultMap="ChessInterface" >
</select>
測試類如下：

System.out.println("----------Chess.testDynamicSQL----------");
Chess chess = new Chess();
//    chess.setChessNo(12);
//    chess.setPrice(50);

List<Chess> listChess = sqlSession.selectList("Chess.testDynamicSQL", chess);
for(Chess c:listChess){
    System.out.println("chessNo=" + c.getChessNo());
    System.out.println("name=" + c.getName());
    System.out.println("price=" + c.getPrice());
    System.out.println("productDate=" + c.getProductDate());
    System.out.println("---------------------------------");
}

※<if>和<choose><when><otherwise>

以java的角度看if就是if沒有else；

choose裡面包when和otherwise，when就是if，otherwise就是else

SELECT
    <include refid="column" />
FROM CHESS
WHERE 1=1
<if test="chessNo != null">
    AND CHESS_NO = #{chessNo}
</if>
<if test="name != null">
    AND NAME = #{name}
</if>
<if test="price &lt; 30">
    AND PRICE = #{price}
</if>
<if test="productDate != null">
    AND PRODUCT_DATE = #{productDate}
</if>
    ORDER BY PRICE



SELECT
    <include refid="column" />
FROM CHESS
WHERE 1=1

<choose>
    <when test="chessNo != null">
        AND CHESS_NO = #{chessNo}
    </when>
    <when test="name != null">
        AND NAME = #{name}
    </when>
    <otherwise>
        AND PRICE = #{price}
    </otherwise>
</choose>
ORDER BY PRICE

※<where>和<trim>和<set>

上個例子的WHERE 1=1看起來看怪，所以mybatis提供一個自動幫我們判斷需不需要WHERE，也能智慧的去掉AND、OR的功能，以上面的例子就可以這樣寫：

SELECT
    <include refid="column" />
FROM CHESS
<WHERE>
    <if test="chessNo != null">
        AND CHESS_NO = #{chessNo}
    </if>
    <if test="name != null">
        AND NAME = #{name}
    </if>
    <if test="price &lt; 30">
        AND PRICE = #{price}
    </if>
    <if test="productDate != null">
        AND PRODUCT_DATE = #{productDate}
    </if>
</WHERE>
ORDER BY PRICE


SELECT
    <include refid="column" />
FROM CHESS
<WHERE>
    <choose>
        <when test="chessNo != null">
            AND CHESS_NO = #{chessNo}
        </when>
        <when test="name != null">
            AND NAME = #{name}
        </when>
        <otherwise>
            AND PRICE = #{price}
        </otherwise>
    </choose>
</WHERE>
ORDER BY PRICE

如果有特殊須求可以用<trim>

SELECT
    <include refid="column" />
FROM CHESS
<trim prefix="WHERE" prefixOverrides="ORDER BY PRICE|AND|OR">
    <choose>
        <when test="chessNo != null">
            AND CHESS_NO = #{chessNo}
        </when>
        <when test="name != null">
            AND NAME = #{name}
        </when>
        <otherwise>
            ORDER BY PRICE 1=1
        </otherwise>
    </choose>
</trim>

prefix就是在最前面增加什麼；
prefixOverrides就是將最前面的什麼取代成空，有順序問題，最前面找到了，就先取代
還有suffix和suffixOverrides就是在最後面增加什麼，和將最後面的什麼取代成空

而set是配合update的，如下：

<update id="update" parameterType="Chess">
    UPDATE CHESS
    <set>
        <if test="chessNo != null">
            NAME = #{name},
        </if>
        <if test="price != null">
            PRICE = #{price},
        </if>
    </set>
    <where>
        CHESS_NO = #{chessNo}
    </where>
</update>

會幫我們判斷最後面的逗點，會把他變不見
所以<where>就等於
<trim prefix="WHERE" prefixOverrides="AND |OR "></trim>
而<set>就等於
<trim prefix="SET" suffixOverrides=","></trim>
所以<where>和<set>，其實就是<trim>分出來的，可能因為常用的關係

<bind>

Dept d = new Dept();
d.setdName("O");
List<Dept> bind1 = mapper.getDeptsByDeptUseBind(d);
bind1.stream().forEach(x -> {
    System.out.println(x.getdName());
});
    
Map<String, String> map = new HashMap<>();
map.put("kkk", "O");
List<Dept> bind2 = mapper.getDeptsByMapUseBind(map);
bind2.stream().forEach(x -> {
    System.out.println(x.getdName());
});

※參數給 java bean 或 Map 都可以

<select id="getDeptsByDeptUseBind" resultType="mp.bean.Dept" parameterType="mp.bean.Dept">
    <!-- <bind name="xxx" value="'%' + dName + '%'"/> -->
    <bind name="xxx" value="'%' + _parameter.getdName() + '%'" />
    select * from dept 
    where dname like #{xxx}
</select>
    
<select id="getDeptsByMapUseBind" resultType="mp.bean.Dept" parameterType="mp.bean.Dept">
    <!-- <bind name="xxx" value="'%' + kkk + '%'"/> -->
    <bind name="xxx" value="'%' + _parameter.kkk + '%'" />
    select * from dept 
    where dname like #{xxx}
</select>

※ _parameter 是內鍵的

※這兩種方式都有兩種用法，其中 dName 是 java bean 的名稱

※用 Map 時，_parameter 必須要.key 的名稱

※<foreach>

主要是給IN使用的，如下：

SELECT
    <include refid="column" />
FROM CHESS
<where>
    PRICE IN
    <foreach item="item" index="index" collection="list" open="(" separator="," close=")">
        #{item}
    </foreach>
</where>

．item為foreach裡要使用的變數名稱
．index索引，我沒加也ok，可能預設會幫我們加，會自動從0開始
．collection有 list、array、Map 的 key 名稱、@Param 裡的名稱，Set 一樣給 list
※1.但 list、array 如果使用 @Param，變數就只能是 @Param的名稱或 param1，統一用@Param ，XML 就不用改了
2.Map 如果使用 @Param，變數就只能是 @Param的名稱.key名稱或 param1.key名稱
．open開始時用什麼符號開始，IN當然是「(」
．close結束時用什麼符號結束，IN當然是「)」
．separator分開始用什麼符號，IN當然是「,」，如果寫在foreach裡，最後會多一個,，所以這個功能會幫我們判斷並刪除
測試類：

List<Integer> l = new ArrayList<>();
l.add(30);
l.add(40);
l.add(50);
List<Chess> listChess = sqlSession.selectList("Chess.testDynamicSQL", l);

for(Chess c:listChess){
    System.out.println("chessNo=" + c.getChessNo());
    System.out.println("name=" + c.getName());
    System.out.println("price=" + c.getPrice());
    System.out.println("productDate=" + c.getProductDate());
    System.out.println("---------------------------------");
}

如果collection想給array，就改第二個參數就可以了

List<Chess> listChess = sqlSession.selectList("Chess.testDynamicSQL", new Integer[]{30,40,50});

如果collection和你傳的參數不匹配, 就會出Parameter 'list' not found.的錯，''裡面是你在collection裡打的字

還可以放map：

List<Integer> l = new ArrayList<>();
l.add(30);
l.add(40);
l.add(50);

Map<String, List<Integer>> map = new HashMap<>();
map.put("xxx", l);

List<Chess> listChess = sqlSession.selectList("Chess.testDynamicSQL", map);
for(Chess c:listChess){
    System.out.println("chessNo=" + c.getChessNo());
    System.out.println("name=" + c.getName());
    System.out.println("price=" + c.getPrice());
    System.out.println("productDate=" + c.getProductDate());
    System.out.println("---------------------------------");
}

記得collection也要給map的變數xxx，不然會出The expression 'list' evaluated to a null value.的錯

※證明index從0開始

我的資料庫有如下資料：

List重點是從0~9跑了10次迴圈，裡面放什麼不重要

List<Integer> l = new ArrayList<>();
for(int i=0; i<10; i++){
    l.add(30);
}
List<Chess> listChess = sqlSession.selectList("Chess.testDynamicSQL", l);
for(Chess c:listChess){
    System.out.println("chessNo=" + c.getChessNo());
    System.out.println("name=" + c.getName());
    System.out.println("price=" + c.getPrice());
    System.out.println("productDate=" + c.getProductDate());
    System.out.println("---------------------------------");
}

因為要用到index, 我給ooo，不打不行，我想說預設值應該是index，結果也不行，反正要用到就打就對了

SELECT
    <include refid="column" />
FROM CHESS
<where>
    CHESS_NO IN
    <foreach item="item" index="ooo" collection="list" open="(" separator="," close=")">
        #{ooo}
    </foreach>
</where>

結果：
----------Chess.testDynamicSQL----------
chessNo=7
name=象棋
price=50
productDate=Sat Jul 18 16:01:37 CST 2015
---------------------------------
chessNo=8
name=西洋棋
price=120
productDate=Sat Jul 18 16:02:20 CST 2015
---------------------------------
chessNo=9
name=跳棋
price=30
productDate=Sat Jul 18 16:03:10 CST 2015
---------------------------------

chessNo我的資料庫有10的沒顯示出來，我的迴圈跑10次，那就表示它一定是從0開始跑，跑到9，所以9有跑出來
index本身是數字，所以裡面還可以運算，我有在裡面加1試試，還真的10就跑出來了

※大量增刪改

Oracle：

Emp e1 = new Emp();
e1.setEname("ccc");
e1.setDeptno(20);
Emp e2 = new Emp();
e2.setEname("ddd");
e2.setDeptno(30);
List<Emp> list = new ArrayList<>();
list.add(e1);
list.add(e2);
    
mapper.bigInsert1(list);
//mapper.bigInsert2(list);
//mapper.bigUpdate(Arrays.asList("xxx", "ooo"));
//mapper.bigDelete(Arrays.asList("xxx", "ooo"));
mapper.getEmp().stream().forEach(x -> {
    System.out.println(x.getEname());
});

※

public void bigInsert1(@Param("emp") List<Emp> list);
public void bigInsert2(@Param("emp") List<Emp> list);
public void bigUpdate(List<String> list);
public void bigDelete(List<String> list);

※

<insert id="bigInsert1">
    <foreach collection="emp" open="begin" close="end;" item="e">
        insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
        values(emp_seq.nextval, #{e.ename}, #{e.job}, #{e.mgr}, #{e.hiredate}, #{e.sal}, #{e.comm}, #{e.deptno});
    </foreach>
</insert>
    
<insert id="bigInsert2">
    insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno)
    <foreach collection="emp" close=")" item="e" separator="union all"
        open="select emp_seq.nextval, ename, job, mgr, hiredate, sal, comm, deptno from(">
    
        select #{e.ename} ename, #{e.job} job, #{e.mgr} mgr, #{e.hiredate} hiredate, 
               #{e.sal} sal, #{e.comm} comm, #{e.deptno} deptno
        from dual
    </foreach>
</insert>
    
<update id="bigUpdate">
    <foreach collection="list" item="v" open="begin" close="end;">
        update emp
        <set>
            ename = #{v}
        </set>
        <where>
            ename in('aaa', 'bbb');
        </where>
    </foreach>
</update>
    
<delete id="bigDelete">
    delete from emp
    <where>
        ename in
        <foreach collection="list" item="v" separator="," open="(" close=")">
            #{v}
        </foreach>
    </where>
</delete>

※簡單的新增和查詢都必需在最前加上 begin，和最後加上 end; 否則報錯

※第二種新增看起來較複雜，以下是 SQL
insert into table_name(id, f1, f2)
    select emp_seq.nextval, f1, f2 from (
        select 'xxx1' f1, 'ttt1' f2 from dual
        union all
        select 'xxx2' f1, 'ttt2' f2 from dual
        union all
        select 'xxx3' f1, 'ttt3' f2 from dual
    )

MySQL：

mysql 可以不用 begin end; 即可新增，但必須在 jdbc.url 後加東西，jdbc.url=jdbc:mysql://localhost:3306/test?allowMultiQueries=true

另外還有一種是 insert into () values(),(),()，Oracle 沒有

<insert id="bigInsert1">
    <foreach collection="emps" item="emp" separator=";">
        insert into emp(t1,t2,t3) values
        (#{emp.f1},#{emp.f2},#{emp.f3},#{emp.f4})
    </foreach>
</insert>
    
<insert id="bigInsert2">
    insert into emp(t1,t2,t3) values
    <foreach collection="emps" item="emp" separator=",">
        (#{emp.f1},#{emp.f2},#{emp.f3})
    </foreach>
</insert>

Blog of Bruce

2015年7月19日星期日

動態SQL Mybatis3.x(四)

※動態SQL

一．CDATA

二．實體參照

※<if>和<choose><when><otherwise>

※<where>和<trim>和<set>

<bind>

※<foreach>

※證明index從0開始

※大量增刪改

Oracle：

MySQL：

沒有留言:

張貼留言

關於我自己

網誌存檔

2015年7月19日 星期日

動態SQL Mybatis3.x(四)

※動態SQL

一．CDATA

二．實體參照

※<if>和<choose><when><otherwise>

※<where>和<trim>和<set>

<bind>

※<foreach>

※證明index從0開始

※大量增刪改

Oracle：

MySQL：

沒有留言:

張貼留言

2015年7月19日星期日