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

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

Jerin D Joy · Accepted Answer · 2018-02-08 17:24:51Z

3

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"

answered Feb 8, 2018 at 17:24

Jerin D Joy

7886 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

UkFLSUI · Accepted Answer · 2018-02-08 18:19:23Z

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.

jshrimp29 · Accepted Answer · 2018-02-08 17:22:14Z

1

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.

answered Feb 8, 2018 at 17:22

jshrimp29

6537 silver badges9 bronze badges

Collectives™ on Stack Overflow

How to execute a SQL statement in Java with variables

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related