Full Text Search(FTS) is a great mechanism that can be used to search the database against a search term. FTS can produce miraculous results when used efficiently. The PostgreSQL database has inbuilt library for FTS and provides various functions to make use of it. It also has a special data type called "tsvector" to store the keywords(tokens) that take part in the searching process. We are not going to discuss about how Postgres handles FTS, but we will discuss about how to use this with Hibernate HQL(Hibernate Query Language). For more info about full text searching with postgres, you can visit This link.
To use FTS with Hibernate, we will have to first create a POJO (say PostgreSQLFullTextSearchFunction) which implements SQLFunction interface. We are going to use the custom Dialect java class (say 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.
1. First create a class named PostgreSQLFullTextSearchFunction with the following code in it:
To use FTS with Hibernate, we will have to first create a POJO (say PostgreSQLFullTextSearchFunction) which implements SQLFunction interface. We are going to use the custom Dialect java class (say 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.
1. 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. Use the following snippet of code:
public class CustomPostgresDialect extends PostgreSQLDialect {
public CustomPostgresDialect() {
registerFunction("fts", new PostgreSQLFullTextSearchFunction());
}
}
3. Use the custom dialect in session factory configuration in spring(any framework). Using properties file, it 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. Now, we are ready to use the custom registered function fts for searching 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;
Here, "pg_catalog.english" tells postgres that we want to use the English dictionary for the searching. "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 saves somebody's precious time!!
