[Spring] JPA Querydsl 사용하기 (group_concat 함수)

1. Querydsl 사용 설정

 

1-1) build.gradle 설정

• querydsl-jpa : 실제 애플리케이션에서 Querydsl 을 사용할때 필요한 라이브러리
• querydsl-apt : Q 클래스를 만드는 용도

  

    
    
    
    
  
// querydsl 버전
buildscript {
    ext {
        queryDslVersion = "5.0.0"
    }
}
plugins {
    id 'org.springframework.boot' version '2.7.3'
    id 'io.spring.dependency-management' version '1.0.13.RELEASE'
    id 'java'
    // querydsl 플러그인
    id "com.ewerk.gradle.plugins.querydsl" version "1.0.10"
}

group = 'com.sto.sale'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '11'

repositories {
    mavenCentral()
}

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-thymeleaf'
    implementation 'org.springframework.boot:spring-boot-starter-web'
    implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
    implementation 'mysql:mysql-connector-java'
    implementation 'org.projectlombok:lombok'
    implementation 'javax.validation:validation-api:2.0.1.Final'
    implementation 'org.modelmapper:modelmapper:3.1.0'
    testImplementation 'org.springframework.boot:spring-boot-starter-test'
	
    //querydsl 관련 dependency 추가
    implementation "com.querydsl:querydsl-jpa:${queryDslVersion}"
    implementation "com.querydsl:querydsl-apt:${queryDslVersion}"
}

tasks.named('test') {
    useJUnitPlatform()
}

// querydsl 사용할 경로 지정합니다. 현재 지정한 부분은 .gitignore에 포함되므로 git에 올라가지 않습니다.
def querydslDir = "$buildDir/generated/'querydsl'"

// JPA 사용여부 및 사용 경로 설정
querydsl {
    jpa = true
    querydslSourcesDir = querydslDir
}

// build시 사용할 sourceSet 추가 설정
sourceSets {
    main.java.srcDir querydslDir
}


// querydsl 컴파일 시 사용할 옵션 설정
compileQuerydsl {
    options.annotationProcessorPath = configurations.querydsl
}

// querydsl이 compileClassPath를 상속하도록 설정
configurations {
    compileOnly {
        extendsFrom annotationProcessor
    }
    querydsl.extendsFrom compileClasspath
}

 

1-2) Config 설정

JPAQueryFactory를 @Bean으로 등록

 

back-sto-sale/src/main/java/com/sto/sale/backstosale/config/SpringConfig.java

  

    
    
    
    
  
    private final EntityManager em;

    @Autowired
    public SpringConfig(ProductRepository productRepository, SaleRepository saleRepository, UserRepository userRepository, HoldingRepository holdingRepository, TransactionRepository transactionRepository, EntityManager em) {
        this.productRepository = productRepository;
        this.saleRepository = saleRepository;
        this.userRepository = userRepository;
        this.holdingRepository = holdingRepository;
        this.transactionRepository = transactionRepository;
        this.em = em;
    }
    
	@Bean
    public JPAQueryFactory jpaQueryFactory(EntityManager em) {
        return new JPAQueryFactory(em);
    }

 

1-3) group_concat 함수 사용 설정

 

back-sto-sale/src/main/java/com/sto/sale/backstosale/sql/CustomMysqlDialect.java

  

    
    
    
    
  
public class CustomMysqlDialect implements MetadataBuilderContributor {
	@Override
	public void contribute(MetadataBuilder metadataBuilder) {
		metadataBuilder.applySqlFunction("group_concat",
				new StandardSQLFunction("group_concat", StandardBasicTypes.STRING));
	}
}

 

1-4) application.properties 에 위에서 구현한 CustomMysqlDialect 클래스 추가

 

back-sto-sale/src/main/resources/application.properties

  

    
    
    
    
  
spring.jpa.properties.hibernate.metadata_builder_contributor=com.sto.sale.backstosale.sql.CustomMysqlDialect

 

 

 

2. Q클래스 생성

Tasks > build > build 실행

 

build.gradle에서 지정한 경로에서 Q클래스 생성 확인

 

3. Querydsl 사용하기

Q클래스 이용 :

  

    
    
    
    
  
QHolding qHolding = QHolding.holding;

 

back-sto-sale/src/main/java/com/sto/sale/backstosale/service/HoldingService.java

  

    
    
    
    
  
	@Autowired
	JPAQueryFactory jpaQueryFactory;
    
	/**
	 * 상품 별 판매 개수, 보유자 리스트
	 */
	public List<GoodsHoldingDto> findListHolding() {
		QHolding qHolding = QHolding.holding;
		List<GoodsHoldingDto> list = jpaQueryFactory
				.select(Projections.fields(
						GoodsHoldingDto.class,
						product.goods_id.as("goodsId"),
						product.goods_nm.as("goodsNm"),
						qHolding.goods_cnt.sum().as("sumGoodsCnt"),
						Expressions.stringTemplate("group_concat({0})", qHolding.user.user_id).as("userIds")
				))
				.from(qHolding)
				.join(qHolding.product, product)
				.join(qHolding.user, user)
				.groupBy(qHolding.product.goods_id)
				.fetch();

		return list;
	}

	/**
	 * 유저 별 보유 상품 개수, 보유 상품 리스트
	 */
	public List<UserHoldingDto> findListUserHolding() {
		QHolding qHolding = QHolding.holding;
		List<UserHoldingDto> userList = jpaQueryFactory
				.select(Projections.fields(
						UserHoldingDto.class,
						user.user_id.as("userId"),
						user.user_nm.as("userNm"),
						Expressions.stringTemplate("group_concat({0})", qHolding.product.goods_id).as("goodsIds"),
						Expressions.stringTemplate("group_concat({0})", qHolding.goods_cnt).as("goodsCnts")
				))
				.from(qHolding)
				.join(qHolding.product, product)
				.join(qHolding.user, user)
				.groupBy(qHolding.user.user_id)
				.fetch();

		return userList;
	}

 

Unsupported expression 에러 ==> 

2022.10.09 - [Java/Spring] - [Spring] JPA Querydsl Unsupported expression

 

 

참고 :

 

https://dingdingmin-back-end-developer.tistory.com/entry/Spring-Data-JPA-7-Querydsl-%EC%82%AC%EC%9A%A9-gradle-7x

https://stackoverflow.com/questions/57385780/how-to-register-non-standarized-sql-functions-manually-in-spring-boot-applicatio

https://web2eye.tistory.com/241

https://madplay.github.io/post/introduction-to-querydsl

http://querydsl.com/static/querydsl/latest/reference/html/ch02s03.html#d0e1067

https://jojoldu.tistory.com/372