mybatis
每日格言
无所畏惧,坚持到底,决不放弃。干兄弟们
动态sql
什么是动态sql,动态sql就是根据不同的条件产生不同的sql语句
动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架,你应该能理解根据不同条件拼接 SQL 语句有多痛苦,例如拼接时要确保不能忘记添加必要的空格,还要注意去掉列表最后一个列名的逗号。利用动态 SQL,可以彻底摆脱这种痛苦。
- if
- choose (when, otherwise)
- trim (where, set)
- foreach
搭建环境
CREATE TABLE `mybatis`.`blog` (
`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '博客id',
`title` VARCHAR(30) NOT NULL COMMENT '博客标题',
`author` VARCHAR(30) NOT NULL COMMENT '博客作者',
`create_time` DATETIME NOT NULL COMMENT '创建时间',
`views` INT(30) NOT NULL COMMENT '浏览量',
PRIMARY KEY (`id`)
)
创建一个基础工程
-
导包
-
编写配置文件
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <!-- 外部配置文件--> <properties resource="com/hyc/dao/db.properties"/> <!-- 可以给实体类起别名--> <settings> <setting name="logImpl" value="LOG4J"/> </settings> <typeAliases> <!-- <typeAlias type="com.hyc.pojo.user" alias="user"/>--> <package name="com.hyc.pojo"/> </typeAliases> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="${driver}"/> <property name="url" value="${url}"/> <property name="username" value="${username}"/> <property name="password" value="${password}"/> </dataSource> </environment> </environments> <mappers> <mapper resource="com/hyc/dao/BlogMapper.xml"/> </mappers> </configuration>
-
编写实体类
注意编写日期要用 util包下的date
package com.hyc.pojo; import lombok.Data; import java.util.Date; @Data public class Blog { private int id; private String title; private String author; private Date createTime; private int views; }
-
编写实体类对应的Mapper接口和对应的Mapper.xml文件
if
<select id="queryBlogif" parameterType="map" resultType="blog">
select * from blog where 1=1
<if test="title != null">
and title = #{title};
</if>
<if test="author != null">
and author = #{author};
</if>
</select>
choose
<select id="queryBlogChoose" resultType="blog" parameterType="map">
select * from blog
<where>
<choose>
<when test="title != null">
and title = #{
title}
</when>
<when test="author != null">
and author = #{
author}
</when>
<otherwise>
and views = #{
views}
</otherwise>
</choose>
</where>
</select>
trim(where,set)
<select id="queryBlogif" parameterType="map" resultType="blog">
select * from blog
<where>
<if test="title != null">
and title = #{title};
</if>
<if test="author != null">
and author = #{author};
</if>
</where>
</select>
<update id="UpdateBlog" parameterType="map" >
update blog
<set>
<if test="title != null">
title = #{title},
</if>
<if test="author != null">
author = #{author}
</if>
</set>
where id = #{id}
</update>
所谓的动态sqL,本质上就是sql语句,只是我们可以在sql层面,去执行一个逻辑代码
sql片段
有的时候,我们可能会将一些公共的部分抽取出来,方便使用
通过include标签的refid属性来调用sql片段
<select id="queryBlogif" parameterType="map" resultType="blog">
select * from blog
<where>
<include refid="if-title-author"></include>
</where>
</select>
设置id来让sql片段可以被调用,
<sql id="if-title-author">
<if test="title != null">
and title = #{title};
</if>
<if test="author != null">
and author = #{author};
</if>
</sql>
注意事项:
- 最好基于单表来定义sql片段
- 最好不在sql片段存在where
Foreach
select * from User where 1=1 and (id = 1 or id=2 or id=3)
<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>
<!-- 就是拼接sql-->
<!-- select * from blog where 1=1 and (id = 1 or id=2 or id=3) -->
<select id="queryBlogForeach" resultType="blog" parameterType="map">
select * from blog
<where>
<foreach collection="ids" item="id" open="and (" close=")" separator="or">
id=#{id}
</foreach>
</where>
</select>
建议:
- 现在mysql写出完整的sql,再对应的修改我们的动态sql再使用,