Geek asks and answers

Spring Result Metadata JDBCTemplate Query Methods

Is there a way to get the result object from one of the 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 a result set. A query is a complex join in 6-7 tables, and I select 4-5 columns from each table and would like to get metadata for these columns to convert data types and data into downstream systems.

If this is a simple query and I only get one table, I can use RowMapper # mapRow and inside this maprow method I can call ResultsetExtractor.extractData to get a list of results; but in this case, I have complex joins in my query, and I'm trying to get the resultset object from this result metadata too ...

The above code is not good, because for each result, it will return the same resultet object, and I don't want to store them in a list ...

Once again, if maprow is called for each result from my query, will JDBCTemplate close rs and the connection, although there is a reference to the RS object in my list?

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

CustomResultSetProcessor ResultSetExtractor extractData. 3 . 1: get ColumnTypes form rs.getMetaData(), - getColumnTypes ,

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

3- (insert) - insert (ready) , , ,

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

, ...

+3
3

, Spring JDBC - , , ResultSet, . Spring ResultSet .

List, ResultSetExtractor RowMapper:

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

- :

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

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

Although I agree with C # axtavt that a ResultSetExtractor is preferable in a Spring environment, it forces you to execute the request.

The code below does not require you to do this, so that the client code does not require the provision of actual arguments for the request 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);
    }
}
0
source

More articles:


All Articles