Spring Boot and PostgreSQL

Overview

PostgreSQL is a general-purpose and object-relational database management system, the most advanced open source database system. In this article, we will be developing a Spring-Boot application with Spring Data JPA to show how to do CRUD operations with PostgreSQL.

Set up PostgreSQL and pgAdmin Tool

To Download PostgreSQL, you can go to PostgreSQL official website. You can select the OS of your choice and download it. Follow the instructions as per the website. I have downloaded the windows version and installed it by running .exe file. I have used 9.5 version as 9.6 was giving error while installing it on windows.

Once you install it, it would be running on localhost:5432 by default unless you changed the port while installing. Now, you will need a client tool to access the database. There are many tools available like psql, Tora, pgAdmin, and others. I am using pgAdmin III for this article. You can download pgAdmin tool from its official website.

Develop Spring-Boot Application to Integrate With PostgreSQL

Now, we will be creating a Spring-Boot application which will interact with PostgreSQL doing CRUD operations. I will be using Spring data JPA with hibernate for the same.

Prerequisites

• PostgreSQL version 9.5 (check above for steps to download)
• pgAdmin III Client Tool (check above for steps to download)
• Gradle
• IDE like Eclipse, VSD (prefer to have VSD as it is very lightweight. I like it more compared to Eclipse)

Gradle Dependency

This project needs a standard spring-boot starter along with spring-boot-data-jpa and postgresql. I am using spring-boot version springBootVersion = '2.1.1.RELEASE' for this exercise.

dependencies {
implementation('org.springframework.boot:spring-boot-starter-data-jpa')
implementation('org.springframework.boot:spring-boot-starter-web')
implementation('org.postgresql:postgresql')
testImplementation('org.springframework.boot:spring-boot-starter-test')
}

Configuration

Spring-Data uses spring.datasource properties to locate the postgres instance and connect it. I have usedspring.jpa.hibernate.ddl-auto=create-drop for this example but it should not be used for production like application. I am using it just to clean up the data once the application is stopped. You would also notice the entry spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true This entry is put just to avoid a warning message in the logs when you start the spring-boot application. This bug is from hibernate which tries to retrieve some metadata from postgresql db and failed to find that and logs as a warning. It doesn't cause any issue though. Also, please ensure to update the database name in spring.datasource.url property if its different than what I used.

server.port=9090
spring.jpa.database=POSTGRESQL
spring.datasource.platform=postgres
spring.datasource.url=jdbc:postgresql://localhost:5432/postgres
spring.datasource.username=postgres
spring.datasource.password=root
spring.jpa.show-sql=true
spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true

Repository and Domain Entity

We have created CustomerRepository, which extendsJpaRepository. Ideally, you could extend CRUDRepositorydirectly, as we are not going to use much of JPA features here in this exercise. I have defined a few methods as well as.

@Repository
public interface CustomerRepository extends JpaRepository<Customer, Long>{
List<Customer> findByFirstName(String FirstName);
List<Customer> findAll();
}

To create a table in PostgreSQL, I have created an Entity class name Customer. It maps to table "customer"

@Entity
@Table(name = "customer")
public class Customer implements Serializable {

private static final long serialVersionUID = -2343243243242432341L;
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private long id;

@Column(name = "firstname")
private String firstName;

@Column(name = "lastname")
private String lastName;

//Setters, getters and constructors
}

REST Controller

We have createdCustomerController to expose the CRUD operations through REST API. It has methods like bulkcreate(), create(), findAll(), search(), and fetchDataByFirstName().

• bulkcreate() creates several customers without passing any data
• create() creates single customer by passing customer data as JSON
• findAll() search for all customers and return as JSON.
• seach() finds a customer by its id.
• fetchDataByFirstName() finds the customer list based on the first name.

@RestController
public class CustomerController {
@Autowired
CustomerRepository repository;

  @GetMapping("/bulkcreate")
public String bulkcreate(){
// save a single Customer
repository.save(new Customer("Rajesh", "Bhojwani"));

// save a list of Customers
        repository.saveAll(Arrays.asList(new Customer("Salim", "Khan")
                       , new Customer("Rajesh", "Parihar")
                       , new Customer("Rahul", "Dravid")
                       , new Customer("Dharmendra", "Bhojwani")));

return "Customers are created";
}
@PostMapping("/create")
public String create(@RequestBody CustomerUI customer){
// save a single Customer
repository.save(new Customer(customer.getFirstName(), customer.getLastName()));

return "Customer is created";
}
@GetMapping("/findall")
public List<CustomerUI> findAll(){

List<Customer> customers = repository.findAll();
List<CustomerUI> customerUI = new ArrayList<>();

for (Customer customer : customers) {
customerUI.add(new CustomerUI(customer.getFirstName(),customer.getLastName()));
}

return customerUI;
}

@RequestMapping("/search/{id}")
public String search(@PathVariable long id){
String customer = "";
customer = repository.findById(id).toString();
return customer;
}

@RequestMapping("/searchbyfirstname/{firstname}")
public List<CustomerUI> fetchDataByFirstName(@PathVariable String firstname){

List<Customer> customers = repository.findByFirstName(firstname);
List<CustomerUI> customerUI = new ArrayList<>();
for (Customer customer : customers) {
customerUI.add(new CustomerUI(customer.getFirstName(),customer.getLastName()));
}
return customerUI;
}
}

Test Application

The application will be running on http://localhost:9090/

Usehttp://localhost:9090/bulkcreate  to create multiple customers in one go. Then, launch the pgAdmin client and run theSelect * from customer, you will see the results like below.

Similarly, other APIs also can be tested. To check how to test all the APIs, you can go through README.md file in code. The source code link is provided at the end of the article.

Summary

To summarize, PostgreSQL is picking up very fast as an RDBMS option and is getting the advantage of being open source technology. Spring provides an easy way of interacting with PostgreSQL through spring data jpa. However, please keep in mind that some of the latest features might not be accessible through hibernate as it's not upgraded. For example, jsonb data type. There is no data type in hibernate that supports that, but alternate options are available like creating its own UserType. Also, you can use native SQL as well to use the same feature.

As always, the code of all the examples above can be found over on GitHub.