Resultset Metadata from Spring JDBCTemplate Query methods

Question

Is there any way I can get resultset object from one of jdbctemplate query methods?

I have a code like

List<ResultSet> rsList = template.query(finalQuery, new RowMapper<ResultSet>() {
        public ResultSet mapRow(ResultSet rs, int rowNum) throws SQLException {
            return rs;
        }
        }
        );

I wanted to execute my sql statement stored in finalQuery String and get the resultset. The query is a complex join on 6 to 7 tables and I am select 4-5 columns from each table and wanted to get the metadata of those columns to transform data types and data to downstream systems.

If it is a simple query and I am fetching form only one table I can use RowMapper#mapRow and inside that maprow method i can call ResultsetExtractor.extractData to get list of results; but in this case I have complex joins in my query and I am trying to get resultset Object and from that resultset metadata...

The above code is not good because for each result it will return same resultset object and I dont want to store them in list ...

Once more thing is if maprow is called for each result from my query will JDBCTemplate close the rs and connection even though my list has reference to RS object?

Is there any simple method like jdbcTemplate.queryForResultSet(sql) ?

Now I have implemented my own ResultSet Extractor to process and insert data into downstream systems

sourceJdbcTemplate.query(finalQuery, new CustomResultSetProcessor(targetTable, targetJdbcTemplate));

This CustomResultSetProcessor implements ResultSetExtractor and in extractData method I am calling 3 different methods 1 is get ColumnTypes form rs.getMetaData() and second is getColumnTypes of target metadata by running

SELECT NAME, COLTYPE, TBNAME FROM SYSIBM.SYSCOLUMNS WHERE TBNAME ='TABLENAME' AND TABCREATOR='TABLE CREATOR'

and in 3rd method I am building the insert statement (prepared) form target columntypes and finally calling that using

new BatchPreparedStatementSetter()
    {
        @Override
        public void setValues(PreparedStatement insertStmt, int i) throws SQLException{} }

Hope this helps to others...

score 7 · Answer 1 · answered Mar 15 '11 at 15:46

7

Note that the whole point of Spring JDBC Template is that it automatically closes all resources, including ResultSet, after execution of callback method. Therefore it would be better to extract necessary data inside a callback method and allow Spring to close the ResultSet after it.

If result of data extraction is not a List, you can use ResultSetExtractor instead of RowMapper:

SomeComplexResult r = template.query(finalQuery, 
    new ResultSetExtractor<SomeComplexResult>() {
        public SomeResult extractData(ResultSet) {
            // do complex processing of ResultSet and return its result as SomeComplexResult
        }
    });

answered Mar 15 '11 at 15:46

axtavt

239,438
41
511
482

Here in my case finalQuery is dynamic everytime and there are 26 such complex queries i need to execute. So I cant map all queries results into SomeComplexResult to return it back. I can map and convert data and datatypes in extractData but once I extracted I need to insert that in some other destination database in downstream systems. I guess above solution will not work for me. – sanumala Mar 15 '11 at 16:56
If I do like bellow is it a good idea `code` try{ con = jdbcTempalte.getDataSource().getConnection(); con.createStatement(); rs = st.executeQuery(finalQuery); rsmd = rs.getMetadata(); `code` I dont know if i get connection form template and get what ever I want and close resources, not sure whether it is a good idea or not... please help – sanumala Mar 15 '11 at 17:00
@sanumala: The point is that you don't need to return anything - you can implement your logic that need to access `ResultSet` inside a callback method. – axtavt Mar 15 '11 at 17:01
@axtavt Is it possible to return a `Map>`. my [Question here](http://stackoverflow.com/questions/25405095/using-a-query-inside-rowmapper) – Santhosh Aug 21 '14 at 09:06
What if I want to procces all entries from my table but my table cantains 20+ millions rows. How to do at this case? I use spring JdbcTempate – gstackoverflow Mar 22 '19 at 22:00

score 4 · Answer 2 · answered Sep 28 '12 at 15:51

4

Something like this would also work:

Connection con = DataSourceUtils.getConnection(dataSource); // your datasource
Statement s = con.createStatement();

ResultSet rs = s.executeQuery(query); // your query
ResultSetMetaData rsmd = rs.getMetaData();

answered Sep 28 '12 at 15:51

andrewrjones

1,801
21
25

Alexander · Answer 3 · 2016-07-06T06:44:49.650

Although I agree with #axtavt that ResultSetExtractor is preferred in Spring environment, it does force you to execute the query.

The code below does not require you to do so, so that the client code is not required to provide the actual arguments for the query parameters:

public SomeResult getMetadata(String querySql) throws SQLException {
    Assert.hasText(querySql);

    DataSource ds = jdbcTemplate.getDataSource();
    Connection con = null;
    PreparedStatement ps = null;
    try {
        con = DataSourceUtils.getConnection(ds);
        ps = con.prepareStatement(querySql);
        ResultSetMetaData md = ps.getMetaData();   //<-- the query is compiled, but not executed
        return processMetadata(md);
    } finally {
        JdbcUtils.closeStatement(ps);
        DataSourceUtils.releaseConnection(con, ds);
    }
}

score 0 · Answer 4 · answered Mar 02 '23 at 10:02

If you just want the ResultSetMetaData, the easiest way is to use a PreparedStatementCallback:

ResultSetMetaData md = jdbcTemplate.execute(sqlString,
    (PreparedStatementCallback<ResultSetMetaData>) ps -> {
        return ps.getMetaData();
    });

However, whether the validity of the ResultSetMetaData extends past the end of the execute call is tremendously variable between SQL connectors; it's advised to extract the information you want and return that instead. For example, to get the list of column labels:

List<String> labels = jdbcTemplate.execute(sqlString,
    (PreparedStatementCallback<List<String>>) ps -> {
        ResultSetMetaData md = ps.getMetaData();
        List<String> list = new ArrayList<>();
        for (int i = 1; i <= md.getColumnCount(); i++) {
            list.add(md.getColumnLabel(i));
        }
        return list;
    });

Resultset Metadata from Spring JDBCTemplate Query methods

4 Answers4