SQL查询未通过JdbcTemplate运行的问题

Question

我遇到了一个奇怪的问题，使用Oracle Pivot语法的sql查询。我可以毫无问题地在SqlDeveloper中运行查询；然而，使用行映射器通过JdbcTemplate运行它会产生关于无效列名的奇怪错误。

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar ... nested exception is java.sql.SQLException: Invalid column name

SQL语句：

select * from ( 
    Select stat.SWRHXML_HXPS_CODE  
    FROM Swbhxml xml 
    LEFT JOIN Swrhxml stat ON stat.swrhxml_trans_id = xml.SWBHXML_TRANS_ID 
    WHERE stat.SWRHXML_ACTIVITY_DATE = (
        SELECT MAX(st.SWRHXML_ACTIVITY_DATE) 
        FROM swrhxml st 
        WHERE stat.SWRHXML_TRANS_ID = st.SWRHXML_TRANS_ID)
   ) pivot (count(SWRHXML_HXPS_CODE) 
       for SWRHXML_HXPS_CODE in 
        ('NEW','EXPORT_READY','PENDING_MATCH','MATCHED_ID','PROCESSED','REJECTED'));

行映射器：

public class TranscriptStatusCountRowMapper implements RowMapper {

    @Override
    public Object mapRow(ResultSet rs, int rowNum) throws SQLException {

        TranscriptStatusCounts tsc = new TranscriptStatusCounts();

        tsc.setNewCount(rs.getLong("NEW_RECORDS"));
        tsc.setExportReadyCount(rs.getLong("EXPORT_READY"));
        tsc.setPendingMatchCount(rs.getLong("PENDING_MATCH"));
        tsc.setMatchedIdCount(rs.getLong("MATCHED_ID"));
        tsc.setProcessedCount(rs.getLong("PROCESSED"));
        tsc.setRejectedCount(rs.getLong("REJECTED"));
        return tsc;
    }
}

DAO调用类：

@Repository("transcriptCountDao")
public class TranscriptCountDaoImpl extends BaseDaoImpl implements   TranscriptCountDao {

    private static final Logger logger =    Logger.getLogger(TranscriptCountDaoImpl.class);

    @Override
    public TranscriptStatusCounts findTranscriptStatusCount() {
        logger.debug("Getting counts of Transcripts status in system"); 
        String sql =  "...sql posted above..."
        TranscriptStatusCounts tsc = 
            (TranscriptStatusCounts) getJdbcTemplate().queryForObject(sql, new TranscriptStatusCountRowMapper());   
        return tsc;
    }
}

Answer 1

好吧…我想好了。。。

数据透视表的列并没有很好地映射到我的行映射器。因此，我将行映射器更改为以下内容，从而解决了问题：

TranscriptStatusCounts tsc = new TranscriptStatusCounts();
    //'NEW','EXPORT_READY','PENDING_MATCH','MATCHED_ID','PROCESSED','REJECTED'
    tsc.setNewCount(rs.getLong(1));
    tsc.setExportReadyCount(rs.getLong(2));
    tsc.setPendingMatchCount(rs.getLong(3));
    tsc.setMatchedIdCount(rs.getLong(4));
    tsc.setProcessedCount(rs.getLong(5));
    tsc.setRejectedCount(rs.getLong(6));
    return tsc;

我忘记了sql“无效的列名”错误也可以引用结果集中用于访问列的名称。由于 PIVOT 查询对它们进行排序，因此我可以仅使用列的编号来获取结果。