Postgresql使用数组及Mybatis自定义Postgresql数组类型映射

创建

CREATE TABLE faviroute_book (
id serial primary key,
student_names TEXT[]
)

在普通类型后面加 [] 即可

插入

-- 方式一 使用{}
insert into student(student_names) values ('{阿离,小奏}')
-- 方式二  使用 array
insert into student(student_names) VALUES (array['韩信','关羽','张飞'])

查询

select * from student
select student_names[2] from student

删除

delete from student where student_names[3] = '张飞'
delete from  student where id = 3

更新

update student set student_names[1] = '阿离' where  id = 1

update student  set student_names[3] = '李白' where  id = 1

Mybatis中的使用

第一种更新操作比较容易， 自己拼接下标

 update student
        set student_names[#{index}] = #{name}
        where
        id= #{id}

第二种自定义数组类型

创建 ArrayTypeHandler

@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 ps, 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());
        }

        // 这3行是关键的代码，创建Array，然后ps.setArray(i, array)就可以了
        Connection conn = ps.getConnection();
        Array array = conn.createArrayOf(typeName, parameter);
        ps.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;
    }

}

ps： 在mybatis 配置中添加配置
mybatis.type-handlers-package=com.zou.infr.config
则可以在使用时不用指定typeHandler

使用

• 查询

<resultMap id="BaseResultMap" type="com.zou.order.model.Student">
        <result column="student_names" typeHandler="com.zou.drug.config.ArrayTypeHandler"  property="studentNames"/>
    </resultMap>

<select id="getConfig" resultMap="BaseResultMap">
        select * from student
</select>

• 更新

List<Student> updateNames(@Param("id") Integer id,@Param("names") Integer... names);

<update id="updateNames">
        update student
        set student_names= #{names,typeHandler = com.zou.drug.config.ArrayTypeHandler}
        where
        id= #{id}
    </update>