Spring boot, PostgreSql setup using Gradle


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:

  1. 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.

  1. 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.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s