I have a problem with the prepared statement in Java - Mysql 8.0

Question

I was working on a prepared statement query , but there's an error with the query syntax (as it shows in my console ) :

java.sql.SQLSyntaxErrorException: 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

I don't know whats wrong with it . note : the above query is working (tested) on mysql , I've just changed the VALUES in Java .

connection = DataSource.getConnection();

String insertGame = " insert into games (game_name , game_genre , game_year ) values ( ? , ? , ? ) ";
statment = connection.prepareStatement(insertGame);
statment.setString(1, game.getName());
statment.setString(2, game.getGenre());
statment.setInt(3, game.getYear());
statment.executeUpdate(insertGame);

Answer 1

The error is that you are using the parent class's Statement.executeUpdate(String) instead of PreparedStatement.executeUpdate().

The semicolon as statement separator does not belong in a single statement offered to the JDBC.

    String insertGame = "insert into games (game_name, game_genre, game_year) "
        + "values (?, ?, ?)";
    try (PreparedStatement statment = connection.prepareStatement(insertGame)) {
        statment.setString(1, game.getName());
        statment.setString(2, game.getGenre());
        statment.setInt(3, game.getYear());
        statment.executeUpdate();
    }

For the rest closing the statement, like with a try-with-resources, is important. The above will also close statment when an SQLException is thrown for say duplicate game names.