Howto retrieve a custom type via JDBC
От | Viktor Rosenfeld |
---|---|
Тема | Howto retrieve a custom type via JDBC |
Дата | |
Msg-id | 20091029201156.GA25285@stan обсуждение исходный текст |
Список | pgsql-jdbc |
Hi, I've created a custom datatype that I would like to retrieve via JDBC. To make the problem more fun, the values are aggregated into an array. The type definition is: CREATE TYPE annotation AS ( namespace varchar, name varchar, value varchar ); The query looks something like this: SELECT node_ref AS id, array_agg(DISTINCT ROW(namespace, name, value)::annotation) AS annotation ... GROUP BY id; I've hacked together some experimental code: Array array = rs.getArray(2); if (array != null) { ResultSet arrayRs = array.getResultSet(); while (arrayRs.next()) { PGobject anno = (PGobject) arrayRs.getObject("value"); // anno.getValue() returns the String "(namespace,name,value)" PGtokenizer t = new PGtokenizer(PGtokenizer.remove(anno.getValue(), "(", ")"), ','); if (t.getSize() != 3) throw new RuntimeException("Could not read to annotation type: " + anno.getValue()); String namespace = t.getToken(0); String name = t.getToken(1); String value = t.getToken(2); Annotation annotation = new Annotation(namespace, name, value); } } The code above is not only fugly, but it breaks if there's a comma in one of those values (e.g. (tiger,lemma,"1,62")). In this case, they are 4 tokens. BTW, the quotes around 1,62 are inserted by PostgreSQL, they're not actually there in database. If PostgreSQL is smart enough to insert the quotes, it should be able to correctly parse the string, shouldn't it? I got the idea to use PGtokenizer from looking at PGbox and stuff. But my reason to create a custom type in the first place was to get to its constituents in a clean way. Previously, I had simply concatenated the strings with ":" and "=" in SQL and splitted them back into the components in my Java code. So, the question is: How to cleanly retrieve the components of a custom datatype using JDBC? Cheers, Viktor
В списке pgsql-jdbc по дате отправления: