Updating a single row database in Java

Question

So I have a database that is made like this

{MATERIAL NAME;QUANTITY}

I also have a JTable in Java that uploads the info from the database (SQLite). As I edit a cell in the table, it automatically updates the database in this way:

//table listener
public void tableChanged(TableModelEvent e) {
    //gets row and column if the table is edited
    int row = e.getFirstRow();
    int column = e.getColumn();
    //change in sqlite
    if (column == 1) {
        int value = Integer.parseInt(table.getModel().getValueAt(row, column).toString());
        String materialId = table.getModel().getValueAt(row, column-1).toString();
        try (Connection c = DriverManager.getConnection("jdbc:sqlite:database.db"); Statement statement = c.createStatement()) {
            String sql = "UPDATE MATERIALS set QUANTITY = " + value + " where MATERIAL='" + materialId +"';";
            statement.executeUpdate(sql);
            statement.close();
            c.close();
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
    }
    else if (column == 0) {
        int value = Integer.parseInt(table.getModel().getValueAt(row, column+1).toString());
        String materialId = table.getModel().getValueAt(row, column).toString();
        try (Connection c = DriverManager.getConnection("jdbc:sqlite:database.db"); Statement statement = c.createStatement()) {
            String sql = "UPDATE MATERIALS set MATERIAL = " + materialId + " where MATERIAL='" + materialId +"';";
            statement.executeUpdate(sql);
            statement.close();
            c.close();
        } catch (SQLException e1) {
            e1.printStackTrace();
        }
    }
}

It's okay when the quantity is updated. However, once the material name is updated, I don't know how I am supposed to update it in the database.

String sql = "UPDATE MATERIALS set MATERIAL = " + materialId + " where MATERIAL='" + materialId +"';";

Maybe there is a way to save a previous value of that value? I can not use the quantity of materials as they might be repeated.

I would like to avoid adding IDs to the objects if possible.

Unrelated, but: do ***not*** concatenate values into SQL queries. Learn to properly use a `PreparedStatement` instead. — , Nov 06 '16 at 09:57

score 1 · Accepted Answer · edited Mar 17 '17 at 15:05

This statement:

String sql = "UPDATE MATERIALS set MATERIAL = " + materialId + " where MATERIAL='" + materialId +"';"

Has two (three) problems.

The 'old' value and the 'new' value are the same. You have a logical problem.
WHERE part is concatenated like for string type, but SET part is not

concatenation is evil. The Correct way to do this is

String sql = "UPDATE MATERIALS set MATERIAL = ? where MATERIAL= ?";
PreparedStatement prepstmt = conn.prepareStatement(sql);
prepstmt .setString(1, newMatewrial);
prepstmt .setString(2, oldMaterial);
prepstmt .executeUpdate();

Logical problem must be resolved in your conception.

It Seems the main problem is in basically not using the ID . Every row in relational database should have stable primary key (ID like You say). Name isn't primary key, because can be changed.

WHERE part should use ID and not name.

EDIT: in my opinion good JTable model is one way to solve your problem.

Maybe automagic default table model is too poor. Class implementing Row should have one more column (ID) which can(or not) be invisible.

My English isn't too fluent, cannot teach relational database and Swing in SO post.

The second, small snippet of code doesn't show your application conception. Read in google about custom JTable model.

Very basic example: http://www.java2s.com/Code/Java/Swing-JFC/TablewithacustomTableModel.htm

Yeah I know it has logical problem, the solution I am looking for is if it is possible to write the statement in the other form. The problem I face is that I am unsure of how I should get the oldMaterial as the tablemodellistener I use in Java only records the column and the row indices which were edited. Either way, thanks for showing how the correct sequence looks like, I will sure use it in my code from now on! — Karolizzz, Nov 06 '16 at 10:21

score 0 · Answer 2 · edited May 23 '17 at 12:17

0

Okay so it seems that I found the solution to the problem

JTable: Detect cell data change

However, this requires implementing another class into the project and using it. The answer for the SQL statement still stays the same.

edited May 23 '17 at 12:17

Community

1
1

answered Nov 06 '16 at 10:38

Karolizzz

119
1
12

Updating a single row database in Java

2 Answers2