一 前言

本篇文章的基礎(chǔ)是建立在mybatis配置

二 準(zhǔn)備工作

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實現(xiàn)一個業(yè)務(wù)邏輯就是 當(dāng)插入數(shù)據(jù)時可以將時間戳轉(zhuǎn)為timestamp格式；當(dāng)查詢數(shù)據(jù)得時候再將數(shù)據(jù)庫中得timestamp格式時間轉(zhuǎn)為時間戳；好吧知識追尋者也是無聊透頂了做這種操作，不過易于讀者理解；

/** * @Author lsc * <p> 一個無聊的業(yè)務(wù)邏輯 輸入的是時間戳，到數(shù)據(jù)庫中的是 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 { // 將 時間戳轉(zhuǎn)為 LocalDateTime LocalDateTime localDateTime = LocalDateTime.ofInstant(Instant.ofEpochSecond((java.lang.Long) t), ZoneOffset.ofHours(8)); // 參數(shù)設(shè)置 System.out.println('業(yè)務(wù)邏輯1'); preparedStatement.setString(i,localDateTime.toString()); } public T getNullableResult(ResultSet resultSet, String s) throws SQLException { System.out.println('業(yè)務(wù)邏輯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('業(yè)務(wù)邏輯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('業(yè)務(wù)邏輯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中做專門得數(shù)據(jù)類型處理，這樣不用配置到全局配置文件中，可以針對特定字段處理是個不錯得選擇；這邊實現(xiàn)得邏輯是兩個部分，查詢語句用于返回時將register_time使用類型處理器處理；插入語句用于將數(shù)據(jù)進入數(shù)據(jù)庫時使用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 type='customer' autoMapping='true'> <result column='register_time' property='register_time' typeHandler='com.zszxz.typehandler.handler.TimeStringHandler'></result> </resultMap> <select resultMap='customerMap' > select * from `customer` </select> <insert 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 會在執(zhí)行測試類之前執(zhí)行該方法 @Before public void before() throws IOException { // 資源路徑 resource目錄下 String resource = 'mybatis-config.xml'; // 配置mybatis獲得輸入流 InputStream inputStream = Resources.getResourceAsStream(resource); // 創(chuàng)建 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(); }}

七 測試插入數(shù)據(jù)

插入數(shù)據(jù)時原本register_time是時間戳，從打印得SQL參數(shù)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 ( ?, ?, ?, ? ) 業(yè)務(wù)邏輯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

八 測試查詢數(shù)據(jù)

原本數(shù)據(jù)庫中是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: 業(yè)務(wù)邏輯2[DEBUG] 2020-02-03 23:39:00,468 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159)<== Total: 1知識追尋者1580739214

以上就是本文的全部內(nèi)容，希望對大家的學(xué)習(xí)有所幫助，也希望大家多多支持好吧啦網(wǎng)。