Spring Boot Data JDBC field as json value

Question

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?

Sorry, i forgot @WritingConverter with JdbcValue.

score 1 · Answer 1 · answered Feb 21 '22 at 08:46

1

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;
    }
}

answered Feb 21 '22 at 08:46

Raushan Kumar

1,195
12
21

@Embedded save settings fields as part of model entity. But i need whole settings object as json. – PirateNinja Feb 21 '22 at 08:50
ERROR: column "fuel_engine" of relation "model" does not exist – PirateNinja Feb 21 '22 at 08:57
could you please check this link: https://stackoverflow.com/questions/51276703/how-to-store-postgresql-jsonb-using-springboot-jpa – Raushan Kumar Feb 21 '22 at 09:03

score 0 · Answer 2 · answered Feb 21 '22 at 11:20

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.

Spring Boot Data JDBC field as json value

2 Answers2