Although configuring a Spring Boot application to use a persistent database from a in-memory database like H2 database might be a simple task for most developers, however since I am not developing on a frequent basis so this was a bit of a task for me, and I was not able to find a dummies guide to go about it, so following are the steps to change an in-memory database like H2 to PostgreSQL
Why PostgreSQL
Since Oracle’s acquisition of MySQL I am sceptical of using MySQL as my default Open Source database so I have started using PostGreSQL over mySQL.
Step 1:
Go to https://jdbc.postgresql.org/download.html to download the required driver for yourself. Since I installed the latest version of PostgreSQL so I download version 42.1.1 the latest version at the time of writing this blog post.
The next step is to place the PostgreSQL jdbc jar file in the project class path, also include this in your list of dependencies in eclipse, so that the compiler is able to find the JDBC driver
Step 2:
Include the dependency in the gradle build file. With my little understanding of gradle this was the most time consuming part as I could not find the correct syntax to include the PostgreSQL dependency in the gradle build file.
This is as simple as including
compile ‘postgresql:postgresql:42.1.1’ |
in your gradle build file.
My complete gradle.build file is as under
buildscript { ext { springBootVersion = ‘2.0.0.BUILD-SNAPSHOT’ } repositories { mavenCentral() } dependencies { classpath(“org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}“) } } apply plugin: ‘java’ apply plugin: ‘eclipse’ apply plugin: ‘org.springframework.boot’ jar { baseName = ‘integration’ version = ‘0.0.1-SNAPSHOT’ } sourceCompatibility = 1.8‘ targetCompatibility = 1.8 repositories { mavenCentral() } dependencies { compile(‘org.springframework.boot:spring-boot-starter-data-jpa‘) compile(‘org.springframework.boot:spring-boot-starter-web‘) compile(‘org.springframework.boot:spring-boot-starter-web-services‘) compile(‘org.springframework.boot:spring-boot-spring-plugin-core‘) compile(‘org.springframework.boot:spring-boot-spring-hateoas‘) compile ‘postgresql:postgresql:42.1.1‘ testCompile(‘org.springframework.boot:spring-boot-starter-test‘) } |
Step 3:
After inclusion of the dependency in the gradle.build file also include the following lines in your Application.properties file to inform the application where the PostgreSQL server is and what are the credentials to access the database
spring.datasource.url= jdbc:postgresql://localhost:5432/dbname spring.datasource.username=postgres spring.datasource.password=xxxxxxxx spring.jpa.hibernate.ddl-auto=create-drop |
That’s it, once you have performed the above actions, your project should now complete and use PostgreSQL as the database server, please note that I came across the following few error messages, so I thought it might be good to mention them here.
Errors:
- Error java.sql.SQLException: Unable to load class: org.postgresql.Driver
This just means that the compiler is not able to find the jar file for the jdbc driver. Make sure you have downloaded the jdbc driver and placed it in the project class path.
- Unable to execute the sql with value ‘desc’
Desc is a reserved word in PostgreSQL server so make sure that you are not using any database fields that are reserved for the specific database server.