postgresSQL查询返回 integer[] 在Java中的接收
ContactsVo.java
inchargeId字段需要接收一个id数组,一直接收不到,估计是sql类型和jdbctype没有对应上,所以尝试用typehandler来解决。
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Getter;
import lombok.Setter;
import java.sql.Array;
import java.util.Date;
import java.util.List;
@ApiModel
@Getter
@Setter
public class ContactsVo {
@ApiModelProperty("联系人id")
private Integer id;
@ApiModelProperty("客户id")
private Integer customerId;
@ApiModelProperty("单位名称")
private String enterpriseName;
@ApiModelProperty("公司负责人id")
private Integer[] inchargeId;
@ApiModelProperty("公司负责人姓名")
private String inchargeName;
@ApiModelProperty("性别 1-男 2-女 3-保密")
private Integer sex;
@ApiModelProperty("年龄")
private String age;
}
ArrayTypeHandler
用作SQL中varchar,integer,boolean, numeric 与 Java中的String,Integer,Boolean,Double之间的类型转换,不然Java接收不到各类型的数组。
package com.popsmart.oa.dao.handler;
import org.apache.ibatis.type.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.sql.*;
@MappedTypes(value = Object.class)
@MappedJdbcTypes(value = {JdbcType.ARRAY})
public class ArrayTypeHandler extends BaseTypeHandler<Object[]> {
private static final Logger LOGGER = LoggerFactory.getLogger(ArrayTypeHandler.class);
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());
}
//这2行是关键的代码,创建array,然后ps.setarray(i, array)就可以了
Array array = ps.getConnection().createArrayOf(typename, parameter);
ps.setArray(i, array);
}
@Override
public Object[] getNullableResult(ResultSet rs, String columnName) throws SQLException {
return getArray(rs.getArray(columnName));
}
@Override
public Object[] getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
return getArray(rs.getArray(columnIndex));
}
@Override
public Object[] getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
return getArray(cs.getArray(columnIndex));
}
private Object[] getArray(Array array) {
if (array == null) {
return null;
}
try {
return (Object[]) array.getArray();
} catch (SQLException e) {
LOGGER.error("ArrayTypeHandler getArray SQLException",e);
}
return null;
}
}
ContactsMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.popsmart.oa.dao.ContactsMapper">
<resultMap id="contactsVoList" type="com.popsmart.oa.domain.vo.ContactsVo">
<result column="id" property="id"/>
<result column="customer_id" property="customerId"/>
<result column="enterprise_name" property="enterpriseName"/>
<result column="incharge_id" property="inchargeId" typeHandler="com.popsmart.oa.dao.handler.ArrayTypeHandler"/>
<result column="incharge_name" property="inchargeName"/>
<result column="sex" property="sex"/>
<result column="age" property="age"/>
</resultMap>
<select id="findList" resultMap="contactsVoList">
select c.*, e.name as enterprise_name, c1.incharge_id, c1.incharge_name,SUBSTRING(age(current_date,c.birthday)::varchar,1,2) age
from (select id,
customer_id,
sex
from contacts
where true
<if test="name != null">
and name like '%' || #{name} || '%'
</if>
<if test="phone != null">
and phone != #{phone}
</if>
<if test="customerId != null">
and customer_id = #{customerId}
</if>
) c
left join customer c2 on c.customer_id = c2.id
left join enterprise e on c2.enterprise_id = e.id
left join (select cur.contacts_id, array_agg(u.id) as incharge_id,
array_to_string(ARRAY(SELECT unnest(array_agg(u.name))), ',') as incharge_name
from contacts_user_rel cur
left join "user" u on cur.incharge_id = u.id
group by cur.contacts_id
) c1 on c1.contacts_id = c.id
where true
<if test="inchargeId != null">
and #{inchargeId} = any (incharge_id)
</if>
</select>
</mapper>
这里指定一下typeHandler
就可以了
接收到了: