SQLite e ResultSet non vanno d'accordo...

[Dreamer89]

Salve, ho un problema. Sto creando un'applicazione standard per visualizzare i risultati di tabelle di database in una JTable. La mia applicazione dovrebbe poter connettersi a MySQL e SQLite. Con MySQL riesco a connettermi e ad eseguire query senza problemi. Con SQLite invece ho dei problemi.

Quando tento di eseguire una query con SQLite, viene lanciata una eccezione con il seguente messaggio: "ResultSet is TYPE_FORWARD ONLY". OK allora ho modificato lo statement in questo modo:

statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); 

Ma appena tento di connettermi ad SQLite un'eccezione viene lanciata con il seguente errore: "SQLite only supports TYPE_FORWARD_ONLY cursors".

Ma allora, come devo risolvere?

Se può esservi utile questa è la classe che gestisce la connessione al database che estende AbstractTableModel per i risultati visualizzabili nella JTable:

import javax.swing.JOptionPane;
import javax.swing.table.AbstractTableModel;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;

public class MyTableModel extends AbstractTableModel
{
    private Connection connection;
    private Statement statement;
    private ResultSet resultSet;
    private ResultSetMetaData metaData;
    private int numberOfRows = 0;
    public boolean isConnected = false;
    
    public void connect (String driver, String database, String username, String password) throws Exception
    {
        Class.forName(driver);
        
        if (driver.equals("com.mysql.jdbc.Driver"))
            connection = DriverManager.getConnection("jdbc:mysql://localhost/" + database, username, password);
        else if (driver.equals("org.sqlite.JDBC"))
            connection = DriverManager.getConnection("jdbc:sqlite:db.db");
        
        statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        
        isConnected = true;
    }
    
    public void setQuery (String query)
    {
        try
        {
            if (query.toLowerCase().contains("select"))
            {
                resultSet = statement.executeQuery (query);
                
                metaData = resultSet.getMetaData();
                
                resultSet.last();
                numberOfRows = resultSet.getRow();
                
                fireTableStructureChanged();
            }
            else
            {
                statement.execute(query);
            }
        }
        catch (Exception exception)
        {
            JOptionPane.showMessageDialog(null, exception.getMessage(), "Errore", JOptionPane.ERROR_MESSAGE);
        }
    }
    
    public Class getColumnClass (int column)
    {
        if (resultSet != null)
        {
            try
            {
                String className = metaData.getColumnClassName(column + 1);
                return Class.forName(className);
            }
            catch (Exception exception)
            {
                exception.printStackTrace();
            }
        }
        
        return Object.class;
    }
    
    public int getColumnCount()
    {
        if (resultSet != null)
        {
            try
            {
                return metaData.getColumnCount();
            }
            catch (Exception exception)
            {
                exception.printStackTrace();
            }
        }
        
        return 0;
    }
    
    public String getColumnName (int column)
    {
        if (resultSet != null)
        {
            try
            {
                return metaData.getColumnName (column + 1);
            }
            catch (Exception exception)
            {
                exception.printStackTrace();
            }
        }
        
        return "";
    }
    
    public int getRowCount()
    {
        return numberOfRows;
    }
    
    public Object getValueAt (int row, int column)
    {
        if (resultSet != null)
        {
            try
            {
                resultSet.absolute(row + 1);
                return resultSet.getObject(column + 1);
            }
            catch (Exception exception)
            {
                exception.printStackTrace();
            }
        }
        
        return "";
    }

    public void disconnect()
    {
        try
        {
            resultSet = null;
            fireTableStructureChanged();
            
            if (connection != null)
                connection.close();
            if (statement != null)
                statement.close();
            
            isConnected = false;
        }
        catch (Exception exception)
        {
            exception.printStackTrace();
        }
    }
} 

[Dreamer89]

Allora, ho capito che la soluzione fosse quella di evitare di utilizzare i metodi last e absolute di ResultSet, allora con un ciclo while ho letto tutte le righe in modo sequenziale e ho salvato tutto in ArrayList < ArrayList < Object > >. Però ogni volta che eseguo una SELECT query viene lanciata una IllegalStateException:

java.lang.IllegalStateException: SQLite JDBC: inconsistent internal state
    at org.sqlite.RS.checkCol(RS.java:62)
    at org.sqlite.RS.getColumnCount(RS.java:315)
    at MyTableModel.getColumnCount(MyTableModel.java:81)
    at javax.swing.JTable.createDefaultColumnsFromModel(JTable.java:1205)
    at javax.swing.JTable.tableChanged(JTable.java:4324)
    at javax.swing.table.AbstractTableModel.fireTableChanged(AbstractTableModel.java:280)
    at javax.swing.table.AbstractTableModel.fireTableStructureChanged(AbstractTableModel.java:200)
    at MyTableModel.setQuery(MyTableModel.java:61)
    at DFDatabase$QueryHandler.actionPerformed(DFDatabase.java:67)
    at javax.swing.JTextField.fireActionPerformed(JTextField.java:492)
    at javax.swing.JTextField.postActionEvent(JTextField.java:705)
    at javax.swing.JTextField$NotifyAction.actionPerformed(JTextField.java:820)
    at javax.swing.SwingUtilities.notifyAction(SwingUtilities.java:1636)
    at javax.swing.JComponent.processKeyBinding(JComponent.java:2849)
    at javax.swing.JComponent.processKeyBindings(JComponent.java:2884)
    at javax.swing.JComponent.processKeyEvent(JComponent.java:2812)
    at java.awt.Component.processEvent(Component.java:5818)
    at java.awt.Container.processEvent(Container.java:2058)
    at java.awt.Component.dispatchEventImpl(Component.java:4413)
    at java.awt.Container.dispatchEventImpl(Container.java:2116)
    at java.awt.Component.dispatchEvent(Component.java:4243)
    at java.awt.KeyboardFocusManager.redispatchEvent(KeyboardFocusManager.java:1848)
    at java.awt.DefaultKeyboardFocusManager.dispatchKeyEvent(DefaultKeyboardFocusManager.java:697)
    at java.awt.DefaultKeyboardFocusManager.preDispatchKeyEvent(DefaultKeyboardFocusManager.java:962)
    at java.awt.DefaultKeyboardFocusManager.typeAheadAssertions(DefaultKeyboardFocusManager.java:834)
    at java.awt.DefaultKeyboardFocusManager.dispatchEvent(DefaultKeyboardFocusManager.java:661)
    at java.awt.Component.dispatchEventImpl(Component.java:4285)
    at java.awt.Container.dispatchEventImpl(Container.java:2116)
    at java.awt.Window.dispatchEventImpl(Window.java:2440)
    at java.awt.Component.dispatchEvent(Component.java:4243)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:599)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:273)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:183)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:173)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:168)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:160)
    at java.awt.EventDispatchThread.run(EventDispatchThread.java:121) 

Questo è MyTableModel che ho modificato:

import javax.swing.JOptionPane;
import javax.swing.table.AbstractTableModel;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;

public class MyTableModel extends AbstractTableModel
{
    private Connection connection;
    private Statement statement;
    private ResultSet resultSet;
    private ResultSetMetaData metaData;
    private int numberOfRows;
    private ArrayList < ArrayList < Object > > tableList = new ArrayList < ArrayList < Object > >();
    private ArrayList < Object > rows = new ArrayList < Object >();
    public boolean isConnected = false;
    
    public void connect (String driver, String database, String username, String password) throws Exception
    {
        Class.forName(driver);
        
        if (driver.equals("com.mysql.jdbc.Driver"))
            connection = DriverManager.getConnection("jdbc:mysql://localhost/" + database, username, password);
        else if (driver.equals("org.sqlite.JDBC"))
            connection = DriverManager.getConnection("jdbc:sqlite:db.db");
        
        statement = connection.createStatement();
        
        numberOfRows = 0;
        isConnected = true;
    }
    
    public void setQuery (String query)
    {
        try
        {
            if (query.toLowerCase().contains("select"))
            {
                resultSet = statement.executeQuery (query);
                metaData = resultSet.getMetaData();
                
                tableList.clear();
                
                for (int i = 1; i <= metaData.getColumnCount(); i++)
                    rows.add (resultSet.getObject (i));
                
                while (resultSet.next())
                {
                    for (int i = 1; i <= metaData.getColumnCount(); i++)
                        rows.add (resultSet.getObject (i));
                    
                    tableList.add (rows);
                    rows.clear();
                }
                
                numberOfRows = tableList.size();
                
                fireTableStructureChanged(); //riga 61
            }
            else
            {
                statement.execute(query);
                JOptionPane.showMessageDialog(null, "Query eseguita con successo", "Query OK", JOptionPane.INFORMATION_MESSAGE);
            }
        }
        catch (Exception exception)
        {
            JOptionPane.showMessageDialog(null, exception.getMessage(), "Errore", JOptionPane.ERROR_MESSAGE);
        }
    }
    
    public int getColumnCount()
    {
        if (resultSet != null)
        {
            try
            {
                return metaData.getColumnCount(); //riga 81
            }
            catch (Exception exception)
            {
                exception.printStackTrace();
            }
        }
        
        return 0;
    }
    
    public String getColumnName (int column)
    {
        if (resultSet != null)
        {
            try
            {
                return metaData.getColumnName (column + 1);
            }
            catch (Exception exception)
            {
                exception.printStackTrace();
            }
        }
        
        return "";
    }
    
    public int getRowCount()
    {
        return numberOfRows;
    }
    
    public Object getValueAt (int row, int column)
    {
        if (resultSet != null)
        {
            ArrayList < Object > rowList = tableList.get(row);
            return rowList.get(column);
        }
        
        return "";
    }

    public void disconnect()
    {
        try
        {
            resultSet = null;
            tableList.clear();
            fireTableStructureChanged();
            
            if (connection != null)
                connection.close();
            if (statement != null)
                statement.close();
            
            isConnected = false;
        }
        catch (Exception exception)
        {
            exception.printStackTrace();
        }
    }
}