PostgreSQL Types in Vaadin

Hi to everybody :slight_smile:

I’ve got a problem with a table in my DB, it seems that I cannot modify a table entry because it uses a type “Enum” for a column, and whenever I try to change the data, Vaadin gives me this error:

“ERROR: the column “tipo” is a cod_type, but the expression is a character varying type.
Hint: you must rewrite or convert the expression type”

How do I implement Enum types in Vaadin?

Thanks :slight_smile:

Hi,
your problem has nothing to do with Vaadin. When using custom types in PostgreSql (cod_type) you need to declare matching JDBC type implementation. Here is my example for UUID type:

public class UUID extends PGobject {
private static final long serialVersionUID = 5088092485075303348L;
public UUID(String s) throws SQLException {
super();
this.setType("uuid");
this.setValue(s);
}
}

You can also add typecast in your SQL statement:

select * from table where cod_type::varchar = 'test'

If you using Hibernate, yo can implement UserType interface for your custom type.

I hope this will help you.

Hi,

I know it’s been two years, but… Could you please provide a full example? I’m facin this problem, too, and although I’m trying to use your recommendations, I think I’m missing some piece of the puzzle.

I created a com.mypackage.MyCustomType.java class mimetizing your example. Then, following postgres docs (
https://www.postgresql.org/docs/7.4/static/jdbc-ext.html
- I haven’t been able to find the reference for the current, 9.4 version, but it’s still in the current API - wtf??), after creating a connection pool with
new SimpleJDBCConnectionPool()
and getting one with
reserveConnection()
, I try to add my custom datatype to it:

((org.postgresql.PGConnection)conn).addDataType("mycustomtype", (Class<? extends PGobject>) Class.forName("com.mypackage.mycustomtype")); but this seems to do nothing at all and I still get the same error.

Typecasting in my SQL statement doesn’t seem an option because currently I’m using the SQL helpers vaadin provides for constructing my own prepared statements (and assigning the proper values to them) without actually having to type the statements by hand, and it doesn’t seem to have a way to pass a type to cast to when doing that.

Any help (maybe a different approach?) will be greatly appreciated…

Ok, I couldn’t typecast in my SQL statements as I was using
StatementHelper
and
SQLGenerator
to build them. As this was not trivial in any way and it took me several hours to solve by myself, I thought I should share my solution just in case anyone faces the same problem.

After adding your data type to your connection and generating your prepared statement and setting your values to them (I do all of this in my StatementDelegate, in
storeRow()
method), you have to “override” the set value for your customized type which probably has the wrong, standard one. I do this with a

preparedStatement.setObject(index, yourCustomTypeObject);

being index an integer belonging to the column index you want to set its value (starting from ONE, not zero!) and yourCustomTypeObject an instance of your custom, PGobject extending class you previously should have been created using the recipe Marko posted two years ago.