LocalDateTime与mysql日期类型的交互（基于mybatis）

众所周知，在实体Entity里面，可以使用Java.sql.Date、java.sql.Timestamp、java.util.Date来映射到数据库的date、timestamp、datetime等字段，但是，java.sql.Date、java.sql.Timestamp、java.util.Date这些类都不好用，很多方法都过时了。

Java8里面新出来了一些API，LocalDate、LocalTime、LocalDateTime 非常好用，默认的情况下，在mybatis里面不支持java8的时间、日期。直接使用，会报如下错误

Caused by: java.lang.IllegalStateException: No typehandler found for property createTime  
    at org.apache.ibatis.mapping.ResultMapping$Builder.validate(ResultMapping.java:151)  
    at org.apache.ibatis.mapping.ResultMapping$Builder.build(ResultMapping.java:140)  
    at org.apache.ibatis.builder.MapperBuilderAssistant.buildResultMapping(MapperBuilderAssistant.java:382)  
    at org.apache.ibatis.builder.xml.XMLMapperBuilder.buildResultMappingFromContext(XMLMapperBuilder.java:378)  
    at org.apache.ibatis.builder.xml.XMLMapperBuilder.resultMapElement(XMLMapperBuilder.java:280)  
    at org.apache.ibatis.builder.xml.XMLMapperBuilder.resultMapElement(XMLMapperBuilder.java:252)  
    at org.apache.ibatis.builder.xml.XMLMapperBuilder.resultMapElements(XMLMapperBuilder.java:244)  
    at org.apache.ibatis.builder.xml.XMLMapperBuilder.configurationElement(XMLMapperBuilder.java:116)  
    ... 81 common frames omitted  

 导入依赖

<dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>tk.mybatis</groupId>
            <artifactId>mapper-spring-boot-starter</artifactId>
            <version>2.0.2</version>
        </dependency>
        <!-- mybatis数据库字段类型映射，此处是重点 -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-typehandlers-jsr310</artifactId>
            <version>1.0.1</version>
        </dependency>
        <!-- MYSQL驱动包 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

属性配置

spring.datasource.url=jdbc:mysql://localhost:3306/dev?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=false
spring.datasource.username=root
spring.datasource.password=123456
# 如果想看到mybatis日志需要做如下配置
logging.level.com.carry=DEBUG
######### Mybatis 自身配置 ##########
mybatis.mapper-locations=classpath:com/carry/mapper/*Mapper.xml
mybatis.type-aliases-package=com.carry.dto
# 驼峰命名规范 如：数据库字段是  order_id 那么 实体字段就要写成 orderId
mybatis.configuration.map-underscore-to-camel-case=true
######### 通用Mapper ##########
# 主键自增回写方法,默认值MYSQL,详细说明请看文档
mapper.identity=MYSQL
mapper.mappers=tk.mybatis.mapper.common.Mapper
# 设置 insert 和 update 中，是否判断字符串类型!=''
mapper.not-empty=true
# 枚举按简单类型处理
mapper.enum-as-simple-type=true

建表SQL

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `create_date` date DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
)

实体类

package com.carry.dto;

import java.io.Serializable;
import java.time.LocalDate;
import java.time.LocalDateTime;

import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

import com.fasterxml.jackson.annotation.JsonFormat;

public class User implements Serializable{

    private static final long serialVersionUID = -108907189034815108L;
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @JsonFormat(pattern = "yyyy-MM-dd")
    private LocalDate createDate;
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private LocalDateTime createTime;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public LocalDate getCreateDate() {
        return createDate;
    }

    public void setCreateDate(LocalDate createDate) {
        this.createDate = createDate;
    }

    public LocalDateTime getCreateTime() {
        return createTime;
    }

    public void setCreateTime(LocalDateTime createTime) {
        this.createTime = createTime;
    }

}

Mapper类

package com.carry.mapper;

import com.carry.dto.User;

import tk.mybatis.mapper.common.Mapper;

public interface UserMapper extends Mapper<User> {

}

启动类

package com.carry;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

import tk.mybatis.spring.annotation.MapperScan;

@SpringBootApplication
@MapperScan(basePackages = "com.carry.mapper")
public class SpringBootDateMysqlApplication {

public static void main(String[] args) {
        SpringApplication.run(SpringBootDateMysqlApplication.class, args);
    }
}

测试

package com.carry;

import java.time.LocalDate;
import java.time.LocalDateTime;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import com.carry.dto.User;
import com.carry.mapper.UserMapper;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;

@RunWith(SpringRunner.class)
@SpringBootTest
public class SpringBootDateMysqlApplicationTests {

    @Autowired
    private UserMapper mapper;

    @Autowired
    private ObjectMapper objectMapper;

    @Test
    public void insert() {
        User user = new User();
        user.setCreateDate(LocalDate.now());
        user.setCreateTime(LocalDateTime.now());
        mapper.insertSelective(user);
    }

    @Test
    public void query() {
        mapper.selectAll().stream().forEach(e -> {
            try {
                System.out.println(objectMapper.writeValueAsString(e));
            } catch (JsonProcessingException ex) {
                ex.printStackTrace();
            }
        });
    }
}

先后执行测试方法 insert 和 query，无错误说明测试通过

SpringBootDateMysql