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.
No comments: