Overview
This article is an extended version atop of the previous article which was a kickstart for building an application using Spring Boot and PostgreSQL. There is no internal feature supported by Java which offers mapping between class objects and database tables; therefore, we use Object Relational Model (ORM). JPA is an abstraction above JDBC and works as an ORM to map Java Objects with the entities in database using metadata. Querying database for all the user operations is another task of JPA for which it uses the concept of “repository”. Consequently, with JPA the development of your product gets at least an order of magnitude faster as it comes up with auto-configuration option which is a goal of Spring Boot to develop Spring applications.
Note: You should read the documentation for Spring Boot objects using the package and type name provided in the code samples below.
Managing Entities With JPA
As mentioned, entities are mapped with the tables in the database where every entity instance holds up the data of a row in the table. Therefore, following this convention we will use @Entity annotation to map and store data of our POJO (Plain Old Java Object) to a table in the database.
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;
}
@Override
public String toString() {
StringBuilder builder = new StringBuilder();
builder.append(String.valueOf(id));
builder.append(", ");
builder.append(name);
builder.append(", ");
builder.append(role);
return builder.toString();
}
}The @Entity annotation should be defined at the class level as it will make JPA aware of this class as a database entity; and help create tables and schema in database upon startup. Like other ORMs, JPA also requires uniquely identified primary key which we have set to automatic generation (like 1, 2, 3… and so on). For different data types, the JPA provider will generate the identities in a different way.
Performing CRUD on Entities
Creating the repository
CRUD in Spring is available with CrudRepository type that offers CRUD implementation over the underlying database structure.
You can create a new CrudRepository by linking the entity type and the type of primary key using Java generics as:
package com.demos.crud.data.repositories;
import com.demos.crud.data.models.Person;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;
@Repository
public interface PersonRepository extends CrudRepository<Person, Long> {
}That is the entire JPA repository code you need to write to provide an ORM support to your applications.
Connection settings for repository
Spring allows you to configure application settings from a file called application.properties. You can configure the connection string and connection pooling (see the part 1 for this post) for the PostgreSQL.
## default connection pool
spring.datasource.hikari.connectionTimeout=20000
spring.datasource.hikari.maximumPoolSize=5
spring.jpa.generate-ddl=false
## PostgreSQL
spring.datasource.url=jdbc:postgresql://172.17.0.2:5432/postgres
spring.datasource.username=postgres
spring.datasource.password=mysecretpassword
# spring.jpa.properties.hibernate.default_schema=techwriting
spring.datasource.hikari.schema=techwriting
#drop n create table again, good for testing, comment this in production
spring.jpa.hibernate.ddl-auto=createWe configure the connection pool to allow up to 5 connections, and the PostgreSQL connection string. Spring Boot JPA will read these settings and create a PostgreSQL ORM. Read more about application.properties here.
Creating a service
Although this step is not necessary, but it is a security practice to create a service around your native ORM and PostgreSQL connection to prevent unvalidated access to your database engine. We do that by creating an interface:
package com.demos.crud.data.services;
import java.util.List;
import com.demos.crud.data.models.Person;
public interface PeopleService {
List<Person> findAllPeople();
Person findById(long id);
Person insert(Person p);
boolean delete(long id);
boolean update(Person p);
}This interface has one job: expose the functions of the repository as they are needed, and only the methods that are needed by application. We will create a concrete implementation of the service as a class and use that class in our controllers later.
@Autowired
private PersonRepository repository;
@Override
public List<Person> findAllPeople() {
return (List<Person>)repository.findAll();
}
@Override
public Person insert(Person p) {
return repository.save(p);
}
@Override
public boolean delete(long id) {
try {
repository.deleteById(id);
return true;
} catch (Exception e) {
System.out.println(e.getMessage());
return false;
}
}
@Override
public Person findById(long id) {
Optional<Person> result = repository.findById(id);
if (result.isPresent()) {
return result.get();
} else {
return null;
}
}
@Override
public boolean update(Person p) {
try {
repository.save(p);
return true;
} catch (Exception e) {
System.out.println(e.getMessage());
return false;
}
}This type will be used as an instance in our application, and Spring Boot will provide the implementation by automatically using Autowiring; dependency injection. You note that we are using @Autowired to inject the repository in this instance too.
Generating schema of the database
When you launch Spring Boot application, JPA will automatically create the table in the schema you specify (see application.properties above). You can see this through PostgreSQL admin panel using OmniDB by providing the connection details and connecting:
Note if you have an existing database or database schema that you are connecting to, you should disable the schema generation of Spring to prevent data loss. PostgreSQL has the columns created for us; and their types are specified by JPA. The SQL for this table was provided in the previous part of this article as well.
Creating the API Controller
For this demo, we will use RestController to create a RESTful API to demonstrate the connection of Java application with PostgreSQL. The controller has this structure:
package com.demos.crud.controllers.apis;
import java.util.List;
import com.demos.crud.data.models.Person;
import com.demos.crud.data.services.PeopleService;
import org.springframework.beans.factory.annotation.Autowired;
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.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
@RestController()
@RequestMapping("/api/people")
public class PeopleApiController {
@Autowired
PeopleService peopleService;
// Routes here
}This API will listen on /api/people for incoming requests and will have the PeopleService instance injected by the platform.
Adding the routes
You can create routes in your application to receive the requests and provide a response. I have created the following routes in demo REST controller (add them to the RESTful controller we created in the previous post):
@GetMapping("")
public List<Person> getAllPeople() {
return peopleService.findAllPeople();
}
@GetMapping("{id}")
public Person getPerson(@PathVariable long id) {
return peopleService.findById(id);
}
@PostMapping("")
public String addPerson(@RequestBody Person person) {
if(person != null) {
peopleService.insert(person);
return "Added a person";
} else {
return "Request does not contain a body";
}
}
@DeleteMapping("{id}")
public String deletePerson(@PathVariable("id") long id) {
if(id > 0) {
if(peopleService.delete(id)) {
return "Deleted the person.";
} else {
return "Cannot delete the person.";
}
}
return "The id is invalid for the person.";
}
@PutMapping("")
public String updatePerson(@RequestBody Person person) {
if(person != null) {
peopleService.update(person);
return "Updated person.";
} else {
return "Request does not contain a body";
}
}You can add more routes as you need. I have created the routes for the reading part, creating part, updating, and deleting part. Read more about routing in Spring applications here. The routes that we created here do:
- getAllPeople is responsible for reading all the people from database and returning them as a List of Person type.
- getPerson accepts a variable from the URL path and uses that as an ID to query the database for a Person. If the person is not found, a null value is returned. You can use a simple if…else block to send a custom response.
- addPerson corresponds to a POST HTTP request and retrieves the Person details sent in the HTTP request body. Read next parts to learn how to send an object with the request.
- deletePerson corresponds to a DELETE HTTP request method and requests the repository to delete a person record from database with the specified ID.
- updatePerson corresponds to a PUT HTTP request method and uses the same function (“save”) as used in the addPerson function to update the record. Read the next part to learn the difference in these two functions.
In the next step, you will call the HTTP API endpoints on the application to perform CRUD operations on PostgreSQL database and review the state changes using OmniDB.
CRUD Operations
I will use Postman to test the API. You can use command-line or other tools to send the requests to the API as well. In the Postman, input the API endpoints and make the request. The default endpoint for our Java application is http://localhost:8080 and our API is hosted at /api/people/.
Create a record
Before sending any requests, let us quickly review the state of our database. Our database does not contain any records in the table as shown in screenshot below:
Now, to add a new record to our database, we will issue a request to out API endpoint with a POST HTTP method. We will pass the JSON notation for our data in the request body for our Java code to extract from:
In the next section we see the state of our database after this operation; with the record added to the table.
Read the record(s)
The entity is added to the database, we can confirm it using OmniDB admin panel, using the SQL:
SELECT t.id
, t.name
, t.role
FROM techwriting.people t
ORDER BY t.id
You can see that the person record we added is now found in the database. And you can also confirm that this works using the RESTful API method:
Web application returns the objects from the database. We only have a single person added to the database. Go ahead, execute a few more HTTP calls and create up to 5 records, and see how Java application parses the data from database in a nice JSON document.
Updating a record
You can update a record using the PUT method on the endpoint and passing an object as payload. So far, we have a record in our database with the role “Technical Writer”. In order to update this record’s role to “Software Engineer”, we can use the updatePerson behind PUT HTTP method and send the record as payload to request:
We sent the details for the record; note the ID of the record, since ORM would use the primary key to create a new record or update the record in the database. Postman shows that we were able to update the person. We can check for the record in OmniDB by rerunning the SQL command we shared above, which results in the following data grid:
This shows the record was updated in the database as well. Now, our database contains a record and we would like to see how to delete it, in the next section we will take a look at the deletion of the records.
Deleting a record
You can use the Java methods to delete the objects from the database by:
- Passing the object (Person in this case)
- Passing the ID (Long object in our case)
- Iterable instance
You can use Iterables and Transaction to overcome the load on the database and pass the array as a buffer to the database to process in a single request.
For sake of simplicity, we do a deletion based on the id of the Person (see the Java code for delete route above).
The route captures the ID “1” and JPA uses this ID to delete the records from PostgreSQL database. You can rerun the SQL (provided above) to verify the objects are deleted:
You can now check using the GET request on the API endpoint to get the list of people as well. Likewise, it returns an empty list:
This shows that each operation that you perform can create new records, update them or remove them based.
Conclusion
In this blog, we covered the PostgreSQL side for the Spring Boot and JPA ORM. We create the entities in Java JPA and linked PostgreSQL database. We had JPA create the tables/records in the database for us. We also created the JPA repository for CRUD operations and a custom service on top of JPA repository as a security layer to prevent direct access to our JPA resources.
We created the API controller and linked it with JPA repository using Spring’s dependency injection. We used Postman to perform CRUD operations on the database. You learnt how easy it is to implement a database and data access layer on top of PostgreSQL instances. Java JPA provides type mapping from POJO to PostgreSQL types. We learnt that we can perform CRUD operations and use HTTP methods as a design principle to control what actions is performed on our API.
You learnt how to connect PostgreSQL database to your Java application, you can use the code provided above (and in the previous part) to develop your applications.