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

Question

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;
    }

You can't because, as you have observed, the columns returned can change based on the SP code and so are not part of usual metadata - this has a reasonable explaination - https://stackoverflow.com/questions/7368864/retrieve-column-definition-for-stored-procedure-result-set. If you can, add code to the SP to populate an extra return column with this info. — John Williams, Jan 31 '23 at 18:18
That goes into the SQL side. I don't actually need the metadata, just the column names. When I execute the procedure on the DB, the column names are returned as a header row. I assumed this would be sent as part of the result set and could be retrieved using some sort of map or something. Is this not the case? Is it only returning the actual data? — rphello101, Jan 31 '23 at 18:59
Yes, getting the ResultSet is the challenge - I have added an answer — John Williams, Feb 01 '23 at 12:50

score 1 · Accepted Answer · answered Feb 01 '23 at 12:49

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

Thanks for the information. That is unfortunate - trying to use the sleek new tools and I guess we're going old-school JDBC connection. I was able to get a proof of concept to work with the sample code you provided. Thanks for the response. — rphello101, Feb 01 '23 at 16:11

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

1 Answers1