RoadToWebDev Day#9 — Attaching Postgres Database to the Spring Boot App

In this article, we will see how to connect the database to our code. The project explained here is about creating and viewing a Song database containing the data entered by the user.

This article is in continuation of my previous article(link is given below), but none of the concepts are skipped. I have tried to explain all of it to a certain level.

Here I am using PostgreSQL that is a powerful, open-source object-relational database system.

As to provide a database to our code, we need to set up PostgreSQL on our computer. You can refer to the below-mentioned site for the same.

Now we need to connect the database to our code. For that, go to the application.properties of your specific project and append the following lines of code.

For now, we will work under the default schema that is “public schema”. As per your need, you can create a new schema and store the data under that.

spring.jpa.hibernate.ddl-auto will turn off the hibernate auto-creation of the tables from the entity objects. Generally, Hibernate runs it if there is an Entity defined. But we will be using a native SQL query with JdbcTemplate, hence, we can turn this off as we will not be creating an Entity.

spring.datasource.url URL of the Postgres DB. It can be a remote DB as well.

spring.datasource.username username for the database.

spring.datasource.password password for the database.

The next step will be adding the required dependencies to the pom.xml file as below.

The dependencies required for database connection are:

spring-boot-starter-jdbc artifact will give all the spring jdbc related jars

org.postgresql.postgresql will have the dependency of Postgres jdbc driver in runtime.

JDBC stands for Java Database Connectivity, which is a standard Java API for database-independent connectivity between the Java programming language and a wide range of databases.

Before getting further, let us create the table for our database. Just go to the console, and write the following query:

CREATE TABLE songs(songName varchar(100) NOT NULL , songAlbum varchar(100) NOT NULL ,songDuration varchar(100) NOT NULL, songKey varchar(100) NOT NULL,songDanceability varchar(100) NOT NULL,songAcousticness varchar(100) NOT NULL);

Now, looking into the code part:

Here is the SongRepository class which is basically a way to interact with the database of songs. We will invoke operations of this repository via GET and POST methods.

Spring JdbcTemplate is a mechanism to connect to the database and execute SQL queries.

  • addSong( ) Method:

jdbcTemplate.update( ) issues a single SQL update operation via a prepared statement, binding the given arguments.

A prepared statement is a way to execute the SQL query using JDBC API.

So the function of this method is to append an object of Song entered by the user to the database.

The SQL string is prefixed with the final keyword, in order to prevent bugs. Because a change in SQL query can lead to not getting the required results.

  • addList( ) Method:

Here we are using the jdbcTemplate.batchUpdate( ) function to insert a batch of data at a time to the database. Sending a batch of updates to the database is efficient than sending them one by one in a loop, waiting for each one to finish. There is lesser network overhead involved while sending the data batch-wise.

BatchPreparedStatementSetter( ) interface sets values on a PreparedStatement provided by the JdbcTemplate class, for each of a number of updates in a batch using the same SQL. This interface takes two parameters methods mentioned below. And as BatchPreparedStatementSetter( ) is an interface, it will be mandatory to define both the methods.

(i) setValues( ) : It takes in two parameters the PreparedStatement to invoke setter methods on, and int i to index the prepared statements in the batch that needs to be appended.

ps.setString( ) sets the designated parameter to the given Java String value. The driver converts this to an SQL VARCHAR value when it sends it to the database.

(ii)getBatchSize( ) : There may come a time when data in the size of thousands is to be appended in the database. So it will be wise to send the data in batches. So this function needs returns that batchSize. But here, I have kept it equal to the size of the entered list of data, which means all data will be pushed in one go.

  • getList( ) Method :

jdbcTemplate.query( ) executes a given static SQL query, mapping each row to a result object via a RowMapper. An example mapper is shown below:

The ResultSet interface provides getter methods for retrieving column values from the current row. Values can be retrieved using either the index number of the column or the name of the column.

For the getter methods, a JDBC driver attempts to convert the underlying data to the Java type specified in the getter method and returns a suitable Java value.

Now It’s time to make a controller class that would help us to use the SongRepository as per the need.

Three different APIs for different operations are as follows:

  1. GET Method for getting the list of songs from the database.(path = “/song”)
  2. POST Method to append-only one object of the class Song to the database. (path = “/song”)
  3. POST Method to append an entire list of songs to the database. (path =“/songs”)

Run the program.

Once the server starts running, we can send the requests through the browser or Postman.

Firstly, let us check if the code is working correctly or not.

Pass the GET request and set the path as http://localhost:8080/song/, and Send!

We got an empty list, as we have not entered any data in the database. So, let us start appending data.

For entering only one Song object: Select POST Method-> set the path as http://localhost:8080/song/,-> go to Body-> raw; and enter the required data and Send!

Let us try appending a whole list of Song objects. For that:

Set the path to http://localhost:8080/songs/, set the specifications for passing POST request (as mentioned above), and Send.

Now, when you are satisfied with adding enough data, let us go and check the database table.

In IntelliJ go to console and write the query:

select * from songs;

Success! All the data that was entered by the user is appended in the database correctly!

Hope you understood the article! :)

References:

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store