JDBC Exception: MySQLSyntaxError with Valid SQL Statement

In this article, we delve into an issue encountered when using JDBC to insert data into a MySQL database. We received a MySQLSyntaxError Exception despite executing a valid INSERT statement in MySQL Workbench.

To investigate the underlying cause, let's analyze the code:

public static boolean aggiungiElem(String nome, GrafoGenerico g){
    if(connessioneAperta()){
        try{
            String sqlCommandUser = "SELECT USER()";
            String sqlCommandInserim = "INSERT INTO salvataggi VALUES ( ? , ? , DEFAULT , NULL );";
            PreparedStatement sUser = conn.prepareStatement(sqlCommandUser);
            ... // Execute user query
            PreparedStatement sInserim = conn.prepareStatement(sqlCommandInserim);
            sInserim.setString(1, utente);
            sInserim.setString(2, nome);
            System.out.println(sInserim);
            sInserim.executeUpdate(sqlCommandInserim);
            ... // Close PreparedStatements and ResultSet
        }
        catch(SQLException e){
            e.printStackTrace();
            return false;
        }
    }
    else
        return false;
}

Examining the stack trace:

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 '? , ? , DEFAULT , NULL )' at line 1

This error indicates that there is a syntax error in the SQL statement. However, our INSERT statement appears to be correct.

Upon closer inspection, we notice that theexecuteUpdate() method takes a String argument SQLCommandInserim instead of the PreparedStatement sInserim. This is an oversight in the code.

Solution:

To resolve this issue, replace the following line:

sInserim.executeUpdate(sqlCommandInserim);

With:

sInserim.executeUpdate();

By invoking executeUpdate() on the PreparedStatement, JDBC will replace the placeholders (?) with the values set using the setString() method, preventing the syntax error.

Additional Considerations:

It is also recommended to use the finally block to close all database objects (PreparedStatements, Connections, Statements, and ResultSets) to prevent resource leaks in the event of exceptions.