4

I have a huge sqlite file containing my db. I need to know if it is possible and how to connect to this db as an embedded one with jpa. I'm developing an app that packs this database inside it's own jar so that when I use it on another system I don't have to import a copy of my db back and forth. The technologies I'd like to use are Angular and Spring since those are the ones I know best. If there are some techonlogies that better suit this purpose I'd like some suggestions.

Thanks :)

Luca Federzoni
  • 45
  • 1
  • 5
  • For example you should look here: https://stackoverflow.com/questions/24232892/spring-boot-and-sqlite and here: https://www.baeldung.com/spring-boot-sqlite . There are numerous different examples in the web and here on SO. Try not to solve all problems at once. Focus you must. :-) – Semo Jun 22 '20 at 21:47
  • 1
    Thanks for the precious advices. I already looked at those post and baeldung example, however I don't understand where to place my sqlite file inside the project and how to reference it from within the configuration. It looks like they use an in memory database. – Luca Federzoni Jun 23 '20 at 08:46

1 Answers1

6

I hope I undestood your question correctly, so I made a small project for you, hence you can have a look into it: spring-jpa-sqlite-sample. It may guide you a bit, though I and don't claim correctness or completeness.

The path to the sqlite file can easily be changed by inserting the correct url in the persistence.properties file:

driverClassName=org.sqlite.JDBC
url=jdbc:sqlite:src/main/resources/chinook.db  --> you may use relative paths.
hibernate.dialect=dev.mutiny.semo.config.SQLiteDataTypesConfig
hibernate.hbm2ddl.auto=none
hibernate.show_sql=true

You can also use Environment variables from your system, which Spring tries to read from, so that you can reference the correct directory to a file. This can be found here: Read system environment var (SO)

Last but not least. Beware of using huge SQLite files. Find another way and transfer it first into a 'real' Database like any other Client/Server RDBMS you know (Oracle, MariaDB, MSSQL, depends on your scenario/taste).

Have closer look onto the documentation: When to use SQLite (and when not to!)

Semo
  • 783
  • 2
  • 17
  • 38
  • Thank you very much for your example. I can now connect to my .sqlite database file. The problem now is that this .sqlite file is already filled with stuff I need to access to. Mapping an entity 1-1 with my database I'm getting an error. Since it is not related to this topic I consider your answer as the correct answer. :) – Luca Federzoni Jun 24 '20 at 09:20
  • Thank you. Could you accept the answer, please (clicking the checkmark)? Regarding the error: It depens on the stdout output messages. Perhaps there is an issue with the entity classes? Eclipse can help you to generate the DAOs: https://blog.webnersolutions.com/java-how-to-create-entities-from-existing-database-in-eclipse-ubuntu/ – Semo Jun 24 '20 at 09:37
  • 1
    I use IntelliJ and there's a plugin for that too. I managed to solve my issue and JPA is now working fine. Thank you very much for your help. – Luca Federzoni Jun 24 '20 at 10:16