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
$BODY$ 

WITH cruce_tipo_gas
     AS (SELECT wise_ageb.id_ageb,
                wise_ageb.cvgeo_ageb,
                wise_ageb.tipo,
                nombre,tipologias_ageb.clave,
                wise_ageb_geom.geom
         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),
     conteo_parcial
     AS (SELECT DISTINCT nombre,
                         Count(nombre) AS conteo
         FROM   gasnatural_clientes
                INNER JOIN cruce_tipo_gas
                        ON St_intersects(cruce_tipo_gas.geom,
                           gasnatural_clientes.geom)
         GROUP  BY( nombre )
         ORDER  BY nombre ASC),
   cruce_manzana
     AS (--obtiene cruce de las manzanas para pobtnener datos de pob
        SELECT id_manzana,
                 clave
         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,
                clave
         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),
     conteo_total
     AS (SELECT Sum(conteo) AS total
         FROM   conteo_parcial)
SE        

Answers


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();
        customResult.set(...
        ...
        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;

            @Override
            public Object transformTuple(Object[] tuple, String[] aliases){
                final CustomResult customResult = new CustomResult();
                CustomResult customResult = new CustomResult();
                customResult.set(...
                ...
                return customResult;
            }
    }).uniqueResult();

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

Search




Leave an answer