Home Ask Login Register

Developers Planet

Your answer is one click away!

B.J. A.A. February 2016

How to map a custom table returned from a stored procedure

I'm using postgresql and hibernate, i have a stored procedure that returns a custom table, how i map the java bean to this custom table? i wrote the java class but i not sure if should use annotations.

my SP:

CREATE OR REPLACE FUNCTION reporte_gas_federal()
  RETURNS TABLE(tipologia text, "Número de clientes" bigint, "Saturación de mercado" text, "Distribución clientes nivel federal" text) AS

WITH cruce_tipo_gas
     AS (SELECT wise_ageb.id_ageb,
         FROM   wise_ageb
                JOIN wise_ageb_geom
                  ON wise_ageb.id_ageb = wise_ageb_geom.id_ageb
                JOIN tipologias_ageb
                  ON tipologias_ageb.id_ageb = wise_ageb.id_ageb
                JOIN tipologias
                  ON tipologias_ageb.clave = tipologias.clave),
     AS (SELECT DISTINCT nombre,
                         Count(nombre) AS conteo
         FROM   gasnatural_clientes
                INNER JOIN cruce_tipo_gas
                        ON St_intersects(cruce_tipo_gas.geom,
         GROUP  BY( nombre )
         ORDER  BY nombre ASC),
     AS (--obtiene cruce de las manzanas para pobtnener datos de pob
        SELECT id_manzana,
         FROM   wise_manzana
                JOIN cruce_tipo_gas using(id_ageb)),
     pob_tipologia --poblacio total x tipologia
     AS (SELECT Sum(Cast(pobtot AS INT))AS poblacion,
         FROM   wise_manzana_poblacion
                JOIN cruce_manzana using (id_manzana)
         GROUP  BY clave),

     conteo_total_pob --poblacion total en la region
     AS (SELECT Sum(poblacion) AS pobtot
         FROM   pob_tipologia),
     AS (SELECT Sum(conteo) AS total
         FROM   conteo_parcial)


Orest February 2016

You can use RowMapper if you have JdbcTemplate for example:

public CustomResult getCustomResult(){
    final String sql = "CALL reporte_gas_federal";
    final CustomResult customResult = (CustomResult) jdbcTemplate.queryForObject(sql, new CustomResultRowMapper());

    return customResult;

public class CustomResultRowMapper implements RowMapper {
    public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
        CustomResult customResult = new CustomResult();
        return customResult;

Otherwise you can use ResultRansformer for example:

CustomResult customResult = (CustomResult)session.createSQLQuery("CALL reporte_gas_federal")
    .setResultTransformer(new BasicTransformerAdapter() {
            private static final long   serialVersionUID    = 1L;

            public Object transformTuple(Object[] tuple, String[] aliases){
                final CustomResult customResult = new CustomResult();
                CustomResult customResult = new CustomResult();
                return customResult;

Or even .setResultTransformer(Transformers.aliasToBean(CustomResult.class)) instead of custom transformer.

Post Status

Asked in February 2016
Viewed 2,308 times
Voted 9
Answered 1 times


Leave an answer

Quote of the day: live life