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.
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.