Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

ERROR: column is of type json but expression is of type character varying in Hibernate

I need to map two columns of entity class as json in postgres using spring data jpa. After reading multiple stackoverflow posts and baeldung post ,

How to map a map JSON column to Java Object with JPA

https://www.baeldung.com/hibernate-persist-json-object

I did configuration as below. However, I am facing error "ERROR: column "headers" is of type json but expression is of type character varying"

Please provide some pointer to resolve this issue.

I have an entity class as below

@Entity
@Data
@SuperBuilder
@NoArgsConstructor
@AllArgsConstructor
public class Task {
    @Id
    @GeneratedValue(strategy = IDENTITY)
    private Integer id;
    
    private String url;
    private String httpMethod;

    @Convert(converter = HashMapConverter.class)
    @Column(columnDefinition = "json")
    private Map<String, String> headers;

    @Convert(converter = HashMapConverter.class)
    @Column(columnDefinition = "json")
    private Map<String, String> urlVariables;
}

I have created a test class to test if entity is persisted or not. On running this junit, below test case is failing with error as below

enter image description here

@SpringBootTest
class TaskRepositoryTest {

    private static Task randomTask = randomTask();

    @Autowired
    private TaskRepository taskRepository;

    @BeforeEach
    void setUp() {
        taskRepository.deleteAll();
        taskRepository.save(randomTask);
    }

    public static Task randomTask() {
        return randomTaskBuilder().build();
    }

    public static TaskBuilder randomTaskBuilder() {
        Map<String,String> headers = new HashMap<>();
        headers.put(randomAlphanumericString(10),randomAlphanumericString(10));

        Map<String,String> urlVariables = new HashMap<>();
        urlVariables.put(randomAlphanumericString(10),randomAlphanumericString(10));

        return builder()
                .id(randomPositiveInteger())
                .httpMethod(randomAlphanumericString(10))
                .headers(headers)
                .urlVariables(urlVariables)
                .url(randomAlphanumericString(10)));
    }
}

Using liquibase, I have created table in postgres DB and I could see column datatype as json.

databaseChangeLog:
  - changeSet:
      id: 1
      author: abc
      changes:
        - createTable:
            tableName: task
            columns:
              - column:
                  name: id
                  type: int
                  autoIncrement: true
                  constraints:
                    primaryKey: true
              - column:
                  name: url
                  type: varchar(250)
                  constraints:
                    nullable: false
                    unique: true
              - column:
                  name: http_method
                  type: varchar(50)
                  constraints:
                    nullable: false
              - column:
                  name: headers
                  type: json
              - column:
                  name: url_variables
                  type: json
      rollback:
        - dropTable:
            tableName: task
like image 723
user2800089 Avatar asked Dec 28 '20 13:12

user2800089


People also ask

Is of type Jsonb but expression is of type?

The cause of the error ERROR: column “metadata” is of type jsonb but expression is of type bytea Hint: You will need to rewrite or cast the expression. if you are executing a native SQL DML statement.

What is Jsonb?

The JSONB data type stores JSON (JavaScript Object Notation) data as a binary representation of the JSONB value, which eliminates whitespace, duplicate keys, and key ordering. JSONB supports GIN indexes.


3 Answers

Above configuration did not work.

Hence, I followed below link to solve the use-case

https://vladmihalcea.com/how-to-map-json-objects-using-generic-hibernate-types/

"Provider com.fasterxml.jackson.module.jaxb.JaxbAnnotationModule not found" after Spring Boot Upgrade

Added additional dependencies in pom.xml

        <dependency>
            <groupId>com.fasterxml.jackson.module</groupId>
            <artifactId>jackson-module-jaxb-annotations</artifactId>
        </dependency>
        <dependency>
            <groupId>com.vladmihalcea</groupId>
            <artifactId>hibernate-types-52</artifactId>
            <version>2.9.11</version>
        </dependency>

Removed HashMapConverter configuration and made below changes in entity class

@Entity
@Data
@SuperBuilder
@NoArgsConstructor
@AllArgsConstructor
@ToString(callSuper = true)
@TypeDefs({
        @TypeDef(name = "json", typeClass = JsonStringType.class),
        @TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
})
public class Task {
    @Id
    @GeneratedValue(strategy = IDENTITY)
    private Integer id;
    
    private String url;
    private String httpMethod;

    @Type(type = "jsonb")
    @Column(columnDefinition = "json")
    private Map<String, String> headers;

    @Type(type = "jsonb")
    @Column(columnDefinition = "json")
    private Map<String, String> urlVariables;
}

After these changes, TaskRepositoryTest passed.

like image 107
user2800089 Avatar answered Oct 13 '22 12:10

user2800089


I ran into this issue when I migrated my projects from MySQL 8.0.21 to Postgres 13. My project uses Spring boot with the Hibernate types dependency version 2.7.1. In my case the solution was simple.

enter image description here

All I needed to do was change that and it worked.

Referenced from the Hibernate Types Documentation page.

like image 27
Joseph Waweru Avatar answered Oct 13 '22 12:10

Joseph Waweru


For anyone who landed here because they're using JdbcTemplate and getting this error, the solution is very simple: In your SQL statement, cast the JSON argument using ::jsonb.

E.g. String INSERT_SQL = "INSERT INTO xxx (id, json_column) VALUES(?, ?)"; becomes String INSERT_SQL = "INSERT INTO xxx (id, json_column) VALUES(?, ?::jsonb)";

like image 4
spork Avatar answered Oct 13 '22 11:10

spork