一、代码链接
https://gitee.com/xuexionghui/springbootdemo/tree/master/mybatis/mybatis__type_handler |
二、自定义类型转换器
三、问题
在Java中的属性是String[]以及Date类型的,在数据库中对应字段存储的数据类型是Varchar以及bigint类型的,这个时候就要用到类型转换器,不然会出错,SQL语句执行失败。
第一步:SQL脚本
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for cat
-- ----------------------------
DROP TABLE IF EXISTS `cat`;
CREATE TABLE `cat` (
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`birthdy` bigint(255) NULL DEFAULT NULL,
`hobby` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of cat
-- ----------------------------
INSERT INTO `cat` VALUES ('阿猫', 1676635989932, '[]');
INSERT INTO `cat` VALUES ('阿猫', 1676636082765, '[]');
SET FOREIGN_KEY_CHECKS = 1;
第二步:引入依赖
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.daxiong</groupId>
<artifactId>mybatis__type_handler</artifactId>
<version>1.0-SNAPSHOT</version>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.5.RELEASE</version>
<relativePath/>
</parent>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- Spring Boot Mybatis 依赖 -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
<!-- MySQL 连接驱动依赖 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.28</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.2.1</version>
</dependency>
</dependencies>
</project>
第三步:实体
package com.daxiong.entity;
import java.util.Date;
/**
* <p>TODO</p>
*
* @author xuexionghui
* @describe
* @date 2023/2/17 17:58
*/
public class Cat {
private String name;
private Date birthdy;
private String[] hobby;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Date getBirthdy() {
return birthdy;
}
public void setBirthdy(Date birthdy) {
this.birthdy = birthdy;
}
public String[] getHobby() {
return hobby;
}
public void setHobby(String[] hobby) {
this.hobby = hobby;
}
public Cat() {
}
}
第四步:自定义typeHandler
package com.daxiong.handler;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* <p>string数组和数据库varchar转化的typeHandler</p>
*
* @author xuexionghui
* @describe 将Java的属性数据类型String[]转化到JSON字符串保存到数据库的varchar数据类型的字段中,
* 将从数据中查询到varchar数据类型的字段转化为Java的属性数据类型是String[]
* @date 2023/2/17 18:00
*/
public class ArrayToStringTypeHandler extends BaseTypeHandler<String[]> {
private static final ObjectMapper mapper = new ObjectMapper();
/**
* 将Java中的数据类型,转换成数据库中的数据类型
* @param ps 预编译处理器
* @param i 开始的位置,这个可以不用管,直接传级可以
* @param parameter 这个是Java程序要转化后存储到数据库中的参数
* @param jdbcType
* @throws SQLException
*/
@Override
public void setNonNullParameter(PreparedStatement ps, int i, String[] parameter, JdbcType jdbcType) throws SQLException {
// 数据库中的目标数据类型是varchar,所以预编译器调用的方法是setString ,ps可以调用很多转成数据库中目标类型的方法
ps.setString(i,toJsonString(parameter));
}
/**
* 将从数据库中查询到的数据类型转换成Java中的数据类型
* @param rs 结果集
* @param columnName 列的名字
* @return
* @throws SQLException
*/
@Override
public String[] getNullableResult(ResultSet rs, String columnName) throws SQLException {
return toObject(rs.getString(columnName));
}
/**
*
* @param rs 结果集
* @param columnIndex 列的索引
* @return
* @throws SQLException
*/
@Override
public String[] getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
String s = rs.getString(columnIndex);
return toObject(s);
}
/**
*
* @param cs 带回调的statement
* @param columnIndex 列的索引
* @return
* @throws SQLException
*/
@Override
public String[] getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
String s = cs.getString(columnIndex);
return toObject(s);
}
/**
* 将string数组转化成JSON字符串,从而存储到数据库的字段类型是varchar的字段上
* @param parameter
* @return
*/
private String toJsonString(String[] parameter) {
try {
String s = mapper.writeValueAsString(parameter);
return s;
} catch (JsonProcessingException e) {
e.printStackTrace();
}
return "[]";
}
/**
* 将数据库中查询得到的数据类型是varchar的字段转化为String数组
* @param content
* @return
*/
private String[] toObject(String content) {
if (content != null && !content.isEmpty()) {
try {
return (String[]) mapper.readValue(content, String[].class);
} catch (Exception e) {
throw new RuntimeException(e);
}
} else {
return null;
}
}
}
package com.daxiong.handler;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
/**
* <p>Date类型转换器</p>
*
* @author xuexionghui
* @describe 将Java中的Date转化成数据库的bigint
* 将数据库中的bigint转换成Java中的Date
* @date 2023/2/17 19:48
*/
public class DateToStringTypeHandler extends BaseTypeHandler<Date> {
/**
* 将Java中的数据类型,转换成数据库中的数据类型
*
* @param ps 预编译处理器
* @param i 开始的位置,这个可以不用管,直接传级可以
* @param date 这个是Java程序要转化后存储到数据库中的参数
* @param jdbcType
* @throws SQLException
*/
@Override
public void setNonNullParameter(PreparedStatement ps, int i, Date date, JdbcType jdbcType) throws SQLException {
// 数据库中的目标数据类型是varchar,所以预编译器调用的方法是setString ,ps可以调用很多转成数据库中目标类型的方法
long time = date.getTime(); // 将date日期数据,转为long类型的毫秒数
ps.setLong(i,time);
}
/**
* 将从数据库中查询到的数据类型转换成Java中的数据类型
*
* @param rs 结果集
* @param columnName 列的名字
* @return
* @throws SQLException
*/
@Override
public Date getNullableResult(ResultSet rs, String columnName) throws SQLException {
try {
// 根据字段名获取数据库中存的long类型的毫秒数
long aLong = rs.getLong(columnName);
Date date = new Date(aLong);
return date;
}catch (Exception e){
}
return null;
}
/**
*
* @param rs 结果集
* @param columnIndex 字段索引值
* @return
* @throws SQLException
*/
@Override
public Date getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
try {
long aLong = rs.getLong(columnIndex);
Date date = new Date(aLong);
return date;
}catch (Exception e){
}
return null;
}
/**
*
* @param cs
* @param columnIndex 字段索引值
* @return
* @throws SQLException
*/
@Override
public Date getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
try {
long aLong = cs.getLong(columnIndex);
Date date = new Date(aLong);
return date;
}catch (Exception e){
}
return null;
}
}
第五步:application.properties文件
spring.datasource.druid.url=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&verifyServerCertificate=false&useSSL=false
spring.datasource.druid.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.druid.username=root
spring.datasource.druid.password=root
spring.datasource.druid.initial-size=10
spring.datasource.druid.max-active=50
spring.datasource.druid.min-idle=10
spring.datasource.druid.max-wait=60000
spring.datasource.druid.pool-prepared-statements=true
spring.datasource.druid.max-pool-prepared-statement-per-connection-size=20
spring.datasource.druid.validation-query=SELECT 1 FROM DUAL
spring.datasource.druid.test-on-borrow=false
spring.datasource.druid.test-on-return=false
spring.datasource.druid.test-while-idle=true
spring.datasource.druid.time-between-eviction-runs-millis=60000
spring.datasource.druid.webStatFilter.enabled=true
spring.datasource.druid.statViewServlet.enabled=false
spring.datasource.druid.filter.stat.enabled=false
#打印执行的SQL语句
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
mybatis.mapper-locations=classpath:mapper/*.xml
第六步:controller
package com.daxiong.controller;
import com.daxiong.entity.Cat;
import com.daxiong.service.TestService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
/**
* <p>TODO</p>
*
* @author xuexionghui
* @describe
* @date 2023/2/17 17:55
*/
@RestController
public class TestController {
@Autowired
private TestService testService;
@GetMapping("/add")
public String addUser(){
int result=testService.addUser();
return result ==1 ?"添加成功":"添加失败";
}
@GetMapping("/list")
public List<Cat> list(){
return testService.list();
}
}
第七步:service层及实现
package com.daxiong.service;
import com.daxiong.entity.Cat;
import java.util.List;
/**
* <p>TODO</p>
*
* @author xuexionghui
* @describe
* @date 2023/2/17 17:55
*/
public interface TestService {
int addUser();
List<Cat> list();
}
package com.daxiong.service.impl;
import com.daxiong.entity.Cat;
import com.daxiong.dao.TestDao;
import com.daxiong.service.TestService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.Date;
import java.util.List;
/**
* <p>TODO</p>
*
* @author xuexionghui
* @describe
* @date 2023/2/17 17:55
*/
@Service
public class TestServiceImpl implements TestService {
@Autowired
private TestDao testDao;
@Override
public int addUser() {
Cat cat = new Cat();
cat.setName("阿猫");
cat.setBirthdy(new Date());
String[] arr = {"睡觉", "抓老鼠", "找小母猫约会"};
cat.setHobby(arr);
return testDao.addUser(cat);
}
@Override
public List<Cat> list() {
return testDao.list();
}
}
第八、dao层及对应的xml
package com.daxiong.dao;
import com.daxiong.entity.Cat;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
/**
* <p>TODO</p>
*
* @author xuexionghui
* @describe
* @date 2023/2/17 17:56
*/
@Mapper
public interface TestDao {
int addUser(Cat cat);
List<Cat> list();
}
<?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.daxiong.dao.TestDao">
<resultMap id="resultmap1" type="com.daxiong.entity.Cat">
<result column="name" property="name"></result>
<result column="birthdy" property="birthdy" typeHandler="com.daxiong.handler.DateToStringTypeHandler"></result>
<result column="hobby" property="hobby" typeHandler="com.daxiong.handler.ArrayToStringTypeHandler"></result>
</resultMap>
<insert id="addUser">
insert into cat values (#{name},
#{birthdy,typeHandler=com.daxiong.handler.DateToStringTypeHandler},
#{hobby,typeHandler=com.daxiong.handler.ArrayToStringTypeHandler})
</insert>
<sql id="sql1">
name,birthdy,hobby
</sql>
<select id="list" resultMap="resultmap1">
select
<include refid="sql1"></include>
from cat
</select>
</mapper>