java - 如何使用Java为MySQL创建新用户？

Question

我想从 NetBeans 为 MySQL 数据库创建一个新用户，但我不知道它的核心代码。

我已经尝试过:

try {

    String query = "CREATE USER = '?' @'localhost' IDENTIFIED BY = '?'; ";

    con = DriverManager.getConnection(url, uname, pwd);
    PreparedStatement pstmnt = con.prepareStatement(query);

    pstmnt.setString(1, newUser[0]);
    pstmnt.setString(2, newUser[3]);

    pstmnt.execute();
    pstmnt.close();

} catch (SQLException ex) {
    ex.printStackTrace();
}

输出为:

java.sql.SQLException: 
Parameter index out of range (1 > number of parameters, which is 0).

我知道在 MySQL 中您可以使用以下命令创建新用户:

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

Answer 1

蒂姆·索罗 How To have Dynamic SQL in MySQL Stored Procedure声明在5.0.13之后，在存储过程中，您可以使用

dynamic sql 

奥梅什 MySQL CREATE USER with a variable?使用动态SQL创建新用户并授予权限

我的混合物使用

stored procedures + dynamic sql

生成以下用于创建新用户的答案:

CREATE DEFINER=`root`@`localhost` PROCEDURE `new_procedure`(IN username VARCHAR(250), IN pwd VARCHAR (100))
BEGIN

SET @s = CONCAT('CREATE USER "',username,'"@"localhost" IDENTIFIED BY "',pwd,'"');
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END