java - 如何使用 JDBC 插入局部变量 MySQL

Question

public class DataBase {

    public static void main(String[] args) {
        try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/company", , )) {
            Type[] types = { new GraphicCard(), new HardDrive(), new Keyboard(), new Memory(), new Monitor(), new Mouse(), new Processor() };
            Product product = new Product(10, types);
            Range rangeUnitPrice = new Range(10_000, 220_000);
            Range rangeQuantity = new Range(0, 20);
            Statement statement = connection.createStatement();
            while (product.getNumberOfEntery() > 0) {
                String typeAndCatagory = product.getRandomType();
                String name = product.getName(typeAndCatagory);
                String description = product.getDescription();
                double unit_Price = product.randomUnit_PriceGenerator(name, 'x', rangeUnitPrice);
                int quantity_In_Stock = product.generateQuantity_In_Stock(rangeQuantity);
                String brand = product.getRandomBrand();
                System.out.println("Name: " + name + ", " + "Type: " + typeAndCatagory + ", " + "Random price: " + unit_Price + ", " + "Quantity in stock: " + quantity_In_Stock + ", " + "Random brand: " + brand);
                String query = "INSERT INTO product VALUES (" + name + ", " + description + ", " + unit_Price + ", " + quantity_In_Stock + ", " + brand + ", " + typeAndCatagory + ", " + typeAndCatagory + ")";
                statement.executeUpdate(query);
                product.decreasesNumberOfEntrees();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
}

查询不起作用，第一个值是默认值(PRIMARY KEY AUTO-INCREMENT)，我不需要指定。错误如下

java.sql.SQLSyntaxErrorException: You have an error in your SQLsyntax; check the manual that corresponds to your MySQL server versionfor the right syntax to use near '10 AMD graphic card Gamer Edition, ,180657.63138583858, 6, HP, Graphic Card, Gr' at line 1

Answer 1

您在这一行中格式化一个字符串以用作 SQL 语句:

String query = "INSERT INTO product VALUES (" + name + ", " + description + ", " + unit_Price + ", " + quantity_In_Stock + ", " + brand + ", " + typeAndCatagory + ", " + typeAndCatagory + ")";

此语句有问题，导致它产生语法错误。怎么了？

很难通过盯着 Java 表达式来调试它。查看所有的 " 和 + 看看哪里出了问题，这让人很困惑。

如果您可以看到字符串的最终结果，而不是构建字符串的 Java 表达式，那么会更容易看出问题所在。

所以在你执行它之前，试着把它打印出来:

System.out.println(query);

那么问题可能会更加明显。

我预测它会是这个样子:

INSERT INTO product VALUES (10 AMD graphic card Gamer Edition, , 180657.63138583858, 6, HP, Graphic Card, Gr...

您的 VALUES 子句中的字符串值周围缺少引号。它不是有效的 SQL。

最好的解决办法是学习使用查询参数。这样您就不必担心围绕值的引号。并且代码对于 SQL 注入(inject)更加安全。

在您的情况下，类似于以下内容:

String query = "INSERT INTO product VALUES (?, ?, ?, ?, ?, ?, ?)";
Statement statement = connection.prepareStatement(query);
while (product.getNumberOfEntery() > 0) {

    // set the values for all your variables...

    statement.setString(1, name);
    statement.setString(2, description);
    statement.setDouble(3, unit_Price);
    statement.setInt(4, quantity_In_Stock);
    statement.setString(5, brand);
    statement.setString(6, typeAndCatagory);
    statement.setString(7, typeAndCatagory);
    statement.executeUpdate();
}