@Test
public void startJPQL(){
        // member1을 찾아라
        String qlString="select m from Member m "+
        "where m.username = :username";

        Member findMember=em.createQuery(qlString,Member.class)
        .setParameter("username","member1")
        .getSingleResult();

        assertThat(findMember.getUsername()).isEqualTo("member1");
        }

@Test
public void startQuerydsl(){
        JPAQueryFactory jpaQueryFactory=new JPAQueryFactory(em);

        QMember m=new QMember("m");

        Member findMember=jpaQueryFactory
        .select(m)
        .from(m)
        .where(m.username.eq("member1")) // 파라미터 바인딩 처리
        .fetchOne();

        assertThat(findMember.getUsername()).isEqualTo("member1");

        }

public class QuerydslBasicTest {

    @Autowired
    EntityManager em;

    JPAQueryFactory jpaQueryFactory;


    @BeforeEach
    public void before() {
        jpaQueryFactory = new JPAQueryFactory(em);
    ...
    }
}

QMember qMember=new QMember("m"); //별칭 직접 지정
        QMember qMember=QMember.member; //기본 인스턴스 사용, static 

jpaQueryFactory
        .select(member)
        .from(member)
        .where(member.username.eq("member1"))
        .fetchOne();
// 아래처럼 select, from을 selectFrom으로 합칠 수 있다. 
        jpaQueryFactory
        .selectFrom(member)
        .where(member.username.eq("member1").and(member.age.eq(10)))
        .fetchOne();

member.username.eq("member1"); // username = 'member1'
member.username.ne("member1"); //username != 'member1'
member.username.eq("member1").not(); // username != 'member1'
member.username.isNotNull(); //이름이 is not null
member.age.in(10,20); // age in (10,20)
member.age.notIn(10,20); // age not in (10, 20)
member.age.between(10,30); //between 10, 30
member.age.goe(30); // age >= 30
member.age.gt(30); // age > 30
member.age.loe(30); // age <= 30
member.age.lt(30);// age < 30
member.username.like("member%"); //like 검색
member.username.contains("member"); // like ‘%member%’ 검색
member.username.startsWith("member"); //like ‘member%’ 검색

List<Member> result1=queryFactory
        .selectFrom(member)
        .where(member.username.eq("member1"),member.age.eq(10))
        .fetch();

public Q where(Predicate...o){
        return queryMixin.where(o);
        }

//페이징에서 사용
QueryResults<Member> results=queryFactory
        .selectFrom(member)
        .fetchResults();

int count=results.getTotal();
List<Member> content=results.getResults();

/**
 * 회원 정렬 순서
 * 1. 회원 나이 내림차순(desc)
 * 2. 회원 이름 올림차순(asc)
 * 단 2에서 회원 이름이 없으면 마지막에 출력(nulls last)
 */
@Test
public void sort(){
        em.persist(new Member(null,100));
        em.persist(new Member("member5",100));
        em.persist(new Member("member6",100));
        List<Member> result=queryFactory
          .selectFrom(member)
          .where(member.age.eq(100))
          .orderBy(member.age.desc(),member.username.asc().nullsLast())
          .fetch();
        
        Member member5=result.get(0);
        Member member6=result.get(1);
        Member memberNull=result.get(2);
        
        assertThat(member5.getUsername()).isEqualTo("member5");
        assertThat(member6.getUsername()).isEqualTo("member6");
        assertThat(memberNull.getUsername()).isNull();
}

@Test
public void paging1() {
    List<Member> result = queryFactory
        .selectFrom(member)
        .orderBy(member.username.desc())
        .offset(1) //0부터 시작(zero index)
        .limit(2) //최대 2건 조회
        .fetch();
assertThat(result.size()).isEqualTo(2);
}

@Test
public void paging2() {
    QueryResults<Member> queryResults = queryFactory
        .selectFrom(member)
        .orderBy(member.username.desc())
        .offset(1)
        .limit(2)
        .fetchResults();
    
    assertThat(queryResults.getTotal()).isEqualTo(4);
    assertThat(queryResults.getLimit()).isEqualTo(2);
    assertThat(queryResults.getOffset()).isEqualTo(1);
    assertThat(queryResults.getResults().size()).isEqualTo(2);
}

/**
* JPQL
* select
* COUNT(m), //회원수
* SUM(m.age), //나이 합
* AVG(m.age), //평균 나이
* MAX(m.age), //최대 나이
* MIN(m.age) //최소 나이
* from Member m
*/
@Test
public void aggregation() throws Exception{
        List<Tuple> result=queryFactory
            .select(member.count(),
                    member.age.sum(),
                    member.age.avg(),
                    member.age.max(),
                    member.age.min())
        .from(member)
        .fetch();
        Tuple tuple=result.get(0);
}

/**
* 팀의 이름과 각 팀의 평균 연령을 구해라.
*/
@Test
public void group() throws Exception {
    List<Tuple> result = queryFactory
        .select(team.name, member.age.avg())
        .from(member)
        .join(member.team, team)
        .groupBy(team.name)
        .fetch();
    Tuple teamA = result.get(0);
    Tuple teamB = result.get(1);
    
    assertThat(teamA.get(team.name)).isEqualTo("teamA");
    assertThat(teamA.get(member.age.avg())).isEqualTo(15);
    assertThat(teamB.get(team.name)).isEqualTo("teamB");
    assertThat(teamB.get(member.age.avg())).isEqualTo(35);
}

…
.groupBy(item.price)
.having(item.price.gt(1000))
…

/**
* 팀 A에 소속된 모든 회원
*/
@Test
public void join() throws Exception {
    QMember member = QMember.member;
    QTeam team = QTeam.team;
    
    List<Member> result = queryFactory
        .selectFrom(member)
        .join(member.team, team)
        .where(team.name.eq("teamA"))
        .fetch();
    assertThat(result)
        .extracting("username")
        .containsExactly("member1", "member2");
}

    /**
     * 세타 조인
     * 회원의 이름이 팀 이름과 같은 회원 조회
     */
    @Test
    public void theta_join() {
        em.persist(new Member("teamA"));
        em.persist(new Member("teamB"));

        List<Member> result = jpaQueryFactory
                .select(member)
                .from(member, team)
                .where(member.username.eq(team.name))
                .fetch();
        
    }

/**
     * 예) 회원과 팀을 조인하면서, 팀 이름이 teamA인 팀만 조인, 회원은 모두 조회
     * JPQL: SELECT m, t FROM Member m LEFT JOIN m.team t on t.name = 'teamA'
     * SQL: SELECT m.*, t.* FROM Member m LEFT JOIN Team t ON m.TEAM_ID=t.id and
     t.name='teamA'
     */
    @Test
    public void join_on_filtering() throws Exception {
        List<Tuple> result = jpaQueryFactory
                .select(member, team)
                .from(member)
                .leftJoin(member.team, team).on(team.name.eq("teamA"))
                .fetch();
        for (Tuple tuple : result) {
            System.out.println("tuple = " + tuple);
        }
    }

/**
 * 2. 연관관계 없는 엔티티 외부 조인
 * 예) 회원의 이름과 팀의 이름이 같은 대상 외부 조인
 * JPQL: SELECT m, t FROM Member m LEFT JOIN Team t on m.username = t.name
 * SQL: SELECT m.*, t.* FROM Member m LEFT JOIN Team t ON m.username = t.name
 */
@Test
public void join_on_no_relation() throws Exception {
        em.persist(new Member("teamA"));
        em.persist(new Member("teamB"));
        List<Tuple> result = jpaQueryFactory
                .select(member, team)
                .from(member)
                .leftJoin(team).on(member.username.eq(team.name))
                .fetch();
        for (Tuple tuple : result) {
            System.out.println("t=" + tuple);
        }
}

@PersistenceUnit // EntityManagerFactory를 만들어줌 
EntityManagerFactory emf;

@Test
public void fetchJoinUse() throws Exception {
    em.flush();
    em.clear();
    Member findMember = queryFactory
        .selectFrom(member)
        .join(member.team, team).fetchJoin() // 
        .where(member.username.eq("member1"))
        .fetchOne();

    boolean loaded = emf.getPersistenceUnitUtil().isLoaded(findMember.getTeam());
    assertThat(loaded).as("페치 조인 적용").isTrue();
}

/**
* 나이가 가장 많은 회원 조회
*/
@Test
public void subQuery() throws Exception {
    QMember memberSub = new QMember("memberSub"); // 서브쿼리 조회용, alias가 같으면 안됨 
    List<Member> result = queryFactory
        .selectFrom(member)
        .where(member.age.eq(JPAExpressions // import
        .select(memberSub.age.max())
            .from(memberSub)
        ))
        .fetch();
    
    assertThat(result).extracting("age")
        .containsExactly(40);
}

@Test
public void subQueryIn() throws Exception {
    QMember memberSub = new QMember("memberSub");
    List<Member> result = queryFactory
        .selectFrom(member)
        .where(member.age.in(
                JPAExpressions
                    .select(memberSub.age)
                    .from(memberSub)
                    .where(memberSub.age.gt(10))
        ))
        .fetch();
        
    assertThat(result).extracting("age")
    .containsExactly(20, 30, 40);
}

QMember memberSub = new QMember("memberSub");
List<Tuple> fetch = queryFactory
    .select(member.username,
            JPAExpressions
                .select(memberSub.age.avg())
                .from(memberSub)
            ).from(member)
    .fetch();

    for (Tuple tuple : fetch) {
        System.out.println("username = " + tuple.get(member.username));
        System.out.println("age = " +
        tuple.get(JPAExpressions.select(memberSub.age.avg())
            .from(memberSub)));
    }

//단순한 조건
List<String> result = queryFactory
        .select(member.age
                .when(10).then("열살")
                .when(20).then("스무살")
                .otherwise("기타"))
        .from(member)
        .fetch();

//복잡한 조건
List<String> result = queryFactory
        .select(new CaseBuilder()
            .when(member.age.between(0, 20)).then("0~20살")
            .when(member.age.between(21, 30)).then("21~30살")
            .otherwise("기타"))
        .from(member)
        .fetch();

    NumberExpression<Integer> rankPath = new CaseBuilder()
                .when(member.age.between(0, 20)).then(2)
                .when(member.age.between(21, 30)).then(1)
                .otherwise(3);
        
    List<Tuple> result = queryFactory
                .select(member.username, member.age, rankPath)
                .from(member)
                .orderBy(rankPath.desc())
                .fetch();
        
        for (Tuple tuple : result) {
            String username = tuple.get(member.username);
            Integer age = tuple.get(member.age);Integer rank = tuple.get(rankPath);
            System.out.println("username = " + username + " age = " + age + " rank = " + rank);

Tuple result = queryFactory
    .select(member.username, Expressions.constant("A"))
    .from(member)
    .fetchFirst();

String result = queryFactory
    .select(member.username.concat("_").concat(member.age.stringValue()))
    .from(member)
    .where(member.username.eq("member1"))
    .fetchOne();
// 결과 : member1_10

List<String> result = queryFactory
    .select(member.username)
    .from(member)
    .fetch();

List<Tuple> result = queryFactory
    .select(member.username, member.age)
    .from(member)
    .fetch();

for (Tuple tuple : result) {
    String username = tuple.get(member.username);
    Integer age = tuple.get(member.age);
    System.out.println("username=" + username);
    System.out.println("age=" + age);
}

List<MemberDto> result = em
        .createQuery("select new study.querydsl.dto.MemberDto(m.username, m.age) " + "from Member m", MemberDto.class)
        .getResultList();

List<MemberDto> result = queryFactory
    .select(Projections.bean(MemberDto.class,
            member.username,
            member.age))
    .from(member)
    .fetch();

List<MemberDto> result = queryFactory
        .select(Projections.fields(MemberDto.class,
                member.username,
                member.age))
        .from(member)
        .fetch();

import lombok.Data;
@Data
public class UserDto {
    private String name;
    private int age;
}

List<UserDto> fetch = queryFactory
    .select(Projections.fields(UserDto.class,
            member.username.as("name"),
            ExpressionUtils.as(
                JPAExpressions
                .select(memberSub.age.max())
                .from(memberSub), "age")
    ))
        .from(member)
        .fetch();

List<MemberDto> result = queryFactory
    .select(Projections.constructor(MemberDto.class,
            member.username,
            member.age))
    .from(member).fetch();
}

@Data
public class MemberDto {
    private String username;
    private int age;
    
    public MemberDto() {
    }

    @QueryProjection
    public MemberDto(String username, int age) {
        this.username = username;
        this.age = age;
    }
}

List<MemberDto> result = queryFactory
        .select(new QMemberDto(member.username, member.age))
        .from(member)
        .fetch();

List<String> result = queryFactory
    .select(member.username).distinct()
    .from(member)
    .fetch();

 @Test
public void 동적쿼리_BooleanBuilder() throws Exception {
    String usernameParam = "member1";
    Integer ageParam = 10;
    List<Member> result = searchMember1(usernameParam, ageParam);
    Assertions.assertThat(result.size()).isEqualTo(1);
    }

private List<Member> searchMember1(String usernameCond, Integer ageCond) {
    BooleanBuilder builder = new BooleanBuilder();
    if (usernameCond != null) {
        builder.and(member.username.eq(usernameCond));
    }

    if (ageCond != null) {
        builder.and(member.age.eq(ageCond));
    }
    return queryFactory
            .selectFrom(member)
            .where(builder)
            .fetch();

 }

@Test
public void 동적쿼리_WhereParam() throws Exception {
    String usernameParam = "member1";
    Integer ageParam = 10;
    List<Member> result = searchMember2(usernameParam, ageParam);
    Assertions.assertThat(result.size()).isEqualTo(1);
}
private List<Member> searchMember2(String usernameCond, Integer ageCond) {
    return queryFactory
            .selectFrom(member)
            .where(usernameEq(usernameCond), ageEq(ageCond))
            .fetch();
}
private BooleanExpression usernameEq(String usernameCond) {
    return usernameCond != null ? member.username.eq(usernameCond) : null;
}
private BooleanExpression ageEq(Integer ageCond) {
    return ageCond != null ? member.age.eq(ageCond) : null;
}

long count = queryFactory   
        .update(member)
        .set(member.username, "비회원")
        .where(member.age.lt(28))
        .execute();

long count = queryFactory
    .update(member)
    .set(member.age, member.age.add(1)) // 
    .execute();

long count = queryFactory
    .delete(member)
    .where(member.age.gt(18))
    .execute();