众所周知,在实体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