Enter the terms you wish to search for.

RESTful CRUD API using PostgreSQL and Spring Boot - Part one

June 08, 2020

Overview

Spring Boot is a framework that is used to build stand-alone Java applications following the Spring framework but with less hassle and minimal configuration. Learning by practical examples is always great; therefore, this article will walk you through the creation of a RESTful CRUD (create-read-update-delete) application using Spring Boot and its connectivity with PostgreSQL. Though Spring Boot presents a complete application development framework with backend as well as the front-end, the greater emphasis in this article will be on a RESTful API, database connectivity and its injection and integration with other components in the application. 

Pre-requisites

In this article, you will:

  1. Set up Java development environment with Spring Boot. 
  2. Create Models and RESTful Controllers for your Spring Boot application.
  3. Set up PostgreSQL connections for JPA and JDBC connections (which, you will use in next blog).

You can use either Visual Studio Code, IntelliJ Idea or your favorite Java IDE, I will be using Visual Studio Code throughout for development. Your IDE will provide a suitable Spring Initializr to set up Java project, or you can use the online Spring Initializr and import the project in your environment. For Visual Studio Code, install and set up Java extension pack.

In the next section, I assume you have a project and IDE set up.

Getting Started

Java Spring Boot applications support both, basic Controllers and RESTful Controllers, to avoid complexity of templates and views in Spring, I will use RESTful controllers and wire them to Model classes. You need to know two key points:

  1. RESTful classes generate JSON/XML data based for our data (Model) in the database.
  2. They work directly with HTTP GET, POST and PUT methods.

In order to create the Model class (like ours) and to set up project for development, open your pom.xml file, and add the following dependencies:

<!-- jpa, crud repository -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

<!-- PostgreSQL -->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
</dependency>

You can either manage these dependencies using a GUI tool, if provided, or add these under <dependency> node in the XML. These dependencies will enable the PostgreSQL-related and JPA-supported objects in your Java Spring Boot project. Refresh the Maven package to synchronize the dependencies.

We start by creating a RESTful controller, PeopleController and add basic HTTP route to this.

Model and Controllers

You will create one Model class for your application: Person.java. For simplicity, create it under models sub package. Add this code:

 

package com.demos.crud.data.models;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity

@Table(name = "`People`")

public class Person {

    @Id

    @GeneratedValue(strategy = GenerationType.AUTO)

    @Column(name = "id")

    public Long id;


    @Column(name = "name")

    public String name;


    @Column(name = "role")

    public String role;


    public Person() {}


    public Person(long id, String name, String role) {

        this.id = id;

        this.name = name;

        this.role = role;

    }

}

This Model class has an ID field, a name and a role attribute. The annotations on the type and its fields are from JPA (see Database Connectivity section below). How they work, and help us with model/database tables, we will see in the next blog. Our RESTful API will be written around this Model class, and we will:

  1. Create new people in the database.
  2. Query existing people in the database.
  3. Update/delete the people from the database.

Before we create the repository and the linking with the database, let us quickly add the RESTful controller, “PeopleApiController” in the apis sub package:

 

package com.demos.crud.controllers.api;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController()

@RequestMapping("/api/people")

public class PeopleApiController {

    // Routes here.
}

This RESTful controller will work as a template for us to inject the PostgreSQL repository connection as well as the HTTP routes. Run the application and make sure you can have the application started on “localhost:8080”. You can ignore the warning page as we will add the RESTful routes in next part. 

I assume that you are aware of the routing/GET mapping concepts, if not then check this tutorial on Spring website. 

Database Connectivity

Integration of your Spring Boot application with database is easy, and uses the drivers and libraries offered by Spring Boot. Our focus in the article will be to show how to connect and query database using two primary approaches: Java Database Connectivity or JDBC and Java Persistence API or JPA. You might have noticed that we will use JPA (check the dependencies above) and create the database layer for our RESTful API. 

Install and setup PostgreSQL on your machine using our open source GUI-based installer. Once you are done, create a new table in the database and name it as “People”. You can use this SQL create script to do that (note that I created a schema, “techwriting”, you can use your own default schema name here):

-- Table: techwriting.People

-- DROP TABLE techwriting."People";

CREATE TABLE techwriting."People"

(

    id integer NOT NULL,

    name text COLLATE pg_catalog."default" NOT NULL,

    role text COLLATE pg_catalog."default",

    CONSTRAINT "Person_pkey" PRIMARY KEY (id)

)
TABLESPACE pg_default;

ALTER TABLE techwriting."People"

    OWNER to postgres;

Now, you should have:

  1. PostgreSQL database with a table, “People”.
  2. A Spring Boot application with a Person model and a RESTful API controller; PeopleApiController.java.

JPA vs JDBC

The most important part to consider when it comes to the data connectivity is whether you want to use JPA or JDBC. These two technologies are equally important when it comes to database layers in Java programming environments. A one-liner for them would be:

  1. JPA is more ORM-oriented and takes care of everything that you need to use when writing the database layer.
  2. JDBC is more low-level layer for the database connectivity. You can use JDBC to write custom queries that might not be possible out-of-box with the JPA services.

Both technologies are interesting and offer an abstraction on top of our database drivers, engines, data repositories and CRUD operations. We will look at both these technologies in the next blog.

Good Practices 

We are covering the best practices in this post so when writing the code in next post, you will know why certain coding practices were used.

I’m sharing some good practices working with PostgreSQL here, which are most likely to work in most of the solutions when you are writing your backend frameworks for applications.

Preventing SQL Injections  

Though a seamless improvement has made in the modern databases from the perspective of designs and protocol, SQL injections bring along huge security risks. Like many other databases, PostgreSQL has the features of protecting your database from SQL injections in multiple ways, whereby the two most common ones from Java developers’ point-of-view are:

  1. Stored procedures.
  2. Server-side prepared statements. 

However, Stored Procedures are not entirely resistant to SQL Injections. As the creation of dynamic SQL makes SPs vulnerable to SQL injections; here is an example scenario reference. Therefore, the good practice is to write your queries using Extended Queries, which are widely supported by PostgreSQL—as you will see, stored procedures also make heavy use of SQL parameters.

Another approach to do this is using parameters with SQL queries, this approach wraps your parameters and prevents them from polluting the dynamic SQL query. We will cover this in the next blog. 

Transactions in Postgres  

A single unit of work consisting of one or many SQL commands is called a transaction. Postgres supports ACID (atomic- consistent-isolated-durable) transactions to ensure, “something either happens completely or not at all (all or nothing-at-all operations)”. This way, we make sure that there will not be any unexpected bugs to welcome from our production. A Postgres transaction kills the hassle of manual reverting of the incomplete actions and queries by supporting auto-commit or rolling-back.   

In PostgreSQL, each query you write is wrapped implicitly in a transaction and then executed. In Postgres, you surround the SQL commands with BEGIN and COMMIT to create a transaction block, like:

BEGIN;

   -- Your SQL statements here.

COMMIT;

When this block is executed (either in a procedure, or raw query), all of changes would be made or none of them would be. Consult the official documentation for PostgreSQL transactions. Spring Boot provides support for easier transaction management out of box to aid with this, so you do not have to work on these elements; check this blog post for more on that.

Connection Pooling  

Connection Pooling is apparently a misunderstood concept amongst many; however, with PostgreSQL, it is an important concept to work with as it can greatly improve the performance of your solution. Instead of establishing a new connection every time, Connection Pooling provides a pool of established connections that are ready to be used and reused for requests.

Connection Pooling is based on connection strings. To re-use a connection from its pool, it is essential to have the same connection string in use, otherwise a new connection request will be established. In a web application this does not create a problem if you have provided the values in the JPA/JDBC configs or if you are using a connection pooler. Hikari is one of such open source services that you can use.

Most of the modern frameworks support Connection Pooling; this ensures that as soon as your application server starts, there will be a maintained pool of connections. As we have used Java (Spring Boot framework) as the client application, it gives multiple ways of supporting Connection Pooling. Though going through their details in not subjected for this article. However, it is good to know these options for their appropriate use based on your requirement-set.

  1. Postgres provided the JDBC interface supports standard connection pooling features. This comes in an optional add-on package known as JDBC 2.0 Standard Extension.  
  2. Next, we have auto-configured connection pools data sources supported by Spring Boot. The application will look for the HikariCP in the configured classpath by default. If that is not found, then the framework will pick Tomcat JDBC Connection Pool.

However, if any of the above are not available, Spring Boot will look for Apache Commons DBCP2.

Conclusion

In this article, you learned Spring Boot framework and created a basic Spring Boot application. You created a model and a controller for this sample application. You also set up PostgreSQL database and created a table to store the records.

You learned the primary difference between JDBC and JPA drivers for Spring Boot, now in the next part (coming next week) you will create the database layer for application and test your API to perform CRUD operations with PostgreSQL database.

Share this

More Blogs