java - MySQLSyntaxErrorException 插入数据库

Question

我在这里看到了很多关于我的问题的答案，但找不到解决方案。我正在读取 Excel 文件并存储在 mysql 数据库中。这是我的代码

PreparedStatement sql_statement = (PreparedStatement) con
                .prepareStatement("insert into medtest(FMCODE,FLAG,MCODE,EMPNO,NAME,ADDRESS1,ADDRESS2,ADDRESS3,BALANCE,HOSPITAL_O,HOSPITAL_I,NURSING,GENERAL,PRIVATE,SPLCODE,BKCD,ACCOUNT_NO) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
        wb = WorkbookFactory.create(new File(filePath));
        // System.out.println(wb.getSheetName());
        Sheet mySheet = wb.getSheetAt(0);

        for (Row row : mySheet) {
            String fmcode = row.getCell(0).getStringCellValue();
            String flag = row.getCell(1).getStringCellValue();
            int mcode = (int) row.getCell(2).getNumericCellValue();
            String empno = row.getCell(3).getStringCellValue();
            String name = row.getCell(4).getStringCellValue();
            String address1 = row.getCell(5).getStringCellValue();
            String address2 = row.getCell(6).getStringCellValue();
            String address3 = row.getCell(7).getStringCellValue();
            double balance = (double) row.getCell(8).getNumericCellValue();
            double hospital_o = (double) row.getCell(9)
                    .getNumericCellValue();
            double hospital_i = (double) row.getCell(10)
                    .getNumericCellValue();
            double nursing = (double) row.getCell(11).getNumericCellValue();
            double general = (double) row.getCell(12).getNumericCellValue();
            double prvte = (double) row.getCell(13).getNumericCellValue();
            String splcode = row.getCell(14).getStringCellValue();
            String bkcd = row.getCell(15).getStringCellValue();
            String account_no = row.getCell(16).getStringCellValue();
            String sql = "insert into medtest values('" + fmcode + "','"
                    + flag + "','" + mcode + "','" + empno + "','" + name
                    + "','" + address1 + "','" + address2 + "','"
                    + address3 + "','" + balance + "','" + hospital_o
                    + "','" + hospital_i + "','" + nursing + "','"
                    + general + "','" + prvte + "','" + splcode + "','"
                    + bkcd + "','" + account_no + "')";
            PreparedStatement ps = (PreparedStatement) con
                    .prepareStatement(sql);
            ps.executeUpdate();

当我将 Excel 文件插入数据库时​​出现以下异常，如何解决它。

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:
        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
        'S DENTAL CLINIC','KINGSWAY CAMP, DELHI.','0.0','0.0','0.0','0.0','0.0','0.0','nu'
        at line 1
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
    ...

错误指向 ps.executeUpdate(); 行。

Answer 1

替换

 sql_statement.executeUpdate() with ps.executeUpdate().

您试图在不设置参数的情况下执行preparedstatement sql_statement。您可以设置参数并调用 sql_statement.executeUpdate() 或简单地将 sql_statement.executeUpdate 替换为 ps.executeUpdate()。