@Query, 값, DTO 조회하기 & 파라미터 바인딩

@Query를 넣어서 단순한 값을 조회하는 코드를 작성해보자.

 

MemberRepository

public interface MemberRepository extends JpaRepository<Member, Long> {

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

    List<Member> findTop3HelloBy();

    List<Member> findByUsername(@Param("username") String username);

    @Query("select m from Member m where m.username = :username and m.age = :age")
    List<Member> findUser(@Param("username") String username, @Param("age") int age);

    @Query("select m.username from Member m")
    List<String> findUsernameList();
}

 

MemberRepositoryTest

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

        List<String> usernameList = memberRepository.findUsernameList();
        for (String s : usernameList) {
            System.out.println("s = " + s);
        }
    }

 

 

run 실행시

select member0_.username as col_0_0_ from member member0_
select member0_.username as col_0_0_ from member member0_;
2022-04-17 15:16:51.006 TRACE 35276 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([col_0_0_] : [VARCHAR]) - [AAA]
2022-04-17 15:16:51.006 TRACE 35276 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([col_0_0_] : [VARCHAR]) - [BBB]
s = AAA
s = BBB

 

 

DTO들을 조회하는 코드를 작성해보자.

dto package에서 MemberDto class를 만들고

// MemberDto

package study.datajpa.dto;

import lombok.Data;

@Data
public class MemberDto {

    private Long id;
    private String username;
    private String teamName;

    public MemberDto(Long id, String username, String teamName) {
        this.id = id;
        this.username = username;
        this.teamName = teamName;
    }
}

MemberRespositry Interface에서 new operation을 작성하고 아래와 같이 설정

public interface MemberRepository extends JpaRepository<Member, Long> {

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

    List<Member> findTop3HelloBy();

    List<Member> findByUsername(@Param("username") String username);

    @Query("select m from Member m where m.username = :username and m.age = :age")
    List<Member> findUser(@Param("username") String username, @Param("age") int age);

    @Query("select m.username from Member m")
    List<String> findUsernameList();

// new operation 설정
    @Query("select new study.datajpa.dto.MEmberDto(m.id, m.username, t.name) from Member m join m.team t")
    List<MemberDto> findMemberDto();
}

 

MEmberRepositoryTest에서 Dto를 출력하면

@Autowired MemberRepository memberRepository;
// team repository 만들어주고
@Autowired TeamRepository teamRepository;


@Test
    public void findMemberDto() {
        Team team = new Team("teamA");
        teamRepository.save(team);

        Member m1 = new Member("AAA", 10);
        m1.setTeam(team);
        memberRepository.save(m1);

        List<MemberDto> memberDto = memberRepository.findMemberDto();
        for (MemberDto dto : memberDto) {
            System.out.println("dto = " + dto);
        }
    }

run 실행시 dto 값이 보이게 된다.

    select
        member0_.member_id as col_0_0_,
        member0_.username as col_1_0_,
        team1_.name as col_2_0_ 
    from
        member member0_ 
    inner join
        team team1_ 
            on member0_.tema_id=team1_.team_id
2022-04-17 15:36:17.551  INFO 35557 --- [           main] p6spy                                    : #1650177377551 | took 0ms | statement | connection 3| url jdbc:h2:tcp://localhost/~/datajpa
select member0_.member_id as col_0_0_, member0_.username as col_1_0_, team1_.name as col_2_0_ from member member0_ inner join team team1_ on member0_.tema_id=team1_.team_id
select member0_.member_id as col_0_0_, member0_.username as col_1_0_, team1_.name as col_2_0_ from member member0_ inner join team team1_ on member0_.tema_id=team1_.team_id;
2022-04-17 15:36:17.552 TRACE 35557 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([col_0_0_] : [BIGINT]) - [2]
2022-04-17 15:36:17.552 TRACE 35557 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([col_1_0_] : [VARCHAR]) - [AAA]
2022-04-17 15:36:17.552 TRACE 35557 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([col_2_0_] : [VARCHAR]) - [teamA]


dto = MemberDto(id=2, username=AAA, teamName=teamA)

MemberDto class에서 지정한 @Data이 toString이랑 이것저것 해주기 때문에 dto 값이 보이게 된다.

@Data
public class MemberDto {

    private Long id;
    private String username;
    private String teamName;

    public MemberDto(Long id, String username, String teamName) {
        this.id = id;
        this.username = username;
        this.teamName = teamName;
    }
}

select
        member0_.member_id as col_0_0_,
        member0_.username as col_1_0_,
        team1_.name as col_2_0_ 
    from
        member member0_ 
    inner join
        team team1_ 
            on member0_.tema_id=team1_.team_id

 

 

 

 

파라미터 바인딩

• 이름 기반을 사용하도록 하자

 

 

컬렉션 파라미터 바인딩이란 뭘까??

in절로 여러개의 기능을 조회하고 싶을 때 쓰는 기능인건 알겠다. 한번 써보자.

// MemberRepository

@Query("select m from Member m where m.username in :names")
    List<Member> findByNames(@Param("names") Collection<String> names);

// MemberRepositoryTest

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

        List<Member> result = memberRepository.findByNames(Arrays.asList("AAA", "BBB"));
        for (Member member : result) {
            System.out.println("member = " + member);
        }
    }

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 in (
            ? , ?
        )

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

<출처 김영한: 실전! 스프링 데이터 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 - 인프런 | 강의