如何使用变量在 Java 中执行 SQL 语句 [英] How to execute a SQL statement in Java with variables

问题描述

我有以下 mysql 语句来从正在运行的数据库中删除记录.

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

SET @email = '[e-mail]';
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;

我想使用 jdbc mysql 连接在 Java 中运行相同的查询.我已经尝试了以下

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);}
    }

运行时出现此错误

java.sql.SQLSyntaxErrorException: 你的 SQL 有错误句法;检查与您的 MariaDB 服务器相对应的手册'SET @userID = (SELECT idFROM user WHERE email = @email);DELETE FROM user_role_' 在第 2 行

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

推荐答案

如果 MySQL 查询在 MySQL 控制台中正确运行，那么在使用 jdbcjdbc 处理时，相同的查询没有理由显示语法错误代码>.您在使用 java 实现查询以及 java 如何处理它时犯了错误.

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 用于尝试从查询中获取结果集.但在这种情况下，您不是要求结果集.这就是为什么不需要 Resultset 而只有 stmt.execute 应该可以工作.

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.

这篇关于如何使用变量在 Java 中执行 SQL 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！