MyBatis-动态SQL（一）：环境搭建

环境搭建

1、建表

CREATE TABLE blog(
	id VARCHAR(50) NOT NULL COMMENT '博客Id',
	title VARCHAR(100) NOT NULL COMMENT '博客标题',
	author VARCHAR(30) NOT NULL COMMENT '博客作者',
	createTime datetime NOT NULL COMMENT '创作时间',
	views int(30) NOT NULL COMMENT'浏览量'
)ENGINE=INNODB DEFAULT CHARSET=utf8

2、配置文件

①mybatis-config.xml

<?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>

    <properties resource="db.properties"/>

    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <typeAliases>
        <typeAlias type="com.hjy.pojo.Blog" alias="Blog"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <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.hjy.mapper.BlogMapper"/>
    </mappers>

</configuration>

②db.properties

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?useSSL=false&useUnicode=true&characterEncoding=UTF8&serverTimezone=UTC
username=root
password=@12345Hjy$

③MyBatisUtils

public class MyBatisUtils {
    private static SqlSessionFactory sqlSessionFactory;

    static {
        try {
            String resource="mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    public static SqlSession getSqlSession(){
            return sqlSessionFactory.openSession(true);
       // return sqlSessionFactory.openSession(true);  如果不设置参数或者参数为false就是手动提交事务，参数设置为true就是自动提交事务
    }

}

 

3、实体类：

public class Blog {

    private String id;
    private String title;
    private String author;
    private Date createTime;
    private int views;
    
    +构造器
    +get set 方法
    +toString 方法

}

 

4、插入测试用数据

①BlogMapper接口

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

}

②BlogMapper.xml

<?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.hjy.mapper.BlogMapper">
    <insert id="addBlog" parameterType="blog">
        insert into mybatis.blog(id,title,author,createTime,views)
        values(#{id},#{title},#{author},#{createTime},#{views})
    </insert>
</mapper>

②工具类

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


    @Test
    public void test(){
        System.out.println(IDUtils.getId());
        System.out.println(IDUtils.getId());
        System.out.println(IDUtils.getId());
    }


}

④插入数据

package com.hjy.mapper;

import com.hjy.pojo.Blog;
import com.hjy.utils.IDUtils;
import com.hjy.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.Date;

public class MapperTest {

    @Test
    public void myTest(){
        SqlSession session= MyBatisUtils.getSqlSession();
        BlogMapper mapper = session.getMapper(BlogMapper.class);

        Blog blog=new Blog();
        blog.setId(IDUtils.getId());
        blog.setTitle("深入理解JVM");
        blog.setAuthor("周志明");
        blog.setCreateTime(new Date());
        blog.setViews(9999);

        mapper.addBlog(blog);

        blog.setId(IDUtils.getId());
        blog.setTitle("SpringBoot编程实战");
        blog.setViews(4325);
        mapper.addBlog(blog);

        blog.setId(IDUtils.getId());
        blog.setTitle("算法");
        blog.setViews(8888);
        mapper.addBlog(blog);

        blog.setId(IDUtils.getId());
        blog.setTitle("剑指Offer");
        blog.setViews(2345);
        mapper.addBlog(blog);

        session.close();
    }

}

二：动态SQL--IF语句

1、BlogMapper

public interface BlogMapper {

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

2、BlogMapper.xml

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

3、测试

   @Test
    public void queryBlogIFTest(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

        HashMap map = new HashMap();
        map.put("title","算法");
        List<Blog> blogs = mapper.queryBlogIF(map);
        for (Blog blog:blogs) {
            System.out.println(blog);
        }
        sqlSession.close();
    }