笔记

 

Mybatis动态SQL

1.创建数据库

create table mybatis.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          not null comment '浏览量'
)
    charset = utf8;

INSERT INTO mybatis.blog (id, title, author, create_time, views) VALUES ('1', 'xg标题', '作者1', '2020-12-24 19:15:41', 30);
INSERT INTO mybatis.blog (id, title, author, create_time, views) VALUES ('2', 'xg标题1', '作者11', '2020-12-25 19:16:08', 90);
INSERT INTO mybatis.blog (id, title, author, create_time, views) VALUES ('4', '你好', '作者2', '2020-12-23 19:16:31', 17);
INSERT INTO mybatis.blog (id, title, author, create_time, views) VALUES ('6', '11', 'gg', '2020-12-23 09:16:48', 8);
INSERT INTO mybatis.blog (id, title, author, create_time, views) VALUES ('7b038d2b1926437fb2d690132c6f7849', '标题1', '作者2', '2020-12-23 11:54:22', 30);
INSERT INTO mybatis.blog (id, title, author, create_time, views) VALUES ('8647eebb78a54826a87195a69f57b0be', '标题2', '作者1', '2020-12-23 11:54:23', 60);
INSERT INTO mybatis.blog (id, title, author, create_time, views) VALUES ('d86e6548645a4203b476699013cc0694', '标题3', '作者1', '2020-12-23 11:54:23', 50);
INSERT INTO mybatis.blog (id, title, author, create_time, views) VALUES ('95b3f4269c064f02824071056a9d59b9', '标题4', '作者2', '2020-12-23 11:54:23', 60);

 

2.创建maven项目,导入依赖

<dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.12</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.2</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-web</artifactId>
            <version>5.1.9.RELEASE</version>
        </dependency>

 

3.编写mybatis工具类

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();
    }
}

 

编写mybatis的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="logImpl" value="LOG4J"/>-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <!--  给实体类起别名方式2:扫描包  -->
    <typeAliases>
        <package name="com.gg.pojo"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${pwd}"/>
            </dataSource>

        </environment>
    </environments>

    <!--  绑定接口  -->
    <mappers>
        <mapper resource="com/gg/dao/BlogMapper.xml"/>
    </mappers>

</configuration>

 

补充db.properties文件

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false&serverTimezone=GMT
username=root
pwd=123456

 

4.实体类

package com.gg.pojo;

import org.apache.ibatis.type.Alias;

import java.util.Date;

@Alias("Blog")
public class Blog {
    private String id;
    private String title;
    private String author;
    private Date createTime;    //属性名和字段名不一样
    private int views;

    public Blog() {
    }

    public Blog(String id, String title, String author, Date createTime, int views) {
        this.id = id;
        this.title = title;
        this.author = author;
        this.createTime = createTime;
        this.views = views;
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getAuthor() {
        return author;
    }

    public void setAuthor(String author) {
        this.author = author;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    public int getViews() {
        return views;
    }

    public void setViews(int views) {
        this.views = views;
    }

    @Override
    public String toString() {
        return "Blog{" +
                "id=" + id +
                ", title='" + title + '\'' +
                ", author='" + author + '\'' +
                ", createTime=" + createTime +
                ", views=" + views +
                '}';
    }
}

 

5.mapper与mapper.xml

public interface BlogMapper {
    int addBlog(Blog blog);

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

    List<Blog> queryBlogChoose(Map map);

    int updateBlog(Map map);

    //查询id为1,2,6的blog
    List<Blog> queryBlogForeach(Map map);

    List<Blog> queryBlogForeach1(Map map);

    List<Blog> querySql1(HashMap map);
}

 

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.gg.dao.BlogMapper">

    <select id="querySql1" parameterType="map" resultType="Blog">
        select *
        from mybatis.Blog
        <where>
            <if test="author != null">
                author=#{author}
            </if>
            and views in
            <foreach collection="views" item="view" open="(" close=")" separator=",">
                #{view}
            </foreach>
            and title in
            <foreach collection="titles" item="title" open="(" close=")" separator=",">
                #{title}
            </foreach>
        </where>
    </select>


    <insert id="addBlog" parameterType="Blog">
        insert into mybatis.Blog (id,title,author,create_time,views)
        values (#{id},#{title},#{author},#{createTime},#{views})
    </insert>

    <sql id="if-title-author">
        <if test="title!=null">
            and title=#{title}
        </if>
        <if test="author!=null">
            and author=#{author}
        </if>
    </sql>

    <select id="queryBlogIF" parameterType="map" resultType="Blog">
        select * from mybatis.blog
        <where>
            <include refid="if-title-author"/>
        </where>
    </select>

    <sql id="choose-when">

        <choose>
            <when test="title!=null">
                <!--              select * from mybatis.user where name like "%"#{value}"%"-->
                and title like "%"#{title}"%"
            </when>

            <when test="author!=null">
                and author=#{author}
            </when>

            <otherwise>
                and 1=1
            </otherwise>
        </choose>

    </sql>

    <select id="queryBlogChoose" parameterType="map" resultType="Blog">
        select * from mybatis.blog
        <where>
            <include refid="choose-when"/>
        </where>
    </select>

    <update id="updateBlog" parameterType="map">
        update mybatis.blog
        <!-- 自动干掉逗号(,) -->
        <set>
            <if test="title!=null">
                title=#{title},
            </if>
            <if test="author!=null">
                author=#{author},
            </if>
        </set>
        where id=#{id}

    </update>

    <select id="queryBlogForeach" parameterType="map" resultType="Blog">
        select * from blog

        <where>
            <foreach collection="ids" item="id" open="(" close=")" separator="or">
                id=#{id}
            </foreach>
            <if test="author!=null">
                and author=#{author}
            </if>
        </where>
    </select>

    <select id="queryBlogForeach1" parameterType="map" resultType="Blog">
        select * from blog
        <where>
            id in
            <foreach collection="ids" item="id" open="(" close=")" separator=",">
                #{id}
            </foreach>
        </where>
    </select>


</mapper>

 

6.测试

public class GgTest {

    /**
     * 查询博客作者为xxx阅读量为30,50,60的博客
     * select * from blog
     * where author='作者2'
     * and title in ('标题1','标题2')
     * and views in (30,50,60)
     */
    @Test
    public void sql1(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();

        ArrayList<String> titles = new ArrayList<String>();
        
        titles.add("标题1");
        titles.add("标题2");
        map.put("titles", titles);
        map.put("author", "作者2");

        List<Integer> views = new ArrayList<Integer>();

        views.add(30);
        views.add(50);
        views.add(60);
        map.put("views", views);

        List<Blog> blogList = blogMapper.querySql1(map);

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

        sqlSession.close();
    }
    
    
    @Test
    public void addBlog() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
        blogMapper.addBlog(new Blog(IDutils.getId(), "标题1", "作者1", new Date(), 20));
        blogMapper.addBlog(new Blog(IDutils.getId(), "标题2", "作者2", new Date(), 20));
        blogMapper.addBlog(new Blog(IDutils.getId(), "标题3", "作者3", new Date(), 20));
        blogMapper.addBlog(new Blog(IDutils.getId(), "标题4", "作者4", new Date(), 20));

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

    @Test
    public void queryBlogIF() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();
        map.put("title", "标题2");
        map.put("author", "作者2");
        List<Blog> blogList = blogMapper.queryBlogIF(map);

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

        sqlSession.close();
    }

    @Test
    public void queryBlogChoose() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();
//        map.put("title","标题");
        map.put("author", "作者2");
        List<Blog> blogList = blogMapper.queryBlogChoose(map);

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

        sqlSession.close();
    }

    @Test
    public void updateBlog() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();
        map.put("title", "xg标题1");
        map.put("author", "作者11");
        map.put("id", "2");
        blogMapper.updateBlog(map);

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

    @Test
    public void queryBlogForeach() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();
        ArrayList<String> ids = new ArrayList<String>();

        ids.add("1");
        ids.add("7b038d2b1926437fb2d690132c6f7849");
        System.out.println(ids);
        map.put("ids", ids);
        map.put("author","作者1");

        List<Blog> blogList = blogMapper.queryBlogForeach(map);

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

        sqlSession.close();
    }

    @Test
    public void queryBlogForeach1() {
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();
        ArrayList<String> ids = new ArrayList<String>();

        ids.add("1");
        ids.add("7b038d2b1926437fb2d690132c6f7849");
        ids.add("d86e6548645a4203b476699013cc0694");
        System.out.println(ids);
        map.put("ids", ids);


        List<Blog> blogList = blogMapper.queryBlogForeach1(map);

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

        sqlSession.close();
    }
}