java - 如何在 Java 中绑定(bind) hstore[] 值

Question

我正在尝试将人们的电话号码和地址存储在数据库表中。我想支持多个电话号码和地址，并希望格式在不同国家/地区有所不同。我决定使用 hstore 来实现这种灵 active 并允许通过特定字段进行高效查询。就目前而言，我可以从数据库接收值，但找不到从 Java 插入它们的方法。表格(简化版)如下所示:

CREATE TABLE IF NOT EXISTS contacts ( "
        + "id uuid NOT NULL, "
        + "title character varying NOT NULL DEFAULT '', "
        + "first_name character varying NOT NULL, "
        + "last_name character varying NOT NULL, "
        + "phones hstore[] NOT NULL DEFAULT '{}', "
        + "addresses hstore[]  NOT NULL DEFAULT '{}')"

我已经创建了一个自定义的 JDBI Binder 来绑定(bind)这些值，但是无论如何我都无法执行该语句。当前 Binder 代码片段如下所示:

@Override
public void bind(SQLStatement<?> q, BindContactBean bind, ContactBean bean) {
        q.bind("phones",
                getHstoreArray(q, PhoneDetailMapper.toMapArray(bean.phones.get())));
        q.bind("addresses",
                getHstoreArray(q, AddressDetailMapper.toMapArray(bean.addresses.get())));

getHstoreArray 函数是一个将 java 数组转换为 SQL 数组的辅助函数，如下所示:

private Array getHstoreArray(SQLStatement<?> q, Map<String, String>[] map) {
    try {
        return q.getContext().getConnection().createArrayOf("hstore", map);
    } catch (SQLException e) {
        throw new IllegalArgumentException(e);
    }
}

我认为问题在于数据的编码。例如，对于数据(为简单起见，使用 JSON 表示法)

{
    "firstName": "Maximum",
    "lastName": "Details",
    "status": "active",        
    "phones": [{
        "type": "mobile",
        "number": "0777 66 55 44"
    }]
}

查询扩展为:

INSERT INTO contacts ( 
id, first_name, last_name, status, phones ) 
VALUES ( '9be1a040-b408-11e3-bb43-00231832fa86',  'Maximum', 'Details', 4, 
'{"{type=mobile, extracted_number=extracted, number=07777 66 55 44}"}'
)

如果我尝试从 PGAdmin 的 SQL 编辑器运行它，返回的错误是:

ERROR:  Syntax error near 'm' at position 6
LINE 5: '{"{type=mobile, extracted_number=extracted, number=07777 66...
        ^
********** Error **********

ERROR: Syntax error near 'm' at position 6
SQL state: XX000
Character: 179

我考虑过使用 JSON 而不是 hstore[]，但这会使特定字段的查询速度变慢且准确性降低(本质上是文本搜索)，我宁愿避免使用它。我在 hstore 之前尝试的另一个选项是 UDT 数组，但如果不为 PGobject 编写解析器就无法从数据库中读取它，这看起来不是一个简单的任务。

---

编辑

我查看了数据库中的数据，转义时是这样的:

'{"\"type\"=>\"mobile\", \"number\"=>\"07777 66 55 44\", \"extracted_number\"=>\"777665544\""}'

我可以从 SQL 编辑器手动运行查询，但在 Java 中仍然不行。

Answer 1

我找到了一个解决方案，在 Postgres 驱动程序中有一个名为 HStoreConverter 的类可以将 Map 转换为 String 文字。不确定这是最好的方法，但它似乎有效，修改了下面的辅助函数。

        private Array getHstoreArray(SQLStatement<?> q, Map<String, String>[] maps) {
            try {
                String[] hstores = new String[maps.length];
                for (int i = 0; i < maps.length; i++)
                    hstores[i] = HStoreConverter.toString(maps[i]);
                return q.getContext().getConnection().createArrayOf("hstore", hstores);
            } catch (SQLException e) {
                throw new IllegalArgumentException(e);
            }
        }