JPA Hibernate Call Postgres Function Void Return MappingException:
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.
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.