英文:
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 = "INSERT INTO employee (dbSurname, dbName, dbBirthDate, dbEmail, dbMobile, dbPabx, " +
"dbDepartment, dbTeam, dbCurrentPosition, dbManager) VALUE (?,?,?,?,?,?,?,?,?,?)";
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 de file");
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 (?,?,?,?,?,?,?,?,?,?)";
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();
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论