Ormlite and PostgreSQL - Error inserting text array with custom persister

Question

I have been working to setup Ormlite as the primary data access layer between a PostgreSQL database and Java application. Everything has been fairly straightforward, until I started messing with PostgreSQL's array types. In my case, I have two tables that make use of text[] array type. Following the documentation, I created a custom data persister as below:

public class StringArrayPersister extends StringType {

    private static final StringArrayPersister singleTon = new StringArrayPersister();

    private StringArrayPersister() {
        super(SqlType.STRING, new Class<?>[]{String[].class});
    }

    public static StringArrayPersister getSingleton() {
        return singleTon;
    }


    @Override
    public Object javaToSqlArg(FieldType fieldType, Object javaObject) {
        String[] array = (String[]) javaObject;


        if (array == null) {
            return null;
        } else {

            String join = "";
            for (String str : array) {
                join += str +",";
            }

            return "'{" + join.substring(0,join.length() - 1) + "}'";
        }

    }

    @Override
    public Object sqlArgToJava(FieldType fieldType, Object sqlArg, int columnPos) {
        String string = (String) sqlArg;

        if (string == null) {
            return null;
        } else {
            return string.replaceAll("[{}]","").split(",");
        }
    }
}

And then in my business object implementation, I set up the persister class on the column likeso:

   @DatabaseField(columnName = TAGS_FIELD, persisterClass = StringArrayPersister.class)
    private String[] tags;

When ever I try inserting a new record with the Dao.create statement, I get an error message saying tags is of type text[], but got character varying... However, when querying existing records from the database, the business object (and text array) load just fine.

Any ideas?

UPDATE:

PostGresSQL 9.2. The exact error message:

Caused by: org.postgresql.util.PSQLException: ERROR: column "tags" is of type text[] but expression is of type character varying Hint: You will need to rewrite or cast the expression.

khampson khampson · Accepted Answer · 2014-08-15T20:24:23

I've not used ormlite before (I generally use MyBatis), however, I believe the proximal issue is this code:

 private StringArrayPersister() {
        super(SqlType.STRING, new Class<?>[]{String[].class});
    }

SqlType.String is mapped to varchar in SQL in the ormlite code, and so therefore I believe is the proximal cause of the error you're getting. See ormlite SQL Data Types info for more detail on that.

Try changing it to this:

private StringArrayPersister() {
        super(SqlType.OTHER, new Class<?>[]{String[].class});
    }

There may be other tweaks necessary as well to get it fully up and running, but that should get you passed this particular error with the varchar type mismatch.

Ormlite and PostgreSQL - Error inserting text array with custom persister

1 Answers