In Spring Boot, how to I get the column names from a StoredProcedureQuery result?-CodePudding

I am working on creating a simple utility that allows our users to execute a pre-selected list of stored procedures that return a simple list result set as a JSON string. The result set varies based on the selected procedure. I am able to get the results easily enough (and pass back as JSON as required), but the results don't include the column names.

The most common answer I found online is to use ResultSetMetaData or NativeQuery, but I couldn't figure out how to extract the metadata or transform the query properly using a StoredProcedureQuery object. How do I get the column names from a StoredProcedureQuery result?

Here is my code:

@SuppressWarnings("unchecked")
    public String executeProcedure(String procedure, String jsonData) { 
        
        //Set up a call to the stored procedure
        StoredProcedureQuery query = entityManager.createStoredProcedureQuery(procedure);
                
        //Register and set the parameters
        query.registerStoredProcedureParameter(0, String.class, ParameterMode.IN);
        query.setParameter(0, jsonData);
        
        String jsonResults = "[{}]";
        
        try {
            //Execute the query and store the results
            query.execute();
            List list = query.getResultList();
            jsonResults = new Gson().toJson(list);          
        } finally {
            try {
                //Cleanup
                query.unwrap(ProcedureOutputs.class).release();
            } catch(Exception e) {
                e.printStackTrace();
            }
        }
        
        return jsonResults;
    }

CodePudding user response：

The challenge is to get a ResultSet. In order to list the column names you need a ResultSet to do the following to access metadata. (Column names are metadata)

  ResultSetMetaData resultSetMetaData = resultSet.getMetaData();

  System.out.println("Column name: " resultSetMetaData.getColumnName(1));
  System.out.println("Column type: " resultSetMetaData.getColumnTypeName(1));

You can't get ResultSet (or metadata) from javax.persistence.StoredProcedureQuery or from spring-jpa Support JPA 2.1 stored procedures returning result sets

You can with low-level JDBC as follows:

CallableStatement stmnt = conn.prepareCall("{call demoSp(?, ?)}");
stmnt.setString(1, "abcdefg");

ResultSet resultSet1 = stmnt.executeQuery();

resultSet1.getMetaData(); // etc