java - h2|未找到 Java 列(但语句在其他地方有效)

Question

我想将数据插入到我的表中，我有一个插入语句(到同一个表)，我在另一种方法中使用该语句，该方法在一种方法中工作，但返回一个JdbcSQLSyntaxErrorException:未找到列xyz; 在另一个。其中 xyz 是我想要传递到列中的值(不是列名称)。这是触发异常的方法:

  public void btnSaveStock(MouseEvent mouseEvent) {

        ArrayList<Stock> stock = new ArrayList<>();
        stock.addAll(tblStock.getItems());
        stock.remove(removedStock);
        try {
            Class.forName("org.h2.Driver");
            Connection connection = DriverManager.getConnection(url, "", "");
            Statement statement = connection.createStatement();

            for (int i = 0; i < stock.size(); i++) {
                String stockName = stock.get(i).getDescription();
                String stockCode = stock.get(i).getCode();
                double fuelBalance = stock.get(i).getStoresBalance();
                double counterBalance = stock.get(i).getCounterBalance();
                System.out.println(stockName + stockCode + fuelBalance + counterBalance);
                String insertQuery = "INSERT INTO PUBLIC.STOCK (CODE, description, \"fuelBalance\", \"counterBalance\") VALUES (" + stockCode + ",'New Product','1.00','1.00')";
                statement.executeUpdate(insertQuery);
                String updateQuery = "Update STOCK set DESCRIPTION='" + stockName + "' where CODE='" + stockCode + "'";
                statement.executeUpdate(updateQuery);
                updateQuery = "Update STOCK set  \"fuelBalance\"='" + fuelBalance + "' where CODE='" + stockCode + "'";
                statement.executeUpdate(updateQuery);
                updateQuery = "Update STOCK set \"counterBalance\"='" + counterBalance + "' where CODE='" + stockCode + "'";
                statement.executeUpdate(updateQuery);
            }

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }

     //more method calls here
    }

这是成功插入数据的方法:

    public void addNewStock(MouseEvent mouseEvent) {
        Stock stock = new Stock();
        String lastCode = savedStockList.get(savedStockList.size() - 1).getCode();
        int newCode = 1 + Integer.parseInt(lastCode);
        stock.setCode(String.valueOf(newCode));
        stock.setCounterBalance(0);
        stock.setDescription("New Product");
        stock.setStoresBalance(0);
        savedStockList.add(stock);
        try {
            Class.forName("org.h2.Driver");
            Connection connection = DriverManager.getConnection(url, "", "");
            Statement statement = connection.createStatement();
            String insertQuery = "INSERT INTO PUBLIC.STOCK (CODE, description, \"fuelBalance\", \"counterBalance\") VALUES (" + newCode + ",'New Product','1.00','1.00')";
            statement.executeUpdate(insertQuery);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        initialiseStock();
    }

因此，如果 stockCode 包含值 xyz，则异常将为未找到列 xyz

我无法理解为什么这在一种方法中有效，但在另一种方法中返回异常。

Answer 1

它之所以有效，是因为 newCode 是一个不需要引号的数字，也不起作用，因为 stockCode 是一个字符串，需要加引号。在 stockCode 周围添加单引号，它应该可以工作:

String insertQuery = "INSERT INTO PUBLIC.STOCK (CODE, description, \"fuelBalance\", \"counterBalance\") VALUES ('" + stockCode + "','New Product','1.00','1.00')";

作为旁注，您应该真正使用 prepared statement .