java - JDBC Error when trying a parametric update

Question

I'm trying to update the record in my MySql database using JDBC. Here is the method:

public void updateGareCorse(CorrePer c) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = DBConnectionPool.getConnection();
            
            String sql = "update corre_per set gare_corse = ?
"
                    + "   where codice_pilota = ? and anno = ?";
            ps = con.prepareStatement(sql);
            ps.setString(1, c.getGare_corse());
            ps.setString(2, c.getCodice());
            ps.setInt(3, c.getAnno());
            
            System.out.println("QUERY:
UPDATE corre_per SET gare_corse = " + c.getGare_corse()+" WHERE anno = "+ c.getAnno() +" AND codice_pilota = " + c.getCodice()+")");

            int result = ps.executeUpdate(sql);

            if (result > 0) {
                System.out.println("Update OK");

            } else {
                System.out.println("Update NOT OK");
            }
            con.commit();
        } catch (SQLException s) {
            System.err.println(s.getMessage());
            Utility.printSQLException(s);
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (ps != null)
                    ps.close();
                DBConnectionPool.releaseConnection(con);
            } catch (SQLException s) {
                System.err.println(s.getMessage());
                Utility.printSQLException(s);
            }
        }
    }

CorrePer is a Java class that represents my CorrePer table and has variables that represent my CorrePer attributes and their getter and setter method. Now, when I execute this method, Eclipse gives this error:

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 codice_pilota = ? and anno = ?' at line 1

Why the method doesn't work? Any help is much appreciated.

UPDATE: I tried to pass only one parameter at a time, with the others not being parametric, but already written in the query, like this:

String sql = "update corre_per set gare_corse = "1-"
"
                    + "   where codice_pilota = "TSU" and anno = ?";
            ps = con.prepareStatement(sql);
            //ps.setString(1, c.getGare_corse());
            //ps.setString(1, c.getCodice());
            ps.setInt(1, c.getAnno());

Now it gives error only on the '?' at the end:

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 '?' at line 2

So looks like there is a problem with the parameters association, but I'm not able to figure out it.

question from:https://stackoverflow.com/questions/65672192/jdbc-error-when-trying-a-parametric-update

Answer 1

Here is your problem

int result = ps.executeUpdate(sql);

Just use (as you already set your parameter values)

int result = ps.executeUpdate();

Otherwise actual call is delegated to java.sql.Statement.executeUpdate(String) which perform SQL udpate as-is (without interpolation of arguments, so ? tokens in your parametrized query are not replaced with supplied values)