Mybatis动态SQL环境及常用标签

动态SQL概述和环境搭建

编写一个基础工程编写辅助工具测试数据插入根据传入参数查询博客 解决if标签问题

解决方案1解决方案2 测试相关操作

更新博客数据导入sql片段测试foreach标签

什么是动态SQL：就是根据不同的条件生成不同的SQL语句，例如拼接SQL

导包导依赖编写配置文件编写实体类编写实体类对应的Mapper接口和xml文件 编写辅助工具

开启驼峰命名转换

编写id随机生成工具类

package com.bkms.utils;

import java.util.UUID;


public class IdUtils {
    public static String getRandomId() {
        return UUID.randomUUID().toString().replaceAll("-","");
    }
}

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

    @Test
    // 测试插入数据
    public void incrementBlogTest() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Blog blog = new Blog();

        blog.setId(IdUtils.getRandomId());
        blog.setTitle("Mybatis");
        blog.setAuthor("Philip");
        blog.setCreateTime(new Date());
        blog.setViews(9999);

        mapper.incrementBlog(blog);

        blog.setId(IdUtils.getRandomId());
        blog.setTitle("Java");
        mapper.incrementBlog(blog);

        blog.setId(IdUtils.getRandomId());
        blog.setTitle("Spring");
        mapper.incrementBlog(blog);

        blog.setId(IdUtils.getRandomId());
        blog.setTitle("微服务");
        mapper.incrementBlog(blog);

        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    // 测试博客查询
    public void searchBlogIfTest() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();
        map.put("title","Spring");
        List blogs = mapper.searchBlogIf(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.close();
    }

    
    
        select * from mydb.blog
        where 1=1
        
            and title = #{title}
        
        
            and author = #{author}
        
    
 
解决if标签问题 
解决方案1 
	
    
        select * from mydb.blog
        
            
                title = #{title}
            
            
                and name = #{name}
            
        
    

    
    
        select * from mydb.blog
        
            
                
                    title = #{title}
                
                
                    and author = #{author}
                
                
                    and views = #{views}
                
            
        
    
 
测试相关操作 
更新博客数据 
	
    
    
        update mydb.blog
        
            
                title = #{title},
            
            
                name = #{name}
            
        
    
 
导入sql片段 
	
    
        
            title = #{title}
        
        
            and name = #{name}
        
    
    
    
    
        select * from mydb.blog
        
            
        
    

sql片段使用注意事项

最好基于单表定义sql字段，不要封装太过复杂的操作不要存在where标签 测试foreach标签

	
    
    
        
            
                id = #{id}