CODEWITHSUNDEEP
javasqlpostgresqlhstorejdbi
Swav
Swav

Reputation: 881

How to bind an hstore[] value in Java

I'm trying to store people's telephone numbers and addresses in a database table. I would like to support multiple phone numbers and addresses and expect the format to be different in different countries. I've decided to use hstore to allow that flexibility and allow efficient querying by specific fields. As it stands I can receive the values from database, but could not find a way to insert them from Java. The table (simplified) looks like that:

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 '{}')"

I have created a custom JDBI Binder to bind the values, but however I try I can't get the statement to execute. Currently Binder code snippet looks like this:

@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())));

The getHstoreArray function is a helper that converts java Array into SQL array and looks like this:

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

I think the problem is in encoding of the data. For example, for data (in JSON notation for simplicity)

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

the query is expanded to:

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}"}'
)

and if I try to run it from PGAdmin's SQL editor the error returned is:

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

I have considered using JSON instead of hstore[], but that would make querying by specific fields slower and less accurate (essentially a text search) and I'd rather avoid it. Another option I tried before hstore is array of UDT, but couldn't even get it to read from database without writing parser for PGobject which doesn't look like a simple task.

EDIT

I had a look at the data in the database and when escaped in the following way: 

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

I can run query manually from SQL editor, but still no luck in Java.

Upvotes: 2

Views: 1564

Answers (1)

Swav
Swav

Reputation: 881

I have found a solution, there is a class available in Postgres driver called HStoreConverter which can convert Map to String literal. Not sure this is the best approach, but it seems to work, modified helper function below.

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

Upvotes: 1

Related Questions