无效的 JavaFx 应用中的 SQL 命令

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

Invalid SQL Command in JavaFx application

问题

public void importXLtoDB()  {
    String sqlQuery = "INSERT INTO employee (dbSurname, dbName, dbBirthDate, dbEmail, dbMobile, dbPabx, " +
            "dbDepartment, dbTeam, dbCurrentPosition, dbManager) VALUES (?,?,?,?,?,?,?,?,?,?)";
    try {
        connection = database.getConnection();
        pst = connection.prepareStatement(sqlQuery);

        FileChooser fileChooser = new FileChooser();
        fileChooser.getExtensionFilters().addAll(
                new FileChooser.ExtensionFilter("Excel Files", "*.xlsx", "*.xls"));
        FileInputStream fileIn = new FileInputStream(fileChooser.showOpenDialog(null));
        XSSFWorkbook wb = new XSSFWorkbook(fileIn);
        XSSFSheet sheet = wb.getSheetAt(0);
        Row row;
        for (int i=1; i<= sheet.getLastRowNum(); i++) {
            row = sheet.getRow(i);
            java.util.Date utilDate = row.getCell(2).getDateCellValue();
            java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
            pst.setString(1, row.getCell(0).getStringCellValue());
            pst.setString(2, row.getCell(1).getStringCellValue());
            pst.setDate(3, sqlDate);
            pst.setString(4, row.getCell(3).getStringCellValue());
            pst.setString(5, row.getCell(4).getStringCellValue());
            pst.setString(6, row.getCell(5).getStringCellValue());
            pst.setString(7, row.getCell(6).getStringCellValue());
            pst.setString(8, row.getCell(7).getStringCellValue());
            pst.setString(9, row.getCell(8).getStringCellValue());
            pst.setString(10, row.getCell(9).getStringCellValue());

            pst.execute();
        }

        NotificationType notificationType = NotificationType.INFORMATION;
        TrayNotification tray = new TrayNotification();
        tray.setTitle("Information Message");
        tray.setMessage("Employees Details Imported to Database");
        tray.setNotificationType(notificationType);
        tray.showAndDismiss(Duration.millis(3000));

        wb.close();
        fileIn.close();
        pst.close();
        rs.close();

    } catch (SQLException e) {
        NotificationType notificationType = NotificationType.ERROR;
        TrayNotification tray = new TrayNotification();
        tray.setTitle("Warning Message Dialog");
        tray.setMessage("Invalid SQL Command");
        tray.setNotificationType(notificationType);
        tray.showAndDismiss(Duration.millis(3000));
    } catch (IOException e) {
        NotificationType notificationType = NotificationType.ERROR;
        TrayNotification tray = new TrayNotification();
        tray.setTitle("Warning Message Dialog");
        tray.setMessage("The System cannot find the file");
        tray.setNotificationType(notificationType);
        tray.showAndDismiss(Duration.millis(3000));
    }
}
英文:

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.

public void importXLtoDB()  {
String sqlQuery = &quot;INSERT INTO employee (dbSurname, dbName, dbBirthDate, dbEmail, dbMobile, dbPabx, &quot; +
&quot;dbDepartment, dbTeam, dbCurrentPosition, dbManager) VALUE (?,?,?,?,?,?,?,?,?,?)&quot;;
try {
connection = database.getConnection();
pst = connection.prepareStatement(sqlQuery);
FileChooser fileChooser = new FileChooser();
fileChooser.getExtensionFilters().addAll(
new FileChooser.ExtensionFilter(&quot;Excel Files&quot;, &quot;*.xlsx&quot;, &quot;*.xls&quot;));
FileInputStream fileIn = new FileInputStream(fileChooser.showOpenDialog(null));
XSSFWorkbook wb = new XSSFWorkbook(fileIn);
XSSFSheet sheet = wb.getSheetAt(0);
Row row;
for (int i=1; i&lt;= sheet.getLastRowNum(); i++) {
row = sheet.getRow(i);
java.util.Date utilDate = row.getCell(2).getDateCellValue();
java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
pst.setString(1, row.getCell(0).getStringCellValue());
pst.setString(2, row.getCell(1).getStringCellValue());
pst.setDate(3, sqlDate);
pst.setString(4, row.getCell(3).getStringCellValue());
pst.setString(5, row.getCell(4).getStringCellValue());
pst.setString(6, row.getCell(5).getStringCellValue());
pst.setString(7, row.getCell(6).getStringCellValue());
pst.setString(8, row.getCell(7).getStringCellValue());
pst.setString(9, row.getCell(8).getStringCellValue());
pst.setString(10, row.getCell(9).getStringCellValue());
pst.execute();
}
NotificationType notificationType = NotificationType.INFORMATION;
TrayNotification tray = new TrayNotification();
tray.setTitle(&quot;Information Message&quot;);
tray.setMessage(&quot;Employees Details Imported to Database&quot;);
tray.setNotificationType(notificationType);
tray.showAndDismiss(Duration.millis(3000));
wb.close();
fileIn.close();
pst.close();
rs.close();
}catch (SQLException e) {
NotificationType notificationType = NotificationType.ERROR;
TrayNotification tray = new TrayNotification();
tray.setTitle(&quot;Warning Message Dialog&quot;);
tray.setMessage(&quot;Invalid SQL Command&quot;);
tray.setNotificationType(notificationType);
tray.showAndDismiss(Duration.millis(3000));
}catch (IOException e) {
NotificationType notificationType = NotificationType.ERROR;
TrayNotification tray = new TrayNotification();
tray.setTitle(&quot;Warning Message Dialog&quot;);
tray.setMessage(&quot;The System cannot find de file&quot;);
tray.setNotificationType(notificationType);
tray.showAndDismiss(Duration.millis(3000));
}
}

答案1

得分: 0

以下是翻译好的内容:

正确的语法应该是:

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循环内应该是这个样子:

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

pst.executeQuery();
英文:

The correct syntax should be:

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:

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

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

发表评论

匿名网友

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

确定