Oracle full text search in JPA level

So let's go back to our Oracle full text search. i found the performance is not so bad, you can read in the previous post what to in database level.

I usually build Predicates for Specifications. whit this solution it is very easy to extend a query if you have many criteria.

We saw that for the full text search we used the CONTAINS Oracle function in the SELECT statement. We can call Oracle predefined functions by the CriteriaBuilder.function() method. Check the solution here:

public static Specification<TDocuLanguageFO> textContains(final String text) {
return new Specification<TDocuLanguageFO>() {
@Override
public Predicate toPredicate(Root<TDocuLanguageFO> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
Expression<String> x = root.get(TDocuLanguageFO_.contentTxtCb);

Expression<BigDecimal> function = cb.function("CONTAINS",
                                                  BigDecimal.class, 
                                                  x, 
                                                  cb.literal(text));

return cb.greaterThan(function, BigDecimal.ZERO);
}
};
}

In this example I want to highlight also the literal() method of the Criteriabuilder. We have the text parameter for the search, with this literal() function we can convert it to Expression<String> from String.

And we can put the condition to the WHERE clause like this:

Specifications<TDocuLanguageFO> spec =                                                       Specifications.where(textContains(KEYWORD));

List<TDocuLanguageFO> result = dao.findAll(spec);

select null

Once you feel, you do not need the projection of a select statement, when you use the exits operator.

1) Select * from apple a where a.id = 1;

2) Select * from apple a where exists (select * from status s where s.id = a.status_id and s.code
='RED');

3) Select * from apple a where exists (select s.id from status s where s.id = a.status_id and s.code ='RED');

4) Select * from apple a where exists (select null from status s where s.id = a.status_id and s.code ='RED');

Really, we do not need that data to read, fetch, put to the memory, etc.

But the Hibernate does not allow you to skip:
4) subquery.select(null);
3) subquery.select(s.get(Status_.id));
2) subquery.select(s);

In fact he will force you to call the select() function. if you put there the root object, he will put to the SELECT statement the ID, primary key and run another select to get the data.