[大数据]Mybatis框架中sql动态语句

创建测试案例的数据库blog

CREATE TABLE `blog`  (
`id` varchar(50) ,
`title` varchar(100) ,
`author` varchar(50) ,
`create_time` datetime,
`views` int
)

创建对应blog实体类

package pojo;

import lombok.Data;

import java.util.Date;
//生成set，get方法注解
@Data
public class Blog {
    private String id;
    private String title;
    private String author;
    private Date createTime;//属性与字段不一致
    private int views;
}

创建BlogMapper接口

package dao;

import pojo.Blog;

import java.util.List;
import java.util.Map;


public interface blogMapper {
 
    //查询博客（if）
    List<Blog> queryBlogIF(Map map);
    
    //查询博客（choose）
    List<Blog> queryBlogChoose(Map map);

    //更新博客
    int updateBlog(Map map);

    //查询1，2号记录博客
    List<Blog> queryBlogForeach(Map map);
}

创建BlogMapper.xml

1、queryBlogIF(Map map)方法sql语句，where标签可以自动帮助我们去除and或添加and

不传作者和标题，查全部博客，传入文章标题,则查找对应标题的文章,传入作者，则查找对应作者文章

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

对应测试案例

@org.junit.Test
public void test2(){
    SqlSession sqlSession = MyBatisUntil.getSqlSession();
    blogMapper mapper = sqlSession.getMapper(blogMapper.class);

    HashMap map=new HashMap<>();
    
    //传入作者和标题
    map.put("title","微服务如此简单");
    map.put("author","小明");

    List<Blog> blogs = mapper.queryBlogIF(map);

    for (Blog blog : blogs) {
        System.out.println(blog);
    }

    sqlSession.close();
}

2、queryBlogChoose(Map map)方法sql语句

不传作者和标题，查全部博客，传入文章标题,则查找对应标题的文章,传入作者，则查找对应作者文章,传入文章阅读量，则查找对应阅读量的文章,choose与when需一起使用，when可以根据语句需要自动删除或添加and

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

测试案例

@org.junit.Test
public void test2(){
    SqlSession sqlSession = MyBatisUntil.getSqlSession();

    blogMapper mapper = sqlSession.getMapper(blogMapper.class);

    HashMap map=new HashMap<>();
   map.put("author","小明");

    List<Blog> blogs = mapper.queryBlogChoose(map);

    for (Blog blog : blogs) {
        System.out.println(blog);
    }

    sqlSession.close();
}

3、updateBlog(Map map)方法sql语句，set标签会自动添加根据需要添加and

<!--    提取公共部分,提高代码效率
        注意事项：最好单表定义的sql片段
        里面不要存在where标签-->
    <sql id="if-title-author">
        <if test="title!=null">
            title=#{title},
        </if>
        <if test="author!=null">
            author=#{author},
        </if>
    </sql>


    <update id="updateBlog" parameterType="map">
        update blog
        <set>
            <!--          引用sql，代码复用-->
           <include refid="if-title-author"/>
        </set>
        where id=#{id}
    </update>

测试案例

@org.junit.Test
public void test3(){
    SqlSession sqlSession = MyBatisUntil.getSqlSession();

    blogMapper mapper = sqlSession.getMapper(blogMapper.class);

    HashMap map=new HashMap<>();
    map.put("title","MyBatis如此简单123");
    //map.put("author","小明");
    map.put("id","1");

    mapper.updateBlog(map);


    sqlSession.close();
}

4、queryBlogForeach(Map map)方法sql语句

查询1，2号文章，运用foreach，collection为对应传入参数集合名称，open为where后字符、item为条件值、close为最后一个字符，separator为条件中间分隔符

<!--    collection现在传递万能map，map存在一个集合-->
    <select id="queryBlogForeach" parameterType="map" resultType="Blog">
        select * from blog
        <where>
            <foreach collection="ids" item="id" open="(" close=")" separator="or">
                id=#{id}
            </foreach>
        </where>
    </select>

测试案例

@org.junit.Test
public void test4(){
    SqlSession sqlSession = MyBatisUntil.getSqlSession();

    blogMapper mapper = sqlSession.getMapper(blogMapper.class);

    HashMap map=new HashMap<>();

    ArrayList<Integer> ids = new ArrayList<>();

    ids.add(1);
    ids.add(2);
    
    //ids与与collection的值要一致
    map.put("ids",ids);

    List<Blog> blogs = mapper.queryBlogForeach(map);

    for (Blog blog : blogs) {
        System.out.println(blog);
    }

    sqlSession.close();
}