pgsql笔记

最近项目中用到pgsql,遇到些麻烦事,记录一下。

pgsql array 类型

不知道大家对于pgsql的Array类型是否用的频繁,我在用Mybatis做持久层的时候,ARRAY类型对应的是java.sql.Array

这时候问题就来了,在写业务代码的时候发现java类型不能直接转换成Array的实现类,这就头痛了。

翻看了JDK的文档,Connection接口中有:

Modifier and Type Method Description
Array createArrayOf(String typeName, Object[] elements) Factory method for creating Array objects.

于是乎,我们就可以利用Mybatis的TypeHandler来解决这个问题了。具体代码如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
/**
* 自定义Mybatis ARRAY类型处理器
*
* @author 7le
*/
@MappedTypes(Object.class)
@MappedJdbcTypes(JdbcType.ARRAY)
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";
private static final String TYPE_NAME_BIGINT = "bigint";

@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;
} else if (parameter instanceof Long[]) {
typeName = TYPE_NAME_BIGINT;
} else {
typeName = TYPE_NAME_VARCHAR;
}

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

然后在application.yml中加上

1
2
mybatis:
type-handlers-package: xxx.xxx.handler #自定义handler的路径

或者

1
2
3
4
5
6
7
8
@Bean(name = "sessionFactory")
public SqlSessionFactory sessionFactory(@Qualifier("dataSource") DataSource dataSource) throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource);
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(PgsqlSourceConfig.MAPPER_LOCATION));
bean.getObject().getConfiguration().getTypeHandlerRegistry().register(ArrayTypeHandler.class);
return bean.getObject();
}

如果不是springboot的话,用spring的配置方式就可以了。

Github 不要吝啬你的star ^.^
更多精彩 戳我

Fork me on GitHub