Query Creation By Method Name

By Default JPARepositories interface will provide some predefined method such as save(), findAll(), findById(), and findOne etc.. In case if we need to load data based on other property using emailAddress or phoneNumber etc, then we need to create our own user defined findXXX() methods in the repository like findByEmailAddress(), findByMobileNumber(), findByFirstName(), etc..

Below keywords will be used to create the different user defined method in repository
Keyword Sample JPQL snippet
Distinct findDistinctByLastnameAndFirstname select distinct …​ where x.lastname = ?1 and x.firstname = ?2
And findByLastnameAndFirstname … where x.lastname = ?1 and x.firstname = ?2
Or findByLastnameOrFirstname … where x.lastname = ?1 or x.firstname = ?2
Is, Equals findByFirstname,findByFirstnameIs,findByFirstnameEquals … where x.firstname = ?1
Between findByStartDateBetween … where x.startDate between ?1 and ?2
LessThan findByAgeLessThan … where x.age < ?1
LessThanEqual findByAgeLessThanEqual … where x.age <= ?1
GreaterThan findByAgeGreaterThan … where x.age > ?1
GreaterThanEqual findByAgeGreaterThanEqual … where x.age >= ?1
After findByStartDateAfter … where x.startDate > ?1
Before findByStartDateBefore … where x.startDate < ?1
IsNull, Null findByAge(Is)Null … where x.age is null
IsNotNull, NotNull findByAge(Is)NotNull … where x.age is not null
Like findByFirstnameLike … where x.firstname like ?1
NotLike findByFirstnameNotLike … where x.firstname not like ?1
StartingWith findByFirstnameStartingWith … where x.firstname like ?1 (parameter bound with appended %)
EndingWith findByFirstnameEndingWith … where x.firstname like ?1 (parameter bound with prepended %)
Containing findByFirstnameContaining … where x.firstname like ?1 (parameter bound wrapped in %)
OrderBy findByAgeOrderByLastnameDesc … where x.age = ?1 order by x.lastname desc
Not findByLastnameNot … where x.lastname <> ?1
In findByAgeIn(Collection<Age> ages) … where x.age in ?1
NotIn findByAgeNotIn(Collection ages) … where x.age not in ?1
True findByActiveTrue() … where x.active = true
False findByActiveFalse() … where x.active = false
IgnoreCase findByFirstnameIgnoreCase … where UPPER(x.firstname) = UPPER(?1)
Lets see an example

First, we need to create a simple SprintBoot and SprintDataJPA applicaiton using MySql, which can be created with the help of following tutorial Customer Registration on top of this we need to make changes to implement a query by method name concept

First will introduce a new enpoint in controller to load data based on the emailAddress like below: 


                    /**
                    * fetch data based on the emailAddress, using QueryByMethod name concept.
                    *
                    * @param emailAddress customer email address
                    * @return response entity object
                    */
                   @GetMapping("/getByEmail/{emailAddress}")
                   public ResponseEntity<APIResponse> getCustomerByEmail(@PathVariable String emailAddress) {
                      return customerService.getCustomerByEmail(emailAddress);
                   }

Introduce new method in service and service Implementation class so that we can call the user defined query method from repository


                    ResponseEntity<APIResponse> getCustomerByEmail(String emailAddress);

Service Implementation class


                    /**
                    * This method is used to call the user defined Query method from repository.
                    *
                    * @param emailAddress customer email address
                    * @return responseEntity object
                    */
                    @Override
                    public ResponseEntity<APIResponse> getCustomerByEmail(String emailAddress) {
                        Optional<CustomerModel> optional = customerRepository.findByCustomerEmailAddress(emailAddress);

                        if (!optional.isPresent()) {
                            return ResponseEntity.ok(
                                    APIResponse.builder()
                                            .errorCode(CUSTOMER_EMAIL_NOT_EXISTS_CODE)
                                            .errorMessage(CUSTOMER_EMAIL_NOT_EXISTS)
                                            .data(List.of())
                                            .build()
                            );
                        }

                        CustomerModel model = optional.get();
                        CustomerResponse response = modelToResponseMapper(model);
                        return ResponseEntity.ok(
                                APIResponse.builder()
                                        .errorCode(SUCCESS_CODE)
                                        .errorMessage(SUCCESSFULLY_RETRIEVED)
                                        .data(response)
                                        .build()
                        );
                    }

Add new error code and error message in APPConstants.java file


                    public static final Integer CUSTOMER_EMAIL_NOT_EXISTS_CODE = 901;
                    public static final String CUSTOMER_EMAIL_NOT_EXISTS = "Customer email address not exists";

Lets implement a PATCH HTTP request now

To implement a PATCH HTTP request, first we need to create a new enpoint in controller like below. 


                    /**
                    * update a specific field from the entity, using PATCH
                    *
                    * @param customerId customer id
                    * @param customerAddress customer address object
                    * @return response entity object
                    */
                   @PatchMapping("/update/address/{customerId}")
                   public ResponseEntity<APIResponse> updateAddress(@PathVariable long customerId, @RequestBody CustomerAddress customerAddress) {
                       return customerService.updateCustomerAddress(customerId, customerAddress);
                   }

Add user defined method CustomerRepository interface, like below


                    package com.sb.sdjpa.crud.repository;

                    import com.sb.sdjpa.crud.model.CustomerModel;
                    import org.springframework.data.jpa.repository.JpaRepository;

                    import java.util.Optional;

                    public interface CustomerRepository extends JpaRepository {
                        Optional<CustomerModel> findByCustomerEmailAddress(String emailAddress);
                    }

Testing through postman

By using this http://localhost:8090/api/v1/customer/getByEmail/{emailAddress} endpoint we can make a request to load the data based on the emailAddress.


If we pass invalid email address, which is not registered with system then will receive a message call Customer email address not exists


Full source code is available in follwong GitHub repository: SpringBoot Query By Method Name