doublemc doublemc -4 years ago 353
Java Question

Hibernate and postgresql. Errors while exporting schema

I'm getting around 10 errors when I start my Spring Boot app using Hibernate and postgresql. How can I fix them? WHat's the problem?
Here are my entities:

Customer.java

@Entity
@Access(AccessType.FIELD) // so I can avoid using setters for fields that won't change
public class Customer {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long customerId;

@Embedded
private FirstName firstName;
@Embedded
private LastName lastName;
@Embedded
private PhoneNumber phoneNumber;

@ManyToOne
@JoinColumn(name = "ADDRESS_ID")
private Address address;

@OneToOne
@JoinColumn(name = "USER_ID")
private User user;

// jpa requirement
public Customer() {
}

public Customer(FirstName firstName, LastName lastName,
PhoneNumber phoneNumber, Address address, User user) {
this.firstName = firstName;
this.lastName = lastName;
this.phoneNumber = phoneNumber;
this.address = address;
this.user = user;
}

public Long getCustomerId() {
return customerId;
}

public FirstName getFirstName() {
return firstName;
}

public LastName getLastName() {
return lastName;
}

public PhoneNumber getPhoneNumber() {
return phoneNumber;
}

// setter for phone number is needed because customer can change his phone number
public void setPhoneNumber(PhoneNumber phoneNumber) {
this.phoneNumber = phoneNumber;
}

public Address getAddress() {
return address;
}

// setter for address is needed because customer can change his address
public void setAddress(Address address) {
this.address = address;
}

public User getUser() {
return user;
}

@Override
public boolean equals(Object o) {
if (this == o) {
return true;
}
if (o == null || getClass() != o.getClass()) {
return false;
}

Customer customer = (Customer) o;

if (firstName != null ? !firstName.equals(customer.firstName) : customer.firstName != null) {
return false;
}
if (lastName != null ? !lastName.equals(customer.lastName) : customer.lastName != null) {
return false;
}
return user != null ? user.equals(customer.user) : customer.user == null;
}

@Override
public int hashCode() {
int result = firstName != null ? firstName.hashCode() : 0;
result = 31 * result + (lastName != null ? lastName.hashCode() : 0);
result = 31 * result + (user != null ? user.hashCode() : 0);
return result;
}

@Override
public String toString() {
return "Customer{" +
"firstName=" + firstName +
", lastName=" + lastName +
", phoneNumber=" + phoneNumber +
'}';
}
}


User.java

@Entity
public class User {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long userId;

@Embedded
private EmailAddress emailAddress;
@Embedded
private Password password;

@OneToOne
@JoinColumn(name = "CUSTOMER_ID")
private Customer customer;

@Column
@Enumerated(EnumType.STRING)
private UserRole userRole;

// jpa requirement
public User() {
}

public User(EmailAddress emailAddress, Password password) {
this.emailAddress = emailAddress;
this.password = password;
this.userRole = UserRole.USER; // on creation everyone is just a user
}

public Long getUserId() {
return userId;
}

public EmailAddress getEmailAddress() {
return emailAddress;
}

public void setEmailAddress(EmailAddress emailAddress) {
this.emailAddress = emailAddress;
}

public Password getPassword() {
return password;
}

public void setPassword(Password password) {
this.password = password;
}

public Customer getCustomer() {
return customer;
}

public void setCustomer(Customer customer) {
this.customer = customer;
}

public UserRole getUserRole() {
return userRole;
}

public void setUserRole(UserRole userRole) {
this.userRole = userRole;
}
}


And Book.java:

@Entity
@Access(AccessType.FIELD) // so I can avoid using setters for fields that won't change
public class Book {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long bookId;

@Embedded
private Isbn isbn;
@Embedded
private Title title;
@Embedded
private Author author;
@Embedded
private Genre genre;
private Year publicationYear;
private BigDecimal price;

// jpa requirement
public Book() {
}

public Book(Isbn isbn, Title title, Author author, Genre genre, Year publicationYear,
BigDecimal price) {
this.isbn = isbn;
this.title = title;
this.author = author;
this.genre = genre;
this.publicationYear = publicationYear;
this.price = price;
}

public Long getBookId() {
return bookId;
}

public Isbn getIsbn() {
return isbn;
}

public Title getTitle() {
return title;
}

public Author getAuthor() {
return author;
}

public Genre getGenre() {
return genre;
}

public BigDecimal getPrice() {
return price;
}

public Year getPublicationYear() {
return publicationYear;
}

// setter for price is needed because price of the book can change (discounts and so on)
public void setPrice(BigDecimal price) {
this.price = price;
}

}


My application.properties for postgresql settings:

spring.datasource.url= jdbc:postgresql://localhost:5432/bookrest
spring.datasource.username=postgres
spring.datasource.password=password

spring.jpa.hibernate.ddl-auto=create-drop


And here is the stack trace for hibernate part (ommitted all the controoller mapping):

2017-02-27 11:03:05.481 INFO 6563 --- [ restartedMain] o.hibernate.jpa.internal.util.LogHelper : HHH000204: Processing PersistenceUnitInfo [
name: default
...]
2017-02-27 11:03:05.555 INFO 6563 --- [ restartedMain] org.hibernate.Version : HHH000412: Hibernate Core {5.0.11.Final}
2017-02-27 11:03:05.556 INFO 6563 --- [ restartedMain] org.hibernate.cfg.Environment : HHH000206: hibernate.properties not found
2017-02-27 11:03:05.558 INFO 6563 --- [ restartedMain] org.hibernate.cfg.Environment : HHH000021: Bytecode provider name : javassist
2017-02-27 11:03:05.609 INFO 6563 --- [ restartedMain] o.hibernate.annotations.common.Version : HCANN000001: Hibernate Commons Annotations {5.0.1.Final}
2017-02-27 11:03:05.819 INFO 6563 --- [ restartedMain] org.hibernate.dialect.Dialect : HHH000400: Using dialect: org.hibernate.dialect.PostgreSQLDialect
2017-02-27 11:03:05.997 INFO 6563 --- [ restartedMain] o.h.e.j.e.i.LobCreatorBuilderImpl : HHH000424: Disabling contextual LOB creation as createClob() method threw error : java.lang.reflect.InvocationTargetException
2017-02-27 11:03:05.999 INFO 6563 --- [ restartedMain] org.hibernate.type.BasicTypeRegistry : HHH000270: Type registration [java.util.UUID] overrides previous : org.hibernate.type.UUIDBinaryType@c866efb
2017-02-27 11:03:06.228 WARN 6563 --- [ restartedMain] org.hibernate.orm.deprecation : HHH90000014: Found use of deprecated [org.hibernate.id.SequenceGenerator] sequence-based id generator; use org.hibernate.id.enhanced.SequenceStyleGenerator instead. See Hibernate Domain Model Mapping Guide for details.
2017-02-27 11:03:06.580 INFO 6563 --- [ restartedMain] org.hibernate.tool.hbm2ddl.SchemaExport : HHH000227: Running hbm2ddl schema export
2017-02-27 11:03:06.584 ERROR 6563 --- [ restartedMain] org.hibernate.tool.hbm2ddl.SchemaExport : HHH000389: Unsuccessful: alter table customer drop constraint FKglkhkmh2vyn790ijs6hiqqpi
2017-02-27 11:03:06.584 ERROR 6563 --- [ restartedMain] org.hibernate.tool.hbm2ddl.SchemaExport : ERROR: relation "customer" does not exist
2017-02-27 11:03:06.584 ERROR 6563 --- [ restartedMain] org.hibernate.tool.hbm2ddl.SchemaExport : HHH000389: Unsuccessful: alter table customer drop constraint FKidmyb2vdwmk3o502u0rg8g32h
2017-02-27 11:03:06.585 ERROR 6563 --- [ restartedMain] org.hibernate.tool.hbm2ddl.SchemaExport : ERROR: relation "customer" does not exist
2017-02-27 11:03:06.585 ERROR 6563 --- [ restartedMain] org.hibernate.tool.hbm2ddl.SchemaExport : HHH000389: Unsuccessful: alter table user drop constraint FK2q989f4c89rv2b9xvtomfc0fs
2017-02-27 11:03:06.586 ERROR 6563 --- [ restartedMain] org.hibernate.tool.hbm2ddl.SchemaExport : ERROR: syntax error at or near "user"
Position: 13
2017-02-27 11:03:06.587 ERROR 6563 --- [ restartedMain] org.hibernate.tool.hbm2ddl.SchemaExport : HHH000389: Unsuccessful: drop table if exists user cascade
2017-02-27 11:03:06.587 ERROR 6563 --- [ restartedMain] org.hibernate.tool.hbm2ddl.SchemaExport : ERROR: syntax error at or near "user"
Position: 22
2017-02-27 11:03:06.588 ERROR 6563 --- [ restartedMain] org.hibernate.tool.hbm2ddl.SchemaExport : HHH000389: Unsuccessful: drop sequence hibernate_sequence
2017-02-27 11:03:06.588 ERROR 6563 --- [ restartedMain] org.hibernate.tool.hbm2ddl.SchemaExport : ERROR: sequence "hibernate_sequence" does not exist
2017-02-27 11:03:06.607 ERROR 6563 --- [ restartedMain] org.hibernate.tool.hbm2ddl.SchemaExport : HHH000389: Unsuccessful: create table user (user_id bigserial not null, email_address varchar(255), password varchar(255), user_role varchar(255), customer_customer_id int8, primary key (user_id))
2017-02-27 11:03:06.607 ERROR 6563 --- [ restartedMain] org.hibernate.tool.hbm2ddl.SchemaExport : ERROR: syntax error at or near "user"
Position: 14
2017-02-27 11:03:06.610 ERROR 6563 --- [ restartedMain] org.hibernate.tool.hbm2ddl.SchemaExport : HHH000389: Unsuccessful: alter table customer add constraint FKidmyb2vdwmk3o502u0rg8g32h foreign key (user_user_id) references user
2017-02-27 11:03:06.610 ERROR 6563 --- [ restartedMain] org.hibernate.tool.hbm2ddl.SchemaExport : ERROR: syntax error at or near "user"
Position: 103
2017-02-27 11:03:06.610 ERROR 6563 --- [ restartedMain] org.hibernate.tool.hbm2ddl.SchemaExport : HHH000389: Unsuccessful: alter table user add constraint FK2q989f4c89rv2b9xvtomfc0fs foreign key (customer_customer_id) references customer
2017-02-27 11:03:06.610 ERROR 6563 --- [ restartedMain] org.hibernate.tool.hbm2ddl.SchemaExport : ERROR: syntax error at or near "user"
Position: 13
2017-02-27 11:03:06.611 INFO 6563 --- [ restartedMain] org.hibernate.tool.hbm2ddl.SchemaExport : HHH000230: Schema export complete
2017-02-27 11:03:06.673 INFO 6563 --- [ restartedMain] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'


EDIT 1:

After changing my User to AppUser (table name user is prohibited) I'm still getting few errors:

2017-02-27 11:45:44.253 INFO 9560 --- [ restartedMain] org.hibernate.Version : HHH000412: Hibernate Core {5.0.11.Final}
2017-02-27 11:45:44.255 INFO 9560 --- [ restartedMain] org.hibernate.cfg.Environment : HHH000206: hibernate.properties not found
2017-02-27 11:45:44.256 INFO 9560 --- [ restartedMain] org.hibernate.cfg.Environment : HHH000021: Bytecode provider name : javassist
2017-02-27 11:45:44.298 INFO 9560 --- [ restartedMain] o.hibernate.annotations.common.Version : HCANN000001: Hibernate Commons Annotations {5.0.1.Final}
2017-02-27 11:45:44.621 INFO 9560 --- [ restartedMain] org.hibernate.dialect.Dialect : HHH000400: Using dialect: org.hibernate.dialect.PostgreSQLDialect
2017-02-27 11:45:44.825 INFO 9560 --- [ restartedMain] o.h.e.j.e.i.LobCreatorBuilderImpl : HHH000424: Disabling contextual LOB creation as createClob() method threw error : java.lang.reflect.InvocationTargetException
2017-02-27 11:45:44.828 INFO 9560 --- [ restartedMain] org.hibernate.type.BasicTypeRegistry : HHH000270: Type registration [java.util.UUID] overrides previous : org.hibernate.type.UUIDBinaryType@35c0ac4e
2017-02-27 11:45:45.370 INFO 9560 --- [ restartedMain] org.hibernate.tool.hbm2ddl.SchemaExport : HHH000227: Running hbm2ddl schema export
2017-02-27 11:45:45.373 ERROR 9560 --- [ restartedMain] org.hibernate.tool.hbm2ddl.SchemaExport : HHH000389: Unsuccessful: alter table app_user drop constraint FKf8cjd2mkc4tu1u5nhju0clae7
2017-02-27 11:45:45.374 ERROR 9560 --- [ restartedMain] org.hibernate.tool.hbm2ddl.SchemaExport : ERROR: relation "app_user" does not exist
2017-02-27 11:45:45.374 ERROR 9560 --- [ restartedMain] org.hibernate.tool.hbm2ddl.SchemaExport : HHH000389: Unsuccessful: alter table customer drop constraint FKglkhkmh2vyn790ijs6hiqqpi
2017-02-27 11:45:45.374 ERROR 9560 --- [ restartedMain] org.hibernate.tool.hbm2ddl.SchemaExport : ERROR: relation "customer" does not exist
2017-02-27 11:45:45.375 ERROR 9560 --- [ restartedMain] org.hibernate.tool.hbm2ddl.SchemaExport : HHH000389: Unsuccessful: alter table customer drop constraint FKslkyb5dphxe4c7au3hqx3la6m
2017-02-27 11:45:45.375 ERROR 9560 --- [ restartedMain] org.hibernate.tool.hbm2ddl.SchemaExport : ERROR: relation "customer" does not exist
2017-02-27 11:45:45.408 INFO 9560 --- [ restartedMain] org.hibernate.tool.hbm2ddl.SchemaExport : HHH000230: Schema export complete
2017-02-27 11:45:45.467 INFO 9560 --- [ restartedMain] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'

Answer Source

user is a reserved keyword in postgresql. I would suggest renaming your entity class to some other name, or just use hibernate annotations @Table to specify postgresql friendly table name, like this:

@Entity
@Table(name = "library_user")
public class User {...}
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download