Home

2022
0
ProgrammingQueryDSL

QueryDSL - 동적 쿼리와 성능 최적화 조회

목차 QueryDSL - QueryDsl 페이징 사용하기 QueryDSL - 사용자 정의 Repository QueryDSL - 동적 쿼리와 성능 최적화 조회 QueryDSL - 순수 JPA 리포지토리와 Querydsl QueryDSL - SQL Function 호출하기 QueryDSL - 수정, 삭제 벌크 연산 QueryDSL - 동적 쿼리 QueryDSL - 프로젝션과 결과 반환 QueryDSL - 조인 QueryDSL - 집계 QueryDSL - 결과 조회 QueryDSL - 검색 조건 쿼리 QueryDSL - Q-Type 활용 QueryDSL - JPA JPQL vs JPA QueryDSL QueryDSL 시작하기 동적 쿼리와 성능 최적화 조회 - Builder 사용public List<MemberTeamDto> searchByBuilder(MemberSearchCondition condition){ BooleanBuilder builder = new BooleanBuilder(); if (StringUtils.hasText(condition.getUsername())) { builder.and(member.username.eq(condition.getUsername())); } if(StringUtils.hasText(condition.getTeamName())){ builder.and(team.name.eq(condition.getUsername())); } if(condition.getAgeGoe()!=null){ builder.and(member.age.goe(condition.getAgeGoe())); } if(condition.getAgeLoe()!=null){ builder.and(member.age.loe(condition.getAgeLoe())); } return queryFactory .select(new QMemberTeamDto( member.id.as("memberId"), member.username, member.age, team.id.as("teamId"), team.name.as("teamName") )) .from(member) .leftJoin(member.team, team) .where(builder) .fetch();} @Testpublic void searchTest(){ Team teamA = new Team("teamA"); Team teamB = new Team("teamB"); em.persist(teamA); em.persist(teamB); Member member1 = new Member("member1", 10, teamA); Member member2 = new Member("member2", 20, teamA); Member member3 = new Member("member3", 30, teamB); Member member4 = new Member("member4", 40, teamB); em.persist(member1); em.persist(member2); em.persist(member3); em.persist(member4); MemberSearchCondition condition = new MemberSearchCondition(); condition.setAgeGoe(35); condition.setAgeLoe(40); condition.setTeamName("teamB"); List<MemberTeamDto> result = memberJpaRepository.searchByBuilder(condition); assertThat(result).extracting("username").containsExactly("member4");} /* select member1.id as memberId, member1.username, member1.age, team.id as teamId, team.name as teamName from Member member1 left join member1.team as team where team.name = ?1 and member1.age >= ?2 and member1.age <= ?3 */ select member0_.member_id as col_0_0_, member0_.username as col_1_0_, member0_.age as col_2_0_, team1_.team_id as col_3_0_, team1_.name as col_4_0_ from member member0_ left outer join team team1_ on member0_.team_id=team1_.team_id where team1_.name=? and member0_.age>=? and member0_.age<=? 동적 쿼리와 성능 최적화 조회 - where 절 파라미터 사용public List<MemberTeamDto> searchByBuilder(MemberSearchCondition condition){ BooleanBuilder builder = new BooleanBuilder(); if (StringUtils.hasText(condition.getUsername())) { builder.and(member.username.eq(condition.getUsername())); } if(StringUtils.hasText(condition.getTeamName())){ builder.and(team.name.eq(condition.getUsername())); } if(condition.getAgeGoe()!=null){ builder.and(member.age.goe(condition.getAgeGoe())); } if(condition.getAgeLoe()!=null){ builder.and(member.age.loe(condition.getAgeLoe())); } return queryFactory .select(new QMemberTeamDto( member.id.as("memberId"), member.username, member.age, team.id.as("teamId"), team.name.as("teamName") )) .from(member) .leftJoin(member.team, team) .where(builder) .fetch();} @Test public void searchTest(){ Team teamA = new Team("teamA"); Team teamB = new Team("teamB"); em.persist(teamA); em.persist(teamB); Member member1 = new Member("member1", 10, teamA); Member member2 = new Member("member2", 20, teamA); Member member3 = new Member("member3", 30, teamB); Member member4 = new Member("member4", 40, teamB); em.persist(member1); em.persist(member2); em.persist(member3); em.persist(member4); MemberSearchCondition condition = new MemberSearchCondition(); condition.setAgeGoe(35); condition.setAgeLoe(40); condition.setTeamName("teamB"); List<MemberTeamDto> result = memberJpaRepository.searchByBuilder(condition); assertThat(result).extracting("username").containsExactly("member4"); }

0
ProgrammingQueryDSL

QueryDSL - 수정, 삭제 벌크 연산

목차 QueryDSL - QueryDsl 페이징 사용하기 QueryDSL - 사용자 정의 Repository QueryDSL - 동적 쿼리와 성능 최적화 조회 QueryDSL - 순수 JPA 리포지토리와 Querydsl QueryDSL - SQL Function 호출하기 QueryDSL - 수정, 삭제 벌크 연산 QueryDSL - 동적 쿼리 QueryDSL - 프로젝션과 결과 반환 QueryDSL - 조인 QueryDSL - 집계 QueryDSL - 결과 조회 QueryDSL - 검색 조건 쿼리 QueryDSL - Q-Type 활용 QueryDSL - JPA JPQL vs JPA QueryDSL QueryDSL 시작하기 수정, 삭제 벌크 연산 Bulk 연산은 영속성 Context 상태를 무시 하고 쿼리가 발생하기 때문에 영속성 Context 와 DB 상태가 다르게 된다. UPDATE, DELETE 와 같은 대량의 데이터를 한번에 처리하는 Bulk 연산을 진행하게 되면 JPA 가 영속성 컨텍스트에 저장된 엔티티를 우회하고 직접 데이터베이스에 접근하기 때문에, 영속성 컨텍스트에 저장된 엔티티 상태와 DB 상태가 다르게 됩니다. 따라서 Bulk 연산시에는 영속성 컨텍스트와 DB 상태간의 데이터 동기화에 주의를 해야 합니다. 수정@Testpublic void bulkUpdate(){ long count = queryFactory .update(member) .set(member.username, "비회원") .where(member.age.lt(28)) .execute();} /* update Member member1 set member1.username = ?1 where member1.age < ?2 */ update member set username=? where age<? @Testpublic void bulkUpdate(){ long count = queryFactory .update(member) .set(member.username, "비회원") .where(member.age.lt(28)) .execute(); List<Member> result = queryFactory .selectFrom(member) .fetch(); for (Member member1 : result) { System.out.println("member1 = " + member1); }}

0
ProgrammingQueryDSL

QueryDSL - SQL Function 호출하기

목차 QueryDSL - QueryDsl 페이징 사용하기 QueryDSL - 사용자 정의 Repository QueryDSL - 동적 쿼리와 성능 최적화 조회 QueryDSL - 순수 JPA 리포지토리와 Querydsl QueryDSL - SQL Function 호출하기 Post not found: jpa/querydsl/querydsl-10 QueryDSL - 동적 쿼리 QueryDSL - 프로젝션과 결과 반환 QueryDSL - 조인 QueryDSL - 집계 QueryDSL - 결과 조회 QueryDSL - 검색 조건 쿼리 QueryDSL - Q-Type 활용 QueryDSL - JPA JPQL vs JPA QueryDSL QueryDSL 시작하기 SQL Function 호출하기@Testpublic void sqlFunction(){ List<String> result = queryFactory .select( Expressions.stringTemplate("function('replace', {0}, {1}, {2})", member.username, "member", "M") ).from(member) .fetch(); for (String s : result) { System.out.println("s = " + s); }} @Testpublic void sqlFunction2(){ queryFactory .select(member.username) .from(member) .where(member.username.eq(Expressions.stringTemplate("function('lower', {0})", member.username)));} registerColumnType( Types.BOOLEAN, "boolean" );registerColumnType( Types.BIGINT, "bigint" );registerColumnType( Types.BINARY, "binary" );registerColumnType( Types.BIT, "boolean" );registerColumnType( Types.CHAR, "char($l)" );registerColumnType( Types.DATE, "date" );registerColumnType( Types.DECIMAL, "decimal($p,$s)" );registerColumnType( Types.NUMERIC, buildId >= 201 ? "numeric($p,$s)" : "decimal($p,$s)" );registerColumnType( Types.DOUBLE, "double" );registerColumnType( Types.FLOAT, "float" );registerColumnType( Types.INTEGER, "integer" );registerColumnType( Types.LONGVARBINARY, "longvarbinary" );// H2 does define "longvarchar", but it is a simple alias to "varchar"registerColumnType( Types.LONGVARCHAR, String.format( "varchar(%d)", Integer.MAX_VALUE ) );registerColumnType( Types.REAL, "real" );registerColumnType( Types.SMALLINT, "smallint" );registerColumnType( Types.TINYINT, "tinyint" );registerColumnType( Types.TIME, "time" );registerColumnType( Types.TIMESTAMP, "timestamp" );registerColumnType( Types.VARCHAR, "varchar($l)" );registerColumnType( Types.VARBINARY, buildId >= 201 ? "varbinary($l)" : "binary($l)" );registerColumnType( Types.BLOB, "blob" );registerColumnType( Types.CLOB, "clob" );if ( isVersion2 ) { registerColumnType( Types.LONGVARCHAR, "character varying" ); registerColumnType( Types.BINARY, "binary($l)" ); registerFunction( "str", new SQLFunctionTemplate( StandardBasicTypes.STRING, "cast(?1 as character varying)") );}// Aggregations ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~registerFunction( "avg", new AvgWithArgumentCastFunction( "double" ) );// select topic, syntax from information_schema.help// where section like 'Function%' order by section, topic//// see also -> http://www.h2database.com/html/functions.html// Numeric Functions ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~registerFunction( "acos", new StandardSQLFunction( "acos", StandardBasicTypes.DOUBLE ) );registerFunction( "asin", new StandardSQLFunction( "asin", StandardBasicTypes.DOUBLE ) );registerFunction( "atan", new StandardSQLFunction( "atan", StandardBasicTypes.DOUBLE ) );registerFunction( "atan2", new StandardSQLFunction( "atan2", StandardBasicTypes.DOUBLE ) );registerFunction( "bitand", new StandardSQLFunction( "bitand", StandardBasicTypes.INTEGER ) );registerFunction( "bitor", new StandardSQLFunction( "bitor", StandardBasicTypes.INTEGER ) );registerFunction( "bitxor", new StandardSQLFunction( "bitxor", StandardBasicTypes.INTEGER ) );registerFunction( "ceiling", new StandardSQLFunction( "ceiling", StandardBasicTypes.DOUBLE ) );registerFunction( "cos", new StandardSQLFunction( "cos", StandardBasicTypes.DOUBLE ) );registerFunction( "compress", new StandardSQLFunction( "compress", StandardBasicTypes.BINARY ) );registerFunction( "cot", new StandardSQLFunction( "cot", StandardBasicTypes.DOUBLE ) );registerFunction( "decrypt", new StandardSQLFunction( "decrypt", StandardBasicTypes.BINARY ) );registerFunction( "degrees", new StandardSQLFunction( "degrees", StandardBasicTypes.DOUBLE ) );registerFunction( "encrypt", new StandardSQLFunction( "encrypt", StandardBasicTypes.BINARY ) );registerFunction( "exp", new StandardSQLFunction( "exp", StandardBasicTypes.DOUBLE ) );registerFunction( "expand", new StandardSQLFunction( "compress", StandardBasicTypes.BINARY ) );registerFunction( "floor", new StandardSQLFunction( "floor", StandardBasicTypes.DOUBLE ) );registerFunction( "hash", new StandardSQLFunction( "hash", StandardBasicTypes.BINARY ) );registerFunction( "log", new StandardSQLFunction( "log", StandardBasicTypes.DOUBLE ) );registerFunction( "log10", new StandardSQLFunction( "log10", StandardBasicTypes.DOUBLE ) );registerFunction( "pi", new NoArgSQLFunction( "pi", StandardBasicTypes.DOUBLE ) );registerFunction( "power", new StandardSQLFunction( "power", StandardBasicTypes.DOUBLE ) );registerFunction( "radians", new StandardSQLFunction( "radians", StandardBasicTypes.DOUBLE ) );registerFunction( "rand", new NoArgSQLFunction( "rand", StandardBasicTypes.DOUBLE ) );registerFunction( "round", new StandardSQLFunction( "round", StandardBasicTypes.DOUBLE ) );registerFunction( "roundmagic", new StandardSQLFunction( "roundmagic", StandardBasicTypes.DOUBLE ) );registerFunction( "sign", new StandardSQLFunction( "sign", StandardBasicTypes.INTEGER ) );registerFunction( "sin", new StandardSQLFunction( "sin", StandardBasicTypes.DOUBLE ) );registerFunction( "tan", new StandardSQLFunction( "tan", StandardBasicTypes.DOUBLE ) );registerFunction( "truncate", new StandardSQLFunction( "truncate", StandardBasicTypes.DOUBLE ) );// String Functions ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~registerFunction( "ascii", new StandardSQLFunction( "ascii", StandardBasicTypes.INTEGER ) );registerFunction( "char", new StandardSQLFunction( "char", StandardBasicTypes.CHARACTER ) );registerFunction( "concat", new VarArgsSQLFunction( StandardBasicTypes.STRING, "(", "||", ")" ) );registerFunction( "difference", new StandardSQLFunction( "difference", StandardBasicTypes.INTEGER ) );registerFunction( "hextoraw", new StandardSQLFunction( "hextoraw", StandardBasicTypes.STRING ) );registerFunction( "insert", new StandardSQLFunction( "lower", StandardBasicTypes.STRING ) );registerFunction( "left", new StandardSQLFunction( "left", StandardBasicTypes.STRING ) );registerFunction( "lcase", new StandardSQLFunction( "lcase", StandardBasicTypes.STRING ) );registerFunction( "ltrim", new StandardSQLFunction( "ltrim", StandardBasicTypes.STRING ) );registerFunction( "octet_length", new StandardSQLFunction( "octet_length", StandardBasicTypes.INTEGER ) );registerFunction( "position", new StandardSQLFunction( "position", StandardBasicTypes.INTEGER ) );registerFunction( "rawtohex", new StandardSQLFunction( "rawtohex", StandardBasicTypes.STRING ) );registerFunction( "repeat", new StandardSQLFunction( "repeat", StandardBasicTypes.STRING ) );registerFunction( "replace", new StandardSQLFunction( "replace", StandardBasicTypes.STRING ) );registerFunction( "right", new StandardSQLFunction( "right", StandardBasicTypes.STRING ) );registerFunction( "rtrim", new StandardSQLFunction( "rtrim", StandardBasicTypes.STRING ) );registerFunction( "soundex", new StandardSQLFunction( "soundex", StandardBasicTypes.STRING ) );registerFunction( "space", new StandardSQLFunction( "space", StandardBasicTypes.STRING ) );registerFunction( "stringencode", new StandardSQLFunction( "stringencode", StandardBasicTypes.STRING ) );registerFunction( "stringdecode", new StandardSQLFunction( "stringdecode", StandardBasicTypes.STRING ) );registerFunction( "stringtoutf8", new StandardSQLFunction( "stringtoutf8", StandardBasicTypes.BINARY ) );registerFunction( "ucase", new StandardSQLFunction( "ucase", StandardBasicTypes.STRING ) );registerFunction( "utf8tostring", new StandardSQLFunction( "utf8tostring", StandardBasicTypes.STRING ) );// Time and Date Functions ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~registerFunction( "curdate", new NoArgSQLFunction( "curdate", StandardBasicTypes.DATE ) );registerFunction( "curtime", new NoArgSQLFunction( "curtime", StandardBasicTypes.TIME ) );registerFunction( "curtimestamp", new NoArgSQLFunction( "curtimestamp", StandardBasicTypes.TIME ) );registerFunction( "current_date", new NoArgSQLFunction( "current_date", StandardBasicTypes.DATE ) );// H2 made a nasty breaking change that changed the type of// - current_timestamp to timestamp with time zone// - current_time to time with time zone// and also refuses to implicitly convert the typeregisterFunction( "current_time", new NoArgSQLFunction( buildId >= 200 ? "localtime" : "current_time", StandardBasicTypes.TIME ) );registerFunction( "current_timestamp", new NoArgSQLFunction( buildId >= 200 ? "localtimestamp" : "current_timestamp", StandardBasicTypes.TIMESTAMP ) );registerFunction( "datediff", new StandardSQLFunction( "datediff", StandardBasicTypes.INTEGER ) );registerFunction( "dayname", new StandardSQLFunction( "dayname", StandardBasicTypes.STRING ) );registerFunction( "dayofmonth", new StandardSQLFunction( "dayofmonth", StandardBasicTypes.INTEGER ) );registerFunction( "dayofweek", new StandardSQLFunction( "dayofweek", StandardBasicTypes.INTEGER ) );registerFunction( "dayofyear", new StandardSQLFunction( "dayofyear", StandardBasicTypes.INTEGER ) );registerFunction( "monthname", new StandardSQLFunction( "monthname", StandardBasicTypes.STRING ) );registerFunction( "now", new NoArgSQLFunction( "now", StandardBasicTypes.TIMESTAMP ) );registerFunction( "quarter", new StandardSQLFunction( "quarter", StandardBasicTypes.INTEGER ) );registerFunction( "week", new StandardSQLFunction( "week", StandardBasicTypes.INTEGER ) );// System Functions ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~registerFunction( "database", new NoArgSQLFunction( "database", StandardBasicTypes.STRING ) );registerFunction( "user", new NoArgSQLFunction( "user", StandardBasicTypes.STRING ) );getDefaultProperties().setProperty( AvailableSettings.STATEMENT_BATCH_SIZE, DEFAULT_BATCH_SIZE );// http://code.google.com/p/h2database/issues/detail?id=235getDefaultProperties().setProperty( AvailableSettings.NON_CONTEXTUAL_LOB_CREATION, "true" );

0
ProgrammingQueryDSL

QueryDSL - 동적 쿼리

목차 QueryDSL - QueryDsl 페이징 사용하기 QueryDSL - 사용자 정의 Repository QueryDSL - 동적 쿼리와 성능 최적화 조회 QueryDSL - 순수 JPA 리포지토리와 Querydsl QueryDSL - SQL Function 호출하기 QueryDSL - 수정, 삭제 벌크 연산 QueryDSL - 동적 쿼리 QueryDSL - 프로젝션과 결과 반환 QueryDSL - 조인 QueryDSL - 집계 QueryDSL - 결과 조회 QueryDSL - 검색 조건 쿼리 QueryDSL - Q-Type 활용 QueryDSL - JPA JPQL vs JPA QueryDSL QueryDSL 시작하기 동적 쿼리를 해결하는 두가지 방식 BooleanBuilder Where 다중 파라미터 사용 동적 쿼리 - BooleanBuilderBooleanBuilder 를 이용해 동적 쿼리를 작성할 수 있다. @Testpublic void dynamicQuery_BooleanBuilder(){ String usernameParam = "member1"; Integer ageParam = 10; List<Member> result = searchMember1(usernameParam, ageParam); 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();} /* select member1 from Member member1 where member1.username = ?1 and member1.age = ?2 */ select member0_.member_id as member_i1_1_, member0_.age as age2_1_, member0_.team_id as team_id4_1_, member0_.username as username3_1_ from member member0_ where member0_.username=? and member0_.age=? 동적 쿼리 - Where 다중 파라미터 사용

0
ProgrammingQueryDSL

QueryDSL - 프로젝션과 결과 반환

목차 QueryDSL - QueryDsl 페이징 사용하기 QueryDSL - 사용자 정의 Repository QueryDSL - 동적 쿼리와 성능 최적화 조회 QueryDSL - 순수 JPA 리포지토리와 Querydsl QueryDSL - SQL Function 호출하기 QueryDSL - 수정, 삭제 벌크 연산 QueryDSL - 동적 쿼리 QueryDSL - 프로젝션과 결과 반환 QueryDSL - 조인 QueryDSL - 집계 QueryDSL - 결과 조회 QueryDSL - 검색 조건 쿼리 QueryDSL - Q-Type 활용 QueryDSL - JPA JPQL vs JPA QueryDSL QueryDSL 시작하기 프로젝션과 결과 반환 프로젝션(Projection) 은 쿼리 결과를 가져올 때 반환할 필드를 지정하는 것 프로젝션 : select 대상 지정 프로젝션 대상이 하나면 타입을 명확하게 지정할 수 있다. 프로젝션 대상이 둘 이상이면 튜플 이나 DTO 로 조회한다. List<String> result = queryFactory .select(member.username.concat("_").concat(member.age.stringValue())) .from(member) .where(member.username.eq("member1")) .fetch(); 튜플 조회@Testpublic void tupleProjection(){ 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); }}

0
ProgrammingQueryDSL

QueryDSL - 조인

목차 QueryDSL - QueryDsl 페이징 사용하기 QueryDSL - 사용자 정의 Repository QueryDSL - 동적 쿼리와 성능 최적화 조회 QueryDSL - 순수 JPA 리포지토리와 Querydsl QueryDSL - SQL Function 호출하기 QueryDSL - 수정, 삭제 벌크 연산 QueryDSL - 동적 쿼리 QueryDSL - 프로젝션과 결과 반환 QueryDSL - 조인 QueryDSL - 집계 QueryDSL - 결과 조회 QueryDSL - 검색 조건 쿼리 QueryDSL - Q-Type 활용 QueryDSL - JPA JPQL vs JPA QueryDSL QueryDSL 시작하기 기본 조인첫 번째 파라미터에 조인 대상을 지정하고, 두번째 파라미터에 별칭으로 사용할 Q 타입을 지정하면 된다. @Testpublic void join(){ List<Member> result = queryFactory.selectFrom(member) .join(member.team, team) .where(team.name.eq("teamA")) .fetch(); assertThat(result) .extracting("username") .containsExactly("member1", "member2");} /* select member1 from Member member1 inner join member1.team as team where team.name = ?1 */ select member0_.member_id as member_i1_1_, member0_.age as age2_1_, member0_.team_id as team_id4_1_, member0_.username as username3_1_ from member member0_ inner join team team1_ on member0_.team_id=team1_.team_id where team1_.name=? Seta Join From 절에 여러 엔티티를 선택해서 세타 조인이 가능하다. 외부 조인 불가능 조인 on 을 사용하면 외부 조인 가능 @Testpublic void setaJoin(){ em.persist(new Member("teamA")); em.persist(new Member("teamB")); List<Member> result = queryFactory.select(member) .from(member, team) .where(member.username.eq(team.name)) .fetch(); assertThat(result) .extracting("username") .containsExactly("teamA", "teamB");}

0
ProgrammingQueryDSL

QueryDSL - 집계

목차 QueryDSL - QueryDsl 페이징 사용하기 QueryDSL - 사용자 정의 Repository QueryDSL - 동적 쿼리와 성능 최적화 조회 QueryDSL - 순수 JPA 리포지토리와 Querydsl QueryDSL - SQL Function 호출하기 QueryDSL - 수정, 삭제 벌크 연산 QueryDSL - 동적 쿼리 QueryDSL - 프로젝션과 결과 반환 QueryDSL - 조인 QueryDSL - 집계 QueryDSL - 결과 조회 QueryDSL - 검색 조건 쿼리 QueryDSL - Q-Type 활용 QueryDSL - JPA JPQL vs JPA QueryDSL QueryDSL 시작하기 집계 함수@Testpublic void aggregation(){ 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); assertThat(tuple.get(member.count())).isEqualTo(4); assertThat(tuple.get(member.age.sum())).isEqualTo(100); assertThat(tuple.get(member.age.avg())).isEqualTo(25); assertThat(tuple.get(member.age.max())).isEqualTo(40); assertThat(tuple.get(member.age.min())).isEqualTo(10);} Group by@Testpublic void group() { 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);}

0
ProgrammingQueryDSL

QueryDSL - 결과 조회

목차 QueryDSL - QueryDsl 페이징 사용하기 QueryDSL - 사용자 정의 Repository QueryDSL - 동적 쿼리와 성능 최적화 조회 QueryDSL - 순수 JPA 리포지토리와 Querydsl QueryDSL - SQL Function 호출하기 QueryDSL - 수정, 삭제 벌크 연산 QueryDSL - 동적 쿼리 QueryDSL - 프로젝션과 결과 반환 QueryDSL - 조인 QueryDSL - 집계 QueryDSL - 결과 조회 QueryDSL - 검색 조건 쿼리 QueryDSL - Q-Type 활용 QueryDSL - JPA JPQL vs JPA QueryDSL QueryDSL 시작하기 Querydsl 결과 조회 fetch : 리스트 조회, 데이터가 없으면 빈 리스트 반환 fetchOne : 단건 조회 결과가 없으면 : null 결과가 둘 이상이면 NonUniqueResultException fetchFirst : limit(1).fetchOne() 와 의미가 동일하다. fetchResults : 페이징 정보 포함, total count 쿼리 추가 실행 fetchCount : count 쿼리로 변경해서 count 수 조회 @Testpublic void resultFetch(){List<Member> fetch = queryFactory.selectFrom(member) .fetch();Member fetchOne = queryFactory.selectFrom(QMember.member) .fetchOne();Member fetchFirst = queryFactory.selectFrom(QMember.member) .fetchFirst();QueryResults<Member> results = queryFactory.selectFrom(member) .fetchResults();results.getTotal();List<Member> content = results.getResults();// Deprecatedlong total = queryFactory .selectFrom(member) .fetchCount();// Count 쿼리 작성long totalCount = queryFactory .select(member.count()) .from(member) .fetchOne(); fetchResults : 페이징 쿼리가 복잡해지면 content 를 가져오는 쿼리와 total count 를 가져오는 쿼리가 다를 때가 있어서 해당 메소드를 사용하면 안된다. 정렬@Testpublic 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();} 페이징

0
ProgrammingQueryDSL

QueryDSL - 검색 조건 쿼리

목차 QueryDSL - QueryDsl 페이징 사용하기 QueryDSL - 사용자 정의 Repository QueryDSL - 동적 쿼리와 성능 최적화 조회 QueryDSL - 순수 JPA 리포지토리와 Querydsl QueryDSL - SQL Function 호출하기 QueryDSL - 수정, 삭제 벌크 연산 QueryDSL - 동적 쿼리 QueryDSL - 프로젝션과 결과 반환 QueryDSL - 조인 QueryDSL - 집계 QueryDSL - 결과 조회 QueryDSL - 검색 조건 쿼리 QueryDSL - Q-Type 활용 QueryDSL - JPA JPQL vs JPA QueryDSL QueryDSL 시작하기 @Testpublic void startQuerydsl(){ QMember qMember = new QMember("m"); Member findMember = queryFactory .select(qMember) .from(qMember) .where(qMember.username.eq("member1")) .fetchOne(); assertThat(findMember.getUsername()).isEqualTo("member1");} @Testpublic void search(){ Member findMember = queryFactory .selectFrom(member) .where(member.username.eq("member1").and(member.age.eq(10))) .fetchOne(); assertThat(findMember.getUsername()).isEqualTo("member1");} and 를 풀어내는 방식 - and 메소드 사용 @Testpublic void search(){ Member findMember = queryFactory .selectFrom(member) .where( member.username.eq("member1").and(member.age.eq(10)) ) .fetchOne(); assertThat(findMember.getUsername()).isEqualTo("member1");} and 를 풀어내는 방식 - 파라미터로 넘기는 방법 @Testpublic void search(){ Member findMember = queryFactory .selectFrom(member) .where( member.username.eq("member1"), member.age.eq(10) ) .fetchOne(); assertThat(findMember.getUsername()).isEqualTo("member1");} Querydsl 에서 사용하는 검색 조건

0
ProgrammingQueryDSL

QueryDSL - Q-Type 활용

목차 QueryDSL - QueryDsl 페이징 사용하기 QueryDSL - 사용자 정의 Repository QueryDSL - 동적 쿼리와 성능 최적화 조회 QueryDSL - 순수 JPA 리포지토리와 Querydsl QueryDSL - SQL Function 호출하기 QueryDSL - 수정, 삭제 벌크 연산 QueryDSL - 동적 쿼리 QueryDSL - 프로젝션과 결과 반환 QueryDSL - 조인 QueryDSL - 집계 QueryDSL - 결과 조회 QueryDSL - 검색 조건 쿼리 QueryDSL - Q-Type 활용 QueryDSL - JPA JPQL vs JPA QueryDSL QueryDSL 시작하기 Q 클래스 인스턴스를 사용하는 2가지 방법 생성자기 parameter 를 넘기게 되면 해당 값으로 alias 가 걸리게 된다. // 별칭 직접 지정 QMember qMember = new QMember("m"); // 기본 인스턴스 사용QMember qMember = QMember.member; use_sql_comments 옵션을 true 로 설정하게 되면 jpql comment 를 볼 수 있다. Configspring: datasource:# url: jdbc:h2:~/querydsl url: jdbc:h2:tcp://localhost/~/querydsl username: sa password: driver-class-name: org.h2.Driver jpa: hibernate: ddl-auto: create-drop properties: hibernate:# show_sql: true format_sql: true use_sql_comments: truelogging.level: org.hibernate.SQL: debug # org.hibernate.type: trace /* select m from Member m where m.username = :username */ select member0_.member_id as member_i1_1_, member0_.age as age2_1_, member0_.team_id as team_id4_1_, member0_.username as username3_1_ from member member0_ where member0_.username=?