动态SQL概述和环境搭建

什么是动态SQL：就是根据不同的条件生成不同的SQL语句，例如拼接SQL

编写一个基础工程

• 导包导依赖
• 编写配置文件
• 编写实体类
• 编写实体类对应的Mapper接口和xml文件

编写辅助工具

开启驼峰命名转换

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configurationPUBLIC "-//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 mapperPUBLIC "-//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.blogwhere 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>