Oracle Text Criteria Query in JPA

Question

Is it possible to query JPA criteria using an Oracle Text statement, and if so, how?

Answer 1

It is doubtful. The API exists in all RDBMS and provides certain constructs, such as "LIKE" / "SUBSTRING", which can be mapped to some of this form when used in Oracle for a TEXT column, but again they can simply use standard SQL. There is no standard way to insist on this

Answer 2

Criteria support a function () API that allows you to call a database function by name.

qb.gt(qb.function("CONTAINS", root.get("name"), qb.parameter("name"), qb.literal(1)), 1) 

EclipseLink also supports this in JPQL using the FUNC keyword.

Answer 3

I just wrote an OracleTextDictionary for openjpa that converts ordinary “similar” statements to “contains” statements when the argument has a magic marker prefix.

Thus, you can use QueryDSL or the criteria language (or JPQL) with Oracle text.

The dictionary detects LIKE statements with a magic marker in the argument and overwrites SQL to use the CTX CONTAINS call.

One of the drawbacks is that pricing is not available in a simple way, but it would be possible to improve the driver for an account order. Feel free to edit the code :-)

I would suggest that it could be hibernated, assuming that there is a similar mechanism for tuning database queries to a specific db.

 package se.grynna.dict; import java.util.regex.Matcher; import java.util.regex.Pattern; import org.apache.openjpa.jdbc.kernel.JDBCFetchConfiguration; import org.apache.openjpa.jdbc.sql.OracleDictionary; import org.apache.openjpa.jdbc.sql.SQLBuffer; import org.apache.openjpa.jdbc.sql.Select; public class OracleTextDictionary extends OracleDictionary { public static final String CTX_MAGIC_MARKER = "@ CTX@ "; final static Pattern likePattern = Pattern .compile("t(\\d+)\\.(\\S+) LIKE (\\?)"); @Override protected SQLBuffer toSelect(SQLBuffer select, JDBCFetchConfiguration fetch, SQLBuffer tables, SQLBuffer where, SQLBuffer group, SQLBuffer having, SQLBuffer order, boolean distinct, boolean forUpdate, long start, long end,Select sel) { SQLBuffer sqlBuffer = super.toSelect(select, fetch, tables, where, group, having, order, distinct, forUpdate, start, end, sel); SQLBuffer tmpBuf = sqlBuffer; String sql = tmpBuf.getSQL(); int label = 1; for (Matcher m = likePattern.matcher(sql); m.find(); sql = tmpBuf.getSQL()) { int argPos = m.start(3); int argIdx = findArgIdx(sql, argPos); Object o = tmpBuf.getParameters().get(argIdx); if( o == null) break; String arg = o.toString(); if (arg.startsWith(CTX_MAGIC_MARKER)) { if (tmpBuf == sqlBuffer) { tmpBuf = new SQLBuffer(sqlBuffer); } arg = arg.substring(CTX_MAGIC_MARKER.length()); setParameter(tmpBuf, argIdx, arg); String aliasNo = m.group(1); String colName = m.group(2); } String replace = String.format("(CONTAINS(t%s.%s,?,%d)>0)", aliasNo, colName, label++); tmpBuf.replaceSqlString(m.start(), m.end(), replace); m.reset(tmpBuf.getSQL()); } } return tmpBuf; } @SuppressWarnings("unchecked") private void setParameter(SQLBuffer tmpBuf, int argIdx, String arg) { tmpBuf.getParameters().set(argIdx, arg); } private int findArgIdx(String sql, int argPos) { int count = -1; for (int i = 0; i <= argPos; i++) { char c = sql.charAt(i); if (c == '?') { count++; } } return count; } } 

Example: the following (obviously far-fetched) input with parameters is called:

 :1 "@ CTX@omg near ponies" :2 "@ CTX@rainbow " :3 "@ CTX@rain %" :4 "abc1%" <-- an ordinary like :-) :5 "@ CTX@mushroom %" 

Jpql

 select distinct customer from Customer customer where customer.custName like :a1 and customer.custName like :a2 and customer.custName like :a1 and customer.custId in (select d.custId from Customer d where d.custName like :a3 or d.custName like :a1) 

SQL

 SELECT t0.custId, t0.custName FROM Customer t0 WHERE ((CONTAINS(t0.custName,?,1)>1) AND (CONTAINS(t0.custName,?,2) >1) AND (CONTAINS(t0.custName,?,3) >1) AND t0.custId IN (SELECT t1.custId FROM Customer t1 WHERE (t1.custName LIKE ? <---- the like survives.... OR (CONTAINS(t1.custName,?,1)>1)) )) AND ROWNUM <= ? 

As a side note: QueryDsl actually has a'contains' statement, presumably for the Lucene backend, for which jpa and sql servers generate a 'like' statement.

I did not understand how to overload the contains statement so that it could be used. (Except for rewriting the code, which I cannot do since I am using the version bundled with WebSphere.)

So, I resort to a small static method to make it look good when using QuertyDSL.

 // x.where(c.custName.like(CTX.contains("omg near ponies")))); 

It would be even better if jpql could provide some abstractions (or plugins) for full-text search engines ...