[QueryDSL] FullText 검색을 위한 Match ... against 절 사용법

MySQL에서 FullText 인덱스 검색을 하기 위해서는 MATCH(), AGAINST() 절을 사용해야합니다.

(풀텍스트 인덱스에 대한 설명은 다른 블로그 포스팅을 참고해주세요.)

 

기존에 dialect는 MySQL5Dialect를 사용하고 있었습니다.

properties.put("hibernate.dialect", "org.hibernate.dialect.MySQL5Dialect");

 

MySQL5Dialect를 커스텀하겠습니다.

// hibernate 5
package com.moonsiri.config.hibernate.dialect;

import org.hibernate.dialect.MySQL5Dialect;
import org.hibernate.dialect.function.SQLFunctionTemplate;
import org.hibernate.type.StandardBasicTypes;

public class CustomMySQL5Dialect extends MySQL5Dialect {

	public CustomMySQL5Dialect() {
		super();

		registerFunction("match", new SQLFunctionTemplate(StandardBasicTypes.DOUBLE, "MATCH(?1) AGAINST (?2 IN NATURAL LANGUAGE MODE)"));
		registerFunction("matchs", new SQLFunctionTemplate(StandardBasicTypes.DOUBLE, "MATCH(?1, ?2) AGAINST (?3 IN NATURAL LANGUAGE MODE)"));
	}
}

// hibernate 6
package com.moonsiri.config.hibernate.dialect;

import org.hibernate.boot.model.FunctionContributions;
import org.hibernate.dialect.MySQLDialect;
import org.hibernate.query.ReturnableType;
import org.hibernate.query.sqm.function.NamedSqmFunctionDescriptor;
import org.hibernate.query.sqm.function.SqmFunctionRegistry;
import org.hibernate.query.sqm.produce.function.StandardArgumentsValidators;
import org.hibernate.sql.ast.SqlAstNodeRenderingMode;
import org.hibernate.sql.ast.SqlAstTranslator;
import org.hibernate.sql.ast.spi.SqlAppender;
import org.hibernate.sql.ast.tree.SqlAstNode;
import java.util.List;

public class CustomMySQLDialect extends MySQLDialect {

	@Override
	public void initializeFunctionRegistry(FunctionContributions functionContributions) {
		super.initializeFunctionRegistry(functionContributions);

		SqmFunctionRegistry functionRegistry = functionContributions.getFunctionRegistry();
		functionRegistry.register("match", MatchFunction.INSTANCE);
		functionRegistry.register("matchs", MatchsFunction.INSTANCE);
	}

	public static class MatchFunction extends NamedSqmFunctionDescriptor {

		public static final MatchFunction INSTANCE = new MatchFunction();

		public MatchFunction() {
			super("MATCH", false, StandardArgumentsValidators.exactly(2), null);
		}

		@Override
		public void render(SqlAppender sqlAppender, List<? extends SqlAstNode> arguments, ReturnableType<?> returnType, SqlAstTranslator<?> translator) {
			sqlAppender.appendSql("MATCH(");
			translator.render(arguments.get(0), SqlAstNodeRenderingMode.DEFAULT);
			sqlAppender.appendSql(") AGAINST (");
			translator.render(arguments.get(1), SqlAstNodeRenderingMode.DEFAULT);
			sqlAppender.appendSql("IN NATURAL LANGUAGE MODE)");
		}

	}

	public static class MatchsFunction extends NamedSqmFunctionDescriptor {

		public static final MatchsFunction INSTANCE = new MatchsFunction();

		public MatchsFunction() {
			super("MATCHS", false, StandardArgumentsValidators.exactly(3), null);
		}

		@Override
		public void render(SqlAppender sqlAppender, List<? extends SqlAstNode> arguments, ReturnableType<?> returnType, SqlAstTranslator<?> translator) {
			sqlAppender.appendSql("MATCH(");
			translator.render(arguments.get(0), SqlAstNodeRenderingMode.DEFAULT);
			sqlAppender.appendSql(",");
			translator.render(arguments.get(1), SqlAstNodeRenderingMode.DEFAULT);
			sqlAppender.appendSql(") AGAINST (");
			translator.render(arguments.get(2), SqlAstNodeRenderingMode.DEFAULT);
			sqlAppender.appendSql("IN NATURAL LANGUAGE MODE)");
		}

	}
}

dialect 속성값을 커스텀한 MySQL5Dialect로 변경합니다.

properties.put("hibernate.dialect", "com.moonsiri.config.hibernate.dialect.CustomMySQL5Dialect");

 

다음과 같이 사용하면,

public List<PostDTO> findAllByKeyword(String keyword) {
	return jpaQueryFactory.select(Projections.constructor(PostDTO.class, postEntity.id, postEntity.title, postEntity.content))
		.from(postEntity)
		.where(this.searchKeyword(keyword))
		.fetch();
}


private BooleanExpression searchKeyword(String keyword) {
	if (StringUtils.isBlank(keyword)) {
		return null;
	}

	return Expressions.numberTemplate(Double.class,
		"function('matchs', {0}, {1}, {2})", postEntity.title, postEntity.content, keyword).gt(0);
}

 

아래와 같은 쿼리를 얻을 수 있습니다.

SELECT `id`, `title`, `content`
FROM `post`
WHERE MATCH(`title`, `content`) AGAINST (? IN NATURAL LANGUAGE MODE) > ?

 

 

[Reference]

https://idlecomputer.tistory.com/337