openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Pinaki Poddar <>
Subject Re: Is this possible with CriteriaBuilder method
Date Fri, 22 Apr 2011 17:16:56 GMT
> Can you provide a demonstrated example that this can be done. 

Do not know what exact form of demonstration you are looking for. But
anyway, given that I have ample bandwidth to fulfill such request, something
similar to the following may *demonstrate* what can or can not be expressed
with this API. 

I am sure you will be able to edit the literals as per your choice, or tune
the shape of the result further via multiselect(). I have also omitted the
where clause or ordering as in your original request because they did not
seem be relevant to the heart of your confusion. 
BTW, the multiselect() as per spec has a rather involved semantics, on top
of that OpenPA supports nesting of shapes (see ...ResultShape javadoc in
public void testDatabaseFunctionInProjection() {
	EntityManager em = emf.createEntityManager();
	CriteriaBuilder cb = emf.getCriteriaBuilder();
	CriteriaQuery&lt;Object[]&gt; c = cb.createQuery(Object[].class);
	Root<Book> b = c.from(Book.class);
	Expression<String> substr = cb.function("SUBSTRING_INDEX", String.class,
b.get("content"), cb.literal(" "), cb.literal(20));
	Expression<String> replacedStr = cb.function("REPLACE", String.class,
substr, cb.literal("<br>"), cb.literal(" "));
        c.multiselect(b.get("title"), replacedStr);
	List&lt;Object[]&gt; result = em.createQuery(c).getResultList();
	for (int i = 0; i &lt;result.size(); i++) {
	    System.err.println(&quot;[&quot;+i+&quot;]&quot; +

with all that code, you should see a SQL query like:
SELECT t0.title, REPLACE(SUBSTRING_INDEX(t0.content, ?, ?), ?, ?) FROM Book
t0 [params=(String)  , (int) 20, (String) &lt;br&gt;, (String)  ]

Another helpful tip:
OpenJPA implementation of the criteria query prints a JPQL equivalent string
in its toString() method. 
The above example Criteria Query will stringify as:
SELECT b.title, REPLACE(SUBSTRING_INDEX(b.content,' ',20),'br>',' ') FROM
Book b

It is JPQL-like in the sense that the string is *not* a valid JPQL query.
That is an example of how a expressiveness of CriteriaQuery API can exceed
that of string-based query language.

About the @rownum trick, you can simply print the index of the Object[] row
as it appears in the List&lt;Object[]&gt; that the query returns. 

View this message in context:
Sent from the OpenJPA Users mailing list archive at

View raw message