Spring Data Jpa Query Using Named Parameters

Spring Data Jpa Provides @Query to provide vendor-specific native queries to the repository methods. but by default, @Query uses position-based parameter binding ie first method parameter will be bind to the first parameter of native query. This makes the native query methods a little error-prone because a slight change in positions will alter the query and the outcome. To solve this issue, Spring Data Jpa Provides @Param annotation which will bind the parameters based on names instead of position.

drop database if exists AppleDb;
create database AppleDb;
use AppleDb;
CREATE TABLE apple ( apple_id BIGINT NOT NULL, apple_name VARCHAR(255) DEFAULT NULL, taste VARCHAR(255) DEFAULT NULL, PRIMARY KEY (apple_id)) ENGINE=INNODB;
CREATE TABLE hibernate_sequence ( next_val BIGINT) ENGINE=INNODB;

insert into hibernate_sequence values ( 1 );
insert into apple (apple_name, apple_id, taste) values ("Macintosh", 1, "tangy");
insert into apple (apple_name, apple_id, taste) values ("Macintosh", 2, "sweet");
insert into apple (apple_name, apple_id, taste) values ("Macintosh", 3, "juicy");
insert into apple (apple_name, apple_id, taste) values ("Macintosh", 4, "firm sweet");
insert into apple (apple_name, apple_id, taste) values ("Macintosh", 5, "sharp");

package com.sujan.example.jpa.controller;

import com.sujan.example.jpa.entity.Apple;
import com.sujan.example.jpa.repository.AppleRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Sort;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class AppleController {
    @Autowired
    private AppleRepository appleRepository;

    @GetMapping
    void Query() {
        List<Apple> findByAppleNameAndTasteSwitchedParam = appleRepository.findByAppleNameAndTasteSwitchedParam("sweet", "Macintosh");
        System.out.println("findByAppleNameAndTasteSwitchedParam = " + findByAppleNameAndTasteSwitchedParam);

        List<Apple> findByAppleNameAndTaste = appleRepository.findByAppleNameAndTaste("Macintosh", "sweet");
        System.out.println("findByAppleNameAndTaste = " + findByAppleNameAndTaste);

        List<Apple> findByAppleNameAndSortByTaste = appleRepository.findByAppleNameAndSortByTaste("Macintosh", Sort.by(new String[]{"taste"}));
        System.out.println("findByAppleNameAndSortByTaste = " + findByAppleNameAndSortByTaste);
    }
}

package com.sujan.example.jpa.entity;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Data
@Entity
@ToString
@NoArgsConstructor
@AllArgsConstructor()
public class Apple {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long appleId;
    private String appleName;
    private String taste;
}

package com.sujan.example.jpa.repository;

import com.sujan.example.jpa.entity.Apple;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface AppleRepository extends JpaRepository<Apple, Long> {
    @Query("select a from Apple a where a.appleName = :appleName and a.taste = :taste")
    List<Apple> findByAppleNameAndTasteSwitchedParam(@Param("taste") String taste, @Param("appleName") String appleName);

    @Query("select a from Apple a where a.appleName = :appleName and a.taste = :taste")
    List<Apple> findByAppleNameAndTaste(@Param("appleName") String appleName, @Param("taste") String taste);

    @Query("select a from Apple a where a.appleName = :appleName")
    List<Apple> findByAppleNameAndSortByTaste(@Param("appleName") String appleName, Sort sort);
}

package com.sujan.example.jpa;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class JpaApplication {
    public static void main(String[] args) {
        SpringApplication.run(JpaApplication.class, args);
    }
}

spring.datasource.url=jdbc:mysql://localhost:3306/AppleDb
spring.datasource.username=root
spring.datasource.password=root

plugins {
    id 'org.springframework.boot' version '2.3.3.RELEASE'
    id 'io.spring.dependency-management' version '1.0.10.RELEASE'
    id 'java'
}
group = 'com.sujan'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '11'
configurations {
    compileOnly {
        extendsFrom annotationProcessor
    }
}
repositories {
    mavenCentral()
}
dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-web'
    annotationProcessor 'org.projectlombok:lombok'
    compileOnly 'org.projectlombok:lombok'
    implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
    runtimeOnly 'mysql:mysql-connector-java'
    testImplementation('org.springframework.boot:spring-boot-starter-test') {
        exclude group: 'org.junit.vintage', module: 'junit-vintage-engine'
    }
}
test {
    useJUnitPlatform()
}

curl --location --request GET 'http://localhost:8080/'

select apple0_.apple_id as apple_id1_0_, apple0_.apple_name as apple_na2_0_, apple0_.taste as taste3_0_ from apple apple0_ where apple0_.apple_name=? and apple0_.taste=?
findByAppleNameAndTasteSwitchedParam = [Apple(appleId=2, appleName=Macintosh, taste=sweet)]
select apple0_.apple_id as apple_id1_0_, apple0_.apple_name as apple_na2_0_, apple0_.taste as taste3_0_ from apple apple0_ where apple0_.apple_name=? and apple0_.taste=?
findByAppleNameAndTaste = [Apple(appleId=2, appleName=Macintosh, taste=sweet)]
select apple0_.apple_id as apple_id1_0_, apple0_.apple_name as apple_na2_0_, apple0_.taste as taste3_0_ from apple apple0_ where apple0_.apple_name=? order by apple0_.taste asc
findByAppleNameAndSortByTaste = [Apple(appleId=4, appleName=Macintosh, taste=firm sweet), Apple(appleId=3, appleName=Macintosh, taste=juicy), Apple(appleId=5, appleName=Macintosh, taste=sharp), Apple(appleId=2, appleName=Macintosh, taste=sweet), Apple(appleId=1, appleName=Macintosh, taste=tangy)]