쿼리메소드

Query Method

find...By필드: findUserByUserid, findByUserid     //...생략해도 무관!
read...By필드: readByUserid
get...By필드: getByUserid
query...By필드: queryByUserid
search...By필드: searchByUserid
stream...By필드: streamByUserid
//By뒤에는 검색하고자하는 필드명을 적는다.

And

Or

Is, Equals

Between

LessThan

GreaterThan

GreaterThanEqual

After

Before

IsNull, Null

IsNotNull, NotNull

Like

NotLike

StartingWith

EndingWith

containing

OrderBy

Not

In

NotIn

TRUE

예제

entity

package com.koreait.day4.model.entity;

import com.koreait.day4.model.enumclass.UserStatus;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.*;
import java.time.LocalDateTime;
@Entity
@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
public class Users {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String userid;
    private String userpw;
    private String name;
    private String hp;
    private String email;
    @Enumerated(EnumType.STRING)
    private UserStatus status;
    private LocalDateTime regDate;
    private LocalDateTime updateDate;
}

repository

package com.koreait.day4.repository;

import com.koreait.day4.model.entity.Users;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;
import java.util.Set;

@Repository
public interface UsersRepository extends JpaRepository<Users, Long> {
    Users findNameByName(String name);  //find...By필드명
    Users findByEmail(String email);
    Users getByName(String name);
    Users readByEmail(String email);
    List<Users> getByUserpw(String userpw);
    Set<Users> readByUserpw(String userpw);
    Users queryAppleByEmail(String email);

    List<Users> findFirstByName(String name);   //first는 limit기능
    List<Users> findTop4ByUserpw(String userpw);
    List<Users> findLast3ByUserpw(String userpw);   //Last3라는건 없음!그래서 다 출력됨
    Users findByUserid(String userid);//문제-내가한거

    Optional<Users> findOpByUserid(String userid);  //강사님

    List<Users> findByNameAndEmail(String name, String email);   //findBy필드명1And필드명2
    List<Users> findByNameOrEmail(String name, String email);
    List<Users> findByIdAfter(Long id); // ~이후 해당 id를 포함하지 않음
    List<Users> findByRegDateAfter(LocalDateTime dateTime); //시간도 after가능!
    List<Users> findByIdGreaterThanEqual(Long id); //~d이상 해당 id를 포함 <-> ~이하 IdLessThanEqual
    List<Users> findByIdGreaterThanEqualAndIdLessThanEqual(Long id1, Long id2);
    List<Users> findByIdBetween(Long id1, Long id2);
    
    List<Users> findByRegDateIsNull();
    List<Users> findByRegDateIsNotNull();
    
    List<Users> findByNameIn(List<String> names);
    List<Users> findByNameStartingWith(String name);
    List<Users> findByNameEndingWith(String name);
    List<Users> findByNameContains(String name);
    List<Users> findByNameLike(String name);    //%을 직접 넣어줘야 함
    
    Optional<Users> findByUseridAndUserpw(String userid, String userpw);    //문제
    List<Users> findTopByNameOrderByIdDesc(String name);
    List<Users> findByOrderByIdDesc();
    //2차정렬
    List<Users> findByOrderByIdDescNameAsc();
}

단위테스트!

package com.koreait.day4.repository;

import com.koreait.day4.Day4ApplicationTests;
import com.koreait.day4.model.entity.Users;
import org.assertj.core.util.Lists;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;

import java.time.LocalDateTime;
import java.util.Optional;

public class UsersRepositoryTest extends Day4ApplicationTests {
    @Autowired
    private UsersRepository usersRepository;

    @Test
    public void read(){
        System.out.println(usersRepository.findNameByName("비일"));
        System.out.println(usersRepository.findByEmail("d1111@d1111.com"));
        System.out.println(usersRepository.readByEmail("c1111@c1111.com"));
        System.out.println(usersRepository.getByName("에프일"));
        System.out.println(usersRepository.getByUserpw("1111"));
        System.out.println(usersRepository.readByUserpw("1111"));//맨위에꺼만 나온다.
        System.out.println(usersRepository.queryAppleByEmail("e1111@e1111.com"));
        System.out.println(usersRepository.findFirstByName("비일"));
        System.out.println(usersRepository.findTop4ByUserpw("1111"));
        System.out.println(usersRepository.findLast3ByUserpw("1111"));
        System.out.println(usersRepository.findByNameAndEmail("에이일","a1111@a1111.com"));
        System.out.println(usersRepository.findByNameOrEmail("에이일","c1111@c1111.com"));
        //select * from users where id > 5; //After 이용!
        System.out.println(usersRepository.findByIdAfter(4L));
        System.out.println(usersRepository.findByRegDateAfter(LocalDateTime.now()));
        System.out.println(usersRepository.findByRegDateAfter(LocalDateTime.now().minusDays(2L)));
        System.out.println(usersRepository.findByIdGreaterThanEqual(3L));
        System.out.println(usersRepository.findByIdGreaterThanEqualAndIdLessThanEqual(3L,5L));
        System.out.println(usersRepository.findByIdBetween(3L,5L));
        System.out.println(usersRepository.findByRegDateIsNotNull());
        System.out.println(usersRepository.findByNameIn(Lists.newArrayList("씨일,이일,에이일")));
        System.out.println(usersRepository.findByNameStartingWith("이"));
        System.out.println(usersRepository.findByNameEndingWith("일"));
        System.out.println(usersRepository.findByNameContains("이"));
        System.out.println(usersRepository.findByNameLike("%이%"));
        System.out.println(usersRepository.findTopByNameOrderByIdDesc("디일"));
        System.out.println(usersRepository.findByOrderByIdDesc());
        System.out.println(usersRepository.findByOrderByIdDescNameAsc());
    }
    @Test
    public void create(){
        Users User1 = Users.builder().userid("a1111")
                .userpw("1111").name("에이일").hp("010-1111-1111")
                .email("a1111@a1111.com").build();
        Users User2 = Users.builder().userid("b1111")
                .userpw("1111").name("비일").hp("010-1111-1111")
                .email("b1111@b1111.com").build();
        Users User3 = Users.builder().userid("c1111")
                .userpw("1111").name("씨일").hp("010-1111-1111")
                .email("c1111@c1111.com").build();
        Users User4 = Users.builder().userid("d1111")
                .userpw("1111").name("디일").hp("010-1111-1111")
                .email("d1111@d1111.com").build();
        Users User5 = Users.builder().userid("e1111")
                .userpw("1111").name("이일").hp("010-1111-1111")
                .email("e1111@e1111.com").build();
        Users User6 = Users.builder().userid("f1111")
                .userpw("1111").name("에프일").hp("010-1111-1111")
                .email("f1111@f1111.com").build();
        Users User7 = Users.builder().userid("g1111")
                .userpw("1111").name("지일").hp("010-1111-1111")
                .email("a1111@a1111.com").build();
        usersRepository.saveAll(Lists.newArrayList(User1,User2,User3,User4,User5,User6,User7));
    }


    @Test
    public void selectUserid(){
        //아이디로 검색하는 로직
        /*
        Users User = usersRepository.findByUserid("a1111");
        System.out.println(User);
        */
        Optional<Users> users= usersRepository.findOpByUserid("b1111");
        users.ifPresent(
                selectUser -> {
                    System.out.println("userid: " + selectUser.getUserid());
                    System.out.println("name: " + selectUser.getName());
                    System.out.println("hp: " + selectUser.getHp());
                    System.out.println("email: " + selectUser.getEmail());
            }
        );
    }


    @Test
    public void updateUserid(){
        //아이디를 통해 다른 정보로 저장(변경)
        //강사님이랑 비슷
        Optional<Users> user = Optional.ofNullable(usersRepository.findByUserid("b1111"));
        user.ifPresent(
                selectUser  -> {
                    selectUser.setUserpw("1112");
                    selectUser.setName("비이일");
                    selectUser.setEmail("a1111@naver.com");
                    selectUser.setUpdateDate(LocalDateTime.now());
                    usersRepository.save(selectUser);
                }
        );
    }

    @Test
    public void deleteUsesrid(){
        // 아이디를 통해 데이터를 삭제
        Optional<Users> user = Optional.ofNullable(usersRepository.findByUserid("b1111"));
        user.ifPresent(
                selectUser ->{
                    usersRepository.delete(selectUser);
                }
        );
    }

    @Test
    public void login(){
        //아이디와 비밀번호를 입력받아 로그인 할 수 있는 query method 생성후,
        //해당 데이터를 찾아 정보를 출력하는 메소드를 작성하자!
        //아래와 같이 출력 되도록
        //userid: xxx
        //hp: xxx
        //Email: xxx
        Optional<Users> users= usersRepository.findByUseridAndUserpw("b1111","1111");
        users.ifPresent(
                selectUser -> {
                    System.out.println("userid: " + selectUser.getUserid());
                    System.out.println("hp: " + selectUser.getHp());
                    System.out.println("email: " + selectUser.getEmail());
                }
        );

    }
}