Currently I'm trying to migrate the app to spring boot 3 and hibernate 6. I faced the issue that when I insert the data where the column has to be in json format I get an error
could not execute statement [INVALID_ARGUMENT: com.google.api.gax.rpc.InvalidArgumentException: io.grpc.StatusRuntimeException: INVALID_ARGUMENT: Value has type STRING which cannot be inserted into column Filter, which has type JSON [at 1:292]\n...ColumnN,ColumnN+1,ColumnN+2) values (@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,...\n ^] [insert into Table1 (Column1,Column2,Column3,Column4,Column5, ...) values (?,?,?,?,?,...)]
I added annotation @JdbcTypeCode(SqlTypes.JSON) to my column and defined the org.hibernate.dialect.SpannerDialect, but it doesn't work.
@Column(name = "Filter")
@JdbcTypeCode(SqlTypes.JSON)
Filter filter;
As far as I see spanner functions have to be applied during the insert the data, but it is not by any reason.
Screenshot of adding spanner functions in SpannerDialect
Maybe someone has the same issue or know how to fix it?
I tried to debug the hibernate to understand why the function doesn't wrap the string to insert the column in json format, but I couldn't understand that. I'm expecting to store java object in json format.
The problem is caused by the default JSON type definition in Hibernate, which does not include the JSON type code when data is inserted/updated in the database. Instead, it tries to send the value as a string. That happens here: https://github.com/hibernate/hibernate-orm/blob/a84ba5c8c98812c03fcd34a8611b3c9da4c9afce/hibernate-core/src/main/java/org/hibernate/type/descriptor/jdbc/JsonJdbcType.java#L123
The Spanner dialect should override this behavior so the correct type code is included when it is sent to Spanner. That was missed in the latest Hibernate 6.x dialect, but will be fixed here: https://github.com/GoogleCloudPlatform/google-cloud-spanner-hibernate/pull/944