渚漪Day29——SSM【Mybatis06】
12、动态SQL
动态SQL就是指根据不同的调价生成不同的SQL语句
搭建环境
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
创建一个基础工程
-
导包
-
编写配置文件
-
编写实体类
-
编写对应的接口和xml
<?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.ijuy.dao.BlogMapper"> <insert id="addBlog" parameterType="Blog"> insert into mybatis.blog(id, title, author, create_time, views) values (#{id},#{title},#{author},#{createTime},#{views}); </insert> </mapper>
import com.ijuy.dao.BlogMapper;
import com.ijuy.pojo.Blog;
import com.ijuy.util.IdUtils;
import com.ijuy.util.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.Date;
public class MyTest {
@Test
public void test1(){
try(SqlSession sqlSession = MybatisUtils.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);
}
}
}
IF
<select id="queryBlogIf" parameterType="map" resultType="blog">
select * from mybatis.blog
<where>
<if test="title != null">
and title=#{title}
</if>
<if test="author != null">
and author=#{author}
</if>
</where>
</select>
choose
<select id="queryBlogChoose" parameterType="map" resultType="blog">
select * from mybatis.blog
<where>
<choose>
<when test="title!=null">
title = #{title}
</when>
<when test="author!=null">
author = #{author}
</when>
<otherwise>
views=#{views}
</otherwise>
</choose>
</where>
</select>
trim（where，set）
where：补充SQL中的where，自动去掉多余的and
set：补充SQL中的set，自动去掉，（逗号）
所谓的动态sql，本质上还是sql语句只是我们可以在sql层面，去执行一个逻辑代码
