EclipseLink JPA Native Constructor Queries

I saw a posting over on JavaRanch from someone wanting to return JavaBean results from a Native SQL query in JPA. Turns out this is pretty easily done with EclipseLink and I just happen to have already written a utility class for just this purpose so I thought I'd post it.

You can configure a native SQL query to return POJOs in a single line of code using the JavaBeanResult utility class:

   JavaBeanResult.setQueryResultClass(query, SomeBeanClass.class); 

This will install an EclipseLink query redirector which will intercept the SQL query results and build JavaBean objects from them.

The JavaBeanResult utility class will do the job but it relies on the developer to align the field selected in their SQL query with the constructor arguments for their bean. For example, for the code below to work, SupplierBean must have a constructor with three arguments with types compatible with the SQL result types for columns id, name, and version. JavaBeanResult uses the EclipseLink ConversionManager to convert each SQL result value to the declared type of the corresponding constructor argument. If it can't you'll get an exception.

       Query query = getEntityManager()  
          .createNativeQuery("SELECT ID, NAME, VER FROM SUPPLIER");  
       JavaBeanResult.setQueryResultClass(query, SupplierBean.class);  
       List<SupplierBean> results = query.getResultList();  

    public SupplierBean(int id, String name, Long version) {  

--Shaun


Complete source for JavaBeanResult.java:

 /*******************************************************************************  
  * Copyright (c) 2010 Oracle. All rights reserved.  
  * This program and the accompanying materials are made available under the   
  * terms of the Eclipse Public License v1.0 and Eclipse Distribution License v. 1.0   
  * which accompanies this distribution.   
  * The Eclipse Public License is available at http://www.eclipse.org/legal/epl-v10.html  
  * and the Eclipse Distribution License is available at   
  * http://www.eclipse.org/org/documents/edl-v10.php.  
  *   
  * @author shsmith  
  ******************************************************************************/  
 package org.eclipse.persistence.example.jpa.nativesql.constructor;  
 import java.lang.reflect.Constructor;  
 import java.lang.reflect.InvocationTargetException;  
 import java.util.ArrayList;  
 import java.util.List;  
 import javax.persistence.Query;  
 import org.eclipse.persistence.exceptions.ConversionException;  
 import org.eclipse.persistence.internal.helper.ConversionManager;  
 import org.eclipse.persistence.internal.sessions.AbstractRecord;  
 import org.eclipse.persistence.internal.sessions.AbstractSession;  
 import org.eclipse.persistence.jpa.JpaHelper;  
 import org.eclipse.persistence.queries.DatabaseQuery;  
 import org.eclipse.persistence.queries.QueryRedirector;  
 import org.eclipse.persistence.sessions.Record;  
 import org.eclipse.persistence.sessions.Session;  
 /***  
  * This class is a simple query redirector that intercepts the result of a  
  * native query and builds an instance of the specified JavaBean class from each  
  * result row. The order of the selected columns musts match the JavaBean class  
  * constructor arguments order.  
  *   
  * To configure a JavaBeanResult on a native SQL query use:  
  *    JavaBeanResult.setQueryResultClass(query, SomeBeanClass.class); 
  * where query is either a JPA SQL Query or native EclipseLink DatabaseQuery.  
  *   
  * @author shsmith  
  *   
  */  
 public final class JavaBeanResult implements QueryRedirector {  
    private static final long serialVersionUID = 3025874987115503731L;  
    protected Class<?> resultClass;  
    public static void setQueryResultClass(Query query, Class<?> resultClass) {  
       JavaBeanResult javaBeanResult = new JavaBeanResult(resultClass);  
       DatabaseQuery databaseQuery = JpaHelper.getDatabaseQuery(query);  
       databaseQuery.setRedirector(javaBeanResult);  
    }  
    public static void setQueryResultClass(DatabaseQuery query,  
          Class<?> resultClass) {  
       JavaBeanResult javaBeanResult = new JavaBeanResult(resultClass);  
       query.setRedirector(javaBeanResult);  
    }  
    protected JavaBeanResult(Class<?> resultClass) {  
       this.resultClass = resultClass;  
    }  
    @SuppressWarnings("unchecked")  
    public Object invokeQuery(DatabaseQuery query, Record arguments,  
          Session session) {  
       List<Object> results = new ArrayList<Object>();  
       try {  
          Constructor<?> javaBeanClassConstructor =   
             (Constructor<?>) resultClass.getDeclaredConstructors()[0];  
          Class<?>[] constructorParameterTypes =   
             javaBeanClassConstructor.getParameterTypes();  
          List<Object[]> rows = (List<Object[]>) query.execute(  
                (AbstractSession) session, (AbstractRecord) arguments);  
          for (Object[] columns : rows) {  
             Object[] constructorArgs = new Object[constructorParameterTypes.length];  
             if (columns.length != constructorParameterTypes.length) {  
                throw new ColumnParameterNumberMismatchException(  
                      resultClass);  
             }  
             for (int j = 0; j < columns.length; j++) {  
                Object columnValue = columns[j];  
                Class<?> parameterType = constructorParameterTypes[j];  
                // convert the column value to the correct type--if possible  
                constructorArgs[j] = ConversionManager.getDefaultManager()  
                      .convertObject(columnValue, parameterType);  
             }  
             results.add(javaBeanClassConstructor.newInstance(constructorArgs));  
          }  
       } catch (ConversionException e) {  
          throw new ColumnParameterMismatchException(e);  
       } catch (IllegalArgumentException e) {  
          throw new ColumnParameterMismatchException(e);  
       } catch (InstantiationException e) {  
          throw new ColumnParameterMismatchException(e);  
       } catch (IllegalAccessException e) {  
          throw new ColumnParameterMismatchException(e);  
       } catch (InvocationTargetException e) {  
          throw new ColumnParameterMismatchException(e);  
       }  
       return results;  
    }  
    public final class ColumnParameterMismatchException extends  
          RuntimeException {  
       private static final long serialVersionUID = 4752000720859502868L;  
       public ColumnParameterMismatchException(Throwable t) {  
          super(  
             "Exception while processing query results-ensure column order matches constructor parameter order",  
             t);  
       }  
    }  
    public final class ColumnParameterNumberMismatchException extends  
          RuntimeException {  
       private static final long serialVersionUID = 1776794744797667755L;  
       public ColumnParameterNumberMismatchException(Class<?> clazz) {  
          super(  
             "Number of selected columns does not match number of constructor arguments for: "  
             + clazz.getName());  
       }  
    }  
 }  

CheckCacheThenDatabase in TopLink Grid

EclipseLink JPA has a number of useful query hints that allow you to query the cache rather than, or before, the database. This is useful because if you know you've warmed up your cache you can execute queries for objects and yet not have to query the database.

As of TopLink 11gR1 (11.1.1.2.0) TopLink Grid doesn't support these hints but it's only a problem for the 'Grid Cache' configuration in which Coherence is used as a shared cache replacement. In Grid Cache, only primary key queries are sent to Coherence and all other queries go to the database. In the 'Grid Read' and 'Grid Entity' configurations all read queries are directed to Coherence.

I was recently asked how to get a query to CheckCacheThenDatabase with Grid Cache and came up with the GridCacheQueryHelper utility class below. It provides the static method checkCacheThenDatabase which will adjust an EclipseLink JPA query so that it queries Coherence (if the query can be translated to a Filter) and if no results are found in Coherence then it queries the database.

Configuring a query would look something like:

        Query query = em.createQuery("select e from Employee e");
        GridCacheQueryHelper.checkCacheThenDatabase(query);
        List<Employee> employees = query.getResultList();


NOTE: this code is based on how TopLink Grid 11.1.1.2.0 is implemented and may not work with future releases.

--Shaun

/**
 * Utility class that provides implementations of EclipseLink 
 * query hints for use with TopLink Grid 'Grid Cache' Configuration.
 * 
 * @author shsmith
 *
 */
class GridCacheQueryHelper {
    
    public static void checkCacheThenDatabase(Query jpqlQuery) {
        JpaHelper
            .getDatabaseQuery(jpqlQuery)
            .setRedirector(new CoherenceCheckCacheThenDatabase());
    }
    
    private static class CoherenceCheckCacheThenDatabase implements QueryRedirector {
        
        public Object invokeQuery(DatabaseQuery query, Record arguments,
                Session session) {
            // Create appropriate Coherence Query Redirector
            QueryRedirector redirector = null;
            if (query.isReadObjectQuery()) {
                redirector = new ReadObjectFromCoherence();
            } else if (query.isReadAllQuery()) {
                redirector = new ReadAllFromCoherence();                
            } else { 
                throw new RuntimeException("CheckCacheThenDatabase only supported on ReadObject and ReadAll Queries.");
            }
            // If nothing returned from Coherence then query database.
            // But only if the query was only run against Coherence 
            // and no results were found.
            List results = (List)redirector.invokeQuery(query, arguments, session);
            if ((results.isEmpty()) && (query.getDoNotRedirect())) {
                return query.execute((AbstractSession)session, (AbstractRecord) arguments);
            } else {
                return results;
            }
        }
    }

}

Running a SQL Script on startup in EclipseLink

Sometimes, when working with DDL generation it's useful to run a script to clean up the database first. In Hibernate if you put a file called "import.sql" on your classpath its contents will be sent to the database. Personally I'm not a fan of magic filenames but this can be a useful feature.

There's no built in support for this in EclipseLink but it's easy to do thank's to EclipseLink's high extensibility. Here's a quick solution I came up with: I simply register an event listener for the session postLogin event and in the handler I read a file and send each SQL statement to the database--nice and clean. I went a little further and supported setting the name of the file as a persistence unit property. You can specify this all in code or in the persistence.xml.

The ImportSQL class is configured as a SessionCustomizer through a persistence unit property which, on the postLogin event, reads the file identified by the "import.sql.file" property. This property is also specified as a persistence unit property which is passed to createEntityManagerFactory. This example also shows how you can define and use your own persistence unit properties.

import org.eclipse.persistence.config.SessionCustomizer;
import org.eclipse.persistence.sessions.Session;
import org.eclipse.persistence.sessions.SessionEvent;
import org.eclipse.persistence.sessions.SessionEventAdapter;
import org.eclipse.persistence.sessions.UnitOfWork;

public class ImportSQL implements SessionCustomizer {
    
    private void importSql(UnitOfWork unitOfWork, String fileName) {
        // Open file
        // Execute each line, e.g.,
        // unitOfWork.executeNonSelectingSQL("select 1 from dual");
    }

    @Override
    public void customize(Session session) throws Exception {
        session.getEventManager().addListener(new SessionEventAdapter() {
            @Override
            public void postLogin(SessionEvent event) {
                String fileName = (String) event.getSession().getProperty("import.sql.file");
                UnitOfWork unitOfWork = event.getSession().acquireUnitOfWork();
                importSql(unitOfWork, fileName);
                unitOfWork.commit() 
            }



        });
    }

---

    public static void main(String[] args) {
        Map<String, Object> properties = new HashMap<String, Object>();
        
        // Enable DDL Generation
        properties.put(PersistenceUnitProperties.DDL_GENERATION, PersistenceUnitProperties.DROP_AND_CREATE);
        properties.put(PersistenceUnitProperties.DDL_GENERATION_MODE, PersistenceUnitProperties.DDL_DATABASE_GENERATION);
        // Configure Session Customizer which will pipe sql file to db before DDL Generation runs
        properties.put(PersistenceUnitProperties.SESSION_CUSTOMIZER, "model.ImportSQL");
        properties.put("import.sql.file","/tmp/someddl.sql");
        
        EntityManagerFactory emf = Persistence
                .createEntityManagerFactory("employee", properties);

EclipseLink @ OSGi DevCon & UKOUG Coherence London SIG

Next week is a busy one for me in London with Gemini and EclipseLink related talks at both the OSGi DevCon (held in conjunction with JAX London) and the UKOUG Coherence London SIG.

At OSGi DevCon I'll be presenting Introducing the OSGi JPA Specification with Mike Kieth, the Gemini project lead, on the new specification and its reference implementation based on EclipseLink. This spec is part of the OSGi Enterprise Specification which extends the core OSGi platform to address enterprise scenarios.

At the UKOUG Coherence London SIG I'll be talking about TopLink Grid which integrates EclipseLink JPA with Oracle Coherence to support scaling JPA applications. If you've ever wondered how do I take my Java EE/JPA application and scale it to a cluster of hundreds of servers, this is the talk for you! As of now the session is fully booked but you can still get on the waiting list. ;-)