Query Annotation

The @Query annotation can only be used to annotate repository interface methods. The call of the annotated methods will trigger the execution of the statement found in it, and their usage is pretty straightforward.

The @Query annotation supports both native SQL and JPQL. When native SQL is used, the nativeQuery parameter of the annotation should be set to true: 


                    @Query("NATIVE_QUERY...", nativeQuery=true)
                    List<Entity> findAllByName(String name);

To select all customers from a database, we can use either a native query or JPQL:


                    @Query("SELECT(*) FROM customer_details", nativeQuery=true)
                    List<CustomerModel> findAll();

                    @Query("SELECT customerName FROM CustomerModel model")
                    List<CustomerModel> findAll();

When using position-based parameters, you have to keep track of the order in which you supply the parameters in. The first parameter passed to the method is mapped to ?1, the second is mapped to ?2, etc. If you accidentally switch these up - your query will likely throw an exception, or silently produce wrong results. 


                    @Query("SELECT c FROM CustomerModel c WHERE c.customerName = ?1 AND c.customerAge = ?2")
                    List<CustomerModel> findAll(String customerName, int customerAge);

Named parameters are, well, named and can be referenced by name, no matter their position. The name within the @Param annotation is matched to the named parameters in the @Query annotation, so you're free to call your variables however you'd like - but for consistency's sake - it's advised to use the same name. If you don't supply a matching @Param for a named parameter in the query - an exception is thrown at compile-time: 


                    @Query("SELECT c FROM CustomerModel c WHERE c.customerName = :name and c.customerAge = :age")
                    List<CustomerModel> findByName(@Param("name") String name, @Param("age") int age);

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 fetch data using Native and JPQL queries.

first will JPQL queries inside the repository. 


                    @Query("SELECT c FROM CustomerModel c WHERE c.customerName =?1 and c.customerAge =?2")
                    Optional<CustomerModel> findByCustomers(String customerName, int customerAge);
                
                    @Query("SELECT c FROM CustomerModel c WHERE c.customerName = :name and c.customerAge = :age")
                    Optional<CustomerModel> findByNamedParameters(@Param("name") String customerName, @Param("age") int customerAge);
                
                    @Query(value = "SELECT * from customer_details c WHERE c.customer_mobile_number = :mobileNumber", nativeQuery = true)
                    Optional<CustomerModel> findBasedOnMobileNumber(@Param("mobileNumber") String mobileNumber);
                
                    @Query(value = "SELECT count(*) FROM customer_details", nativeQuery = true)
                    Long findAllCustomersCount();

Introduce the new endpoints in customer controller.


                    /**
                    * fetch customers using position based parameters from repository.
                    *
                    * @param name customer name
                    * @param age customer age
                    * @return responseEntity object
                    */
                    @GetMapping("/position-based/parameters/{name}/{age}")
                    public ResponseEntity<APIResponse> getCustomerUsingPositionBasedParameters(@PathVariable String name, @PathVariable int age) {
                        return customerService.getCustomerUsingPositionBasedParameters(name, age);
                    }

                    /**
                    * fetch customer from database using named parameters.
                    *
                    * @param name customer name
                    * @param age customer age
                    * @return responseEntity object
                    */
                    @GetMapping("/named/parameters/{name}/{age}")
                    public ResponseEntity<APIResponse> getCustomerUsingNamedParameters(@PathVariable String name, @PathVariable int age) {
                        return customerService.getCustomerUsingNamedParameters(name, age);
                    }

                    /**
                    * fetch customer from database using native query.
                    *
                    * @param mobileNumber customer name
                    * @return responseEntity object
                    */
                    @GetMapping("/native/query/{mobileNumber}")
                    public ResponseEntity<APIResponse> getCustomerNativeQuery(@PathVariable String mobileNumber) {
                        return customerService.getCustomerUsingNativeQuery(mobileNumber);
                    }

                    /**
                    * fetch total customer count.
                    *
                    * @return responseEntity object
                    */
                    @GetMapping("/total/count")
                    public ResponseEntity<APIResponse> getTotalCustomerCount() {
                        return customerService.getTotalCustomerCount();
                    }

Introduce the methods inside the service interface


                      ResponseEntity<APIResponse> getCustomerUsingPositionBasedParameters(String customerName, int customerAge);
                      ResponseEntity<APIResponse> getCustomerUsingNamedParameters(String customerName, int customerAge);
                      ResponseEntity<APIResponse> getCustomerUsingNativeQuery(String mobileNumber);
                      ResponseEntity<APIResponse> getTotalCustomerCount();

customer service implementation class changes


                    /**
                    * fetch customers using position based parameters from repository.
                    *
                    * @param customerName customer name
                    * @param customerAge customer age
                    * @return responseEntity object
                    */
                    @Override
                    public ResponseEntity<APIResponse> getCustomerUsingPositionBasedParameters(String customerName, int customerAge) {
                        Optional<CustomerModel> optionalCustomerModel = customerRepository.findByCustomers(customerName, customerAge);

                        return optionalCustomerModel.map(customerModel -> ResponseEntity.ok(
                                APIResponse.builder()
                                        .errorCode(SUCCESS_CODE)
                                        .errorMessage(SUCCESSFULLY_RETRIEVED)
                                        .data(modelToResponseMapper(customerModel))
                                        .build()
                        )).orElseGet(() -> ResponseEntity.ok(
                                APIResponse.builder()
                                        .errorCode(CUSTOMER_NOT_EXISTS_CODE)
                                        .errorMessage(CUSTOMER_NOT_EXISTS)
                                        .data(List.of())
                                        .build()
                        ));
                    }

                    /**
                    * fetch customer from database using named parameters.
                    *
                    * @param customerName customer name
                    * @param customerAge customer age
                    * @return responseEntity object
                    */
                    @Override
                    public ResponseEntity<APIResponse> getCustomerUsingNamedParameters(String customerName, int customerAge) {
                        Optional<CustomerModel> optionalCustomerModel = customerRepository.findByNamedParameters(customerName, customerAge);

                        return optionalCustomerModel.map(customerModel -> ResponseEntity.ok(
                                APIResponse.builder()
                                        .errorCode(SUCCESS_CODE)
                                        .errorMessage(SUCCESSFULLY_RETRIEVED)
                                        .data(modelToResponseMapper(customerModel))
                                        .build()
                        )).orElseGet(() -> ResponseEntity.ok(
                                APIResponse.builder()
                                        .errorCode(CUSTOMER_NOT_EXISTS_CODE)
                                        .errorMessage(CUSTOMER_NOT_EXISTS)
                                        .data(List.of())
                                        .build()
                        ));
                    }

                    /**
                    * fetch customer from database using native query.
                    *
                    * @param mobileNumber customer name
                    * @return responseEntity object
                    */
                    @Override
                    public ResponseEntity<APIResponse> getCustomerUsingNativeQuery(String mobileNumber) {
                        Optional<CustomerModel> optionalCustomerModel = customerRepository.findBasedOnMobileNumber(mobileNumber);

                        return optionalCustomerModel.map(customerModel -> ResponseEntity.ok(
                                APIResponse.builder()
                                        .errorCode(SUCCESS_CODE)
                                        .errorMessage(SUCCESSFULLY_RETRIEVED)
                                        .data(modelToResponseMapper(customerModel))
                                        .build()
                        )).orElseGet(() -> ResponseEntity.ok(
                                APIResponse.builder()
                                        .errorCode(CUSTOMER_NOT_EXISTS_CODE)
                                        .errorMessage(CUSTOMER_NOT_EXISTS)
                                        .data(List.of())
                                        .build()
                        ));
                    }

                    /**
                    * fetch total customer count.
                    *
                    * @return responseEntity object
                    */
                    public ResponseEntity<APIResponse> getTotalCustomerCount() {
                        Long totalCustomer = customerRepository.findAllCustomersCount();
                        return ResponseEntity.ok(
                                APIResponse.builder()
                                        .errorCode(SUCCESS_CODE)
                                        .errorMessage(SUCCESSFULLY_RETRIEVED)
                                        .data(CustomersCount.builder().totalCustomers(totalCustomer).build())
                                        .build()
                        );
                    }

CustomerCount.java

Introduce one new CustomerCount reponse object, so that we can map our total customer count and send it to the client. 


                    package com.sb.sdjpa.crud.response;

                    import lombok.*;

                    @Getter
                    @Setter
                    @NoArgsConstructor
                    @AllArgsConstructor
                    @ToString
                    @EqualsAndHashCode
                    @Builder(toBuilder = true)
                    public class CustomersCount {
                        private long totalCustomers;
                    }

Testing through postman

Fetch customers using position based parameters


Fetch customers using named parameters


Fetch customers using native query


Fetch total number of customer


Full source code is available in follwong GitHub repository: SpringBoot Query Annotation