QueryDSL Custom Funtion 등록 및 where 절에서 Index 사용하도록 하는 방법

 

아래와 같이 하고 Test를 실행 시켜 보았다, 

//아래와 같이 상속을 받고 CustomDialect 을 만들어 준다. 
public class CustomDialect extends MySQL56SpatialDialect {
	   public CustomDialect() {
        super();
        //여기에서 ?1 ?2 는 Prams 를 받는 순서이다. 
        //StandardBasicTypes.BOOLEAN 는 해당 함수의 리턴 타입이다. 
        //추후 where 절이나 select 절에서 사용하게 된다. 
        this.registerFunction("match", new StandardSQLFunction ("match(?1) against  (?2 in boolean mode)",StandardBasicTypes.BOOLEAN));
    }
}
//위와 같이 적용 되었다면 
//JPA에 해당 hibernate.dialect 을 위에 해당하는 클래스로 설정 했다는 가정하에 아래 설명을 시작한다. 


    @Test
    public  void MysqlFullTextMatch(){
        JPAQueryFactory queryFactory = new JPAQueryFactory(em);
        String text = "test";
        NumberTemplate booleanTemplate = Expressions.numberTemplate(Double.class,"function('match',{0},{1})", QFBall.fBall.ballName, "+ret*");
        List<FBall> fetch = queryFactory.select(QFBall.fBall).from(QFBall.fBall).where(booleanTemplate.isTrue()).fetch();
        for (FBall fBall : fetch) {
            System.out.println(fBall.getBallName());
        }
    }

 

StandardSQLFunction 이 뭔지를 몰라서 이다. 

 

    from
        FBall fball0_ 
    where
        match(?1) against  (?2 in boolean mode)(
            fball0_.ballName, ?
        )=?

 

사용 결과 where 절에서 결과이다. 

 

함수명 그대로 인자로 주는 Params 2개를 집어 넣고 정의 해준 함수를 그냥 앞에 위와 같이 해준다. 

당연히 Full Text match 쿼리는 위와 같이 사용하면 안되기 때문에 Query 에러가 난다. 

 

//이번에는 아래와 같이 SQLFunctionTemplate 으로 변경 하였다
public class CustomDialect extends MySQL56SpatialDialect {
    private MySQLSpatialDialect dialectDelegate = new MySQLSpatialDialect();

    public CustomDialect() {
        super();
        this.registerFunction("distance",new StandardSQLFunction("ST_Distance", StandardBasicTypes.DOUBLE));
        this.registerFunction("match", new SQLFunctionTemplate(StandardBasicTypes.DOUBLE, "match(?1) against  (?2 in boolean mode)"));


    }

 이번에는  SQLFunctionTemplate 를 사용해 주었다, 

 

테스트코드는 동일하게 하였다.

 

    @Test
    public  void MysqlFullTextMatch(){
        JPAQueryFactory queryFactory = new JPAQueryFactory(em);
        String text = "test";

        NumberTemplate booleanTemplate = Expressions.numberTemplate(Double.class,"function('match',{0},{1})", QFBall.fBall.ballName, "+ret*");


        List<FBall> fetch = queryFactory.select(QFBall.fBall).from(QFBall.fBall).where(booleanTemplate.gt(0)).fetch();
        for (FBall fBall : fetch) {
            System.out.println(fBall.getBallName());
        }

    }

 

  from
        FBall fball0_ 
    where
        match(fball0_.ballName) against  (? in boolean mode)>?
2020-04-06 01:23:23.854 TRACE 56044 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [+ret*]
2020-04-06 01:23:23.855 TRACE 56044 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [BOOLEAN] - [false]

 

위와 같이 바인딩 되어 잘 들어 갔다. 

 

해당 테스트로 Expressions 의 Templeate 이해를 높일수 있었다. 

 

참고로 

Expressions.stringTemplate

은 Select 문에서는 사용할수 있었으나. 

where 절에는 사용을 하지 못하였습니다. 

 

where 절에서는 

 

Expressions.booleanTemplate 을 통해 gt >0 을 이용하여 쿼리를 날립니다. 

 

위의 경우 Index을 타는것을 볼수 있습니다. 

 

하지만 booleanTemplate.eq(true) , booleanTemplate.isTure 을 이용하면

 

 

Index을 타지 않는것을 확인 할수 있습니다. 

 

 

확인 결과 몇몇 스페셜 인덱스에서는 위의 방법 또한 인덱스를 타지 않는 경우가 빈번합니다. 

 

그래서 아래는 생각해낸 방법이자 결과 입니다. 

아래와 같이 CustomFuntion을 등록 합니다.

this.registerFunction("match", new SQLFunctionTemplate(StandardBasicTypes.INTEGER, "match(?1) against (?2 in boolean mode) and 1"));

여기서 핵심은 끝에 and 1 입니다. 

 

queryFactory.select(QFBall.fBall).from(QFBall.fBall).where(booleanTemplate.eq(1)).fetch();

booleanTemplate.eq(1)

 

이렇게 사용합니다. 

 

그럼  

 

match(fball0_.ballName) against (? in boolean mode) 
        and 1=?

 

이렇게 쿼리를 실행 하면서  

 

Excetion 을 띄우지 않고 실행 하게 됩니다. 

 

이렇게 실행된 쿼리는 모두 올바르게 Index을 처리 하였습니다. 

 

이 방법은 ST_Within 등 GIS 정보의 데이터 Index 처리 할때 Query SQL의 쿼리에서는 

끝에 =1 을 붙히는등 이상한 쿼리를 실행 시켜서 Index을 안 탈때 나름 해결책입니다.