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/testdriver-class-name: org.postgresql.Driverusername: postgrespassword: 123456

FavirouteBookMapper.xml需要的配置

INSERT INTO faviroute_book (employee_id, books)VALUES (#{employeeId}, #{books, jdbcType=ARRAY, typeHandler=com.power.mytest.handler.ArrayTypeHandler})

以插入和查询单个为例

FavirouteBookMapper.java

@Mapper
public interface FavirouteBookMapper extends BaseMapper {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 {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";@Overridepublic 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);}@Overridepublic Object[] getNullableResult(ResultSet resultSet, String s) throws SQLException {return getArray(resultSet.getArray(s));}@Overridepublic Object[] getNullableResult(ResultSet resultSet, int i) throws SQLException {return getArray(resultSet.getArray(i));}@Overridepublic 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 INTO faviroute_book (employee_id, books)VALUES (#{employeeId}, #{books, jdbcType=ARRAY, typeHandler=org.apache.ibatis.type.ArrayTypeHandler})
select * from faviroute_bookwhere employee_id = #{id}

测试类

@RunWith(SpringRunner.class)
@SpringBootTest(classes = {MyTestApplication.class})
public class Test01 {@Autowiredprivate FavirouteBookService favirouteBookService;@Testpublic void test02() {FavirouteBook favirouteBook = new FavirouteBook();Integer[] arrays = new Integer[] {43,123,321};favirouteBook.setEmployeeId(100);favirouteBook.setBooks(arrays);favirouteBookService.insertFavirouteBook(favirouteBook);}@Testpublic 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 list = favirouteBookService.list();System.out.println(list);System.out.println("==============使用getOne()方法====================");LambdaQueryWrapper 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

查看效果

​

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！