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 queryBlogIF(Map map);
    
    //查询博客（choose）
    List queryBlogChoose(Map map);

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

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

创建BlogMapper.xml

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

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

    
        select * from blog
        
            
                title=#{title},
            
            
                author=#{author},
            
        
    
 
对应测试案例
 
@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 blogs = mapper.queryBlogIF(map);

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

    sqlSession.close();
}
 
 
2、queryBlogChoose(Map map)方法sql语句
 
不传作者和标题，查全部博客，传入文章标题,则查找对应标题的文章,传入作者，则查找对应作者文章,传入文章阅读量，则查找对应阅读量的文章,choose与when需一起使用，when可以根据语句需要自动删除或添加and
 

    select * from blog
    
        
            
                and title=#{title}
            
            
                and author=#{author}
            
            
                and views=#{views}
            
        
    

测试案例

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

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

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

    List blogs = mapper.queryBlogChoose(map);

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

    sqlSession.close();
}

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

    
        
            title=#{title},
        
        
            author=#{author},
        
    


    
        update blog
        
            
           
        
        where id=#{id}
    

测试案例

@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为条件中间分隔符

    
        select * from blog
        
            
                id=#{id}
            
        
    

测试案例

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

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

    HashMap map=new HashMap<>();

    ArrayList ids = new ArrayList<>();

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

    List blogs = mapper.queryBlogForeach(map);

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

    sqlSession.close();
}