Java Retrieve metadata from StoredProcedure with StoredProcedureQuery

146 views Asked by At

Before Hibernate 6 - JDK 17 when I've called a stored procedure, I've used CallableStatement, now I'm using StoredProcedureQuery object.

For S.P. with output paramter this strategy is OK, but I've a Stored Procedure (result of pivot operations inside), where I need to kjnow the metadata information to know the name of columns.

With the old strategy I wrote this code:

    Connection conn = ((SessionImpl)session).connection();
    CallableStatement callableStatement = null;
    List<EmbedDipQualificatoExport> lst = new ArrayList<EmbedDipQualificatoExport>();
    String idReparto = (String)hmParam.get("idReparto");
    
    try {
        callableStatement = conn.prepareCall("call my_sp(?)");
        callableStatement.setString(1, idReparto);
        
        callableStatement.execute();
    }
    catch(SQLException ex) {
        logger.error(ex.getMessage());
    }
    finally {
        if (callableStatement != null) {
            try {
                resultSet = callableStatement.getResultSet();
                if (resultSet != null) {
                    lst = new ArrayList<EmbedDipQualificatoExport>();
                    while (resultSet.next()) {
                        EmbedDipQualificatoExport curr = new EmbedDipQualificatoExport();
                        String codice = resultSet.getString(1);
                        String nomeMPPT = resultSet.getString(2);
                        String nomeFase = resultSet.getString(3);
                        curr.setCodice(codice);
                        curr.setNomeMPPT(nomeMPPT);
                        curr.setNomeFase(nomeFase);
                        List<InfoDipendenteQualificatoExport> lstDip = new ArrayList<InfoDipendenteQualificatoExport>();
                        for (int i = 4; i <= resultSet.getMetaData().getColumnCount(); i ++) {
                            InfoDipendenteQualificatoExport currDip = new InfoDipendenteQualificatoExport();
                            currDip.setInfoDipendente(resultSet.getMetaData().getColumnName(i));
                            Integer valore = resultSet.getInt(i);
                            currDip.setQualificato(valore.equals(0) ? null : "X");
                            lstDip.add(currDip);
                        }
                        curr.setLstDipendentiQualificati(lstDip);
                        
                        lst.add(curr);
                    }
                }
                callableStatement.close();
                if (resultSet != null) {
                    resultSet.close();
                }
            }
            catch(SQLException ex) {
                logger.error(ex.getMessage());
            }
        }
    }

So, in the finally branch I've got the metadata columns to build my output collection.

But with StoredProcedureQuery how can I do the same result? Because as property of query exists only the getResultList() property with data but no with metadata.

0

There are 0 answers