I am working on analysis of spanner for compatibility for our existing application migration. I am facing an error when trying to insert json data into a column in spanner using JPA and hibernate 6. I already referred Sample Application for spanner JPA and multiple posts like cloud spanner integration etc. But couldnt move forward with those suggestions. Need suggestions to fix the same. Below is the details of error and apps
Error: Value has type STRING which cannot be inserted into column json_data, which has type JSON
Entity Class:
import com.google.cloud.spanner.hibernate.PooledBitReversedSequenceStyleGenerator;
import jakarta.persistence.*;
import lombok.AllArgsConstructor;
import lombok.NoArgsConstructor;
import org.hibernate.annotations.GenericGenerator;
import org.hibernate.annotations.JdbcTypeCode;
import org.hibernate.id.enhanced.SequenceStyleGenerator;
import org.hibernate.type.SqlTypes;
import java.sql.SQLType;
import java.util.Map;
@AllArgsConstructor
@NoArgsConstructor
@Entity
@Table(name = "demo")
public class Demo {
@Id
@Column(name = "id")
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "sequence_generator")
@GenericGenerator(
name = "sequence_generator",
strategy = "com.google.cloud.spanner.hibernate.PooledBitReversedSequenceStyleGenerator",
parameters = {
@org.hibernate.annotations.Parameter(name = SequenceStyleGenerator.SEQUENCE_PARAM, value = "demo_sequence"),
@org.hibernate.annotations.Parameter(name = SequenceStyleGenerator.INCREMENT_PARAM, value = "200"),
@org.hibernate.annotations.Parameter(name = SequenceStyleGenerator.INITIAL_PARAM, value = "50000"),
@org.hibernate.annotations.Parameter(name = PooledBitReversedSequenceStyleGenerator.EXCLUDE_RANGE_PARAM, value = "[1,1000]"),
})
private Long id;
@JdbcTypeCode(SqlTypes.JSON)
private Map<String, Object> jsonData;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public Map<String, Object> getJsonData() {
return jsonData;
}
public void setJsonData(Map<String, Object> jsonData) {
this.jsonData = jsonData;
}
}
DDL and DML:
drop table IF EXISTS demo;
drop sequence IF EXISTS demo_sequence;
CREATE SEQUENCE demo_sequence OPTIONS (
sequence_kind="bit_reversed_positive"
);
create table demo(
id INT64 NOT NULL default (GET_NEXT_SEQUENCE_VALUE(SEQUENCE demo_sequence)),
json_data JSON,
)priMARY key(id);
insert into demo(json_data) values (JSON '{"name":"tom","age":30}');
POM.xml
<properties>
<java.version>17</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.google.cloud</groupId>
<artifactId>google-cloud-spanner-hibernate-dialect</artifactId>
<version>3.3.0</version>
</dependency>
<dependency>
<groupId>com.google.cloud</groupId>
<artifactId>google-cloud-spanner</artifactId>
<version>6.80.1</version>
</dependency>
<dependency>
<groupId>com.google.cloud</groupId>
<artifactId>spring-cloud-gcp-data-spanner</artifactId>
<version>5.8.0</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-rest</artifactId>
<version>3.3.5</version>
</dependency>
<dependency>
<groupId>com.google.cloud</groupId>
<artifactId>google-cloud-spanner-jdbc</artifactId>
<version>2.24.1</version>
</dependency>
<dependency>
<groupId>com.vladmihalcea</groupId>
<artifactId>hibernate-types-60</artifactId>
<version>2.21.1</version>
</dependency>
</dependencies>
Upgrading the hibernate dialect dependency from 3.3.0 to 3.7.1 fixed my issue with json parsing. This failure happened because of hibernate considering json as string.
More on the issue can be found here