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: stackoverflow.com/q/12557957/398670 — Craig Ringer
– Craig Ringer, Commented Jun 2, 2013 at 14:46
I changed the function to return a dummy integer. Now it works! Thanks — krackmoe
– krackmoe, Commented Jun 2, 2013 at 15:05

Community · Accepted Answer · 2017-05-23 12:07:49Z

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, 2017 at 12:07

CommunityBot

11 silver badge

answered Jun 2, 2013 at 14:51

bpgergo

16.1k5 gold badges48 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Call a Postgres function via Hibernate

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related