Spring Boot + Spring JPA with PostgreSQL or MySQL or Oracle or SQL Server database and Thymeleaf using Gradle

Here we will see how to build a sample full stack web application using Spring Boot, Spring JPA with database like PostgreSQL, MySQL or Oracle Thymeleaf and Gradle. This example is very similar with my other example regarding Spring boot with H2 database:

The only exception here, we will use some production database like postgres as database. So there are changes around Spring JPA configuration and Gradle build file. Also I have made some minor changes in StudentController.java.

Like other example, here also we will create a sample Student Information System where we can enter Student record in one panel and see the existing records in another panel like this screenshot:

Student Information System

The source files are mostly same with the H2 database example. So I am only showing the changed files here. You can get complete source code by downloading the zip file from bottom of this page.


build.gradle

The build.gradle files defines all the dependencies to Spring boot, Thymeleaf and Postgres database library.

buildscript {
    ext {
        springBootVersion = '1.5.3.RELEASE'
    }
    repositories {
        mavenCentral()
    }
    dependencies {
        classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
    }
}

group 'com.firstfewlines'
version '1.0.0-SNAPSHOT'

apply plugin: 'java'
apply plugin: 'idea'
apply plugin: 'org.springframework.boot'

repositories {
    mavenCentral()
}

dependencies {
    compile "org.springframework.boot:spring-boot-starter-data-jpa:$springBootVersion"
    compile "org.springframework.boot:spring-boot-starter-web:$springBootVersion"
    compile "org.springframework.boot:spring-boot-starter-thymeleaf:$springBootVersion"
    compile "org.springframework.boot:spring-boot-devtools:$springBootVersion"
    runtime 'org.postgresql:postgresql:42.1.1'
}

To use database other than Postgres, we need to change the above runtime dependency to appropriate database driver.


SpringBootPostgresExample.java

It is being the main Application entry point, initializes the Spring boot application.

package com.firstfewlines;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class SpringBootPostgresExample {
    public static void main(String [] argv){
        SpringApplication.run(SpringBootPostgresExample.class, argv);
    }
}


StudentController.java

I made changes to the StudentController.home() method so that upon opening the home page, all the existing Student records will displayed.

package com.firstfewlines.controller;

import com.firstfewlines.domain.Student;
import com.firstfewlines.service.StudentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.servlet.ModelAndView;
import java.text.DateFormat;
import java.text.SimpleDateFormat;


@Controller
@RequestMapping("/")
class StudentController {
    final static DateFormat df = new SimpleDateFormat("yyyy-MM-dd");

    @Autowired
    StudentService studentService;

    @RequestMapping(method = RequestMethod.GET)
    ModelAndView home() {
        ModelAndView modelAndView = new ModelAndView("student");
        modelAndView.addObject("students", studentService.getStudents());
        return modelAndView;
    }


    @RequestMapping(value = "student", method = RequestMethod.POST, consumes = MediaType.ALL_VALUE)
    @ResponseStatus(value = HttpStatus.OK)
    ModelAndView addStudent(@RequestParam Integer rollNo,
                            @RequestParam String name,
                            @RequestParam String dateOfBirth ) throws Exception {

        ModelAndView modelAndView = new ModelAndView("student");
        try {
            Student student = new Student();
            student.setRollNo(rollNo);
            student.setName(name);
            student.setDateOfBirth(df.parse(dateOfBirth));
            student = studentService.addStudent(student);
            modelAndView.addObject("message", "Student added with name: " + student.getName());
        }
        catch (Exception ex){
            modelAndView.addObject("message", "Failed to add student: " + ex.getMessage());
        }
        modelAndView.addObject("students", studentService.getStudents());
        return modelAndView;
    }
}


application.properties

It defines the application settings like database connection, server port etc. This example code snippet is for connecting with PostgreSQL database. For MySQL, Oracle, SQL Server or other database we just need to change the database url and the hibernate dialect appropriately.

Please refer to Complete list of Hibernate Dialects

You also need to make sure you have same database name, user/pwd. Otherwise edit this properties file to match your database.

server.port=8090

spring.datasource.url = jdbc:postgresql://127.0.0.1:5432/test
spring.datasource.username = test
spring.datasource.password = test123

# JPA/Hibernate properties
spring.jpa.show-sql = true

# Hibernate ddl auto (create, create-drop, update): with "update" the database
# schema will be automatically updated accordingly to java entities found in project
spring.jpa.hibernate.ddl-auto = update

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.PostgreSQLDialect
spring.jpa.hibernate.naming.strategy = org.hibernate.cfg.ImprovedNamingStrategy

For MySQL database, the datasource url and hibernate dialect should be like:
spring.datasource.url = jdbc:mysql://localhost:3306/test
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL57Dialect


How to run this Spring boot application?

  • Make sure you have latest PostgreSql database and pgAdmin installed in your system. I used Postgres 9.6 with pgAdmin4.
  • If you use other database, make sure you changed the datasource url and hibernate dialect appropriately.
  • Then make sure the database credential matches with configuration specified in application.properties
  • Make sure you have latest version of Gradle installed and PATH is configured.
  • Go to the directory where you unzipped the file i.e. where the build.gradle is there
  • Run gradle clean bootRun command
  • This should run the application with output similar to below
  • Now open your browser and point to http://localhost:8090/

Alternatively, you can use IntelliJ IDEa or Eclipse to import the Gradle project and run from there.


Download full source code:


References:


Hansaraj avatar
About Hansaraj
Hansaraj is a Software Engineer experienced in Java, Groovy, JavaScript, SQL, C#, C++
comments powered by Disqus