mybatis中如何选择文本数组类型的结果？

Question

例如text[]，在 PostgreSQL 中有一个表，它的列类型为 ：

CREATE TABLE t

(

    id integer,

    name text,

    tags text[],

    PRIMARY KEY (id)

)

现在，我想通过tags两种方式进行选择：

tags使用主键选择id，结果应该是类型List<String>

选择tagsusing name，结果应该是 typeList<List<String>>

我应该如何编写 MyBatis 映射器来实现这一点？

Answer 1

你仍然可以使用 Java 映射器，但是，SqlSession#selectList当返回类型是List并且这不是你想要的时，MyBatis 会在内部调用。因此，您需要改为使用Object作为返回类型。@Select("select tags from t where id = #{id}")Object getTagById(Integer id);@Select("select tags from t where name = #{name}")List<Object> getTagByName(String name);并在配置中全局注册您的类型处理程序。IE<typeHandlers>&nbsp; <typeHandler handler="xxx.yyy.ListArrayTypeHandler" /></typeHandlers>或者configuration.getTypeHandlerRegistry()&nbsp; .register(ListArrayTypeHandler.class);为了完整起见，这里是一个示例类型处理程序实现。import java.sql.Array;import java.sql.CallableStatement;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.Arrays;import java.util.List;import org.apache.ibatis.type.BaseTypeHandler;import org.apache.ibatis.type.JdbcType;import org.apache.ibatis.type.MappedJdbcTypes;import org.apache.ibatis.type.MappedTypes;@MappedJdbcTypes({ JdbcType.ARRAY })@MappedTypes({ Object.class })public class ListArrayTypeHandler extends BaseTypeHandler<List<?>> {&nbsp; @Override&nbsp; public void setNonNullParameter(PreparedStatement ps, int i,&nbsp; &nbsp; &nbsp; List<?> parameter, JdbcType jdbcType) throws SQLException {&nbsp; &nbsp; //&nbsp; JDBC type is required&nbsp; &nbsp; Array array = ps.getConnection().createArrayOf("TEXT", parameter.toArray());&nbsp; &nbsp; try {&nbsp; &nbsp; &nbsp; ps.setArray(i, array);&nbsp; &nbsp; } finally {&nbsp; &nbsp; &nbsp; array.free();&nbsp; &nbsp; }&nbsp; }&nbsp; @Override&nbsp; public List<?> getNullableResult(ResultSet rs, String columnName) throws SQLException {&nbsp; &nbsp; return extractArray(rs.getArray(columnName));&nbsp; }&nbsp; @Override&nbsp; public List<?> getNullableResult(ResultSet rs, int columnIndex) throws SQLException {&nbsp; &nbsp; return extractArray(rs.getArray(columnIndex));&nbsp; }&nbsp; @Override&nbsp; public List<?> getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {&nbsp; &nbsp; return extractArray(cs.getArray(columnIndex));&nbsp; }&nbsp; protected List<?> extractArray(Array array) throws SQLException {&nbsp; &nbsp; if (array == null) {&nbsp; &nbsp; &nbsp; return null;&nbsp; &nbsp; }&nbsp; &nbsp; Object javaArray = array.getArray();&nbsp; &nbsp; array.free();&nbsp; &nbsp; return new ArrayList<>(Arrays.asList((Object[])javaArray));&nbsp; }}仅供参考，要将 a 存储List到tags列中，您可能必须明确指定类型处理程序。insert into t (...) values (#{id}, #{name},&nbsp; #{tags,typeHandler=xxx.yyy.ListArrayTypeHandler})

Answer 2

你不是第一个遇到这个问题的人。项目common-mybatis有一个专门针对这个用例的类型处理程序：StringArrayTypeHandler只需将其添加到 MyBatis 配置中：&nbsp; &nbsp; <typeHandlers>&nbsp; &nbsp; &nbsp; &nbsp; <typeHandler handler="org.gbif.mybatis.type.StringArrayTypeHandler"/>&nbsp; &nbsp; </typeHandlers>...然后对于映射，它很简单：&nbsp; &nbsp; <select id="getTagsById" resultType="java.util.List">&nbsp; &nbsp; &nbsp; &nbsp; SELECT tags FROM t WHERE id = #{id}&nbsp; &nbsp; </select>&nbsp; &nbsp; <select id="getTagsByName" resultType="java.util.List">&nbsp; &nbsp; &nbsp; &nbsp; SELECT tags FROM t WHERE name = #{name}&nbsp; &nbsp; </select>...在代码中：try (SqlSession session = sessionFactory.openSession()) {&nbsp; &nbsp; List<String> tags = session.selectOne("[...].getTagsById", 1);&nbsp; &nbsp; System.out.println("Tags: " + tags);&nbsp; &nbsp; List<List<String>> multiTags = session.selectList("[...].getTagsByName", "test");&nbsp; &nbsp; System.out.println("Tags: " + multiTags);}使用 JDBC 驱动程序版本 42.2.5 和以下测试数据针对 PostgreSQL 11 进行测试：select * from t;&nbsp;id |&nbsp; name&nbsp; &nbsp;|&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; tags&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;----+---------+------------------------------------&nbsp; 1 | test&nbsp; &nbsp; | {Thriller,Drama}&nbsp; 2 | my name | {Science-Fiction,Adventure,Horror}&nbsp; 3 | test&nbsp; &nbsp; | {Comedy,Adventure}(3 rows)...产生：Tags: [Thriller, Drama]Tags: [[Thriller, Drama], [Comedy, Adventure]]