Mybatis动态SQL

Mybatis动态SQL

**动态SQL：**根据不同的查询条件生成不同的sql语句

搭建环境

1.数据库表blog

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

2.MybatisUtils工具类

/**
 * Mybatis工具类
 * @author 刘淳
 */
public class MybatisUtils {
    private static SqlSessionFactory sqlSessionFactory;
    static {
        try {
            //使用Mybatis第一步：   获取SqlSessionFactory对象
            String resource="mybatis-config.xml";  //配置文件路径
            InputStream inputStream= Resources.getResourceAsStream(resource);  //读取配置文件到输入流
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);  //使用配置文件创建工厂对象
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    //使用Mybatis第二部：   获取SqlSession对象
    //SqlSession：完全包含面向数据库执行sql命令的所有方法
    public static SqlSession getSqlSession(){
        return sqlSessionFactory.openSession();
    }
}

3.IdUtil工具类

/**
 * 生成唯一ID的工具类
 * @author 刘淳
 */
public class IDUtil {
    public static String getId(){
        return UUID.randomUUID().toString().replaceAll("-","");
    }
}

4.Blog实体类

/**
 * Blog表实体类
 * @author 刘淳
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Blog {
    private String id;
    private String title;
    private String author;
    private Date createTime;
    private int views;
}

5.Mapper接口

/**
 * Blog实体类Mapper接口
 * @author 刘淳
 */
public interface BlogMapper {
}

6.Mapper.xml

< ?xml version="1.0" encoding="UTF-8" ?>
< !DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
< mapper namespace="ltd.lccyj.mapper.BlogMapper">

< /mapper>

7.mybatis核心配置文件

< ?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>
    < !--属性引入及配置-->
    < properties resource="jdbc.properties"/>
    
    < settings>
        < !--设置日志实现-->
        < setting name="logImpl" value="LOG4J"/>
        < !--开启下划线命名与驼峰命名自动转换-->
        < setting name="mapUnderscoreToCamelCase" value="true"/>
    < /settings>

    < typeAliases>
        < typeAlias type="ltd.lccyj.domain.Blog" alias="Blog"/>
    < /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="ltd.lccyj.mapper"/>
    < /mappers>
< /configuration>

8.准备初始数据

• 编写接口

  int addBlog(Blog blog);
  

• mapper.xml

  < insert id="addBlog" parameterType="Blog">
      insert into blog (id, title, author, create_time, views)
      VALUES (#{id},#{title},#{author},#{createTime},#{views})
  < /insert>
  

• 测试类中初始化博客数据

  @Test
  public void addBlog(){
      SqlSession session = MybatisUtils.getSqlSession();
      BlogMapper mapper = session.getMapper(BlogMapper.class);
      
      Blog blog = new Blog();
      blog.setId(IDUtil.getId());
      blog.setTitle("阿巴阿巴阿巴阿巴");
      blog.setAuthor("刘淳");
      blog.setCreateTime(new Date());
      blog.setViews(9999);
      
      mapper.addBlog(blog);
      
      blog.setId(IDUtil.getId());
      blog.setTitle("阿巴");
      mapper.addBlog(blog);
      
      blog.setId(IDUtil.getId());
      blog.setTitle("阿巴阿巴");
      mapper.addBlog(blog);
      
      blog.setId(IDUtil.getId());
      blog.setTitle("阿巴阿巴阿巴");
      mapper.addBlog(blog);
      
      session.commit();
      session.close();
  }
  

if语句

**需求：**根据作者名字和博客名字来查询博客，如果作者为空，根据博客名查询，反之根据作者名查询

**if：**如果test中条件为true，则会向sql中插入标签中的语句

1.Mapper接口

List< Blog> selectBlogIf(Map< String,String> map);

2.Mapper.xml

< select id="selectBlogIf" parameterType="map" resultType="Blog">
    select * from blog where
    < if test="title!=null">
        title=#{title}
    < /if>
    < if test="author!=null">
        and author=#{author}
    < /if>
< /select>

3.测试

@Test
public void selectBlogIf(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    Map< String,String> map=new HashMap< >();
    map.put("title","阿巴");
    map.put("author","刘淳");
    List< Blog> blogs = mapper.selectBlogIf(map);
    for (Blog blog : blogs) {
        System.out.println(blog);
    }
    sqlSession.close();
}

where语句

上面的实现存在一个问题，当title为空，但存在where时会出现sql语句错误

可使用where解决

• 如果where标签包含的标签中有返回值，它会向sql中插入一个where
• 如果包含的标签返回内容是and或or开头，会将其去除

修改Mapper.xml

< select id="selectBlogIf" parameterType="map" resultType="Blog">
    select * from blog
    < where>
        < if test="title!=null">
            title=#{title}
        < /if>
        < if test="author!=null">
            and author=#{author}
        < /if>
    < /where>
< /select>

set语句

**set：**用于更新语句，能自动去除语句中多余的逗号

1.Mapepr接口

void updateBlog(Map< String,String> map);

2.Mapper.xml

< update id="updateBlog" parameterType="map">
    update blog
    < set>
        < if test="title!=null">
            title=#{title},
        < /if>
        < if test="author!=null">
            author=#{author}
        < /if>
    < /set>
    where id=#{id}
< /update>

3.测试

@Test
public void updateBlog(){
    SqlSession session = MybatisUtils.getSqlSession();
    BlogMapper mapper = session.getMapper(BlogMapper.class);

    HashMap< String, String> map = new HashMap< >();
    map.put("title","阿巴");
    map.put("author","小学生");
    map.put("id","c66a359f11964fc19fda3113606eace2");

    mapper.updateBlog(map);

    session.commit();
    session.close();
}

choose语句

**choose:**相当于java中的switch when相当于case otherwise等于default

1.Mapper接口

List< Blog> selectBlogChoose(Map< String,Object> map);

2.Mapper.xml

< select id="selectBlogChoose" parameterType="map" resultType="Blog">
    select * from blog
    < where>
        < choose>
            < when test="title!=null">
                title=#{title}
            < /when>
            < when test="author!=null">
                author=#{author}
            < /when>
            < otherwise>
                views=#{views}
            < /otherwise>
        < /choose>
    < /where>
< /select>

3.测试

@Test
public void selectBlogChoose(){
    SqlSession session = MybatisUtils.getSqlSession();
    BlogMapper mapper = session.getMapper(BlogMapper.class);

    HashMap< String, Object> map = new HashMap< String, Object>();
    map.put("title","阿巴");
    map.put("author","刘淳");
    map.put("views",9999);
    List< Blog> blogs = mapper.selectBlogChoose(map);

    System.out.println(blogs);

    session.close();
}

SQL片段

重复使用的sql语句可以抽取出来，使用时直接调用

• 建议基于单表定义sql，提高sql片段复用性
• sql片段中不要包括where

示例：

< sql id="if-title-author">
    < if test="title!=null">
        title=#{title}
    < /if>
    < if test="author!=null">
        and author=#{author}
    < /if>
< /sql>

< select id="selectBlogIf" parameterType="map" resultType="Blog">
    select * from blog
    < where>
        < include refid="if-title-author"/>
    < /where>
< /select>

foreach语句

**环境准备：**将数据库表的前三数据id改为1，2，3

**需求：**查询表中blog表中id分别为1，2，3的博客信息

foreach:

• collection:指定参数中的集合
• item：每次遍历生成的对象
• open：开始遍历时拼接的字符串
• close：结束时拼接的字符串
• separator：遍历对象之间拼接的字符串

1.Mapper接口

List< Blog> selectBlogForeach(List< Integer> list);

2.Mapper.xml

< select id="selectBlogForeach" parameterType="int" resultType="Blog">
    select * from blog
    < where>
        < foreach collection="list" item="id" separator="or">
            id=#{id}
        < /foreach>
    < /where>
< /select>

3.测试

@Test
public void selectBlogForeach(){
    SqlSession session = MybatisUtils.getSqlSession();
    BlogMapper mapper = session.getMapper(BlogMapper.class);

    List< Integer> list = new ArrayList< >();
    list.add(1);
    list.add(2);
    list.add(3);

    List< Blog> blogs = mapper.selectBlogForeach(list);
    System.out.println(blogs);

    session.close();
}