Mybatis之动态sql标签
什么是动态SQL? 动态SQL有什么作用?
传统的使用JDBC的方法,相信大家在组合复杂的的SQL语句的时候,需要去拼接,稍不注意哪怕少了个空格,都会导致错误。Mybatis的动态SQL功能正是为了解决这种问题, 其通过 if, choose, when, otherwise, trim, where, set, foreach标签,可组合成非常灵活的SQL语句,从而提高开发人员的效率。
来让我们感受一下Mybatis动态SQL的魅力吧!
if和where
未使用where标签:
<select id="findUserById" resultType="user">
select * from user where
<if test="id != null">
id=#{id}
</if>
and deleteFlag=0;
</select>
问题:
要是你传入的id为null, 那么你这最终的SQL语句不就成了 select * from user where and deleteFlag=0, 这语句有问题!”
<!--动态Sql : where / if-->
<select id="dynamicSql" resultType="com.lks.domain.User">
select <include refid="tableAllkey"/> from users
<where>
<if test="id != null and id != 0">
AND id = #{id}
</if>
<if test="name != null and name != ''">
AND name = #{name}
</if>
<if test="county != null and county != ''">
AND county = #{county}
</if>
</where>
</select>
where标签:
mybatis是对它做了处理,当它遇到AND或者OR这些,它知道怎么处理。
set
未使用set标签:
<update id="updateUser" parameterType="com.dy.entity.User">
update user set
<if test="name != null">
name = #{name},
</if>
<if test="password != null">
password = #{password},
</if>
<if test="age != null">
age = #{age}
</if>
<where>
<if test="id != null">
id = #{id}
</if>
and deleteFlag = 0;
</where>
</update>
“如果我只有name不为null, 那么这SQL不就成了 update set name = #{name}, where … ? 你那name后面那逗号会导致出错啊!”
使用set标签:
<update id="updateUser" parameterType="com.dy.entity.User">
update user
<set>
<if test="name != null">name = #{name},</if>
<if test="password != null">password = #{password},</if>
<if test="age != null">age = #{age},</if>
</set>
<where>
<if test="id != null">
id = #{id}
</if>
and deleteFlag = 0;
</where>
</update>
trim
trim 是一个格式化标签,可以完成< set > 或者是 < where > 标记的功能。主要有4个参数:
① prefix:前缀
② prefixOverrides:去掉第一个and或者是or
③ suffix:后缀
④ suffixOverrides:去掉最后一个逗号,也可以是其他的标记
<!--动态Sql: trim 标签-->
<select id="dynamicSqlTrim" resultType="com.lks.domain.User">
select * from users
<trim prefix="where" suffix="order by age" prefixOverrides="and | or" suffixOverrides=",">
<if test="name != null and name != ''">
AND name = #{name}
</if>
<if test="county != null and county != ''">
AND county = #{county}
</if>
</trim>
</select>
foreach 标签
foreach标签主要有以下参数:
item :循环体中的具体对象。支持属性的点路径访问,如item.age,item.info.details,在list和数组中是其中的对象,在map中是value。
index :在list和数组中,index是元素的序号,在map中,index是元素的key,该参数可选。
open :表示该语句以什么开始
close :表示该语句以什么结束
separator :表示元素之间的分隔符,例如在in()的时候,separator=","会自动在元素中间用“,“隔开,避免手动输入逗号导致sql错误,如in(1,2,)这样。该参数可选。
<select id="selectPostIn" resultType="domain.blog.Post">
SELECT *
FROM POST P
WHERE ID in
<foreach item="item" index="index" collection="list"
open="(" separator="," close=")">
#{item}
</foreach>
</select>
choose标签
<select id="findActiveBlogLike"
resultType="Blog">
SELECT * FROM BLOG WHERE state = ‘ACTIVE’
<choose>
<when test="title != null">
AND title like #{title}
</when>
<when test="author != null and author.name != null">
AND author_name like #{author.name}
</when>
<otherwise>
AND featured = 1
</otherwise>
</choose>
</select>
当title和author都不为null的时候, 那么选择二选一(前者优先), 如果都为null, 那么就选择 otherwise中的, 如果tilte和author只有一个不为null, 那么就选择不为null的那个。
常用SQL语句
SQL分类:
DDL—数据定义语言(CREATE,ALTER,DROP,DECLARE)
DML—数据操纵语言(SELECT,DELETE,UPDATE,INSERT)
DCL—数据控制语言(GRANT,REVOKE,COMMIT,ROLLBACK)
DDL
1、说明:创建数据库
CREATE DATABASE database-name
2、说明:删除数据库
drop database dbname
3、说明:备份sql server
— 创建 备份数据的 device USE master EXEC sp_addumpdevice ’disk’, ’testBack’, ’c:\mssql7backup\MyNwind_1.dat’
— 开始 备份
BACKUP DATABASE pubs TO testBack
4、说明:创建新表
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],…)
根据已有的表创建新表:
A:create table tab_new like tab_old (使用旧表创建新表)
B:create table tab_new as select col1,col2… from tab_old definition only
5、说明:
删除新表:drop table tabname
6、说明:
增加一个列:Alter table tabname add column col type
注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。
7、说明:
添加主键:Alter table tabname add primary key(col)
说明:
删除主键:Alter table tabname drop primary key(col)
8、说明:
创建索引:create [unique] index idxname on tabname(col….)
删除索引:drop index idxname
注:索引是不可更改的,想更改必须删除重新建。
9、说明:
创建视图:create view viewname as select statement
删除视图:drop view viewname
DML
几个简单的基本的sql语句:
选择:select * from table1 where 范围
插入:insert into table1(field1,field2) values(value1,value2)
删除:delete from table1 where 范围
更新:update table1 set field1=value1 where 范围
查找:select * from table1 where field1 like ’%value1%’ —like的语法很精妙,查资料!
排序:select * from table1 order by field1,field2 [desc]
总数:select count * as totalcount from table1
求和:select sum(field1) as sumvalue from table1
平均:select avg(field1) as avgvalue from table1
最大:select max(field1) as maxvalue from table1
最小:select min(field1) as minvalue from table1
GROUP BY 语句
GROUP BY 语句用于结合聚合函数,根据一个或多个列对结果集进行分组。
SQL GROUP BY 语法
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
例子:
mysql> SELECT * FROM access_log;
±----±--------±------±-----------+
| aid | site_id | count | date |
±----±--------±------±-----------+
| 1 | 1 | 45 | 2016-05-10 |
| 2 | 3 | 100 | 2016-05-13 |
| 3 | 1 | 230 | 2016-05-14 |
| 4 | 2 | 10 | 2016-05-14 |
| 5 | 5 | 205 | 2016-05-14 |
| 6 | 4 | 13 | 2016-05-15 |
| 7 | 3 | 220 | 2016-05-15 |
| 8 | 5 | 545 | 2016-05-16 |
| 9 | 3 | 201 | 2016-05-17 |
±----±--------±------±-----------+
9 rows in set (0.00 sec)
SELECT site_id, SUM(access_log.count) AS nums
FROM access_log GROUP BY site_id;
结果:
HAVING 子句
在 SQL 中增加 HAVING 子句原因是,WHERE 关键字无法与聚合函数一起使用。
HAVING 子句可以让我们筛选分组后的各组数据。
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;
更多sql用法参考: