2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / Haroon's PlanetPostgreSQL3 / RESTful CRUD API using PostgreSQL and Spring Boot – Part one
RESTful CRUD API using PostgreSQL and Spring Boot – Part one
Muhammad Haroon

RESTful CRUD API using PostgreSQL and Spring Boot – Part one

June 8, 2020/1 Comment/in Haroon's PlanetPostgreSQL /by Muhammad Haroon

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.

Tags: java, PostgreSQL, Spring Boot
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
1 reply
  1. Narendra chinnu
    Narendra chinnu says:
    October 16, 2020 at 11:30 am

    Thanks for your information, you have given very useful and important information.

    https://nareshit.com/spring-online-training/

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
Failover slots for PostgreSQL Failover slots for PostgreSQL How to use AdaBoost Machine Learning model with 2UDA – PostgreSQL and Orange (Part 6) How to use AdaBoost Machine Learning model with 2UDA – PostgreSQL and Orange...
Scroll to top
×