Tool to Execute JPQL Queries?

Question

Is there any sort of tool available which allows one to execute JPQL queries against a database "directly"? I would like to type JPQL queries directly into a window and execute them.

Of course it would probably require me to do quite a bit of configuration so that it would be aware of my JPA entities, etc., but I guess it could be done... Anyone know of such a tool?

Thanks.

Answer 1

Did you check whether NetBeans, Eclipse or IntelliJ do what you want?

Especially with the Hibernate Plugin for Eclipse, this should be possible (if you use Hibernate as your JPA Backend).

Answer 2

Until Eclipse Dali gets a generic JPQL editor you can use Hibernate Tools. Hibernate Tools works with Dali and provides a HQL/JPQL query editor which uses Hibernate to execute the queries.

An alternative would be to use the JPA Query Tool [JQT], an Interactive JPA query editor and runner. It might be closer to what you're looking for (runs as a standalone application).

Update: I removed the tool suggested for NetBeans, the project is inactive and doesn't provide anything.

Answer 3

You also have Hibernate Tool Suite, which is based on NetBeans

Answer 4

I think I tried all the tools and IDE's.

In the end, I settled with following bit of code...

The queryEditor runs as a UnitTest.

Assuming you already have UnitTests, you avoid the hassle of configuring PersistenceContexts, DataSources, Drivers, libraries, etc... It also alows you to pass Entity instances as parameters.

And when you're done, copy the query-String to a @NamedParameter definition and set the @Test to enable=false.

import org.apache.commons.lang.builder.ReflectionToStringBuilder;
import org.apache.commons.lang.builder.ToStringStyle;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.testng.annotations.Test;

import javax.persistence.Query;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class TestGeneric extends AbstractModelTest {

    private static final Logger logger = LoggerFactory.getLogger(TestGeneric.class.getName());

    /**
     * This is not a test.  Just a convenience method to edit queries
     */
    @Test(enabled = true)
    public void queryEditor() throws Exception {
        String query = "SELECT mpe " +
                       "  FROM ActiveProduct apt JOIN apt.supportedProduct spt JOIN apt.account act JOIN act.merchantProfile mpe" +
                       " WHERE mpe.id = :mpeId ";
        Class resultClass = MerchantProfile.class;
        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("mpeId", 1L);
        performQuery(query, resultClass, parameters);
    }

    private <T> void performQuery(String jplQuery, Class<T> type, Map parameters) throws Exception {
        Query query = this.em.createQuery(jplQuery, type);

        Set<Map.Entry<String, Object>> rawParameters = parameters.entrySet();
        for (Map.Entry<String, Object> entry : rawParameters) {
            query.setParameter(entry.getKey(), entry.getValue());
        }
        List<T> resultList = query.getResultList();

        if (resultList.size() > 0) {
            int count = 0;
            StringBuffer resultString;
            for (Object o : resultList) {
                resultString = new StringBuffer(++count + " - ");
                dumpObject(o, resultString);
                logger.info(resultString.toString());
            }
        } else {
            logger.info("Empty result list");
        }
    }

    private void dumpObject(Object o, StringBuffer resultString) throws Exception {
        if (o == null) {
            resultString.append("NULL");
        } else if (o instanceof Object[]) {
            Object[] row = (Object[]) o;
            resultString.append("[");
            for (int i = 0; i < row.length; i++) {
                dumpObject(row[i], resultString);
            }
            resultString.append("]");
        } else if (o instanceof Long ||
                   o instanceof Double ||
                   o instanceof String) {
            resultString.append(o.getClass().getName() + ": " + o);
        } else {
            resultString.append(ReflectionToStringBuilder.toString(o, ToStringStyle.SHORT_PREFIX_STYLE));
        }
    }

Answer 5

Looks like IntelliJ 10 will complement the HSQL editor with an JPQL editor. (Assuming that branch 96.xxx is version 10)

• http://youtrack.jetbrains.net/issue/IDEA-55361
• http://youtrack.jetbrains.net/issue/IDEA-54622