JDBC SQLite PreparedStatement update and delete

Question

I wanted to update/delete a user specified field for a given table set up in sqlite3 via JDBC. I have used PreparedStatements for this purpose.

Now the case 3: delete sql query has no syntax error but it doesn't delete the row.

And the case 2: update sql query has syntax error - I want it to accept a field, update value and the condition.

What am I doing wrong here?

[Edit] Here's the code segment: http://pastebin.com/8naiXZ2v

String temp;
switch (n){
    case 1: // Insert
        System.out.println("Okay!");
        PreparedStatement inPst = con.prepareStatement("insert into " + dob.tbName + " (roll, branch, fname, lname, cgpa)" + " values(?,?,?,?,?)");
        System.out.print("Roll: ");
        inPst.setString(1, in.nextLine());
        System.out.print("Branch: ");
        inPst.setString(2, in.nextLine());
        System.out.print("Name: ");
        inPst.setString(3, in.nextLine());
        System.out.print("Surname: ");
        inPst.setString(4, in.nextLine());
        System.out.print("CGPA: ");
        inPst.setString(5, in.nextLine());

        inPst.executeUpdate();
        break;

    case 2: // Update
        System.out.println("Okay!");
        System.out.println("Fields: roll\tbranch\tfname\tlname\tcgpa");
        PreparedStatement upPst = con.prepareStatement("update " + dob.tbName + " set ? = ? where ? ;");
        System.out.print("Enter field name: ");
        temp = in.nextLine();
        upPst.setString(1, temp);
        System.out.print("Enter field value: ");
        temp = in.nextLine();
        temp = "'" + temp + "'";
        upPst.setString(2, temp);
        System.out.print("Enter condition: ");
        temp = in.nextLine();
        upPst.setString(3, temp);

        upPst.executeUpdate();
        break;

    case 3: //Delete
        System.out.println("Okay!");
        System.out.println("Fields: roll\tbranch\tfname\tlname\tcgpa");
        PreparedStatement delPst = con.prepareStatement("delete from " + dob.tbName + " where ? = ? ;");
        System.out.print("Enter key field: ");
        temp = in.nextLine();
        delPst.setString(1, temp);
        System.out.print("Enter key value: ");
        temp = in.nextLine();
        temp = "'" + temp + "'";
        delPst.setString(2, temp);

        delPst.executeUpdate();
        System.out.println("Deleted!");

        break;

You can't parametrize conditions like that. A parameter can only be a value, not an object name, and certainly not a predicate. It might also be a good idea to include connection creation etc. Did you for example disable autoCommit? — Mark Rotteveel
Well then is there a way to form sql queries for user specified fields to update/delete the values in them? — sprkv5

Gord Thompson Gord Thompson · Accepted Answer · 2014-11-15T11:38:39

Your intentions are good. It is always preferable to use parameters instead of dynamic ("glued together") SQL statements. However, parameters can only be used to represent the values in an SQL statement. So, this will NOT work

String tableName = "Clients";
String columnToUpdate = "LastName";
String newValue = "Thompson";
String columnToSearch = "ID";
int idToMatch = 1;
String sql = String.format(
        "UPDATE \"%s\" SET ? = ? WHERE ? = ?", 
        tableName.replaceAll("\"", "\"\""));
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setString(1, columnToUpdate);
    ps.setString(2, newValue);
    ps.setString(3, columnToSearch);
    ps.setInt(4, idToMatch);
    ps.executeUpdate();
}

Just like we did for the table name, we have to put the column names inside the PreparedStatement text and just supply the values as parameters:

String tableName = "Clients";
String columnToUpdate = "LastName";
String newValue = "Thompson";
String columnToSearch = "ID";
int idToMatch = 1;
String sql = String.format(
        "UPDATE \"%s\" SET \"%s\" = ? WHERE \"%s\" = ?", 
        tableName.replaceAll("\"", "\"\""),
        columnToUpdate.replaceAll("\"", "\"\""),
        columnToSearch.replaceAll("\"", "\"\""));
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setString(1, newValue);
    ps.setInt(2, idToMatch);
    ps.executeUpdate();
}

JDBC SQLite PreparedStatement update and delete

1 Answers