根据不同条件生成不同的SQL语句
本质还是SQL语句,只是在SQL层面执行逻辑代码
- if
- choose (when, otherwise)
- trim (where, set)
- foreach
1. 搭建环境
-
导包
<dependencies> <!--lombok--> <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok --> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.24</version> <scope>provided</scope> </dependency> <!--junit--> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> </dependency> </dependencies>
-
编写配置文件
<?xml version="1.0" encoding="GBK" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <!--核心配置文件--> <configuration> <!--引入外部配置文件--> <properties resource="db.properties"> <property name="username" value="root"/> <property name="password" value="yL@98"/> </properties> <!--日志--> <settings> <!--标准日志输出--> <setting name="logImpl" value="STDOUT_LOGGING"/> <!--<setting name="logImpl" value="LOG4J"/>--> <!--驼峰命名转换--> <setting name="mapUnderscoreToCamelCase" value="true"/> </settings> <!--给实体类起别名--> <typeAliases> <!--<typeAlias type="com.yl.pojo.User" alias="User"/>--> <package name="com.yl.pojo"/> </typeAliases> <environments default="test"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="com.mysql.cj.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8"/> <property name="username" value="root"/> <property name="password" value="yL@98"/> </dataSource> </environment> <environment id="test"> <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 class="com.yl.dao.BlogMapper"/> </mappers> </configuration>
-
编写实体类
import java.util.Date; @Data public class Blog { private String id; private String title; private String author; private Date create_time; private int views; }
-
编写实体类对应 Mapper 接口和 Mapper.xml 文件
import com.yl.pojo.Blog; import java.util.List; import java.util.Map; public interface BlogMapper { }
<?xml version="1.0" encoding="GBK" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <!--核心配置文件--> <mapper namespace="com.yl.dao.BlogMapper"> </mapper>
2. if
-
接口
//插入数据 int addBlog(Blog blog); //查询博客 List<Blog> queryBlogIF(Map map);
-
Mapper.xml
<insert id="addBlog" parameterType="blog"> insert into blog(id,title,author,create_time,views) values(#{id},#{title},#{author},#{createTime},#{views}) </insert> <!--查询博客--> <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>
-
测试
public class MapperTest { @Test public void addBlogTest() { SqlSession sqlSession = MyBatisUntils.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); Blog blog = new Blog(); blog.setId(IDutils.getId()); blog.setTitle("Mybatis"); blog.setAuthor("杨杨"); blog.setCreateTime(new Date()); blog.setViews(9999); mapper.addBlog(blog); blog.setId(IDutils.getId()); blog.setTitle("Java"); mapper.addBlog(blog); blog.setId(IDutils.getId()); blog.setTitle("Spring"); mapper.addBlog(blog); blog.setId(IDutils.getId()); blog.setTitle("微服务"); mapper.addBlog(blog); sqlSession.close(); } @Test public void queryBlogIF(){ SqlSession sqlSession = MyBatisUntils.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); HashMap map = new HashMap(); //map.put("title", "Java"); map.put("author", "杨杨"); List<Blog> blogs = mapper.queryBlogIF(map); for (Blog blog : blogs) { System.out.println(blog); } sqlSession.close(); } }
3. choose (when, otherwise)
<select id="queryBlogChoose" parameterType="map" resultType="blog">
select * from mybatis.blog
<where>
<choose>
<when test="title != null">
title = #{title}
</when>
<when test="author != null">
and author = #{author}
</when>
<otherwise>
and views = #{views}
</otherwise>
</choose>
</where>
</select>
@Test
public void queryBlogChoose(){
SqlSession sqlSession = MyBatisUntils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
//map.put("title", "Java");
map.put("author", "杨杨");
map.put("views", 9999);
List<Blog> blogs = mapper.queryBlogChoose(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
4. 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 mybatis.blog
<set>
<if test="title != null">
title = #{title},
</if>
<if test="author != null">
author = #{author}
</if>
</set>
where id = #{id}
</update>
@Test
public void updateBlog(){
SqlSession sqlSession = MyBatisUntils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
map.put("title", "JVM");
//map.put("author", "杨");
map.put("id", "0c1db0ef618b458f964c6b29401d22c1");
mapper.updateBlog(map);
sqlSession.close();
}
5. SQL片段
抽取公共部分方便复用
<sql id="if-title-author">
<if test="title != null">
and title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</sql>
<!--查询博客-->
<select id="queryBlogIF" parameterType="map" resultType="blog">
select * from blog
<where>
<include refid="if-title-author"></include>
</where>
</select>
最好基于单表定义SQL片段
不设置where标签
6. foreach
动态 SQL 的另一个常见使用场景是对集合进行遍历(尤其是在构建 IN 条件语句的时候)
<select id="selectPostIn" resultType="domain.blog.Post">
SELECT *
FROM POST P
<where>
<foreach item="item" index="index" collection="list"
open="ID in (" separator="," close=")" nullable="true">
#{item}
</foreach>
</where>
</select>
-
接口
List<Blog> queryBlogForeach(Map map);
-
xml
<select id="queryBlogForeach" parameterType="map" resultType="blog"> select * from mybatis.blog <where> <foreach collection="ids" item="id" open="and (" close=")" separator="or"> id = #{id} </foreach> </where> </select>
-
测试
@Test public void queryBlogForeach(){ SqlSession sqlSession = MyBatisUntils.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); HashMap map = new HashMap(); ArrayList<Integer> ids = new ArrayList<Integer>(); ids.add(1); ids.add(2); ids.add(3); map.put("ids", ids); List<Blog> blogs = mapper.queryBlogForeach(map); for (Blog blog : blogs) { System.out.println(blog); } sqlSession.close(); }
动态SQL本质就是拼接SQL语句