How to get the SQL String From JPQLQuery

Question

I'm using EclipseLink.

I've a JPQLquery and I want to get the sql String.. Now I'm doing in this way:

EJBQueryImpl qi = (EJBQueryImpl)jpqlQuery;
String sqlQueryString = qi.getDatabaseQuery().getSQLString();

The problem is that in the sqlQueryString the constant are replaced with ?

I've tried to get the values navigating the expressions trees (getSelectionCriteria() and getHavingCriteria()) but in this way I loose the type...

Do any one ever have a problem like this one?

As a follow up question: how do I get the SQL string when I do a em.persist(pojoDbObject)? — OliCoder
– OliCoder, Commented Mar 25, 2014 at 15:00

OliCoder · Accepted Answer · 2014-03-25 14:55:56Z

4

Quoted from the EclipseLink FAQ:

To see the SQL for a JPA Query you can enable logging on FINE or lower.

To get the SQL for a specific Query at runtime you can use the DatabaseQuery API.

Session session = em.unwrap(JpaEntityManager.class).getActiveSession();
DatabaseQuery databaseQuery = ((EJBQueryImpl)query).getDatabaseQuery();
databaseQuery.prepareCall(session, new DatabaseRecord());
String sqlString = databaseQuery.getSQLString();

This SQL will contain ? for parameters. To get the SQL translated with the arguments you need a DatabaseRecord with the parameter values.

Session session = em.unwrap(JpaEntityManager.class).getActiveSession();
DatabaseQuery databaseQuery = ((EJBQueryImpl)query).getDatabaseQuery();
String sqlString = databaseQuery.getTranslatedSQLString(session, recordWithValues);

edited Mar 25, 2014 at 14:55

OliCoder

1,39910 silver badges25 bronze badges

answered Jun 20, 2011 at 20:00

Matt Handy

30.1k3 gold badges91 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

zmeda Over a year ago

What is recordWithValues? Is it possible to get it from DatabaseQuery or EJBQueryImpl?

Sammy S. Over a year ago

For future reference: recordWithValues should be databaseQuery.getTranslationRow() (source: dzone.com/articles/eclipselink-how-get-sql)

nettie · Accepted Answer · 2018-08-07 16:05:53Z

0

Here is a method that works:

public static void logJpaQuery(EntityManager em, TypedQuery tq){
        org.eclipse.persistence.sessions.Session session = em.unwrap(JpaEntityManager.class).getActiveSession();        
        DatabaseQuery dq = ((EJBQueryImpl) tq).getDatabaseQuery();
        AbstractRecord translationRow = dq.getTranslationRow();        
        dq.prepareCall(session, new DatabaseRecord());        
        String translatedSQLString = dq.getTranslatedSQLString(session, translationRow);
        log.trace("translated sql is: {}", translatedSQLString);
    }

answered Aug 7, 2018 at 16:05

nettie

6882 gold badges11 silver badges24 bronze badges

Collectives™ on Stack Overflow

How to get the SQL String From JPQLQuery

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related