Hello everyone,


In the last post, we have seen how to create an email scheduling application in spring boot with quartz scheduler and mysql as database. We created CRUD(Create, Read, Update and Delete) operations on scheduling functionality.  


We also created one time schedules with Simple triggers.  


In this post, we are going to see how to do the implement the same functionality with another relational database named PostgreSQL as called as Postgres.


Please feel free to explore the official docs to understand about it. 


Official documentation  - https://www.postgresql.org/


In this post, we are going to use the same codebase used in the last post  and integrate PostgreSQL as the database.


Please explore it to get an overview about the code base and quartz library.





Postgres Installation 



We are going to use docker. For installation of docker, refer https://docs.docker.com/engine/install/


After docker is installed. We will pull Postgres image and run it as a container. 



docker pull postgres


Type below command to verify the image is pulled.



docker images


Once the image is pulled successfully from docker registry, execute below command on terminal. This will run Postgres as a container exposing its default running post 5432 to host and it will run in detached mode i.e in background with the user and password as root. 


Feel free to set custom username and password.  I have named the container as postgres.



docker run -p 5432:5432 -e POSTGRES_PASSWORD=root  -e POSTGRES_USER=root --name postgres -d postgres

 




Database and tables Creation


Now logon to the docker container by executing the below command. You will land on the bash shell of the container. 



docker exec -it <containerId> bash

 

In the psql console, type below two commands 



create database quartz_scheduler;


grant all privileges on database quartz_scheduler to root;


You should be able to see the created database like below. 






Now, open another terminal and l copy the table creation sql script for postgres to docker container. 



cd quartz-scheduler/scripts/



docker cp postgres_tables.sql <containerId>:/

Once the above commands are executed, go the bash shell of the container, by executing the below command on the terminal two or by going to sql console on the terminal one and exiting the psql console by typing \q command



docker exec -it <containerId> bash

When you execute command ls, you should be able to see the sql file - postgres_tables.sql


Now let’s import the tables from the sql script copied from host by executing the below command.



psql -d quartz_scheduler -U root -f postgres_tables.sql

The above table will connect to database quartz_scheduler as user root and execute the sql commands.  


You should be able to see the tables created like below.




What’s next? 


Nothing. Run the application by executing the below command. 


 mvn spring-boot:run -Dspring-boot.run.arguments="--spring.profiles.active=local-postgres --spring.mail.password=<YOUR_SMTP_PASSWORD>"                         


Let’s recap a bit on what we have done so far. 

  • Added postgres dependency
  • Created the spring profile for postgres
  • Created the tables with the help of script. 


No code changes. Why? 





Why no code changes?


It is because of spring data JPA. The DAO layer  or persistence layer consists of lot of boiler plate code and simplifies it by making it possible to remove all the implementations artifacts. We define only an interface of the DAO and extending it with JpaRepository. The Spring data programming model automatically create an implementation for our Dao interface. 


In Standard Dao, we get CRUD operations. For custom queries, refer - https://www.baeldung.com/the-persistence-layer-with-spring-data-jpa


This is the reason, we don’t have any code changes in any layer. 





Testing the application


Our application is up now. Like the previous post with MySQL database. Let’s create the mail schedule and test it. 


Go to the swagger - http://localhost:8080/swagger-ui/index.html




Creating the mail schedule

  • Request


  • Response


  • Mail received at the specified time





List the mail schedule


This will list all the mail schedules scheduled.

  • Request


  • Response





Delete the schedule


We can only delete the schedules not fired as all schedules created are one time schedules. Once fired, they are marked deleted. 

  • Request



  • Response





Postman Collection


You can find the postman collection of all APIs here - Postman collection. Please find the create and update APIs JSON request body below






Keep Experimenting 🔎 

Keep Learning 🚀

Post a Comment

Previous Post Next Post