Hibernate says that table doesn't exist but it does

Question

I am experiencing problem with Hibernate throwing following error:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'Library.book' doesn't exist

My dependency setup looks like this (I believe this might be the reason):

compile("org.springframework.boot:spring-boot-starter-web")
testCompile("org.springframework.boot:spring-boot-starter-test") 

compile 'org.springframework:spring-orm:4.1.6.RELEASE'
compile 'org.springframework.data:spring-data-jpa:1.8.0.RELEASE'

compile 'org.hibernate:hibernate-entitymanager:4.3.8.Final'
compile 'org.hibernate:hibernate-core:4.3.8.Final'

compile 'org.apache.commons:commons-dbcp2:2.1'

compile 'mysql:mysql-connector-java:5.1.35'

compile 'org.apache.logging.log4j:log4j-core:2.2'

So I am using spring-boot-starter-web (project created using Spring CLI), then added not-spring-boot dependencies for Hibernate and Spring Data among other things (used the exact same dependency set in different projects, but without spring-boot-starter-web and everything worked just fine).

After reading others' questions I checked whether my @EnableJpaRepositories has correct path to Repositories and if entityManagerFactoryBean has packagesToScan set correctly.

I believe that Spring Boot has conflicts with other dependencies, because my configuration looks fine.

I will now show some code snippets, because I might be wrong about my configuration's correctness ;p

Book MySQL DDL:

CREATE TABLE IF NOT EXISTS `Library`.`Book` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(100) NOT NULL,
  `description` VARCHAR(256),
  `genre` VARCHAR(50) NOT NULL,
  `releaseDate` DATE NOT NULL,
  PRIMARY KEY (`id`)
)

Book entity:

@Entity
@Table(name="Book")
public class Book {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;
    private String title;
    private String description;
    private String genre;
    @Temporal(TemporalType.DATE)
    private Date releaseDate;
}

EntityManagerFactory bean:

@Bean
@Autowired(required = true)
public EntityManagerFactory entityManagerFactory(DataSource dataSource) {
    HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
    vendorAdapter.setGenerateDdl(true);
    vendorAdapter.setShowSql(false);
    vendorAdapter.setDatabasePlatform("org.hibernate.dialect.MySQLDialect");
    vendorAdapter.setDatabase(Database.MYSQL);

    LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
    factory.setJpaVendorAdapter(vendorAdapter);
    factory.setPackagesToScan("pl.com.imralav.library.data.entity");
    factory.setDataSource(dataSource);

    Properties properties = new Properties();
    properties.setProperty("hibernate.generate_statistics", "false");
    properties.setProperty("hibernate.show_sql", "false");

    factory.setJpaProperties(properties);

    factory.afterPropertiesSet();

    return factory.getObject();
}

Please tell me if you need more information.

If you look carefully 'Library.book' has `book` in lower case. It is maybe related to [JPA 2.0 & MySQL are not respecting case sensitive table names](http://stackoverflow.com/questions/15247386/jpa-2-0-mysql-are-not-respecting-case-sensitive-table-names) — Nicolas Labrot, Apr 10 '15 at 18:58
There is some improvement, after changing database name to book, new error appears: `com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'book0_.release_date' in 'field list'`, I tried to fix it by using @Column(name="releaseDate") annotation, but it didn't work (just like @Table(name="Book") didn't). Any idea why are the annotations ignored? — Tomek, Apr 10 '15 at 19:07
After recreating database with a change to book schema (release_date instead of releaseDate) it all worked just fine. But I am not satisfied, as I need to know why some annotations worked (like `@Entity`, because the book was mapped to database table) and some didn't (like `@Table`, `@Column`, they didn't make Hibernate look for other table or other column name). — Tomek, Apr 10 '15 at 19:24
The Spring boot default Hibernate naming strategy is an extension of Hibernate `ImprovedNamingStrategy` which `prefers embedded underscores to mixed case names` (ie. camel case name are converted to underscore). You could override using the property `spring.jpa.hibernate.naming_strategy`. BTW I recommend to use naming convention using underscore with name in upper/lower case — Nicolas Labrot, Apr 10 '15 at 19:42
Could you please clarify one thing for me? When using spring boot, it tries to autoconfigure ANY dependency, doesn't matter if it's spring-boot specific dependency or not? Until now I thought that for spring-boot to do autoconfiguring I need to add some special spring-boot version of a dependency, like spring-boot-starter-web I am using right now, and it will autoconfigure only those spring-boot dependencies. — Tomek, Apr 10 '15 at 19:58
As soon as you import the spring boot dependency, you import all its [auto configurations](http://docs.spring.io/spring-boot/docs/current/api/) which include web, jpa, hibernate.... I'm not very comfortable with my previous statement as you already define the `LocalContainerEntityManagerFactoryBean`. Some debugging may help to understand the initialization flow — Nicolas Labrot, Apr 10 '15 at 20:27
You should really name your tables with snake_case for compatibility (if you ever change databases) — Neil McGuigan, Apr 11 '15 at 20:08
Sure, never really realised this convention, haven't been doing much persistence coding till now. Thanks ;) — Tomek, Apr 13 '15 at 04:16

Tomek · Accepted Answer · 2015-04-11T18:52:30.883

5

The problem was my poor understanding of how Spring Boot works. It loads whole set of autoconfiguration classess, which in my case where setting incorrect naming strategy for Hibernate. All I had to do was exclude the Hibernate autoconfiguration class

@SpringBootApplication(exclude={HibernateJpaAutoConfiguration.class})

and suddenly everything worked just like I wanted it to. Big thanks to @Nicolas for pointing me in the right directions with his comments.

~~One thing that surprises me was that~~ according to Spring Boot Reference:

Auto-configuration is noninvasive, at any point you can start to define your own configuration to replace specific parts of the auto-configuration. For example, if you add your own DataSource bean, the default embedded database support will back away.

~~but in my case it didn't work.~~ and it works perfectly fine, I just needed to "override" correct bean (as pointed out by @Oliver)

edited Apr 11 '15 at 18:52

answered Apr 10 '15 at 20:45

Tomek

147
1
1
11

Disabling the auto-configuration works, if you stick to the conventions of exposing a `LocalContainerEntityManagerFactoryBean` instead of `EntityManagerFactory` as this is what Boot's auto-configuration currently expects not to find. I've filed [a ticket for Spring boot](https://github.com/spring-projects/spring-boot/issues/2803) to also consider this case but simply switching to returning the `LCEMFB` should make it work as expected. – Oliver Drotbohm Apr 11 '15 at 14:04
@Tomek can you please shed more lights on what was done for "I just needed to "override" correct bean (as pointed out by @Oliver"? – Dom Nov 12 '19 at 07:53

Hibernate says that table doesn't exist but it does

1 Answers1

Linked