动态SQL
动态SQL概述和环境搭建
什么是动态SQL：就是根据不同的条件生成不同的SQL语句，例如拼接SQL
编写一个基础工程
- 导包导依赖
- 编写配置文件
- 编写实体类
- 编写实体类对应的Mapper接口和xml文件
编写辅助工具
开启驼峰命名转换
<?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>
<!-- Write labels in order -->
<properties resource="db.properties" />
<settings>
<setting name="logImpl" value="LOG4J" />
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<typeAliases>
<package name="com.bkms.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="${user}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/bkms/dao/BlogMapper.xml"/>
</mappers>
</configuration>
编写id随机生成工具类
package com.bkms.utils;
import java.util.UUID;
/**
* 获取随机id值
* @author bkms
*/
public class IdUtils {
public static String getRandomId() {
return UUID.randomUUID().toString().replaceAll("-","");
}
}
测试数据插入
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.bkms.dao.BlogMapper">
<!-- 插入数据 -->
<insert id="incrementBlog" parameterType="blog">
insert into mydb.blog (id,title,author,create_time,views)
values (#{id},#{title},#{author},#{createTime},#{views});
</insert>
</mapper>
@Test
// 测试插入数据
public void incrementBlogTest() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
Blog blog = new Blog();
blog.setId(IdUtils.getRandomId());
blog.setTitle("Mybatis");
blog.setAuthor("Philip");
blog.setCreateTime(new Date());
blog.setViews(9999);
mapper.incrementBlog(blog);
blog.setId(IdUtils.getRandomId());
blog.setTitle("Java");
mapper.incrementBlog(blog);
blog.setId(IdUtils.getRandomId());
blog.setTitle("Spring");
mapper.incrementBlog(blog);
blog.setId(IdUtils.getRandomId());
blog.setTitle("微服务");
mapper.incrementBlog(blog);
sqlSession.commit();
sqlSession.close();
}
根据传入参数查询博客
@Test
// 测试博客查询
public void searchBlogIfTest() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap<String,Object> map = new HashMap<String,Object>();
map.put("title","Spring");
List<Blog> blogs = mapper.searchBlogIf(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
<!-- 查询博客 -->
<select id="searchBlogIf" parameterType="map" resultType="blog">
select * from mydb.blog
where 1=1
<if test="title != null">
and title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</select>
解决if标签问题
解决方案1
<!-- 查询博客(改进1) -->
<select id="searchBlogIf2" parameterType="map" resultType="blog">
select * from mydb.blog
<where>
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
and name = #{name}
</if>
</where>
</select>
解决方案2
<!-- 查询博客(改进2) -->
<!--
where-choose标签只能选择1个满足条件的sql语句
都满足的话还是走第1个
-->
<select id="searchBlogIf2" parameterType="map" resultType="blog">
select * from mydb.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>
测试相关操作
更新博客数据
<!-- 更新博客数据 -->
<!--
必须要满足至少1个if的test语句
否则会因为语句不完整报错
set标签会自动前置set关键字
同时会删除无关的逗号
-->
<update id="updateBlogData" parameterType="map">
update mydb.blog
<set>
<if test="title != null">
title = #{title},
</if>
<if test="author != null">
name = #{name}
</if>
</set>
</update>
导入sql片段
<!-- sql片段 -->
<sql id="if-title-author">
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
and name = #{name}
</if>
</sql>
<!-- 查询博客(改进1) -->
<select id="searchBlogIf2" parameterType="map" resultType="blog">
select * from mydb.blog
<where>
<include refid="if-title-author"></include>
</where>
</select>
sql片段使用注意事项
- 最好基于单表定义sql字段，不要封装太过复杂的操作
- 不要存在where标签
测试foreach标签
<!-- 通过foreach查询博客 -->
<!--
效果等同于
select * from mydb.blog where 1=1 and (id = 1 or id = 2 or id = 3)
假设这个List集合叫做ids，遍历的每一项叫做id
如果ids集合是空的，where标签会自动省略
-->
<select id="searchblogByForeach" parameterType="map" resultType="blog">
<where>
<foreach collection="ids" item="id" open="and (" close=")" separator="or">
id = #{id}
</foreach>
</where>
</select>