Get result via PostgreSQL procedure using Hibernate

Question

I am trying to get a value from a postgres database using a plpgsql function _{editor's note: OP had plsql} but I am not able to retrieve the data.

I get the following exception:

Exception in thread "main" java.lang.UnsupportedOperationException: org.hibernate.dialect.PostgreSQLDialect does not support resultsets via stored procedures

My hibernate configuration file:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
  <session-factory>
    <property name="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</property>
    <property name="hibernate.connection.driver_class">org.postgresql.Driver</property>
    <property name="hibernate.connection.url">jdbc:postgresql://192.168.1.100:54321/localDB</property>
    <!--    <property name="hibernate.connection.url">jdbc:postgresql://192.168.1.100:54321/scprj</property>-->
    <property name="hibernate.connection.username">postgres</property>
    <property name="hibernate.connection.password">dbserver</property>
    <property name="hibernate.show_sql">true</property>
    <mapping resource="hibernate.hbm.xml"/>
  </session-factory>
</hibernate-configuration>

My hibernate mapping file:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
    <class name="callFunctionPack.DateBean" table="userlogin">
        <id column="userid" name="userid">
            <generator class="increment"/>
        </id>
        <property column="username" name="username" type="string"/>
        <property column="password" name="password" type="string"/>
    </class>

    <sql-query name="getlogin" callable="true" comment="Call the getlogindata procedure">>
      <return class="callFunctionPack.DateBean">
        <return-property name="username" column="username"/>
        <return-property name="password" column="password"/>
      </return>
      { call getlogin(:userid) }
    </sql-query>
</hibernate-mapping>

My main class function to the procedure:

public class Call {

    public static void main(String... args) {
        //select();
        show();
    }
    public static void show() {
        Query nQuery = getSession().getNamedQuery("getlogin").setParameter("userid", 1);
        List results = nQuery.list();
        for (Iterator it = results.iterator(); it.hasNext();) {
            DateBean dateBean = (DateBean)it.next();
            System.out.println(dateBean.getUsername());
            System.out.println(dateBean.getPassword());
        }
    }
    public static Session getSession() {
        Session session = new Configuration().configure().buildSessionFactory().openSession();
        return session;
    }
}

My bean class:

public class DateBean {
    private String date;
    private int userid;
    private String username;
    private String password;

    public String getDate() {
        return date;
    }
    public void setDate(String date) {
        this.date = date;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public int getUserid() {
        return userid;
    }

    public void setUserid(int userid) {
        this.userid = userid;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

}

My plpgsql function:

CREATE OR REPLACE FUNCTION getlogindata(numeric)
  RETURNS refcursor AS
$BODY$
    DECLARE

        p_userid            ALIAS FOR $1;

        sql_stmt            VARCHAR;
        p_condition         VARCHAR;
        v_boolean           BOOLEAN := false;
        email_tracking_cursor       REFCURSOR;
    BEGIN
    sql_stmt := 'SELECT USERNAME, PASSWORD FROM USERLOGIN WHERE USERID =' || p_userid;

    raise notice '%',sql_stmt;
        OPEN email_tracking_cursor FOR EXECUTE sql_stmt;
        RETURN email_tracking_cursor;

     END; $BODY$
  LANGUAGE plpgsql VOLATILE;
ALTER FUNCTION getlogindata(numeric) OWNER TO postgres;

Erwin Brandstetter · Accepted Answer · 2011-12-30 15:51:27Z

2

Your plpgsql function does not return a "value", but a cursor. This function does what you seem to want:

CREATE OR REPLACE FUNCTION getlogindata(numeric, OUT username text, OUT password text)
  RETURNS record AS
$BODY$
    SELECT u.username, u.password FROM userlogin u WHERE u.userid = $1;
$BODY$
  LANGUAGE sql STABLE;

You could also use plain SQL for this simple query:

SELECT username, password FROM userlogin WHERE userid = $my_userid;

answered Dec 30, 2011 at 15:51

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

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

Comments

user330315 · Accepted Answer · 2011-12-30 15:52:19Z

1

I don't know Hibernate very well, but you can change the function to be defined as "RETURNS TABLE" and then you can do a select * from getlogindata(42);.

Maybe Hibernate can work with that solution.

answered Dec 30, 2011 at 15:52

user330315

1 Comment

Marcel Over a year ago

That worked for me. I had the problem that hibernate could not find any columns in the result set. So i changed it from select function(); to select * from function(). Thanks a lot for this hint :)

Collectives™ on Stack Overflow

Get result via PostgreSQL procedure using Hibernate

My hibernate configuration file:

My hibernate mapping file:

My main class function to the procedure:

My bean class:

My plpgsql function:

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

My hibernate configuration file:

My hibernate mapping file:

My main class function to the procedure:

My bean class:

My plpgsql function:

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related