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 = 'delete@mailinator.com';
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

PreparedStatement will simplify the same query. It has performance benefit also as the query is compiled once and executed multiple times. — Vijay, Feb 08 '18 at 17:10

score 3 · Answer 1 · answered Feb 08 '18 at 17:24

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"

score 1 · Answer 2 · answered Feb 08 '18 at 17:22

I suspect the issue is due to the allowMultiQueries flag in MariaDB. This defaults to false, meaning each query is essentially run in a vacuum and your SET @userID = (SELECT id FROM user WHERE email = @email); query doesn't know what @email is. To resolve this with you current code, set the database allowMultiQueries=true.

score 1 · Accepted Answer · answered Feb 08 '18 at 18:19

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.

How to execute a SQL statement in Java with variables

3 Answers3

Linked