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
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);
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments