How to read/write a boolean array in a Postgres database?

2.9k views Asked by At

I have a postgres DB containing a table with a boolean array for the days of the week (t/f whether the employee has to work on this day or not).

The table is as follows:

CREATE TABLE employee(
        ...,
    weekdays BOOLEAN[7],
        ...
);

In my Java servlet I would like to read and write the array using a boolean[] (primitive type). I have found out that it should work with the java.sql.Array Object.

Inserting does not work with the following:

Connection dbcon = DataSource.checkOut();

preparedStatement pstatement = dbcon.prepareStatement("INSERT INTO employee (weekdays) VALUES (?)");
pstatement.setArray(1, dbcon.createArrayOf("BOOLEAN", castToObjectType( objectToInsert.getWorkingdays() ).toArray() ) );

...

private ArrayList<Boolean> castToObjectType(boolean[] valueArray){
    ArrayList<Boolean> objArray = new ArrayList<Boolean>();

    for (int i = 0; i < valueArray.length; i++) {
        objArray.add( new Boolean(valueArray[i]) );
    }

    return objArray;
}

I'm getting an org.postgresql.util.PSQLException: Unable to find server array type for provided name BOOLEAN exception.

I'm also somehow stuck the way round doing the conversion from java.sql.Array to boolean[]. Ideas, Best practices?

0

There are 0 answers