CODEWITHSUNDEEP
javapostgresqljdbc
Artem Malinko
Artem Malinko

Reputation: 1771

How to convert array of composite type to array of java objects with jdbc and Postgres?

I have an array of composite type. Let's say it looks like this:

CREATE TYPE hobbie AS (
  name VARCHAR,
  description VARCHAR
);

CREATE TABLE person (
    id SERIAL,
    hobbie hobbie[]
);

Now I need to retrieve hobbies of person from postgres and convert them into java object which looks like this:

public class Hobbie {
    String name;
    String description;
}

The only thing which I was able to do with jdbc is to convert array get to result set with hobbies as String like this ("basketball","the best game"). How can I convert it to java object using jdbc?

UPDATE:

Here is my code to work with postgres array using jdbc:

ResultSet hobbies = rs.getArray("hobbies").getResultSet
while (hobbies.next()){
    hobbies.getString(1) // returns some index or I don't know
    hobbies.getObject(2) // returns PgValue with value == String ("basketball","the best game")
    hobbies.getObject(2)  // obviously returns String with the same value
    hobbies.getArray(2)   // returns something strange
    hobbies.getObject(2, Hobbie.class) // throws unsupported conversion to class com.test.Hobbie. Maybe there is a way to register custom converter, but I didn't find any
 }

Upvotes: 1

Views: 1980

Answers (1)

Sheinbergon
Sheinbergon

Reputation: 3063

Query the table like this

SELECT (r).* FROM (SELECT unnest(hobbie) AS r 
from person) AS hobbies;

It'll output the hobbies as if they were a table. JDBC translation should rather straight-forward from there.

Upvotes: 2

Related Questions