Spring Boot Data JDBC field as json value-CodePudding

I have a model which i want to save to database.

@Data
public class Model {
    @Id
    private UUID id;
    private String name;
    private ModelSettings settings;

    @Data
    static class ModelSettings {
        boolean fuelEngine;
    }
}

create table model
(
    id       uuid        not null,
    name     varchar(25) not null,
    settings jsonb
)

i try to save modelSettings as jsonb object using simple repository method save(), but i got error

ERROR: relation "settings" does not exist

i wrote custom Converter and i see when modelSettings is converted to json, but after prepare statement Spring Data try to save settings field to related table. How to tell Spring Data save field as json only, not row in related table?

CodePudding user response：

Hi Please use Embedded Annotation: Embedded entities are used to have value objects in your java data model, even if there is only one table in your database.

@Data
public class Model {
    @Id
    private UUID id;
    private String name;
    @Embedded(onEmpty = USE_NULL)
    private ModelSettings settings;

    @Data
    static class ModelSettings {
        boolean fuelEngine;
    }
}

CodePudding user response：

Sorry, i forgot @WritingConverter with JdbcValue.

CodePudding user response：

You can not have an object in your entity and expect JDBC to save it as json for you.

you need to define a String column and write a converter for it for saving in and reading from database.

also some databases like Oracle supports json values but you have not mentioned which database you are using.

in Oracle database you can define a table including a json column as below:

CREATE TABLE "USERS"
(
    "ID" NUMBER(16,0) PRIMARY KEY,
    "USER_NAME" VARCHAR2(85) UNIQUE NOT NULL,
    "PASSWORD" VARCHAR2(48) NOT NULL,
    "PROFILE" NCLOB NOT NULL CONSTRAINT profile_json CHECK ("PROFILE" IS JSON),
    "SETTINGS" NCLOB NOT NULL CONSTRAINT settings_json CHECK ("SETTINGS" IS JSON),   
 
);

And you need to create your entity class as below:

@Entity
@Table(name = "Users")
public class User {

@Id
@Column(name = "ID")
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "USER_GENERATOR")
@SequenceGenerator(name = "USER_GENERATOR", sequenceName = "USERS_SEQ", allocationSize = 1)
private Long id;


@Column(name = "USER_NAME")
private String userName;
    
@Column(name = "PASSWORD")
private String password;

@Lob
@Nationalized
@Column(name = "PROFILE",columnDefinition="NCLOB NOT NULL")
private String profile;

@Lob
@Nationalized
@Column(name = "SETTINGS",columnDefinition="NCLOB NOT NULL")
private String settings;

}

as you can see here profile and setting are my json columns.