Home

2022
0
SpringSpring Data JPA

Spring Data JPA - 벌크성 수정 쿼리

목차 Spring Data JPA - 벌크성 수정 쿼리 Spring Data JPA - Convertor Spring Data JPA - Auditing Spring Data JPA - Paging Request Paramater Spring Data JPA - 페이징과 정렬 Post not found: spring/spring-data-jpa/07-spring-data-jpa Spring Data JPA - 반환 타입 Spring Data JPA - Query 파라미터 바인딩 Spring Data JPA - Query 를 이용한 조회 결과를 특정 값으로 반환하기 Spring Data JPA - JPQL (Java Persistence Query Lange) 사용하기 Spring Data JPA - 메소드 이름으로 쿼리 생성하기 Spring Data JPA - 시작하기 벌크성 수정 쿼리public int bulkAgePlus(int age) { int resultCount = em.createQuery("update Member m set m.age = m.age + 1 where m.age >= :age") .setParameter("age", age) .executeUpdate(); return resultCount;} @Testpublic void bulkUpdate(){ memberJpaRepository.save(new Member("member1", 10)); memberJpaRepository.save(new Member("member2", 19)); memberJpaRepository.save(new Member("member3", 20)); memberJpaRepository.save(new Member("member4", 21)); memberJpaRepository.save(new Member("member5", 40)); int resultCount = memberJpaRepository.bulkAgePlus(20); assertThat(resultCount).isEqualTo(3);} Spring JPA 사용하기@Modifying@Query("update Member m set m.age = :age+1 where m.age >= :age")int bulkAgePlus(@Param("age") int age); 벌크성 쿼리는 영속성 Context를 무시하고 바로 DB에 업데이트를 진행 시키기 때문에 벌크성 쿼리를 수행한 후에는 영속성 Context를 초기화 해줄 필요가 있다. 안그러면 기존에 남아있는데이터가 꼬여서 문제가 발생할 수 있다. @Testpublic void bulkUpdate(){ memberRepository.save(new Member("member1", 10)); memberRepository.save(new Member("member2", 19)); memberRepository.save(new Member("member3", 20)); memberRepository.save(new Member("member4", 21)); memberRepository.save(new Member("member5", 40)); int resultCount = memberRepository.bulkAgePlus(20); // 벌크성 쿼리를 실행한 후 영속성 컨텍스트를 날려준다. entityManager.flush(); entityManager.clear(); assertThat(resultCount).isEqualTo(3);}

0
DevelopQueryDSL

QueryDSL - 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 페이징public interface MemberRepositoryCustom { List<MemberTeamDto> search(MemberSearchCondition condition); Page<MemberTeamDto> searchPageSimple(MemberSearchCondition condition, Pageable pageable); Page<MemberTeamDto> searchPageComplex(MemberSearchCondition condition, Pageable pageable);} QueryDSL 에서 제공하는 fetchResults 를 사용하게 되면 데이터를 가져오는 쿼리 와 가져온 데이터 수를 확인하는 Count 쿼리 총 2개의 쿼리가 발생하게 된다. @Overridepublic Page<MemberTeamDto> searchPageSimple(MemberSearchCondition condition, Pageable pageable) { QueryResults<MemberTeamDto> results = 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( usernameEq(condition.getUsername()), teamNameEq(condition.getTeamName()), ageGoe(condition.getAgeGoe()), ageLoe(condition.getAgeLoe()) ) .offset(pageable.getOffset()) .limit(pageable.getPageSize()) .fetchResults(); // fetchResults 를 사용하게 되면 count 쿼리가 같이 나가게 된다. List<MemberTeamDto> content = results.getResults(); long total = results.getTotal(); return new PageImpl<>(content, pageable, total);} Querydsl 페이징 쿼리와 Count 쿼리 분리@Overridepublic Page<MemberTeamDto> searchPageComplex(MemberSearchCondition condition, Pageable pageable) { // 전체 내용을 가져오는 쿼리 List<MemberTeamDto> content = 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( usernameEq(condition.getUsername()), teamNameEq(condition.getTeamName()), ageGoe(condition.getAgeGoe()), ageLoe(condition.getAgeLoe()) ) .offset(pageable.getOffset()) .limit(pageable.getPageSize()) .fetch(); // 전체 Count 를 가져오는 쿼리 long total = queryFactory .select(ExpressionUtils.count(member)) .from(member) .leftJoin(member.team, team) .where( usernameEq(condition.getUsername()), teamNameEq(condition.getTeamName()), ageGoe(condition.getAgeGoe()), ageLoe(condition.getAgeLoe()) ) .fetchCount(); return new PageImpl<>(content, pageable, total);} 스프링 데이터 페이징 활용2 - CountQuery 최적화

0
DevelopQueryDSL

QueryDSL - 사용자 정의 Repository

목차 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 시작하기 참고 https://docs.spring.io/spring-data/jpa/docs/2.1.3.RELEASE/reference/html/#repositories.custom-implementations 사용자 정의 Repository 사용법 사용자 정의 인터페이스 작성 사용자 정의 인터페이스 구현 스프링 데이터 Repository 에 사용자 정의 인터페이스 상속 1. 사용자 정의 인터페이스 작성public interface MemberRepositoryCustom { List<MemberTeamDto> search(MemberSearchCondition condition);} 2. 사용자 정의 인터페이스 구현

0
DevelopQueryDSL

QueryDSL - 순수 JPA 리포지토리와 Querydsl

목차 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 시작하기 순수 JPA 리포지토리와 Querydsl@Repositorypublic class MemberJpaRepository { private final EntityManager em; private final JPAQueryFactory queryFactory; public MemberJpaRepository(EntityManager em){ this.em = em; this.queryFactory = new JPAQueryFactory(em); } public void save(Member member){ em.persist(member); } public Optional<Member> findById(Long id){ Member findMember = em.find(Member.class, id); return Optional.ofNullable(findMember); } public List<Member> findAll(){ return em.createQuery("select m from Member m", Member.class) .getResultList(); } public List<Member> findByUsername(String username){ return em.createQuery("select m from Member m where m.username = :username", Member.class) .setParameter("username", username) .getResultList(); }} SpringBootTest@Transactionalclass MemberJpaRepositoryTest { @Autowired EntityManager em; @Autowired MemberJpaRepository memberJpaRepository; @Test public void basicTest(){ Member member = new Member("member1", 10); memberJpaRepository.save(member); Member findMember = memberJpaRepository.findById(member.getId()).get(); assertThat(findMember).isEqualTo(member); List<Member> result = memberJpaRepository.findAll(); assertThat(result).containsExactly(member); List<Member> result2 = memberJpaRepository.findByUsername("member1"); assertThat(result2).containsExactly(member); }} JPQL QueryDsl 로 변경public List<Member> findAll(){ return em.createQuery("select m from Member m", Member.class) .getResultList();}public List<Member> findAll_Querydsl(){ return queryFactory .selectFrom(member) .fetch();}public List<Member> findByUsername(String username){ return em.createQuery("select m from Member m where m.username = :username", Member.class) .setParameter("username", username) .getResultList();}public List<Member> findByUsername_Querydsl(String username){ return queryFactory .selectFrom(member) .where(member.username.eq(username)) .fetch();} @Test public void basicQueryDsl(){ Member member = new Member("member1", 10); memberJpaRepository.save(member); Member findMember = memberJpaRepository.findById(member.getId()).get(); assertThat(findMember).isEqualTo(member); List<Member> result = memberJpaRepository.findAll_Querydsl(); assertThat(result).containsExactly(member); List<Member> result2 = memberJpaRepository.findByUsername_Querydsl("member1"); assertThat(result2).containsExactly(member); }

0
DevelopQueryDSL

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
DevelopQueryDSL

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
DevelopQueryDSL

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
DevelopQueryDSL

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
DevelopQueryDSL

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
DevelopQueryDSL

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");}