准备语句中的java SQL引发SQL异常

Question

我正在试图弄清楚为什么这段代码会抛出SQL异常。当我运行这段代码时，它会打印“customer insert ps中的坏SQL”，这是内部catch块中的消息。在这个类和我的应用程序的其他地方，我已经准备了多个SQL插入语句。他们都很好。我已经一遍又一遍地查看了这个，我不明白为什么这个会抛出异常

try {
                Connection conn = DBconnection.getConnection();
                PreparedStatement ps = conn.prepareStatement("SELECT customerId FROM customer WHERE customerName=\"" + name + "\";");
                System.out.println(ps.toString());
                ResultSet rs = ps.executeQuery();

                if (rs.next()) {
                    customerId = rs.getString("customerId");
                }
                try {

                    PreparedStatement customerInsert = DBconnection.getConnection().prepareStatement("INSERT "
                            + "INTO customer (customerName, addressId, active, createDate, createdBy, lastUpdate, lastUpdateBy)"
                            + "VALUES(\"" + name + "\", " + addressId + ", " + active + ", UTC_TIMESTAMP(), \"" + LogInController.getUserName() + "\", UTC_TIMESTAMP(), \"" + LogInController.getUserName() + "\");");

                    customerInsert.executeUpdate();

                    System.out.println(customerInsert.toString());
                    System.out.println(rs.toString());

                } catch (SQLException sq) {
                System.out.println("Bad SQL in customer insert ps");
                }

            } catch (SQLException customerIdException) {
                System.out.println("Bad SQL in customer ps");
            }

Answer 1

您正在使用PreparedStatement，就像在使用Statement。不要将参数放在SQL中，而是放在占位符?标记中。然后使用各种setXyz方法（setString、setInt等）填写参数：

PreparedStatement customerInsert = DBconnection.getConnection().prepareStatement(
    "INSERT INTO customer (customerName, addressId, active, createDate, createdBy, lastUpdate, lastUpdateBy)" +
                   "VALUES(?, ?, ?, ?, ?, ?, ?);"
);
customerInsert.setString(1, name);
customerInsert.setInt(2, addressId);
// ...etc. Notice that the parameter indexes start with 1 rather than 0 as you might expect