java - 一次性向多个数据库和表中插入数据

Question

我正在尝试使用准备好的语句插入到多个数据库和表中。是否可以插入到多个数据库和表中？这是我试过的代码，你能告诉我下一步应该做什么吗，因为我被困在这部分了。正在从文本框中捕获数据。

Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost   /mirroreddatabase","root","");
Connection con1 = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/breastcancerdatabase","root","");
Connection con2 = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/genedetailsdatabase","root","");
 String sql= "Insert into omimmirrored(id,genesymbol,sequencelinks)values (?,?,?) ";
 PreparedStatement pst=(PreparedStatement) con.prepareStatement(sql);

 String sql2 = "Insert into breastcancer_genename(id,symbol,geneName)values (?,?,?) ";
 PreparedStatement pst1=(PreparedStatement) con.prepareStatement(sql2);

 String sql3 = "Insert into genedetails(id,symbol,GeneDetailsLinks)values (?,?,?) ";
 PreparedStatement pst2=(PreparedStatement) con.prepareStatement(sql3);

 String sql4 = "Insert into breastcancer_synonym(id,symbol,geneName)values (?,?,?) ";
 PreparedStatement pst3=(PreparedStatement) con.prepareStatement(sql4);

Answer 1

您可以只使用一个连接并授予该连接的用户对所有数据库的权限，这样您就可以在所有数据库中执行插入操作。

要在所有数据库上授予 root 权限，您可以执行以下操作:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

您只需要将数据库作为前缀(即，您必须将 insert into breastcancerdatabase.breastcancer_genename 而不是 insert into breastcancer_genename)。

Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost   /mirroreddatabase","root","");

 String sql= "Insert into omimmirrored(id,genesymbol,sequencelinks)values (?,?,?) ";
 PreparedStatement pst=(PreparedStatement) con.prepareStatement(sql);

 String sql2 = "Insert into breastcancerdatabase.breastcancer_genename(id,symbol,geneName)values (?,?,?) ";
 PreparedStatement pst1=(PreparedStatement) con.prepareStatement(sql2);

 String sql3 = "Insert into genedetailsdatabase.genedetails(id,symbol,GeneDetailsLinks)values (?,?,?) ";
 PreparedStatement pst2=(PreparedStatement) con.prepareStatement(sql3);

 String sql4 = "Insert into breastcancerdatabase.breastcancer_synonym(id,symbol,geneName)values (?,?,?) ";
 PreparedStatement pst3=(PreparedStatement) con.prepareStatement(sql4);

关于 GRANT 语句(来自 MySQL 文档):

The GRANT statement grants privileges to MySQL user accounts. GRANT also serves to specify other account characteristics such as use of secure connections and limits on access to server resources. To use GRANT, you must have the GRANT OPTION privilege, and you must have the privileges that you are granting.

更多信息:http://dev.mysql.com/doc/refman/5.1/en/grant.html