java.sql.SQLException: ORA-01000: 超出打开游标的最大数
源代码如下 private static List<StbInfoBo> queryStbDevInfo(long offset, long limit) {
ResultSet rs = null;
PreparedStatement pstmt = null;
String sql = " SELECT * FROM (SELECT STB.ID,"
+ " STB.PARENT_RESDEV_ID," + " STB.BRAS_ID_1," + " STB.PVLAN,"
+ " rownum rwn " + " FROM STB"
+ " WHERE (STB.DEL_FLAG IS NULL OR STB.DEL_FLAG = 'U')"
+ "and stb.bras_id_1 is not null" + " AND ROWNUM <= ? )"
+ " WHERE rwn > ?";
List<StbInfoBo> stbList = new ArrayList<StbInfoBo>();
try {
pstmt = conn.prepareStatement(sql);
pstmt.setLong(1, offset + limit);
pstmt.setLong(2, offset);
rs = pstmt.executeQuery();
while (rs.next()) {
StbInfoBo stb = new StbInfoBo();
stb.setId(rs.getLong(1));
stb.setParentId(rs.getLong(2));
stb.setBrasId1(rs.getLong(3));
stb.setPvlan(rs.getLong(4));
stbList.add(stb);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtil.close(null, pstmt, rs);
}
return stbList;
}JdbcUtil 的close访法:
[code=Java]
public static void close(Connection conn,
PreparedStatement pstmt, ResultSet rs) {
try {
if (conn != null) {
conn.close();
conn = null;
}
if (pstmt != null) {
pstmt.close();
pstmt = null;
}
if (rs != null) {
rs.close();
rs = null;
}
} catch (Exception e) {
e.printStackTrace();
}
}
[/code]
开始的时候在网上查了一下,说是PreparedStatement在执行完一次SQL之后没有关闭,但是我这个是关闭了的啊,怎么还报这个错,望指点
源代码如下 private static List<StbInfoBo> queryStbDevInfo(long offset, long limit) {
ResultSet rs = null;
PreparedStatement pstmt = null;
String sql = " SELECT * FROM (SELECT STB.ID,"
+ " STB.PARENT_RESDEV_ID," + " STB.BRAS_ID_1," + " STB.PVLAN,"
+ " rownum rwn " + " FROM STB"
+ " WHERE (STB.DEL_FLAG IS NULL OR STB.DEL_FLAG = 'U')"
+ "and stb.bras_id_1 is not null" + " AND ROWNUM <= ? )"
+ " WHERE rwn > ?";
List<StbInfoBo> stbList = new ArrayList<StbInfoBo>();
try {
pstmt = conn.prepareStatement(sql);
pstmt.setLong(1, offset + limit);
pstmt.setLong(2, offset);
rs = pstmt.executeQuery();
while (rs.next()) {
StbInfoBo stb = new StbInfoBo();
stb.setId(rs.getLong(1));
stb.setParentId(rs.getLong(2));
stb.setBrasId1(rs.getLong(3));
stb.setPvlan(rs.getLong(4));
stbList.add(stb);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtil.close(null, pstmt, rs);
}
return stbList;
}JdbcUtil 的close访法:
[code=Java]
public static void close(Connection conn,
PreparedStatement pstmt, ResultSet rs) {
try {
if (conn != null) {
conn.close();
conn = null;
}
if (pstmt != null) {
pstmt.close();
pstmt = null;
}
if (rs != null) {
rs.close();
rs = null;
}
} catch (Exception e) {
e.printStackTrace();
}
}
[/code]
开始的时候在网上查了一下,说是PreparedStatement在执行完一次SQL之后没有关闭,但是我这个是关闭了的啊,怎么还报这个错,望指点
rs.close();
rs = null;
}
if (pstmt != null) {
pstmt.close();
pstmt = null;
}
你的sql写的蛮奇怪的,记住,资源是先获取的后关闭。
PreparedStatement pstmt, ResultSet rs) {
try {
if (rs != null) {
rs.close();
rs = null;
}
if (pstmt != null) {
pstmt.close();
pstmt = null;
}
if (conn != null) {
conn.close();
conn = null;
}
} catch (Exception e) {
e.printStackTrace();
}
}
我去试了一下,还是不行啊,说一下我查询的数据量有点大大约有几百万条,就成这样子了
* 关闭数据库连接,注意关闭的顺序
*/
public void close(ResultSet rs,Statement ps, Connection conn) {
if(rs!=null){
try{
rs.close();
rs=null;
}catch(SQLException e){
e.printStackTrace();
}
}
if(ps!=null){
try{
ps.close();
ps=null;
}catch(SQLException e){
e.printStackTrace();
}
}
if(conn!=null){
try{
conn.close();
conn=null;
}catch(SQLException e){
e.printStackTrace();
}
}
}