CODEWITHSUNDEEP
javaeclipsesqlitejdbc
user3932611
user3932611

Reputation:

Java JDBC SQLite update entry not working

I have created a database and a set of table using JDBC and SQLite in Eclipse.

I am trying to update the table using the following code:

public static void update()
      {
        Connection c = null;
        Statement stmt = null;
        try {
          Class.forName("org.sqlite.JDBC");
          c = DriverManager.getConnection("jdbc:sqlite:WalkerTechCars.db");
          c.setAutoCommit(false);
          System.out.println("Opened database successfully");

          stmt = c.createStatement();
          String sql = "UPDATE CUSTOMERS2 set ADDRESS = Byron WHERE PHONE=2;";
          stmt.executeUpdate(sql);
          c.commit();

          ResultSet rs = stmt.executeQuery( "SELECT * FROM CUSTOMERS;" );
          while ( rs.next() ) {
             int id = rs.getInt("id");
             String  name = rs.getString("name");
             int age  = rs.getInt("age");
             String  address = rs.getString("address");
             float salary = rs.getFloat("salary");
             System.out.println( "ID = " + id );
             System.out.println( "NAME = " + name );
             System.out.println( "AGE = " + age );
             System.out.println( "ADDRESS = " + address );
             System.out.println( "SALARY = " + salary );
             System.out.println();
          }
          rs.close();
          stmt.close();
          c.close();
        } catch ( Exception e ) {
          System.err.println( e.getClass().getName() + ": " + e.getMessage() );
          System.exit(0);
        }
        System.out.println("Update Completed successfully");
      }

As far as I can understand the SQL syntax I m using says:

update the table customers2 and set the address as Byron where phone =2

But am getting the following error:

java.sql.SQLException: no such column: Byron

Which tells me it is interpreting my request as asking to alter the column named Byron, which is not what I believe the code to be saying?

Upvotes: 0

Views: 323

Answers (1)

Juned Ahsan
Juned Ahsan

Reputation: 68715

As per SQL syntax, varchar values should be used with single quotes so update this:

      String sql = "UPDATE CUSTOMERS2 set ADDRESS = Byron WHERE PHONE=2;";

to

      String sql = "UPDATE CUSTOMERS2 set ADDRESS = 'Byron' WHERE PHONE=2;";

If you don't use the single quotes, SQL assumes that you are trying to set the value using a different column and hence it throws the error:

java.sql.SQLException: no such column: Byron

ADVICE: Use PreparedStatment for dynamic parameter queries

PreparedStatement stmt;
String sql = "UPDATE CUSTOMERS2 set ADDRESS=? WHERE PHONE=?";
stmt = c.prepareStatement(sql);
stmt.setString(1, "Byron");
stmt.setInt(2, 2);
stmt.executeUpdate();

Upvotes: 2

Related Questions