动态SQL_01-CSDN博客

1、简介

动态SQL就是指根据不同的条件生成不同的SQL语句，我们可以在SQL层面，去执行一个逻辑代码。
动态SQL就是在拼接SQL语句，我们只要保证SQL的正确性，按照SQL的格式，去排列组合就可以了。
if
choose (when, otherwise)
trim (where, set)
foreach

2、搭建环境

1、SQL

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、创建一个基础工程；

导入jar包；

<!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.18</version>
        </dependency>

编写核心配置文件；

<?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:mybatis核心配置文件-->
<configuration>
    <!--引入外部配置文件-->
    <properties resource="db.properties">
        <property name="username" value="root"/>
    </properties>
    
    <settings>
        <!--
        STDOUT_LOGGING：标准日志
        mapUnderscoreToCamelCase：启用从经典数据库列名A_COLUMN到驼峰式经典Java属性名aColumn的自动映射。
        -->
        <setting name="logImpl" value="STDOUT_LOGGING"/>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <!--可以给实体类起别名-->
    <typeAliases>
        <typeAlias type="com.beyond.pojo.Blog" alias="Blog"/>
<!--        <package name="com.beyond.pojo"/>-->
    </typeAliases>

    <!--environments：环境-->
    <environments default="development">
        <environment id="development">
            <!--transactionManager：事务-->
            <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>
        <environment id="test">
            <!--transactionManager：事务-->
            <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>
        <mapper class="com.beyond.dao.BlogMapper"/>
    </mappers>

</configuration>

编写实体类和工具类；

package com.beyond.utils;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;

//获取SqlSessionFactory，工具类
public class MyBatisUtil {
    private static SqlSessionFactory sqlSessionFactory;

    static {
        //使用MyBatis获取SqlSessionFactory对象
        String resource = "mybatis-config.xml";
        InputStream inputStream = null;
        try {
            inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //现在有了SqlSessionFactory，您可以获取SqlSession的实例。SqlSession绝对包含对数据库执行SQL命令所需的所有方法。
    // 您可以直接对SqlSession实例执行映射的SQL语句。
    public static SqlSession getSqlSession(){
        //设置自动提交事务为true
        return sqlSessionFactory.openSession(true);
    }
}

package com.beyond.utils;

import java.util.UUID;

public class IdUtil {
    public static void main(String[] args) {
        System.out.println(IdUtil.getId());
    }

    public static String getId() {
        return UUID.randomUUID().toString().replaceAll("-", "");//生成随机id
    }
}

package com.beyond.pojo;

import lombok.Data;

import java.util.Date;

@Data
public class Blog {
    private String id;
    private String title;
    private String author;
    private Date createTime;    //属性名与字段名不一致
    private int views;
}

编写实体类对应的Mapper接口和Mapper.xml文件；

package com.beyond.dao;

import com.beyond.pojo.Blog;

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

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.beyond.dao.BlogMapper">
    <insert id="addBlog" parameterType="blog">
        insert into blog (id,title,author,create_time,views)
        values (#{id},#{title},#{author},#{createTime},#{views})
    </insert>
</mapper>

3、测试

import com.beyond.dao.BlogMapper;
import com.beyond.pojo.Blog;
import com.beyond.utils.IdUtil;
import com.beyond.utils.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.Date;

public class MyTest {
    @Test
    public void addInitBlog(){
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

        Blog blog = new Blog();
        blog.setId(IdUtil.getId());
        blog.setTitle("MyBatis");
        blog.setAuthor("狂神说");
        blog.setCreateTime(new Date());
        blog.setViews(6666);

        mapper.addBlog(blog);

        blog.setId(IdUtil.getId());
        blog.setTitle("java");
        mapper.addBlog(blog);

        blog.setId(IdUtil.getId());
        blog.setTitle("Spring");
        mapper.addBlog(blog);

        blog.setId(IdUtil.getId());
        blog.setTitle("微服务");
        mapper.addBlog(blog);

        sqlSession.close();
    }
}

3、IF

编写Mapper接口和Mapper.xml文件；

package com.beyond.dao;

import com.beyond.pojo.Blog;

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

public interface BlogMapper {
    //查询博客
    List<Blog> queryBlogIf(Map map);
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.beyond.dao.BlogMapper">
    <select id="queryBlogIf" parameterType="map" resultType="Blog">
        select * from blog
        <where>
            <if test="title != null">
                and title=#{title}
            </if>
            <if test="author != null">
                and author=#{author}
            </if>
        </where>
    </select>
</mapper>

测试

import com.beyond.dao.BlogMapper;
import com.beyond.pojo.Blog;
import com.beyond.utils.IdUtil;
import com.beyond.utils.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

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

public class MyTest {
    @Test
    public void queryBlogIf(){
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

        HashMap map = new HashMap();
        map.put("title","java");
        map.put("author","狂神说");

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

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

        sqlSession.close();
    }
}