(十八)、动态SQL
1.什么是动态SQL?
实质:拼接SQL语句
什么是动态SQL: 动态SQL就是指根据不同的条件生成不同的SQL语句
MyBatis 3 替换了之前的大部分元素,大大精简了元素种类,现在要学习的元素种类比原来的一半还要少。
if choose (when, otherwise) trim (where, set) foreach
2.搭建环境(UUID)
CREATE TABLE `blog`( `id` VARCHAR(50) NOT NULL COMMENT '博客id', `title` VARCHAR(100) NOT NULL COMMENT '博客标题', `author` VARCHAR(30) NOT NULL COMMENT '博客作者', `create_time` DATETIME NOT NULL COMMENT '创建时间', `views` INT(30) NOT NULL COMMENT '浏览量' )ENGINE=INNODB DEFAULT CHARSET=utf8
1.导包
2.编写配置文件
同意数据库的id转换为驼峰形式
<setting name="mapUnderscoreToCamelCase" value="true"/>
3.编写实体类
package Com.Jsxs.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; import java.util.Date; @Data @AllArgsConstructor @NoArgsConstructor public class Blog { private String id; private String title; private String author; // 字段名和数据库不一致 private Date createTime; private int views; }
4.数据库的id,进行设置UUID
package Com.Jsxs.utils; import org.junit.jupiter.api.Test; import java.util.UUID; @SuppressWarnings("all") public class IDutils { public static String getID(){ return UUID.randomUUID().toString().replaceAll("-",""); } @Test public void test(){ System.out.println(IDutils.getID()); } }
mybatis-config.xml
<?xml version="1.0" encoding="UTF8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "https://mybatis.org/dtd/mybatis-3-config.dtd"> <!--configuration核心配置文件--> <configuration> <properties resource="db.properties"></properties> <settings> <setting name="logImpl" value="LOG4J"/> <setting name="mapUnderscoreToCamelCase" value="true"/> </settings> <!-- 类型别名--> <typeAliases> <package name="Com.Jsxs.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> <package name="Com.Jsxs.Dao"/> </mappers> </configuration>
3.动态SQL语句的 IF
(1).IF语句
接口
package Com.Jsxs.Dao; import Com.Jsxs.pojo.Blog; import java.util.List; import java.util.Map; public interface BlogMapper { // 查询博客 List<Blog> queryBlogIF(Map<String,Object> map); }
配置文件
<?xml version="1.0" encoding="UTF8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "https://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="Com.Jsxs.Dao.BlogMapper"> <!-- 通过IF语句进行查询博客--> <!-- select *from blog where title=#{title} and author=#{author};--> <select id="queryBlogIF" parameterType="map" resultType="Com.Jsxs.pojo.Blog"> select *from blog where 1=1 <!-- 我的需求是: 假如说title,没有传值,那么就查询出全部的title --> <if test="title !=null"> and title= #{title} </if> <if test="author != null"> and author = #{author} </if> </select> </mapper>
测试
@Test public void TestIf(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); HashMap<String, Object> map = new HashMap<>(); map.put("title1","C++"); map.put("author","吉士先生"); List<Blog> blogs = mapper.queryBlogIF(map); for (Blog blog : blogs) { System.out.println(blog); } } }
4.choose (when, otherwise)
有时候,我们不想使用所有的条件,而只是想从多个条件中选择一个使用。针对这种情况,MyBatis 提供了 choose 元素,它有点像 Java 中的 switch 语句。对应—》 Switch Catch Deafult
(1).choose
为什么要使用choose 因为是为了实现 Switch Catch Deafult
<?xml version="1.0" encoding="UTF8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "https://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="Com.Jsxs.Dao.BlogMapper"> <select id="queryBlogChoose" parameterType="map" resultType="Com.Jsxs.pojo.Blog"> select *from 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> </mapper>
测试
@Test public void TestChoose(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); HashMap<String, Object> map = new HashMap<>(); map.put("title","C++"); map.put("author","吉士先生"); List<Blog> blogs = mapper.queryBlogChoose(map); for (Blog blog : blogs) { System.out.println(blog); } }
5.trim (where, set)
(1).where标签
为什么要使用Where标签,因为这个标签可以代替强求设置的where 1=1
,并且假如说and在第一个,会自动消除不适应的and;
文件配置
<?xml version="1.0" encoding="UTF8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "https://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="Com.Jsxs.Dao.BlogMapper"> <!-- 通过IF语句进行查询博客--> <!-- select *from blog where title=#{title1} and author=#{author};--> <select id="queryBlogIF" parameterType="map" resultType="Com.Jsxs.pojo.Blog"> select *from blog <!-- 我的需求是: 假如说title,没有传值,那么就查询出全部的title --> <where> <if test="title !=null"> title= #{title} </if> <if test="author != null"> and author = #{author} </if> </where> </select> </mapper>
测试
@Test public void TestIf(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); HashMap<String, Object> map = new HashMap<>(); map.put("title","C++"); map.put("author","吉士先生"); List<Blog> blogs = mapper.queryBlogIF(map); for (Blog blog : blogs) { System.out.println(blog); } }
(2)Set标签(更新语句)
为什么要使用set标签?
因为在update的时候,我们可能会使用逗号隔离,这时候可能会出现差错。
update blog set title=? ,author=? where id=?;
接口
package Com.Jsxs.Dao; import Com.Jsxs.pojo.Blog; import java.util.List; import java.util.Map; public interface BlogMapper { // 插入数据 int addBlog(Blog blog); // 查询博客 List<Blog> queryBlogIF(Map<String,Object> map); // 利用Choose List<Blog> queryBlogChoose(Map<String,Object> map); // 更新数据 int updateBlogChoose(Map<String,Object> map); }
配置文件
<?xml version="1.0" encoding="UTF8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "https://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="Com.Jsxs.Dao.BlogMapper"> <!-- update blog set title--> <update id="updateBlogChoose" parameterType="map"> update blog <set> <if test="title!=null"> title=#{title}, </if> <if test="author!=null"> author=#{author} </if> </set> where id=#{id} </update> </mapper>