Episode 61

In the previous episode, we covered basic concepts of web services in Spring framework. Today we will build upon that foundation and take a look on how to transfer some data between our Spring Boot application and the database. Getting started with this part requires just a bit of effort to set up the aforementioned database and configuring our application, so the two can talk to each other, but no worries I will go through this step by step.

As usual, all the code presented in this article is available on my GitHub in the same project as before. This tag corresponds to project state at the time of writing this article. Before we dive into the configuration and code, let’s start with some underlying concepts, namely ORM and JPA.

Foundations

ORM, or Object Relational Mapping is a technique of mapping data between objects in object oriented languages and relational databases. Complications stem from the fact, that objects form graphs in the mathematical sense, and relational databases comprise of heterogeneous tuples following relational algebra. The classic approach to the problem was to use SQL queries and build objects manually from the result set. Using ORM greatly reduces amount of code required to deal with that, at the expense of additional layer of abstraction which leads to sacrificing some control over database query, performance trade off and additional problems like select n+1.

JPA stands for Java Persistence Api and is a part of JEE set of specifications. It includes annotations used to map between database schema and Java objects, JPQL – Java Persistence Query Language, an abstraction over SQL and the Criteria API interfaces for constructing queries as object tree. One of the most common implementations of the standard is Hibernate, used by default by Spring Boot as a basis of Spring Data JPA to add further features. Trivia: Hibernate is older than JPA itself, it’s one of the examples where JEE specification adopted something that already proved effective in the industry instead of inventing a standard from scratch.

Setup and Configuration

To proceed, we will need a database server running. I used MySQL in my examples, but you can change to something else with very little modifications. You might also want some database client, like MySQL Workbench or Toad. When you got your database running, create a schema, user, grant privileges, prepare a simple table and test it using the following SQL statements:

CREATE DATABASE Library;
CREATE USER 'librarian'@'localhost' IDENTIFIED BY 'Mellon';
GRANT INSERT,SELECT,DELETE,UPDATE ON Library.* TO 'librarian'@'localhost';

CREATE TABLE books (
id int not null auto_increment,
name varchar(100) not null,
published date,
primary key (id)
)

insert into books (name,published) values
("The Hobbit", STR_TO_DATE('21/09/1937', '%d/%m/%Y'));

select * from books;
select * from books where name like "%Hobbit%";

In order to get to our data, we need to add a few lines to application.properties file:

spring.datasource.url=jdbc:mysql://localhost/library
spring.datasource.username=librarian
spring.datasource.password=Mellon
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

We will need some dependencies in our pom.xml

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

• spring-boot-starter-data-jpa  contains JPA specification and Hibernate and adds functionality over standard JPA implementation, that are the focus of this article
• mysql-connector-java is a JDBC driver that enables interaction with MySQL database.

Entities and Repositories

We are all set to do some coding now. Let’s start with the object that will be an equivalent of the single table we have created in the database:

@Entity
@Table(name = "books")
@Data
public class Book {

	@Id
	@GeneratedValue
	private Long id;

	@Column
	private String name;

	@Column
	private Date published;

}

There are few JPA annotations:

• @Entity indicates, that this objects is going to be managed by persistence provider and bound to particular row in the database.
• @Table may specify table name and optionally schema.
• @Id indicates, that the property is primary key.
• @GeneratedValue means that key will be generated and allows to specify generation strategy.
• @Column maps object property to database column, we can supply a database name if it differs from property name.
• @Data is another story. It’s part of project Lombok – set of useful annotations processed by Java agent that auto generate and inject boilerplate code.

Then we will need a new Spring component to operate on our entities – the repository.

@Repository
public interface BookRepository extends JpaRepository<Book, Long> {}

• @Repository is Spring stereotype indicating that the component is meant to store and retrieve data.
• JpaRepository offer a set of methods for manipulating entities – searching, adding, updating and deleting

To keep some order, let’s have another controller:

@RestController
@RequestMapping("/db")
public class DatabaseController {

	@Autowired
	BookRepository bookRepository;

	@RequestMapping("/book/findall")
	public List<Book> findAll() {
		return bookRepository.findAll();
	}
}

It uses our repository and a single method to return all entries in the table. We already have one, so after sending GET request to localhost:8080/SpringAngularIntro/db/book/findall we should get a single element list: [{“id”:1,”name”:”The Hobbit”,”published”:”1937-09-21″}]

Inserts and Selects

Database insert is simple. Following REST principles, we should use PUT HTTP method for that.

@RequestMapping(value = "/book/add", method = RequestMethod.PUT)
public String addBook(@RequestBody Book book) {
	book.setId(null);
	bookRepository.save(book);
	return "We now have " + bookRepository.count() + " books!";
}

As with POST request in previous episode, we can use some tool like Postman to deliver the JSON body of book we want to insert to database. We can, and should ignore the id property, since it will be auto generated. To retrieve an entity by id, we can use another standard JpaRepository method:

@RequestMapping("/book/get/{id}")
public Book find(@PathVariable Long id) {
	return bookRepository.findOne(id);
}

The request to /db/ book/get/1 should return The Hobbit again.

The Magic!

Let’s now add a method to our repository. Mind, it’s still an interface only.

List<Book> findByNameIgnoreCaseContaining(String name);

And use it in new controller method accordingly:

@RequestMapping("/book/find/{name}")
public List<Book> find(@PathVariable String name) {
	return bookRepository.findByNameIgnoreCaseContaining(name);
}

Now we can try request like /db/book/find/obbiT, and it will return our first entity again and perhaps some other depending on what has been inserted into the table. Wait, what!? You may be wondering how the hell Spring was actually able to do that without any SQL code or bunch of annotations or anything like that? Here comes the magic :) That’s a query building mechanism embedded into Spring Data JPA that generates query based on interface method name. It picks substrings like “find”, “or”, “Between”, “IgnoreCase” and others, builds JPQL query, shovels it down the database and gets you list of results. It’s not even one line of code, it’s just an interface method name.

Spring in the Library

That’s enough for short introduction. Of course, there is much more there. As you have most likely noticed, we have omitted Author of the book. It’s because Author can write many books and book can have many authors, so we are dealing with some kind of complicated relationship here. If you would like to dive a bit deeper, you can refer to the Spring Data JPA documentation and JPA specification. The latter might be a bit heavy, but there are many books covering the subject. Have a good inserts and selects and see you next week!

Image sources:

• BenceBalaton
• Oleksii Petrov
• Luce-in-the-sky