java springboot mybatis 操作postgres数据库 关于int[]类型数据写入的做法
- 数据库表字段类型的设计
users表：
groups 使用的是int[]类型CREATE TABLE "public"."users" ( "id" int4 NOT NULL DEFAULT nextval('users_id_seq'::regclass), "name" varchar(320) COLLATE "pg_catalog"."default" NOT NULL, "groups" int4[] "disabled_at" timestamptz(6), "updated_at" timestamptz(6) NOT NULL, "created_at" timestamptz(6) NOT NULL, ) - userMapper.xml
<resultMap id="baseResultMap" type="com.dancoder.redash.dao.entity.UserDO"> <result column="id" property="id" jdbcType="INTEGER"></result> <result column="groups" property="groups" typeHandler="com.test.handle.mybatis.ArrayTypeHandler"></result> <result column="name" property="name" jdbcType="VARCHAR"></result> <result column="disabled_at" property="disabledAt" jdbcType="TIMESTAMP"></result> <result column="created_at" property="createdAt" jdbcType="TIMESTAMP"></result> <result column="updated_at" property="updatedAt" jdbcType="TIMESTAMP"></result> </resultMap> xml中insert方法：
<insert id="insert" parameterType="com.dancoder.redash.dao.entity.UserDO"> INSERT INTO users(name, email, groups, created_at, updated_at) VALUES (#{name}, #{email}, #{groups,jdbcType=ARRAY, typeHandler=com.test.handle.mybatis.ArrayTypeHandler}, now(), now()) </insert>创建 ArrayTypeHandler 类：
package com.test.handle.mybatis;
import org.apache.ibatis.type.*;
import java.sql.*;
/**
* @author dancoder
*/
@MappedJdbcTypes(JdbcType.ARRAY)
@MappedTypes(String[].class)
public class ArrayTypeHandler extends BaseTypeHandler<Object[]> {
private static final String TYPE_NAME_VARCHAR = "varchar";
private static final String TYPE_NAME_INTEGER = "integer";
private static final String TYPE_NAME_BOOLEAN = "boolean";
private static final String TYPE_NAME_NUMERIC = "numeric";
@Override
public void setNonNullParameter(PreparedStatement preparedStatement, int i, Object[] parameter, JdbcType jdbcType) throws SQLException {
String typeName = null;
if (parameter instanceof Integer[]) {
typeName = TYPE_NAME_INTEGER;
} else if (parameter instanceof String[]) {
typeName = TYPE_NAME_VARCHAR;
} else if (parameter instanceof Boolean[]) {
typeName = TYPE_NAME_BOOLEAN;
} else if (parameter instanceof Double[]) {
typeName = TYPE_NAME_NUMERIC;
}
if (typeName == null) {
throw new TypeException("ArrayTypeHandler parameter typeName error, your type is " + parameter.getClass().getName());
}
// 创建Array，然后preparedStatement.setArray(i, array)就可以了
Connection conn = preparedStatement.getConnection();
Array array = conn.createArrayOf(typeName, parameter);
preparedStatement.setArray(i, array);
}
@Override
public Object[] getNullableResult(ResultSet resultSet, String s) throws SQLException {
return getArray(resultSet.getArray(s));
}
@Override
public Object[] getNullableResult(ResultSet resultSet, int i) throws SQLException {
return getArray(resultSet.getArray(i));
}
@Override
public Object[] getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
return getArray(callableStatement.getArray(i));
}
private Object[] getArray(Array array) {
if (array == null) {
return null;
}
try {
return (Object[]) array.getArray();
} catch (Exception e) {
}
return null;
}
}
正文到此结束
- 本文标签： Java Spring Boot postgreSql
- 版权声明： 本站原创文章，于2020年12月27日由蛋蛋发布，转载请注明出处
热门推荐
相关文章
该篇文章的评论功能已被站长关闭
免注册登录
