Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Resultset Metadata from Spring JDBCTemplate Query methods

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...

like image 709
sanumala Avatar asked Mar 15 '11 15:03

sanumala


People also ask

How do you find the ResultSet from JdbcTemplate?

Usage. Step 1 − Create a JdbcTemplate object using a configured datasource. Step 2 − Use JdbcTemplate object methods to make database operations while parsing the resultset using ResultSetExtractor.

What is the difference between RowMapper and ResultSetExtractor?

A RowMapper is usually a simpler choice for ResultSet processing, mapping one result object per row instead of one result object for the entire ResultSet. ResultSetExtractor is suppose to extract the whole ResultSet (possibly multiple rows), while RowMapper is feeded with row at a time.

Which method is used to get objects from JdbcTemplate?

In Java's Spring framework, we can use jdbcTemplate. queryForObject() to get a single row record from the database, and convert the row into an object (i.e. POJO or your entity class) via the row mapper feature.


2 Answers

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
        }
    });
like image 139
axtavt Avatar answered Sep 30 '22 13:09

axtavt


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();
like image 36
andrewrjones Avatar answered Sep 30 '22 15:09

andrewrjones