Mybatis

YINMK/MybatisDemos

大于等于小于写法

第一种写法:

原符号 <  <=  >  >=  & ' "
替换符号 &lt; &lt;= &gt; &gt;= &amp; &apos; &quot;
例如:sql如下:
create_date_time &gt;= #{startTime} and create_date_time &lt;= #{endTime}

第二种写法:
大于等于
<![CDATA[ >= ]]>
小于等于
<![CDATA[ <= ]]>
例如:sql如下:
create_date_time <![CDATA[ >= ]]> #{startTime} and create_date_time <![CDATA[ <= ]]> #{endTime}

SpringBoot整合Mybatis基础版

https://gitee.com/yinmk/MybatisDemos/tree/master/

创建表插入数据

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '一号', '123456');

SET FOREIGN_KEY_CHECKS = 1;

依赖导入

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.2.6.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.kai</groupId>
    <artifactId>mybatis_demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>mybatis_demo</name>
    <description>Demo project for mybatis</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.2</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

配置文件

spring:
  #配置数据库连接
  datasource:
    username: root
    password: 123456
    url: jdbc:mysql://192.168.11.128:3306/mybatis?user=root&password=&useUnicode=true&characterEncoding=utf-8&autoReconnect=true&serverTimezone=Asia/Shanghai&failOverReadOnly=false
    driver-class-name: com.mysql.cj.jdbc.Driver

mybatis:
  #自动配置别名
  type-aliases-package: com.kai.pojo
  #配置mapper文件的位置
  mapper-locations: classpath:mapper/*.xml

创建实体类

package com.kai.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class User {
    private Integer id;
    private String username;
    private String password;
}

创建mapper接口

package com.kai.mapper;

import com.kai.pojo.User;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;

import java.util.List;

@Mapper
@Repository
public interface UserMapper {
    /**
     * 查询所有用户信息
     * @return
     */
    List<User> findAll();
}

创建XMl映射文件

<?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.kai.mapper.UserMapper">
    <select id="findAll" resultType="User">
        select * from user
    </select>
</mapper>

创建controller,调用mapper接口

package com.kai.controller;

import com.kai.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class UserController {

    @Autowired
    private UserMapper userMapper;

    @GetMapping("/user")
    public String listUser(){
        return userMapper.findAll().toString();
    }

}

启动项目浏览器访问此接口进行测试

动态SQL

https://gitee.com/yinmk/MybatisDemos/tree/DynamicSQL

动态sql的四种语句

if
choose(when otherwise)
trim(where set)
foreach

if

如果传入了某个值,就追加一段SQL

mapper接口添加方法

/**
 * 动态SQL的IF语句
 * @param map 传入参数
 * @return
 */
List<Blog> findBlogIf(Map<String,Object> map);

映射文件增加查询

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

调用方法

@GetMapping("/if")
public String listIf(){
    Map<String,Object> map = new HashMap<>();
    //map.put("author","admin");
    map.put("title","最近好吗");
    return blogMapper.findBlogIf(map).toString();
}

可以发现,接口返回的数据根据参数的变化动态改变了

choose(when otherwise)

类似java的switch语句

mapper接口添加方法

/**
 * 动态SQL的choose语句
 * @param map 传入参数
 * @return
 */
List<Blog> findBlogChoose(Map<String,Object> map);

映射文件增加查询
此处的where标签,会自动去除第一个条件的and或者or语句

<select id="findBlogChoose" resultType="Blog" parameterType="map">
    select * from blog
    <where>
        <choose>
            <when test="title != null">
                and title = #{title}
            </when>
            <when test="author != null">
                and author = #{author}
            </when>
            <otherwise>
                and views > #{views}
            </otherwise>
        </choose>
    </where>
</select>

调用方法

@GetMapping("/choose")
public String listchoose(){
    Map<String,Object> map = new HashMap<>();
    map.put("views",5000);
    return blogMapper.findBlogChoose(map).toString();
}

trim(where set)

mapper接口添加方法

/**
 * 动态SQL的choose语句
 * @param map 传入参数
 * @return
 */
Integer updateBlog(Map<String,Object> map);

映射文件增加查询

set语句会动态的前置set关键字,并且自动去除无关的逗号

<update id="updateBlog" parameterType="map">
    update blog
    <set>
        <if test="title != null">
            title = #{title},
        </if>
        <if test="author != null">
            author = #{author}
        </if>
    </set>
    where id = #{id}
</update>

调用方法

@PutMapping("/set")
public String setUpdate(@RequestBody Map<String,Object> map){
    Integer result = blogMapper.updateBlog(map);
    return result.toString();
}

测试接口,请求体使用json,可以测试没有title或者author时查看服务器对数据的操作情况

{
	"id":"1588570811810",
	"title":"学习springboot1",
	"author":"admin"
}

SQL片段

抽取出SQL的公共部分方便取用

抽取前的映射文件

<?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.kai.mapper.BlogMapper">

    <select id="findAll" resultType="Blog">
        select * from blog
    </select>

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

    <select id="findBlogChoose" resultType="Blog" parameterType="map">
        select * from blog
        <where>
            <choose>
                <when test="title != null">
                    and title = #{title}
                </when>
                <when test="author != null">
                    and author = #{author}
                </when>
                <otherwise>
                    and views > #{views}
                </otherwise>
            </choose>
        </where>
    </select>

    <update id="updateBlog" parameterType="map">
        update blog
        <set>
            <if test="title != null">
                title = #{title},
            </if>
            <if test="author != null">
                author = #{author}
            </if>
        </set>
        where id = #{id}
    </update>

    <insert id="add" parameterType="Blog">
        INSERT INTO `mybatis`.`blog`(`id`, `title`, `author`, `created_time`, `views`)
        VALUES (#{id},#{title}, #{author}, #{createdTime}, #{views})
    </insert>
</mapper>

抽取之后的映射文件
使用SQL标签存储SQL语句的公共部分,在需要使用的地方使用include标签引入即可

<?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.kai.mapper.BlogMapper">
    
    <sql id="if-title-author">
        <if test="title != null">
            and title = #{title}
        </if>
        <if test="author != null">
            and author = #{author}
        </if>
    </sql>

    <select id="findAll" resultType="Blog">
        select * from blog
    </select>

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

    <select id="findBlogChoose" resultType="Blog" parameterType="map">
        select * from blog
        <where>
            <choose>
                <when test="title != null">
                    and title = #{title}
                </when>
                <when test="author != null">
                    and author = #{author}
                </when>
                <otherwise>
                    and views > #{views}
                </otherwise>
            </choose>
        </where>
    </select>

    <update id="updateBlog" parameterType="map">
        update blog
        <set>
            <if test="title != null">
                title = #{title},
            </if>
            <if test="author != null">
                author = #{author}
            </if>
        </set>
        where id = #{id}
    </update>

    <insert id="add" parameterType="Blog">
        INSERT INTO `mybatis`.`blog`(`id`, `title`, `author`, `created_time`, `views`)
        VALUES (#{id},#{title}, #{author}, #{createdTime}, #{views})
    </insert>

</mapper>

ForEach

mapper接口添加方法

/**
 * 动态SQL的Foreach语句
 * @param map 传入参数
 * @return
 */
List<Blog> findBlogForeach(Map<String,Object> map);

映射文件增加查询

list是存在map中的集合的key,item是遍历出来的对象,open是拼接的开始,close是拼接的结束,separator是分隔符

<select id="findBlogForeach" resultType="Blog" parameterType="map">
    select * from blog
    <where>
        <foreach collection="list" item="id" open="and (" close=")" separator="or">
            id = #{id}
        </foreach>
    </where>
</select>

调用方法

 * @return
 */
@GetMapping("/foreach")
public String listForeach(){
    Map<String,Object> map = new HashMap<>();
    List<Long> list = new ArrayList<>();
    list.add(1588570808809L);
    list.add(1588571272951L);
    list.add(1588571353715L);

    map.put("list",list);
    return blogMapper.findBlogForeach(map).toString();
}

相当于执行了以下查询语句

select * from blog where 1=1 and (id = 1588570808809 or id = 1588571272951 or id = 1588571353715);