Error SQL: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax

Question

I've been trying to do a JDBC connection with MySQL using the preparedStatement.

Here is my code so far:

package uni.jdbc.preparedStatement;
import java.sql.*;

public class Main {

    public static void main(String[] args) {

        try {

            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/banco_Teste","root","password");

            //ADD
            String resultSet1 = "INSERT INTO Clientes(nome) VALUES(?)";
            String resultSet2 = "INSERT INTO Clientes(nome) VALUES(?)";

            //UPDATE
            String resultSet3 = "UPDATE Clientes SET nome = ? WHERE id = ?";
            String resultSet4 = "UPDATE Clientes SET nome = ? WHERE id = ?";

            //DELETE
            String resultSet5 = "DELETE FROM Clientes WHERE id = ?";
            String resultSet6 = "DELETE FROM Clientes WHERE id = ?";

            //SELECT
            String select = "Select * FROM Clientes";

            try (PreparedStatement pS = conn.prepareStatement(resultSet1 + resultSet2 + resultSet3 + resultSet4 + resultSet5 + resultSet6 + select)){

                //INSERT
                pS.setString(1, "João");
                pS.setString(2, "Maria");
                pS.execute(resultSet1 + resultSet2);

                //UPDATE
                pS.setString(1, "John");
                pS.setString(2, "Marya");
                pS.execute(resultSet3 + resultSet4);

                //DELETE
                pS.setString(1, "John");
                pS.setString(2, "Marya");
                pS.execute(resultSet5 + resultSet6 );

                //SELECT
                pS.execute(select);

            }catch (SQLException e){

                e.printStackTrace();

            }

        }catch(SQLException | ClassNotFoundException e){

            e.printStackTrace();

            System.out.println("Conexão falhou!");

        }
    }
}

Here is the query:

create database banco_Teste

use banco_Teste

CREATE TABLE Clientes(

    id_cliente INTEGER PRIMARY KEY AUTO_INCREMENT,
    nome varchar (100)

)

The problem is: as I run, the following error is returned:

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 '?)INSERT INTO Clientes(nome) VALUES(?)' at line 1 at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:768) at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:653) at uni.jdbc.preparedStatement.Main.main(Main.java:46)

Could it be a version problem? The connector version is 8.0.27.

You need to use `pS.execute()`. Also, you need to ensure you're actually executing/preparing a valid statement, because your current statement is not valid. — Mark Rotteveel, Nov 05 '21 at 11:58

score 1 · Accepted Answer · answered Nov 05 '21 at 03:46

resultSet1 + resultSet2 + resultSet3 + resultSet4 + resultSet5 + resultSet6 + select

will give you an error because a the end, the result of your query look like this :

INSERT INTO Clientes(nome) VALUES(?)INSERT INTO Clientes(nome) VALUES(?)UPDATE Clientes SET nome = ? WHERE id = ?UPDATE Clientes SET nome = ? WHERE id = ?...

what is syntaxically incorrect as stated in your exception. What you have to do is to add ; at the end of your queries.

Your queries should look like this :

    //ADD
    String resultSet1 = "INSERT INTO Clientes(nome) VALUES(?);";
    String resultSet2 = "INSERT INTO Clientes(nome) VALUES(?);";

    //UPDATE
    String resultSet3 = "UPDATE Clientes SET nome = ? WHERE id = ?;";
    String resultSet4 = "UPDATE Clientes SET nome = ? WHERE id = ?;";

    //DELETE
    String resultSet5 = "DELETE FROM Clientes WHERE id = ?;";
    String resultSet6 = "DELETE FROM Clientes WHERE id = ?;";

    //SELECT
    String select = "Select * FROM Clientes;";

This link can help you for more details.

I am a bit confused about this call :

pS.execute(resultSet1 + resultSet2);

as per the Java Doc, there is no such method.

Error SQL: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax

1 Answers1