Mybatis系列--13-动态sql之环境搭建及if语句

Mybatis系列--13-动态sql之环境搭建及if语句

概述

本文是动态sql学习的基础环境的搭建, 也涉及if语句

实践

在数据库中创建表

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

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <artifactId>Mybatis-Study</artifactId>
        <groupId>org.example</groupId>
        <version>1.0-SNAPSHOT</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>Mybatis-Study07-dynamicSql</artifactId>
    <dependencies>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.24</version>
        </dependency>
    </dependencies>
    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>
</project>

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"></properties>
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true" />
    </settings>
    <typeAliases>
        <package name="com.com.com.kuang.pojo" />
    </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>
        <package name="com.kuang.dao" />
    </mappers>

</configuration>

Blog.java

package com.kuang.pojo;

import java.util.Date;

import lombok.Data;

/**
 * 功能描述
 *
 * @since 2022-07-22
 */
@Data
public class Blog {
    private String id;
    private String title;
    private String author;
    private Date createTime;
    private int views;
}

IdUtil.java

package com.kuang.util;

import java.util.UUID;

/**
 * 功能描述
 *
 * @since 2022-07-22
 */
public class IdUtil {
    public static String getId() {
        return UUID.randomUUID().toString().replaceAll("-", "");
    }
}

MyBatisUtil.java

package com.kuang.util;

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

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

/**
 * 功能描述
 *
 * @since 2022-07-13
 */
public class MyBatisUtil {
    private static SqlSessionFactory sqlSessionFactory;

    static {

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

    }

    public static SqlSession getSqlsession() {
        return sqlSessionFactory.openSession(true);
    }
}

BlogMapper.java

package com.kuang.dao;

import java.util.List;

import org.apache.ibatis.annotations.Param;

import com.kuang.pojo.Blog;

/**
 * 功能描述
 *
 * @since 2022-07-22
 */
public interface BlogMapper {
    void addBlog(Blog blog);

    List<Blog> getBlogs(@Param("title") String title, @Param("author") String author);
}

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.kuang.dao.BlogMapper">
    <insert id="addBlog" parameterType="com.kuang.pojo.Blog">
        insert into blog (id, title, author, create_time, views)
        values (#{id}, #{title}, #{author}, #{createTime}, #{views})
    </insert>

    <select id="getBlogs" resultType="blog">
        select * from blog where 1=1
        <if test="title != null">
            and title = #{title}
        </if>
        <if test="author != null">
            and author = #{author}
        </if>
    </select>
</mapper>

测试类MyTest.java

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

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import com.kuang.dao.BlogMapper;
import com.kuang.pojo.Blog;
import com.kuang.util.IdUtil;
import com.kuang.util.MyBatisUtil;

/**
 * 功能描述
 *
 * @since 2022-07-22
 */
public class MyTest {
    @Test
    public void TestAdd() {
        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(9999);

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

    @Test
    public void TestGetBlogs() {
        SqlSession sqlsession = MyBatisUtil.getSqlsession();
        BlogMapper mapper = sqlsession.getMapper(BlogMapper.class);
        List<Blog> blogs = mapper.getBlogs(null, "狂神说");
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlsession.close();

    }

}