Skip to main content
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
Re: [eclipselink-users] Cannot get the JPQL & SQL String of a CriteriaQuery
Thanks. That gave me an idea... writing a blog post about how to get a JPQL/SQL string representation of a CriteriaQuery using the 3 main JPA ORM frameworks (EclipseLink, Hibernate, OpenJPA)... and also to send an email to the JPA 2.1 Expert Members to see if they could add this feature directly in the Query API.

Antonio

2012/5/22 Christopher Delahunt <christopher.delahunt@xxxxxxxxxx>
There is an example posted here:
http://wiki.eclipse.org/EclipseLink/FAQ/JPA#How_to_get_the_SQL_for_a_Query.3F




On 22/05/2012 11:13 AM, Tom Ware wrote:
Hi Antonio,

prepareCall is the method you want. You should be able to get the
Session using our JpaHelper. There are several methods you could use
here's an example.

org.eclipse.persistence.jpa.JpaHelper.getDatabaseSession(myEntityManagerFactory)


For the second argument, just supply an empty
org.eclipse.persistence.sessions.DatabaseRecord

-Tom

On 22/05/2012 10:51 AM, Antonio Goncalves wrote:
Hi Tom,

Thanks for the information. Indeed, when I execute the query once
(calling
getResultList()) I can get the SQL string :

findAllBooksCriteriaQuery.*getResultList*();
System.out.println("SQL > " +
findAllBooksCriteriaQuery.unwrap(EJBQueryImpl.class).getDatabaseQuery().getSQLString());


But how do you prepare it ? I've tried the following but it doesn't
work :

findAllBooksCriteriaQuery.unwrap(EJBQueryImpl.class).getDatabaseQuery().*prepareForExecution*();


The only method prepareCall I found needs some parameter :
prepareCall(Session
session, Record translationRow)

Thanks
Antonio

2012/5/22 Tom Ware <tom.ware@xxxxxxxxxx <mailto:tom.ware@xxxxxxxxxx>>

Hi Antonio,

EclipseLink will not translate a Criteria query into JPQL for you. The
getJPQLString() method will only work for queries originally written
in JPQL.

The getSQLString() method relies on the query being "prepared" as
indicated in the comment for that method. One you have run the query
once,
it will be prepared. Alternately, you should be able to call prepareCall,
or prepareCalls on it to cause it to prepare.

-Tom


On 21/05/2012 9:17 AM, Antonio Goncalves wrote:

Hi all,

I need to get the JPQL and/or SQL String of a type safe query... but I
can't
make it work. Let's take a simple example : find all the Book entities.
Here is
what I can do with a simple dynamic query :

TypedQuery<Book> findAllBooksQuery = em.createQuery("SELECT b FROM Book
b ORDER
BY b.id <http://b.id> <http://b.id> DESC", Book.class);

System.out.println("JPQL " +
findAllBooksQuery.unwrap(__EJBQueryImpl.class).__getDatabaseQuery().__getJPQLString());

System.out.println("SQL " +
findAllBooksQuery.unwrap(__EJBQueryImpl.class).__getDatabaseQuery().__getSQLString());


As you can see, I use the unwrap(EJBQueryImpl.class) method to get the
needed
string. And this works fine. But if I do the following, I get a null
(for both
JPQL and SQL String) :

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Book> q = cb.createQuery(Book.class);
Root<Book> b = q.from(Book.class);
q.select(b).orderBy(cb.desc(b.__get("id")));
TypedQuery<Book> findAllBooksCriteriaQuery = em.createQuery(q);
System.out.println("JPQL " +
findAllBooksCriteriaQuery.__unwrap(EJBQueryImpl.class).__getDatabaseQuery().__getJPQLString());

System.out.println("SQL " +
findAllBooksCriteriaQuery.__unwrap(EJBQueryImpl.class).__getDatabaseQuery().__getSQLString());


Any idea what this happens ? I am using the right implementation (i.e.
EJBQueryImpl) ?

Thanks
Antonio


_________________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx <mailto:eclipselink-users@eclipse.org>
https://dev.eclipse.org/__mailman/listinfo/eclipselink-__users
<https://dev.eclipse.org/mailman/listinfo/eclipselink-users>

_________________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx <mailto:eclipselink-users@eclipse.org>
https://dev.eclipse.org/__mailman/listinfo/eclipselink-__users
<https://dev.eclipse.org/mailman/listinfo/eclipselink-users>




--
--
Antonio Goncalves (antonio.goncalves@xxxxxxxxx
<mailto:antonio.goncalves@gmail.com>)
Software architect

Web site : www.antoniogoncalves.org <http://www.antoniogoncalves.org>
Blog: agoncal.wordpress.com <http://agoncal.wordpress.com>
Feed: feeds2.feedburner.com/AntonioGoncalves
<http://feeds2.feedburner.com/AntonioGoncalves>
Paris JUG leader : www.parisjug.org <http://www.parisjug.org>
LinkedIn: www.linkedin.com/in/agoncal
<http://www.linkedin.com/in/agoncal>


_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users



--
--
Antonio Goncalves (antonio.goncalves@xxxxxxxxx)
Software architect

Web site : www.antoniogoncalves.org
Blog: agoncal.wordpress.com
Feed: feeds2.feedburner.com/AntonioGoncalves
Paris JUG leader : www.parisjug.org
LinkedIn: www.linkedin.com/in/agoncal

Back to the top