I figured out how to do this from this post:
viewtopic.php?f=1&t=944270&start=0The key points are:
(A) recognize that SQLFunctions have to return a value and when used in HQL must be in an expression form (e.g., fts(body, 'dog') = true -- fts(body, 'dog') won't work in HQL)
(B) create a Hibernate SQLFunction for PG FTS; and
(C) remember that the expression "to_tsvector(body) @@ to_tsquery('dog')" evaluates to a boolean
(1) Create a new function class (e.g., PostgreSQLFullTextSearchFunction) which implements SQLFunction. The important method in this class is render().
Code:
package com.acme.hibernate;
import java.util.List;
import org.hibernate.QueryException;
import org.hibernate.dialect.function.SQLFunction;
import org.hibernate.engine.Mapping;
import org.hibernate.engine.SessionFactoryImplementor;
import org.hibernate.type.BooleanType;
import org.hibernate.type.Type;
public class PostgreSQLFullTextSearchFunction implements SQLFunction {
@SuppressWarnings("unchecked")
public String render(List args, SessionFactoryImplementor factory) {
if (args.size() != 3) {
throw new IllegalArgumentException(
"The function must be passed 3 arguments");
}
String ftsConfig = (String) args.get(0);
String field = (String) args.get(1);
String value = (String) args.get(2);
String fragment = null;
if (ftsConfig == null) {
fragment = "to_tsvector(" + field + ") @@ " + "to_tsquery('"
+ value + "')";
} else {
fragment = "to_tsvector(" + ftsConfig + "::regconfig, " + field + ") @@ "
+ "to_tsquery(" + ftsConfig + ", " + value + ")";
}
return fragment;
}
@Override
public Type getReturnType(Type columnType, Mapping mapping)
throws QueryException {
return new BooleanType();
}
@Override
public boolean hasArguments() {
return true;
}
@Override
public boolean hasParenthesesIfNoArguments() {
return false;
}
}
(2) Create a new PostgreSQL Dialect class (e.g., MyPostgreSQLDialect), which extends PostgreSQLDialect, and in the constructor, register a *Hibernate* function that will get translated into the proper PostgreSQL syntax:
Code:
public class MyPostgreSQLDialect extends PostgreSQLDialect {
public MyPostgreSQLDialect() {
registerFunction("fts", new PostgreSQLFullTextSearchFunction());
}
}
(3) Use the new Dialect in your Hibernate/JPA configuration settings. For example, for JPA, in META-INF/persistence.xml:
Code:
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence
http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd"
version="1.0">
<persistence-unit name="test" transaction-type="RESOURCE_LOCAL">
<properties>
<property name="hibernate.dialect"
value="com.acme.hibernate.MyPostgreSQLDialect" />
...
(4) Use the Hibernate function just defined in code (assuming the Sentence class is an entity) in HQL:
Code:
...
List<Sentence> list = em.createQuery("select s from Sentence s where fts('english', s.body, :body) = true")
.setParameter("body", "andrew").getResultList();
...
This generates a query like this:
Code:
select
sentence0_.id as id0_,
sentence0_.body as body0_
from
Sentence sentence0_
where
to_tsvector('english'::regconfig, sentence0_.body) @@ to_tsquery('english', ?)=true
which properly uses the FTS index.
Login
FAQ
Search