0

I am developing JavaFX Application using Spring boot, JPA, Hibernate & SQLite Database with the Gradle Build system in IntelliJ. Everything works well for the first time when there is no SQLite DB file. It will create that file and create all tables with proper definitions and I am able to do all DB operations using my app.

But when I run it for the second time It's not running and giving me an error which I am unable to solve it. Because this is the first time I am doing this.

the error is below.

Caused by: org.hibernate.exception.GenericJDBCException: Error accessing tables metadata at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
... 35 more

Caused by: org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (too many terms in compound SELECT) at org.sqlite.core.DB.newSQLException(DB.java:941) at org.sqlite.core.DB.newSQLException(DB.java:953) at org.sqlite.core.DB.throwex(DB.java:918) at org.sqlite.core.NativeDB.prepare_utf8(Native Method) at org.sqlite.core.NativeDB.prepare(NativeDB.java:134) at org.sqlite.core.DB.prepare(DB.java:257) ... 25 more



Now I have no Idea about

org.hibernate.exception.GenericJDBCException: Error accessing tables metadata

and

org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (too many terms in compound SELECT)


My application.properties file is like below 

##Database Properties
spring.datasource.driver-class-name=org.sqlite.JDBC
spring.datasource.url=jdbc:sqlite:MYDATABASE.db
spring.datasource.username=root
spring.datasource.password=


#Hibernate Properties

#The SQL dialect makes hibernate generate better SQL for chosen database
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.SQLiteDialect


#Hibernate DDL auto (create, create-drop, validate, update)
spring.jpa.hibernate.ddl-auto=update
spring.main.web-environment=false
spring.main.web-application-type=none

##Uncomment below 2 lines to enable hibernate JDBC queries/logs
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

Is there anyone gone through this? please help.

GreenROBO
  • 4,725
  • 4
  • 23
  • 43

1 Answers1

1

I suspect the problem might be related to the setting at this line:

spring.jpa.hibernate.ddl-auto=update

As far as I understand, hibernate will try to update the existing schema at startup. For this to happen, Hibernate needs to resolve the structure of your existing tables, so it needs to fetch the metadata. Looking at the exception you get, I believe it fails to fetch metadata using SQLLite JDBC driver. This would also explain why you did not get any errors when you run your application for the first time. Hibernate creates the schema from scratch in this case, so there is no error.

I suggest you to use create-drop during your development phase and none when you go to production.

You can refer to the answer for another question which explains the settings in more detail: How does spring.jpa.hibernate.ddl-auto property exactly work in Spring?

sanemain
  • 139
  • 1
  • 13
  • Your solution seems correct but my requirements are bit different. I cannot put `create-drop` as I need data every time when I run app in development phase. Also, I want to know that if I put `none` in production. How will it generate SQLite db and add all constraints on the very first time run? If is there any way to achieve this then please suggest I will apply that. – GreenROBO Oct 28 '19 at 04:05
  • So what I did in my development machine is after creating SQLite DB using `update` I changed it to `none`. Now what I will do is I will Add DB file (with tables & constraints but no DATA) in installation package. and in production code I will use `none`. I want you to confirm whether this is correct way or not. :) – GreenROBO Oct 28 '19 at 04:27
  • When you use `none`, you are responsible for creating the schema yourself. Hibernate will use the existing schema and not create the schema itself. When you use `create-drop`, hibernate creates the schema each time your application starts. Obviously, it is wise to use `none` for your production phase. Your schema structure should be finalized at this stage and there is no reason to create the DDL's each time you run the application. Besides, your production schemas will probably have data populated, so you would not want to mess with that. – sanemain Oct 28 '19 at 10:28
  • For your development phase, you can use `create-drop` and then configure hibernate to run a load script to populate data to your tables. In your persistence.xml, you can add this line: `` . This script can contain insert statements. – sanemain Oct 28 '19 at 10:32
  • Alternatively, you can also use `none` for your development phase as you mentioned, then you are responsible for updating the schema accordingly when you make changes in your Entity classes. – sanemain Oct 28 '19 at 10:38
  • Thank You for your explained response. It really helped me to solve my issue. Since I am not using persistence.xml file, I will look into it that how to use it. If I have any clarification I will come to you. Thanks again for your help sanemain. :) – GreenROBO Oct 28 '19 at 12:49
  • You are welcome. I am not sure `javax.persistence.sql-load-script-source` can be used in Spring environment directly. You can check these links for loading initial data in Spring: https://stackoverflow.com/questions/38040572/spring-boot-loading-initial-data https://dimitr.im/loading-initial-data-with-spring – sanemain Oct 28 '19 at 13:19