postgres jdbc insert array of custom type

Question

I want to insert array of custom type into postgres with jdbc.

my sql schema:

CREATE TYPE element_pk_t AS (
    workspace_id   BIGINT,
    element_id     BIGINT,
    history_id     BIGINT
);

my java class:

public class ElementPK {
    public Long workspaceId;

    public Long elementId;

    public Long historyId;
}

How should I do this in java with jdbc?

I've found tutorial regarding custom type https://docs.oracle.com/javase/tutorial/jdbc/basics/sqlcustommapping.html, but array of custom type is still unclear to me.

One trivial approach is using pure string style SQL statement, but I still prefer using PreparedStatement with setObject or setArray

Loop through the array and insert each element.

Laurenz Albe
– Laurenz Albe

2021-09-16 06:14:59 +00:00
Commented Sep 16, 2021 at 6:14 — Laurenz Albe
– Laurenz Albe, Commented Sep 16, 2021 at 6:14

Yaros_npi · Accepted Answer · 2022-03-28 12:58:58Z

2

Follow up to the last part of @misha2045 answer.

If you still refuese to use JSONB for whatever reason you need to add parenthesis at the end and start of your toString method.

@Override
public String toString() {
    return "("+this.field1+ ", " + this.field2+")";
}

Then set the array in the prepared statement

ps.setArray(1, con.createArrayOf("yourType", yourClassArray));

edited Mar 28, 2022 at 12:58

answered Mar 28, 2022 at 9:53

Yaros_npi

1109 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mipo256 · Accepted Answer · 2021-09-16 19:00:20Z

If I understood you correctly, then, from the Java code, you need to generate query that looks, for example, like this:

INSERT INTO parent_table(elements) 
VALUES (
ARRAY[
     row(1, 2, 3)::element_pk, 
     row(4, 5, 6)::element_pk, 
     row(7, 8, 9)::element_pk
]);

Assuming that you have a table like the following:

CREATE TABLE parent_table(
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    elements element_pk[]
)

And the Type like you described. If I were you, I wont implement it via an array. I would have use JSONB instead - in this way you wont lose an ability to use indexing. Of course, there is java.sql.Array out there, and you can still do it via the following:

Array array = connection.createArrayOf("public.element_pk", new ElementPK[]{
                new ElementPK(1, 2, 3),
                new ElementPK(9, 4, 6)
        });

And then set the array, to PgPreparedStatement, but the thing is that internally PgPreparedStatement will enclose your elements within curly braces, which is ok, but each element will be represented by its toString method call result. I mean, assume your ElementPK toString looks like this:

    @Override
    public String toString() {
        return "This is how it is implemented, really?";
    }

then you will get the SQL like:

INSERT INTO parent_table (elements) VALUES ('{"This is how it is implemented, really?","This is how it is implemented, really?"}')

Again, it is maybe possible to adopt it, but from my perspective - at least having your logic within toString method is not that great, is it? Spare yourself and do the following:

CREATE TABLE via_jsonb(
    elements JSONB
);

and then simply:

INSERT INTO via_jsonb VALUES(
'{
    "workspace_id" : 1,
    "element_id" : 2,
    "history_id" : 3
 }'
);

and in the Java code I would have simply create json from your object and set it as a string. Really, there are a lot of functions and cool features on JSONB out of the box.

Hope it helped, have a nice day!)

Thanks! really helpful answer. Yeah the toString() approach is just too adhoc, totally agree, I'll consider using jsonb

Collectives™ on Stack Overflow

postgres jdbc insert array of custom type

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related