0

I have a Spring Boot based REST API that supports CRUD operations on a resource. All the methods are working fine except POST where I see two problems:

  1. The Response section (mentioned below) keeps active value as null even though the DB has the default value of 'Y' defined in schema.sql
  2. The sequence is generating negative value (-45) even though the sequence is supposed to start with 1 and increment by as defined in schema.sql

Endpoint: 

http://localhost:8080/client (POST)

Payload:

{
    "clientName": "Walmart"
}

Response: (Why is active field null and id=-45 instead of 4 here???)

{
    "clientName": "Walmart",
    "active": null, //THIS SHOULD BE 'Y'
    "_links": {
        "self": {
            "href": "http://localhost:8080/client/-45" (THIS SHOULD BE 4 INSTEAD OF -45)
        },
        "client": {
            "href": "http://localhost:8080/client/-45"
        }
    }
}

schema.sql

CREATE SEQUENCE IF NOT EXISTS CLIENT_SEQ START WITH 1 INCREMENT BY 1;
CREATE TABLE IF NOT EXISTS CLIENT(
  CLIENT_ID     BIGINT          NOT NULL  DEFAULT CLIENT_SEQ.NEXTVAL PRIMARY KEY,
  CLIENT_NAME   VARCHAR(255)    NOT NULL,
  ACTIVE        CHAR(1)         NOT NULL  DEFAULT 'Y'
);

data.sql

INSERT INTO CLIENT(CLIENT_NAME) VALUES ('SPOTIFY');
INSERT INTO CLIENT(CLIENT_NAME) VALUES ('DAILY BURN');
INSERT INTO CLIENT(CLIENT_NAME) VALUES ('CREATIVE BUG');

ClientRepository.java

@RepositoryRestResource(path = "client")
public interface ClientRepository extends PagingAndSortingRepository<Client, Long> {

}

Client.java

@Entity
@Data
public class Client {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "client_id_generator")
    @SequenceGenerator(name = "client_id_generator", sequenceName = "client_seq")
    private Long clientId;
    private String clientName;
    private String active;
}
Nital
  • 5,784
  • 26
  • 103
  • 195
  • Have you checked actual SQL queries executed by application? Not sure about `clientId` column but for `active` column if SQL looks like this `INSERT(clientName, active) VALUES('qwerty', null)` then null will be inserted in `active` column as value for it was explicitly passed in SQL and `DEFAULT` clause will be ignored – Ivan May 31 '18 at 17:53
  • Yes, I just checked the query statements after adding `spring.jpa.show-sql=true` property in `application.properties` and this is what I see: `Hibernate: call next value for client_seq Hibernate: insert into client (active, client_name, client_id) values (?, ?, ?)` – Nital May 31 '18 at 18:04
  • I think you could declare `active` property as `private String active = "Y";`. This will fix setting default value – Ivan May 31 '18 at 18:20
  • Yes, it definitely will but that will still leave me puzzled as to why the earlier implementation didn't work because I see nothing wrong in it. – Nital May 31 '18 at 18:48
  • I think this is how `DEFAULT` in database works. If you omit that column in insert it will use default value but if you provide that column but with null value then that null value passed explicitly will be used instead of default. You could execute the following insert to check `INSERT INTO CLIENT(CLIENT_NAME, active) VALUES ('QUERTY', null);` – Ivan May 31 '18 at 19:03
  • Ok, now I understand. Any idea on that negative sequence value that is getting generated? – Nital May 31 '18 at 19:21
  • Check this question: https://stackoverflow.com/questions/9861416/hibernate-generates-negative-id-values-when-using-a-sequence – Ivan May 31 '18 at 19:41
  • Thanks a lot Ivan, the problem is resolved. The issue was with `allocationSize` field mismatch with the `INCREMENT BY` value in schema.sql script – Nital May 31 '18 at 19:52

0 Answers0