Java – How to let Hibernate create tables in database automatically when used with JPA

hibernatejavajpamysqlspring-data-jpa

I am new to JPA, And for now I am trying to understand standard examples, I was reading online and saw a few stackoverflow questions, What I found in all of them is the following statement will allow hibernate to create tables at run-time.

hibernate.hbm2ddl.auto=create

But Whenever I run my code without tables in mysql database, it throws the following error :

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v1.1.4.RELEASE)

2014-07-24 09:44:25.365  INFO 8496 --- [           main] o.s.d.j.e.r.simple.SimpleConfiguration   : Starting SimpleConfiguration on harmathu-WS with PID 8496 (C:\Songs\spring-data-jpa-examples\spring-data-jpa-example\target\classes started by harmathu in C:\Songs\spring-data-jpa-examples\spring-data-jpa-example)
2014-07-24 09:44:25.421  INFO 8496 --- [           main] s.c.a.AnnotationConfigApplicationContext : Refreshing org.springframework.context.annotation.AnnotationConfigApplicationContext@429bd883: startup date [Thu Jul 24 09:44:25 IST 2014]; root of context hierarchy
2014-07-24 09:44:26.722  INFO 8496 --- [           main] j.LocalContainerEntityManagerFactoryBean : Building JPA container EntityManagerFactory for persistence unit 'default'
2014-07-24 09:44:26.747  INFO 8496 --- [           main] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [
    name: default
    ...]
2014-07-24 09:44:26.831  INFO 8496 --- [           main] org.hibernate.Version                    : HHH000412: Hibernate Core {4.3.5.Final}
2014-07-24 09:44:26.834  INFO 8496 --- [           main] org.hibernate.cfg.Environment            : HHH000206: hibernate.properties not found
2014-07-24 09:44:26.836  INFO 8496 --- [           main] org.hibernate.cfg.Environment            : HHH000021: Bytecode provider name : javassist
2014-07-24 09:44:27.172  INFO 8496 --- [           main] o.hibernate.annotations.common.Version   : HCANN000001: Hibernate Commons Annotations {4.0.4.Final}
2014-07-24 09:44:27.390  INFO 8496 --- [           main] org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.hibernate.dialect.MySQL5Dialect
2014-07-24 09:44:27.571  INFO 8496 --- [           main] o.h.h.i.ast.ASTQueryTranslatorFactory    : HHH000397: Using ASTQueryTranslatorFactory
2014-07-24 09:44:28.392  WARN 8496 --- [           main] o.h.hql.internal.ast.HqlSqlWalker        : [DEPRECATION] Encountered positional parameter near line 1, column 97.  Positional parameter are considered deprecated; use named parameters or JPA-style positional parameters instead.
2014-07-24 09:44:28.567  INFO 8496 --- [           main] o.s.j.e.a.AnnotationMBeanExporter        : Registering beans for JMX exposure on startup
2014-07-24 09:44:28.591  INFO 8496 --- [           main] o.s.d.j.e.r.simple.SimpleConfiguration   : Started SimpleConfiguration in 3.611 seconds (JVM running for 4.607)
2014-07-24 09:44:28.658  WARN 8496 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1146, SQLState: 42S02
2014-07-24 09:44:28.659 ERROR 8496 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : Table 'test_auditor.app_user' doesn't exist
Exception in thread "main" org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute statement
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:172)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:155)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:417)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodIntercceptor.invoke(CrudMethodMetadataPostProcessor.java:111)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
    at com.sun.proxy.$Proxy56.save(Unknown Source)
    at org.springframework.data.jpa.example.repository.simple.SimpleConfiguration.main(SimpleConfiguration.java:45)
Caused by: org.hibernate.exception.SQLGrammarException: could not execute statement
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:80)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:190)
    at org.hibernate.id.IdentityGenerator$GetGeneratedKeysDelegate.executeAndExtract(IdentityGenerator.java:96)
    at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:58)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3032)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3558)
    at org.hibernate.action.internal.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:98)
    at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:490)
    at org.hibernate.engine.spi.ActionQueue.addResolvedEntityInsertAction(ActionQueue.java:195)
    at org.hibernate.engine.spi.ActionQueue.addInsertAction(ActionQueue.java:179)
    at org.hibernate.engine.spi.ActionQueue.addAction(ActionQueue.java:214)
    at org.hibernate.event.internal.AbstractSaveEventListener.addInsertAction(AbstractSaveEventListener.java:324)
    at org.hibernate.event.internal.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:288)
    at org.hibernate.event.internal.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:194)
    at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:125)
    at org.hibernate.jpa.event.internal.core.JpaPersistEventListener.saveWithGeneratedId(JpaPersistEventListener.java:84)
    at org.hibernate.event.internal.DefaultPersistEventListener.entityIsTransient(DefaultPersistEventListener.java:206)
    at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:149)
    at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:75)
    at org.hibernate.internal.SessionImpl.firePersist(SessionImpl.java:811)
    at org.hibernate.internal.SessionImpl.persist(SessionImpl.java:784)
    at org.hibernate.internal.SessionImpl.persist(SessionImpl.java:789)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.persist(AbstractEntityManagerImpl.java:1181)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:289)
    at com.sun.proxy.$Proxy49.persist(Unknown Source)
    at org.springframework.data.jpa.repository.support.SimpleJpaRepository.save(SimpleJpaRepository.java:389)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.executeMethodOn(RepositoryFactorySupport.java:405)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:390)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:344)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:262)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
    ... 8 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'test_auditor.app_user' doesn't exist
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:408)
    at com.mysql.jdbc.Util.getInstance(Util.java:383)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1062)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4226)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4158)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2840)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2082)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2334)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2262)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2246)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:187)
    ... 49 more
2014-07-24 09:44:28.674  INFO 8496 --- [       Thread-1] s.c.a.AnnotationConfigApplicationContext : Closing org.springframework.context.annotation.AnnotationConfigApplicationContext@429bd883: startup date [Thu Jul 24 09:44:25 IST 2014]; root of context hierarchy
2014-07-24 09:44:28.676  INFO 8496 --- [       Thread-1] o.s.j.e.a.AnnotationMBeanExporter        : Unregistering JMX-exposed beans on shutdown
2014-07-24 09:44:28.678  INFO 8496 --- [       Thread-1] j.LocalContainerEntityManagerFactoryBean : Closing JPA EntityManagerFactory for persistence unit 'default'

I have application.properties as this :

################### DataSource Configuration ##########################
spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test_auditor
spring.datasource.username=root
spring.datasource.password=<password>
################### Hibernate Configuration ##########################
hibernate.dialect=org.hibernate.dialect.MySQLDialect
hibernate.show_sql=true
hibernate.hbm2ddl.auto=create

Users.java :

package org.springframework.data.jpa.example.repository.simple;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.NamedQuery;
import javax.persistence.Table;
import org.springframework.data.jpa.domain.AbstractPersistable;

/**
 * Sample user class.
 * 
 * @author Oliver Gierke
 * @author Thomas Darimont
 */
@Entity
@Table (name="app_User")
@NamedQuery(name = "User.findByTheUsersName", query = "SELECT u FROM User u where u.username = ?")
public class User extends AbstractPersistable<Long> {

    private static final long serialVersionUID = -2952735933715107252L;

    @Column(name="username", unique = true) private String username;

    @Column (name="firstname")
    private String firstname;

    @Column (name="lastname")
    private String lastname;

    public User() {
        this(null);
    }

    /**
     * Creates a new user instance.
     */

    public User(Long id) {
        this.setId(id);
    }

    public User(String username, String firstname, String lastname) {
        this.firstname = firstname;
        this.lastname = lastname;
        this.username = username;
    }

    /**
     * Returns the username.
     * 
     * @return
     */
    public String getUsername() {

        return username;
    }

    /**
     * @param username the username to set
     */
    public void setUsername(String username) {
        this.username = username;
    }

    /**
     * @return the firstname
     */
    public String getFirstname() {
        return firstname;
    }

    /**
     * @param firstname the firstname to set
     */
    public void setFirstname(String firstname) {
        this.firstname = firstname;
    }

    /**
     * @return the lastname
     */
    public String getLastname() {
        return lastname;
    }

    /**
     * @param lastname the lastname to set
     */
    public void setLastname(String lastname) {
        this.lastname = lastname;
    }

     @Override
        public String toString() {
            return String.format(
                    "User[id=%d, username='%s', firstName='%s', lastName='%s']",
                    super.getId(), this.username, this.firstname, this.lastname);
        }
}

Simple Configuration.java :

package org.springframework.data.jpa.example.repository.simple;

import java.util.List;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.context.ConfigurableApplicationContext;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;




/**
 * @author Oliver Gierke
 */
@Configuration
@PropertySource(value = { "classpath:application.properties" })
@EnableJpaRepositories
@EnableAutoConfiguration
class SimpleConfiguration {
    public static void main(String[] args) {
        ConfigurableApplicationContext context = SpringApplication.run(
                SimpleConfiguration.class);

        SimpleUserRepository repository = context
                .getBean(SimpleUserRepository.class);

        repository.save(new User("harmathu", "Harsh", "Mathur"));
        repository.save(new User("vibmathu", "Vibhor", "Mathur"));
        repository.save(new User("jackbau", "Jack", "Bauer"));
        repository.save(new User("chobr", "Chloe", "O'Brian"));
        repository.save(new User("kimbau", "Kim", "Bauer"));
        repository.save(new User("davipa", "David", "Palmer"));
        repository.save(new User("midess", "Michelle", "Dessler"));

        Iterable<User> users = repository.findAll();

        System.out.println("Users found with findAll():");
        System.out.println("-------------------------------");
        for (User user : users) {
            System.out.println(user);
        }
        System.out.println();

        User user = repository.findOne(1L);
        System.out.println("User found with findOne(1L):");
        System.out.println("--------------------------------");
        System.out.println(user);
        System.out.println();

        User bauers = repository.findByTheUsersName("chobr");
        System.out.println("User found with findByLastName('Bauer'):");
        System.out.println("--------------------------------------------");

            System.out.println(bauers);


        context.close();
    }
}

Best Solution

I think this is a Spring Boot issue and not a Spring Data JPA issue.

Try changing your properties from:

hibernate.dialect=org.hibernate.dialect.MySQLDialect
hibernate.show_sql=true
hibernate.hbm2ddl.auto=create

to:

spring.jpa.database=MYSQL
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=create

http://docs.spring.io/spring-boot/docs/1.1.5.BUILD-SNAPSHOT/reference/htmlsingle/#howto-configure-jpa-properties

Related Question