Mybatis实现动态SQL

https://blog.csdn.net/liyuuhuvnjjv/article/details/122276943?spm=1001.2014.3001.5501

传统的使用JDBC的方法，相信大家在组合复杂的的SQL语句的时候，需要去拼接，稍不注意哪怕少了个空格，都会导致错误。Mybatis的动态SQL功能正是为了解决这种问题，其通过 if, choose, when, otherwise, trim, where, set,foreach标签，可组合成非常灵活的SQL语句，从而提高开发人员的效率。

package org.dao;

import org.pojo.Blog;

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

public interface BlogMapper {
    //插入数据
    int addBlog(Blog blog);

    //查询博客
    List queryBlogIF(Map map);
}

    
        insert into mybatis.blog (id,title,author,create_time,views) values (#{id},#{title},#{author},#{createTime},#{views})
    

    
        select * from mybatis.blog where 1=1
        
            and title = #{title}
        
        
            and author = #{author}
        
    

import org.apache.ibatis.session.SqlSession;
import org.dao.BlogMapper;
import org.junit.Test;
import org.pojo.Blog;
import org.utils.IDutils;
import org.utils.MybatisUtils;

import java.util.Date;
import java.util.HashMap;
import java.util.List;

public class MyTest {
    @Test
    public void queryBlogIF(){
        SqlSession sqlSession = null;
        try{
            sqlSession = MybatisUtils.getSqlSession();
            BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
            HashMap map = new HashMap();
            map.put("title","Java如此简单");
            map.put("author","lyh");
            List blogList = mapper.queryBlogIF(map);
            for (Blog blog : blogList) {
                System.out.println(blog);
            }
        }catch(Exception e){
            e.printStackTrace();
        }catch(Error e){
            e.printStackTrace();
        }finally {
            sqlSession.close();
        }
    }
}

    select * from mybatis.blog where 
        
               id=#{id}
        
    and author='lyh';

 
如果传入的id 不为空， 那么才会SQL才拼接id = #{id}。要是你传入的id为null, 那么你这最终的SQL语句就成了 select * from user where and author='lyh';显然，这条SQL语句是有问题的。
 
2.解决办法：通过where标签改造 

    select * from user 
        
            
                id=#{id}
            
            and author='lyh';
        

 
的确，从表面上来看，就是多了个where标签而已， 不过实质上， mybatis是对它做了处理，当它遇到AND或者OR这些，它知道怎么处理。其实我们可以通过 trim 标签去自定义这种处理规则。
 
choose（when、otherwise） 
List queryBlogChoose(Map map);
 

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

    @Test
    public void queryBlogChoose(){
        SqlSession sqlSession = null;
        try{
            sqlSession = MybatisUtils.getSqlSession();
            BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
            HashMap map = new HashMap();
            map.put("title","Java如此简单");
            map.put("author","lyh");
            map.put("views",9999);
            List blogList = mapper.queryBlogChoose(map);
            for (Blog blog : blogList) {
                System.out.println(blog);
            }
        }catch(Exception e){
            e.printStackTrace();
        }catch(Error e){
            e.printStackTrace();
        }finally {
            sqlSession.close();
        }
    }

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

        update mybatis.blog
        
            
                title=#{title},
            
            
                author=#{author}
            
        
        where id=#{id}
    

	@Test
    public void updateBlog(){
        SqlSession sqlSession = null;
        try{
            sqlSession = MybatisUtils.getSqlSession();
            BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
            HashMap map = new HashMap();
            map.put("title","Java如此简单!!!");
            map.put("author","lyh");
            map.put("id","1093918391");
            mapper.updateBlog(map);
            sqlSession.commit();
        }catch(Exception e){
            e.printStackTrace();
        }catch(Error e){
            e.printStackTrace();
        }finally {
            sqlSession.close();
        }
    }

类似于使用动态更新语句的解决方案set，set元素可以用于动态包含需要更新的列，而舍弃其他的列。set元素会动态的前置SET关键字，同时也会删除无关的逗号。