1

I have a table with a column of type json in my postgreSQL DB (9.2). I'm using Hibernate and implemented the code example from Mapping postgreSQL JSON column to Hibernate value type suggested by Tim Fulmer, But I keep getting the following exception :

ERROR: column "jsonobject" is of type json but expression is of type character varying
Hint: You will need to rewrite or cast the expression.

I don't want to cast the expression because it is not recommended. I getting this error when I'm using a table that was pre-generated and the column is of type json. When I'm changing Hibernate configuration to build the table for me, I don't get the exception and the value are inserted to the newly created table but the type of this column is 'character varying(255)' instead of JSON.

I'm bet that I missed something in my implementation but can't understand what. Where should I look ? I even tried to set breakpoints in my code but I don't reach them.

Thanks.

Community
  • 1
  • 1
sagi
  • 523
  • 3
  • 15
  • You'll need to use a custom Hibernate type, [create a cast in PostgreSQL to allow implicit conversion](http://stackoverflow.com/a/15983429/398670), or cast at the SQL level. – Craig Ringer Jan 07 '14 at 04:45
  • Show your mappings. At a guess, you've added the type support code, but have not actually used it in your Hibernate mappings. – Craig Ringer Jan 07 '14 at 04:47
  • The column mapping : ** ** I tried to change the mapping but got exceptions that the type that I used is not known. – sagi Jan 07 '14 at 05:09
  • I think you'll need to specify `typeClass=` or similar. I don't work with Hibernate XML mappings, but you should be able to translate the annotations in the example given on the other answer. – Craig Ringer Jan 07 '14 at 05:47
  • I've added **@TypeDefs( {@TypeDef( name= "StringJsonObject", typeClass = StringJsonUserType.class)})** at the top of my class (as mentioned in the other answer), but it does not help. I don't think that the usertype class is even being called. You suggest that I should put the typeClass in my xml mapping file ? – sagi Jan 07 '14 at 05:52
  • As I said, I don't use Hibernate XML mappings. I suspect that maybe if you use XML mappings it doesn't see the annotation? The other possibility is that you did not specify the custom dialect. – Craig Ringer Jan 07 '14 at 05:54
  • I will try to use in Class mapping. Where should I specify the custom dialect ? – sagi Jan 07 '14 at 06:05
  • http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/session-configuration.html#configuration-optional-dialects – Craig Ringer Jan 07 '14 at 06:08

1 Answers1

1

Found the solution, the following definition was really missing from my project. In the xml mapping file for the table I had to use type="com.example.StringJsonUserType" for that specific field instead of type="java.lang.String" so the mapping for this column is :

<property name="someobjects" type="com.example.StringJsonUserType">
    <column name="jsonobject" />
</property>

Sagi.

sagi
  • 523
  • 3
  • 15