JPA Hibernate Call Postgres Function Void Return M

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

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();
}

Answer 2

This was posted some time ago, but I had the similar issue. As stated, Hibernate looks allergic to void and will try to lock you into using the return type every time. From my point of view, this is a good practice as you should normally always have a return at least to specify if it succeeded: throwing exception is often abusive.

Yet, Hibernate DOES offer a way to bypass it's limitations: org.hibernate.jdbc.Work

You can easily reproduce what was required in a small class:

class VoidProcedureWork implements Work {

    String query;

    private VoidProcedureWork(String sql) {
        query = sql;
    }

    public static boolean execute(Session session, String sql) {
        try {
            // We assume that we will succeed or receive an exception.
            session.doWork(new VoidProcedureWork(sql));
            return true;
        } catch (Throwable e) {
            // log exception
            return false;
        }
    }

    /** 
     * @see org.hibernate.jdbc.Work#execute(java.sql.Connection)
     */
    @Override
    public void execute(Connection connection) throws SQLException {
        Statement statement = null;
        try {
            statement = connection.createStatement();
            statement.execute(query);
        } finally {
            if (statement != null)
                statement.close();
        }
    }
}

Now you can call it whenever you want by using

VoidProcedureWork.execute(hibernateSession, sqlQuery);

You will notice two things about this class: 1) I do NOT close the Connection. I leave it that way because I do not know who opened the Connection, how and why. Is the same connection used in a transaction? If I close it ant someone uses it after, will it crash? Etc. I did not code Hibernate and did not use connection.open(). Therefore, I do not close it and assume whatever opened it will close it too. 2) It is more procedural programming than OOP. I know, but I am lazy: it is easier (and clearer imo) to use VoidProcedureWork.execute(session, sql) than new VoidProcedureWork(sql).execute(session). Or even worst: reproduce the code of execute everytime I want to use that little trick (session.doWork(new VoidProcedureWork(sql)) with exception processing).

Answer 3

It seems that the problem occurs when the postgres stored procedure returns void. Try to change the return type to return some dummy value, perhaps a string. This worked in my case.

Answer 4

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 5

Dude! It's as easy as quoting the function name. Like so:

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

Answer 6

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.