MyBatis PostgreSQL实现数组类型的操作

我的GitHub：Powerveil · GitHub

我的Gitee：Powercs12 (powercs12) - Gitee.com

皮卡丘每天学Java

最近在学习数据库PostgreSQL，遇到如何实现对数组类型的数据操作，试着自己尝试学习实现。

话不多说，直接撸代码。

建表语句 PostgreSQL DDL

-- auto-generated definition
create table faviroute_book
(
    employee_id integer,
    books       integer[]
);

insert into faviroute_book values (1,ARRAY [1,2,55]);
insert into faviroute_book values (2,ARRAY [1,2,23]);
insert into faviroute_book values (3,ARRAY [1,23,554]);

select *
from faviroute_book;

备注：integer

​

 application.yml

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/test
    driver-class-name: org.postgresql.Driver
    username: postgres
    password: 123456

FavirouteBookMapper.xml需要的配置

<resultMap id="BaseResultMap" type="com.power.mytest.domain.FavirouteBook">
    <result property="employeeId" column="employee_id" jdbcType="INTEGER"/>
<!--        <result property="books" column="books" jdbcType="ARRAY" typeHandler="org.apache.ibatis.type.ArrayTypeHandler"/>-->
<!--这里使用resultMap会识别到，注意这里说的是mybatis/>-->
    <result property="books" column="books" jdbcType="ARRAY"
            typeHandler="com.power.mytest.handler.ArrayTypeHandler"/>
</resultMap>


<insert id="insertFavirouteBook" parameterType="com.power.mytest.domain.FavirouteBook">
    INSERT INTO faviroute_book (employee_id, books)
    VALUES (#{employeeId}, #{books, jdbcType=ARRAY, typeHandler=com.power.mytest.handler.ArrayTypeHandler})
</insert>

以插入和查询单个为例

FavirouteBookMapper.java

@Mapper
public interface FavirouteBookMapper extends BaseMapper<FavirouteBook> {

    int insertFavirouteBook(FavirouteBook favirouteBook);

    FavirouteBook getOneByIdMy(Integer id);
}

插入方法时需要加上jdbcType=ARRAY, typeHandler=org.apache.ibatis.type.ArrayTypeHandler

typeHandler是一个数组类型转换器，

查询方法时，resultMap的数组字段要加上jdbcType=ARRAY, typeHandler=org.apache.ibatis.type.ArrayTypeHandler

这里我使用的是apache的

也可以使用自定义，当然apache更全，下面是一个自定义的数组类型转换器

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeException;

import java.sql.*;

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;
    }
}

对应的xml

<insert id="insertFavirouteBook" parameterType="com.power.mytest.domain.FavirouteBook">
    INSERT INTO faviroute_book (employee_id, books)
    VALUES (#{employeeId}, #{books, jdbcType=ARRAY, typeHandler=org.apache.ibatis.type.ArrayTypeHandler})
</insert>

<select id="getOneByIdMy" resultMap="BaseResultMap">
    select * from faviroute_book
    where employee_id = #{id}
</select>

测试类

@RunWith(SpringRunner.class)
@SpringBootTest(classes = {MyTestApplication.class})
public class Test01 {

    @Autowired
    private FavirouteBookService favirouteBookService;

    @Test
    public void test02() {
        FavirouteBook favirouteBook = new FavirouteBook();
        Integer[] arrays = new Integer[] {43,123,321};
        favirouteBook.setEmployeeId(100);
        favirouteBook.setBooks(arrays);
        favirouteBookService.insertFavirouteBook(favirouteBook);
    }


    @Test
    public void test03() {
        FavirouteBook oneByIdMy = favirouteBookService.getOneByIdMy(1);
        System.out.println(oneByIdMy);
    }
}

效果

插入

​

 ​

查询

​

如果要使用MyBatis Plus查询，而且使用内置的方法就不可以了

测试方法

@Test
public void test01() {
    System.out.println("Hello World");
    System.out.println("==============使用list()方法====================");
    List<FavirouteBook> list = favirouteBookService.list();
    System.out.println(list);
    System.out.println("==============使用getOne()方法====================");
    LambdaQueryWrapper<FavirouteBook> queryWrapper = new LambdaQueryWrapper<>();
    queryWrapper.eq(FavirouteBook::getEmployeeId, 1);
    FavirouteBook favirouteBook = favirouteBookService.getOne(queryWrapper);
    System.out.println(favirouteBook);
    System.out.println(Arrays.toString(favirouteBook.getBooks()));
}

效果

​

怎么解决呢？

需要在实体类中加入一些配置，必须加入的有

autoResultMap = true

@TableField(value = "books", jdbcType = JdbcType.ARRAY, typeHandler = ArrayTypeHandler.class)

@TableName(value ="faviroute_book", autoResultMap = true)
@Data
public class FavirouteBook implements Serializable {
    @TableField(value = "employee_id")
    private Integer employeeId;
    @TableField(value = "books", jdbcType = JdbcType.ARRAY, typeHandler = ArrayTypeHandler.class)
    private Integer[] books;
}

为了让框架识别通过实体类加过直接生成的resultMap，框架默认识别不了我们自己写的resultMap

查看效果

​