Table is not found when table relation is added

Question

I want to use JPA with Spring. I tried to implement this:

Main table Merchants:

@Entity
@Table
public class Merchants {

  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  @Column(name = "merchantId", updatable = false, nullable = false)
  private int merchantId;

  @Column
  private String name;

  @OneToMany(mappedBy="merchants")
  private List<Terminals> terminals;

  @Column
  private String login;
}

Second table which holds merchant id:

@Entity
@Table
public class Terminals {

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

  @ManyToOne(fetch=FetchType.LAZY)
  @JoinColumn(name="merchantId")
  private Merchants merchants;

  @Column
  private String mode;
}

But when I deploy the code I get exception:

Caused by: java.sql.SQLException: Table 'production.terminals' doesn't exist
Query is: alter table terminals drop foreign key FKbemlf2t70j5blgdda69vyl032

When I remove table relations code the tables are created without errors. Is there any solution?

Edit:

MariaDB [production]> show tables;
+------------------------------+
| Tables_in_production |
+------------------------------+
| hibernate_sequence           |
| merchants                    |
| terminals                    |
+------------------------------+
3 rows in set (0.00 sec)

MariaDB [production]> 

application.properties

spring.jmx.enabled=false
spring.datasource.jndi-name=java:/global/production
spring.datasource.driver-class-name=org.mariadb.jdbc.Driver
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MariaDBDialect
spring.jpa.show-sql = true
spring.jpa.hibernate.ddl-auto = create-drop
request.limit=300000

Answer 1

The problem you're seeing here has to do with the DDL policy you have defined in your application.properties file.

Seems that you use the create-drop with which you tell Hibernate to create then destroy the database schema after the session has ended. Hibernate in general supports the following DDL directives:

1. update (Update the schema if necessary)
2. create (create the schema and destroy previous data)
3. create-drop (create and then destroy the schema at the end of the session)
4. none (disable ddl handling)
5. validate (validate the schema , make no changes to the database)

In any case, I have seen this happening using the create-drop DDL policy with a H2 database for integration tests. I have never spent any time to research this more, but it might be attributed to the dialect you're using, as with standard MySQL this does not happen.

In any way though, I tend to avoid using Hibernate's DDL function to create or alter my database schema -- especially on my production deployment. While it works fine for the most, using the automatic DDL function can lead to many nasty side-effects.

Thus, I tend to set Hibernate's DDL function to validate and handle all the needed schema changes - from creation to data changes - using Flyway https://flywaydb.org/.

This way I am able to run my own sql scripts to perform whatever change I need. I think that using flyway is a way better option as it allows for the following:

• Validation of the schema changes.

• A safer way to handle any database change.

• Allows for having VCS controlled schema changes (the actual scripts).

In general it integrates very well, and it's very easy to setup so I suggest you have a look at it.