You are viewing a plain text version of this content. The canonical link for it is here.

Posted to users@openjpa.apache.org by chuongpham <ch...@gmail.com> on 2011/04/18 13:01:16 UTC

Is this possible with CriteriaBuilder method

Can I convert this native MySQL query using purely the CriteriaBuilder
method:

SELECT @rownum:=@rownum+1 'no', m.title, m.author,
REPLACE(SUBSTRING_INDEX(m.content, ' ', 20), '<br>', ' '), m.viewed,
m.hashid FROM book m, (SELECT @rownum:=0) r WHERE m.lang = ?1 AND m.title
like CONCAT('%',?2,'%') ORDER BY m.title asc

I can't find a similar example to reference from the JEE 6 Tutorial
available at Oracle website.


--
View this message in context: http://openjpa.208410.n2.nabble.com/Is-this-possible-with-CriteriaBuilder-method-tp6283183p6283183.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: Is this possible with CriteriaBuilder method

Posted by chuongpham <ch...@gmail.com>.

Thanks, Kevin. My original post had "@rownum:=@rownum+1" in it because at the
time I couldn't figure out a way to number return records using Primefaces
dataTable component. I have now so I have modified my original post.

Still, I find this query "SELECT m.title, m.author,
REPLACE(SUBSTRING_INDEX(m.content, ' ', 20), '<br>', ' '), m.viewed,
m.hashid" hard to implement with the CriteriaBuilder method. I don't know if
it's possible - haven't seen anything similar in the JEE 6 Tutorial at
Oracle website.

My understanding of the CriteriaBuilder method is that by default, the
method will select all columns identified in your entity. Unless someone
points me in the right direction, I may have to stick with JPQL.

--
View this message in context: http://openjpa.208410.n2.nabble.com/Is-this-possible-with-CriteriaBuilder-method-tp6283183p6290838.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: Is this possible with CriteriaBuilder method

Posted by Kevin Sutter <kw...@gmail.com>.

Hi,
I think the real question is whether your native MySQL query can be
represented with JPQL...  If you can represent this query in the object
space, then it should be doable via either JPQL or the CriteriaBuilder.
Looking at the query below, the only questionable part is the "@rownum" user
variable usage.  The rest of it looks pretty straight forward.

Kevin

On Mon, Apr 18, 2011 at 6:01 AM, chuongpham <ch...@gmail.com> wrote:

> Can I convert this native MySQL query using purely the CriteriaBuilder
> method:
>
> SELECT @rownum:=@rownum+1 'no', m.title, m.author,
> REPLACE(SUBSTRING_INDEX(m.content, ' ', 20), '<br>', ' '), m.viewed,
> m.hashid FROM book m, (SELECT @rownum:=0) r WHERE m.lang = ?1 AND m.title
> like CONCAT('%',?2,'%') ORDER BY m.title asc
>
> I can't find a similar example to reference from the JEE 6 Tutorial
> available at Oracle website.
>
>
> --
> View this message in context:
> http://openjpa.208410.n2.nabble.com/Is-this-possible-with-CriteriaBuilder-method-tp6283183p6283183.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>

Re: Is this possible with CriteriaBuilder method

Posted by chuongpham <ch...@gmail.com>.

Thanks, Pinaki, but I have already seen that article from IBM before seeing
your reply. I couldn't see anything there remotely resembling - or, close to
resembling - what I wanted to do with my query.

Can you provide a demonstrated example that this can be done. Thanks.

--
View this message in context: http://openjpa.208410.n2.nabble.com/Is-this-possible-with-CriteriaBuilder-method-tp6283183p6290854.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: Is this possible with CriteriaBuilder method

Posted by chuongpham <ch...@gmail.com>.

Appreciate the link, Pinaki. I'm using EclipseLink and I'm getting this
error:

Internal Exception: Exception [EclipseLink-7161] (Eclipse Persistence
Services - 2.2.0.v20110202-r8913):
org.eclipse.persistence.exceptions.ValidationException
Exception Description: Entity class [class com.ckd.model.BookModel] has no
primary key specified. It should define either an @Id, @EmbeddedId or an
@IdClass. If you have defined PK using any of these annotations then make
sure that you do not have mixed access-type (both fields and properties
annotated) in your entity class hierarchy.

Since this is the OpenJPA forum - and my error is related to EclipseLink -
I'll post my error over at the EclipseLink forum and ask them for their
help. Regardless, I thank you for all your help and guidance that you have
posted in this thread. Much appreciated. :)

--
View this message in context: http://openjpa.208410.n2.nabble.com/Is-this-possible-with-CriteriaBuilder-method-tp6283183p6300866.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: Is this possible with CriteriaBuilder method

Posted by Pinaki Poddar <pp...@apache.org>.

An example Ant script [1]. See target name="generate-canonical-model"

[1]
https://fisheye6.atlassian.com/browse/openjpa/trunk/openjpa-examples/openbooks/build.xml?hb=true


-----
Pinaki 
--
View this message in context: http://openjpa.208410.n2.nabble.com/Is-this-possible-with-CriteriaBuilder-method-tp6283183p6300451.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: Is this possible with CriteriaBuilder method

Posted by chuongpham <ch...@gmail.com>.

Thanks, Pinaki. I'm setting some Ant task to create the model definition.
Once I've figure out how to do this, I'll post the changes back here.

--
View this message in context: http://openjpa.208410.n2.nabble.com/Is-this-possible-with-CriteriaBuilder-method-tp6283183p6300413.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: Is this possible with CriteriaBuilder method

Posted by Pinaki Poddar <pp...@apache.org>.

While you are it, you should also seriously consider usng canonical metamodel
classes as opposed to the string based get() method I used in this example.
Otherwise the core advantage of  type-safety is only partially realized
(which effectively takes us back to the basic risk of writing queries that
pass the compiler but fail at runtime). 
So instead of
    c.multiselect(b.get("title"), replacedStr);
you should write
   c.multiselect(b.get(Book_.title), replacedStr); 

where Book_ (with the underscore) is the mea-class that the compiler will
generate while compiling Book.java if you instruct it to do so. Only by
using these canonical meta-classes, you can ascertain type-safety. 
For example, if someone had renamed the Book.title field to Book.xyz, the
Criteria query withh simple get() will still break (as a JPQL), but the
strict typed version will fail at compile time itself saving everybody some
trouble. 

-----
Pinaki 
--
View this message in context: http://openjpa.208410.n2.nabble.com/Is-this-possible-with-CriteriaBuilder-method-tp6283183p6297829.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: Is this possible with CriteriaBuilder method

Posted by chuongpham <ch...@gmail.com>.

Thanks, Pinaki, that's exactly what I meant by a demonstrated example.

A pity that the JEE 6 Tutorial does not have a similar example to your codes
- it would have helped tremendously as far as understanding JPA is
concerned.

As for the WHERE clause of the query, I'll investigate it further on how to
implement it as part of my learning.

Also, I have figured out a way to display the @rownum by using Primefaces
dataTable's rowIndexVar attribute - so I guess I don't need the @rownum as
part of the SQL query anymore.

--
View this message in context: http://openjpa.208410.n2.nabble.com/Is-this-possible-with-CriteriaBuilder-method-tp6283183p6297772.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: Is this possible with CriteriaBuilder method

Posted by Pinaki Poddar <pp...@apache.org>.

> 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
OpenJPA).
 
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; +
Arrays.toString(result.get(i));
	}
}

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. 


-----
Pinaki 
--
View this message in context: http://openjpa.208410.n2.nabble.com/Is-this-possible-with-CriteriaBuilder-method-tp6283183p6297580.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: Is this possible with CriteriaBuilder method

Posted by chuongpham <ch...@gmail.com>.

Can you provide a demonstrated example that this can be done.

--
View this message in context: http://openjpa.208410.n2.nabble.com/Is-this-possible-with-CriteriaBuilder-method-tp6283183p6297336.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: Is this possible with CriteriaBuilder method

Posted by Pinaki Poddar <pp...@apache.org>.

>  gather then that this is not possible with the CriteriaBuilder method?! 

This SQL query:
[SELECT m.title, m.author, REPLACE(SUBSTRING_INDEX(m.content, ' ', 20),
'<br>', ' '), m.viewed, m.hashid FROM book m WHERE m.lang = ?1 AND m.title
like CONCAT('%',?2,'%') ORDER BY m.title asc]
is possible.

And so is getting the relative index o the selected row from the JPA query.

What does not seem possible is to write a query with @rownum to get the
relative index as part of the query result itself. 

-----
Pinaki 
--
View this message in context: http://openjpa.208410.n2.nabble.com/Is-this-possible-with-CriteriaBuilder-method-tp6283183p6297276.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: Is this possible with CriteriaBuilder method

Posted by chuongpham <ch...@gmail.com>.

I gather then that this is not possible with the CriteriaBuilder method?!

--
View this message in context: http://openjpa.208410.n2.nabble.com/Is-this-possible-with-CriteriaBuilder-method-tp6283183p6297254.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: Is this possible with CriteriaBuilder method

Posted by Pinaki Poddar <pp...@apache.org>.

Kevin,
we looked at different queries
I read this one from the (original?) post:
SELECT m.title, m.author, REPLACE(SUBSTRING_INDEX(m.content, ' ', 20),
'<br>', ' '), m.viewed, m.hashid FROM book m WHERE m.lang = ?1 AND m.title
like CONCAT('%',?2,'%') ORDER BY m.title asc 

On a second look I see you post a different one with @rownum in SELECT
projection clause etc.

Firstly, I do not have an answer for that @rownum trick using Criteria API.
Secondly, I though the question is about that REPLACE() function.
Thirdly, the trick is not required at all with JPA query. The @rownumber
stuff add a monotonic row number to the selected results.  But JPA results
are in a java.util.List anyway which is ordered by definition! 

-----
Pinaki 
--
View this message in context: http://openjpa.208410.n2.nabble.com/Is-this-possible-with-CriteriaBuilder-method-tp6283183p6291279.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.

Re: Is this possible with CriteriaBuilder method

Posted by Kevin Sutter <kw...@gmail.com>.

Hi Pinaki,
Can you demonstrate how the "@rownum" user variable could be utilized in
CriteriaBuilder?

SELECT @rownum:=@rownum+1 'no', m.title, m.author,
REPLACE(SUBSTRING_INDEX(m.
content, ' ', 20), '<br>', ' '), m.viewed,
m.hashid FROM book m, (SELECT @rownum:=0) r WHERE m.lang = ?1 AND m.title
like CONCAT('%',?2,'%') ORDER BY m.title asc

I looked through your article and the spec, and couldn't find how to use
this idea.

Thanks,
Kevin


On Wed, Apr 20, 2011 at 3:30 AM, Pinaki Poddar <pp...@apache.org> wrote:

> Hi,
>   > Can I convert this native MySQL query using purely the CriteriaBuilder
> method:
>   Yes, you can.
>
>  Your question points to an important feature of Criteria query that is
> distinct from string-based JPQL query. Criteria query API makes this
> distinction via CriteriaBuilder.function() method -- that will allow you to
> use database functions in your query.
>
>
>  Similar expression is not possible to express with JPQL query that use a
> fixed grammar.
>
>  See usage of CriteriaBuilder.function() method.
>  Or refer to this article [1] which has few examples of this method in its
> 'Advanced Features' section.
>
>
> [1] http://www.ibm.com/developerworks/java/library/j-typesafejpa/
>
> -----
> Pinaki
> --
> View this message in context:
> http://openjpa.208410.n2.nabble.com/Is-this-possible-with-CriteriaBuilder-method-tp6283183p6289977.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>

Re: Is this possible with CriteriaBuilder method

Posted by Pinaki Poddar <pp...@apache.org>.

Hi,
  > Can I convert this native MySQL query using purely the CriteriaBuilder
method: 
  Yes, you can. 

  Your question points to an important feature of Criteria query that is
distinct from string-based JPQL query. Criteria query API makes this
distinction via CriteriaBuilder.function() method -- that will allow you to
use database functions in your query.
 

  Similar expression is not possible to express with JPQL query that use a
fixed grammar.

  See usage of CriteriaBuilder.function() method.
  Or refer to this article [1] which has few examples of this method in its
'Advanced Features' section.


[1] http://www.ibm.com/developerworks/java/library/j-typesafejpa/

-----
Pinaki 
--
View this message in context: http://openjpa.208410.n2.nabble.com/Is-this-possible-with-CriteriaBuilder-method-tp6283183p6289977.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.