메소드 이름으로 쿼리 생성

Spring/SpringDataJPA

메소드 이름으로 쿼리 생성

느리지만 꾸준하게 2022. 4. 15. 22:48

스프링 데이터 JPA는 먼저 메소드 이름으로 쿼리를 생성해준다.

순수한 JPA로 몇 살 이상인지를 판단하는 쿼리를 짜보면 아래와 같다.

public List<Member> findByUserNameAndAgeGreaterThen(String username, int age) {
        return em.createQuery("select m from Member m where m.username = :username and m.age > :age")
                .setParameter("username", username)
                .setParameter("username", username)
                .getResultList();
    }

MemberJpaRepositotryTest에서 테스트 코드를 작성해보면

Member Class에서 constructor을 생성해주고

public Member(String username, int age) {
        this.username = username;
        this.age = age;
    }

MemberJpaRepository를 아래와 같이 작성해주고

// MemberRepository

package study.datajpa.repository;


import org.springframework.stereotype.Repository;
import study.datajpa.entity.Member;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import java.util.List;
import java.util.Optional;

@Repository
public class MemberJpaRepository {

    @PersistenceContext
    // 엔티티 집어넣고 jpa가 db에다 insert query를 날려서 저장을 하게됨
    private EntityManager em;

    public Member save(Member member) {
        em.persist(member);
        return member;
    }

    public void delete(Member member) {
        em.remove(member);
    }

    public List<Member> findAll() {
        return em.createQuery("select m from Member m", Member.class)
                .getResultList();
    }

    public Optional<Member> findById(Long id) {
        Member member = em.find(Member.class, id);
        return Optional.ofNullable(member);
    }

    public long count() {
        return em.createQuery("select count(m) from Member m", Long.class)
                .getSingleResult();
    }



    public Member find(Long id) {
        // jpa가 알아서 Member entity에 맞는 select query를 db에다가 가져오게 된다.
        return em.find(Member.class, id);
    }

    public List<Member> findByUserNameAndAgeGreaterThen(String username, int age) {
        return em.createQuery("select m from Member m where m.username = :username and m.age > :age")
                .setParameter("username", username)
                .setParameter("age", age)
                .getResultList();
    }

}

MemberJpaRepotoryTest도 아래와 같이 작성

package study.datajpa.repository;


// junit의 jupiter test
import org.assertj.core.api.Assertions;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.annotation.Rollback;
import org.springframework.transaction.annotation.Transactional;
import study.datajpa.entity.Member;

import java.util.List;

import static org.assertj.core.api.Assertions.*;
import static org.junit.jupiter.api.Assertions.*;

@SpringBootTest
@Transactional
@Rollback(false)
class MemberJpaRepositoryTest {

    @Autowired
    MemberJpaRepository memberJpaRepository;

    @Test
    public void testMember() {
        Member member = new Member("memberA");
        Member savedMember = memberJpaRepository.save(member);

        Member findMember = memberJpaRepository.find(savedMember.getId());

        // 검증
        assertThat(findMember.getId()).isEqualTo(member.getId());
        assertThat(findMember.getUsername()).isEqualTo(member.getUsername());
        assertThat(findMember).isEqualTo(member);
    }

    @Test
    public void basicCRUD() {
        Member member1 = new Member("member1");
        Member member2 = new Member("member2");
        memberJpaRepository.save(member1);
        memberJpaRepository.save(member2);


        // 단건 조회 검증
        Member findMember1 = memberJpaRepository.findById(member1.getId()).get();
        Member findMember2 = memberJpaRepository.findById(member2.getId()).get();
        assertThat(findMember1).isEqualTo(member1);
        assertThat(findMember2).isEqualTo(member2);

        findMember1.setUsername("member!!!!!!!");



        // 리스트 조회 검증
        List<Member> all = memberJpaRepository.findAll();
        assertThat(all.size()).isEqualTo(2);

        // 카운트 검증
        long count = memberJpaRepository.count();
        assertThat(count).isEqualTo(2);

        // 삭제 검증
        memberJpaRepository.delete(member1);
        memberJpaRepository.delete(member2);

        long deleteCount = memberJpaRepository.count();
        assertThat(deleteCount).isEqualTo(0);

    }

    @Test
    public void findByUserNameAndAgeGreaterThen() {
        Member m1 = new Member("AAA", 10);
        Member m2 = new Member("AAA", 20);
        memberJpaRepository.save(m1);
        memberJpaRepository.save(m2);

        List<Member> result = memberJpaRepository.findByUserNameAndAgeGreaterThen("AAA", 15);

        // 실행하고자 하는 대상이 이름이 AAA이고 나이는 20살
        assertThat(result.get(0).getUsername()).isEqualTo("AAA");
        assertThat(result.get(0).getAge()).isEqualTo(20);
        assertThat(result.size()).isEqualTo(1);
    }
}

빌드된 JPQL을 보면 아래와 같다.

select
        member0_.member_id as member_i1_0_,
        member0_.age as age2_0_,
        member0_.tema_id as tema_id4_0_,
        member0_.username as username3_0_ 
    from
        member member0_ 
    where
        member0_.username=? 
        and member0_.age>?
        


// AAA에 15보다 큰 사람 해서 1명이 나오게 된다.
        select member0_.member_id as member_i1_0_, member0_.age as age2_0_, member0_.tema_id as tema_id4_0_, member0_.username as username3_0_ from member member0_ where member0_.username='AAA' and member0_.age>15;

MemberJpaRepository 처럼 코드를 작성해야하면 번거롭다.

public List<Member> findByUserNameAndAgeGreaterThen(String username, int age) {
    return em.createQuery("select m from Member m where m.username = :username and m.age > :age")
            .setParameter("username", username)
            .setParameter("age", age)
            .getResultList();
}

스프링 데이터 JPA를 써보자.

MemberRepository Interface로 가서 작성해주고

package study.datajpa.repository;


import org.springframework.data.jpa.repository.JpaRepository;
import study.datajpa.entity.Member;

import java.util.List;

public interface MemberRepository extends JpaRepository<Member, Long> {

    List<Member> findByUserNameAndAgeGreaterThen(String username, int age);
}

테스트 코드를 짜보면 아래와 같고 빌드를 돌렸을 때 에러가 나온다.

해결해야한다.(10:07 이전)

// MemberRepositoryTest

package study.datajpa.repository;

import org.assertj.core.api.Assertions;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.annotation.Rollback;
import org.springframework.transaction.annotation.Transactional;
import study.datajpa.entity.Member;

import java.util.List;

import static org.assertj.core.api.Assertions.*;
import static org.junit.jupiter.api.Assertions.*;


@SpringBootTest
@Transactional
@Rollback(false)
class MemberRepositoryTest {

    @Autowired
    MemberRepository memberRepository;

    @Test
    public void testMember() {
        System.out.println("memberRepository = " + memberRepository.getClass());
        Member member = new Member("memberA");
        Member savedMember = memberRepository.save(member);

        Member findMember = memberRepository.findById(savedMember.getId()).get();

        assertThat(findMember.getId()).isEqualTo(member.getId());
        assertThat(findMember.getUsername()).isEqualTo(member.getUsername());
        assertThat(findMember).isEqualTo(member);
    }


    @Test
    public void basicCRUD() {
        Member member1 = new Member("member1");
        Member member2 = new Member("member2");
        memberRepository.save(member1);
        memberRepository.save(member2);


        // 단건 조회 검증
        Member findMember1 = memberRepository.findById(member1.getId()).get();
        Member findMember2 = memberRepository.findById(member2.getId()).get();
        assertThat(findMember1).isEqualTo(member1);
        assertThat(findMember2).isEqualTo(member2);

        findMember1.setUsername("member!!!!!!!");



        // 리스트 조회 검증
        List<Member> all = memberRepository.findAll();
        assertThat(all.size()).isEqualTo(2);

        // 카운트 검증
        long count = memberRepository.count();
        assertThat(count).isEqualTo(2);

        // 삭제 검증
        memberRepository.delete(member1);
        memberRepository.delete(member2);

        long deleteCount = memberRepository.count();
        assertThat(deleteCount).isEqualTo(0);

    }

    @Test
    public void findByUserNameAndAgeGreaterThen() {
        Member m1 = new Member("AAA", 10);
        Member m2 = new Member("AAA", 20);
        memberRepository.save(m1);
        memberRepository.save(m2);

        List<Member> result = memberRepository.findByUserNameAndAgeGreaterThen("AAA", 15);

        // 실행하고자 하는 대상이 이름이 AAA이고 나이는 20살
        assertThat(result.get(0).getUsername()).isEqualTo("AAA");
        assertThat(result.get(0).getAge()).isEqualTo(20);
        assertThat(result.size()).isEqualTo(1);
    }
}

에러가 난 이유는 MemberRepository interface와 MemberRepositoryTest사이에서 아래 두개의 메서드 명을 다르게 해주었기 때문이다.

findByUsernameAndAgeGreaterThen

findByUsernameAndAgeGreaterThan

MemberRepository는 아래와 같다.

package study.datajpa.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import study.datajpa.entity.Member;

import java.util.List;

public interface MemberRepository extends JpaRepository<Member, Long> {

    List<Member> findByUsernameAndAgeGreaterThan(String username, int age);

    List<Member> findHelloBy();
}

MemberRepositoryTest는 아래와 같다.

package study.datajpa.repository;

import org.assertj.core.api.Assertions;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.annotation.Rollback;
import org.springframework.transaction.annotation.Transactional;
import study.datajpa.entity.Member;

import java.util.List;

import static org.assertj.core.api.Assertions.*;
import static org.junit.jupiter.api.Assertions.*;


@SpringBootTest
@Transactional
@Rollback(false)
class MemberRepositoryTest {

    @Autowired
    MemberRepository memberRepository;

    @Test
    public void testMember() {
        System.out.println("memberRepository = " + memberRepository.getClass());
        Member member = new Member("memberA");
        Member savedMember = memberRepository.save(member);

        Member findMember = memberRepository.findById(savedMember.getId()).get();

        assertThat(findMember.getId()).isEqualTo(member.getId());
        assertThat(findMember.getUsername()).isEqualTo(member.getUsername());
        assertThat(findMember).isEqualTo(member);
    }


    @Test
    public void basicCRUD() {
        Member member1 = new Member("member1");
        Member member2 = new Member("member2");
        memberRepository.save(member1);
        memberRepository.save(member2);

        // 단건 조회 검증
        Member findMember1 = memberRepository.findById(member1.getId()).get();
        Member findMember2 = memberRepository.findById(member2.getId()).get();
        assertThat(findMember1).isEqualTo(member1);
        assertThat(findMember2).isEqualTo(member2);

        findMember1.setUsername("member!!!!!!!");

        // 리스트 조회 검증
        List<Member> all = memberRepository.findAll();
        assertThat(all.size()).isEqualTo(2);

        // 카운트 검증
        long count = memberRepository.count();
        assertThat(count).isEqualTo(2);

        // 삭제 검증
        memberRepository.delete(member1);
        memberRepository.delete(member2);

        long deleteCount = memberRepository.count();
        assertThat(deleteCount).isEqualTo(0);

    }

    @Test
    public void findByUsernameAndAgeGreaterThen() {
        Member m1 = new Member("AAA", 10);
        Member m2 = new Member("AAA", 20);
        memberRepository.save(m1);
        memberRepository.save(m2);

        List<Member> result = memberRepository.findByUsernameAndAgeGreaterThan("AAA", 15);

        assertThat(result.get(0).getUsername()).isEqualTo("AAA");
        assertThat(result.get(0).getAge()).isEqualTo(20);
        assertThat(result.size()).isEqualTo(1);
    }

    @Test
    public void findHelloBy() {
        List<Member> helloBy = memberRepository.findHelloBy();
    }

}

빌드를 해보면 쿼리도 알맞게 나오고 실행결과도 알맞게 나오는 것을 확인할 수 있다.

// 실행결과 
select
        member0_.member_id as member_i1_0_,
        member0_.age as age2_0_,
        member0_.tema_id as tema_id4_0_,
        member0_.username as username3_0_ 
    from
        member member0_ 
    where
        member0_.username=? 
        and member0_.age>?


// 쿼리
select member0_.member_id as member_i1_0_, member0_.age as age2_0_, member0_.tema_id as tema_id4_0_, member0_.username as username3_0_ from member member0_ where member0_.username='AAA' and member0_.age>15;

쿼리 메서드 핉터 조건은 아래 사이트에서 참조하면 될 것이다.

https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.query-creation

Spring Data JPA - Reference Documentation

Example 109. Using @Transactional at query methods @Transactional(readOnly = true) interface UserRepository extends JpaRepository { List findByLastname(String lastname); @Modifying @Transactional @Query("delete from User u where u.active = false") void del

docs.spring.io

MemberRepositoryTest에서 findHelloBy 뒤에 들어갈 where 절을 쭉쭉 넣을 수도 있다. 일단 아래와 같이 설정을 하고 빌드를 돌리면 where절 빼고 결과가 나오게 된다.

@Test
    public void findHelloBy() {
        List<Member> helloBy = memberRepository.findHelloBy();
    }

select
        member0_.member_id as member_i1_0_,
        member0_.age as age2_0_,
        member0_.tema_id as tema_id4_0_,
        member0_.username as username3_0_ 
    from
        member member0_
        
        
select member0_.member_id as member_i1_0_, member0_.age as age2_0_, member0_.tema_id as tema_id4_0_, member0_.username as username3_0_ from member member0_;

스프링 데이터 JPA가 제공하는 쿼리 메소드 기능은 아래와 같다.

조회 : find... By, read... By, query...By, get...By,
findHelloBy 처럼...에 식별하기 위한 내용이 들어가도 된다.

COUNT: count...By 반환타입 long
EXISTS: exists...By 반환타입 boolean
삭제: delete... By, remove...By 반환타입 long
DISTINCT:findDistinct, findMemberDistinctBy
LIMIT: findFirst3, findFirst, findTop, findTop3

https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.limit-query-result

Spring Data JPA - Reference Documentation

docs.spring.io

findTop3라고 하고 빌드를 돌려보자.

@Test
    public void findByUsernameAndAgeGreaterThen() {
        Member m1 = new Member("AAA", 10);
        Member m2 = new Member("AAA", 20);
        memberRepository.save(m1);
        memberRepository.save(m2);

        List<Member> result = memberRepository.findByUsernameAndAgeGreaterThan("AAA", 15);

        assertThat(result.get(0).getUsername()).isEqualTo("AAA");
        assertThat(result.get(0).getAge()).isEqualTo(20);
        assertThat(result.size()).isEqualTo(1);
    }

    @Test
    public void findHelloBy() {
        List<Member> helloBy = memberRepository.findTop3HelloBy();
    }

MemberRepository Interface

public interface MemberRepository extends JpaRepository<Member, Long> {

    List<Member> findByUsernameAndAgeGreaterThan(String username, int age);

    List<Member> findTop3HelloBy();
}

select
        member0_.member_id as member_i1_0_,
        member0_.age as age2_0_,
        member0_.tema_id as tema_id4_0_,
        member0_.username as username3_0_ 
    from
        member member0_ limit ?
        
        
        

select member0_.member_id as member_i1_0_, member0_.age as age2_0_, member0_.tema_id as tema_id4_0_, member0_.username as username3_0_ from member member0_ limit 3;

<출처 김영한: 실전! 스프링 데이터 JPA >

https://www.inflearn.com/course/%EC%8A%A4%ED%94%84%EB%A7%81-%EB%8D%B0%EC%9D%B4%ED%84%B0-JPA-%EC%8B%A4%EC%A0%84/dashboard

실전! 스프링 데이터 JPA - 인프런 | 강의

스프링 데이터 JPA는 기존의 한계를 넘어 마치 마법처럼 리포지토리에 구현 클래스 없이 인터페이스만으로 개발을 완료할 수 있습니다. 그리고 반복 개발해온 기본 CRUD 기능도 모두 제공합니다.

www.inflearn.com

'Spring > SpringDataJPA' 카테고리의 다른 글

@Query, 리포지토리 메소드에 쿼리 정의하기 (0)	2022.04.17
JPA NamedQuery (0)	2022.04.17
공통 인터페이스 분석 (0)	2022.04.15
공통 인터페이스 설정 & 적용 (0)	2022.04.15
공통 인터페이스 기반 - 순수 JPA 기반 레포지토리 만들기 (0)	2022.04.15

현재글메소드 이름으로 쿼리 생성

Jay

AWS, 항해플러스, javascript, springboot, ORM, Spring, Post, http, jdbc, SpringMVC, SpringDataJpa, API, ec2, transaction, thymeleaf, MVC, SpringDB, JPA, querydsl, servlet,

Today :
Yesterday :

일	월	화	수	목	금	토
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30

Jay