Call a Postgres function via Hibernate

Question

I wrote a PostgreSQL function with the return type of void:

CREATE OR REPLACE FUNCTION queryinteriorexteriorcount()
  RETURNS void AS .....

The function works as expected when I call it from pgAdmin. However, I doesn't seems to work when called from Hibernate. Instead, I just keep getting the following exception thrown at me:

failed.org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111

I tried to create a custom dialect and registered my function like so:

public class PostgisDialect extends PostgisDialect {

    private static final long serialVersionUID = 3397563913838305367L;

    public PostgresDialect(){
        registerFunction("queryinteriorexteriorcount", new StandardSQLFunction("queryinteriorexteriorcount"));
    }
}

Then i changed the dialect in my hibernate.cfg.xml:

<property name="dialect">at.opendata.hibernate.PostgresDialect</property>

and tried to call the function (again) in the following way:

Query query = session.createSQLQuery("SELECT queryinteriorexteriorcount()");
query.uniqueResult();

Can you please tell me how I could properly call this function? I don't expect any return values, I just want to call it - the function would take care of everything else.

Hibernate is trying to decode `void` into something it can understand and failing. Give it a dummy return value? Or see if you can get Hibernate to discard the results - does it have a query operation to execute the function and ignore the result, like `executeUpdate` or similar? See also: http://stackoverflow.com/q/12557957/398670 — Craig Ringer, Jun 02 '13 at 14:46
I changed the function to return a dummy integer. Now it works! Thanks — krackmoe, Jun 02 '13 at 15:05

score 2 · Answer 1 · edited May 23 '17 at 12:07

2

If you want to fall back to JDBC, here's how to handle stored procedures and get Connection from Hibernate session.

session.doWork(new Work() {
    @Override
    public void execute(Connection connection) throws SQLException {
        connection.prepareCall("{call queryinteriorexteriorcount()}").executeQuery();
    }
});

edited May 23 '17 at 12:07

Community

1
1

answered Jun 02 '13 at 14:51

bpgergo

15,669
5
44
68

Call a Postgres function via Hibernate

1 Answers1