A preface

This article is based on the configuration of Mybatis. Before, it was a little vague about the user-defined type. The knowledge seeker is a compulsive person. Article content posted the key code to provide personal learning, if what do not understand, you can refer to the knowledge seeker’s column description to obtain github source code to learn;

Ii. Preparations

2.1 Construction of the predicate sentence

CREATE TABLE `customer` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `customer_name` varchar(255) DEFAULT NULL COMMENT 'Customer Name',
  `gender` varchar(255) DEFAULT NULL COMMENT 'gender',
  `telephone` varchar(255) DEFAULT NULL COMMENT 'Phone number',
  `register_time` timestamp NULL DEFAULT NULL COMMENT 'Registration Time',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Customer list';
Copy the code

2.2 the entity

Public class Customer {// private Long id; // Customer name private String customer_name; // private String gender; Private String telephone; // Register time private Long register_time; / / to omitset get
  }  
Copy the code

Customizing TypeHandler

Custom TypeHandler implements a business logic that converts the timestamp to timestamp format when inserting data; When the query data, then the timestamp format time in the database into the timestamp; Well, knowledge seekers are boring to do this, but it’s easy for readers to understand;

/** * @author LSC * <p> </p> */ @mappedjdbcTypes (jdbctype.timestamp) @mappedTypes (long.class) public class TimeStringHandler<T> extends BaseTypeHandler<T> { public voidsetNonNullParameter(PreparedStatement preparedStatement, int i, T t, JdbcType JdbcType) throws SQLException {// Converts the timestamp to LocalDateTime LocalDateTimelocalDateTime = LocalDateTime.ofInstant(Instant.ofEpochSecond((java.lang.Long) t), ZoneOffset.ofHours(8)); System.out.println(system.out.println ())"Business Logic 1");
        preparedStatement.setString(i,localDateTime.toString());
    }

    public T getNullableResult(ResultSet resultSet, String s) throws SQLException {
        System.out.println("Business Logic 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("Business Logic 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("Business Logic 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; }}Copy the code

Four mappe interface

Public interface CustomerMapper {// addCustomer int addCustomer(Customer Customer); List<Customer> getCustomer(); }Copy the code

5 SQL mapping file

Register_time is used in SQL mapping files for specific data type processing. This does not need to be configured in the global configuration file. It is a good choice to process data for specific fields. The logic implemented here is two-part. Register_time is handled by the type handler when the query is used for return; Insert statements are used to register data into the database using type handler processing using 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>
Copy the code

Six test class

The test class is also divided into two parts, query and new part;

/** * @Author lsc * <p> </p> */ @RunWith(JUnit4.class) public class TypeHandlerTest { SqlSession sqlSession = null; @before Public void Before () throws IOException {// String resource = in the resource path resource directory"mybatis-config.xml"; / / configure mybatis obtain input stream InputStream InputStream = Resources. The getResourceAsStream (resource); SqlSessionFactory SqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); / / get from SqlSessionFactory SqlSession SqlSession = SqlSessionFactory. OpenSession (); } @Test public voidtestInsertCustomerMapper mapper = sqlsession.getMapper (customermapper.class); Customer customer = new Customer(); customer.setCustomer_name("Knowledge seekers");
        customer.setRegister_time(1580739214L);
        customer.setGender("Male");
        customer.setTelephone("999"); // Add mapper.addCustomer(customer); sqlSession.commit(); sqlSession.close(); } @Test public voidtestSelectCustomerMapper 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(); }}Copy the code

Test insert data

Register_time = register_time; register_time = register_time; register_time = register_time; register_time = register_time; register_time = register_time;

DEBUG the 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 ( ?, ?, ?, [DEBUG] 2020-02-03 23:39:33.052 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) ==> Parameters: Knowledge Seeker (String), male (String), 999(String), 2020-02-03 T22: (String) (DEBUG) became the 2020-02-03 23:39:33, 116 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159) <== Updates: 1Copy the code

Test query data

The original database is timestamp support format of the time, out is the timestamp;

DEBUG the 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: Service Logic 2 [DEBUG] 2020-02-03 23:39:00,468 Method: org. Apache. Ibatis. Logging. JDBC. BaseJdbcLogger. Debug (BaseJdbcLogger. Java: 159) < = = Total: 1 the knowledge seeker. "1580739214Copy the code