CODEWITHSUNDEEP
javaarrayspostgresql
Derek Mahar
Derek Mahar

Reputation: 28386

What Java library can map a PostgreSQL array literal to a Java array or list?

What third-party Java class library is available to map a PostgreSQL array literal string to a Java array or list of strings?

For example, suppose I wish to convert a PostgreSQL array literal string such as

{ A, B, C }

into the equivalent Java List<String> or Array<String> that contains { "A", "B", "C" }. Does the the PostgreSQL JDBC driver have a class method to perform this conversion?

Upvotes: 4

Views: 4691

Answers (3)

a.l.
a.l.

Reputation: 1135

For whom needs to parse array value from logical replication(pgoutput): In such situation, it's so good/familiar/high-performance to use the sql way in Craig Ringer's answer, so I wrote a Class to parse it.

Upvotes: 0

Craig Ringer
Craig Ringer

Reputation: 324475

Why would you want to parse array string literals in Java, when you can just get a java.sql.Array from PgJDBC and call getArray() on it to get the native Java array directly?

Parsing array literals in Java seems like an unnecessarily complicated approach.

Since you're getting the values as array literals from hstore, I'd say that you have a bit of a data model design problem since hstore really only stores string values so storing array literals is a wee bit ugly. I can understand that there might be circumstances where it's desirable though, at least until Pg gets improved json support.

In that case I'd let PostgreSQL parse the array literals for you. Given setup:

regress=# CREATE EXTENSION hstore;
CREATE EXTENSION
regress=# CREATE TABLE hstoretest ( test hstore );
CREATE TABLE
regress=# INSERT INTO hstoretest(test) VALUES ( hstore( ARRAY['a', 'b'], ARRAY[ ARRAY[1,2,3]::text, ARRAY[9,8,7]::text ] ) );
INSERT 0 1
regress=# INSERT INTO hstoretest(test) VALUES ( hstore( ARRAY['a', 'b'], ARRAY[ ARRAY[11,12,13]::text, ARRAY[99,88,77]::text ] ) );
INSERT 0 1
regress=# SELECT * FROM hstoretest ;
                 test                 
--------------------------------------
 "a"=>"{1,2,3}", "b"=>"{9,8,7}"
 "a"=>"{11,12,13}", "b"=>"{99,88,77}"
(2 rows)

You could let Pg expand and parse the array literals for you with something like:

regress=# SELECT k, fetchval(test,k)::integer[] FROM (SELECT test, skeys(test) AS k FROM hstoretest) withkeys;
 k |  fetchval  
---+------------
 a | {11,12,13}
 b | {99,88,77}
 a | {1,2,3}
 b | {9,8,7}
(4 rows)

You may need more or less complicated variants depending on whether you're fetching just one hstore field or many fields, whether all your values are arrays of the same type or not, etc. In some cases you'll need to do individual SQL calls to unpack values.

Upvotes: 5

Josue Montano
Josue Montano

Reputation: 521

Yes, it has. Take a look at: http://www.postgresql.org/message-id/[email protected]. Also there is an interesting article here. That article has a comment linking to an interesting project on GitHub (sproc-spring-mapper).

Upvotes: 0

Related Questions