CODEWITHSUNDEEP
javadatabasesqlitejavafx
Aleksandar Danevski
Aleksandar Danevski

Reputation: 25

JavaFX update a Sqlite table

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)

Upvotes: 0

Views: 3605

Answers (2)

James_D
James_D

Reputation: 209684

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).

Upvotes: 1

Aleksandar Danevski
Aleksandar Danevski

Reputation: 25

@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();

Upvotes: 0

Related Questions