Spring

★[스프링] Query predicate keywords

웨일파도 2023. 7. 11. 15:11
반응형

@Builder
@NoArgsConstructor
@AllArgsConstructor
@Data
@Entity
public class Member {
    @Id
    private Long id;
    private String name;
    private String email;
    private LocalDateTime createAt;
    private LocalDateTime updateAt;
}

 

 

1. keyword: AND, OR
List<Member> findByNameAndEmail(String name, String email);
List<Member> findByNameOrEmail(String name, String email);
List<Member> memberList = memberRepository.findByNameAndEmail("홍길동","mars@thejoeun.com");
memberList.forEach(System.out::println);

List<Member> memberList2 = memberRepository.findByNameOrEmail("홍길동","mars@thejoeun.com");
memberList2.forEach(System.out::println);

WHERE 절에 AND / OR 

/* <criteria> */ 
select 
  m1_0.id,m1_0.create_at,m1_0.email,m1_0.name,m1_0.update_at 
from 
  member m1_0 
where 
  m1_0.name='홍길동' and m1_0.email='mars@thejoeun.com';
Member(id=1, name=홍길동, email=mars@thejoeun.com, createAt=2023-07-11T15:08:34, updateAt=2023-07-11T15:08:34)

/* <criteria> */ 
select 
  m1_0.id,m1_0.create_at,m1_0.email,m1_0.name,m1_0.update_at 
from 
  member m1_0 
where 
  m1_0.name='홍길동' or m1_0.email='mars@thejoeun.com';
Member(id=1, name=홍길동, email=mars@thejoeun.com, createAt=2023-07-11T15:08:34, updateAt=2023-07-11T15:08:34)
Member(id=5, name=홍길동, email=ryukwansun@thejoeun.com, createAt=2023-07-11T15:08:34, updateAt=2023-07-11T15:08:34)
2. keyword: after, greater, equal, between
List<Member> findByCreateAtAfter(LocalDateTime yesterday);
List<Member> findByCreateAtIsAfter(LocalDateTime yesterday);

List<Member> findByCreateAtGreaterThan(LocalDateTime yesterday);
List<Member> findByCreateAtGreaterThanEqual(LocalDateTime yesterday);
List<Member> findByIdGreaterThanEqual(Long id);

List<Member> findByCreateAtBetween(LocalDateTime yesterday, LocalDateTime tomorrow);
@Test
    void jpaQueryMethodTest4() {
        System.out.println("1. CreateAt + After----------------------------");
        // CreateAt + After
        List<Member> memberList1 = memberRepository.findByCreateAtAfter(LocalDateTime.now().minusDays(1L));
        memberList1.forEach(s -> System.out.println(s));

        System.out.println("2. CreateAt + IsAfter----------------------------");
        // CreateAt + IsAfter
        List<Member> memberList2 = memberRepository.findByCreateAtIsAfter(LocalDateTime.now().minusDays(1L));
        memberList2.forEach(s -> System.out.println(s));

        System.out.println("3. CreateAt + GreaterThan----------------------------");
        // CreateAt + GreaterThan
        List<Member> memberList3 = memberRepository.findByCreateAtGreaterThan(LocalDateTime.now().minusDays(1L));
        memberList3.forEach(s -> System.out.println(s));

        System.out.println("4. CreateAt + GreaterThanEqual----------------------------");
        // CreateAt + GreaterThanEqual
        List<Member> memberList4 = memberRepository.findByCreateAtGreaterThanEqual(LocalDateTime.now().minusDays(1L));
        memberList4.forEach(s -> System.out.println(s));

        System.out.println("5. GreaterThanEqual----------------------------");
        // GreaterThanEqual
        List<Member> memberList5 = memberRepository.findByIdGreaterThanEqual(3L);
        memberList5.forEach(s -> System.out.println(s));

        System.out.println("6. CreateAt + Between----------------------------");
        // CreateAt + Between
        List<Member> memberList6 = memberRepository.findByCreateAtBetween(LocalDateTime.now().minusDays(1L), LocalDateTime.now().plusDays(1L));
        memberList6.forEach(s -> System.out.println(s));
    }

 

1. CreateAt + After---------------------------- (>)
/* <criteria> */ 
select m1_0.id,m1_0.create_at,m1_0.email,m1_0.name,m1_0.update_at 
from member m1_0 
where m1_0.create_at>'2023-07-10T15:18:45.314+0900';
Member(id=1, name=홍길동, email=mars@thejoeun.com, createAt=2023-07-11T15:18:44, updateAt=2023-07-11T15:18:44)
Member(id=2, name=박남순, email=namsun@thejoeun.com, createAt=2023-07-11T15:18:44, updateAt=2023-07-11T15:18:44)
Member(id=3, name=이순신, email=leesunsin@gmail.com, createAt=2023-07-11T15:18:44, updateAt=2023-07-11T15:18:44)
Member(id=4, name=강감찬, email=namsun@thejoeun.com, createAt=2023-07-11T15:18:44, updateAt=2023-07-11T15:18:44)
Member(id=5, name=홍길동, email=ryukwansun@thejoeun.com, createAt=2023-07-11T15:18:44, updateAt=2023-07-11T15:18:44)
Member(id=6, name=유관순1, email=ryukwansun1@thejoeun.com, createAt=2023-07-11T15:18:42, updateAt=2023-07-11T15:18:42)
Member(id=7, name=유관순2, email=ryukwansun2@thejoeun.com, createAt=2023-07-11T15:18:42, updateAt=2023-07-11T15:18:42)

2. CreateAt + IsAfter----------------------------(>)
/* <criteria> */ 
select m1_0.id,m1_0.create_at,m1_0.email,m1_0.name,m1_0.update_at 
from member m1_0 
where m1_0.create_at>'2023-07-10T15:18:45.489+0900';
Member(id=1, name=홍길동, email=mars@thejoeun.com, createAt=2023-07-11T15:18:44, updateAt=2023-07-11T15:18:44)
Member(id=2, name=박남순, email=namsun@thejoeun.com, createAt=2023-07-11T15:18:44, updateAt=2023-07-11T15:18:44)
Member(id=3, name=이순신, email=leesunsin@gmail.com, createAt=2023-07-11T15:18:44, updateAt=2023-07-11T15:18:44)
Member(id=4, name=강감찬, email=namsun@thejoeun.com, createAt=2023-07-11T15:18:44, updateAt=2023-07-11T15:18:44)
Member(id=5, name=홍길동, email=ryukwansun@thejoeun.com, createAt=2023-07-11T15:18:44, updateAt=2023-07-11T15:18:44)
Member(id=6, name=유관순1, email=ryukwansun1@thejoeun.com, createAt=2023-07-11T15:18:42, updateAt=2023-07-11T15:18:42)
Member(id=7, name=유관순2, email=ryukwansun2@thejoeun.com, createAt=2023-07-11T15:18:42, updateAt=2023-07-11T15:18:42)

3. CreateAt + GreaterThan----------------------------(>)
/* <criteria> */ 
select m1_0.id,m1_0.create_at,m1_0.email,m1_0.name,m1_0.update_at 
from member m1_0 
where m1_0.create_at>'2023-07-10T15:18:45.494+0900';
Member(id=1, name=홍길동, email=mars@thejoeun.com, createAt=2023-07-11T15:18:44, updateAt=2023-07-11T15:18:44)
Member(id=2, name=박남순, email=namsun@thejoeun.com, createAt=2023-07-11T15:18:44, updateAt=2023-07-11T15:18:44)
Member(id=3, name=이순신, email=leesunsin@gmail.com, createAt=2023-07-11T15:18:44, updateAt=2023-07-11T15:18:44)
Member(id=4, name=강감찬, email=namsun@thejoeun.com, createAt=2023-07-11T15:18:44, updateAt=2023-07-11T15:18:44)
Member(id=5, name=홍길동, email=ryukwansun@thejoeun.com, createAt=2023-07-11T15:18:44, updateAt=2023-07-11T15:18:44)
Member(id=6, name=유관순1, email=ryukwansun1@thejoeun.com, createAt=2023-07-11T15:18:42, updateAt=2023-07-11T15:18:42)
Member(id=7, name=유관순2, email=ryukwansun2@thejoeun.com, createAt=2023-07-11T15:18:42, updateAt=2023-07-11T15:18:42)

4. CreateAt + GreaterThanEqual----------------------------(>=)
/* <criteria> */ 
select m1_0.id,m1_0.create_at,m1_0.email,m1_0.name,m1_0.update_at 
from member m1_0 
where m1_0.create_at>='2023-07-10T15:18:45.499+0900';
Member(id=1, name=홍길동, email=mars@thejoeun.com, createAt=2023-07-11T15:18:44, updateAt=2023-07-11T15:18:44)
Member(id=2, name=박남순, email=namsun@thejoeun.com, createAt=2023-07-11T15:18:44, updateAt=2023-07-11T15:18:44)
Member(id=3, name=이순신, email=leesunsin@gmail.com, createAt=2023-07-11T15:18:44, updateAt=2023-07-11T15:18:44)
Member(id=4, name=강감찬, email=namsun@thejoeun.com, createAt=2023-07-11T15:18:44, updateAt=2023-07-11T15:18:44)
Member(id=5, name=홍길동, email=ryukwansun@thejoeun.com, createAt=2023-07-11T15:18:44, updateAt=2023-07-11T15:18:44)
Member(id=6, name=유관순1, email=ryukwansun1@thejoeun.com, createAt=2023-07-11T15:18:42, updateAt=2023-07-11T15:18:42)
Member(id=7, name=유관순2, email=ryukwansun2@thejoeun.com, createAt=2023-07-11T15:18:42, updateAt=2023-07-11T15:18:42)

5. GreaterThanEqual----------------------------(>=)
/* <criteria> */ 
select m1_0.id,m1_0.create_at,m1_0.email,m1_0.name,m1_0.update_at 
from member m1_0 
where m1_0.id>=3;
Member(id=3, name=이순신, email=leesunsin@gmail.com, createAt=2023-07-11T15:18:44, updateAt=2023-07-11T15:18:44)
Member(id=4, name=강감찬, email=namsun@thejoeun.com, createAt=2023-07-11T15:18:44, updateAt=2023-07-11T15:18:44)
Member(id=5, name=홍길동, email=ryukwansun@thejoeun.com, createAt=2023-07-11T15:18:44, updateAt=2023-07-11T15:18:44)
Member(id=6, name=유관순1, email=ryukwansun1@thejoeun.com, createAt=2023-07-11T15:18:42, updateAt=2023-07-11T15:18:42)
Member(id=7, name=유관순2, email=ryukwansun2@thejoeun.com, createAt=2023-07-11T15:18:42, updateAt=2023-07-11T15:18:42)

6. CreateAt + Between----------------------------(betwwen A and B)
/* <criteria> */ 
select m1_0.id,m1_0.create_at,m1_0.email,m1_0.name,m1_0.update_at 
from member m1_0 
where m1_0.create_at between '2023-07-10T15:18:45.508+0900' and '2023-07-12T15:18:45.508+0900';
Member(id=1, name=홍길동, email=mars@thejoeun.com, createAt=2023-07-11T15:18:44, updateAt=2023-07-11T15:18:44)
Member(id=2, name=박남순, email=namsun@thejoeun.com, createAt=2023-07-11T15:18:44, updateAt=2023-07-11T15:18:44)
Member(id=3, name=이순신, email=leesunsin@gmail.com, createAt=2023-07-11T15:18:44, updateAt=2023-07-11T15:18:44)
Member(id=4, name=강감찬, email=namsun@thejoeun.com, createAt=2023-07-11T15:18:44, updateAt=2023-07-11T15:18:44)
Member(id=5, name=홍길동, email=ryukwansun@thejoeun.com, createAt=2023-07-11T15:18:44, updateAt=2023-07-11T15:18:44)
Member(id=6, name=유관순1, email=ryukwansun1@thejoeun.com, createAt=2023-07-11T15:18:42, updateAt=2023-07-11T15:18:42)
Member(id=7, name=유관순2, email=ryukwansun2@thejoeun.com, createAt=2023-07-11T15:18:42, updateAt=2023-07-11T15:18:42)

 

3. keyword: like, contains
(data.sql)
insert into member(`id`, `name`, `email`, `create_at`, `update_at`) values (1, '홍길동', 'mars@thejoeun.com', now(), now());
insert into member(`id`, `name`, `email`, `create_at`, `update_at`) values (2, '박남순', 'namsun@thejoeun.com', now(), now());
insert into member(`id`, `name`, `email`, `create_at`, `update_at`) values (3, '이순신', 'leesunsin@gmail.com', now(), now());
insert into member(`id`, `name`, `email`, `create_at`, `update_at`) values (4, '강감찬', 'namsun@thejoeun.com', now(), now());
insert into member(`id`, `name`, `email`, `create_at`, `update_at`) values (5, '홍길동', 'ryukwansun@thejoeun.com', now(), now());
insert into member(`id`, `name`, `email`, `create_at`, `update_at`) values (6, '이홍렬', 'hong@thejoeun.com', now(), now());
List<Member> findByNameLike(String likeName);

List<Member> findByNameContains(String name);
List<Member> memberList7 = memberRepository.findByNameLike("%홍%");
        memberList7.forEach(s -> System.out.println(s));

List<Member> memberList1 = memberRepository.findByNameContains("이");
        memberList1.forEach(s -> System.out.println(s));
select m1_0.id,m1_0.create_at,m1_0.email,m1_0.name,m1_0.update_at 
from member m1_0 
where m1_0.name like '%홍%' escape '\\';
Member(id=1, name=홍길동, email=mars@thejoeun.com, createAt=2023-07-11T15:40:59, updateAt=2023-07-11T15:40:59)
Member(id=5, name=홍길동, email=ryukwansun@thejoeun.com, createAt=2023-07-11T15:40:59, updateAt=2023-07-11T15:40:59)
Member(id=6, name=이홍렬, email=hong@thejoeun.com, createAt=2023-07-11T15:40:59, updateAt=2023-07-11T15:40:59)

select m1_0.id,m1_0.create_at,m1_0.email,m1_0.name,m1_0.update_at 
from member m1_0 
where m1_0.name like '%이%' escape '\\';
Member(id=3, name=이순신, email=leesunsin@gmail.com, createAt=2023-07-11T15:52:53, updateAt=2023-07-11T15:52:53)
Member(id=6, name=이홍렬, email=hong@thejoeun.com, createAt=2023-07-11T15:52:53, updateAt=2023-07-11T15:52:53)

 

4. keyword: in
List<Member> findByNameIn(List<String> nameList);
List<Member> memberList8 = memberRepository.findByNameIn(Lists.newArrayList("홍길동", "강감찬"));
        memberList8.forEach(s -> System.out.println(s));
select m1_0.id,m1_0.create_at,m1_0.email,m1_0.name,m1_0.update_at 
from member m1_0 
where m1_0.name in ('홍길동','강감찬');
Member(id=1, name=홍길동, email=mars@thejoeun.com, createAt=2023-07-11T15:44:20, updateAt=2023-07-11T15:44:20)
Member(id=4, name=강감찬, email=namsun@thejoeun.com, createAt=2023-07-11T15:44:20, updateAt=2023-07-11T15:44:20)
Member(id=5, name=홍길동, email=ryukwansun@thejoeun.com, createAt=2023-07-11T15:44:20, updateAt=2023-07-11T15:44:20)

 

5. keyword: startingWith, endingWith
List<Member> findByNameStartingWith(String name);
List<Member> findByNameEndingWith(String name);
System.out.println("startingWith----------------");
List<Member> memberList2 = memberRepository.findByNameStartingWith("이");
memberList2.forEach(s -> System.out.println(s));

System.out.println("EndingWith----------------");
List<Member> memberList3 = memberRepository.findByNameEndingWith("동");
memberList3.forEach(s -> System.out.println(s));
startingWith----------------
select m1_0.id,m1_0.create_at,m1_0.email,m1_0.name,m1_0.update_at 
from member m1_0 
where m1_0.name like '이%' escape '\\';
Member(id=3, name=이순신, email=leesunsin@gmail.com, createAt=2023-07-11T15:52:53, updateAt=2023-07-11T15:52:53)
Member(id=6, name=이홍렬, email=hong@thejoeun.com, createAt=2023-07-11T15:52:53, updateAt=2023-07-11T15:52:53)

EndingWith----------------
select m1_0.id,m1_0.create_at,m1_0.email,m1_0.name,m1_0.update_at 
from member m1_0 
where m1_0.name like '%동' escape '\\';
Member(id=1, name=홍길동, email=mars@thejoeun.com, createAt=2023-07-11T15:52:53, updateAt=2023-07-11T15:52:53)
Member(id=5, name=홍길동, email=ryukwansun@thejoeun.com, createAt=2023-07-11T15:52:53, updateAt=2023-07-11T15:52:53)

 

반응형

'Spring' 카테고리의 다른 글

[스프링 JPA] Entity Listener  (0) 2023.07.13
[스프링] page  (0) 2023.07.11
[스프링] query subject keywords  (0) 2023.07.11
[스프링] crud test  (0) 2023.07.11
[스프링] logging  (0) 2023.07.11

'Spring'의 다른글

  • 현재글★[스프링] Query predicate keywords

관련글

티스토리툴바