PostgreSQL’s Full Text Search With Hibernate
Full Text Search (FTS) is a great mechanism that can be used to search the database against a search term. FTS can produce better results than conventional ways of querying, when used efficiently. The PostgreSQL database has inbuilt library for FTS and provides various functions to make use of it. There is a special data type called “tsvector” to store the keywords (tokens) that are non-trivial in the whole process. We are not going to discuss about how Postgres handles FTS, but we will discuss about how to use this functionality with Hibernate HQL (Hibernate Query Language). For more info about full text search with Postgres, you can visit This link.
To use FTS with Hibernate, we will have to first create a POJO (PostgreSQLFullTextSearchFunction) which implements SQLFunction interface. We will use custom Dialect java class (CustomPostgresDialect) in the SessionFactory configuration. In this custom dialect class, you will have to register a new SQL function by passing the object of PostgreSQLFullTextSearchFunction class in the registerFunction() method called in the constructor of CustomPostgresDialect class.
- First create a class named PostgreSQLFullTextSearchFunction with the following code in it:
public class PostgreSQLFullTextSearchFunction implements SQLFunction {
@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;
}
@SuppressWarnings("rawtypes")
@Override
public String render(List args, SessionFactoryImplementor factory)
throws QueryException {
if (args!= null && args.size() < 2) {
throw new IllegalArgumentException(
"The function must be passed 2 arguments");
}
String fragment = null;
String ftsConfig = null;
String field = null;
String value = null;
if(args.size() == 3) {
ftsConfig = (String) args.get(0);
field = (String) args.get(1);
value = (String) args.get(2);
fragment = field+" @@ to_tsquery("+ftsConfig+", "+value+")";
} else {
field = (String) args.get(0);
value = (String) args.get(1);
fragment = field+" @@ to_tsquery("+value+")";
}
return fragment;
}
}2. Now, we have to create the custom dialect for registering the newly created function with the following code snippet:
public class CustomPostgresDialect extends PostgreSQLDialect {
public CustomPostgresDialect() {
registerFunction("fts", new PostgreSQLFullTextSearchFunction());
}
}3. Use the custom dialect in session factory configuration in spring (any framework) as defined in the properties file, which will look like:
jdbc.driverClassName=org.postgresql.Driver
jdbc.url=jdbc:postgresql://localhost:5432/db_name
jdbc.username=postgres
jdbc.password=postgres
hibernate.dialect=package_name.CustomPostgresDialect
hibernate.hbm2ddl.auto=update
hibernate.show_sql=false
hibernate.format_sql=false4. At this point, we are ready to use the custom registered function fts to search using HQL as shown below:
String queryString = "select COUNT(sr.id) from SiteReview sr where fts('pg_catalog.english', sr.searchvector, :vector) = true";
Query query = getSession().createQuery(queryString).setCacheable(true);
query.setParameter("vector", "String-To-Be-Searched");
count = query.uniqueResult()!=null ? ((Long) query.uniqueResult()).intValue() : 0;Argument “pg_catalog.english” in fts() method tells Postgres that we want to use the English dictionary for the lookup. The “searchvector” is the property of SiteReview model class mapped to the corresponding column in the database i.e. column with data type tsvector. Rest of the code is self explanatory. Hope this helps and save your time!!