How to execute a SQL statement in Java with variables

Question

I have the following mysql statement to delete records from a DB that is working.

SET @email = '[email protected]';
SET @userID = (SELECT id FROM USER WHERE email = @email);
DELETE FROM user_role_group WHERE user_id = @userID;
DELETE FROM user_client_setup WHERE user_id = @userID;
DELETE FROM USER WHERE id = @userID;

I want to run this same query in Java with a jdbc mysql connection. I have tried the following

public void deleteCoreUser(String email) {
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con=DriverManager.getConnection(
                    "jdbc:mysql://db.com:3306/core","username","password");
            Statement stmt=con.createStatement();
            ResultSet rs=stmt.executeQuery("SET @email = '"+email+"';\n" +
                    "SET @userID = (SELECT id FROM user WHERE email = @email);\n" +
                    "DELETE FROM user_role_group WHERE user_id = @userID;\n" +
                    "DELETE FROM user_client_setup WHERE user_id = @userID;\n" +
                    "DELETE FROM user WHERE id = @userID;");
            con.close();
            System.out.println("Deleting user "+email+" from the Core DB");
        }catch(Exception e){ System.out.println(e);}
    }

I am getting this error when running

java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SET @userID = (SELECT id FROM user WHERE email = @email); DELETE FROM user_role_' at line 2

Answer 1

Use execute() instead of executeQuery() since it returns multiple ResultSet. See answer (Statement.execute(sql) vs executeUpdate(sql) and executeQuery(sql)) and add ?allowMultiQueries=true to the database url "jdbc:mysql://db.com:3306/core?allowMultiQueries=true"

Answer 2

If the MySQL queries are running correctly in MySQL console, then there is no reason that same query will show syntax error when handling it with jdbc. You are making mistakes in implementing the queries with java and how java handles it.

public void deleteCoreUser(String email) {
    try {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:mysql://db.com:3306/core","username","password");
        Statement stmt = con.createStatement();
        String sql = "SET @email = '" + email + "'";
        stmt.execute(sql);
        sql = "SET @userID = (SELECT id FROM USER WHERE email = @email)";
        stmt.execute(sql);
        sql = "DELETE FROM user_role_group WHERE user_id = @userID";
        stmt.execute(sql);
        sql = "DELETE FROM user_client_setup WHERE user_id = @userID";
        stmt.execute(sql);
        sql = "DELETE FROM USER WHERE id = @userID";
        stmt.execute(sql);
        con.close();
        System.out.println("Deleting user " + email + " from the Core DB");
    } catch(Exception e){ System.out.println(e);}
}

stmt.executeQuery is used when you try to get resultset from the queries. But in this case you are not asking for resultset. That's why no Resultset is necessary and only stmt.execute should work.