Spring Boot < == > SQL Databases
Spring Boot is a framework powered by Java. As such, it allows you to use Java code to connect with and interact with a database. Because Java is all about OOP (Object Oriented Programing) the name of the game is to write Java Classes that can be mapped to tables in a relational (SQL) database. The classes are not what technically are mapped, but rather, the objects that the classes generate or instantiate.
In technical terms, We’re talking about an ORM (Object Relational Mapper). This is provided by a Spring Project known as Spring Data JPA.
Yes, I know, it’s yet another Java acronym to remember. JPA stands for Java Persistence API. Persistence is the technical term used for making data permanent, like in a database, as opposed to data that is temporary, like stuff you enter into a form on a web page. When you refresh that page, the data is long gone. If you refresh your webpage, data stored in a database will not disappear, thereby making it persistent.
There are several implementations of spring data jpa. Perhaps the most popular implementation is Hibernate. To graphically show you what Hibernate is doing, take a look at the image :
It literally generates SQL tables based on the Java Classes that are written. Think of it as a translator. Or more accurately, an API, which is why it is technically an implementation of Java Persistence API afterall.
There are pros and cons to using a middleman like Hibernate to set up and fetch data from SQL. Ever heard of the phrase “lost in translation?” Well Hibernate is no exception to that. There are protocols that must be adhered to to get the translation to be done correctly, and it does get tedious at times. But nevertheless, Spring Boot remains as one of the leading back-end frameworks for Web Development, so we work with what we have.
In order to use Spring Boot to set up a database, we will need to have the correct spring-boot-starters added to the project firstly. At a minimum, we need spring-web, spring-data-jpa, and h2-database.
These spring-boot-starters generate values in the POM.xml (if managing your dependencies using maven).
POM.xml (maven)
<dependencies><dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency><dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency> <dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>runtime</scope>
</dependency><dependencies>
Next, we need to annotate a java class as an @Entity. For this example, we will attempt to model Projects and Employees. For our example, we are modeling these entities as a one-to-many relationship. That is, for every ONE project, there can be MANY potential employees. But ONE employee is can only have a max of ONE project assignment. This is known in the database modeling world as the one-to-many relationship.
How would we do this using SQL? Well, on the many side of the relationship (employees), we would have a foreign key that is referencing the primary key of the ONE side of the relationship. For every employee, there is a reference to a single project. Here’s are the SQL queries to do this.
CREATE TABLE Project(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(32) NOT NULL,
description VARCHAR(512),
stage VARCHAR(32)
);CREATE TABLE Employee (
id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(32) NOT NULL,
last_name VARCHAR(32),
project_id int,
FOREIGN KEY(project_id) REFERENCES Project(id)
);
Doing the same in Java with a little help from the Java Persistence Api. (JPA).
Project
import javax.persistence.*;
import java.util.List;
@Entity
public class Project {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
private String stage;
private String description;
@OneToMany(mappedBy = "project")
private List<Employee> employees;
}Employee
import javax.persistence.*;
@Entity
public class Employee {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String firstName;
private String lastName;
@ManyToOne
@JoinColumn(name="project_id")
private Project project;You might be wondering about the @GeneratedValue(…). That is basically setting up the AUTO_INCREMENT for the id column.
Note that the Project class has a List of Employees. The Employee class has a project field that is used to “join” the Employee table back to the Project table. These @OneToMany and @ManyToOne annotations are just doing in java what we did in SQL, compliments of JPA.
Now if we were to try create an @Entity class with these Annotations from the javax.persistence library, we would still run into an issue. This is because we never specified a valid SQL data source where the database and tables will be created.
We do this in what is known as an application.properties file, located in the resources folder of the project.
Add the following properties to the file to set up a basic H2 in-memory database.
spring.datasource.url=jdbc:h2:mem:testdb
spring.h2.console.enabled=true
spring.h2.console.path=/h2-consoleMore info on this can be found in the spring.io docs or over at Baeldung’s Spring Tutorials.
If we log on to http://localhost:8080/h2-console/, using the default :
username : sa
password : <empty>
We should see the SQL table schemas show up (after expanding the tree) :
And there we have it. We have essentially used the Java Programming language to generate a Database schema!
Now for those die hard SQL fans, it is possible to use raw SQL to create the SQL schemas. There are two steps to do this :
- Create a schema.sql file in the resources folder of the project.
- add the following property to application.properties :
spring.jpa.hibernate.ddl-auto=noneThis property will prevent the annotations from auto-generating the SQL queries to set up the database. This way, we can regain control of how we set up our SQL schemas.
