Java Struts 报告

Question

我正在使用操作、 Controller 、servlet struts 框架编写 Excel 报告。该报告非常拥挤，已经有大约 10 个单独的查询。由于报告发生变化，我需要再添加大约 10 个查询。有没有办法做到这一点而不必继续嵌套结果集？以下是现有代码。

public TotalSalesKPIContainer totalSalesKPIQuery(String franchiseId,    java.util.Date startDate, java.util.Date stopDate) {
    String getFranIds = null;
    ArrayList storeResultList = new ArrayList();
    ArrayList salesPersonResultList = new ArrayList();
    TotalSalesKPIContainer result = null;
    Connection conn = null;
    PreparedStatement pstmt1, pstmtLY, pstmtCountLY, pstmtTCount, pstmtTCountLY, pstmtCount = null;
    PreparedStatement pstmt, ps = null;
    ResultSet rs1, rsLY, rsCountLY, rsTCount, rsTCountLY, rsCount = null;
    ResultSet rs = null, rsSP = null, rsBySP, rsTCount2, rsTCountLY2 = null, rsBySPLY, rsCountLY2, rsCount2;
    java.util.Date prevStartDate;
    java.util.Date prevStopDate;
    try {

        conn = getConnection();
        if (franchiseId.equals("0")) {
            getFranIds = "Select id, name from vymac.organization";
            pstmt = conn.prepareStatement(getFranIds);
        } else {
            getFranIds = "Select id, name from vymac.organization where id = ? ";
            pstmt = conn.prepareStatement(getFranIds);
            pstmt.setString(1, franchiseId);
        }
        int franID = Integer.parseInt(franchiseId);
        rs = pstmt.executeQuery();
        while (rs.next()) {

            result = new TotalSalesKPIContainer(rs.getString(2), franID,startDate, stopDate, storeResultList,salesPersonResultList);
            /*
             * This section is for Salesperson total sales
             */
            pstmt1 = conn.prepareStatement(getTotalSalesBySP);
            pstmt1.setInt(1, rs.getInt(1));
            pstmt1.setDate(2, new java.sql.Date(startDate.getTime()));
            pstmt1.setDate(3, new java.sql.Date(stopDate.getTime()));
            rsBySP = pstmt1.executeQuery();

            while (rsBySP.next()) {
                ps = conn.prepareStatement(getSalesPersons);
                ps.setInt(1, rsBySP.getInt(1));
                rsSP = ps.executeQuery();
                if (rsSP.next()) {
                    ps = conn.prepareStatement(getPrevTotalSalesBySP);

                    // Get previous year date
                    Calendar cal = Calendar.getInstance();
                    cal.setTime(startDate);
                    cal.add(Calendar.YEAR, -1);
                    prevStartDate = cal.getTime();
                    cal.setTime(stopDate);
                    cal.add(Calendar.YEAR, -1);
                    prevStopDate = cal.getTime();

                    ps.setString(1, rsBySP.getString(1));
                    ps.setDate(2,new java.sql.Date(prevStartDate.getTime()));
                    ps.setDate(3, new java.sql.Date(prevStopDate.getTime()));
                    rsBySPLY = ps.executeQuery();

                    if (rsBySPLY.next()) {
                        // Count Invoices for Last Year
                        ps = conn.prepareStatement(findCountBySP);
                        ps.setDate(1, new java.sql.Date(prevStartDate.getTime()));
                        ps.setDate(2, new java.sql.Date(prevStopDate.getTime()));
                        ps.setString(3, rsBySP.getString(1));

                        rsCountLY2 = ps.executeQuery();

                        if (rsCountLY2.next()) {
                            ps = conn.prepareStatement(findCountBySP);
                            ps.setDate(1, new java.sql.Date(startDate.getTime()));
                            ps.setDate(2, new java.sql.Date(stopDate.getTime()));
                            ps.setString(3, rsBySP.getString(1));

                            rsCount2 = ps.executeQuery();
                            if (rsCount2.next()) {
                                ps = conn.prepareStatement(trafficCountBySP);
                                ps.setDate(1, new java.sql.Date(startDate.getTime()));
                                ps.setDate(2, new java.sql.Date(stopDate.getTime()));
                                ps.setString(3, rsBySP.getString(1));
                                rsTCount2 = ps.executeQuery();

                                if (rsTCount2.next()) {
                                    ps = conn.prepareStatement(trafficCountBySP);
                                    ps.setDate(1, new java.sql.Date(prevStartDate.getTime()));
                                    ps.setDate(2, new java.sql.Date(prevStopDate.getTime()));
                                    ps.setString(3, rsBySP.getString(1));

                                    rsTCountLY2 = ps.executeQuery();

                                    if (rsTCountLY2.next()) {



                                        TotalSalesInformationBySalesPerson tbsp = new TotalSalesInformationBySalesPerson(rsSP.getString(1), rs.getString(2), rs.getInt(1),rsCount2.getInt(1), rsBySP.getDouble(2),rsBySPLY.getDouble(2),rsCountLY2.getInt(1),
                                                rsTCount2.getInt(1),rsTCountLY2.getInt(1));
                                        salesPersonResultList.add(tbsp);
                                    } else {
                                        TotalSalesInformationBySalesPerson tbsp = new TotalSalesInformationBySalesPerson(
                                                rsSP.getString(1), rs
                                                        .getString(2), rs
                                                        .getInt(1),
                                                rsCount2.getInt(1), rsBySP
                                                        .getDouble(2),
                                                rsBySPLY.getDouble(2),
                                                rsCountLY2.getInt(1),
                                                rsTCount2.getInt(1), 0);
                                        salesPersonResultList.add(tbsp);
                                    }
                                }
                            }
                        }
                    } else {
                        ps = conn.prepareStatement(findCountBySP);
                        ps.setDate(1,
                                new java.sql.Date(startDate.getTime()));
                        ps
                                .setDate(2, new java.sql.Date(stopDate
                                        .getTime()));
                        ps.setString(3, rsBySP.getString(1));
                        rsCount2 = ps.executeQuery();
                        if (rsCount2.next()) {
                            ps = conn.prepareStatement(trafficCountBySP);
                            ps.setDate(1, new java.sql.Date(startDate
                                    .getTime()));
                            ps.setDate(2, new java.sql.Date(stopDate
                                    .getTime()));
                            ps.setString(3, rsBySP.getString(1));
                            rsTCount2 = ps.executeQuery();
                            if (rsTCount2.next()) {

                                TotalSalesInformationBySalesPerson tbsp = new TotalSalesInformationBySalesPerson(
                                        rsSP.getString(1), rs.getString(2),
                                        rs.getInt(1), rsCount2.getInt(1),
                                        rsBySP.getDouble(2), 0.00, 0,
                                        rsTCount2.getInt(1), 0);
                                salesPersonResultList.add(tbsp);
                            } else {
                                TotalSalesInformationBySalesPerson tbsp = new TotalSalesInformationBySalesPerson(
                                        rsSP.getString(1), rs.getString(2),
                                        rs.getInt(1), rsCount2.getInt(1),
                                        rsBySP.getDouble(2), 0.00, 0,
                                        rsTCount2.getInt(1), 0);
                                salesPersonResultList.add(tbsp);
                            }
                        }
                    }
                }
            }

Answer 1

看起来您正在代码中进行联接，即执行查询，并针对返回的每条记录，对其中一些数据执行查询。然后实际上嵌套更多这样的查询。

通常可以在数据库中完成更多的连接工作，但我认为如果不查看数据库模式并理解代码的含义，我们无法提供更多帮助。

我建议您只考虑问题的一个级别，并考虑它是否可以表示为单个查询，或者一些相关查询的 UNION。然后从那里构建。

顺便说一句，您拥有的 Java 可以重新组织和重构，以使其更容易理解，将子查询拉出到它们自己的函数中 - 但希望您不需要像这样的嵌套代码。