javaresultsettablemodel

Operation not allowed after ResultSet closed after using ResultSet in AbstractTableModel


I'm trying to create a frame that show me all the movies names in my database, and them select a row in the frame to execute another query.

I have a ResultSet (rs) that receive the result of a query execution to select the name of the movies. After that, I use the rs in my SQLTableModel that I use to create a JTable and show in my frame. The table show me the name of all movies that I have in my in my database. Everything works great.

But, when I select a row, I use the same rs to receive another query, that go on my database to select all the informations of the movie that I have selected in my JTable, and I get some erros.

Firts, the row I have selected in my JTable lose the value (the name of the movie). But in the others rows nothing happen. Second, my console show me a error that came from my SQLTableModel, in the function "getValueAt", saying that "Erro: #0 Operation not allowed after ResultSet closed".

I searched some solution here in stackoverflow, but no success.

I did some tests. If my ResultSet (rs) receives multiple queries before I use it in my SQLTableModel, everything works fine. But after I use it in the SQLTableModel, if i try to use it again, I get the errors I mentioned.

FrameCliente.java

public class FrameCliente {

    JFrame frameCliente;
    JTable table;

    BancoDeDados bd;
    ResultSet rs;

    public FrameCliente() {
        bd = new BancoDeDados();
        frameCliente = new JFrame();
        table = new JTable();
        rs = bd.listar("SELECT fil_nome Nome FROM filme");
        table.setModel(new SQLTableModel(rs));

        table.addMouseListener(new MouseAdapter() {
            public void mousePressed(MouseEvent e) {
                if(table.getSelectedRowCount() > 0) {
                    rs = bd.listar(" SELECT * FROM filme WHERE fil_nome = '" + table.getValueAt(table.getSelectedRow(), 0) + "'");  
                }
            }
        });

        frameCliente.add(new JScrollPane(table));
        frameCliente.setTitle("Filmes");
        frameCliente.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        frameCliente.setSize(800, 300);
        frameCliente.setLocationRelativeTo(null);
        frameCliente.setVisible(true);
    }

}

SQLTableModel.java

public class SQLTableModel extends AbstractTableModel {

    private ResultSet rs;
    private ResultSetMetaData rsmd;
    private int numberOfRows=0;

    public SQLTableModel(ResultSet rs){
        this.rs = rs;
        try { //Todos os métodos são privado e isso gera exceção.
            rsmd = this.rs.getMetaData();
            //Navegar para encontrar a ultima linha, para saber quantas linhas a tabela possui.
            this.rs.last();     // Pega a ultima linha;
            numberOfRows = rs.getRow(); //Devolve a linha, no caso, a ultima.
            this.rs.beforeFirst(); //Retorna para a primeira linha.
        }catch(SQLException sqle){
            System.out.printf("Erro: #%d [%s]\n", 
                    sqle.getErrorCode(), sqle.getMessage());
        }

    }


    @Override
    public int getRowCount() {
        // TODO Auto-generated method stub
        return numberOfRows;
    }

    @Override
    public int getColumnCount() {
        // TODO Auto-generated method stub
        try { 
            return rsmd.getColumnCount();
        }catch(SQLException sqle){
            System.out.printf("Erro: #%d [%s]\n", 
                    sqle.getErrorCode(), sqle.getMessage());
            return 0; // Se der exceção não retorna nenhuma coluna;
        }
    }

    @Override
    public String getColumnName(int column) {
        String ColumnName = "";
        try { 
            ColumnName = rsmd.getColumnLabel(column+1);
        }catch(SQLException sqle){
            System.out.printf("Erro: #%d [%s]\n", 
                    sqle.getErrorCode(), sqle.getMessage());
        }
        return ColumnName;
    }

    @Override
    public Object getValueAt(int rowIndex, int columnIndex) {
        // TODO Auto-generated method stub
        Object dado = null;
        try { 
            rs.absolute(rowIndex+1);
            dado = rs.getObject(columnIndex+1);
        }catch(SQLException sqle){
            System.out.printf("Erro: #%d [%s]\n", 
                    sqle.getErrorCode(), sqle.getMessage());
        }
        return dado;
    }

    @Override
    public Class<?> getColumnClass(int columnIndex) {
        String className;
        try { 
            className = rsmd.getColumnClassName(columnIndex+1);
            return Class.forName(className);
        }catch(SQLException sqle){
            System.out.printf("Erro: #%d [%s]\n", 
                    sqle.getErrorCode(), sqle.getMessage());
        } catch (ClassNotFoundException cnfe) {
            System.out.printf("Erro: %s\n", cnfe.getMessage());
        }
        return null;
    }

    @Override
    public boolean isCellEditable(int rowIndex, int columnIndex) {
        return false;
    }

    public void atualizarTabela() {
        fireTableDataChanged();
    }


}

BancoDeDados

public class BancoDeDados {
    private Connection con = null;
    private Statement sta = null;    //faz consultas
    private ResultSet rset = null;  //armazenas as info pra trabalhar com elas depois - resultado do select

    public BancoDeDados() {
        this.conectar();
    }


    public void conectar() {
        String servidor = "jdbc:mysql://localhost:3306/cinema?useSSL=true&useTimezone=true&serverTimezone=UTC";
        String usuario = "root";
        String senha = "coreduo2";
        String driver = "com.mysql.cj.jdbc.Driver";

        try {

            //Class.forName(driver);
            this.con = DriverManager.getConnection(servidor, usuario, senha);
            this.sta = this.con.createStatement();
        }
        catch(Exception e)
        {
            System.out.println("Error " + e.getMessage());
        }
    }

    public boolean estaConectado() {
        if (con != null) {
            return true;
        }
        else {
            return false;
        }
    }

    public void desconecte() {
        try {
            con.close();
            System.out.println("Conexao com banco encerrado");
        }
        catch(SQLException e) {
            System.out.println("Erro: " +  e.getMessage());
        }
    }

    public ResultSet listar(String query) {
        try {
            return sta.executeQuery(query);
            //this.sta = this.con.createStatement();
            /*while (this.rset.next()) {
                System.out.println("ID: " + rset.getString("fil_codigo") + " Nome: " + rset.getString("fil_nome") + " Duração: " + rset.getString("fil_duracao"));
            }*/

        }
        catch(SQLException e) {
            System.out.println("Erro "+ e.getMessage());
            return null;
        }   
    }

    public void inserir(String query) {
        try {
            this.sta.executeUpdate(query);
        }
        catch(SQLException e) {
            System.out.println("Erro"+ e.getMessage());
        }

    }

    public void excluir(String query) {
        try {
            this.sta.executeUpdate(query);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public void editar(String query) {
        try {
            this.sta.executeUpdate(query);
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
    }

}


Solution

  • Always use the memory structure such as ArrayList or Map to cache the result got from DB. As connection to DB is expensive, keeping this connection is also expensive. So calling DB as quick as possible, as short as possbile. Once you got data in memory, you are free to manipulate as you wish.