Mybatis

Mapper

association & collection

参考;https://blog.csdn.net/qq_34351177/article/details/106191896

association用于一对一，即A实体中，引用另一个实体B

  

collection用于一对多，即A实体中，引用另一个集合List<B>

JdbcType

// org.apache.ibatis.type.JdbcType


public enum JdbcType {
  /*
   * This is added to enable basic support for the
   * ARRAY data type - but a custom type handler is still required
   */
  ARRAY(Types.ARRAY),
  BIT(Types.BIT),
  TINYINT(Types.TINYINT),
  SMALLINT(Types.SMALLINT),
  INTEGER(Types.INTEGER),
  BIGINT(Types.BIGINT),
  FLOAT(Types.FLOAT),
  REAL(Types.REAL),
  DOUBLE(Types.DOUBLE),
  NUMERIC(Types.NUMERIC),
  DECIMAL(Types.DECIMAL),
  CHAR(Types.CHAR),
  VARCHAR(Types.VARCHAR),
  LONGVARCHAR(Types.LONGVARCHAR),
  DATE(Types.DATE),
  TIME(Types.TIME),
  TIMESTAMP(Types.TIMESTAMP),
  BINARY(Types.BINARY),
  VARBINARY(Types.VARBINARY),
  LONGVARBINARY(Types.LONGVARBINARY),
  NULL(Types.NULL),
  OTHER(Types.OTHER),
  BLOB(Types.BLOB),
  CLOB(Types.CLOB),
  BOOLEAN(Types.BOOLEAN),
  CURSOR(-10), // Oracle
  UNDEFINED(Integer.MIN_VALUE + 1000),
  NVARCHAR(Types.NVARCHAR), // JDK6
  NCHAR(Types.NCHAR), // JDK6
  NCLOB(Types.NCLOB), // JDK6
  STRUCT(Types.STRUCT),
  JAVA_OBJECT(Types.JAVA_OBJECT),
  DISTINCT(Types.DISTINCT),
  REF(Types.REF),
  DATALINK(Types.DATALINK),
  ROWID(Types.ROWID), // JDK6
  LONGNVARCHAR(Types.LONGNVARCHAR), // JDK6
  SQLXML(Types.SQLXML), // JDK6
  DATETIMEOFFSET(-155), // SQL Server 2008
  TIME_WITH_TIMEZONE(Types.TIME_WITH_TIMEZONE), // JDBC 4.2 JDK8
  TIMESTAMP_WITH_TIMEZONE(Types.TIMESTAMP_WITH_TIMEZONE); // JDBC 4.2 JDK8

  public final int TYPE_CODE;
  private static Map<Integer,JdbcType> codeLookup = new HashMap<>();

  static {
    for (JdbcType type : JdbcType.values()) {
      codeLookup.put(type.TYPE_CODE, type);
    }
  }

  JdbcType(int code) {
    this.TYPE_CODE = code;
  }

  public static JdbcType forCode(int code)  {
    return codeLookup.get(code);
  }

}

Type aliases for common Java types

https://mybatis.org/mybatis-3/configuration.html

与SpringBoot进行整合

Mybatis 与 SpringBoot 版本对应

http://mybatis.org/spring-boot-starter/mybatis-spring-boot-autoconfigure/

The MyBatis-Spring-Boot-Starter requires following versions:

PageHelper

https://github.com/pagehelper/Mybatis-PageHelper

版本

https://github.com/pagehelper/pagehelper-spring-boot

demo

<?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.5.14</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>pagehelper-demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>pagehelper-demo</name>
    <description>pagehelper-demo</description>
    <properties>
        <java.version>1.8</java.version>
        <mybatis-spring-boot.version>2.2.2</mybatis-spring-boot.version>
        <pagehelper.boot.version>1.4.3</pagehelper.boot.version>
    </properties>
    <dependencies>
        <!-- Mysql驱动包 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <!-- SpringBoot集成mybatis框架 -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>${mybatis-spring-boot.version}</version>
        </dependency>

        <!-- pagehelper 分页插件 -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>${pagehelper.boot.version}</version>
        </dependency>

        
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

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

</project>

application.yml

server:
  port: 8081
spring:
  # datasource 数据源配置内容，对应 DataSourceProperties 配置属性类
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/test_wp?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&failOverReadOnly=false&serverTimezone=GMT%2B8
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: root # 数据库账号
    password: es0s1oJvm
    # HikariCP 自定义配置，对应 HikariConfig 配置属性类
    hikari:
      minimum-idle: 10 # 池中维护的最小空闲连接数，默认为 10 个。
      maximum-pool-size: 10 # 池中最大连接数，包括闲置和使用中的连接，默认为 10 个。


mybatis:
  mapper-locations:
  - classpath:mybatis/mapper/*.xml
  config-location: classpath:mybatis/mybatisConfig.xml
  type-aliases-package: com.example.pagehelperdemo.domain

pagehelper:
  helperDialect: mysql

mybatisConfig.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>
    <!--在控制台打印出 sql语句-->
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING" />
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

</configuration>

UserMapper.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.example.pagehelperdemo.mapper.UserMapper">
    <select id="getUserByName" parameterType="string" resultType="com.example.pagehelperdemo.domain.User">
        select * from user where  name like concat('%',#{userName,jdbcType=VARCHAR},'%')
    </select>


    <select id="getUserByDeptId" parameterType="string" resultType="com.example.pagehelperdemo.domain.User">
        select * from user where  dept_id=#{deptId}
    </select>

</mapper>

package com.example.pagehelperdemo.mapper;

import com.example.pagehelperdemo.domain.User;

import java.util.List;

public interface UserMapper {

    public List<User> getUserByName(String userName);

    public List<User> getUserByDeptId(String deptId);
}

package com.example.pagehelperdemo;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@MapperScan("com.example.pagehelperdemo.mapper")  // 这个很重要，一定要加上
@SpringBootApplication
public class PagehelperDemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(PagehelperDemoApplication.class, args);
    }

}

package com.example.pagehelperdemo.domain;

public class User {

    private String id;

    private String name;

    private String age;

    private String deptId;

    private boolean enable;

    public String getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAge() {
        return age;
    }

    public void setAge(String age) {
        this.age = age;
    }

    public String getDeptId() {
        return deptId;
    }

    public void setDeptId(String deptId) {
        this.deptId = deptId;
    }

    public boolean isEnable() {
        return enable;
    }

    public void setEnable(boolean enable) {
        this.enable = enable;
    }
}

package com.example.pagehelperdemo.controller;

import com.example.pagehelperdemo.domain.User;
import com.example.pagehelperdemo.mapper.UserMapper;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.github.pagehelper.PageHelper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
@RequestMapping("/page")
public class PageController {

    @Autowired
    private UserMapper userMapper;

    @Autowired
    private ObjectMapper objectMapper;


    @RequestMapping("/deptId")
    public String selectByPage(@RequestParam("deptId") String deptId, @RequestParam("pageNum") int pageNum, @RequestParam("pageSize") int pageSize) throws JsonProcessingException {
        PageHelper.startPage(pageNum, pageSize);
        List<User> users = userMapper.getUserByDeptId(deptId);
        System.out.println(objectMapper.writeValueAsString(users));
        return "ok";
    }
}

测试

http://localhost:8081/page/deptId?deptId=111&pageNum=2&pageSize=3

PageHelper源码解析

refer to : https://blog.csdn.net/u014240299/article/details/120613600

java doc

https://apidoc.gitee.com/free/Mybatis_PageHelper/

collection & 一个部门多个人

public class Dept {
    private String deptId;

    private List<String> userNames;
    
    // get set
    
}

public interface DeptMapper {
    List<Dept> getDeptById(String deptId);

}

  <select id="getDeptById" parameterType="string" resultMap="TMqttSubscribeConfigResult">
        select dept_id, name from user where dept_id in ('111','222')
    </select>
    <resultMap type="com.example.pagehelperdemo.domain.Dept" id="TMqttSubscribeConfigResult">
        <result property="deptId"    column="dept_id"    />
        <collection ofType="string" property="userNames">
            <result column="name"></result>
        </collection>
    </resultMap>

sqlmap-xml

parameterType

The fully qualified class name or alias for the parameter that will be passed into this statement.

This attribute is optional because MyBatis can calculate the TypeHandler to use out of the actual parameter passed to the statement. Default is unset.

• 如果参数是list，那么parameterType=java.util.List
• 如果参数是map，那么parameterType=java.util.Map
• 如果参数是实体类person，那么parameterType=com.wp.domain.Person
• 如果参数是int/string等，那么使用mybatis提供的Type aliases

resultType

• 如果返回是集合，那么resultType 的值，是集合内存储数据的类型，不是 'list'。

<select id="getAllEmps" resultType="employee">
        select * from t_employee
    </select>

xml 大于|小于|等于 写法及介绍

refer to : https://www.cnblogs.com/yangyanrui/p/mybatis-xml-zhong-de-da-yu-xiao-yu-deng-yu.html

方法一：使用xml 原生转义的方式进行转义

简单代码示例：

sql语句

select * from user where `age` <= 10

xml 语句

<select id="getUser"  resultType="userInfo">
    select * from user 
        where `age`  &lt;= #{age}
</select>

写法注意

• 转义字符 之前不能有空格，例如：& gt; 或者 &g t; 都是错误的
• 转义字符 必须以; 结尾
• 转义字符 是区分大小写的
• 单独出现的&不会被认为是转义的开始
• 转义字符 中是没有 = 所以 大于等于 在写的时候，=直接拼接在转义字符结尾的后面

以上转义符号在 【MarkDown】 中也同样试用会被转义，
在【MarkDown】文档中想要保留展示这些转移符号，
需要在转义符前面写上一个 '\',例如：\>

方法二：使用 <![CDATA[ 内容 ]]>

在 xml 格式中，当遇到 <![CDATA[ ]]> 这种格式时，会把方块中的内容直接输出，不做任何的解析转义

简单代码示例：

sql语句

select * from user where `age` <= 10

xml 语句

<select id="getUser"  resultType="userInfo">
    select * from user 
        where `age`  <![CDATA[ <= ]]>  #{age}
</select>


<select id="getUser"  resultType="userInfo">
    select * from user 
        <![CDATA[ where `age` <= #{age} ]]>
</select>

写法注意

<![CDATA[ 内容 ]]> 中的内容 部分，不能再包含 <![CDATA[]]> ，也不能嵌套使用
<![CDATA[]]> 是一个整体，处理放置内容文本的部分之外，不能出现 空格 或者 换行

两种方法对比

<![CDATA[]]> 比 转义符 来的繁琐
<![CDATA[]]> 表示xml解析器忽略解析，相对比速度会比较快
个人比较喜欢使用 <![CDATA[]]> ，因为我记不住那个转义的意思

批量更新

refer to: https://www.cnblogs.com/personsiglewine/p/12889619.html

通过循环，依次执行多条update的sql

要实现批量更新，首先得设置mysql支持批量操作，在jdbc链接中需要附加&allowMultiQueries=true属性才行
例如：
jdbc:mysql://218.93.228.30:3306/iot?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8&allowMultiQueries=true

<update id="updateBatch"  parameterType="java.util.List">
    <foreach collection="list" item="item" index="index" open="" close="" separator=";">
        update course
        <set>
            name=${item.name}
        </set>
        where id = ${item.id}
    </foreach>
</update>

这种方式，一条记录update一次，性能比较差，容易造成阻塞。

实际实践(传入的是List<Map<String, Object>>)

https://www.cnblogs.com/personsiglewine/p/12889619.html