6 replies to this topic

[chili5]

This probably seems like a silly question but I have 2 queries that are very similar to each other but they are different enough that I have to define two methods to use the query.

One query that I have selects all data from a database and displays it in a JTable. I have a second method that is very similar but it only selects data from the database that is between 2 dates. However, the difference in queries has me defining 2 queries and when I want to make a modification I have to modify the query and display code twice.

Like I have this:

String sQuery = "SELECT group_trans.trans_type, group_trans.amount, " +

                    " group_trans.date, group_trans.mint_id, customers.homeArea, customers.homePrefix, " +

                    "customers.homeLine, group_trans.PaymentType FROM group_trans " +

                    "INNER JOIN customers ON customers.cust_id = group_trans.mint_id " +

                    "WHERE group_trans.group_id =" + nGrpId + "" +

                    " ORDER BY group_trans.trans_id " +

                    "";

Then I have a second method that is used to display data between 2 dates.

 String sQuery = "SELECT group_trans.trans_type, group_trans.amount, " +

                " group_trans.date, customers.homeArea, customers.homePrefix, " +

                "customers.homeLine, group_trans.PaymentType FROM group_trans " +

                "INNER JOIN customers ON customers.cust_id = group_trans.mint_id " +

                "WHERE group_trans.group_id =" + nGrpId + " " +

                "AND group_trans.date BETWEEN '" + dateFormat.format(startDate.getDate()) +

                "' AND '" + dateFormat.format(dateEnd.getDate()) + "' ORDER BY group_trans.date ASC , " +

                "group_trans.trans_id ASC";

Obviously having 2 methods that do the exact same thing is really pointless. I'm looking for a suggestion on how I can clean this up some.

Any thoughts to make this any better? I know I'm doing something wrong because I have 2 methods that are identical to each other.

Any thoughts?

[BlaineSch]

If only selects them all, the other selects between dates, why not just select all and test during the loop if its between two dates, or create a dynamic column in the select query that is 1 for true between the dates or false.

[relapse]

The suggestion about sounds right. Wtf is wrong with executing two SQL queries anyway?

[BlaineSch]

Nothing really, you probably got like 15 queries on this page alone.

But those 15 queries were all needed, why would you need to two 2 queries when you could just do one?

My suggestion only works depending on how he handles them. It really depends on what he needs.

[chili5]

The problem isn't that I got 2 queries (that is required) but the problem I have is there is 2 method executing very similar queries and it just seems kind of pointless.

Like, I have this method:

public void addData() {
        DecimalFormat dfForm = new DecimalFormat("$0.00");
        DateFormat dateFormat = new SimpleDateFormat("MMMM d, yyyy");
        
        try {
            ResultSet rs = null;
            double dAmount = 0.0;

            // select information showing all the transactions for the groups
            String sQuery = "SELECT group_trans.trans_type, group_trans.amount, " +
                    " group_trans.date, group_trans.mint_id, customers.homeArea, customers.homePrefix, " +
                    "customers.homeLine, group_trans.PaymentType FROM group_trans " +
                    "INNER JOIN customers ON customers.cust_id = group_trans.mint_id " +
                    "WHERE group_trans.group_id =" + nGrpId + "" +
                    " ORDER BY group_trans.trans_id " +
                    "";
            rs = menu.dbConn.results(sQuery);
            if (!rs.next()) {
                return; // no results in the database
            }

            // add the data from the database to the table.
            // start the indices at 1 because the indices in the result set
            // start at 1. The table indices start 0 which is why we subtract one.
            for (int x = 1; x <= nRows; x++) {
                for (int y = 1; y <= nCols; y++) {
                     if (y == 1) {
                        if (rs.getString("trans_type").equals("Credit")) {
                            data[x-1][y-1] = dfForm.format(rs.getDouble("amount"));
                            data[x-1][y] = "-";
                            dAmount += rs.getDouble("amount");
                        }
                    } else if (y == 2) {
                        if (rs.getString("trans_type").equals("Payment")) {
                            data[x-1][y-1] = dfForm.format(rs.getDouble("amount"));
                            data[x-1][y-2] = "-";
                            dAmount -= rs.getDouble("amount");
                        }
                    } else if (y == 3) {
                        data[x-1][y-1] = dateFormat.format(rs.getDate("date"));
                    } else if (y == 4) {
                        if (rs.getString("mint_id").equals("-1")) {
                            data[x-1][y-1] = "-";
                        } else {
                            data[x-1][y-1] = rs.getString("homeArea") + rs.getString("homePrefix") + rs.getString("homeLine");
                        }
                    } else if (y == 5) {
                        data[x-1][y-1] = dfForm.format(dAmount);
                    } else if (y == 6) {
                        // show the payment type
                        if (!rs.getString("PaymentType").equals("Cash") && !rs.getString("PaymentType").equals("-")) {
                            data[x-1][y-1] = "Cheque # " + rs.getString("PaymentType");
                        } else {
                            data[x-1][y-1] = rs.getString("PaymentType");
                        }
                    }
                }
                rs.next(); // go to the next row in the result set
            }
            rs.close();
        } catch (SQLException ex) {
            errors.sqlError(ex);
        }
    }

Which executes one query and displays it in a table. Then I have another method that selects all the transactions between 2 dates and displays them in another table.

private Vector<Vector> getTrans() {
        Vector<Vector> rows = new Vector<Vector>();
        DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
        // get all the credit for this group before startDate
        double dBalance = grpControl.getCredits(startDate.getDate(),nGrpId);

        String sQuery = "SELECT group_trans.trans_type, group_trans.amount, " +
                " group_trans.date, customers.homeArea, customers.homePrefix, " +
                "customers.homeLine, group_trans.PaymentType FROM group_trans " +
                "INNER JOIN customers ON customers.cust_id = group_trans.mint_id " +
                "WHERE group_trans.group_id =" + nGrpId + " " +
                "AND group_trans.date BETWEEN '" + dateFormat.format(startDate.getDate()) +
                "' AND '" + dateFormat.format(dateEnd.getDate()) + "' ORDER BY group_trans.date ASC , " +
                "group_trans.trans_id ASC";

        dateFormat = new SimpleDateFormat("MMMM d, yyyy");

        try {
            ResultSet rs = menu.dbConn.results(sQuery);

            while (rs.next()) {
                Vector row = new Vector();
                if (rs.getString("trans_type").equals("Payment")) {
                    // debits
                    row.add("-");
                    row.add(dfForm.format(rs.getDouble("amount")));
                    dBalance -= rs.getDouble("amount");
                } else {
                    // credits
                    row.add(dfForm.format(rs.getDouble("amount")));
                    row.add("-");
                    dBalance += rs.getDouble("amount");
                }
                
                row.add(dateFormat.format(rs.getDate("date")));
                row.add(rs.getString("homeArea") + rs.getString("homePrefix") + rs.getString("homeLine"));
                row.add(dfForm.format(dBalance));
                if (!rs.getString("PaymentType").equals("Cash") && !rs.getString("PaymentType").equals("-")) {
                    row.add("Cheque # " + rs.getString("PaymentType"));
                } else {
                    row.add(rs.getString("PaymentType"));
                }
                
                rows.add(row);
            }

            return rows;
        } catch (SQLException ex) {
            errors.sqlError(ex);
            return null;
        }
    }

See, the redundancy? The getTrans method passes the vector back and then it is iterated and displayed in the table using a second method very similar to the method I posted first. Thus every time we limit transactions we are iterating twice which is slow and makes my job a lot harder.

I'm just not sure on how I can make one method that handles selecting all transactions and one that selects only transactions between 2 dates. Thoughts?

[WingedPanther]

The differences start to matter when you have a LOT of data. If you have 50,000 records, but only 50 in the date range, you will see a huge performance hit if you grab all 50,000.

[chili5]

That is what I was thinking of. This program has to be very stable for a really long time. Like an entire business has to run off of this program.

I went back and looked at my design of the method and changed the functions so that I pass the current balance and the query to a method and then I only have to iterate once instead of twice. My previous code iterated the result set and added items to a vector (which is really stupid because I know the vector is really inefficient at inserting constantly). So I get 0(n) to iterate the Result Set and then I have 0(n) to iterate the vector a second time to display in the table. So now I only have one 0(n) iteration. Like I need an iteration of the data at least once so I can display it in the database.

Now that is just not feasible at all for a large data set which we are bound to have eventually.

At some point or another, I'm probably going to have to really test it to see how well it performs on a large amount of data.