Indexing With JPA

What Is The Indexing?

• Indexing in a database is a technique that improves the speed of data retrieval operations. It works by creating a data structure known as an index, which allows for faster searching and accessing of data from the database.
• An index is a small table with two columns. The first column contains a copy of the primary or candidate key of a table, and the second column contains a set of pointers, each of which holds the address of the disk block where the specific key value is stored
• When a query needs to access a table that has an index, the database can use the index to retrieve records faster. The database finds the key value in the index, and each key value is stored with a pointer to the record in the table associated with this key value.

• Indexes are not required for a database to function, but they can significantly improve query response time. However, they do require additional space to store the index records.

• There are different types of indexing methods such as Primary Indexing and Secondary Indexing. In Primary Indexing, there is a one-to-one relationship between entries in the index table. This type of indexing can be further divided into Dense Index, where a record is created for every search key value in the database, and Sparse Index, where an index record appears only for some of the values in the file.

• Secondary Indexing can be generated by a field which has a unique value for each record, and it should be a candidate key. This type of indexing is used to reduce the mapping size of the first level.

• In summary, indexing is a powerful technique that can greatly enhance the performance of a database by enabling faster retrieval of data.

• More information at learnsql.com and guru99.com

Indexing With JPA

• In JPA, we can create indexes for tables using the @Index annotation. Let's see the example below.

package com.springboot.project.entity;

import jakarta.persistence.CascadeType;
import jakarta.persistence.Entity;
import jakarta.persistence.EnumType;
import jakarta.persistence.Enumerated;
import jakarta.persistence.FetchType;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Index;
import jakarta.persistence.OneToMany;
import jakarta.persistence.Table;
import lombok.Getter;
import lombok.Setter;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;

@Getter
@Setter
@Entity
@Table(name = "customers", indexes = {
        @Index(name = "uniqueEmailIndex", columnList = "email", unique = true),
        @Index(name = "uniquePhoneIndex", columnList = "phone", unique = true),
        @Index(name = "uniqueMultiIndex", columnList = "email, phone", unique = true)
})
public class CustomerEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    private UUID id;
    private String fullName;
    private String email;
    private String address;
    private String phone;
    @Enumerated(EnumType.STRING)
    private Gender gender;
    private Date dob;

    @OneToMany(mappedBy = "customer", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    private List<OrderEntity> orders = new ArrayList<>();

}

• So the we can see @Index annotations are used to define database indexes for CustomerEntity. Then in every @Index annotation we define some parameter as name, columnList and unique. For example @Index(name = "uniqueEmailIndex", columnList = "email", unique = true)
  • name: we define the name of the index.
  • columnList: we specify the column of the table that we will create an index.
  • unique: specifies that the values in the column must be unique, meaning that no two rows in the table can have the same value. This enforces data integrity and prevents duplicate in the table.
• We can also create a composite index for multi column in a table by defining columns name in the columnList parameter. Then in this case if we define the unique = true then it means no two rows in the table can have both the same email and phone values simultaneously.
• Note: In JPA, if we use annotation @Column(name = "email", unique = true) then JPA also will create for us an index for that column automatically. However, the index name will have a random name.

Example Project

• Now, let's take an example for applying Indexing with JPA.

Dependency

• Let's create an sample project and add these dependencies below.

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>3.1.4</version>
        </dependency>

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

        <!-- postgresql driver -->
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.6.0</version>
        </dependency>


        <!-- lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.28</version>
            <scope>provided</scope>
        </dependency>

        <!-- Apache commons-lang3 -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.13.0</version>
        </dependency>

        <!-- slf4j -->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>2.0.9</version>
        </dependency>

        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-simple</artifactId>
            <version>2.0.9</version>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.openapitools</groupId>
            <artifactId>jackson-databind-nullable</artifactId>
            <version>0.2.6</version>
        </dependency>

        <dependency>
            <groupId>jakarta.validation</groupId>
            <artifactId>jakarta.validation-api</artifactId>
            <version>3.0.2</version>
        </dependency>

        <dependency>
            <groupId>io.swagger.core.v3</groupId>
            <artifactId>swagger-annotations</artifactId>
            <version>2.2.16</version>
        </dependency>

        <dependency>
            <groupId>org.hibernate.validator</groupId>
            <artifactId>hibernate-validator</artifactId>
            <version>8.0.1.Final</version>
        </dependency>

        <dependency>
            <groupId>org.mapstruct</groupId>
            <artifactId>mapstruct</artifactId>
            <version>1.5.5.Final</version>
        </dependency>
    </dependencies>

• Then let's apply the plugin below for OpenApi generator and Mapstruct

 <plugins>
    <plugin>
        <groupId>org.openapitools</groupId>
        <artifactId>openapi-generator-maven-plugin</artifactId>
        <version>7.0.1</version>
        <executions>
            <execution>
                <goals>
                    <goal>generate</goal>
                </goals>
                <configuration>
                    <!-- path to the openapi file spec `.yml` -->
                    <inputSpec>
                        ${project.basedir}/src/main/resources/openapi/openapi-server.yml
                    </inputSpec>
                    <generatorName>spring</generatorName>
                    <!-- generated package for api interface -->
                    <apiPackage>com.springboot.project.generated.api</apiPackage>
                    <!-- generated package for models -->
                    <modelPackage>com.springboot.project.generated.model</modelPackage>
                    <!-- using supportingFilesToGenerate -->
                    <supportingFilesToGenerate>
                        ApiUtil.java
                    </supportingFilesToGenerate>
                    <configOptions>
                        <useTags>true</useTags>
                        <delegatePattern>true</delegatePattern>
                        <dateLibrary>java8</dateLibrary>
                        <java8>false</java8>
                        <interfaceOnly>true</interfaceOnly>
                        <useBeanValidation>true</useBeanValidation>
                        <performBeanValidation>true</performBeanValidation>
                        <useOptional>false</useOptional>
                        <useSpringBoot3>true</useSpringBoot3>
                    </configOptions>
                </configuration>
            </execution>
        </executions>
    </plugin>
    <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-compiler-plugin</artifactId>
        <version>3.11.0</version>
        <configuration>
            <source>17</source>
            <target>17</target>
            <annotationProcessorPaths>
                <path>
                    <groupId>org.mapstruct</groupId>
                    <artifactId>mapstruct-processor</artifactId>
                    <version>1.5.5.Final</version>
                </path>
                <path>
                    <groupId>org.projectlombok</groupId>
                    <artifactId>lombok</artifactId>
                    <version>1.18.28</version>
                </path>
                <path>
                    <groupId>org.projectlombok</groupId>
                    <artifactId>lombok-mapstruct-binding</artifactId>
                    <version>0.2.0</version>
                </path>
            </annotationProcessorPaths>
        </configuration>
        </plugin>
</plugins>

OpenApi

• Now, in the folder resource, we will create a folder openapi and a openapi-server.yaml inside it. The content of this file will look like below with some apis.

openapi: 3.0.3
info:
  title: Swagger Customer - OpenAPI 3.0
  description: Everything about sample hibernate second level cache
  termsOfService: http://swagger.io/terms/
  contact:
    email: apiteam@swagger.io
  license:
    name: Apache 2.0
    url: http://www.apache.org/licenses/LICENSE-2.0.html
  version: 1.0.11
externalDocs:
  description: Find out more about Swagger
  url: http://swagger.io
servers:
  - url: https://petstore3.swagger.io/api/v1
tags:
  - name: customer
    description: Everything about your Customer
    externalDocs:
      description: Find out more
      url: http://swagger.io
  - name: order
    description: Everything about your Order
    externalDocs:
      description: Find out more
      url: http://swagger.io
paths:
  /v1/customers:
    get:
      tags:
        - customer
      summary: get an existing customer info
      description: get an existing customer info by Email
      operationId: getCustomerInfoByEmail
      parameters:
        - name: email
          in: query
          required: true
          schema:
            type: string
      responses:
        '200':
          description: Successful operation
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/CustomerResponse'
        '400':
          description: Invalid ID supplied
        '404':
          description: Pet not found
        '405':
          description: Validation exception
    post:
      tags:
        - customer
      summary: Add a Customer to database
      description: Add a Customer to database
      operationId: addCustomer
      requestBody:
        description: Create a Customer to database
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/CustomerRequest'
        required: true
      responses:
        '200':
          description: Successful operation
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/CustomerResponse'
        '405':
          description: Invalid input
  /v1/customers/{customerId}/info:
    get:
      tags:
        - customer
      summary: get an existing customer info
      description: get an existing customer info by Id
      operationId: getCustomerInfo
      parameters:
        - name: customerId
          in: path
          required: true
          schema:
            type: string
            format: uuid
      responses:
        '200':
          description: Successful operation
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/CustomerResponse'
        '400':
          description: Invalid ID supplied
        '404':
          description: Pet not found
        '405':
          description: Validation exception
    put:
      tags:
        - customer
      summary: Update an existing customer
      description: Update an existing customer by Id
      operationId: updateCustomers
      parameters:
        - name: customerId
          in: path
          required: true
          schema:
            type: string
            format: uuid
      requestBody:
        description: Update an existent customer in the database
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/CustomerResponse'
        required: true
      responses:
        '200':
          description: Successful operation
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/CustomerResponse'
        '400':
          description: Invalid ID supplied
        '404':
          description: Pet not found
        '405':
          description: Validation exception
  /v1/customers/{customerId}/orders:
    post:
      tags:
        - order
      summary: Add an order to database
      description: Add an order to database
      operationId: addOrder
      parameters:
        - name: customerId
          in: path
          required: true
          schema:
            type: string
            format: uuid
      requestBody:
        description: Create an Order to database
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/OrderRequest'
        required: true
      responses:
        '200':
          description: Successful operation
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/OrderResponse'
        '405':
          description: Invalid input
  /v1/customers/{customerId}/orders/{orderId}:
    get:
      tags:
        - order
      summary: Get an order from database
      description: Get an order from database
      operationId: getOrderDetail
      parameters:
        - name: customerId
          in: path
          required: true
          schema:
            type: string
            format: uuid
        - name: orderId
          in: path
          required: true
          schema:
            type: string
            format: uuid
      responses:
        '200':
          description: Successful operation
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/OrderResponse'
        '405':
          description: Invalid input
components:
  schemas:
    CustomerResponse:
      allOf:
        - $ref: '#/components/schemas/CustomerRequest'
        - type: object
          properties:
            id:
              type: string
              format: uuid
    OrderResponse:
      allOf:
        - $ref: '#/components/schemas/OrderRequest'
        - type: object
          properties:
            id:
              type: string
              format: uuid

    ItemResponse:
      allOf:
        - $ref: '#/components/schemas/ItemRequest'
        - type: object
          properties:
            id:
              type: string
              format: uuid



    CustomerRequest:
      type: object
      required:
        - email
        - phone
      properties:
        fullName:
          type: string
        email:
          type: string
        address:
          type: string
        phone:
          type: string
        gender:
          type: string
          enum:
            - M
            - F
        dob:
          type: string
          format: date

    OrderRequest:
      type: object
      required:
        - orderName
        - orderStatus
      properties:
        orderName:
          type: string
        orderStatus:
          type: string
          enum:
            - CREATED
            - SHIPPING
            - FINISHED
        items:
          type: array
          items:
            $ref: '#/components/schemas/ItemRequest'

    ItemRequest:
      type: object
      required:
        - itemName
        - quantity
        - price
      properties:
        itemName:
          type: string
        quantity:
          type: integer
          format: int64
        price:
          type: number
          format: double

• Now, we can use command mvn clean install to build and generate apis and model from openapi-server.yaml.

Entity

• Then let's define Entities as below. In this example a CustomerEnitty will contain many OrderEntity, then in an OrderEntity will contain many ItemEntity.
• In CustomerEntity we will put index for email, phone and a composite index for email and phone.

package com.springboot.project.entity;

import jakarta.persistence.CascadeType;
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.EnumType;
import jakarta.persistence.Enumerated;
import jakarta.persistence.FetchType;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Index;
import jakarta.persistence.OneToMany;
import jakarta.persistence.Table;
import lombok.Getter;
import lombok.Setter;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;

@Getter
@Setter
@Entity
@Table(name = "customers", indexes = {
        @Index(name = "uniqueEmailIndex", columnList = "email", unique = true),
        @Index(name = "uniquePhoneIndex", columnList = "phone", unique = true),
        @Index(name = "uniqueMultiIndex", columnList = "email, phone", unique = true)
})
public class CustomerEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    private UUID id;
    private String fullName;
    private String email;
    private String address;
    private String phone;
    @Enumerated(EnumType.STRING)
    private Gender gender;
    private Date dob;

    @OneToMany(mappedBy = "customer", fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    private List<OrderEntity> orders = new ArrayList<>();

}

package com.springboot.project.entity;

public enum Gender {

    M, F

}

• Then in the OrderEntity we have no index setting there.

package com.springboot.project.entity;

import jakarta.persistence.CascadeType;
import jakarta.persistence.Entity;
import jakarta.persistence.EnumType;
import jakarta.persistence.Enumerated;
import jakarta.persistence.FetchType;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.ManyToOne;
import jakarta.persistence.OneToMany;
import jakarta.persistence.PrePersist;
import jakarta.persistence.PreUpdate;
import jakarta.persistence.Table;
import lombok.Getter;
import lombok.Setter;

import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

@Getter
@Setter
@Entity
@Table(name = "orders")
public class OrderEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    private UUID id;

    private String orderName;

    private LocalDateTime createdDate;

    private LocalDateTime lastUpdatedDate;

    @Enumerated(value = EnumType.STRING)
    private OrderStatus orderStatus;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "customer_id")
    private CustomerEntity customer;

    @OneToMany(mappedBy = "order", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private List<ItemEntity> items = new ArrayList<>();

    @PrePersist
    private void setCreatedDate() {
        LocalDateTime localDateTime = LocalDateTime.now();
        this.createdDate = localDateTime;
        this.lastUpdatedDate = localDateTime;
    }

    @PreUpdate
    private void setLastUpdatedDate() {
        this.lastUpdatedDate = LocalDateTime.now();
    }

}

• Finally, In the ItemEntity, we will use the annotation @Column(name = "item_name", unique = true) to create an index for column item_name.

package com.springboot.project.entity;

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.FetchType;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.ManyToOne;
import lombok.Getter;
import lombok.Setter;

import java.util.UUID;

@Getter
@Setter
@Entity
public class ItemEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    private UUID id;

    @Column(name = "item_name", unique = true)
    private String itemName;

    private Long quantity;

    private Float price;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "order_id")
    private OrderEntity order;

}

Repository

• Now, lets create 3 Repositories for 3 Entities above.

package com.springboot.project.repository;


import com.springboot.project.entity.CustomerEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import java.util.Optional;
import java.util.UUID;

@Repository
public interface CustomerRepository extends JpaRepository<CustomerEntity, UUID> {
    Optional<CustomerEntity> findCustomerByEmail(String email);
}

package com.springboot.project.repository;


import com.springboot.project.entity.OrderEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import java.util.UUID;

@Repository
public interface OrderRepository extends JpaRepository<OrderEntity, UUID> {
}

package com.springboot.project.repository;


import com.springboot.project.entity.ItemEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import java.util.UUID;

@Repository
public interface ItemRepository extends JpaRepository<ItemEntity, UUID> {
}

Dto

• Next, let's create some DTOs for these 3 Entities

package com.springboot.project.model;

import com.springboot.project.entity.Gender;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

import java.util.Date;
import java.util.List;
import java.util.UUID;

@Getter
@Setter
@NoArgsConstructor
public class Customer {

    private UUID id;
    private String fullName;
    private String email;
    private String address;
    private String phone;
    private Gender gender;
    private Date dob;
    private List<Order> orders;

}

package com.springboot.project.model;

import com.springboot.project.entity.OrderStatus;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

import java.util.List;
import java.util.UUID;

@Getter
@Setter
@NoArgsConstructor
public class Order {

    private UUID id;
    private String orderName;
    private OrderStatus orderStatus;
    private List<Item> items;

}

package com.springboot.project.model;

import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

import java.util.UUID;

@Getter
@Setter
@NoArgsConstructor
public class Item {

    private UUID id;
    private UUID orderId;
    private String itemName;
    private Long quantity;
    private Float price;

}

Mapper

• Next, let's create some mapper interfaces for mapping DTOs, Entities and request models using MapStruct.

package com.springboot.project.mapper;

import com.springboot.project.entity.CustomerEntity;
import com.springboot.project.generated.model.CustomerRequest;
import com.springboot.project.generated.model.CustomerResponse;
import com.springboot.project.model.Customer;
import org.mapstruct.Mapper;
import org.mapstruct.MappingTarget;
import org.mapstruct.factory.Mappers;

@Mapper
public interface AutoCustomerMapper {

    AutoCustomerMapper MAPPER = Mappers.getMapper(AutoCustomerMapper.class);

    Customer mapToCustomerFromRequest(CustomerRequest customerRequest);

    Customer mapToCustomer(CustomerEntity customerEntity);

    CustomerEntity mapToCustomerEntity(Customer customer);

    CustomerResponse mapToCustomerResponse(Customer customer);

    void updateCustomerEntity(@MappingTarget CustomerEntity customerEntityTarget, CustomerEntity updateEntity);

}

package com.springboot.project.mapper;

import com.springboot.project.entity.OrderEntity;
import com.springboot.project.generated.model.OrderRequest;
import com.springboot.project.generated.model.OrderResponse;
import com.springboot.project.model.Order;
import org.mapstruct.Mapper;
import org.mapstruct.factory.Mappers;

@Mapper
public interface AutoOrderMapper {

    AutoOrderMapper MAPPER = Mappers.getMapper(AutoOrderMapper.class);

    Order mapToOrderFromRequest(OrderRequest orderRequest);

    OrderEntity mapToEntity(Order order);

    OrderResponse mapToOrderResponse(Order order);

    Order mapToOrderFromEntity(OrderEntity orderEntity);

}

package com.springboot.project.mapper;

import com.springboot.project.entity.ItemEntity;
import com.springboot.project.generated.model.ItemRequest;
import com.springboot.project.generated.model.ItemResponse;
import com.springboot.project.model.Item;
import org.mapstruct.Mapper;
import org.mapstruct.factory.Mappers;

@Mapper
public interface AutoItemMapper {

    AutoItemMapper MAPPER = Mappers.getMapper(AutoItemMapper.class);

    Item toItemFromRequest(ItemRequest itemRequest);

    Item toItemFromEntity(ItemEntity itemEntity);

    ItemEntity toItemEntity(Item item);

    ItemResponse toItemResponse(Item item);

}

Service

• Now, with these classes that we defined above, we can create service class for handling logics for CRUD entities.

package com.springboot.project.service;

import com.springboot.project.entity.CustomerEntity;
import com.springboot.project.mapper.AutoCustomerMapper;
import com.springboot.project.model.Customer;
import com.springboot.project.repository.CustomerRepository;
import lombok.AllArgsConstructor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.Optional;
import java.util.UUID;

@Service
@AllArgsConstructor(onConstructor = @__(@Autowired))
public class CustomerService {

    private final CustomerRepository customerRepository;

    public Customer createCustomer(Customer customer) {
        CustomerEntity customerEntity = AutoCustomerMapper.MAPPER.mapToCustomerEntity(customer);
        customerEntity = this.customerRepository.save(customerEntity);
        return AutoCustomerMapper.MAPPER.mapToCustomer(customerEntity);
    }

    public Customer getCustomer(UUID customerId) {
        Optional<CustomerEntity> customerEntity = this.customerRepository.findById(customerId);
        if (customerEntity.isPresent()) {
            return AutoCustomerMapper.MAPPER.mapToCustomer(customerEntity.get());
        }
        throw new RuntimeException("Customer Not Found!");
    }

    public void updateCustomer(UUID customerId, Customer customer) {
        Optional<CustomerEntity> customerEntity = this.customerRepository.findById(customerId);
        if (customerEntity.isPresent()) {
            CustomerEntity existedCustomerEntity = customerEntity.get();
            CustomerEntity updateCustomerEntity = AutoCustomerMapper.MAPPER.mapToCustomerEntity(customer);
            AutoCustomerMapper.MAPPER.updateCustomerEntity(existedCustomerEntity, updateCustomerEntity);
            this.customerRepository.save(existedCustomerEntity);
            return;
        }
        throw new RuntimeException("Customer Not Found!");
    }

    public void deleteCustomer(UUID customerId) {
        this.customerRepository.deleteById(customerId);
    }

    public Customer findCustomerByEmail(String email) {
        Optional<CustomerEntity> customerEntity = this.customerRepository.findCustomerByEmail(email);
        if (customerEntity.isPresent()) {
            return AutoCustomerMapper.MAPPER.mapToCustomer(customerEntity.get());
        }
        throw new RuntimeException("Customer Not Found! with email: " + email);
    }

}

package com.springboot.project.service;


import com.springboot.project.entity.CustomerEntity;
import com.springboot.project.entity.ItemEntity;
import com.springboot.project.entity.OrderEntity;
import com.springboot.project.mapper.AutoItemMapper;
import com.springboot.project.mapper.AutoOrderMapper;
import com.springboot.project.model.Item;
import com.springboot.project.model.Order;
import com.springboot.project.repository.CustomerRepository;
import com.springboot.project.repository.OrderRepository;
import lombok.AllArgsConstructor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import java.util.List;
import java.util.Optional;
import java.util.UUID;
import java.util.stream.Collectors;

@Service
@AllArgsConstructor(onConstructor = @__(@Autowired))
public class OrderService {

    private final CustomerRepository customerRepository;
    private final OrderRepository orderRepository;

    public Order createOrder(UUID customerId, Order order) {
        if (CollectionUtils.isEmpty(order.getItems())) {
            throw new RuntimeException("Can not create Order without any Item!");
        }
        CustomerEntity customerEntity = this.getCustomer(customerId);
        OrderEntity orderEntity = AutoOrderMapper.MAPPER.mapToEntity(order);
        List<ItemEntity> itemEntities = this.mapToItemEntities(order.getItems(), orderEntity);
        orderEntity.setCustomer(customerEntity);
        orderEntity.setItems(itemEntities);
        orderEntity = this.orderRepository.save(orderEntity);
        return AutoOrderMapper.MAPPER.mapToOrderFromEntity(orderEntity);
    }

    public Order getOrderDetail(UUID customerId, UUID orderId) {
        Optional<OrderEntity> orderEntityOpt = this.orderRepository.findById(orderId);
        if (orderEntityOpt.isPresent()) {
            OrderEntity orderEntity = orderEntityOpt.get();
            if(orderEntity.getCustomer().getId().equals(customerId)) {
                return AutoOrderMapper.MAPPER.mapToOrderFromEntity(orderEntity);
            }
        }
        throw new RuntimeException("customerId or orderId is not correct or relative!");
    }

    private CustomerEntity getCustomer(UUID customerId) {
        Optional<CustomerEntity> customerEntity = this.customerRepository.findById(customerId);
        if (customerEntity.isPresent()) {
            return customerEntity.get();
        }
        throw new RuntimeException("Customer Not Found!");
    }

    private List<ItemEntity> mapToItemEntities(List<Item> items, OrderEntity orderEntity) {
        return items.stream()
                .map(AutoItemMapper.MAPPER::toItemEntity)
                .peek(i -> i.setOrder(orderEntity))
                .collect(Collectors.toList());
    }

}

package com.springboot.project.service;

import com.springboot.project.entity.ItemEntity;
import com.springboot.project.entity.OrderEntity;
import com.springboot.project.mapper.AutoItemMapper;
import com.springboot.project.model.Item;
import com.springboot.project.repository.CustomerRepository;
import com.springboot.project.repository.ItemRepository;
import com.springboot.project.repository.OrderRepository;
import lombok.AllArgsConstructor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Optional;
import java.util.UUID;
import java.util.stream.Collectors;

@Service
@AllArgsConstructor(onConstructor = @__(@Autowired))
public class ItemService {

    private final ItemRepository itemRepository;
    private final CustomerRepository customerRepository;
    private final OrderRepository orderRepository;

    private List<Item> addItemToOrder(UUID orderId, List<Item> items) {
        OrderEntity orderEntity = this.getOrderEntity(orderId);
        List<ItemEntity> itemEntities = this.toItemEntities(items);
        itemEntities.forEach(i -> i.setOrder(orderEntity));
        List<ItemEntity> itemEntityResults = this.itemRepository.saveAll(itemEntities);
        return this.toItems(itemEntityResults);
    }

    private List<ItemEntity> toItemEntities(List<Item> items) {
        return items.stream().map(AutoItemMapper.MAPPER::toItemEntity).collect(Collectors.toList());
    }

    private List<Item> toItems(List<ItemEntity> itemEntities) {
        return itemEntities.stream().map(AutoItemMapper.MAPPER::toItemFromEntity).collect(Collectors.toList());
    }

    private OrderEntity getOrderEntity(UUID orderId) {
        Optional<OrderEntity> orderEntity = this.orderRepository.findById(orderId);
        if (orderEntity.isPresent()) {
            return orderEntity.get();
        }
        throw new RuntimeException("Order Not Found!");
    }

}

Controller

• Now, we can create some basic controllers and implement the generated apis.

package com.springboot.project.controller;


import com.springboot.project.generated.api.CustomerApi;
import com.springboot.project.generated.model.CustomerRequest;
import com.springboot.project.generated.model.CustomerResponse;
import com.springboot.project.mapper.AutoCustomerMapper;
import com.springboot.project.model.Customer;
import com.springboot.project.service.CustomerService;
import lombok.AllArgsConstructor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.RestController;

import java.util.UUID;

@RestController
@AllArgsConstructor(onConstructor = @__(@Autowired))
public class CustomerController implements CustomerApi {

    private final CustomerService customerService;

    @Override
    public ResponseEntity<CustomerResponse> addCustomer(CustomerRequest customerRequest) {
        Customer customer = AutoCustomerMapper.MAPPER.mapToCustomerFromRequest(customerRequest);
        customer = this.customerService.createCustomer(customer);
        CustomerResponse customerResponse = AutoCustomerMapper.MAPPER.mapToCustomerResponse(customer);
        return new ResponseEntity<>(customerResponse, HttpStatus.CREATED);
    }

    @Override
    public ResponseEntity<CustomerResponse> getCustomerInfo(UUID customerId) {
        Customer customer = this.customerService.getCustomer(customerId);
        CustomerResponse customerResponse = AutoCustomerMapper.MAPPER.mapToCustomerResponse(customer);
        return new ResponseEntity<>(customerResponse, HttpStatus.OK);

    }

    @Override
    public ResponseEntity<CustomerResponse> getCustomerInfoByEmail(String email) {
        Customer customer = this.customerService.findCustomerByEmail(email);
        CustomerResponse customerResponse = AutoCustomerMapper.MAPPER.mapToCustomerResponse(customer);
        return new ResponseEntity<>(customerResponse, HttpStatus.OK);
    }

}

package com.springboot.project.controller;


import com.springboot.project.generated.api.OrderApi;
import com.springboot.project.generated.model.OrderRequest;
import com.springboot.project.generated.model.OrderResponse;
import com.springboot.project.mapper.AutoOrderMapper;
import com.springboot.project.model.Order;
import com.springboot.project.service.OrderService;
import lombok.AllArgsConstructor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.RestController;

import java.util.UUID;

@RestController
@AllArgsConstructor(onConstructor = @__(@Autowired))
public class OrderController implements OrderApi {

    private final OrderService orderService;

    @Override
    public ResponseEntity<OrderResponse> addOrder(UUID customerId, OrderRequest orderRequest) {
        Order order = AutoOrderMapper.MAPPER.mapToOrderFromRequest(orderRequest);
        order = this.orderService.createOrder(customerId, order);
        OrderResponse orderResponse = AutoOrderMapper.MAPPER.mapToOrderResponse(order);
        return new ResponseEntity<>(orderResponse, HttpStatus.CREATED);
    }

    @Override
    public ResponseEntity<OrderResponse> getOrderDetail(UUID customerId, UUID orderId) {
        Order order = this.orderService.getOrderDetail(customerId, orderId);
        OrderResponse orderResponse = AutoOrderMapper.MAPPER.mapToOrderResponse(order);
        return new ResponseEntity<>(orderResponse, HttpStatus.OK);
    }

}

Testing

• Now, let's start the application and use a Database tool to check the tables.

• As we can see, Indexes in customers table are created with the name and unique is true as we configured in the CustomerEntity.

• Next, we can also see the index of column item_name is created when we use @Column(name = "item_name", unique = true) but the Index Name is a random name.

• Now, we will test how do the indexing help us in querying in database.

• Firstly, let's use the postman and with the request body as below.

{
    "fullName": "{{$randomFullName}}",
    "email": "{{$randomExampleEmail}}",
    "address": "{{$randomStreetAddress}}",
    "phone": "{{$randomPhoneNumber}}",
    "gender": "M",
    "dob": "1995-10-10"
}

• Postman supports us to random some common fields every time we send the request body.
• Now, let's run the api CreateCustomer for 10000 times. Then we will have 10000 records in the customers table.

• Now, let's run an sql command below to analyze the select query using an indexed column email as below.

explain ANALYZE select * from customers c where c.email = 'Greyson_McClure41@example.net';

• Then we can see the result as below.

Index Scan using uniquemultiindex on customers c  (cost=0.29..8.30 rows=1 width=97) (actual time=0.016..0.017 rows=1 loops=1)
  Index Cond: ((email)::text = 'Greyson_McClure41@example.net'::text)
Planning Time: 0.054 ms
Execution Time: 0.029 ms

• As you can see, finding a record in 10000 rows just took 0.029ms, it is so fast.
• Now, let's use the sql command below to to analyze the select query using no indexed column full_name as below.

explain ANALYZE select * from customers c where c.full_name = 'Katrina Kerluke';

• Then we can see the result as below.

Seq Scan on customers c  (cost=0.00..319.50 rows=1 width=97) (actual time=1.739..3.679 rows=1 loops=1)
  Filter: ((full_name)::text = 'Katrina Kerluke'::text)
  Rows Removed by Filter: 10994
Planning Time: 0.057 ms
Execution Time: 3.696 ms

• So, we can see querying without indexing will take 3.696ms, it is slower than 0.029ms using indexing very much.

Conclusion

• We have just known about what is index and how to apply indexing with Jpa.
• We also do some tests with indexing in database and see the different between indexed and no indexed.
• However, we also need to consider applying indexing:
  • Pros: Indexing improve performance for querying in the database.
  • Cons: Indexing will take more space of the table in database and it also can slow down the INSERT statements in database because:
    • When we INSERT new row, we are not also updating the table but also the indexes in that table.
    • Most indexes use some form of a tree data structure. When a new entry is inserted, this tree needs to be rebalanced. Depending on the type of the index and the distribution of data, The rebalancing operation can be costly.
    • If there are concurrent transactions happening on the same table, having an index can increase the chances of lock contention, which can also slow down INSERT operations.

See Also

• SB 3x JPA Native Query Postgres Optional Params
• SB 3x JPA Native Query Postgres Full Text Search

References

• learnsql.com
• guru99.com
• Full Source Code