JPA Hibernate Call Postgres Function Void Return MappingException:

Question

I have a problem where I am getting an: org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111 when trying to call a postgres function using JPA create native query.

I created an EJB timer in a startup singleton to run a Postgres function every 6 hours. The function returns void and checks for expired records, deletes them, and updates some statuses. It takes no arguments and it returns void.

• The postgres function runs perfectly if I call it using PgAdmin query tool (select function();) and returns void.

• When I deploy the app on Glassfish 3.1.1 I get an exception and a failure to deploy.

This is the (shortened) stack trace:

WARNING: A system exception occurred during an invocation on EJB UserQueryBean method public void com.mysoftwareco.entity.utility.UserQueryBean.runRequestCleanup()
javax.ejb.TransactionRolledbackLocalException: Exception thrown from bean
...STACK TRACE BLAH BLAH BLAH ...
Caused by: javax.persistence.PersistenceException: org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111

Here is the code:

First the JPA stuff:

public void runRequestCleanup() {
    String queryString = "SELECT a_function_that_hibernate_chokes_on()";
    Query query = em.createNativeQuery(queryString);
    Object result = query.getSingleResult();
}

This is the singleton calling it:

@Startup
@Singleton
public class RequestCleanupTimer {
    @Resource
    TimerService timerService;
    @EJB
    UserQueryBean queryBean;

    @PostConstruct
    @Schedule(hour = "*/6")
    void runCleanupTimer() {
        queryBean.runRequestCleanup();
    }
}

And the function:

CREATE OR REPLACE FUNCTION a_function_that_hibernate_chokes_on()
  RETURNS void AS
$BODY$
    DECLARE 
        var_field_id myTable.field_id%TYPE;
    BEGIN
        FOR var_field_id IN
                select field_id from myTable 
                where status = 'some status'
                and disposition = 'some disposition'
                and valid_through < now()
        LOOP
            BEGIN
                -- Do Stuff
            END;
        END LOOP;
    END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Answer 1

This could be a hack, but it worked for me and is pretty simple. Just change the query to:

SELECT count(*) FROM your_function();

Now it returns a proper integer and hibernate is happy.

Answer 2

I had enough messing around with JPA trying to get it to run a stored procedure.

I ended up using JDBC with a prepared statement. I did it in 15 minutes after spending several fruitless hours trying to fit a square peg into a round hole. I called the same jndi datasource my persistence unit uses to get a connection, created a prepared statement and closed it when done.

So if you need to run a stored procedure (or Postgres function) from a (now mostly) JPA app, here is what worked for me:

@Stateless
@LocalBean
public class UserQueryBean implements Serializable {

    @Resource(mappedName="jdbc/DatabasePool") 
    private javax.sql.DataSource ds;

    ...

    public void runRequestCleanup() {

        String queryString = "SELECT cleanup_function_that_hibernateJPA_choked_on()";
        Connection conn = null;
        PreparedStatement statement = null;
        try {
            conn = ds.getConnection();
            statement = conn.prepareCall(queryString);
            statement.executeQuery();
        } catch (SQLException ex) {
            Logger.getLogger(UserQueryBean.class.getName()).log(Level.SEVERE, null, ex);
        }finally{
            try {
                statement.close();
                conn.close();
            } catch (SQLException ex) {
                Logger.getLogger(UserQueryBean.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        // bit of logging code here    
    }
    ...
}

There seems to be a horrible oversight to leave out the simple ability to run a function or stored procedure on the server from JPA; especially one that doesn't return anything except void or the number of rows affected. And if it was deliberate ... no comment.

Edit: added close connection.

Answer 3

For future visitors of this issue, a cast would have worked too. posted on this thread as well

public void runRequestCleanup() {
    String queryString = "SELECT cast(a_function_that_hibernate_chokes_on() as text)";
    Query query = em.createNativeQuery(queryString);
    query.getSingleResult();
}