In this chapter we are going to create database schema with initial data. Then we will map it into entities model and provide operations for management of the entities.
After you have completed your own Task Service Setup, we are going to create our first app component.
Going back to our example application, Task Service, we need to provide basic functionalities:
-
Create a
Personassociated with aTaskList -
Perform CRUD (Create/Read/Update/Delete) operations on
TaskListand itsTaskItems -
Create a
TaskListwith a specified name and a set number ofTaskItems(each containing sample data) -
Search for a
TaskListby name -
Find overdue and incomplete
TaskItems
To accomplish that we are going to work over three entities: PersonEntity, TaskItemEntity, TaskListEntity.
Each of the entities will have autogenerated id and version which specifies version number of the entity.
The TaskListEntity will be defined with name. It will also have a collection of tasks (TaskItemEntities).
The TaskItemEntity will be defined with: name, completed (true of false) and a deadline. It will have a TaskListEntity, that it belongs to.
The PersonEntity will be defined with an email. It will also hold an information, which task list it has assigned (TaskListEntity).
This means, we will have to represent two relationships:
The one-to-many relation between the task list and task items.
The one-to-one relation between a person and a task list.
For the sake of the training we will be working with H2 database engine to create our database schema. We will be using flyway to migrate our database scheme.
You can check that your schema is valid running TodoAppApplication.java which recreates schema after each run. Created schema can be found in the H2 console.
Lets start with the database schema. Create a new sql file V0001__Create_schema.sql in todo-app/src/main/resources/db/migration/1.0/ folder.
We will add our first table TASK_LIST in /todo-app/src/main/resources/db/migration/1.0/V0001__Create_schema.sql. In the case of TaskService, the items will provide: id, version and name. So we need to represent that data in our table:
CREATE TABLE TASK_LIST (
ID NUMBER(19,0) NOT NULL AUTO_INCREMENT,
VERSION INTEGER NOT NULL,
NAME VARCHAR(256),
PRIMARY KEY (ID)
);-
ID: the id for each item, automatically incremented using sequence HIBERNATE_SEQUENCE.
-
VERSION: used internally by JPA to take care of the optimistic locking for us.
-
NAME: the task list’s name.
We will also set the constraints: - primary key for id to take care of it’s uniqueness.
Basing on TASK_LIST table lets provide also TASK_ITEM table. This time we will have to add a foreign key, which will describe the relation between these two tables. TASK_ITEM table will be created with attributes as follows:
-
ID: the id for each customer, automatically incremented using sequence HIBERNATE_SEQUENCE.
-
VERSION: used internally by JPA to take care of the optimistic locking for us.
-
NAME: the task’s name.
-
COMPLETED: the information whether the task was completed in form of a number: 1 as true, 0 as false, with default value being equal to 0.
-
DEADLINE: a time by which the task needs to be done, as a timestamp.
-
TASK_LIST_ID: the id of a TASK_LIST entry, to which the TASK_ITEM belongs to.
We will also set the constraints: - primary key for ID to take care of it’s uniqueness. - foreign key for TASK_LIST_ID referencing the TASK_LIST Table, describing the relationship between the Task Item and the List.
Please create this table definition basing on types and specific column names that are shown in the diagram.
Basing on TASK_LIST table we will provide the PERSON Table as well. Similar to TASK_ITEM, PERSON will also contain a foreign key to TASK_LIST table, which will describe a relationship between the two. The attributes we need to provide are as follows:
-
ID: the id for each order, automatically incremented using sequence HIBERNATE_SEQUENCE.
-
VERSION: used internally by JPA to take care of the optimistic locking for us.
-
EMAIL: an email of the Person
-
TASK_LIST_ID: the id of a TASK_LIST entry, to which the PERSON is assigned.
We will also set the constraints: - primary key for ID to take care of it’s uniqueness. - foreign key for TASK_LIST_ID referencing the TASK_LIST Table, describing the relationship between the Person and the Task List.
CREATE TABLE PERSON (
ID NUMBER(19,0) NOT NULL AUTO_INCREMENT,
VERSION INTEGER NOT NULL,
EMAIL VARCHAR(256) NOT NULL,
TASK_LIST_ID NUMBER(19,0),
PRIMARY KEY (ID),
FOREIGN KEY (TASK_LIST_ID) REFERENCES TASK_LIST(ID)
);Finally we can provide a certain amount of mock data to start our app. Add a new sql script /todo-app/src/main/resources/db/migration/1.0/V0002__Create_mockdata.sql adding sample data:
INSERT INTO TASK_LIST(ID, VERSION, NAME) VALUES (-1, 0, 'Business Analysis Tasks');
INSERT INTO TASK_LIST(ID, VERSION, NAME) VALUES (-2, 0, 'Developer Tasks');
INSERT INTO TASK_LIST(ID, VERSION, NAME) VALUES (-3, 0, 'Tester Tasks');
INSERT INTO TASK_ITEM(ID, VERSION, NAME, COMPLETED, DEADLINE, TASK_LIST_ID) VALUES (-1, 0, 'Update Instruction on Confluence', 0, '2024-12-01', -1);
INSERT INTO TASK_ITEM(ID, VERSION, NAME, COMPLETED, DEADLINE, TASK_LIST_ID) VALUES (-2, 0, 'Review statuses in JIRA Tasks', 0, '2024-12-05', -1);
INSERT INTO TASK_ITEM(ID, VERSION, NAME, COMPLETED, DEADLINE, TASK_LIST_ID) VALUES (-3, 0, 'Perform Review on Change Request #1', 0, '2024-05-01', -2);
INSERT INTO TASK_ITEM(ID, VERSION, NAME, COMPLETED, DEADLINE, TASK_LIST_ID) VALUES (-4, 0, 'Prepare test scenarios for Change Request #1', 0, '2024-04-15', -3);
INSERT INTO PERSON(ID, VERSION, EMAIL, TASK_LIST_ID) VALUES (-1, 0, 'ba@capgemini.com', -1);
INSERT INTO PERSON(ID, VERSION, EMAIL, TASK_LIST_ID) VALUES (-2, 0, 'developer@capgemini.com', -2);
INSERT INTO PERSON(ID, VERSION, EMAIL, TASK_LIST_ID) VALUES (-3, 0, 'tester@capgemini.com', -3);You can provide your own data or use script which can be found here.
Run application and check that the data you provided is inserted into the database
If you don’t have the lombok dependency yet, add it to the pom.xml:
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>You might have to install lombok separately in your IDE for the lombok annotations to work.
Now that we have defined the data base for our entities is the moment to start creating the code of the related components.
We are going to create entities in new package task which will contain all objects specific for our application.
Create package task.dataaccess.entity under com.capgemini.training.todo where we will place our entities.
Create new class TaskListEntity.
Mark table with @Entity annotation, a @Table annotation and set a name for a table which represents the Entity in the database. Additionally, mark the class with @Getters and @Setters annotations from lombok - this will allow a getter and setter generation "in the background", without adding them in the class.
Create there private attributes basing on the schema (id, version, name). We will add the bi-directional relationship with TaskItemEntity later on. Mark the id attribute with @Id annotation and @GeneratedValue(strategy = GenerationType.IDENTITY) which describes the generation strategy for the id. Mark the version attribute with @Version annotation which specifies, that the attribute will be automatically incremented and used for versioning control.
@Entity
@Table(name = "TASK_LIST")
@Setter
@Getter
public class TaskListEntity {Next to the TaskListEntity create PersonEntity. Mark the class with proper annotations. Prepare attributes as shown in the schema. For Id and Version use the same annotations, as in TaskListEntity.
Now, lets implement the uni-directional @OneToOne relationship between the person and the task list. PersonEntity will be the owner of the relationship. Add a private attribute of type TaskListEntity in PersonEntity. Mark it with @OneToOne annotation. Additionaly, you can add a @JoinColumn annotation, which will precisely describe what is the name of the Foreign Key column in the database in the PERSON table, and which column it references in TASK_LIST table.
@OneToOne
@JoinColumn(name = "TASK_LIST_ID", referencedColumnName = "ID")
private TaskListEntity taskList;Create new entity TaskItemEntity. Mark the class with proper annotations.
First, add all attributes described in the structure and use appropriate annotations for id and version.
Now we will create a bi-directional @ManyToOne relationship with TaskListEntity.
TaskItemEntity is the owning side and will hold the information about the TaskListEntity. Add the private attribute of type TaskListEntity in TaskItemEntity. Mark it with @ManyToOne annotation. You can additionally specify the fetchType, so that the loading strategy is lazy.
@ManyToOne(fetch = FetchType.LAZY)
private TaskListEntity taskList;Even though TaskItemEntity is the owner, we also want TaskListEntity to hold information about the tasks connected with the list. Edit the TaskListEntity and add an additional private attribute of type List<TaskItemEntity. Mark the attribute with @OneToMany annotation. Add an information, which attribute is this relationship mapped by, describe the fetch type (LAZY in this case, though it’s a default behaviour) and the cascade (for the sake of the training we will use CascadeType.ALL).
@OneToMany(mappedBy = "taskList", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
private List<TaskItemEntity> items;You are ready to go! Run the application and check if database schema maps correctly into entities model.
To perform operations on our entities we need to create repositories for each of them. They will contain operations specific for each of the objects. Repositories used in Spring are already defined as Interface called Repository. There are multiple extentions of this interface and we will use JpaRepository<ENTITY, ID>.
In the component task, create package dataaccess.repository. We will place there all our repositories.
Create interface TaskListRepository. It should extend JpaRepository from Spring which contains all basic operations along with methods used in sorting and paging of results. This Interface is generic.
public interface TaskListRepository extends JpaRepository<TaskListEntity, Long> {
//Add Imports and that's it
}With such repository we can already perform basic CRUD operations, f.e. save, deleteById, findById.
We can test the behaviour of Repository methods using unit tests. Remember that we do not test the framework itself but our usage of it. Still writing test for simple CRUD operations is usually not needed. We will write some simple tests nevertheless, just to understand the structure and behaviour of repository tests using @DataJpaTest
In source folder src/test/java of project create same package as for interface. Alternatively, you can use IDE to generate test in corresponding package but in srt/test/java folder. This will enable you to test this class package protected (default visibility) methods. Create the class TaskListRepositoryTest. Remember to add imports.
@DataJpaTest
public class TaskListRepositoryTest {}@DataJpaTest creates us a simplified Spring Test configuration with loaded Entities and repositories. All of the tests on @DataJpaTest will be launched on preconfigured H2 Database, so there’s no configuration needed. Remember, that by default all @Test methods in a @DataJpaTest annotated test class will be automatically rolled back and not commited. This actually may cause some of your database-specific constraints to not be actually checked, only your Entity annotations - and that’s a plus, because the tests are much faster, and you should add constraints to BOTH db and Entities. Just like you should do frontend AND backend validation.
Let’s write our first test - we would like to find all entities. We will use for it findAll() method which is available in TaskListRepository thanks to inheritance of JpaRepository. Spring then creates a proxy classes of all Interfaces extending the Spring repositories, because Spring is still java, and you cannot have instances of interfaces.
-
inject TaskListRepository to test class.
-
prepare public method annotated with @Test
-
call there taskListRepository.findAll() method
-
check that number of found elements is same as number of elements created in your migration.
-
imports
@DataJpaTest
public class TaskListRepositoryTest {
@Autowired
private TaskListRepository taskListRepository; //some IDE's wrongfully mark this var as unused
@Test
void testFindAll() {
//given when
List<TaskListEntity> result = taskListRepository.findAll();
//then
assertThat(result).isNotEmpty();
assertThat(result).hasSize(3);
// Notice, that the number of objects are equal to entities inserted by mockdata script launched by flyway.
// We could also define separate test/resources migration scripts, but we won't do that!
// (let's spare the time, you can google it if you want)
}
}To run the tests, right click and select run tests.
You should also see some hibernate queries, they may be difficult to read but its there.
Hibernate: select tle1_0.id,tle1_0.name,tle1_0.version from task_list tle1_0You can also use the mvn command:
mvn testWonderful job! You’ve created your first Test using Spring Repositories and @DataJpaTest. You may have also heard of @SpringBootTest. You can try and switch it up to see the difference. @SpringBootTest's are slower, but also allow you to test web communication and they search for @SpringBootConfiguration when required, allowing you to overwrite configuration for testing purposes.
Regardless JPA Testing, those annotations seem same at first, but it’s much easier to connect yourself to an other database, be it in local or in remote test environment and check if your database constraints etc. are working, if your triggers, scripts or whatever else is working as intended too. These tests use I/O intensively, so they are PAINFULLY slow and you need to maintain data stability between tests, so developers need to implement proper measures. On top of that, we can throw parallel test launches out of the window. Still, some projects use or require this approach, so it’s worth noting.
Spring Data Allows us to use a multitude of basic queries, but what if we want something customised? Due to the age (Java developers prefer word "Maturity") of the language and JPA itself, multitude of different libraries, Domain Specific Languages, frameworks etc were introduced, to aid us or to make our lives more difficult.
In the end, it doesn’t even matter - it’s all always mapped to JPQL Query String, and called through entityManager, the core of JPA. If you will debug deep enough, you will find it (try it if you’re bored). How you will use the JPA is usually decided by an architect or the developers themselves.
Implement those 2 queries and test them. Before you dive into the code, you may want to look at examples in paragraph below.
1) Find all TaskLists with partially given name, ignoring upper and lowercase
2) Find all TaskItems by completion and deadline - essentially we want to find and which are not completed and with exceeded deadline
Courtesy of Spring: https://docs.spring.io/spring-data/jpa/reference/jpa/query-methods.html
Example’s of queries in given forms with explanation:
Spring Query Methods is a mechanism used solely by Spring. It can match the method name with corresponding table, due to Generic Type, and add simple clauses just by interpreting the name of the methods. It will cause wierd exceptions if this cannot be parsed, so be advised!
In the TaskListRepository interface type:
List<TaskListEntity> findAllByName(String name);That’s it.
Directly in the inteface create a new method with a @Query annotation and JQPL query inside. @Param annotation is used to map the java var name of query variable
@Query("SELECT item FROM TaskItemEntity item WHERE item.deadline < :deadline")
List<TaskItemEntity> findByDeadlineBefore(@Param("deadline") Instant deadline);We can predefine a NamedQuery in the Entity class as a query with a given name and query string. You can use parameters there, but to use Spring mechanisms, the name of the query needs to start with the name of the entity.
@NamedQuery(name = "TaskListEntity.findUsingNamedQuery",
query = "select task from TaskListEntity task where name =:name")Then you can create a method in interface with same name:
List<TaskListEntity> findUsingNamedQuery(String name);If the name will match, then you will be able to run the named query from interface.
To implement custom queries we need to have access to the EntityManager. The easiest way is to write default methods and using the entityManager as parameter. This breaks SOLID but is definitely quicker if you just want to test something out.
The proper way to use the entityManager without receiving it as a param, is to implement a customRepo and extending your main repo with it, thus keeping our SOLID rules in check and having a way to implement methods manually.
In the same package, create a CustomTaskItemRepository. It will contain method signatures for your JpaRepository.
public interface CustomTaskItemRepository {
List<TaskItemEntity> findByCriteria(TaskItemCriteria searchCriteria);
}Create a custom implementation of your Custom repository. Here Spring does some interpreting, so to avoid exceptions all of the interfaces and classes should be in same package and have corresponding name. Class implementing the custom repository has to have a name ending with Impl - CustomTaskItemRepositoryImpl. Write your own implementation there
public class CustomTaskItemRepositoryImpl implements CustomTaskItemRepository{
@PersistenceContext
private EntityManager entityManager;
@Override
public List<TaskItemEntity> findByCriteria(TaskItemCriteria searchCriteria) {
// implementation
}
}Now extend your own interface repository with BOTH JpaRepository and CustomTaskItemRepository. Your TaskItemRepository will now understand that it has those custom methods already implemented.
public interface TaskItemRepository extends JpaRepository<TaskItemEntity, Long>, CustomTaskItemRepository {
//nothing! it just works
}As personal opinion of author(ppodolsk): I abhor this abomination. It allows you to build somewhat customized, more complex queries, but the amount of boilerplate code is obnoxious. The boilerplate code will require two different "creator" classes to be instantiated and mixed with each other. In the end the dev is forced to either build a horrific chain of subclasses to generalise some of the code, or, typically, copy-paste like the client would pay them for LoC.
Here’s a working sample, you can copy-paste in most of projects using Criteria API and you’re golden.
In the TaskItemRepository interface type:
default List<TaskItemEntity> findByCriteria(TaskItemCriteria searchCriteria, EntityManager entityManager) {
Objects.requireNonNull(searchCriteria, "Invalid input: searchCriteria - cannot be null!");
CriteriaBuilder builder = entityManager.getCriteriaBuilder(); // initialize criteria builder, to build queries
CriteriaQuery<TaskItemEntity> criteriaQuery = builder.createQuery(TaskItemEntity.class); // initialize Query structure
Root<TaskItemEntity> root = criteriaQuery.from(TaskItemEntity.class); //Initialise "select from"
List<Predicate> predicateList = new ArrayList<>();
if (searchCriteria.taskItemName() != null) {
predicateList.add(builder.like(root.get("name"), searchCriteria.taskItemName()));
}
if (searchCriteria.taskListName() != null) {
Join<TaskItemEntity, TaskListEntity> joinedList = root.join("taskList", JoinType.INNER);
predicateList.add(builder.like(joinedList.get("name"), searchCriteria.taskListName()));
}
Predicate[] predicateArray = predicateList.toArray(new Predicate[0]);
criteriaQuery.where(predicateArray); //API made for lists does not like lists
TypedQuery<TaskItemEntity> query = entityManager.createQuery(criteriaQuery);
return query.getResultList();
}Oof.
Criteria Api long lost step-brother. Everyone likes him more, he’s cool. So what that (in older versions, don’t know for sure now) he will occasionally cut parts of your query WHERE clauses and won’t add them to the query String, potentially causing catastrophic reads. Imagine if that bad read would go to some batch processing.
Still, those errors are not that often, and the API is so nice and easy, that we forgive him.
To use QueryDSL along with its supportive QClasses we need to do the following:
Add these 2 dependencies to the pom.xml in the <dependencies> block;
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-apt</artifactId>
<version>5.0.0</version>
<classifier>jakarta</classifier>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-jpa</artifactId>
<classifier>jakarta</classifier>
<version>5.0.0</version>
</dependency>Add this plugin in the <plugins> block;
<plugin>
<groupId>com.mysema.maven</groupId>
<artifactId>apt-maven-plugin</artifactId>
<version>1.1.3</version>
<executions>
<execution>
<goals>
<goal>process</goal>
</goals>
<configuration>
<outputDirectory>target/generated-sources/java</outputDirectory>
<processor>com.mysema.query.apt.jpa.JPAAnnotationProcessor</processor>
</configuration>
</execution>
</executions>
</plugin>Clean install the project. In target.generated-sources/annotations there should be QPersonEntity, QTaskItemEntity, QTaskListEntity classes.
QueryDSL allows us to write us easy queries. In PersonRepository we can add the method
@Repository
public interface PersonRepository extends JpaRepository<PersonEntity, Long> {
default List<PersonEntity> findCustomByEmailWithTaskListName(String email, String taskListName, EntityManager entityManager) {
QPersonEntity person = QPersonEntity.personEntity;
QTaskListEntity taskList = QPersonEntity.personEntity.taskList;
JPAQueryFactory jpaQueryFactory = new JPAQueryFactory(entityManager);
return jpaQueryFactory.select(person).from(person)
.innerJoin(taskList)
.where(person.email.eq(email)
.and(taskList.name.like(taskListName)))
.orderBy(person.email.desc(), taskList.name.asc())
.fetch();
}
}And then simply test it:
@DataJpaTest
class PersonRepositoryTest {
@Autowired
private PersonRepository personRepository;
@PersistenceContext
private EntityManager entityManager;
@Test
void testFindByQueryDSL(){
// given when
List<PersonEntity> result = personRepository.findCustomByEmailWithTaskListName(
"ba@capgemini.com", "Business Analysis Tasks", entityManager);
// then
assertThat(result).isNotEmpty();
assertThat(result).hasSize(1);
}
}Implement these queries:
1) Find person using email - use Spring Query Method 2) Find TaskItems with deadline between - use @Query 3) Find TaskList without any items - use NamedQuery and bind it to interface method 4) Find all TaskLists with more than 1 item - use QueryDSL - this one may be tricky
Remember to test your queries!
During your implementation you may encounter multiple vague exceptions. JPA Exceptions look sometimes like they are wrapped or hidden, so careful consolelog analysis is required. However, the are some exceptions that are more common than others:
IllegalStateException: Failed to load ApplicationContextThis happens usually if the query you have created has errors and does not compile. This is because queries are checked in runtime during startup (not compile-time) - that’s why your code compiles, but the test or app does not run.