调用存储结构时报如下错:
org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{ CALL PAGE_QUERY(?,?,?,?,?) }]; SQL state [S0022]; error code [0]; Column 'DeviceDN' not found.; nested exception is java.sql.SQLException: Column 'DeviceDN' not found.
存储结构如下:
DROP PROCEDURE IF EXISTS `PAGE_QUERY`;CREATE DEFINER = `root`@`localhost` PROCEDURE `PAGE_QUERY`(IN `p_sql` varchar(2000),IN `p_pageIndex` int,IN `p_pageSize` int,OUT `p_pageCount` int,OUT `p_totalCount` int )
BEGIN
DECLARE v_pageIndex int;
DECLARE v_pageSize int;
DECLARE v_startCurs int;
DECLARE v_endCurs int;
DECLARE v_sql varchar(2000);
set v_sql = concat('select count(*) into @otalCount from (',p_sql,') as ss');
select v_sql;
set @sqlcounts = v_sql;
prepare stmt from @sqlcounts;
execute stmt;
deallocate prepare stmt;
set p_totalCount=@otalCount;
set p_pageCount:=ceil(p_totalCount/p_pageSize);
set v_pageSize:=p_pageSize;
if v_pageSize < 0 then
set v_pageSize:=0;
end if;
set v_pageIndex:=p_pageIndex;
if v_pageIndex < 0 then
set v_pageIndex:=1;
end if;
if v_pageIndex>p_pageCount then
set v_pageIndex:=p_pageCount;
end if;
set v_startCurs:=(v_pageIndex-1)*v_pageSize+1;
set v_endCurs:=v_pageIndex*v_pageSize;
set v_sql:=concat(p_sql,' limit ', v_startCurs,',',v_endCurs);
#set v_sql:=concat(v_sql,') t where rownum<=',v_endCurs,')where num>=',v_startCurs);
set @qsql = v_sql;
prepare stmt1 from @qsql;
execute stmt1;
deallocate prepare stmt1;
select @qsql;
#Routine body goes here...END;
存储过程编译成功,我在java后台调用存储过程的代码如下:
(List<Record>)jdbcTemplate.execute("{ CALL PAGE_QUERY(?,?,?,?,?) }",
new RecordStatementCallback(sql, parseInt(rows), parseInt(page)));
RecordStatementCallback类如下:
class RecordStatementCallback implements CallableStatementCallback{
private int page = 1;
private int rows = 20;
private String sql;
RecordStatementCallback(String sql, int rows, int page){
this.sql = sql;
this.rows = rows;
this.page = page;
}
public List<Record> doInCallableStatement(CallableStatement cs)throws SQLException, DataAccessException {
cs.setString(1, sql);
cs.setInt(2, page);
cs.setInt(3, rows);
cs.registerOutParameter(4, INTEGER);
cs.registerOutParameter(5, INTEGER);
boolean bb=cs.execute();
List<Record> recordList=null;
while(bb){
try (ResultSet results = cs.getResultSet()) {
recordList = new ArrayList<>();
while (results != null &&results.next()) {
Record ro = new Record();
//注册机器号码
ro.setAgentDn(results.getString("DeviceDN"));
ro.setCalledNum(results.getString("OriDNIS"));
ro.setCallingNum(results.getString("OriANI"));
ro.setDuration(Integer.toString(Math.round(results.getBigDecimal("TimeLength").intValue())));
//客服人员分配号码
ro.setExtension(results.getString("UserField"));
ro.setFileName(results.getString("FileName"));
ro.setStartTime(results.getString("StartTime"));
ro.setTotlePageCount(((Integer) cs.getObject(4)));
ro.setTotleItemCount(((Integer) cs.getObject(5)));
recordList.add(ro);
}
}
}
return recordList;
}
}
表结构如图:
传入参数('SELECT * FROM base_recordfile',1,20) 页数为1,每页最多20条数据结果集应该每个字段都有,因为我传入的sql是select *,但是报错说找不到这个字段,求解
org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{ CALL PAGE_QUERY(?,?,?,?,?) }]; SQL state [S0022]; error code [0]; Column 'DeviceDN' not found.; nested exception is java.sql.SQLException: Column 'DeviceDN' not found.
存储结构如下:
DROP PROCEDURE IF EXISTS `PAGE_QUERY`;CREATE DEFINER = `root`@`localhost` PROCEDURE `PAGE_QUERY`(IN `p_sql` varchar(2000),IN `p_pageIndex` int,IN `p_pageSize` int,OUT `p_pageCount` int,OUT `p_totalCount` int )
BEGIN
DECLARE v_pageIndex int;
DECLARE v_pageSize int;
DECLARE v_startCurs int;
DECLARE v_endCurs int;
DECLARE v_sql varchar(2000);
set v_sql = concat('select count(*) into @otalCount from (',p_sql,') as ss');
select v_sql;
set @sqlcounts = v_sql;
prepare stmt from @sqlcounts;
execute stmt;
deallocate prepare stmt;
set p_totalCount=@otalCount;
set p_pageCount:=ceil(p_totalCount/p_pageSize);
set v_pageSize:=p_pageSize;
if v_pageSize < 0 then
set v_pageSize:=0;
end if;
set v_pageIndex:=p_pageIndex;
if v_pageIndex < 0 then
set v_pageIndex:=1;
end if;
if v_pageIndex>p_pageCount then
set v_pageIndex:=p_pageCount;
end if;
set v_startCurs:=(v_pageIndex-1)*v_pageSize+1;
set v_endCurs:=v_pageIndex*v_pageSize;
set v_sql:=concat(p_sql,' limit ', v_startCurs,',',v_endCurs);
#set v_sql:=concat(v_sql,') t where rownum<=',v_endCurs,')where num>=',v_startCurs);
set @qsql = v_sql;
prepare stmt1 from @qsql;
execute stmt1;
deallocate prepare stmt1;
select @qsql;
#Routine body goes here...END;
存储过程编译成功,我在java后台调用存储过程的代码如下:
(List<Record>)jdbcTemplate.execute("{ CALL PAGE_QUERY(?,?,?,?,?) }",
new RecordStatementCallback(sql, parseInt(rows), parseInt(page)));
RecordStatementCallback类如下:
class RecordStatementCallback implements CallableStatementCallback{
private int page = 1;
private int rows = 20;
private String sql;
RecordStatementCallback(String sql, int rows, int page){
this.sql = sql;
this.rows = rows;
this.page = page;
}
public List<Record> doInCallableStatement(CallableStatement cs)throws SQLException, DataAccessException {
cs.setString(1, sql);
cs.setInt(2, page);
cs.setInt(3, rows);
cs.registerOutParameter(4, INTEGER);
cs.registerOutParameter(5, INTEGER);
boolean bb=cs.execute();
List<Record> recordList=null;
while(bb){
try (ResultSet results = cs.getResultSet()) {
recordList = new ArrayList<>();
while (results != null &&results.next()) {
Record ro = new Record();
//注册机器号码
ro.setAgentDn(results.getString("DeviceDN"));
ro.setCalledNum(results.getString("OriDNIS"));
ro.setCallingNum(results.getString("OriANI"));
ro.setDuration(Integer.toString(Math.round(results.getBigDecimal("TimeLength").intValue())));
//客服人员分配号码
ro.setExtension(results.getString("UserField"));
ro.setFileName(results.getString("FileName"));
ro.setStartTime(results.getString("StartTime"));
ro.setTotlePageCount(((Integer) cs.getObject(4)));
ro.setTotleItemCount(((Integer) cs.getObject(5)));
recordList.add(ro);
}
}
}
return recordList;
}
}
表结构如图:
传入参数('SELECT * FROM base_recordfile',1,20) 页数为1,每页最多20条数据结果集应该每个字段都有,因为我传入的sql是select *,但是报错说找不到这个字段,求解
java.lang.RuntimeException: java.sql.SQLException: Operation not allowed after ResultSet closed