how to make @Lob annotation compatible for both oracle and postgresql

Question

My code is designed to support both oracle and postgresql, but the @Lob annotation behaved different from oracle to pg, in oracle @Lob with Java String Object type works well, but in pg, I should add annotation @Type(type ="org.hibernate.type.TextType") to make String mapping to pg text format, which makes it not compatible with oracle. How to make it possible to support oracle and pg in just one JPA entity class?

Here is my entity class.

package hello.world.demo3;

import lombok.Getter;
import lombok.Setter;
import org.hibernate.annotations.Type;

import javax.persistence.*;

@Entity
@Table(name = "my_task")
@Getter
@Setter
public class Task {
    
    @Id
    @GeneratedValue
    @Column(name = "i_id", nullable = false)
    private Long id;
    
    @Lob
    @Column(name = "c_lob")
    private String lob;

    @Lob
    @Type(type ="org.hibernate.type.TextType")
    @Column(name = "c_text")
    private String text;

}

Answer 1

I suggest using the columnDefinition in the @Column annotation to specify that the field should be TEXT type in the underlying SQL table.

@Column(name = "c_text", columnDefinition = "TEXT")
private String text;

Note that specifying @Lob will probably not give you the behavior you want in Postgres. In Postgres, using @Lob will instruct the database to create an auxiliary table with the purpose of storing large binary content.