Mybatis的扩展

1 Mybatis分页插件--PageHelper

导入相关jar包的Maven坐标：

<!-- Mybatis -->
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.4.1</version>
</dependency>
<!-- 日志 -->
<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>1.2.17</version>
</dependency>
<!-- MySQL驱动 -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.21</version>
</dependency>
<!-- Junit单元测试 -->
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.13</version>
    <scope>test</scope>
</dependency>
<!-- 分页插件 -->
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>5.1.11</version>
</dependency>

sql脚本：

DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `last_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `gender` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;


INSERT INTO `employee` VALUES (1, 'jerry', '男', 'jerry@qq.com');
INSERT INTO `employee` VALUES (2, 'aa', '男', 'aa@11.com');
INSERT INTO `employee` VALUES (3, 'bb', '男', 'bb@11.com');
INSERT INTO `employee` VALUES (4, 'aa', '男', 'aa@11.com');
INSERT INTO `employee` VALUES (5, 'bb', '男', 'bb@11.com');

log4j.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
 
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
 
 <appender name="STDOUT" class="org.apache.log4j.ConsoleAppender">
   <param name="Encoding" value="UTF-8" />
   <layout class="org.apache.log4j.PatternLayout">
    <param name="ConversionPattern" value="%-5p %d{MM-dd HH:mm:ss,SSS} %m  (%F:%L) \n" />
   </layout>
 </appender>
 <logger name="java.sql">
   <level value="debug" />
 </logger>
 <logger name="org.apache.ibatis">
   <level value="info" />
 </logger>
 <root>
   <level value="debug" />
   <appender-ref ref="STDOUT" />
 </root>
</log4j:configuration>

Employee.java

package com.sunxiaping.domain;

import org.apache.ibatis.type.Alias;

import java.io.Serializable;

@Alias("emp")
public class Employee implements Serializable {

    private Integer id;

    private String lastName;

    private String email;

    private String gender;

    public Integer getId() {
        return id;
    }

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

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", lastName='" + lastName + '\'' +
                ", email='" + email + '\'' +
                ", gender='" + gender + '\'' +
                '}';
    }
}

EmployeeMapper.java

package com.sunxiaping.mapper;

import com.sunxiaping.domain.Employee;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface EmployeeMapper {

    @Select(" SELECT * FROM employee ")
    @Results({
            @Result(id = true, property = "id", column = "id"),
            @Result(property = "lastName", column = "last_name"),
            @Result(property = "email", column = "email"),
            @Result(property = "gender", column = "gender")}
    )
    List<Employee> findAllEmps();
}

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>
    <!-- 注册插件 -->
    <plugins>
        <!-- com.github.pagehelper为PageHelper类所在包名 -->
        <plugin interceptor="com.github.pagehelper.PageInterceptor">
            <!-- 使用下面的方式配置参数，后面会有所有的参数介绍 -->
            <property name="param1" value="value1"/>
        </plugin>
    </plugins>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url"
                          value="jdbc:mysql://192.168.134.100:3306/test?useUnicode=true&amp;characterEncoding=UTF-8&amp;autoReconnect=true&amp;useSSL=false&amp;serverTimezone=GMT%2B8&amp;allowPublicKeyRetrieval=true"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <package name="com.sunxiaping.mapper"/>
    </mappers>
</configuration>

测试：

package com.sunxiaping;

import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.sunxiaping.domain.Employee;
import com.sunxiaping.mapper.EmployeeMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class EmployeeTest {

    public static void main(String[] args) throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();

        EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);

        Page<Object> page = PageHelper.startPage(3, 2);

        List<Employee> employeeList = employeeMapper.findAllEmps();

        System.out.println("employeeList = " + employeeList);

        System.out.println("当前页码：" + page.getPageNum());
        System.out.println("总记录数：" + page.getTotal());
        System.out.println("每页的记录数：" + page.getPageSize());
        System.out.println("总页码：" + page.getPages());

        sqlSession.close();
    }
}

2 批量操作

BatchExecutor，只需要在SqlSession中设置如下的代码：

SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);

如果Mybatis和Spring整合，则需要额外的配置：

<!--配置一个可以进行批量执行的sqlSession  -->
<bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
	<constructor-arg name="sqlSessionFactory" ref="sqlSessionFactoryBean"></constructor-arg>
	<constructor-arg name="executorType" value="BATCH"></constructor-arg>
</bean>

3 自定义类型处理器

3.1 概述

我们可以通过自定义TypeHandler的形式来设置参数或者取出结果集的时候自定义参数封装策略。

3.2 开发步骤和应用示例

步骤：
- ①实现TypeHandler接口或者继承BaseTypeHandler。
- ②使用@MappedTypes定义处理的java类型，使用@MappedJdbcTypes定义jdbcType类型。
- ③在自定义结果集标签或者参数处理的时候声明使用自定义TypeHandler进行处理或者在全局配置TypeHandler要处理的javaType。
示例：Mybatis默认处理枚举的类型（EnumTypeHandler）
sql脚本：

DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `last_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `gender` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `emp_status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of employee
-- ----------------------------
INSERT INTO `employee` VALUES (1, 'jerry', '男', 'jerry@qq.com', NULL);
INSERT INTO `employee` VALUES (2, 'aa', '男', 'aa@11.com', NULL);
INSERT INTO `employee` VALUES (3, 'bb', '男', 'bb@11.com', NULL);
INSERT INTO `employee` VALUES (4, 'aa', '男', 'aa@11.com', NULL);
INSERT INTO `employee` VALUES (5, 'bb', '男', 'bb@11.com', NULL);
INSERT INTO `employee` VALUES (6, '哈哈', '男', 'haha@qq.com', 'LOGIN');
INSERT INTO `employee` VALUES (7, '哈哈', '男', 'haha@qq.com', 'LOGIN');
INSERT INTO `employee` VALUES (8, '哈哈', '男', 'haha@qq.com', 'LOGIN');
INSERT INTO `employee` VALUES (9, '哈哈', '男', 'haha@qq.com', 'LOGIN');
INSERT INTO `employee` VALUES (10, '哈哈', '男', 'haha@qq.com', 'LOGIN');

EmpStatus.java

package com.sunxiaping.enums;

/**
 * 用户状态
 */
public enum EmpStatus {

    LOGIN, LOGOUT, REMOVE
}

Employee.java

package com.sunxiaping.domain;

import com.sunxiaping.enums.EmpStatus;

import java.io.Serializable;

public class Employee implements Serializable {

    private Integer id;

    private String lastName;

    private String email;

    private String gender;

    private EmpStatus empStatus;

    public EmpStatus getEmpStatus() {
        return empStatus;
    }

    public void setEmpStatus(EmpStatus empStatus) {
        this.empStatus = empStatus;
    }

    public Integer getId() {
        return id;
    }

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

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", lastName='" + lastName + '\'' +
                ", email='" + email + '\'' +
                ", gender='" + gender + '\'' +
                ", empStatus=" + empStatus +
                '}';
    }
}

EmployeeMapper.java

package com.sunxiaping.mapper;

import com.sunxiaping.domain.Employee;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Options;

@Mapper
public interface EmployeeMapper {

    @Insert(" INSERT INTO `employee` (last_name,email,gender,emp_status) VALUES (#{lastName},#{email},#{gender},#{empStatus}) ")
    @Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
    void addEmp(Employee employee);

}

测试：

package com.sunxiaping;

import com.sunxiaping.domain.Employee;
import com.sunxiaping.enums.EmpStatus;
import com.sunxiaping.mapper.EmployeeMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

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

public class EmployeeTest {

    /**
     * 默认情况下，Mybatis在处理枚举对象的时候保存的是枚举的名字：EnumTypeHandler
     *
     * @param args
     * @throws IOException
     */
    public static void main(String[] args) throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();

        EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);

        Employee employee = new Employee();
        employee.setLastName("哈哈");
        employee.setGender("男");
        employee.setEmail("haha@qq.com");
        employee.setEmpStatus(EmpStatus.LOGIN);

        employeeMapper.addEmp(employee);

        System.out.println("employee = " + employee);

        EmpStatus empStatus = EmpStatus.LOGIN;
        System.out.println("枚举的索引" + empStatus.ordinal());
        System.out.println("枚举的名称" + empStatus.name());

        sqlSession.commit();

        sqlSession.close();
    }
}

示例：使用自定义的类型处理器处理枚举类型
sql脚本：

DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `last_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `gender` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `emp_status` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of employee
-- ----------------------------
INSERT INTO `employee` VALUES (1, 'jerry', '男', 'jerry@qq.com', NULL);
INSERT INTO `employee` VALUES (2, 'aa', '男', 'aa@11.com', NULL);
INSERT INTO `employee` VALUES (3, 'bb', '男', 'bb@11.com', NULL);
INSERT INTO `employee` VALUES (4, 'aa', '男', 'aa@11.com', NULL);
INSERT INTO `employee` VALUES (5, 'bb', '男', 'bb@11.com', NULL);

EmpStatus.java

package com.sunxiaping.enums;

/**
 * 用户状态
 */
public enum EmpStatus {

    LOGIN(100, "用户登录"), LOGOUT(200, "用户登出"), REMOVE(300, "用户不存在");

    private Integer code;
    private String name;

    EmpStatus(Integer code, String name) {
        this.code = code;
        this.name = name;
    }

    public Integer getCode() {
        return code;
    }

    public void setCode(Integer code) {
        this.code = code;
    }

    public String getName() {
        return name;
    }

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

EmployeeMapper.java

package com.sunxiaping.mapper;

import com.sunxiaping.domain.Employee;
import org.apache.ibatis.annotations.*;

@Mapper
public interface EmployeeMapper {

    @Insert(" INSERT INTO `employee` (last_name,email,gender,emp_status) VALUES (#{lastName},#{email},#{gender},#{empStatus}) ")
    @Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
    void addEmp(Employee employee);

    @Select(" SELECT * FROM employee WHERE id = #{id} ")
    @Results({
            @Result(id = true, property = "id", column = "id"),
            @Result(property = "lastName",column = "last_name"),
            @Result(property = "gender",column = "gender"),
            @Result(property = "empStatus",column = "emp_status")
    })
    Employee findById(Integer id);

}

SelfTypeHandler.java

package com.sunxiaping.type.handler;

import com.sunxiaping.enums.EmpStatus;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * 自定义枚举处理器
 */
public class SelfTypeHandler extends BaseTypeHandler<EmpStatus> {
    /**
     * 定义当前数据如何保存到数据库中
     *
     * @param ps
     * @param i
     * @param parameter
     * @param jdbcType
     * @throws SQLException
     */
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, EmpStatus parameter, JdbcType jdbcType) throws SQLException {
        ps.setInt(i, parameter.getCode());
    }

    @Override
    public EmpStatus getNullableResult(ResultSet rs, String columnName) throws SQLException {

        int code = rs.getInt(columnName);

        EmpStatus[] empStatuses = EmpStatus.values();
        for (EmpStatus empStatus : empStatuses) {
            if (empStatus.getCode().equals(code)) {
                return empStatus;
            }
        }
        return null;
    }

    @Override
    public EmpStatus getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        int code = rs.getInt(columnIndex);

        EmpStatus[] empStatuses = EmpStatus.values();
        for (EmpStatus empStatus : empStatuses) {
            if (empStatus.getCode().equals(code)) {
                return empStatus;
            }
        }
        return null;
    }

    @Override
    public EmpStatus getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        int code = cs.getInt(columnIndex);

        EmpStatus[] empStatuses = EmpStatus.values();
        for (EmpStatus empStatus : empStatuses) {
            if (empStatus.getCode().equals(code)) {
                return empStatus;
            }
        }
        return null;
    }
}

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>

    <typeHandlers>
        <!-- 配置自定义类型处理器 -->
        <typeHandler handler="com.sunxiaping.type.handler.SelfTypeHandler" javaType="com.sunxiaping.enums.EmpStatus"></typeHandler>
    </typeHandlers>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url"
                          value="jdbc:mysql://192.168.134.100:3306/test?useUnicode=true&amp;characterEncoding=UTF-8&amp;autoReconnect=true&amp;useSSL=false&amp;serverTimezone=GMT%2B8&amp;allowPublicKeyRetrieval=true"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <package name="com.sunxiaping.mapper"/>
    </mappers>
</configuration>

测试：

package com.sunxiaping;

import com.sunxiaping.domain.Employee;
import com.sunxiaping.enums.EmpStatus;
import com.sunxiaping.mapper.EmployeeMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

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

public class EmployeeTest {

    /**
     * 默认情况下，Mybatis在处理枚举对象的时候保存的是枚举的名字：EnumTypeHandler
     *
     * @param args
     * @throws IOException
     */
    public static void main(String[] args) throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();

        EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class);

        Employee employee = new Employee();
        employee.setLastName("哈哈");
        employee.setGender("男");
        employee.setEmail("haha@qq.com");
        employee.setEmpStatus(EmpStatus.LOGIN);

        employeeMapper.addEmp(employee);

        System.out.println("employee = " + employee);

        EmpStatus empStatus = EmpStatus.LOGIN;
        System.out.println("枚举的索引" + empStatus.ordinal());
        System.out.println("枚举的名称" + empStatus.name());


        Employee employeeDb = employeeMapper.findById(1);

        System.out.println("employeeDb = " + employeeDb);

        sqlSession.commit();

        sqlSession.close();
    }
}

posted @ 2020-09-13 09:11 许大仙阅读(458) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

Loading

不为往事扰，余生只爱笑。

Mybatis的扩展

1 Mybatis分页插件--PageHelper

2 批量操作

3 自定义类型处理器

3.1 概述

3.2 开发步骤和应用示例

公告