mysql mapper设置当前日期_LocalDateTime与mysql日期类型的交互（基于mybatis）

怼烎@ 2022-10-25 16:18 463阅读 0赞

众所周知，在实体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));