英文:
Batch select and insert in Java
问题
private void btnBatchPayrollActionPerformed(java.awt.event.ActionEvent evt) {
String url = "jdbc:mysql://localhost:3306/";
String dbName = "arrowsdb";
String driver = "com.mysql.jdbc.Driver";
String userName = "root";
String password = "";
try {
Object newInstance = Class.forName(driver).newInstance();
Connection conn = DriverManager.getConnection(url + dbName, userName, password);
Statement st = conn.createStatement();
// int rows = st.executeUpdate("INSERT INTO employeeinfo SELECT * FROM employeeinfo");
// int rows = st.executeUpdate("INSERT INTO payroll(employeeid,fname,basic,housing,transport,medical) SELECT * FROM employeeinfo('EmployeeId','Name,Basic','Housing','Transport','Medical')");
// INSERT INTO payroll (employeeid,fname,basic,housing,transport,medical) SELECT EmployeeId,Name,Basic,Housing,Transport,Medical FROM employeeinfo;
int rows = st.executeUpdate("INSERT INTO payroll (employeeid,fname,basic,housing,transport,medical) SELECT EmployeeId,Name,Basic,Housing,Transport,Medical FROM employeeinfo;");
if (rows == 0) {
System.out.println("Don't add any row!");
} else {
System.out.println(rows + " row(s)affected.");
conn.close();
}
} catch (ClassNotFoundException | SQLException e) {
System.out.println(e);
} catch (InstantiationException | IllegalAccessException ex) {
Logger.getLogger(BatchPayroll.class.getName()).log(Level.SEVERE, null, ex);
}
}
英文:
Hellos,
Can anyone help me look at this code; its generating an error."java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1"
//Code
private void btnBatchPayrollActionPerformed(java.awt.event.ActionEvent evt) {
String url = "jdbc:mysql://localhost:3306/";
String dbName = "arrowsdb";
String driver = "com.mysql.jdbc.Driver";
String userName = "root";
String password = "";
try{
Object newInstance = Class.forName(driver).newInstance();
Connection conn = DriverManager.getConnection(url + dbName, userName, password);
Statement st = conn.createStatement();
// int rows = st.executeUpdate("INSERT INTO employeeinfo SELECT * FROM employeeinfo");
//int rows = st.executeUpdate("INSERT INTO payroll(employeeid,fname,basic,housing,transport,medical) SELECT * FROM employeeinfo('EmployeeId','Name,Basic','Housing','Transport','Medical')");
//INSERT INTO payroll (employeeid,fname,basic,housing,transport,medical) SELECT EmployeeId,Name,Basic,Housing,Transport,Medical FROM employeeinfo;
int rows = st.executeUpdate("INSERT INTO payroll (employeeid,fname,basic,housing,transport,medical) SELECT EmployeeId,Name,Basic,Housing,Transport,Medical FROM employeeinfo;)");
if (rows == 0) {
System.out.println("Don't add any row!");
} else {
System.out.println(rows + " row(s)affected.");
conn.close();
}
}
catch(ClassNotFoundException | SQLException e)
{
System.out.println(e);
} catch (InstantiationException | IllegalAccessException ex) {
Logger.getLogger(BatchPayroll.class.getName()).log(Level.SEVERE, null, ex);
}
}
Thx
fm
答案1
得分: 0
你的SQL语句中有一个多余的右括号。我还没有测试过这个，因为我没有这些SQL表，但我认为这是正确的。我使用了MySQL 8的文档来验证格式。
另外，对于代码和SQL的格式化也有帮助，这样可以更容易地发现诸如不平衡的括号之类的错误。
private void btnBatchPayrollActionPerformed(java.awt.event.ActionEvent evt) {
String url = "jdbc:mysql://localhost:3306/";
String dbName = "arrowsdb";
String driver = "com.mysql.jdbc.Driver";
String userName = "root";
String password = "";
try {
Object newInstance = Class.forName(driver).newInstance();
Connection conn = DriverManager.getConnection(url + dbName, userName, password);
Statement st = conn.createStatement();
int rows = st.executeUpdate(
"INSERT INTO payroll " +
" (employeeid, fname, basic, housing, transport, medical) " +
"SELECT EmployeeId, Name, Basic, Housing, Transport, Medical " +
"FROM employeeinfo;");
if (rows == 0) {
System.out.println("没有添加任何行！");
} else {
System.out.println(rows + " 行受影响。");
conn.close();
}
} catch (ClassNotFoundException | SQLException e) {
System.out.println(e);
} catch (InstantiationException | IllegalAccessException ex) {
Logger.getLogger(BatchPayroll.class.getName()).log(Level.SEVERE, null, ex);
}
}
英文:
You had an extra right parenthesis in your SQL. I haven't tested this, because I don't have these SQL tables, but I think this is correct. I used the MySQL 8 documentation to verify the format.
It also helps to format your code and SQL, so it's easier to spot errors like unbalanced parenthesis.
private void btnBatchPayrollActionPerformed(java.awt.event.ActionEvent evt) {
String url = "jdbc:mysql://localhost:3306/";
String dbName = "arrowsdb";
String driver = "com.mysql.jdbc.Driver";
String userName = "root";
String password = "";
try {
Object newInstance = Class.forName(driver).newInstance();
Connection conn = DriverManager.getConnection(url + dbName, userName, password);
Statement st = conn.createStatement();
// int rows = st.executeUpdate("INSERT INTO employeeinfo SELECT * FROM
// employeeinfo");
// int rows = st.executeUpdate("INSERT INTO
// payroll(employeeid,fname,basic,housing,transport,medical) SELECT * FROM
// employeeinfo('EmployeeId','Name,Basic','Housing','Transport','Medical')");
// INSERT INTO payroll (employeeid,fname,basic,housing,transport,medical) SELECT
// EmployeeId,Name,Basic,Housing,Transport,Medical FROM employeeinfo;
int rows = st.executeUpdate(
"INSERT INTO payroll " +
" (employeeid, fname, basic, housing, transport, medical) " +
"SELECT EmployeeId, Name, Basic, Housing, Transport, Medical " +
"FROM employeeinfo; ");
if (rows == 0) {
System.out.println("Don't add any row!");
} else {
System.out.println(rows + " row(s)affected.");
conn.close();
}
} catch (ClassNotFoundException | SQLException e) {
System.out.println(e);
} catch (InstantiationException | IllegalAccessException ex) {
Logger.getLogger(BatchPayroll.class.getName()).log(Level.SEVERE, null, ex);
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论