9

In my application there is an entity:

@Entity
@Table(schema = "hr", name = "personal_data")
public class PersonalData {
}

and connection string defined in Spring's application.properties:

spring.datasource.url=jdbc:mysql://localhost/mobile?UseUnicode=true&characterEncoding=utf8

If I invoke the following code:

TypedQuery<E> typedQuery = em.createQuery("from PersonalData pd where pd.employeeId = ?1", PersonalData.class);
typedQuery.setParameter(1, 123);
return typedQuery.getSingleResult();

it will result in this SQL:

select * from personal_data personalda0_ where personalda0_.employee_id=?

Which will fail with the exception

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'mobile.personal_data' doesn't exist

because the table personal_data is defined in the hr database and there is no such table in mobile.

This was working fine(i.e. table name in SQL was prefixed with database name) in Hibernate 4.3.13 and stopped when the application was migrated to Spring Boot 2.0 which uses Hibernate 5.2.14. Is there any way to achieve the old behaviour in Hibernate 5.x?

Tomasz W
  • 2,263
  • 1
  • 17
  • 22
  • 1
    remove `mobile` from connection string? – XtremeBaumer Mar 29 '18 at 09:38
  • @XtremeBaumer I've tried this as suggested in https://stackoverflow.com/questions/47296766/spring-boot-jpa-does-not-prepend-schema-name-to-tables-in-query but it results in java.sql.SQLException: No database selected – Tomasz W Mar 29 '18 at 09:51
  • since MySQL does not support schema (and never has), and instead uses catalog then is that surprising? That is irrespective of which JPA provider you use –  Mar 29 '18 at 10:35
  • @DN1 It worked in the previous versions of Hibernate. As for the naming convention in MySQL I think it's ambiguous - see https://stackoverflow.com/questions/7942520/relationship-between-catalog-schema-user-and-database-instance/7944489#7944489 – Tomasz W Mar 29 '18 at 10:59

1 Answers1

14

I can say that there is a misunderstanding between Hibernate 5 and MySQL, a long story here Hibernate 5.0.6 Ignores schema in MySQL

One Solution is proposed is to use the name of schema in the place of catalog so instead of :

@Table(schema = "hr", name = "personal_data")
       ^^^^^^

You can use :

@Table(catalog = "hr", name = "personal_data")
       ^^^^^^^

Also take a look at this :

Youcef LAIDANI
  • 55,661
  • 15
  • 90
  • 140
  • It works! Thanks for the answer and for the references. I am not sure if this is the best solution in my case, most likely I will create views in MySQL for the missing tables instead, as this is MySQL specific issue. – Tomasz W Mar 29 '18 at 10:18
  • @TomaszW I'm sure that they will fix this, maybe by create a new dialect, I will search more, If I find another solution I will inform you ;) – Youcef LAIDANI Mar 29 '18 at 10:25