java - SQL 不应该在 java 中运行

Question

我已经在 pgAdmin 中测试了我的 sql，它返回的结果很好。但是，在 Java 中，当日期为 25/03/2011 和 30/03/2011 时，它不会返回任何值，但当开始日期为 23/03/2011 时，它会返回任何值。

我有 System.out.println'ed 我创建的 sql 并在 pgAdmin 中运行它，它返回结果很好，但在 java 中却没有，有什么想法吗？数据库正在打开和关闭，语句和结果集都正常，我没有看到问题吗？这是我的代码片段

String sql = "SELECT Salesrep.Name, SUM(OrderLine.Quantity) AS Total_Sold, SUM(OrderLine.UnitSellingPrice * Orderline.Quantity) AS Total_Value"
            + " FROM SalesRep, OrderLine, ShopOrder WHERE ShopOrder.SalesRepID = SalesRep.SalesRepID"
            + " AND OrderLine.ShopOrderID = ShopOrder.ShopOrderID"
            + " AND ShopOrder.OrderDate BETWEEN '"+start+"' AND '"+finish+"'"
            + " GROUP BY SalesRep.SalesRepID, SalesRep.Name "
            + " ORDER BY Total_Value DESC";
            System.out.println(sql);
    try {
        rs = Database.stmt.executeQuery(sql);
        String name;
        int total;
        double totalPrice;
        int count = 0;
        String output;

        if (rs.next()) {
            System.out.println("Sales representative performace review from " + start + " to " + finish);
            name = rs.getString(1);
            total = rs.getInt(2);
            totalPrice = rs.getDouble(3);
            output = String.format("%-20s %-18s %-15s", "Sales Rep", "Total units sold", "Total Value");
            System.out.println(output);
            output = String.format("%-20s %-18d £%-15.2f", name, total, totalPrice);
            System.out.println(output);
            count++;
            while (rs.next()) {
                name = rs.getString(1);
                total = rs.getInt(2);
                totalPrice = rs.getDouble(3);
                output = String.format("%-20s %-18d £%-15.2f", name, total, totalPrice);
                System.out.println(output);
                count++;
            }
        }
        if (count > 0) {
            System.out.println("\r\nQuery complete with " + count + " results! \r\n");
        } else {
            System.out.println("\r\nSorry.. no results! \r\n");
        }
        rs.close();

<小时/>

已解决:
我有 2 个数据库模式，但我使用了错误的模式(旧版本)

Answer 1

尝试使用 PreparedStatement 而不是将您的值连接在一起。我还建议不要打印 \r 字符，除非您确实想从行首开始覆盖。我重写了您的部分代码以展示我通常如何执行此操作；希望它对您有用:

PreparedStatement ps = Connection.prepareStatement("SELECT Salesrep.Name, SUM(OrderLine.Quantity) AS Total_Sold, SUM(OrderLine.UnitSellingPrice * Orderline.Quantity) AS Total_Value"
            + " FROM SalesRep, OrderLine, ShopOrder WHERE ShopOrder.SalesRepID = SalesRep.SalesRepID"
            + " AND OrderLine.ShopOrderID = ShopOrder.ShopOrderID"
            + " AND ShopOrder.OrderDate BETWEEN ? AND ?"
            + " GROUP BY SalesRep.SalesRepID, SalesRep.Name "
            + " ORDER BY Total_Value DESC";
try {
    ps.setDate(1, start);
    ps.setDate(2, finish);

    ResultSet rs = ps.executeQuery();
    try {
        String name;
        int total;
        double totalPrice;
        int count = 0;
        String output;

        while (rs.next()) {
            if (count == 0) {
                System.out.printf("%-20s %-18s %-15s\n", "Sales Rep", "Total units sold", "Total Value");
            }

            count++;
            System.out.println("Sales representative performace review from " + start + " to " + finish);
            name = rs.getString(1);
            total = rs.getInt(2);
            totalPrice = rs.getDouble(3);
            System.out.printf("%-20s %-18d £%-15.2f\n", name, total, totalPrice);
        }

        if (count > 0) {
            System.out.println("\nQuery complete with " + count + " results!\n");
        } else {
            System.out.println("\nSorry.. no results!\n");
        }
    }
    finally {
        rs.close();
    }
}
finally {
    ps.close();
}

如果这不起作用，您可能遇到时区问题。在 ps.setDate(...) 中传递一个 Calendar 对象来更改您传递的日期的时区。