MySQLSyntaxErrorException when trying to execute PreparedStatement [duplicate]

Question

I tried to insert a userId (int) and a userName(String) using the PreparedStatement using JDBC using the following method:

public boolean saveUser(int userId, String userName){
    boolean saveStatus = false;
    try {
        connection.setAutoCommit(true);
        String sql = "insert into testtable values(?,?)";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1, userId);
        statement.setString(2, userName);
        saveStatus  = statement.execute(sql);
    } catch (SQLException e) {
        e.printStackTrace();
    }finally{
        Connector.closeConnections();
    }
    return saveStatus;
}

I get the following stacktrace:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 1

What am I doing wrong?

Answer 1

Try this this should work:

    try {
        connection.setAutoCommit(true);
        String sql = "insert into testtable values(?,?)";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setInt(1, userId);
        statement.setString(2, userName);
        saveStatus  = statement.execute();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return saveStatus;
}

PreparedStatement is precompiled statement. you don't have to supply the sql string while executing.

Answer 2

There is a fundamental mistake in your code.

Once the PreparedStatement is created, we need to call preparedStatement.executeUpdate(); with out passing SQL again

Here is the full code for your question.

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;


public class SampleMysql {

    public static Connection getConnection() throws SQLException {
        Driver drv = new com.mysql.jdbc.Driver();
        DriverManager.registerDriver(drv);
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/sample","sample", "sample");
        return con;
    }

    public static int saveUser(int userId, String userName){
        int saveStatus = 0;
        Connection connection = null;
        try {
            connection = getConnection();
            connection.setAutoCommit(true);
            String sql = "INSERT INTO testtable VALUES(?,?)";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, userId);
            preparedStatement.setString(2, userName);
            saveStatus  = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {

                    e.printStackTrace();
                }
            }
        }
        return saveStatus;
    }

    public static void main(String[] a) {
        System.out.println(saveUser(1, "sample"));
    }
}

The table script is given below.

CREATE TABLE testtable(user_ID INT(10), user_name VARCHAR(10));