How to handle not null field in java before inserting record in table

Question

I am trying to implement post request using spring data jpa. I am trying to add new room in room table with some values. Some of the column from room table are not null I set that with default. But when I am inserting I am getting error as violates not-null constraint.

RoomService class

// Add new Room Details
    public String addNewRoom(@RequestBody RoomInformation roomInfo) {

        Room roomRecord = new Room();

        if(roomInfo.nCampusId != 0)
        roomRecord.nCampusId = roomInfo.nCampusId;

        if(roomInfo.nBuildId != 0)
        roomRecord.nBuildId=roomInfo.nBuildId;

        if(roomInfo.nCRTCodeId !=0)
        roomRecord.nCRTCodeId=roomInfo.nCRTCodeId;

        roomRecord.nInstId=roomInfo.nInstId;
        roomRecord.sRoomNumber=roomInfo.sRoomNumber;
        roomRecord.sRoomDesc=roomInfo.sRoomDesc;
        roomRecord.nArea=roomInfo.nArea;
        roomRecord.sFloor=roomInfo.sFloor;
        roomRecord.bIsActive= true; 

        roomRepository.save(roomRecord);            

        return "New room added sucessfully";
    }

Room Table

CREATE TABLE public.room
(
    nroom_id numeric(18,0) NOT NULL DEFAULT nextval('room_seq'::regclass),
    ncampus_id numeric(18,0),
    nbuild_id numeric(18,0),
    ninst_id numeric(18,0) NOT NULL DEFAULT 0,
    sfloor character varying(10) COLLATE pg_catalog."default",
    sroom_number character varying(10) COLLATE pg_catalog."default",
    sroom_desc character varying(255) COLLATE pg_catalog."default",
    scomments text COLLATE pg_catalog."default",
    daccepted_date timestamp(3) without time zone,
    ssurveyor character varying(255) COLLATE pg_catalog."default",
    narea integer,
    ncrt_code_id numeric(18,0),
    ncmn_room_bln smallint,
    nunvalidated_bln smallint,
    sbfr_field character varying(50) COLLATE pg_catalog."default",
    ntemp_room_id numeric(18,0),
    bis_active boolean NOT NULL DEFAULT false,
    bis_jointuse boolean NOT NULL DEFAULT false,
    sstations_desc character varying(25) COLLATE pg_catalog."default",
    ddeleted_on timestamp(3) without time zone,
    ndeleted_by numeric(18,0),
    bis_incluster boolean NOT NULL DEFAULT false,
    bis_service_center_activity boolean NOT NULL DEFAULT false,
    service_center_comments text COLLATE pg_catalog."default",
    CONSTRAINT pk_roomdeails PRIMARY KEY (nroom_id),
        )

Error at console

Null value in column "bis_jointuse" violates not-null constraint

Detail: Failing row contains (1203521, 270, 11135, 106, 0, 10, abc, null, null, null, 22, 2122, null, null, null, null, t, null, null, null, null, null, null, null).

You explicitly request to store NULL in those columns and that's what Postgres will (try to) do. — user330315
– user330315, Commented Sep 25, 2018 at 14:43

Lukasz Szozda · Accepted Answer · 2018-09-25 14:43:27Z

2

You need to skip column or provide DEFAULT keyword instead of explicit NULL:

INSERT INTO tab(col1,...,bis_jointuse)
VALUES (22, ..., DEFAULT);

-- skipping column
INSERT INTO tab(col1,...)
VALUES (22,...);

Those values are not null in table that I set with default value. But default value is not taking when I am trying to insert.

As far I as know only Oracle supports such construct: DEFAULT Values On Explicit NULLs

In the previous section we saw default values are only used when a column is not referenced in an insert statement. If the column is referenced, even when supplying the value NULL, the default value is not used. Oracle 12c allows you to modify this behaviour using the ON NULL clause in the default definition.

CREATE TABLE t2 (
  col1        NUMBER DEFAULT 1,
  col2        NUMBER DEFAULT ON NULL 2, 
  description VARCHAR2(30)
);

edited Sep 25, 2018 at 14:43

answered Sep 25, 2018 at 14:39

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SpringUser Over a year ago

Actually I am not manually inserting into table, I am implementing one service for save button click. In that I am only saving some values rest of values its taking as null

Lukasz Szozda Over a year ago

@SpringUser So you have to handle it in your app code. Simply does not provide values when NULL is present or use EXPLICIT DEFAULT keyword.

Collectives™ on Stack Overflow

How to handle not null field in java before inserting record in table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related