数据库:oracle9i
应用服务器:tomcat6
查询调用 jdbcTemplate().queryForList(
StringBuffer sb = new StringBuffer();
sb.append(" SELECT PLU_CODE,PLU_VALUE,MEASURE_TIME,EQP_ID FROM E_DATA_VALUE ");
sb.append(" WHERE EQP_ID=? AND MEASURE_TIME BETWEEN ? AND ? ");*/
System.out.println(System.currentTimeMillis());
List rsop = getJdbcTemplate().queryForList(sb.toString(),param);
System.out.println(System.currentTimeMillis());
速度非常慢,表中已经分区,数据大有5000万条,查询要30秒左右
DataSource ds = this.getDataSource();
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs =null;
try {
con = ds.getConnection();
{
pstmt = con.prepareStatement("SELECT PLU_CODE,PLU_VALUE,MEASURE_TIME,EQP_ID FROM E_DATA_VALUE WHERE EQP_ID=? AND MEASURE_TIME BETWEEN to_date(?,'yyyy-mm-dd hh24:mi:ss') AND to_date(?,'yyyy-mm-dd hh24:mi:ss') ");
pstmt.setInt(1, eqpId);
pstmt.setString(2,qd.getBeginTime());
pstmt.setString(3, qd.getEndTime());
rs = pstmt.executeQuery();
}
}
catch( Exception sqle ) {
ds = null;
con = null;
pstmt = null;
}finally{
ds = null;
con = null;
pstmt = null;
}这种方式,查询只要<1秒, 可是无法释放连接池,大概一天时间后,就会连接池耗尽
不知道有没有好的办法实现解决getJdbcTemplate().queryForList(sb.toString(),param);查询慢的问题,困扰!