动态SQL
多数据库支持
- 在 MyBatis 配置文件中提供数据库别名
<databaseIdProvider type="DB_VENDOR">
<property name="MySQL" value="mysql" />
<property name="Oracle" value="oracle" />
<property name="SQL Server" value="sqlserver" />
</databaseIdProvider>
- 在执行SQL时指定 databaseId
<selectKey order="BEFORE" resultType="java.lang.Integer" keyProperty="id" databaseId="mysql">
SELECT IFNULL( max(id) , 0 ) + 1 FROM t_pandas
</selectKey>
<selectKey order="BEFORE" resultType="java.lang.Integer" keyProperty="id" databaseId="oracle">
SELECT NVL( max(id) , 0 ) + 1 FROM t_pandas
</selectKey>
if
- insert
INSERT INTO t_pandas (
<if test="name != null and name !=''"> name , </if>
<if test="gender != null and gender !=''"> gender , </if>
<if test="birthdate != null"> birthdate , </if>
id
) VALUES (
<if test="name != null and name !=''"> #{name} , </if>
<if test="gender != null and gender !=''"> #{gender} , </if>
<if test="birthdate != null"> #{birthdate} , </if>
#{id}
)
set
<!-- int update( Panda p ) ; -->
<update id="update" parameterType="Panda" >
UPDATE t_pandas
<set>
<if test="name != null and name !=''"> name = #{name}, </if>
<if test="gender != null and gender !=''"> gender = #{gender} , </if>
<if test="birthdate != null"> birthdate = #{birthdate} , </if>
</set>
WHERE id = #{id}
</update>
使用 set 标签可以产生 UPDATE 语句中的 SET 字句。
同时,对于 SET 字句中最后一个字符如果是 逗号,MyBatis 会将这个 逗号 剔除。
choose
<insert id="persist" parameterType="Human" useGeneratedKeys="true" keyProperty="id" statementType="PREPARED">
INSERT INTO t_humans
(
<if test="gender != null and gender !=''"> gender , </if>
<if test="birthdate != null"> birthdate , </if>
<if test="married != null"> married , </if>
name
)
VALUES
(
<if test="gender != null and gender !=''"> #{gender} , </if>
<if test="birthdate != null"> #{birthdate} , </if>
<if test="married != null">
<choose>
<when test="married == true">'Y' , </when>
<otherwise>'N' , </otherwise>
</choose>
</if>
#{name}
)
</insert>
where 、 trim
<select id="query" resultMap="humanResultMap">
SELECT id , name , gender , birthdate , married FROM t_humans
<where>
<if test="gender != null and gender !=''">
AND gender = #{gender}
</if>
<if test="married != null">
AND married =
<choose>
<when test="married == true">'Y'</when>
<otherwise>'N'</otherwise>
</choose>
</if>
<trim prefixOverrides="AND |OR "></trim>
</where>
</select>
sql
<!-- 声明SQL片段 ( fragment ) -->
<sql id="baseQuery">
SELECT id , name , gender , birthdate , married FROM t_humans
</sql>
bind
<!-- List<Human> findByName( String nameLike ) ; -->
<select id="findByName" parameterType="java.lang.String" resultMap="humanResultMap">
<!-- 引入 SQL 片段 -->
<include refid="baseQuery" />
<!-- 绑定变量,为 name 变量指定值 ( 使用 OGNL 表达式拼接字符串 ) -->
<bind name="name" value="'%' + nameLike + '%'" />
WHERE name LIKE #{name}
</select>
foreach
- MySQL 批量插入
<insert id="persistBatch" parameterType="java.util.List" databaseId="mysql">
INSERT INTO t_humans
( name , gender , birthdate , married )
VALUES
<foreach collection="list" item="h" open="" close="" separator=",">
( #{h.name} , #{h.gender} , #{h.birthdate} , #{h.married,typeHandler=org.malajava.dynamic.type.BooleanTypeHandler} )
</foreach>
</insert>
- 批量删除
<delete id="removeBatch" parameterType="java.util.List" >
DELETE FROM t_humans WHERE
<choose>
<when test="list != null and list.size() > 0">
id IN
<foreach collection="list" open="(" close=")" item="id" separator=",">
#{id}
</foreach>
</when>
<otherwise> 1 = 2 </otherwise>
</choose>
</delete>
10、过程和函数
10.1、过程( procedure )
数据库中的过程( procedure ) ,就是存储过程( stored procedure )。
10.1.1、 在MySQL数据库环境下,需要修改 delimiter
mysq > delimiter $
mysql > select now() from dual ;
-> select current_timestamp from dual ;
-> $
+---------------------+
| now() |
+---------------------+
| 2018-11-29 14:23:16 |
+---------------------+
1 row in set (0.00 sec)
+---------------------+
| current_timestamp |
+---------------------+
| 2018-11-29 14:23:16 |
+---------------------+
1 row in set (0.00 sec)
10.1.2、变量
- 用户自定义变量
在 MySQL 中 , @ 开头的是用户自定义变量,首次在函数或过程中使用时即声明
调用函数或过程时即声明变量:
mysql> call count_human_male( @male_count ) ;
通过 select 可以查询该变量的值:
mysql> select @male_count from dual ;
- MySQL数据库系统变量
比如 事务隔离级别: @@tx_isolation
查询系统变量,获取当前事务的隔离级别
mysql> select @@tx_isolation from dual ;
修改当前会话的事务隔离级别 ( 读未已提交 )
mysql> set session tx_isolation = 'read-uncommitted';
修改当前会话的事务隔离级别 ( 读已提交 )
mysql> set session tx_isolation = 'read-committed';
修改当前会话的事务隔离级别 ( 可重复读 )
mysql> set session tx_isolation = 'repeatable-read';
修改当前会话的事务隔离级别 ( 序列化 )
mysql> set session tx_isolation = 'serializable';
比如 事务提交方式: @@autocommit
查询系统变量,获取当前事务提交方式
mysql> select @@autocommit from dual ;
1 表示 自动提交 ( 每执行一条 DML 语句就提交一次事务 )
0 表示 手动提交 ( 需要通过 commit 来提交事务 或 通过 rollback 回滚事务 )
设置事务提交方式
mysql> set autocommit = false ; -- 不要再自动提交
mysql> set autocommit = true ; -- 自动提交 (默认值)
10.1.3、 开发 “过程”
CREATE PROCEDURE 过程名称 ( 类型 名称 数据类型 [ , 类型 名称 数据类型 ] )
BEGIN
-- 在 BEGIN 和 END 之间书写 过程要完成的操作
END
10.1.4、 参数类型
- IN : 传入参数
delimiter $
CREATE PROCEDURE add ( IN a INT , IN b INT )
BEGIN
SELECT a + b FROM dual ;
END
$
delimiter ;
- OUT : 传出参数
定义带有传出参数的过程:
delimiter $
create procedure count_human_male ( OUT mcount INT )
begin
select count(*) from t_humans where gender = '男' INTO mcount ;
end ;
$
delimiter ;
调用带有传出参数的 过程:
mysql> CALL 过程名称( @用户变量 )
比如调用 count_human_male 过程,并向其传递参数
mysql> CALL count_human_male( @male_count ) ;
通过查询 male_1
vcount 变量来获取 过程 传出的数值
mysql> SELECT @male_count FROM dual ;
- INOUT : 传入/传出参数
函数( function )
数据库中的函数( function ) ,也被某些人称作 存储函数( stored function )。