CODEWITHSUNDEEP
javapostgresqljdbcorm
Ziqi Liu
Ziqi Liu

Reputation: 3171

postgres jdbc insert array of custom type

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

Upvotes: 2

Views: 2512

Answers (2)

Yaros_npi
Yaros_npi

Reputation: 110

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

Upvotes: 2

mipo256
mipo256

Reputation: 3140

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!)

Upvotes: 1

Related Questions