SQL not running in java when it should

Question

I have tested my sql in pgAdmin, it returns results fine. However when in Java it does not return any values when the dates are 25/03/2011 and 30/03/2011 but it does when the start is 23/03/2011.

i have System.out.println'ed the sql that i created and run it in pgAdmin and it returns the results fine, but in java it doesnt, any ideas why? The database is being opend and closed as well as the statment and result set fine, i dont see the problem? Here is a snippet of my code

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();

Solved:
I had 2 schemas for the database and i was using the wrong one (an old version)

Just to be clear. When start is 2011-03-23 and finish is 2011-03-30, your Java code prints out "Sorry.. no results!" yet if you copy-and-paste the SELECT query into pgAdmin and run it against the same database, you get data. Correct? — NPE
– NPE, Commented Mar 31, 2011 at 14:41
Are you sure that you are executing the sql on same database for java as well as for pdAdmin. — Naved
– Naved, Commented Mar 31, 2011 at 14:45
@user195257 What if you use a new unique Statement object for this query? — NPE
– NPE, Commented Mar 31, 2011 at 14:45
Got it! I had 2 schemas for the database and i was using the wrong one (an old version) cheers @naved — user195257
– user195257, Commented Mar 31, 2011 at 14:57
You could try using jooq.org, to avoid these kinds of syntax errors — Lukas Eder
– Lukas Eder, Commented Jul 4, 2011 at 6:24

Jonathan · Accepted Answer · 2011-03-31 14:50:34Z

Try using a PreparedStatement instead of concatenating your values together. I also recommend not printing \r characters unless you really want to overwrite from the beginning of a line. I have rewritten parts of your code to show how I generally do this; hopefully it will work for you:

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();
}

If this doesn't work, you may have a problem with time zones. Pass a Calendar object in ps.setDate(...) to change the time zone of the date you are passing.

Collectives™ on Stack Overflow

SQL not running in java when it should

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related