Spring Boot Integration With PostgreSQL as a Maven Project

Last Updated : 09 Oct, 2022

PostgreSQL is a user-friendly versatile RDBMS. This article lets us see how to integrate Spring Data JPA with PostgreSQL. There are some conventions to be followed while using PostgreSQL. We will cover that also.

Working with PostgreSQL

We can easily create databases and tables in that. The below screenshot will help with how to connect to PostgreSQL via the command line

Now let us create a table named "geek_author" under "geeksforgeeks" database.

Note: During table creation, PostgreSQL expects _ as the separator between two words.

Sequence is necessary as we are using "GenerationType.SEQUENCE". Hence we need to create by using

CREATE SEQUENCE hibernate_sequence START 1;

Example Project

Project Structure:

This is the maven project. All dependencies need to be specified under pom.xml. As we are connecting PostgreSQL, this dependency is significant

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
</dependency>

As we are using spring data JPA, this dependency is essential

<dependency>
    <groupId>org.springframework.boot</groupId>
     <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

pom.xml

XML

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" 
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 
                             https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.gfg.postgresql</groupId>
    <artifactId>springboot-postgresql-sample</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>

    <name>springboot-postgresql-sample</name>
    <description>Demo project for Spring Boot</description>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.0</version>
        <relativePath />
    </parent>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
      
        <!--postgresql dependency -->
         <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
</project>

Let's start with the Model class. Namingwise let it be concurrent with the Postgres table

geek_author.java

Java

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity 
@Table(name = "geek_author")
public class geek_author {
    @Id
    @GeneratedValue(strategy=GenerationType.SEQUENCE)
    private Integer author_id;

    private String author_name;

    private String email_id;

    public Integer getAuthor_id() {
        return author_id;
    }

    public void setAuthor_id(Integer author_id) {
        this.author_id = author_id;
    }

    public String getAuthor_name() {
        return author_name;
    }

    public void setAuthor_name(String author_name) {
        this.author_name = author_name;
    }

    public String getEmail_id() {
        return email_id;
    }

    public void setEmail_id(String email_id) {
        this.email_id = email_id;
    }    
    
}

Let us write the repository class now

AuthorRepository.java

Java

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import com.gfg.postgresql.model.geek_author;

@Repository
public interface AuthorRepository extends JpaRepository<geek_author, Long>{

}

AuthorController.java

Java

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import com.gfg.postgresql.model.geek_author;
import com.gfg.postgresql.repository.AuthorRepository;

// This means that this 
// class is a Controller
@Controller    

// This means URL's start with /geek (after Application path)
@RequestMapping(path="/geek") 
public class AuthorController {
  
    // This means to get the bean called geekuserRepository
    // Which is auto-generated by Spring, we will use it
    // to handle the data
    @Autowired 
    private AuthorRepository authorRepository;

    // Map ONLY POST Requests
    @PostMapping(path="/addauthor") 
    public @ResponseBody String addAuthors (@RequestParam String author_name
            , @RequestParam String email_id) {
      
        // @ResponseBody means the returned String
        // is the response, not a view name
        // @RequestParam means it is a parameter
        // from the GET or POST request      
        geek_author geekAuthor = new geek_author();
        
        geekAuthor.setAuthor_name(author_name);
        geekAuthor.setEmail_id(email_id);
        authorRepository.save(geekAuthor);
        return "Details got Saved";
    }

    @GetMapping(path="/authors")
    public @ResponseBody Iterable<geek_author> getAllAuthors() {
        // This returns a JSON or 
        // XML with the Book
        return authorRepository.findAll();
    }
}

Application that can be started is coded as follows :

AuthorApplication.java

Java

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

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