1. Overview
PostgreSQL is a general purpose and object-relational database management system, the most advanced open source database system. In this blog, we will be developing a Spring-Boot application with Spring Data JPA to show how to do CRUD operations with PostgreSQL.
2. Setup 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 instruction 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 would 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 blog. You can download pgAdmin tool from its official website.
3. 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.
3.1. Pre-requisites
- 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)
3.2. 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.RELEASEfor this exercise.3.3. Configuration
Spring-Data uses spring.datasource properties to locate the postgres instance and connect it. I have used
spring.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.3.4. Repository & 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.
To create a table in PostgreSQL, I have created an Entity class name Customer. It maps to table "customer"
3.5. REST Controller
We have created
CustomerController to expose the CRUD operations through REST API. It has methods like bulkcreate(), create(), findAll(), search(),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.
3.6. Test Application
The application will be running on
http://localhost:9090/
Use
http://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. Source code link is provided at the end of the blog.
4. Summary
To summarize, PostgreSQL is picking up very fast as an RDBMS option 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 which 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 examples above can be found over on GitHub.
The article provides a practical walkthrough of connecting a Spring Boot application with PostgreSQL using Spring Data JPA and Hibernate. The progression from PostgreSQL and pgAdmin setup to Gradle dependencies and datasource configuration makes the CRUD integration process easy to follow, while the warning about using ddl-auto=create-drop only for the example is an important implementation detail.
ReplyDeleteThe combination of JPA-based data access and PostgreSQL is especially useful for understanding how persistence fits into a Spring Boot application. These concepts are directly applicable when building applications through a Spring Boot ReactJS Full Stack Developer Course, particularly when backend services need reliable database connectivity and CRUD operations.
It is also useful to see the database layer discussed alongside the Spring Boot web starter rather than treating PostgreSQL integration in isolation. The same persistence and configuration concepts can support applications developed with a Spring Boot NextJS Full Stack Developer Course, where the Spring Boot backend handles data operations for a modern web interface.
ReplyDelete