1

I'm trying to execute a stored procedure with hibernate (Java Persistence API) and postgreSQL. My mapping file looks as follows:

<?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>
<sql-query name="getnotificaciones" callable="true">
<return alias="notificacion"
class="tempranillo.entidades.sistemanotificaciones.NotificacionDB" lock-mode="read">
<return-property name="fecha" column="fecha"/>
<return-property name="notificacion" column="notificacioncolumn"/>
<return-property name="xmlnotificacion" column="xmlcolumn"/>
</return>
{call getnotificaciones(:idusuario)}
</sql-query>
</hibernate-mapping>

The stored procedure is shown below:

CREATE OR REPLACE FUNCTION getNotificaciones(idusuario  int4,
OUT fecha               date,
OUT notificacioncolumn  varchar,
OUT xmlcolumn           xml
)
RETURNS SETOF record
AS $$
SELECT 
   INFONOT.fecha, 
   INFONOT.notificacion, 
xmlelement (name notificacion, xmlattributes(current_date as "fecha",
INFONOT.xmlTipoNotificacion as "tipoNotificacion"),
   xmlelement (name infovino,
     xmlforest(
                 tvinos.idvino,
                 tvinos.nombre,
                 tvinos.tipovino,
                 tvinos.anio,
                 tvinos.variedad,
                 tvinos.zona,
                 tvinos.pais)
                 ),
     xmlelement (name infousuario,
     xmlforest(
                 tusuarios.idusuario,
                 tusuarios.ALIAS)
                 ),
     xmlelement (name infologro,
     xmlforest(
               tlogros.idlogro,
               tlogros.nombrelogro,
               tlogros.descripcion
                 )
               )
     )
FROM
public.tusuarios
INNER JOIN 
(SELECT DISTINCT(xpath('//Notificacion/usuarioOrigina
/id/text()',xmlnotificacion::xml))[1]::varchar::int4 as xmlidusuario, 
             (xpath('//Notificacion/vino/idvino/text()',xmlnotificacion::xml
[1]::varchar::int4 as XMLIDVINO, 
             (xpath('//Notificacion/tipoNotificacion/text()',xmlnotificacion::xml
[1]::varchar as xmlTipoNotificacion,
             (xpath('//Notificacion/Logro/IdLogro/text()',xmlnotificacion::xml
[1]::varchar::int4 as xmlIdLogro,
             public.tnotificacion.idnotificacion,
             public.tnotificacion.fecha,
             public.tnotificacion.notificacion
FROM  
  public.tamistad  RIGHT OUTER JOIN public.tnotificacion ON
(public.tamistad.idamigo=     public.tnotificacion.idusuario) 
WHERE  (public.tamistad.idusuario = $1 OR   public.tnotificacion.idusuario = $1) AND
public.tnotificacion.xmlnotificacion IS NOT NULL)  AS INFONOT 
ON (public.tusuarios.idusuario = INFONOT.xmlidusuario)
LEFT OUTER JOIN public.tvinos 
on (public.tvinos.idvino = INFONOT.xmlidvino)
LEFT OUTER JOIN public.tlogros
on (public.tlogros.idlogro = INFONOT.xmlIdLogro) 
ORDER BY 
INFONOT.fecha DESC;          
$$ LANGUAGE SQL;

The entity declared in java as follows:

package tempranillo.entidades.sistemanotificaciones;

public class NotificacionDB implements Serializable {

    public NotificacionDB() {
    }

    public NotificacionDB(int idusuario,
                            Date fecha,
                            String notificacion) {
        this.idusuario = idusuario;
        this.fecha = fecha;
        this.notificacion = notificacion;
    }

     public NotificacionDB(int idusuario,
                            Date fecha,
                            String notificacion,
                            String xmlnotificacion) {
        this.idusuario = idusuario;
        this.fecha = fecha;
        this.notificacion = notificacion;
        this.xmlnotificacion = xmlnotificacion;
    }


    private int idnotificacion;

    public int getIdnotificacion() {
        return idnotificacion;
    }

    private void setIdnotificacion(int idnotificacion) {
        this.idnotificacion = idnotificacion;
    }
    private int idusuario;

    public int getIdusuario() {
        return idusuario;
    }

    public void setIdusuario(int idusuario) {
        this.idusuario = idusuario;
    }
    private Date fecha;

    public Date getFecha() {
        return fecha;
    }

    public void setFecha(Date fecha) {
        this.fecha = fecha;
    }
    private String notificacion;

    public String getNotificacion() {
        return notificacion;
    }

    public void setNotificacion(String notificacion) {
        this.notificacion = notificacion;
    }

    private String xmlnotificacion;

    public String getXmlnotificacion() {
        return xmlnotificacion;
    }

    public void setXmlnotificacion(String xmlnotificacion) {
        this.xmlnotificacion = xmlnotificacion;
    }

}

When I try to execute the following code:

   Query query = sesion.getNamedQuery("getnotificaciones");
   query.setInteger("idusuario", idusuario);
   List listanotificaciones = query.list();

I always get the same error: There is a problema with the postgreSQLDialect.

I tried with select * from getnotificaciones (:idusuario) but in this case I get "could not execute query" error message.

Can anyone help me?

6
  • Does it works outside of hibenrate? directly with an sql query tool? BTW, try with: </return> <![CDATA[call getNotificaciones(:idusuario)]]> </sql-query> Commented Aug 20, 2012 at 10:05
  • If I exectue the store procedure in PostgreSQL Maestro, returns data correctly. Commented Aug 20, 2012 at 10:15
  • 1
    have you tried with the CDATA instead of {}? What is the version you are using? Commented Aug 20, 2012 at 10:26
  • yes, return: java.lang.UnsupportedOperationException: org.hibernate.dialect.PostgreSQLDialect does not support resultsets via stored procedures at org.hibernate.dialect.Dialect.registerResultSetOutParameter(Dialect.java:1090) at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1560) at org.hibernate.loader.Loader.doQuery(Loader.java:673) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections Commented Aug 20, 2012 at 11:16
  • @JoseAdameFernandez Please show your PostgreSQL and Hibernate versions, and the version of PgJDBC you're using. Commented Aug 20, 2012 at 13:19

1 Answer 1

3

It seems like Java can not take your RETURNS SETOF recordand you could find some references for that part.

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.