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???????????

Just to answer some of the comments below. The example is only illustrative, not the exact code I'm using. I know date is probably illegal as a column name, didn't really think while I was setting up the example :) I will be normalizing, but wanted to if working wwith an enum array was possible. Doesn't sound like it from below without a hack of some kind — McGin, May 27 '10 at 07:09

duffymo · Accepted Answer · 2010-05-26T23:03:08.380

3

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

edited May 26 '10 at 23:03

answered May 26 '10 at 22:35

duffymo

305,152
44
369
561

Psst, `name()`, not `getName()`. And on retrieval, use `Enum#valueOf(String)` on the outcome of `ResultSet#getString()`. +1 for normalizing. – BalusC May 26 '10 at 22:52
I had already read that article duffymo, works perfectly for single enum entries, but not for arrays. As you suggest I should normalize, but was just interested to know if the arrays were possible. – McGin May 27 '10 at 07:11
This doesn't actually answer the question, which is about how to use arrays. For those of us who have a legitimate use for them, this answer is not useful – Will Vousden Jul 04 '19 at 06:10
Nine years ago it was accepted. If you have a better one, please post it. If not, go find another way to burnish your rep here besides posting comments on decade old answers. – duffymo Jul 04 '19 at 12:41

score 3 · Answer 2 · answered May 26 '10 at 22:41

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.

Note that you could also probably just have the cast on your prepared statement, if you're willing to do it for every statement. — Steven Schlansker, May 26 '10 at 22:41

score 1 · Answer 3 · answered May 26 '10 at 22:42

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 \"

First time using Postgres, so thanks for the info about case. Was wondering why I needed the quotes — McGin, May 27 '10 at 07:05
@McGin : some more info here http://stackoverflow.com/questions/2774406/psycopg2-doesnt-like-table-names-that-start-with-a-lower-case-letter/2776308#2776308 — leonbloy, May 27 '10 at 11:36

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

3 Answers3