On trying to create the above entity I am getting CommandAcceptanceException and the table is not able to get created in the database, please tell me how to fix it.

application.properties file

 spring.datasource.url= jdbc:mysql://localhost:3307/mapping spring.datasource.username=root spring.datasource.password=admin spring.jpa.hibernate.ddl-auto=create-drop spring.jpa.show-sql=true spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQLInnoDBDialect

Java Class:

 @Entity @Table(name="BOOKS") public class Book { @Id @Column(name="bookId") private Long id; @Column(name="TITLE") private String title; @Column(name="AUTHOR") private String author; public Book(Long id, String title, String author) { super(); this.id = id; this.title = title; this.author = author; } public Long getId() { return id; } public void setId(Long id) { this.id = id; } public String getTitle() { return title; } public void setTitle(String title) { this.title = title; } public String getAuthor() { return author; } public void setAuthor(String author) { this.author = author; } public Book() { super(); // TODO Auto-generated constructor stub } }

build.gradle file:

plugins { id 'org.springframework.boot' version '2.2.2.RELEASE' id 'io.spring.dependency-management' version '1.0.8.RELEASE' id 'java' } group = 'org.springframework.hib' version = '0.0.1-SNAPSHOT' sourceCompatibility = '1.8' repositories { mavenCentral() } dependencies { implementation 'org.springframework.boot:spring-boot-starter-data-jpa' implementation 'org.springframework.boot:spring-boot-starter-web' testCompile group: 'com.h2database', name: 'h2', version: '1.4.200' runtimeOnly 'mysql:mysql-connector-java' testImplementation('org.springframework.boot:spring-boot-starter-test') { exclude group: 'org.junit.vintage', module: 'junit-vintage-engine' } } test { useJUnitPlatform() }

Console: . ____ _ __ _ _ /\ / ' __ _ ()_ __ __ _ \ \ \ \ ( ( )_ | '_ | '| | ' / ` | \ \ \ \ \/ )| |)| | | | | || (| | ) ) ) ) ' |____| .|| ||| |__, | / / / / =========|_|==============|___/=///_/ :: Spring Boot :: (v2.2.2.RELEASE)

 2020-01-09 21:52:36.380 INFO 14108 --- [ main] com.learn.hib.LearnHibernateApplication : Starting LearnHibernateApplication on LAPTOP-B759SS03 with PID 14108 (E:\learn-space\learn-hibernate\bin\main started by Lenovo in E:\learn-space\learn-hibernate) 2020-01-09 21:52:36.380 INFO 14108 --- [ main] com.learn.hib.LearnHibernateApplication : No active profile set, falling back to default profiles: default 2020-01-09 21:52:36.974 INFO 14108 --- [ main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data JPA repositories in DEFAULT mode. 2020-01-09 21:52:37.037 INFO 14108 --- [ main] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 46ms. Found 1 JPA repository interfaces. 2020-01-09 21:52:37.302 INFO 14108 --- [ main] trationDelegate$BeanPostProcessorChecker : Bean 'org.springframework.transaction.annotation.ProxyTransactionManagementConfiguration' of type [org.springframework.transaction.annotation.ProxyTransactionManagementConfiguration] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying) 2020-01-09 21:52:37.521 INFO 14108 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat initialized with port(s): 8080 (http) 2020-01-09 21:52:37.535 INFO 14108 --- [ main] o.apache.catalina.core.StandardService : Starting service [Tomcat] 2020-01-09 21:52:37.535 INFO 14108 --- [ main] org.apache.catalina.core.StandardEngine : Starting Servlet engine: [Apache Tomcat/9.0.29] 2020-01-09 21:52:37.644 INFO 14108 --- [ main] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring embedded WebApplicationContext 2020-01-09 21:52:37.644 INFO 14108 --- [ main] o.s.web.context.ContextLoader : Root WebApplicationContext: initialization completed in 1217 ms 2020-01-09 21:52:37.816 INFO 14108 --- [ main] o.hibernate.jpa.internal.util.LogHelper : HHH000204: Processing PersistenceUnitInfo [name: default] 2020-01-09 21:52:37.878 INFO 14108 --- [ main] org.hibernate.Version : HHH000412: Hibernate Core {5.4.9.Final} 2020-01-09 21:52:38.003 INFO 14108 --- [ main] o.hibernate.annotations.common.Version : HCANN000001: Hibernate Commons Annotations {5.1.0.Final} 2020-01-09 21:52:38.097 INFO 14108 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting... 2020-01-09 21:52:39.206 INFO 14108 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed. 2020-01-09 21:52:39.216 INFO 14108 --- [ main] org.hibernate.dialect.Dialect : HHH000400: Using dialect: org.hibernate.dialect.MySQLInnoDBDialect Hibernate: create table books (book_id bigint not null, author varchar(255), title varchar(255), primary key (book_id)) type=InnoDB 2020-01-09 21:52:39.935 WARN 14108 --- [ main] o.h.t.s.i.ExceptionHandlerLoggedImpl : GenerationTarget encountered exception accepting command : Error executing DDL "create table books (book_id bigint not null, author varchar(255), title varchar(255), primary key (book_id)) type=InnoDB" via JDBC Statement org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "create table books (book_id bigint not null, author varchar(255), title varchar(255), primary key (book_id)) type=InnoDB" via JDBC Statement at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final] at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlString(AbstractSchemaMigrator.java:559) [hibernate-core-5.4.9.Final.jar:5.4.9.Final] at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlStrings(AbstractSchemaMigrator.java:504) [hibernate-core-5.4.9.Final.jar:5.4.9.Final] at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.createTable(AbstractSchemaMigrator.java:277) [hibernate-core-5.4.9.Final.jar:5.4.9.Final] at org.hibernate.tool.schema.internal.GroupedSchemaMigratorImpl.performTablesMigration(GroupedSchemaMigratorImpl.java:71) [hibernate-core-5.4.9.Final.jar:5.4.9.Final] at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.performMigration(AbstractSchemaMigrator.java:207) [hibernate-core-5.4.9.Final.jar:5.4.9.Final] at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:114) [hibernate-core-5.4.9.Final.jar:5.4.9.Final] at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:184) [hibernate-core-5.4.9.Final.jar:5.4.9.Final] at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:73) [hibernate-core-5.4.9.Final.jar:5.4.9.Final] at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:320) [hibernate-core-5.4.9.Final.jar:5.4.9.Final] at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:462) [hibernate-core-5.4.9.Final.jar:5.4.9.Final] at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1237) [hibernate-core-5.4.9.Final.jar:5.4.9.Final] at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:58) [spring-orm-5.2.2.RELEASE.jar:5.2.2.RELEASE] at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365) [spring-orm-5.2.2.RELEASE.jar:5.2.2.RELEASE] at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:391) [spring-orm-5.2.2.RELEASE.jar:5.2.2.RELEASE] at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:378) [spring-orm-5.2.2.RELEASE.jar:5.2.2.RELEASE] at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:341) [spring-orm-5.2.2.RELEASE.jar:5.2.2.RELEASE] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1855) [spring-beans-5.2.2.RELEASE.jar:5.2.2.RELEASE] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1792) [spring-beans-5.2.2.RELEASE.jar:5.2.2.RELEASE] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:595) [spring-beans-5.2.2.RELEASE.jar:5.2.2.RELEASE] at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:517) [spring-beans-5.2.2.RELEASE.jar:5.2.2.RELEASE] at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:323) [spring-beans-5.2.2.RELEASE.jar:5.2.2.RELEASE] at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222) ~[spring-beans-5.2.2.RELEASE.jar:5.2.2.RELEASE] at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:321) [spring-beans-5.2.2.RELEASE.jar:5.2.2.RELEASE] at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202) [spring-beans-5.2.2.RELEASE.jar:5.2.2.RELEASE] at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1108) ~[spring-context-5.2.2.RELEASE.jar:5.2.2.RELEASE] at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:868) ~[spring-context-5.2.2.RELEASE.jar:5.2.2.RELEASE] at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:550) ~[spring-context-5.2.2.RELEASE.jar:5.2.2.RELEASE] at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:141) ~[spring-boot-2.2.2.RELEASE.jar:2.2.2.RELEASE] at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:747) ~[spring-boot-2.2.2.RELEASE.jar:2.2.2.RELEASE] at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:397) ~[spring-boot-2.2.2.RELEASE.jar:2.2.2.RELEASE] at org.springframework.boot.SpringApplication.run(SpringApplication.java:315) ~[spring-boot-2.2.2.RELEASE.jar:2.2.2.RELEASE] at org.springframework.boot.SpringApplication.run(SpringApplication.java:1226) ~[spring-boot-2.2.2.RELEASE.jar:2.2.2.RELEASE] at org.springframework.boot.SpringApplication.run(SpringApplication.java:1215) ~[spring-boot-2.2.2.RELEASE.jar:2.2.2.RELEASE] at com.learn.hib.LearnHibernateApplication.main(LearnHibernateApplication.java:10) ~[main/:na] Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'type=InnoDB' at line 1 at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-java-8.0.18.jar:8.0.18] at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.18.jar:8.0.18] at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.18.jar:8.0.18] at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:764) ~[mysql-connector-java-8.0.18.jar:8.0.18] at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648) ~[mysql-connector-java-8.0.18.jar:8.0.18] at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:95) ~[HikariCP-3.4.1.jar:na] at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java) ~[HikariCP-3.4.1.jar:na] at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54) ~[hibernate-core-5.4.9.Final.jar:5.4.9.Final] ... 34 common frames omitted 2020-01-09 21:52:39.950 INFO 14108 --- [ main] o.h.e.t.j.p.i.JtaPlatformInitiator : HHH000490: Using JtaPlatform implementation: [org.hibernate.engine.transaction.jta.platform.internal.NoJtaPlatform] 2020-01-09 21:52:39.950 INFO 14108 --- [ main] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default' 2020-01-09 21:52:40.028 WARN 14108 --- [ main] JpaBaseConfiguration$JpaWebConfiguration : spring.jpa.open-in-view is enabled by default. Therefore, database queries may be performed during view rendering. Explicitly configure spring.jpa.open-in-view to disable this warning 2020-01-09 21:52:40.528 INFO 14108 --- [ main] o.s.s.concurrent.ThreadPoolTaskExecutor : Initializing ExecutorService 'applicationTaskExecutor' 2020-01-09 21:52:40.837 INFO 14108 --- [ main] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat started on port(s): 8080 (http) with context path '' 2020-01-09 21:52:40.837 INFO 14108 --- [ main] com.learn.hib.LearnHibernateApplication : Started LearnHibernateApplication in 4.831 seconds (JVM running for 6.101)
2

5 Answers

You need to just change

spring.jpa.hibernate.ddl-auto property to update or create

and

spring.jpa.properties.hibernate.dialect to org.hibernate.dialect.MySQL5InnoDBDialect (as suggested by Patel Romil ) and everything will work fine.

The create-drop is generally used for testing purpose, when you want to create a table on application startup, perform some db transactions in your tests and then drop the table on test case cleanup. The table won't exist in database after the test case execution gets completed.

Now coming to spring.jpa.properties.hibernate.dialect , using org.hibernate.dialect.MySQL5InnoDBDialect instead of org.hibernate.dialect.MySQLInnoDBDialect makes Hibernate to append engine=InnoDB instead of type=InnoDB to the query, so your queries will be syntactically correct ( FYI, type= InnoDB was deprecated in MySQL 5.0 and was removed in My SQL 5.1)

For more details about spring.jpa.hibernate.ddl-auto property and it's value, here is an accepted answer How does spring.jpa.hibernate.ddl-auto property exactly work in Spring?

2

Replace 

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQLInnoDBDialect

To

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect hibernate.dialect.storage_engine=innodb spring.jpa.database-platform: org.hibernate.dialect.MySQL5InnoDBDialect
6

In my case, I have a string field in Entity class like

@Column(name = "text", length = 65535) private String text;

After changing it like below issue is resolved.

@Lob @Column(name = "text") private String text;

enter application.properties file

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect hibernate.dialect.storage_engine=innodb spring.jpa.database-platform: org.hibernate.dialect.MySQL5InnoDBDialect

then run, it will work.

I realized the exception will be the same if the mysql engine type is different from the engine type of the table you want to connect to!

1

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password

Post as a guest

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy