iBATIS date MySQL_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 forproperty 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

导入依赖

org.springframework.boot

spring-boot-starter-web

tk.mybatis

mapper-spring-boot-starter

2.0.2

org.mybatis

mybatis-typehandlers-jsr310

1.0.1

mysql

mysql-connector-java

org.springframework.boot

spring-boot-starter-test

test

属性配置

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 NULLAUTO_INCREMENT,

`create_date` dateDEFAULT NULL,

`create_time`datetime DEFAULT NULL,PRIMARY KEY(`id`)

)

实体类

packagecom.carry.dto;importjava.io.Serializable;importjava.time.LocalDate;importjava.time.LocalDateTime;importjavax.persistence.GeneratedValue;importjavax.persistence.GenerationType;importjavax.persistence.Id;importcom.fasterxml.jackson.annotation.JsonFormat;public class User implementsSerializable{private static final long serialVersionUID = -108907189034815108L;

@Id

@GeneratedValue(strategy=GenerationType.IDENTITY)privateLong id;

@JsonFormat(pattern= "yyyy-MM-dd")privateLocalDate createDate;

@JsonFormat(pattern= "yyyy-MM-dd HH:mm:ss")privateLocalDateTime createTime;publicLong getId() {returnid;

}public voidsetId(Long id) {this.id =id;

}publicLocalDate getCreateDate() {returncreateDate;

}public voidsetCreateDate(LocalDate createDate) {this.createDate =createDate;

}publicLocalDateTime getCreateTime() {returncreateTime;

}public voidsetCreateTime(LocalDateTime createTime) {this.createTime =createTime;

}

}

Mapper类

packagecom.carry.mapper;importcom.carry.dto.User;importtk.mybatis.mapper.common.Mapper;public interface UserMapper extends Mapper{

}

启动类

packagecom.carry;importorg.springframework.boot.SpringApplication;importorg.springframework.boot.autoconfigure.SpringBootApplication;importtk.mybatis.spring.annotation.MapperScan;

@SpringBootApplication

@MapperScan(basePackages= "com.carry.mapper")public classSpringBootDateMysqlApplication {

public static voidmain(String[] args) {

SpringApplication.run(SpringBootDateMysqlApplication.class, args);}

}

测试

packagecom.carry;importjava.time.LocalDate;importjava.time.LocalDateTime;importorg.junit.Test;importorg.junit.runner.RunWith;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.boot.test.context.SpringBootTest;importorg.springframework.test.context.junit4.SpringRunner;importcom.carry.dto.User;importcom.carry.mapper.UserMapper;importcom.fasterxml.jackson.core.JsonProcessingException;importcom.fasterxml.jackson.databind.ObjectMapper;

@RunWith(SpringRunner.class)

@SpringBootTestpublic classSpringBootDateMysqlApplicationTests {

@AutowiredprivateUserMapper mapper;

@AutowiredprivateObjectMapper objectMapper;

@Testpublic voidinsert() {

User user= newUser();

user.setCreateDate(LocalDate.now());

user.setCreateTime(LocalDateTime.now());

mapper.insertSelective(user);

}

@Testpublic voidquery() {

mapper.selectAll().stream().forEach(e->{try{

System.out.println(objectMapper.writeValueAsString(e));

}catch(JsonProcessingException ex) {

ex.printStackTrace();

}

});

}

}

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