存储过程和动态SQL（学习笔记）

 

Mysql存储过程，具体可以见

http://blog.sina.com.cn/s/blog_52d20fbf0100ofd5.html

各数据库存储过程的写法略微有些差别，示例是Oracle，找时间再学，这里简单介绍Mybatis中对于存储过程的配置

存储过程       （不完全，INOUT）

----IN和OUT使用

定义场景，根据角色名称进行模糊查询其总数,返回调用者总数和查询日期

存储过程：

create or replace 
PROCEDURE count_role(
p_role_name in varchar,
count_total out int,
exec_date out date)
IS
BEGIN
select count(*) into count_total from role where role_name like '%'||p_role_name||'%';
select sysdate into exec_date from dual;
End;

创建POJO类CountRoleParams.java

public class CountRoleParams{
    private String roleName;
    private int total;
    private Date exexDate;
    
    //GET//SET
}

Mapper.xml:

<select id="countRole" parameterType="com.ssm.param.CountRoleParams" statement="CALLABLE">
    {call count_role(
            #{roleName,mode=IN, jdbcType=VARCHAR},
            #{total,mode=OUT, jdbcType=INTEGER},
            #{execDate,mode=OUT, jdbcType=DATE}
                )}
</select>

--指定statementType为CALLABLE,说明是在使用存储过程。

--定义了参数类型parameterType为pojo类

--在调用存储过程中放入参数对应的类型，通过mode设置输入或输出参数，指定对应jdbcType。

 

----游标

如果需要映射一个集合，需要使用游标

POJO类FindRoleParams.java存在属性private List<Role> roleList;

Mapper.xml:::

<resultMap type ="role" id="roleMap">
    <id property="id" column="id"/>
    <result property="roleName" column="role_name"/>
    <result property="note" column="note"/>
</resultMap>

<select id="findRole" parameterType="com.ssm.param.FindRoleParams" statement="CALLABLE">
    {call count_role(
            #{roleName,mode=IN, jdbcType=VARCHAR},
            #{total,mode=OUT, jdbcType=INTEGER},
            #{execDate,mode=OUT, jdbcType=DATE}，
            #{roleList,mode=OUT, jdbcType=CURSOR,javaType=ResultSet,resultMap=roleMap}
                )}
</select>

先定义resultMap元素，定义映射规则。在调用中对于roleList定义jdbcType为CURSOR,这样就会把结果使用ResultSet对象处理。为了使resultSet能够映射为POJO，设置resultMap为roleMap.

 

 

动态SQL

 mybatis 动态SQL，通过 if, choose, when, otherwise, trim, where, set, foreach等标签，可组合成非常灵活的SQL语句，从而在提高 SQL 语句的准确性的同时，也大大提高了开发人员的效率。

----if元素

判断语句，常与test元素连用，示例：

根据id和角色名称查询角色   

public class findRoleParams{
    private int id;
    private String roleName;
}

<select id="findRoles" parameterType="findRoleParams" resultMap="roleResultMap">
    select id ,role_name ,note from role where
    <if test="id!=null">
           id= #{id}
    </if>
    <if test="roleName!=null and roleName!=''">
           and role_name =#{roleName}
    </if>
</select>

当两个都不为空时，SQL为 select id ,role_name ,note from role where id =#{id} and role_name=#{roleName}

当roleName为空时，SQL为select id ,role_name ,note from role where id =#{id} 

但是当id为空时，SQL为select id ,role_name ,note from role where  and role_name=#{roleName}，，显然SQL是错误的，可以使用where标签来解决

----if+where

<select id="findRoles" parameterType="findRoleParams" resultMap="roleResultMap">
    select id ,role_name ,note from role 
    <where>
        <if test="id!=null">
           id= #{id}
        </if>
        <if test="roleName!=null and roleName!=''">
           and role_name =#{roleName}
        </if>
    </where>
</select>

where语句会检测，如果标签内符合要求的语句是and或or开头，会删掉and|or,如果没有符合要求的语句，会删掉where

 

----if+set

同理，如果是更新语句如下

<update id="updateRoles" parameterType="role" >
    update role 
    <set>
        <if test="roleName!=null and roleName!=''">
           role_name= #{roleName},
        </if>
        <if test="note!=null and note!=''">
           note = #{note},
        </if>
    </set>
    where id=#{id}
</update>

set标签会删除标签内语句最后一个逗号

 

----choose(when,otherwise)

类似switch..case语句

示例：查询角色，若角色ID不为空，使用id查询，若ID为空，使用角色名称查询，若角色名也为空，要求查询备注不为空的记录

<select id="findRoles" parameterType="role" resultMap="roleResultMap">
    select id ,role_name ,note from role 
    <where>
        <choose>
            <when test="id!=null">
               and id= #{id}
            </when>
            <when test="roleName!=null and roleName!=''">
               and role_name =#{roleName}
            </when>
            <otherwise>
               and note is not null
            </otherwise>
        </choose>
    </where>
</select>

 

----trim 

trim标记是一个格式化的标记，可以完成set或者是where标记的功能

改写上述的where和set示例

 
<select id="findRoles" parameterType="findRoleParams" resultMap="roleResultMap">
    select id ,role_name ,note from role 
    <trim prefix="where" prefixOverrides="and | or">
        <if test="id!=null">
           id= #{id}
        </if>
        <if test="roleName!=null and roleName!=''">
           and role_name =#{roleName}
        </if>
    </trim
</select>

prefix：前缀　　　　　　

prefixoverride：去掉第一个and或者是or

<update id="updateRoles" parameterType="role" >
    update role 
    <trim prefix="set" suffixOverrides=",">
        <if test="roleName!=null and roleName!=''">
           role_name= #{roleName},
        </if>
        <if test="note!=null and note!=''">
           note = #{note},
        </if>
    </trim>
    where id=#{id}
</update>

suffix：后缀　　

suffixoverride：去掉最后一个逗号（也可以是其他的标记，就像是上面前缀中的and一样）

 

----foreach

循环语句，遍历集合，支持数组，List,Set

--假设有一个IdsParams.java 中有一个 private List<Integer> ids; 的角色id集合

SQL语句为select * from role where r_id in (1,2,3,4)

<select id="findRolesByIds" resultType="role">
    select * from role where 
        <foreach item="id" index="index" collection="ids" 
                open="r_id in (" close=")" separator=",">
               #{id}
        </foreach>
</select>

collection:指定输入对象中的集合属性

item:每次遍历生成的对象

index:元素在集合下标

open:开始遍历时的拼接字符串

close:结束时拼接的字符串

separator:遍历对象之间需要拼接的字符串

--示例2

select * from role where id=1 or id=2 or id=3

<select id="selectRoleByListId" parameterType="com.ssm.IdsParams" resultType="role">
    select * from role
    <where>
        <foreach collection="ids" item="id" open="and (" close=")" separator="or">
            id=#{id}
        </foreach>
    </where>
</select>

 

 

 

 

--参考书目

JavaEE互联网轻量级框架整合开发--SSM框架（Spring MVC +Spring+Mybatis）和Redis开发

 

---