Allright been trying to figure this out the last 2 days.
Statement statement = con.createStatement();
String query = "SELECT * FROM sell";
ResultSet rs = query(query);
while (rs.next()){//<--- I get there operation error here
This is the query method.
public static ResultSet query(String s) throws SQLException {
try {
if (s.toLowerCase().startsWith("select")) {
if(stm == null) {
createConnection();
}
ResultSet rs = stm.executeQuery(s);
return rs;
} else {
if(stm == null) {
createConnection();
}
stm.executeUpdate(s);
}
return null;
} catch (Exception e) {
e.printStackTrace();
con = null;
stm = null;
}
return null;
}
How can I fix this error?
wattostudios
8,65613 gold badges43 silver badges57 bronze badges
asked Jun 7, 2012 at 14:01
2
It’s hard to be sure just from the code you’ve posted, but I suspect that the ResultSet is inadvertently getting closed (or stm is getting reused) inside the body of the while loop. This would trigger the exception at the start of the following iteration.
Additionally, you need to make sure there are no other threads in your application that could potentially be using the same DB connection or stm object.
answered Jun 7, 2012 at 14:06
NPENPE
484k108 gold badges944 silver badges1009 bronze badges
1
IMHO, you should do everything you need with your ResultSet before you close your connection.
answered Jun 7, 2012 at 14:22
iozeeiozee
1,3391 gold badge12 silver badges24 bronze badges
there are few things you need to fix. Opening a connection, running a query to get the rs, closing it, and closing the connection all should be done in the same function scope as far as possible. from your code, you seem to use the «con» variable as a global variable, which could potentially cause a problem. you are not closing the stm object. or the rs object. this code does not run for too long, even if it has no errors. Your code should be like this:
if (stringUtils.isBlank(sql)){
throw new IllegalArgumentsException ("SQL statement is required");
}
Connection con = null;
PreparedStatement ps =null;
Resultset rs = null;
try{
con = getConnection();
ps = con.preparestatement(sql);
rs = ps.executeQuery();
processResults(rs);
close(rs);
close(ps);
close(con);
}catch (Execption e){
log.Exception ("Error in: {}", sql, e);
throw new RuntimeException (e);
}finally{
close(rs);
close(ps);
close(con);
}
answered Jun 7, 2012 at 14:33
srini.venigallasrini.venigalla
5,1271 gold badge18 silver badges29 bronze badges
use another Statement object in inner loop
Like
Statement st,st1;
st=con.createStatement();
st1=con.createStatement();
//in Inner loop
while(<<your code>>)
{
st1.executeQuery(<<your query>>);
}
answered Jun 30, 2013 at 1:33
I know this is a few years late, but I’ve found that synchronizing the db methods usually get rid of this problem.
answered Nov 22, 2013 at 8:39
ZeeZee
1,57212 silver badges25 bronze badges
0
As a developer, encountering errors is a common occurrence. One of the most frustrating errors to encounter is the ‘java.sql.sqlexception: operation not allowed after resultset closed’ error. This error can be caused by a number of factors, including incorrect code implementation, database connection issues, and more. In this guide, we will explore how to troubleshoot and fix this error.
The ‘java.sql.sqlexception: operation not allowed after resultset closed’ error occurs when a developer attempts to access a ResultSet object that has already been closed. This error can be caused by a number of factors, including:
- Incorrect implementation of JDBC code
- Connection issues with the database
- Incorrect use of ResultSet object
How to Troubleshoot and Fix the ‘java.sql.sqlexception: operation not allowed after resultset closed’ Error
To fix the ‘java.sql.sqlexception: operation not allowed after resultset closed’ error, follow these steps:
Check your code implementation: The first step to troubleshooting this error is to check your code implementation. Make sure that your JDBC code is correctly implemented and that all ResultSet objects are properly closed. If you are unsure about the implementation, consult the JDBC documentation for more information.
Check your database connection: Another common cause of this error is connection issues with the database. Make sure that your database connection is stable and that there are no connection issues. If you are unsure about the connection, try restarting your database and then running your code again.
Check your ResultSet object: If you have checked your JDBC code implementation and your database connection and the error persists, then the issue could be with your ResultSet object. Make sure that you are using the ResultSet object correctly and that it is not being closed prematurely.
- Use a try-with-resources statement: If you are still encountering the error, try using a try-with-resources statement when creating your ResultSet object. This statement will automatically close your ResultSet object when it is no longer needed, ensuring that it is not closed prematurely.
FAQ
Q1. What is a ResultSet object?
A ResultSet object is a table of data representing a database result set, which is usually generated by executing a statement that queries the database.
Q2. Why is my ResultSet object closing prematurely?
There could be a number of reasons why your ResultSet object is closing prematurely, including incorrect implementation of JDBC code, connection issues with the database, or incorrect use of the ResultSet object.
Q3. What is a try-with-resources statement?
A try-with-resources statement is a Java statement that ensures that a resource is automatically closed at the end of the statement’s execution. This statement can be used to automatically close a ResultSet object when it is no longer needed.
Q4. How can I prevent the ‘java.sql.sqlexception: operation not allowed after resultset closed’ error from occurring?
To prevent the ‘java.sql.sqlexception: operation not allowed after resultset closed’ error from occurring, make sure that you are using the ResultSet object correctly, that it is not being closed prematurely, and that your JDBC code implementation is correct.
Q5. What other errors can occur when working with JDBC code?
Other errors that can occur when working with JDBC code include SQLExceptions, ClassNotFoundExceptions, and more.
- Oracle JDBC documentation
- Stack Overflow thread on ‘java.sql.sqlexception: operation not allowed after resultset closed’ error
- Java Error
java.sql.SQLException: Operation not allowed after ResultSet closed - Fix Java Error
java.sql.SQLException: Operation not allowed after ResultSet closed
This tutorial demonstrates the java.sql.SQLException: Operation not allowed after ResultSet closed error in Java.
Java Error java.sql.SQLException: Operation not allowed after ResultSet closed
The error Operation Not Allowed After Resultset Closed is an SQL exception when we try to access a closed result set. As the Java Doc mentions, whenever a statement object is closed, If its Resultset object exists, it will also be closed.
The problem with the error is that the Resultset instance will also save the underlying statement. So when the underlying statement is closed, the Resultset will also be closed, which throws the error.
Here’s a snippet of code that will throw the same error.
Code:
ResultSet Result_Set; // class variable
Statement Demo_Statement = null;
try{
Demo_Statement = DB_Connection.createStatement();
Result_Set = Demo_Statement.getGeneratedKeys();
}
catch (Exception e){
throw e;
}
finally{
try{
if (Demo_Statement != null)
Demo_Statement.close();
} catch (SQLException e){
throw e;
}
}
System.out.println(Result_Set.next());
The code above with a database connection will throw the following error.
java.sql.SQLException: Operation not allowed after ResultSet closed
Fix Java Error java.sql.SQLException: Operation not allowed after ResultSet closed
The problem in the code is that we cannot close the statement instance before we are done with Resultset. Once we are done with the Resultset, we can close both Resultset and Statement Instance.
As we can see, we are trying to print the Boolean from Result_Set.next(), but we are using the Resultset after closing the statement instance, which is also closing the Resultset.
In the code above, the fix will be not to close the statement at this place or not to use the Resultset after closing the statement instance. It is based on your application either we can remove the Demo_Statement.close() or the System.out.println(Result_Set.next()); statement.
Code:
ResultSet Result_Set; // class variable
Statement Demo_Statement = null;
try{
Demo_Statement = DB_Connection.createStatement();
Result_Set = Demo_Statement.getGeneratedKeys();
}
catch (Exception e){
throw e;
}
finally{
try{
if (Demo_Statement != null)
Demo_Statement.close();
} catch (SQLException e){
throw e;
}
}
We just removed the part where we are trying the use the Resultset after the statement instance is closed. Once all the operations are done with the Resultset, we can close both the statement instance and Resultset.
I am creating a program to rename databases in mysql.
I have succeeded in everything and it successfully happens. But in the end of my script, its shows an error/exception saying «Operation not allowed after ResultSet closed». I really have no idea why this error appears even after researching about this error.
Although the full operation is successfully completed and the database is renamed.
Here is my code->
String x = (String) jComboBox1.getSelectedItem(); //jComboBox1 contains the name of current database selected
String z = JOptionPane.showInputDialog("Please enter new name for Database"); //Where user enters the name for new database.
new CustComm().setVisible(false); //Frame that carries the names of tables.
try{
Class.forName("java.sql.DriverManager");
Connection con = (Connection)
DriverManager.getConnection("jdbc:mysql://localhost:"+GlobalParams.portvar+"/",""+k,""+j);
Statement stmnt = (Statement) con.createStatement();
String query = "use "+x;
stmnt.executeQuery(query);
String query2 = "show tables";
ResultSet rs = stmnt.executeQuery(query2);
while (rs.next()){
String dname = rs.getString("Tables_in_"+x);
if(CustComm.jTextArea1.getText().equals("")){
CustComm.jTextArea1.setText(CustComm.jTextArea1.getText()+dname);
}
else{
CustComm.jTextArea1.setText(CustComm.jTextArea1.getText()+"n"+dname);
}
String y = CustComm.jTextArea1.getText();
Scanner scanner = new Scanner(y);
while (scanner.hasNextLine()) {
String line = scanner.nextLine();
String query3 = "Create database "+z;
stmnt.executeUpdate(query3);
//alter table my_old_db.mytable rename my_new_db.mytable
String query4 = "RENAME TABLE "+x+"."+line+" TO "+z+"."+line;
stmnt.executeUpdate(query4);
String query5 = "drop database "+x;
stmnt.executeUpdate(query5);
}}}
catch(Exception e){
JOptionPane.showMessageDialog(this,e.getMessage());
}
Please help.
asked Oct 4, 2013 at 12:35
2
You shouldn’t execute new queries on statement Statement stmnt = (Statement) con.createStatement(); while you use ResultSet from it, because this will close your ResultSet.
By default, only one ResultSet object per Statement object can be open
at the same time. Therefore, if the reading of one ResultSet object is
interleaved with the reading of another, each must have been generated
by different Statement objects. All execution methods in the Statement
interface implicitly close a statment’s current ResultSet object if an
open one exists.
You should create 2 different statements: first for query2 and second for queries 3-5.
Also it’s better to use PreparedStatement. You can read about the difference here.
answered Oct 4, 2013 at 12:55
Pavlo K.Pavlo K.
3711 silver badge9 bronze badges
1
Do you have to do this work via code? Have you looked into tools like Liquibase?
answered Oct 4, 2013 at 15:28
hooknchooknc
4,8345 gold badges31 silver badges60 bronze badges
Код метода отрабатывает нормально, но еще и выводит «Operation not allowed after ResultSet closed». На stackoverflow есть такее вопросы и они уже решенные. Но я что-то не так делаю, и оно не работает 
Код должен брать количество одного продукта из бд и увеличивать его на то количество, которое напишет пользователь. Как избавиться от «Operation not allowed after ResultSet closed»
Вообщем и целом, вот код:
if (searchedCategory) {
Statement statementUpdate = connection.createStatement();
sql = "select * from snacks where category='" + categoryFromUser + "'";
ResultSet rsUpdate = statementUpdate.executeQuery(sql);
// add an additional amount
while (rsUpdate.next()) {
int amountFromTable = rsUpdate.getInt("amount");
double priceFromTable = rsUpdate.getDouble("price");
String formattedPrice = String.format("%.2f", priceFromTable).replace(",", ".");
int amountRes = amountFromTable + amountFromUser;
sql = "update snacks " +
"set amount=" + amountRes + " where category='" + categoryFromUser + "'";
statementUpdate.executeUpdate(sql);
System.out.println(categoryFromUser + " " + formattedPrice + " " + amountRes);
}
rsUpdate.close();
statementUpdate.close();
} else {
System.out.println("There is no such category in the list :( n" +
"First, add your "" + categoryFromUser + "" using the addCategory command");
}
Как избавиться от «Operation not allowed after ResultSet closed»?
Ответы (2 шт):
Вот этот код надо убрать или перенести за цикл
sql = "update snacks " +
"set amount=" + amountRes + " where category='" + categoryFromUser + "'";
statementUpdate.executeUpdate(sql);
Нельзя использовать один и тот же statement для разных запросов одновременно. Сначала надо закрыть текущий, а потом только возможно переиспользовать его.
Можно создать новый statement, но лучше обновлять таблицу после ее чтения.
→ Ссылка
Автор решения: Roman Konoval
Лучше всего делать прямое изменение в БД, не вычитывая данные.
if (searchedCategory) {
String sql = "UPDATE snacks SET amount = amount + ? WHERE category = ?";
try (PreparedStatement updateStatement = connection.prepareStatement(sql)) {
updateStatement.setInt(1, amountFromUser);
updateStatement.setString(2, categoryFromUser);
updateStatement.executeUpdate();
}
}
Лучше использовать PreparedStatement (как в коде выше) и не строить запрос вручную из кусочков текста, так как это опасно.
А ошибка возникает, скорее всего, на второй итерации цикла на шаге rsUpdate.next(), т.к выполнение executeUpdate закрывает существующий ResultSet.
→ Ссылка
Вы не должны передавать ResultSet через общедоступные методы. Это подвержено утечке ресурсов, потому что вы вынуждены сохранять отчет и соединение открыто. Закрытие их неявно закрывает набор результатов. Но держать их открытыми приведет к тому, что они будут болтаться и заставить БД исчерпывать ресурсы, когда их слишком много.
Сопоставьте его с коллекцией Javabeans, как это, и верните его вместо:
public List<Biler> list() throws SQLException {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
List<Biler> bilers = new ArrayList<Biler>();
try {
connection = database.getConnection();
statement = connection.prepareStatement("SELECT id, name, value FROM Biler");
resultSet = statement.executeQuery();
while (resultSet.next()) {
Biler biler = new Biler();
biler.setId(resultSet.getLong("id"));
biler.setName(resultSet.getString("name"));
biler.setValue(resultSet.getInt("value"));
bilers.add(biler);
}
} finally {
if (resultSet != null) try { resultSet.close(); } catch (SQLException ignore) {}
if (statement != null) try { statement.close(); } catch (SQLException ignore) {}
if (connection != null) try { connection.close(); } catch (SQLException ignore) {}
}
return bilers;
}
Или, если вы уже на Java 7, просто используйте try-with-resources выражение, которое автоматически закрывает эти ресурсы
public List<Biler> list() throws SQLException {
List<Biler> bilers = new ArrayList<Biler>();
try (
Connection connection = database.getConnection();
PreparedStatement statement = connection.prepareStatement("SELECT id, name, value FROM Biler");
ResultSet resultSet = statement.executeQuery();
) {
while (resultSet.next()) {
Biler biler = new Biler();
biler.setId(resultSet.getLong("id"));
biler.setName(resultSet.getString("name"));
biler.setValue(resultSet.getInt("value"));
bilers.add(biler);
}
}
return bilers;
}
Кстати, вы не должны объявлять Connection, Statement и ResultSet как переменные экземпляра вообще (основная проблема безопасности потоков!) и не проглатывать SQLException в этой точке вообще ( вызывающий не будет знать, что возникла проблема), а также не закрывать ресурсы в том же try (если, например, закрытие результата закрывает исключение, то инструкция и соединение все еще открыты). Все эти проблемы исправлены в приведенных выше фрагментах кода.
|
igoRRR 1 / 1 / 0 Регистрация: 12.01.2010 Сообщений: 13 |
||||
|
1 |
||||
|
MySQL 04.04.2015, 00:20. Показов 6387. Ответов 2 Метки нет (Все метки)
Не давно начал изучать эту тему и столкнулся с проблемой. В общем у меня строится JTree на основе БД, получилось связать две таблицы, но при добавлении третей то есть третьего вложения выскакивает ошибка «Operation not allowed after ResultSet closed». Вот код
Может кто-нибудь поможет? Заранее благодарен.
0 |
|
Programming Эксперт 94731 / 64177 / 26122 Регистрация: 12.04.2006 Сообщений: 116,782 |
04.04.2015, 00:20 |
|
Ответы с готовыми решениями: Вернуть ResultSet JDBC: ResultSet ResultSet не определяется Сразу к делу. Взял коннектор с оф. сайта… ResultSet and Proccess 2 |
|
185 / 160 / 49 Регистрация: 30.07.2013 Сообщений: 508 |
|
|
05.04.2015, 01:23 |
2 |
|
Ты один Statement (stm) используешь для нескольких ResultSet. Как только ты получил данные для rs4 , то rs3 у тебя закрывается.
1 |
|
1 / 1 / 0 Регистрация: 12.01.2010 Сообщений: 13 |
|
|
05.04.2015, 14:25 [ТС] |
3 |
|
darknim, всё отлично заработало!
0 |
Answer by Brielle Ponce
The problem is with the way you fetch data in getStuff(). Each time you visit getStuff() you obtain a fresh ResultSet but you don’t close it. ,
Cool. I created a new Statement in getStuff() and that solved the problem.
– samxli
Apr 30 ’11 at 11:59
,
Can we tell which way AC power is going through a cable without cutting the cable?
,I guess after while(rs2.next()) you are trying to access something from rs1. But it’s already closed since you reexecuted statement to get rs2 from it. Since you didn’t close it, I beleive it’s used again below.
The problem is with the way you fetch data in getStuff(). Each time you visit getStuff() you obtain a fresh ResultSet but you don’t close it.
getStuff()The problem is with the way you fetch data in getStuff(). Each time you visit getStuff() you obtain a fresh ResultSet but you don’t close it.
getStuff()The problem is with the way you fetch data in getStuff(). Each time you visit getStuff() you obtain a fresh ResultSet but you don’t close it.
ResultSetAnswer by Raelyn Burns
Page generated in 0.009 sec. using MySQL 8.0.26-commercial ,The file you have requested (bug.php?id=620)
does not exist., © 2021, Oracle Corporation and/or its affiliates
The file you have requested (bug.php?id=620)
does not exist.
bug.php?id=620Answer by Sky Pollard
Detect Loop in Linked List And Identify the Start …,Count Number of Words in the given input String,Shortest Distance in m x n Matrix from Source to D…,HackerRank Problem: Birthday Chocolate
ResultSet resultSet; // class variable
...
...
Statement st = null;
try {
st = conn.createStatement();
resultSet = st.getGeneratedKeys();
} catch (Exception e) {
throw e;
} finally {
try {
if (st != null) st.close();
} catch (SQLException e) {
throw e;
}
}
Answer by Amias Singh
Getting java.sql.SQLException: Operation not allowed after ResultSet closed ,Copyright © 2021 SemicolonWorld. All Rights Reserved. ,Bottom line: you have several ResultSet pertaining to the same Statement object concurrently opened.,What makes things even worse is the rs from the calling code. It is also derived off-of the statement field but it is not closed.
When I execute the following code, I get an exception. I think it is because I’m preparing in new statement with he same connection object. How should I rewrite this so that I can create a prepared statement AND get to use rs2? Do I have to create a new connection object even if the connection is to the same DB?
try
{
//Get some stuff
String name = "";
String sql = "SELECT `name` FROM `user` WHERE `id` = " + userId + " LIMIT 1;";
ResultSet rs = statement.executeQuery(sql);
if(rs.next())
{
name = rs.getString("name");
}
String sql2 = "SELECT `id` FROM `profiles` WHERE `id` =" + profId + ";";
ResultSet rs2 = statement.executeQuery(sql2);
String updateSql = "INSERT INTO `blah`............";
PreparedStatement pst = (PreparedStatement)connection.prepareStatement(updateSql);
while(rs2.next())
{
int id = rs2.getInt("id");
int stuff = getStuff(id);
pst.setInt(1, stuff);
pst.addBatch();
}
pst.executeBatch();
}
catch (Exception e)
{
e.printStackTrace();
}
private int getStuff(int id)
{
try
{
String sql = "SELECT ......;";
ResultSet rs = statement.executeQuery(sql);
if(rs.next())
{
return rs.getInt("something");
}
return -1;
}//code continues
HI. I’m having a java.sql.SQLException on my code. I don’t understand why the resultset is closed in my try/catch block, since I put the close methods on the finally block.
Please help. I have searched the forum and I did what I could to research on this, but to no avail, i failed.
here is my code:
private void comboTBMonthItemStateChanged(java.awt.event.ItemEvent evt) {
// TODO add your handling code here:
//code, title, gdebit, gcredit, tdebit, tcredit, rdebit, rcredit == 8 fields
//fund, daccount, debit, caccount, credit, wtax, dcfa, ccfa,wcfa
Connection conn = null, dConn = null;
Statement stmt = null, dStmt = null;
ResultSet rs = null, dRs = null;
String xCode = "", xFund = "";
double xDebit = 0, xCredit = 0;
if(evt.getStateChange() == java.awt.event.ItemEvent.SELECTED){
try{
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(accountingLogIn.connectInfo.getUrl(), accountingLogIn.connectInfo.getUser(),
accountingLogIn.connectInfo.getPasswd());
dConn = DriverManager.getConnection(accountingLogIn.connectInfo.getUrl(), accountingLogIn.connectInfo.getUser(),
accountingLogIn.connectInfo.getPasswd());
stmt = conn.createStatement();
stmt.executeUpdate("DELETE FROM accounting.trialbalance");
rs = stmt.executeQuery(
"SELECT daccount, caccount, debit, credit, wtax, LEFT(fund, 2) AS fund " +
"FROM accounting.jdv " +
"WHERE MONTH(jdvdate) = '"+ comboTBMonth.getSelectedItem() +"' " +
"AND YEAR(jdvdate) = '"+comboTBYear.getSelectedItem()+"'");
rs.beforeFirst();
int i = 0, x = 0;
while(rs.next()){
xFund = rs.getString("fund");
if(rs.getDouble("debit") != 0){
xCode = rs.getString("daccount");
xDebit = rs.getDouble("debit");
dStmt = dConn.createStatement();
dRs = dStmt.executeQuery(
"SELECT account, title, gfcredit, gfdebit, tfcredit, tfdebit, rfcredit, rfdebit " +
"FROM accounting.trialbalance");
dRs.last();
i = dRs.getRow();
//JOptionPane.showMessageDialog(this, i + " # row in trialbalance");
if(i == 0){
if(xFund.equals("GF")){
dStmt.executeUpdate(
"INSERT INTO accounting.trialbalance" +
"(account, gfdebit)" +
"VALUES('"+ rs.getString("daccount") +"', '"+ rs.getString("debit") +"')");
}
}
else{
// JOptionPane.showMessageDialog(this, i + " with contents");
dRs.beforeFirst();
int y = 0;
while(dRs.next()){
// JOptionPane.showMessageDialog(this, xFund);
if(xFund.equals("GF")){
// JOptionPane.showMessageDialog(this, dRs.getString("account") + " trial account");
if(dRs.getString("account").equals(rs.getString("daccount"))){
JOptionPane.showMessageDialog(this, "insert this");
dStmt.executeUpdate(
"UPDATE accounting.trialbalance " +
"SET gfdebit = '"+ (dRs.getDouble("gfdebit") + 5000) +"' " +
"WHERE account = '"+ rs.getString("daccount")+"'");
JOptionPane.showMessageDialog(this, "updated");
}
else{
dStmt.executeUpdate(
"INSERT INTO accounting.trialbalance" +
"(account, gfdebit)" +
"VALUES('"+ rs.getString("daccount") +"', '"+ rs.getString("debit") +"')");
}
}
}
}
}
}
displayTable(tableTB, "SELECT * FROM accounting.trialbalance ORDER BY account", scrollTB, getModelTableTB(), tbwidth);
}
catch(SQLException ex){
JOptionPane.showMessageDialog(this, ex + " A");
}
catch(ClassNotFoundException ex){
}
finally{
try{
dRs.close();
dRs = null;
dStmt.close();
dStmt = null;
rs.close();
rs = null;
stmt.close();
stmt = null;
conn.close();
conn = null;
}
catch(SQLException ex){
JOptionPane.showMessageDialog(this, ex + " B");
}
}
}
}