原创

java springboot mybatis 操作postgres数据库 关于int[]类型数据写入的做法

  1. 数据库表字段类型的设计
    users表:
    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,
    )
    
    groups 使用的是int[]类型
  2. 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>
    
  3. 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>
    
  4. 创建 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;
    }
}
正文到此结束
该篇文章的评论功能已被站长关闭
本文目录