无效的 JavaFx 应用中的 SQL 命令

huangapple
go评论97阅读模式
英文:

Invalid SQL Command in JavaFx application

问题

  1. public void importXLtoDB()  {
  2.     String sqlQuery = "INSERT INTO employee (dbSurname, dbName, dbBirthDate, dbEmail, dbMobile, dbPabx, " +
  3.             "dbDepartment, dbTeam, dbCurrentPosition, dbManager) VALUES (?,?,?,?,?,?,?,?,?,?)";
  4.     try {
  5.         connection = database.getConnection();
  6.         pst = connection.prepareStatement(sqlQuery);
  8.         FileChooser fileChooser = new FileChooser();
  9.         fileChooser.getExtensionFilters().addAll(
  10.                 new FileChooser.ExtensionFilter("Excel Files", "*.xlsx", "*.xls"));
  11.         FileInputStream fileIn = new FileInputStream(fileChooser.showOpenDialog(null));
  12.         XSSFWorkbook wb = new XSSFWorkbook(fileIn);
  13.         XSSFSheet sheet = wb.getSheetAt(0);
  14.         Row row;
  15.         for (int i=1; i<= sheet.getLastRowNum(); i++) {
  16.             row = sheet.getRow(i);
  17.             java.util.Date utilDate = row.getCell(2).getDateCellValue();
  18.             java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
  19.             pst.setString(1, row.getCell(0).getStringCellValue());
  20.             pst.setString(2, row.getCell(1).getStringCellValue());
  21.             pst.setDate(3, sqlDate);
  22.             pst.setString(4, row.getCell(3).getStringCellValue());
  23.             pst.setString(5, row.getCell(4).getStringCellValue());
  24.             pst.setString(6, row.getCell(5).getStringCellValue());
  25.             pst.setString(7, row.getCell(6).getStringCellValue());
  26.             pst.setString(8, row.getCell(7).getStringCellValue());
  27.             pst.setString(9, row.getCell(8).getStringCellValue());
  28.             pst.setString(10, row.getCell(9).getStringCellValue());
  30.             pst.execute();
  31.         }
  33.         NotificationType notificationType = NotificationType.INFORMATION;
  34.         TrayNotification tray = new TrayNotification();
  35.         tray.setTitle("Information Message");
  36.         tray.setMessage("Employees Details Imported to Database");
  37.         tray.setNotificationType(notificationType);
  38.         tray.showAndDismiss(Duration.millis(3000));
  40.         wb.close();
  41.         fileIn.close();
  42.         pst.close();
  43.         rs.close();
  45.     } catch (SQLException e) {
  46.         NotificationType notificationType = NotificationType.ERROR;
  47.         TrayNotification tray = new TrayNotification();
  48.         tray.setTitle("Warning Message Dialog");
  49.         tray.setMessage("Invalid SQL Command");
  50.         tray.setNotificationType(notificationType);
  51.         tray.showAndDismiss(Duration.millis(3000));
  52.     } catch (IOException e) {
  53.         NotificationType notificationType = NotificationType.ERROR;
  54.         TrayNotification tray = new TrayNotification();
  55.         tray.setTitle("Warning Message Dialog");
  56.         tray.setMessage("The System cannot find the file");
  57.         tray.setNotificationType(notificationType);
  58.         tray.showAndDismiss(Duration.millis(3000));
  59.     }
  60. }
英文:

I want to import data from excel file to MySQL Database using Java. I use for activity to read each row and insert it to database. After the first iterration, I get an error Invalid SQL Command. First record is inserted in database. Please help me with this issue. Bellow is my java code. Thanks.

  1. public void importXLtoDB()  {
  2. String sqlQuery = &quot;INSERT INTO employee (dbSurname, dbName, dbBirthDate, dbEmail, dbMobile, dbPabx, &quot; +
  3. &quot;dbDepartment, dbTeam, dbCurrentPosition, dbManager) VALUE (?,?,?,?,?,?,?,?,?,?)&quot;;
  4. try {
  5. connection = database.getConnection();
  6. pst = connection.prepareStatement(sqlQuery);
  7. FileChooser fileChooser = new FileChooser();
  8. fileChooser.getExtensionFilters().addAll(
  9. new FileChooser.ExtensionFilter(&quot;Excel Files&quot;, &quot;*.xlsx&quot;, &quot;*.xls&quot;));
  10. FileInputStream fileIn = new FileInputStream(fileChooser.showOpenDialog(null));
  11. XSSFWorkbook wb = new XSSFWorkbook(fileIn);
  12. XSSFSheet sheet = wb.getSheetAt(0);
  13. Row row;
  14. for (int i=1; i&lt;= sheet.getLastRowNum(); i++) {
  15. row = sheet.getRow(i);
  16. java.util.Date utilDate = row.getCell(2).getDateCellValue();
  17. java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
  18. pst.setString(1, row.getCell(0).getStringCellValue());
  19. pst.setString(2, row.getCell(1).getStringCellValue());
  20. pst.setDate(3, sqlDate);
  21. pst.setString(4, row.getCell(3).getStringCellValue());
  22. pst.setString(5, row.getCell(4).getStringCellValue());
  23. pst.setString(6, row.getCell(5).getStringCellValue());
  24. pst.setString(7, row.getCell(6).getStringCellValue());
  25. pst.setString(8, row.getCell(7).getStringCellValue());
  26. pst.setString(9, row.getCell(8).getStringCellValue());
  27. pst.setString(10, row.getCell(9).getStringCellValue());
  28. pst.execute();
  29. }
  30. NotificationType notificationType = NotificationType.INFORMATION;
  31. TrayNotification tray = new TrayNotification();
  32. tray.setTitle(&quot;Information Message&quot;);
  33. tray.setMessage(&quot;Employees Details Imported to Database&quot;);
  34. tray.setNotificationType(notificationType);
  35. tray.showAndDismiss(Duration.millis(3000));
  36. wb.close();
  37. fileIn.close();
  38. pst.close();
  39. rs.close();
  40. }catch (SQLException e) {
  41. NotificationType notificationType = NotificationType.ERROR;
  42. TrayNotification tray = new TrayNotification();
  43. tray.setTitle(&quot;Warning Message Dialog&quot;);
  44. tray.setMessage(&quot;Invalid SQL Command&quot;);
  45. tray.setNotificationType(notificationType);
  46. tray.showAndDismiss(Duration.millis(3000));
  47. }catch (IOException e) {
  48. NotificationType notificationType = NotificationType.ERROR;
  49. TrayNotification tray = new TrayNotification();
  50. tray.setTitle(&quot;Warning Message Dialog&quot;);
  51. tray.setMessage(&quot;The System cannot find de file&quot;);
  52. tray.setNotificationType(notificationType);
  53. tray.showAndDismiss(Duration.millis(3000));
  54. }
  55. }

答案1

得分: 0

以下是翻译好的内容：

正确的语法应该是：

  1. INSERT INTO employee (dbSurname, dbName, dbBirthDate, dbEmail, dbMobile, dbPabx, dbDepartment, dbTeam, dbCurrentPosition, dbManager) VALUES (?,?,?,?,?,?,?,?,?,?);

应为VALUES，而不是VALUE。

此外，您应该将您的命令执行为更新。因此，您应该将pst.execute()替换为pst.executeQuery()。

如果那样不起作用，请尝试在for循环中添加以下代码块pst = connection.prepareStatement(sqlQuery);。

在for循环内应该是这个样子：

  1. pst = connection.prepareStatement(sqlQuery);
  2. row = sheet.getRow(i);
  3. java.util.Date utilDate = row.getCell(2).getDateCellValue();
  4. java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
  5. pst.setString(1, row.getCell(0).getStringCellValue());
  6. pst.setString(2, row.getCell(1).getStringCellValue());
  7. pst.setDate(3, sqlDate);
  8. pst.setString(4, row.getCell(3).getStringCellValue());
  9. pst.setString(5, row.getCell(4).getStringCellValue());
  10. pst.setString(6, row.getCell(5).getStringCellValue());
  11. pst.setString(7, row.getCell(6).getStringCellValue());
  12. pst.setString(8, row.getCell(7).getStringCellValue());
  13. pst.setString(9, row.getCell(8).getStringCellValue());
  14. pst.setString(10, row.getCell(9).getStringCellValue());
  16. pst.executeQuery();
英文:

The correct syntax should be:

  1. INSERT INTO employee (dbSurname, dbName, dbBirthDate, dbEmail, dbMobile, dbPabx, dbDepartment, dbTeam, dbCurrentPosition, dbManager) VALUES (?,?,?,?,?,?,?,?,?,?)&quot;;

It's VALUES, not VALUE

Futhermore, you should be executing your command as an update. So you should replace pst.execute() with pst.executeQuery()

If that doesn't work, try adding this block of code pst = connection.prepareStatement(sqlQuery); into the for loop.

It should look like this inside the for loop:

  1.     pst = connection.prepareStatement(sqlQuery);
  2. row = sheet.getRow(i);
  3. java.util.Date utilDate = row.getCell(2).getDateCellValue();
  4. java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
  5. pst.setString(1, row.getCell(0).getStringCellValue());
  6. pst.setString(2, row.getCell(1).getStringCellValue());
  7. pst.setDate(3, sqlDate);
  8. pst.setString(4, row.getCell(3).getStringCellValue());
  9. pst.setString(5, row.getCell(4).getStringCellValue());
  10. pst.setString(6, row.getCell(5).getStringCellValue());
  11. pst.setString(7, row.getCell(6).getStringCellValue());
  12. pst.setString(8, row.getCell(7).getStringCellValue());
  13. pst.setString(9, row.getCell(8).getStringCellValue());
  14. pst.setString(10, row.getCell(9).getStringCellValue());
  15. pst.executeQuery();

huangapple
  • 本文由 发表于 2020年9月12日 23:11:50
  • 转载请务必保留本文链接：https://go.coder-hub.com/63861776.html
  • java
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定