3

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;

}
justin
  • 37
  • 4

2 Answers2

3

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.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Can the text field be mapping to clob in oracle when using columnDefinition = "TEXT"? – justin Jun 10 '21 at 06:05
  • @justin: you can remove the `columnDefinition = "TEXT"` - just make sure to use a "compatible" data type in the target database (which means you can't use your obfuscation layer to create the table - but schema migrations are better done using Liquibase anyway) –  Jun 10 '21 at 06:06
  • @a_horse_with_no_name My answer above was the only way I could coax JPA to create a formal TEXT column in Postgres. Your mileage and experience may of course differ. – Tim Biegeleisen Jun 10 '21 at 06:07
  • @TimBiegeleisen: as I said: don't let the obfuscation layer create the tables if you need a solution that needs to work with different databases. Liquibase is much better at that –  Jun 10 '21 at 06:08
  • 1
    thanks, that is to say I would not using the JPA anto-generate, just create table by writing compatible ddl – justin Jun 10 '21 at 06:47
  • @justin I can't say that I've tried doing that, but yes, you might be able to create the table directly using a Postgres script, and then just use something like `varchar` from JPA (but don't let JPA recreate the tables...that would erase your definition and also wipe out all of your data). – Tim Biegeleisen Jun 10 '21 at 06:50
0

Do not use LOMBOK for multipurpose database, its not helping you.

Put annotations on methods forces JPA to access properties via methods. It makes sense when internal state of your object differs from the database schema:

private String text;

@Lob
@Basic(fetch = FetchType.LAZY)
@Column(name = "text")
public String getText(text ) {
    return text;
}
public void setText(String text) {
    text = text;
}

read this : source

I use this method for local Postgres development and Oracle Server both.

LunaLissa
  • 45
  • 3