本篇文章的基础是建立在mybatis配置一文,之前关于自定义类型写的有点模糊,知识追寻者是个有强迫的症的人,又发了一篇,初学者可以查阅知识追寻者专栏学习;文章内容中贴出了关键代码提供个人学习,如果有什么不明白之处,可以查阅知识追寻者的专栏说明获得github源码学习;
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='顾客表'; 复制代码
public class Customer { // 主键 private Long id; // 客户姓名 private String customer_name; // 性别 private String gender; // 电话 private String telephone; // 注册时间 private Long register_time; // 省略 set get } 复制代码
自定义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; } } 复制代码
public interface CustomerMapper { // 添加客户 int addCustomer(Customer customer); // 查询客户 List<Customer> getCustomer(); } 复制代码
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 复制代码