JavaFX update a Sqlite table

Question

I am currently working on a program the function of which is to store my passwords, and this is why I am using an SQL database called Users. This database contains tables for all the users which will be using the program. Those tables have four columns:

SiteName, Username, Password, AdditionalInfo

I am having a problem updating a specific row. This is my the code I get an error with:

public static void editPassword(String user, String siteEdited, String site, String usernamej, String password, String info){
     try{
         System.out.println(usernamej);
         Class.forName("org.sqlite.JDBC");
         c = DriverManager.getConnection("jdbc:sqlite:res/Users");
         c.setAutoCommit(false);
         stmt = c.createStatement();
         String update = "UPDATE " + user + " set Username = " + usernamej + " where SiteName = " + siteEdited;
         stmt.executeUpdate(update);
         stmt.close();
         c.close();
     }catch(Exception e){
         System.err.print( e.getClass().getName() + ": " + e.getMessage());
     }
 }

It is in a class made specifically for dealing with the sql database and it gets the following error when I try to change the username to 'test':

java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (no such column: test)

Could you print out the 'update' string before you doe the 'executeUpdate()'? — clartaq, Jul 30 '15 at 13:12

James_D · Accepted Answer · 2015-07-31T17:22:43.820

Assuming the value you pass in for user is the name of the table, your update string is going to look like

UPDATE usertable SET Username = test where SiteName = siteEditedValue

You need to quote the string values:

UPDATE usertable SET Username = 'test' where SiteName = 'siteEditedValue'

The quick and dirty way is:

String update = "UPDATE " + user + " set Username = '" + usernamej + "' where SiteName = '" + siteEdited + "'";

However, it's much (much, much) better to use a PreparedStatement in this case:

public static void editPassword(String user, String siteEdited, String site, String usernamej, String password, String info){
     try{
         System.out.println(usernamej);
         Class.forName("org.sqlite.JDBC");
         c = DriverManager.getConnection("jdbc:sqlite:res/Users");
         stmt = c.prepareStatement("UPDATE " + user + " SET Username = ? Where SiteName = ?");
         stmt.setString(1, usernamej);
         stmt.setString(2, siteEdited);
         stmt.executeUpdate();
         stmt.close();
         c.close();
     }catch(Exception e){
         System.err.print( e.getClass().getName() + ": " + e.getMessage());
     }
 }

This code assumes the type of stmt is PreparedStatement, not just Statement.

As well as taking care of quoting the values for you, this will escape any sql for you, preventing the possibility of SQL-injection attacks (while these are far less of an issue in a desktop application that a web application, it's still a good habit to get into).

Ok sorry for asking another maybe quite stupid question but it your code in the line : stmt.executeUpdate(update); the update is never declared.. — Aleksandar Danevski, Jul 30 '15 at 18:56
@AleksandarDanevski its probably a typo ... the method `executeUpdate` in a `PreparedStatement` doesnt have a argument. so just call `stmt.executeUpdate()` — griFlo, Jul 31 '15 at 06:38
@James I tried it both ways and I still get an error: java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (Connection is closed) — Aleksandar Danevski, Jul 31 '15 at 07:53
@AleksandarDanevski take a look at http://stackoverflow.com/questions/14998695/java-missing-database-error maybe you find a solution here — griFlo, Jul 31 '15 at 07:56
@griFlo yes: thanks for pointing out the typo. Fixed in the answer now. — James_D, Jul 31 '15 at 17:23

score 0 · Answer 2 · answered Jul 31 '15 at 08:09

@griFlo I got it running with this code:

 public static void editPassword(String user, String siteEdited, String site, String usernamej, String password, String info){
         try{
             System.out.println(usernamej);
             Class.forName("org.sqlite.JDBC");
             c = DriverManager.getConnection("jdbc:sqlite:res/Users");
             c.setAutoCommit(false);
             PreparedStatement stmt = c.prepareStatement("UPDATE " + user + " SET Username = ? Where SiteName = ?");
             stmt.setString(1, usernamej);
             stmt.setString(2, siteEdited);
             stmt.executeUpdate(update);
             c.commit();

         stmt.close();
         c.close();
     }catch(Exception e){
         System.err.print( e.getClass().getName() + ": " + e.getMessage());
     }
     }

I had forgotten to put c.commit();

JavaFX update a Sqlite table

2 Answers2