一 前言

本篇文章的基础是建立在mybatis配置

二 准备工作

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

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。