Spring Boot + PostgreSQL + Maven: CRUD example
Tags: spring, postgres, java, webdev
Published: 2021-05-13 02:45:25

In this tutorial, we're gonna build a Spring Boot + PostgreSQL example with Maven that uses Spring Data JPA/Hibernate to interact with PostgreSQL database and export Rest CRUD API. You'll know:
<ul>
    	<li>How to configure Spring Data, JPA, Hibernate to work with PostgreSQL Database</li>
    	<li>How to define Data Models and Repository interfaces</li>
	<li>Way to create Spring Rest Controller to process HTTP requests</li>
   	<li>Way to use Spring Data JPA to interact with PostgreSQL Database</li>
</ul>

Full Article: [Spring Boot + PostgreSQL + Maven: CRUD example](https://bezkoder.com/spring-boot-postgresql-example/)

<h2>Overview of Spring Boot, PostgreSQL example with Maven</h2>
We will build a Spring Boot + PostgreSQL + Rest CRUD API for a Tutorial application in that:
<ul>
	<li>Each Tutotial has id, title, description, published status.</li>
	<li>Apis help to create, retrieve, update, delete Tutorials.</li>
	<li>Apis also support custom finder methods such as find by published status or by title.</li>
</ul>

These are APIs that we need to provide:
<div class="table-responsive">
<table class="table table-hover  table table-hover" width="100%">
<thead>
<tr>
<th>Methods</th>
<th>Urls</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
<tr>
<td>POST</td>
<td>/api/tutorials</td>
<td>create new Tutorial</td>
</tr>
<tr>
<td>GET</td>
<td>/api/tutorials</td>
<td>retrieve all Tutorials</td>
</tr>
<tr>
<td>GET</td>
<td>/api/tutorials/:id</td>
<td>retrieve a Tutorial by <code>:id</code></td>
</tr>
<tr>
<td>PUT</td>
<td>/api/tutorials/:id</td>
<td>update a Tutorial by <code>:id</code></td>
</tr>
<tr>
<td>DELETE</td>
<td>/api/tutorials/:id</td>
<td>delete a Tutorial by <code>:id</code></td>
</tr>
<tr>
<td>DELETE</td>
<td>/api/tutorials</td>
<td>delete all Tutorials</td>
</tr>
<tr>
<td>GET</td>
<td>/api/tutorials/published</td>
<td>find all published Tutorials</td>
</tr>
<tr>
<td>GET</td>
<td>/api/tutorials?title=[keyword]</td>
<td>find all Tutorials which title contains <code>keyword</code></td>
</tr>
</tbody>
</table>
</div>
- We make CRUD operations & finder methods with Spring Data JPA's <code>JpaRepository</code>.
- The database will be PostgreSQL by configuring project dependency & datasource.

<h2>Technology</h2>
<ul>
    	<li>Java 17 / 11 / 8</li>
    	<li>Spring Boot 3 / 2 (with Spring Web MVC, Spring Data JPA)</li>
    	<li>PostgreSQL</li>
    	<li>Maven</li>
</ul>

<h2>Maven Project Structure</h2>
![spring-boot-postgresql-maven-example-crud-project-structure](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5liqbzrq967scmflp2s8.png)

Let me explain it briefly.

– <code>Tutorial</code> data model class corresponds to entity and table <em>tutorials</em>.
– <code>TutorialRepository</code> is an interface that extends <a href="https://docs.spring.io/spring-data/jpa/docs/current/api/org/springframework/data/jpa/repository/JpaRepository.html">JpaRepository</a> for CRUD methods and custom finder methods. It will be autowired in <code>TutorialController</code>.
– <code>TutorialController</code> is a <a href="https://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/web/bind/annotation/RestController.html">RestController</a> which has request mapping methods for RESTful requests such as: <em>getAllTutorials</em>, <em>createTutorial</em>, <em>updateTutorial</em>, <em>deleteTutorial</em>, <em>findByPublished</em>...
– Configuration for Spring Datasource, JPA & Hibernate in <strong>application.properties</strong>.
– <strong>pom.xml</strong> contains dependencies for Spring Boot and PostgreSQL.

<h2>Run the Application</h2>
Run Spring Boot + PostgreSQL application with Maven: <code>mvn spring-boot:run</code>.

<strong><em>tutorials</em></strong> table will be automatically generated in Database.
If you check PostgreSQL for example, you can see things like this:

```
testdb=# \d tutorials
             Table "public.tutorials"
   Column    |          Type          | Modifiers
-------------+------------------------+-----------
 id          | bigint                 | not null
 description | character varying(255) |
 published   | boolean                |
 title       | character varying(255) |
Indexes:
    "tutorials_pkey" PRIMARY KEY, btree (id)
```

Create some Tutorials:

![spring-boot-postgresql-maven-example-crud-create-tutorial](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/so5wyp87n07dvyv0r3ui.png)


```
testdb=# SELECT * FROM tutorials;
 id |    description    | published |            title
----+-------------------+-----------+------------------------------
  1 | Tut#1 Description | f         | Spring Boot Tut#1
  2 | Tut#2 Description | f         | PostgreSQL Tut#2
  3 | Tut#3 Description | f         | Spring Data JPA Tut#3
  4 | Tut#4 Description | f         | Maven Tut#4
  5 | Tut#5 Description | f         | Spring Boot PostgreSQL Tut#5
(5 rows)
```

Update some Tutorials:

![spring-boot-postgresql-maven-example-crud-update-tutorial](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/us7pgwj918s0gpth7v7x.png)

```
testdb=# SELECT * FROM tutorials;
 id |    description    | published |            title
----+-------------------+-----------+------------------------------
  3 | Tut#3 Description | f         | Spring Data JPA Tut#3
  5 | Tut#5 Description | f         | Spring Boot PostgreSQL Tut#5
  2 | Desc for Tut#2    | t         | PostgreSQL DB Tut#2
  4 | Desc for Tut#4    | t         | Maven Tut#4
  1 | Desc for Tut#1    | t         | Spring Boot Tut#1
(5 rows)
```

Retrieve all Tutorials:

![spring-boot-postgresql-maven-example-crud-retrieve-tutorial](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/j3jnbsl4byerd169t344.png)

Get a Tutorial by Id:

![spring-boot-postgresql-maven-example-crud-retrieve-one-tutorial](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ukoee36t85op5d5tf40r.png)

Find all <em><strong>published</strong></em> Tutorials:

![spring-boot-postgresql-maven-example-crud-search-field](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zizqwijhv2zlihpc38js.png)

Find all Tutorials which title contains 'ring':

![spring-boot-postgresql-maven-example-crud-search-title](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cn18ngbiesx721788er6.png)

Delete a Tutorial:

![spring-boot-postgresql-maven-example-crud-delete-tutorial](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/s84pt7hhkw3cmfpiqc45.png)

```
testdb=# SELECT * FROM tutorials;
 id |    description    | published |            title
----+-------------------+-----------+------------------------------
  3 | Tut#3 Description | f         | Spring Data JPA Tut#3
  5 | Tut#5 Description | f         | Spring Boot PostgreSQL Tut#5
  2 | Desc for Tut#2    | t         | PostgreSQL DB Tut#2
  1 | Desc for Tut#1    | t         | Spring Boot Tut#1
(4 rows)
```

Delete all Tutorials:

![spring-boot-postgresql-maven-example-crud-delete-all](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/aoz03xvj61bg5i06zfqa.png)

```
testdb=# SELECT * FROM tutorials;
 id | description | published | title
----+-------------+-----------+-------
(0 rows)
```

<h2>Source code</h2>
For step by step instruction and Github, please visit:
Full Article: [Spring Boot + PostgreSQL + Maven: CRUD example](https://bezkoder.com/spring-boot-postgresql-example/)

<h2>Further Reading</h2>
> [Spring Boot One To Many example with Spring JPA, Hibernate](https://www.bezkoder.com/jpa-one-to-many/)

If you want to add Pagination to this Spring project, you can find the instruction at:
<a href="https://bezkoder.com/spring-boot-pagination-filter-jpa-pageable/">Spring Boot Pagination & Filter example | Spring JPA, Pageable</a>

To sort/order by multiple fields:
<a href="https://bezkoder.com/spring-data-sort-multiple-columns/">Spring Data JPA Sort/Order by multiple Columns | Spring Boot</a>

Handle Exception for this Rest APIs is necessary:
- <a href="https://bezkoder.com/spring-boot-controlleradvice-exceptionhandler/">Spring Boot @ControllerAdvice & @ExceptionHandler example</a>
- <a href="https://bezkoder.com/spring-boot-restcontrolleradvice/">@RestControllerAdvice example in Spring Boot</a>

Or way to write Unit Test for the JPA Repository:
<a href="https://bezkoder.com/spring-boot-unit-test-jpa-repo-datajpatest/">Spring Boot Unit Test for JPA Repositiory with @DataJpaTest</a>

Security:
<a href="https://bezkoder.com/spring-boot-security-postgresql-jwt-authentication/">Spring Boot, Spring Security, PostgreSQL: JWT Authentication example</a>

You can also know:
- how to deploy this Spring Boot App on AWS (for free) with <a href="https://bezkoder.com/deploy-spring-boot-aws-eb/">this tutorial</a>.
- way to upload an Excel file and store the data in MySQL database with <a href="https://bezkoder.com/spring-boot-upload-excel-file-database/">this post</a>
- upload CSV file and store the data in MySQL with <a href="https://bezkoder.com/spring-boot-upload-csv-file/">this post</a>.

Fullstack examples:
- <a href="https://bezkoder.com/spring-boot-vue-js-postgresql/">Vue.js + Spring Boot + PostgreSQL example</a>
- <a href="https://bezkoder.com/angular-spring-boot-postgresql/">Angular 8 + Spring Boot + PostgreSQL example</a>
- <a href="https://bezkoder.com/angular-10-spring-boot-postgresql/">Angular 10 + Spring Boot + PostgreSQL example</a>
- <a href="https://bezkoder.com/angular-11-spring-boot-postgresql/">Angular 11 + Spring Boot + PostgreSQL example</a>
- <a href="https://bezkoder.com/spring-boot-react-postgresql/">React + Spring Boot + PostgreSQL example</a>