MybatisPlus拼接SQL,PostgreSQL 数组查询
PostgreSQL 查询的时候,对数组元素进行匹配,这块调了一下午,有点坑,MybatisPlus对于拼接SQL默认前后加了双引号,我怀疑是这样的,凭经验来看,我没有进一步追查源码(懒得追),这样写比较好。
List<String> fieldSql = Lists.newArrayList();
List<String> valueSql = Lists.newArrayList();
for (int i = 0; i < planQueryRequest.getFeatureType().size(); i++) {
fieldSql.add("{" + i + "}");
valueSql.add(planQueryRequest.getFeatureType().get(i).toString());
}
queryWrapper.apply("feature_type_array @> ARRAY[" +
//列表转string,用英文逗号","拼接
StringSplitUtils.join(fieldSql, ",") +
"]", valueSql.stream().toArray(String[]::new));
写入代码
Entity字段标明Array
@TableField(value = "feature_type_array",typeHandler = ListStrTypeHandler.class,jdbcType = JdbcType.ARRAY)
private List<String> featureTypeArray;
TypeHandler
public class ListStrTypeHandler extends BaseTypeHandler<List<String>> {
private static final Logger LOGGER = LoggerFactory.getLogger(ListStrTypeHandler.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, List<String> 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());
// }
if (parameter != null && !parameter.isEmpty()) {
// 这2行是关键的代码,创建array,然后ps.setarray(i, array)就可以了
Array array = ps.getConnection().createArrayOf(TYPE_NAME_VARCHAR, parameter.stream().toArray(String[]::new));
ps.setArray(i, array);
}
// 这2行是关键的代码,创建array,然后ps.setarray(i, array)就可以了
// Array array = ps.getConnection().createArrayOf(TYPE_NAME_VARCHAR, parameter);
// ps.setArray(i, array);
}
@Override
public List<String> getNullableResult(ResultSet rs, String columnName) throws SQLException {
return converList(rs.getArray(columnName));
}
@Override
public List<String> getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
return converList(rs.getArray(columnIndex));
}
@Override
public List<String> getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
return converList(cs.getArray(columnIndex));
}
private List<String> converList(Array array){
if (array == null) {
return null;
}
try {
if (array.getArray() == null) {
return null;
}
Object[] array1 = (Object[]) array.getArray();
List<Object> objects = Arrays.asList(array1);
return objects.stream().filter(Objects::nonNull).map(Objects::toString).collect(Collectors.toList());
} catch (SQLException e) {
LOGGER.error("ArrayTypeHandler getArray SQLException",e);
}
return null;
}
}
这种写法,查询返回的时候,可以直接用List接收返回。