CODEWITHSUNDEEP
javaswingsqlitenetbeans-7
CODe
CODe

Reputation: 2301

Database Update & Sortable JTable Issues

I've written a custom JTable to show the contents of my SQLite database in my Java program. Here's the code:

public class SortableJTable extends JTable{

public SortableJTable()
{
    super();
}

public SortableJTable(DefaultTableModel dtm)
{
    super(dtm);
}

@Override
public void setValueAt(Object obj, int row, int col)
{
    super.setValueAt(obj, row, col);
    PRGView.dbc.updateOrdersWithTicketRequestID(this.getColumnName(col).toString(), obj, (Integer)this.getValueAt(row, 0));
}

@Override
public Class getColumnClass(int c) 
{
    switch (c)
    {
        case 0:
            return Integer.class;
        case 1:
            return Integer.class;
        case 2:
            return String.class;
        case 3:
            return String.class;
        case 4:
            return Double.class;
        case 5:
            return Double.class;
        default:
            return String.class;
    }
}
}

As you can see, I'd like whenever someone edits a cell in the custom JTable (which is when the setValueAt method is called) to be able to immediately modify the database contents to reflect those changes.

Here's the database code that is called within that method.

public void updateOrdersWithTicketRequestID(String columnName, Object columnValue, Integer ticketRequestID)
{
    try
    {
        String sql = "UPDATE ORDERS SET ? = ? WHERE Ticket_Request_ID = ?";

        pstmt = conn.prepareStatement(sql);

        pstmt.setString(1, columnName);
        pstmt.setObject(2, columnValue);
        pstmt.setInt(3, ticketRequestID);

        pstmt.executeUpdate();
        pstmt.close();
    }
    catch (SQLException e){}
}

Once my code gets to this statement: 

 pstmt = conn.prepareStatement(sql);

in the above code, it returns the error: SQL error or missing database (near "?": syntax error).

I have two questions:

1) Why am I getting this error message? (Am I doing the prepared statement incorrectly?)

2) The getColumnClass in my custom JTable class is not sorting my columns correctly. They are always sorted in String order, rather than using the switch cases I have laid out. Am I doing this wrong?

Thanks for any help!

Upvotes: 0

Views: 89

Answers (1)

camickr
camickr

Reputation: 324118

1) Why am I getting this error message? (Am I doing the prepared statement incorrectly?)

I don't know much about SQL so I'll just make a couple of suggestions:

  1. catch (SQLException e){} - print out the SQLException.
  2. Also, if you are not sure that the PreparedStatement is correct. Then try to invoke the SQL by using hardcoded values in the SQL string. Once you get the hardcode SQL working you can convert it to a PreparedStatement.

The getColumnClass in my custom JTable class is not sorting my columns correctly

The Comparator used by the sorter is actually based on the getColumnClass() method of the TableModel, not the JTable.

So you need to override the getColumnClass(...) method of your TableModel. I would also override the setValueAt(...) method in the TableModel as well to keep all the overrides in one class.

Edit:

It is possible that you can't specify a column name as a variable. I have used code like this in the past successfully:

String sql = "UPDATE Page SET Title = ? WHERE Name = ?";

PreparedStatement stmt = connection.prepareStatement(sql);

stmt.setString( 1, "update" );
stmt.setString( 2, "Name3" );
stmt.executeUpdate();
stmt.close();

Again, try with hardcoded values first.

Upvotes: 2

Related Questions