JDBC error

redman85

Guys having a problem with an assignment I'm doing. I have to connect to a database using JDBC and perform CRUD operations on it. I can get most of it working but I'm getting an DBException with the update method.

Heres the exception :

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE id = 1' at line 1

Heres the update method :

private static final String TABLE_NAME = "shop";
private static final String COLUMN_ID = "id";
private static final String COLUMN_NAME = "name";
private static final String COLUMN_ADDRESS = "address";
private static final String COLUMN_TURNOVER = "turnover";

public boolean updateShop(Shop s) throws SQLException{
    
        String query;
        PreparedStatement stmt;
        int numRowsAffected;
        
        query = "UPDATE " + TABLE_NAME + " SET " + 
                COLUMN_NAME+ "      = ?, " +
                COLUMN_ADDRESS + "  = ?, " +
                COLUMN_TURNOVER + " = ?, " +
                "WHERE " + COLUMN_ID + " = ?";
        
        stmt = mConnection.prepareStatement(query);
        
        stmt.setString(1, s.getName());
        stmt.setString(2, s.getAddress());
        stmt.setDouble(3, s.getTurnover());
        stmt.setInt(4, s.getId());
        
        numRowsAffected = stmt.executeUpdate();
        
        return (numRowsAffected != 1);
    }

If someone could point me in the right direction I would really appreciate it.

I'm on a mac using Netbeans 7.4 and MySQL 5.5.33

Thanks

Seridisand

There is a hint the sql error

'WHERE id = 1'

Make sure you have a column in your db called id that stores a numerical data type

oscarBravo

Actually, the problem is before the WHERE clause - you have a superfluous comma at the end of your SET statement.

Talisman

oscarBravo wrote: »

Actually, the problem is before the WHERE clause - you have a superfluous comma at the end of your SET statement.

The nasty copy and paste side effect.

@redman85: For your own sanity you should print the SQL query to the console or log for debugging purposes.

System.out.println(query);

Seeing the actual query that is generated will help you avoid making simple mistakes in the future. You can remove the additional code before you submit the assignment

jester77

It might not have been part of your assignment but a good ORM lib would help here. Writing SQL statements like that is prone to errors, especially once they get a bit more complicated.

Sparks

Bad idea to use ORM libraries when teaching the basics of how you put together SQL commands. Learn to walk first, then proceed to wingsuit-aided basejumping.

smcelhinney

Sparks wrote: »

Learn to walk first, then proceed to wingsuit-aided basejumping.

Love this analogy, duly robbed.

amen

Writing SQL statements like that is prone to errors, especially once they get a bit more complicated.

only because you are not paying attention!

Writing SQL Statements is no different from writing other code especially complex calculations. Just take care and be careful.

GreeBo

If you are writing bare SQL then run it in some DB editor first, before you wrap the Java around it; that way if it suddenly stops working you know if its the SQL or the Java implementation and it makes it much easier to ask help from someone else.

Working baby steps, thats the Holy Grail.