I'll leave my code here. My problem resides in the line marked with --> <-- where I get java.sql.SQLException: ResultSet closed and I can't find the reason why.
I've tried creating a different Statement, using stat2 which works fine with the ResultSet right above this one, changing stat4 to be a PreparedStatement instead, but nothing seems to work.
I've already found trouble with the previous ResultSet in this project, so I guess I don't really understand them very well.
Anyway, if someone can give me some tips about this I would appreciate it a lot.
public void asignarOficio(AsignacionesData pagina) throws SQLException {
Statement stat1 = con.createStatement();
ResultSet rsObligados = stat1.executeQuery("SELECT * FROM " TABLA_OBLIGADOS " WHERE " OBLIGADOS_COL_ASIGNADO " = 'NON'");
//POR CADA ALUMNO/A EXISTENTE EN LA TABLA BUSCAREMOS UNA OFERTA DE TFG QUE ASIGNARLE
while(rsObligados.next()) {
//PARA SABER QUE OFERTA DEBEMOS ASIGNAR AL ALUMNOS, PRIMERO BUSCAMOS
//EL PROFESOR/A QUE (ESTANDO OBLIGADO/A) MENOS TFGS DIRIJA
Statement stat2 = con.createStatement();
ResultSet rsMin = stat2.executeQuery("SELECT MIN(" PROFESORES_COL_TFGS ") FROM " TABLA_PROFESORES " WHERE " PROFESORES_COL_OBLIGA " = 'SÍ'");
int min = rsMin.getInt(1);
System.out.println("min: " min);
rsMin.close();
ResultSet rsProf = stat2.executeQuery("SELECT * FROM " TABLA_PROFESORES " WHERE " PROFESORES_COL_TFGS " = '" min "'");
String prof = rsProf.getString(2);
System.out.println("prof: " prof);
rsProf.close();
//UNA VEZ CONSEGUIDO EL NOMBRE DEL PROFESOR/A BUSCAMOS
//ENTRE LAS OFERTAS DISPONIBLES, UNA QUE DIRIJA ÉL/ELLA
Statement stat4 = con.createStatement();
ResultSet rsOferta = stat4.executeQuery("SELECT * FROM " TABLA_OFERTAS " WHERE " OFERTAS_COL_TITOR " = '" prof "' AND " OFERTAS_COL_ASIGNADA " = 'NON'");
------------>int idOfer = rsOferta.getInt(1);<--------------
System.out.println(" idOfer = " idOfer);
int numOfer = rsOferta.getInt(2);
String tutOfer = rsOferta.getString(4);
String cotutOfer = rsOferta.getString(5);
rsOferta.close();
//AL TENER YA LOCALIZADO EL TFG SOLO QUEDA HACER LOS UPDATES
//PERTINENTES EN LAS TABLAS DE LA BD
Statement stat3 = con.createStatement();
stat3.executeUpdate("UPDATE " TABLA_OFERTAS " SET " OFERTAS_COL_ASIGNADA " = 'OB' WHERE " OFERTAS_COL_ID " = '" idOfer "'");
stat3.executeUpdate("UPDATE " TABLA_OBLIGADOS " SET " OBLIGADOS_COL_ASIGNADO " = 'SÍ' WHERE " OBLIGADOS_COL_ID " = '" rsObligados.getInt(1) "'");
stat3.executeUpdate("UPDATE " TABLA_OBLIGADOS " SET " OBLIGADOS_COL_SELECCION " = " numOfer " WHERE " OBLIGADOS_COL_ID " = '" rsObligados.getInt(1) "'");
if (cotutOfer.equals("")) {
stat3.executeUpdate("UPDATE " TABLA_PROFESORES " SET " PROFESORES_COL_TFGS " = " PROFESORES_COL_TFGS " 1 WHERE " PROFESORES_COL_NOMBRE " = '" tutOfer "'");
} else {
stat3.executeUpdate("UPDATE " TABLA_PROFESORES " SET " PROFESORES_COL_TFGS " = " PROFESORES_COL_TFGS " 0.5 WHERE " PROFESORES_COL_NOMBRE " = '" tutOfer "'");
stat3.executeUpdate("UPDATE " TABLA_PROFESORES " SET " PROFESORES_COL_TFGS " = " PROFESORES_COL_TFGS " 0.5 WHERE " PROFESORES_COL_NOMBRE " = '" cotutOfer "'");
}
}
}
Here I provide de Log in case someone wants take a look at it:
prof: Formella, Arno
sept. 09, 2021 6:13:05 P. M. UI.AsignacionesData btn_AsignarActionPerformed
SEVERE: null
java.sql.SQLException: ResultSet closed
at org.sqlite.core.CoreResultSet.checkOpen(CoreResultSet.java:76)
at org.sqlite.core.CoreResultSet.markCol(CoreResultSet.java:103)
at org.sqlite.jdbc3.JDBC3ResultSet.getInt(JDBC3ResultSet.java:395)
at CORE.DBManager.asignarOficio(DBManager.java:754)
at UI.AsignacionesData.btn_AsignarActionPerformed(AsignacionesData.java:209)
at UI.AsignacionesData$1.actionPerformed(AsignacionesData.java:74)
at java.desktop/javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1967)
at java.desktop/javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2308)
at java.desktop/javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:405)
at java.desktop/javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:262)
at java.desktop/javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:279)
at java.desktop/java.awt.Component.processMouseEvent(Component.java:6636)
at java.desktop/javax.swing.JComponent.processMouseEvent(JComponent.java:3342)
at java.desktop/java.awt.Component.processEvent(Component.java:6401)
at java.desktop/java.awt.Container.processEvent(Container.java:2263)
at java.desktop/java.awt.Component.dispatchEventImpl(Component.java:5012)
at java.desktop/java.awt.Container.dispatchEventImpl(Container.java:2321)
at java.desktop/java.awt.Component.dispatchEvent(Component.java:4844)
at java.desktop/java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4919)
at java.desktop/java.awt.LightweightDispatcher.processMouseEvent(Container.java:4548)
at java.desktop/java.awt.LightweightDispatcher.dispatchEvent(Container.java:4489)
at java.desktop/java.awt.Container.dispatchEventImpl(Container.java:2307)
at java.desktop/java.awt.Window.dispatchEventImpl(Window.java:2764)
at java.desktop/java.awt.Component.dispatchEvent(Component.java:4844)
at java.desktop/java.awt.EventQueue.dispatchEventImpl(EventQueue.java:772)
at java.desktop/java.awt.EventQueue$4.run(EventQueue.java:721)
at java.desktop/java.awt.EventQueue$4.run(EventQueue.java:715)
at java.base/java.security.AccessController.doPrivileged(AccessController.java:391)
at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:85)
at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:95)
at java.desktop/java.awt.EventQueue$5.run(EventQueue.java:745)
at java.desktop/java.awt.EventQueue$5.run(EventQueue.java:743)
at java.base/java.security.AccessController.doPrivileged(AccessController.java:391)
at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:85)
at java.desktop/java.awt.EventQueue.dispatchEvent(EventQueue.java:742)
at java.desktop/java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:203)
at java.desktop/java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:124)
at java.desktop/java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:113)
at java.desktop/java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:109)
at java.desktop/java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
at java.desktop/java.awt.EventDispatchThread.run(EventDispatchThread.java:90)
I'm not able to put the log as "code" in the editor of StackOverflow, sorry for the inconvenience that may cause.
CodePudding user response：
It looks like you are reusing Statements here:
Statement stat2 = con.createStatement();
ResultSet rsMin = stat2.executeQuery("SELECT MIN(" PROFESORES_COL_TFGS ") FROM " TABLA_PROFESORES " WHERE " PROFESORES_COL_OBLIGA " = 'SÍ'");
int min = rsMin.getInt(1);
System.out.println("min: " min);
rsMin.close();
ResultSet rsProf = stat2.executeQuery("SELECT * FROM " TABLA_PROFESORES " WHERE " PROFESORES_COL_TFGS " = '" min "'");
String prof = rsProf.getString(2);
System.out.println("prof: " prof);
Try to create a Statement (or even better PreparedStatments) for each query.
Also please check if the ResultSets are not empty, SQLite may close the ResultSet when it's empty.
CodePudding user response：
The problem comes that apparently SQLite closes de ResultSet automatically when the query finds no row to match.
Be sure to check your ResultSet with if(ResultSet.next()) it will only go through the if if the ResultSet has something inside.