Mybatis自定义typeHandle过程解析
一 前言
本篇文章的基础是建立在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
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持我们。