Using a PreparedStatement to persist an array of Java Enums to an array of Postgres Enums

Question

I have a Java Enum:

public enum Equipment { Hood, Blinkers, ToungTie, CheekPieces, Visor, EyeShield, None;}

and a corresponding Postgres enum:

CREATE TYPE equipment AS ENUM ('Hood', 'Blinkers', 'ToungTie', 'CheekPieces', 'Visor', 'EyeShield', 'None');

Within my database I have a table which has a column containing an array of "equipment" items:

CREATE TABLE "Entry" (
    id bigint NOT NULL DEFAULT nextval('seq'::regclass),
    "date" character(10) NOT NULL,
    equipment equipment[]
);

And finally when I am running my application I have an array of the "Equipment" enums which I want to persist to the database using a Prepared Statement, and for the life of me I can't figure out how to do it.

StringBuffer sb =  new StringBuffer("insert into \"Entry\" ");
sb.append("( \"date\", \"equipment \" )");
sb.append(" values ( ?, ? )");
PreparedStatement ps = db.prepareStatement(sb.toString());

ps.setString("2010-10-10");
ps.set???????????

Answer 1

You should read this.

I'd suggest that your code look more like this:

    // A column named "date" is probably illegal and not very illustrative.  "date" is a keyword for most databases.
    // Building a string that never changes again and again is a waste of CPU and heap
    private static final String INSERT_SQL =  "insert into Entry(date,equipment) values(?,?)";

    PreparedStatement ps = db.prepareStatement(INSERT_SQL);

    // Use the type system properly.  Dates should be DATE type columns in a database.  Why string?    
    ps.setDate(entryDate);

    // You shouldn't want to insert an array of values; that's not normalized.
    ps.setString(equipmentEnumValue.name());

Answer 2

I ran into this exact problem, and could not find a good solution.

The solution I ended up settling on was to insert as an array of String:

conn.createArrayOf("varchar", elements.toArray());

and to have an assignment cast in the DB:

CREATE OR REPLACE FUNCTION cast_meal_array(src_str character varying[]) RETURNS meal_type[] AS $$
BEGIN
RETURN src_str::text[]::meal_type[];
END;
$$ LANGUAGE plpgsql;

DROP CAST IF EXISTS (character varying[] as meal_type[]);
CREATE CAST (character varying[] AS meal_type[]) WITH FUNCTION cast_meal_array(character varying[]) AS assignment;

I was not happy with this solution, but it does work and does not require any particular JDBC wizardry.

Answer 3

You are trying to do two non standards things in plain JDBC: enums and arrays. None of them is very straightforward - though both can be done. But I advise against both: I prefer to use ad-hoc-enums (just integers in some parametric table), and avoid arrays inside the database, except in very special cases. If you insist, attack one problem at a time.

BTW, you have two other issues: identifiers (tables and column names) with mixed cases (that must be quoted to avoid postgresql folding to lower case), and a column with a reserved sql word (date) as name. This is not bad, but it certainly does not make your developer life easier... And more BTW: beware of that space after \"equipment \"