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

小鱼儿 2023-02-20 06:42 18阅读 0赞

### 文章目录 ###

*   *  创建
     *  插入
     *  查询
     *  删除
     *  更新
     *  Mybatis中的使用

## 创建 ##

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

![在这里插入图片描述][20200624084237125.png]  
![在这里插入图片描述][20200624084245126.png]

## 删除 ##

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

## 更新 ##

![在这里插入图片描述][20200624085017654.png]

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

![在这里插入图片描述][20200624085110112.png]

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

![在这里插入图片描述][20200624085138430.png]

## 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.shopcider.plutus.infr.config  
> 则可以在使用时不用指定typeHandler

使用

*  查询

<resultMap id="BaseResultMap" type="com.sinoxk.order.model.Student">
            <result column="student_names" typeHandler="com.sinoxk.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.sinoxk.drug.config.ArrayTypeHandler}
            where
            id= #{id}
        </update>

[20200624084237125.png]: https://img-blog.csdnimg.cn/20200624084237125.png
[20200624084245126.png]: https://img-blog.csdnimg.cn/20200624084245126.png
[20200624085017654.png]: https://img-blog.csdnimg.cn/20200624085017654.png
[20200624085110112.png]: https://img-blog.csdnimg.cn/20200624085110112.png
[20200624085138430.png]: https://img-blog.csdnimg.cn/20200624085138430.png