Search code examples
jsonpostgresqljpaspring-booteclipselink

Alternate solution to save as JSON datatype in postgres spring-boot + eclipselink

I am using eclipselink 2.6 with spring-boot JPA for persistance in postgres.

I am persisting a List of objects as a JSON column in database.Acording to this solution: eclipselink + @convert(json) + postgres + list property I am able to save the data in postgres.

When the column is null, I get this exception: 

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

I can solve this issue by this answer:

Writing to JSON column of Postgres database using Spring / JPA

Q1: Is there an alternate solution other than setting this property stringtype=unspecified int url spring.datasource.url=jdbc:postgresql://localhost:5432/dbnam‌​e?stringtype=unspeci‌​fied

Q2: If not, How can I set stringtype=unspecified in application.prooerties of spring-boot rather than embedding it in the spring.datasource.url

Solution

  • If you are using spring-boot 1.4.1 or above,
    add a data.sql file in resources folder,
    -- IN H2 database, create a column as 'OTHER' data type, -- if H2 fails to create a column as 'JSON' data type. CREATE DOMAIN IF NOT EXISTS JSON AS OTHER;


    This .sql file will be executed during startup of your application and will create a column with data type others in table for the 'json' data type columns in H2 database.