How to boost your database performances under JPA part 1: @Index 📖
Hugo Brunet6 min read
TL;DR: I will present you how the use of an ORM and its given tools - here @Index - can boost your application’s performance thanks to the illustration of a booming business and its scale’s related issues 🚀
Introduction to Object-Relational Mapping (ORM) with JPA
Databases play a crucial role in most applications. However, working directly with databases can be complex and time-consuming. This is where Object-Relational Mapping frameworks like Java Persistence API (JPA) come into play.
ORM frameworks provide a way to map Java objects to relational database entities, allowing developers to interact with databases using object-oriented programming concepts. JPA, is a popular ORM framework that simplifies database operations and enhances productivity.
Your first trusty partner: JPA 👨💼
As the CEO of a new company, you’ll soon find yourself needing to retrieve data from a database. But don’t worry, you don’t have to deal with the difficulty of writing SQL queries and managing connections yourself. That’s where our trusty associate, JPA, comes in. With JPA, you can simply ask it to fetch the data you need, and it will take care of all the dirty work for you 😉
But before searching for employees, you need to have some so here is how we will define them:
@Entity
@Table(name = "employees")
public class Employee {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "name")
private String name;
@Column(name = "age")
private int age;
@Column(name = "departmentCode")
private int departmentCode;
// ... additional properties, getters, and setters
}
Let’s say you want to retrieve a list of employees based on their department code. Without JPA, you would have to manually write the SQL query, manage the database connection, and map the query results to Java objects 😪
But with JPA, here is what you can do:
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
List<Employee> findByDepartmentCode(int departmentCode);
}
Your only need is to use the EmployeeRepository interface, which is already set up for you. Just call the findByDepartmentCode method, passing in the department code you’re interested in, and JPA will handle the rest. It will execute the query, map the results to Java objects, and manage the database connection behind the scenes 🤩
So, sit back and relax while JPA does the heavy lifting for you. You can focus on running your company while JPA takes care of retrieving the data you need from the database 🏝️
Handle your database scale with @Index annotation 📈
2 years later, the company has experienced tremendous growth, and thousands of employees have been hired. However, the trusty associate, JPA, is struggling to keep up with the increasing workload, even finding the list of employees related to a department takes hours. As the CEO, you are determined to find a solution to help JPA handle the company’s growth effectively.
Fortunately, you come across a powerful tool called the @Index annotation. This annotation is designed to optimize query performance by creating indexes on specific columns of the database tables and put some order in JPA’s files.
This guy won’t believe his eyes when he’ll see his room so clean thanks to this single line of code:
@Entity
@Table(
name = "employees",
**indexes = { @Index(name = "department_code_idx", columnList = "departmentCode") }**
)
public class Employee {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "name")
private String name;
@Column(name = "age")
private int age;
@Column(name = "departmentCode")
private int departmentCode;
// ... additional properties, getters, and setters
}
All you have done by putting this index in @Table is build an ordered structure outside the database in which you can easily find a departmentCode. Why department codes and not age ? Because I said columnList=”departmentCode”, nothing more. So when JPA will find for datas in a specific department, the search will be blazingly fast ⚡
⚠️ Keep in mind that you are maybe going to build some more indexes so even if it’s optional, name them well as I said with name = “department_code_idx”. You better not add a new department everyday or you will have to destroy and re build this data structure because order may have changed ⚠️
If you don’t want your employees to socialize and would rather only allow one unique employee per department, you can set your index to force unicity of departmentCode thanks to the last available parameter: unique
@Index(name = "department_code_idx", columnList = "departmentCode", **unique = true**)
As the CEO, you can now focus on running the company, confident that JPA, with the help of the @Index annotation, can effectively handle the data management needs of the expanding organization.
Let’s level up: how to index a join table column in JPA ?
You are now a direct concurrent of the GAFAM, thus your employees will need to work on many different missions at the same time and JPA will need to find them in the blink of an eye. Previously, JPA went into the model of the employee to add an index, which means he needs access to the the table in order to index, what if JPA wants to index a table which he can’t instantly access ?
ManyToMany relations bring this kind of situation where a new table is created without being self suffisant, these are Join Tables and are created as shown:
@Entity
@Table(name = "employees")
public class Employee {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "name")
private String name;
@Column(name = "age")
private int age;
**@ManyToMany
@JoinTable(name = "employee_mission",
joinColumns = @JoinColumn(name = "employee_id"),
inverseJoinColumns = @JoinColumn(name = "mission_id"))
private List<Mission> missions;**
// ... additional properties, getters, and setters
}
@Entity
@Table(name = "missions")
public class Mission {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "name")
private String name;
// ... additional properties, getters, and setters
}
Fortunately @JoinTable annotation allows us to pass one more parameter: indexes which takes a list of indexes to add in the table, so if i want to index both of my columns here is what I can do
@ManyToMany
@JoinTable(name = "employee_mission",
joinColumns = @JoinColumn(name = "employee_id"),
inverseJoinColumns = @JoinColumn(name = "mission_id"),
indexes = {
@Index(name = "employee_id_idx", columnList = "employee_id"),
@Index(name = "mission_id_idx", columnList = "mission_id")
}
)
private List<Mission> missions;
Now, a new structure is built in JPA’s office which allows him to quickly find all the missions affected to a given employee and all employees working on a given mission.
I wish you a good luck for the rest of your journey with this successful business with JPA and his new favorite tool @Index 😎
@Index in a nutshell
The @Index annotation in JPA is used to define indexes on specific columns of database tables. It has three parameters:
- columnList: Specifies the name of the column(s) on which the index should be created.
- name: (Optional) Specifies the name of the index.
- unique: (Optional) Specifies whether the index should enforce uniqueness on the column(s) it is created on.
@Index can be used in various JPA annotations, such as @Table and @JoinTable or by itself.
The use of @Index has divided the response time of some routes in my project by at most 42 with a route going from 26.51s to 0.634s.
Stay tuned for the next parts of this series, where we will explore other techniques to boost database performances under JPA.
