Spring Boot and H2 Table Population Error: org.hibernate.tool.schema.spi.CommandAcceptanceException:-CodePudding

I am having trouble getting an H2 In Memory database to populate. The idea here to have the BuildingCode which is unique as a primary key and id.

I get the following class level errors:

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "alter table property add column building_code varchar(255) not null" via JDBC Statement

Caused by: org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: NULL not allowed for column "BUILDING_CODE"; SQL statement:

@Getter
@Setter
@ToString
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "Property")
public class Property {

    @Id
    @Column(name = "BuildingCode")
    String id;

    @Column(name = "Latitude")
    Float latitude;

    @Column(name = "Longitude")
    Float longitude;

    @Column(name = "BuildingName")
    String buildingName;

    @Column(name = "BuildAbr")
    String buildAbr;

    @Column(name = "Address")
    String address;

    @Column(name = "SquareFt")
    Long squareFt;

    @Column(name = "AssetId")
    Long assetId;

    public String getLatLong(){
        return "["   this.getLatitude()   ","   this.getLongitude()   "]";
    }
}

Table Creation and Sample Insert

CREATE TABLE Property(
   Latitude       NUMERIC(9,6) NOT NULL
  ,Longitude      NUMERIC(10,6) NOT NULL
  ,BuildingCode   VARCHAR(5) NOT NULL PRIMARY KEY
  ,BuildingName   VARCHAR(42) NOT NULL
  ,BuildAbr       VARCHAR(18) NOT NULL
  ,Address        VARCHAR(42) NOT NULL
  ,SquareFt       INTEGER
  ,AssetId        INTEGER
);

INSERT INTO Property(Latitude,Longitude,BuildingCode,BuildingName,BuildAbr,Address,SquareFt,AssetId) VALUES (43.453696,-76.544895,'0006','Lanigan Hall','LANIGAN-6','some address',88200,1743);

Also here is my application.properties

spring.jpa.hibernate.ddl-auto=update
spring.datasource.url = jdbc:h2:mem:testdb:DB_CLOSE_ON_EXIT=FALSE

spring.jpa.show-sql=false
spring.jpa.properties.hibernate.generate_statistics=false
spring.jpa.properties.hibernate.show_sql=false
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.H2Dialect

CodePudding user response：

In Entity class you have defined column name in camel case i.e. BuildingCode,

@Id
@Column(name = "BuildingCode")
private String id;

This will make JPA to expect a column named building_code in the DB table and if it does not find one, it will create column named building_code with data size varchar(255) and "NOT NULL".

In your insert query, it has value for BuildingCode column but no value for newly created building_code column, hence the error -

**NULL not allowed for column "BUILDING_CODE"**

Same is applicable for other Camel-cased columns as well, viz.

BuildingName, 
BuildAbr,
SquareFt,
AssetId

Conclusion: If you do not want JPA to create columns with underscore, do not mention column names in entity in camel case. Your code will work if you change column definition as below:

@Id
@Column(name = "Buildingcode") //change camel case to title case
private String id;

But, instead I would suggest simply changing column names in create table and insert table scripts to have underscore as below:

    create table property(
      latitude        NUMERIC(9,6) NOT NULL,
      longitude       NUMERIC(10,6) NOT NULL,
      building_code   VARCHAR(5) NOT NULL PRIMARY KEY,
      building_name   VARCHAR(42) NOT NULL,
      build_abr       VARCHAR(18) NOT NULL,
      address         VARCHAR(42) NOT NULL,
      square_ft       INTEGER,
      asset_id        INTEGER
    );
        
    insert into property 
    ( latitude, 
         longitude,
         building_code,
         building_name,
         build_abr,
         address,
         square_ft,
         asset_id) 
    values (43.453696,
         -76.544895,
         '0006',
         'Lanigan Hall',
         'LANIGAN-6',
         'some address',
         88200,
         1743);