mybatis自定义typeHandler

1,075 阅读4分钟

一 前言

本篇文章的基础是建立在mybatis配置一文,之前关于自定义类型写的有点模糊,知识追寻者是个有强迫的症的人,又发了一篇,初学者可以查阅知识追寻者专栏学习;文章内容中贴出了关键代码提供个人学习,如果有什么不明白之处,可以查阅知识追寻者的专栏说明获得github源码学习;

二 准备工作

2.1建表语句

CREATE TABLE `customer` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `customer_name` varchar(255) DEFAULT NULL COMMENT '顾客名称',
  `gender` varchar(255) DEFAULT NULL COMMENT '性别',
  `telephone` varchar(255) DEFAULT NULL COMMENT '电话号码',
  `register_time` timestamp NULL DEFAULT NULL COMMENT '注册时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='顾客表';

2.2 实体

public class Customer {

    // 主键
    private Long id;
    // 客户姓名
    private String customer_name;
    // 性别
    private String gender;
    // 电话
    private String telephone;
    // 注册时间
    private Long register_time;
    // 省略 set get
  }  

三 自定义TypeHandler

自定义TypeHandler实现一个业务逻辑就是 当插入数据时可以将时间戳转为timestamp格式;当查询数据得时候再将数据库中得timestamp格式时间转为时间戳;好吧知识追寻者也是无聊透顶了做这种操作,不过易于读者理解;

/**
 * @Author lsc
 * <p> 一个无聊的业务逻辑 输入的是时间戳,到数据库中的是 timestamp 格式 输出的又是时间戳 </p>
 */
@MappedJdbcTypes(JdbcType.TIMESTAMP)
@MappedTypes(Long.class)
public class TimeStringHandler<T> extends BaseTypeHandler<T> {


    public void setNonNullParameter(PreparedStatement preparedStatement, int i, T t, JdbcType jdbcType) throws SQLException {
        // 将 时间戳转为 LocalDateTime
        LocalDateTime localDateTime = LocalDateTime.ofInstant(Instant.ofEpochSecond((java.lang.Long) t), ZoneOffset.ofHours(8));
        // 参数设置
        System.out.println("业务逻辑1");
        preparedStatement.setString(i,localDateTime.toString());
    }

    public T getNullableResult(ResultSet resultSet, String s) throws SQLException {
        System.out.println("业务逻辑2");
        String time = resultSet.getString(s);
        LocalDateTime localDateTime = LocalDateTime.parse(time, DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
        Long second = localDateTime.toEpochSecond(ZoneOffset.ofHours(8));
        return (T) second;
    }

    public T getNullableResult(ResultSet resultSet, int i) throws SQLException {
        System.out.println("业务逻辑3");
        String time = resultSet.getString(i);
        LocalDateTime localDateTime = LocalDateTime.parse(time, DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
        Long second = localDateTime.toEpochSecond(ZoneOffset.ofHours(8));
        return (T) second;
    }

    public T getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        System.out.println("业务逻辑4");
        String time = callableStatement.getString(i);
        LocalDateTime localDateTime = LocalDateTime.parse(time, DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
        Long second = localDateTime.toEpochSecond(ZoneOffset.ofHours(8));
        return (T) second;
    }
}

四 mappe接口

public interface CustomerMapper {

    // 添加客户
    int addCustomer(Customer customer);
    // 查询客户
    List<Customer> getCustomer();
}

五 sql映射文件

sql映射文件中在使用得字段register_time中做专门得数据类型处理,这样不用配置到全局配置文件中,可以针对特定字段处理是个不错得选择;这边实现得逻辑是两个部分,查询语句用于返回时将register_time使用类型处理器处理;插入语句用于将数据进入数据库时使用register_time使用类型处理器处理。

<?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.zszxz.typehandler.mapper.CustomerMapper">

    <resultMap id="customerMap" type="customer" autoMapping="true">
        <result column="register_time" property="register_time" typeHandler="com.zszxz.typehandler.handler.TimeStringHandler"></result>
    </resultMap>

    <select id="getCustomer" resultMap="customerMap" >
        select * from `customer`
    </select>

    <insert id="addCustomer" parameterType="customer">
         insert into `customer`(
            `customer_name`,
            `gender`,
            `telephone`,
            `register_time`
         )values (
            #{customer_name},
            #{gender},
            #{telephone},
            #{register_time,javaType=Long,jdbcType=TIMESTAMP,typeHandler=com.zszxz.typehandler.handler.TimeStringHandler}
         )

    </insert>

</mapper>

六测试类

测试类 也是分为2部分,查询和新增部分;

/**
 * @Author lsc
 * <p> </p>
 */
@RunWith(JUnit4.class)
public class TypeHandlerTest {

    SqlSession sqlSession = null;

    // @Before 会在执行测试类之前执行该方法
    @Before
    public void before() throws IOException {
        // 资源路径 resource目录下
        String resource = "mybatis-config.xml";
        // 配置mybatis获得输入流
        InputStream inputStream = Resources.getResourceAsStream(resource);
        // 创建 SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //从 SqlSessionFactory 中获取 SqlSession
        sqlSession= sqlSessionFactory.openSession();
    }

    @Test
    public void testInsert(){
        // 获得mapper的形式
        CustomerMapper mapper = sqlSession.getMapper(CustomerMapper.class);
        Customer customer = new Customer();
        customer.setCustomer_name("知识追寻者");
        customer.setRegister_time(1580739214L);
        customer.setGender("男");
        customer.setTelephone("999");
        // 添加客户
        mapper.addCustomer(customer);
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void testSelect(){
        // 获得mapper的形式
        CustomerMapper mapper = sqlSession.getMapper(CustomerMapper.class);
        List<Customer> customerList = mapper.getCustomer();
        for (Customer customer :customerList){
            System.out.println(customer.getCustomer_name());
            System.out.println(customer.getRegister_time());
        }
        sqlSession.commit();
        sqlSession.close();
    }
}

七 测试插入数据

插入数据时原本register_time是时间戳,从打印得SQL参数2020-02-03T22:13:34(String)可以看见入库时就变成了timestamp支持的格式入库;

[DEBUG] 2020-02-03 23:39:33,018 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159)
==>  Preparing: insert into `customer`( `customer_name`, `gender`, `telephone`, `register_time` )values ( ?, ?, ?, ? ) 
业务逻辑1
[DEBUG] 2020-02-03 23:39:33,052 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159)
==> Parameters: 知识追寻者(String), 男(String), 999(String), 2020-02-03T22:13:34(String)
[DEBUG] 2020-02-03 23:39:33,116 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159)
<==    Updates: 1

八 测试查询数据

原本数据库中是timestamp支持的格式得时间,出来就是时间戳;

[DEBUG] 2020-02-03 23:39:00,371 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159)
==>  Preparing: select * from `customer` 
[DEBUG] 2020-02-03 23:39:00,410 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159)
==> Parameters: 
业务逻辑2
[DEBUG] 2020-02-03 23:39:00,468 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159)
<==      Total: 1
知识追寻者
1580739214