Spring boot error: NULL not allowed for column "ID" error

Question

I keep getting this error on start up of my Spring Boot Application

Caused by: org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: NULL not allowed for column "ID"; SQL statement:
insert into bookings(bookings_name) values('Kris') [23502-199]

data.sql has

insert into bookings(bookings_name) values('Kris');
insert into bookings(bookings_name) values('Martin');

And I believe I have the annotations so that @Id is generated automatically

@Entity
class Bookings {


    @Id
    @GeneratedValue
    @Column(name = "id", updatable = false, nullable = false)
    private Long id;


    private String BookingsName;

    public Long  getId() {
        return id;
    }

    public String getBookingsName() {
        return BookingsName;
    }

    public Bookings(String BookingsName) {
        super();
        this.BookingsName = BookingsName;

    }


    @Override
    public String toString() {
        return "Bookings [id=" + id + ", BookingsName=" + BookingsName + "]";
    }

}

I am just starting to learn Spring boot and every example I find online doesn't seem to transalte to my very trivial example here.

Full Application.java

import java.util.Collection;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Component;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@Configuration
@ComponentScan
@EnableAutoConfiguration
public class Application {

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }

}


@Component
class BookingsCommandLineRunner implements CommandLineRunner {

    @Override
    public void run(String... args) throws Exception {



    }

}




interface BookingsRepository extends JpaRepository<Bookings, Long> {

    Collection<Bookings> findByBookingsName(String BookingsName);

}


@RestController
class BookingsRestController {

    @Autowired
    BookingsRepository BookingsRepository;

    @RequestMapping("/Bookingss")
    Collection<Bookings> Bookingss() {
        return this.BookingsRepository.findAll();

    }

}


@Entity
class Bookings {


    @Id
    @GeneratedValue
    @Column(name = "id", updatable = false, nullable = false)
    private Long id;


    private String BookingsName;

    public Long  getId() {
        return id;
    }

    public String getBookingsName() {
        return BookingsName;
    }

    public Bookings(String BookingsName) {
        super();
        this.BookingsName = BookingsName;

    }


    @Override
    public String toString() {
        return "Bookings [id=" + id + ", BookingsName=" + BookingsName + "]";
    }

}

Here's a similar topic https://stackoverflow.com/a/39094773/7736617 — Arnaud Claudel, Jul 16 '19 at 17:41

score 2 · Answer 1 · answered Jul 16 '19 at 17:39

Which database do you use ? I think that the default strategy may not always work.
Try to use a different strategy in your entity declaration :

@Entity
class Bookings {

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name = "id", updatable = false, nullable = false)
    private Long id;

score 2 · Answer 2 · answered Jul 17 '19 at 07:40

You need to alter your database so that the ID column is an identity/auto-increment column i.e. the value will be managed by H2 and clients will not specify a value for new records.

Once you have done this then the the records in data.sql should be loaded without any issues.

auto increment ID in H2 database

For the JPA entities you need to update as below to advise your JPA provider that IDs are manged by the database:

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id", updatable = false, nullable = false)
private Long id;

score 1 · Answer 3 · answered Jul 16 '19 at 17:47

I think there is a confusion here about Spring/JPA and DBMS ID generation. Running pure SQL to your database (through java code or not) like hardcoded "insert into bookings(bookings_name) values('Kris');" will not care about the Id field of the entity being annotated @GeneratedValue or not.

To insert the bookings you could :

Use the JPA way to insert data through EntityManager
configure your DBMS schema to generate default IDs
Use IDs in the insert statement of your SQL

Hope that could help,

bye

Spring boot error: NULL not allowed for column "ID" error

3 Answers3