从数据库生成报告

Question

关于我现在问的那个问题，我已经有了贴出一个问题。

要快速重新讨论这些场景：

我正在尝试生成涉及从DB获取数据、将数据写入CSV文件和发送电子邮件的报告。我以相同的格式为所有报告提取数据，因为我希望DAO层和CSV编写独立于特定的报告。我现在面临的问题是，所有报告的逻辑大部分是相同的。对于不同的报告，我还有不同的课程。当然，也有一些定制，但在不同的服务。

ReportInterface，我有两个方法：

/**
 * This interface should be implemented by the report programs.
 */

import java.io.IOException;
import java.sql.SQLException;
import java.text.ParseException;

import com.aig.dtc.report.batch.exception.SystemException;

public interface ReportsInterface {

    public String[] execute(String startDate,String endDate,String reportType) throws IOException,SQLException,Exception;

    /**
     * called after execute. 
     */
    public String getMailContent(boolean singleDate,String startDate,String endDate) throws SystemException, ParseException;
}

RequestCallReport

import java.io.IOException;
import java.io.StringWriter;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.Map;
import java.util.TreeMap;


public class RequestCallReport implements ReportsInterface {
    private static final String STILD = "~";
    private Map<Integer, TreeMap<String,String>> callMeReportData =null;
    String dateTimeStamp;


    public String[] execute(String startDate, String endDate,String  reportType) throws IOException,SQLException,Exception{
        String[] details= new String[5];
        ReportDao reportDao = new ReportDao();
        Object[] reportDetails = reportDao.getDataForRepot(startDate,
                endDate,reportType);
            callMeReportData = (Map<Integer, TreeMap<String,String>>) reportDetails[0];
            CsvWriter csvWriter = new CsvWriter();
            setCallMeReportData(callMeReportData);
            String[] fileName=csvWriter.writeDetailsToFile(reportType, callMeReportData,startDate,endDate);
            for (int i = 0; i < fileName.length; i++) {
                details[i]=fileName[i];
            }
            details[3]=(String)reportDetails[1];
            details[4]=(String)reportDetails[2];
            dateTimeStamp=fileName[2];
        return details;
    }

    public String getMailContent(boolean singleDate,String startDate,String endDate) throws SystemException, ParseException{
             // only this method has some diiferent logic in few reports
    }

    public Map<Integer, TreeMap<String, String>> getCallMeReportData() {
        return callMeReportData;
    }

    public void setCallMeReportData(
            Map<Integer, TreeMap<String, String>> callMeReportData) {
        this.callMeReportData = callMeReportData;
    }
}

RequestLeadReport

import java.io.IOException;
import java.io.StringWriter;
import java.sql.SQLException;
import java.text.ParseException;
import java.util.Map;
import java.util.TreeMap;

public class RequestLeadReport implements ReportsInterface {
    private static final String STILD = "~";
    private Map<Integer, TreeMap<String,String>> callMeReportData =null;
    String dateTimeStamp;

    public String[] execute(String startDate, String endDate,String  reportType) throws IOException,SQLException,Exception{
        String[] details= new String[5];
        ReportDao reportDao = new ReportDao();
        Object[] reportDetails = reportDao.getDataForRepot(startDate,
                endDate,reportType);
            callMeReportData = (Map<Integer, TreeMap<String,String>>) reportDetails[0];
            CsvWriter csvWriter = new CsvWriter();
            setCallMeReportData(callMeReportData);
            String[] fileName=csvWriter.writeDetailsToFile(reportType, callMeReportData,startDate,endDate);
            for (int i = 0; i < fileName.length; i++) {
                details[i]=fileName[i];
            }
            details[3]=(String)reportDetails[1];
            details[4]=(String)reportDetails[2];
            dateTimeStamp=fileName[2];
        return details;
    }

    public String getMailContent(boolean singleDate,String startDate,String endDate) throws SystemException, ParseException{
             // only this method has some diiferent logic in few reports
    }

    public Map<Integer, TreeMap<String, String>> getCallMeReportData() {
        return callMeReportData;
    }

    public void setCallMeReportData(
            Map<Integer, TreeMap<String, String>> callMeReportData) {
        this.callMeReportData = callMeReportData;
    }
}

我至少有6份这样的报告。到目前为止，只有getMailContent有更改，但执行保持不变。

我有这段代码从DB中获取数据。我对所有的报告都使用相同的代码。

ReportDao

import java.lang.reflect.Field;
import java.sql.CallableStatement;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

public class ReportDao {
    public RConnection connection = new RConnection();
    public enum ReportType{CALLME_REPORT,LEADGEN_REPORT};
    public final static int NO_OF_RECORDS=6;

    public Object[] getDataForRepot(String startDate, String endDate,String reportType) throws Exception {
        BatchJLogger.logMessage(" Started Execution of method getDataForRepot " );
        ResultSet rs = null;
        Statement stmt = null;
        CallableStatement cstmt = null;
        Object[] details = new Object[3];
        try {
            stmt = connection.getDBConnection().createStatement();
            Object[] reportDetails = getReportRecords(cstmt,startDate,endDate,reportType);
            rs = (ResultSet)reportDetails[0];
            Map<Integer, TreeMap<String,String>> values= getFormattedData(rs);
            details[0]=values;
            details[1]=reportDetails[1];
            details[2]=reportDetails[2];
            BatchJLogger.logMessage(" No of records fetched  "+values.size() );
            BatchJLogger.logMessage(" End Execution of method getDataForRepot " );
            return details;

        }  finally {
            if (rs != null)
                rs.close();
            if (stmt != null)
                stmt.close();
            /*if(cstmt!=null){
                cstmt.close();
            }*/
            connection.close();
            BatchJLogger.logMessage(" End Execution of method getDataForRepot " );
        }
    }

    public Object[] getReportRecords(CallableStatement cstmt,String startDate, String endDate,String reportType)
            throws SQLException,Exception {
        Object[]  queryValues = new Object[3];
        ResultSet rs = null;
        try{
            BatchJLogger.logMessage(" Started Execution of method getReportRecords ");

            String procedure = "{call GET_DAILY_RPT_REC (?,?,?,?,?,?)}";
            cstmt = connection.createCallableStatement(procedure);
            int procId=7;
            switch (ReportType.valueOf(reportType)) {
            case REPORTCALL_REPORT:
                cstmt.setInt(1, 7);
                break;
            case REPORTLEAD_REPORT:
                cstmt.setInt(1, 8);
                break;
            default:
                break;
            }
            if(startDate!=null){
                cstmt.setTimestamp(2, BatchJUtil.convertToTimeStamp(startDate,true));
                cstmt.setTimestamp(3, BatchJUtil.convertToTimeStamp(endDate,false));
            }else{
                cstmt.setTimestamp(2, null);
                cstmt.setTimestamp(3, null);
            }
            cstmt.registerOutParameter(4,
                    getOracleParamReturnType("CURSOR"));
            cstmt.registerOutParameter(5,
                    java.sql.Types.VARCHAR);
            cstmt.registerOutParameter(6,
                    java.sql.Types.VARCHAR);
            cstmt.execute();
            rs = connection.getResultSet(cstmt, 4);
            System.out.println(" out params form proc");
            System.out.println(cstmt.getString(5));
            System.out.println(cstmt.getString(6));
            Timestamp cronJobStDate=cstmt.getTimestamp(6);
            String formattedDate=null;
            if(cronJobStDate!=null){
                formattedDate=cronJobStDate.toString();
                formattedDate=BatchJUtil.convertformat(formattedDate);
            }
            String changeInStDate=cstmt.getString(5);
            queryValues[0]=rs;
            queryValues[1]=changeInStDate;
            queryValues[2]=cstmt.getString(6);

            BatchJLogger.logMessage(" End Execution of method getReportRecords ");
            return queryValues;
        }catch (Exception e) {
            e.printStackTrace();
            connection.close();
        }

        return queryValues;


    }

    public static int getOracleParamReturnType(String paramName) {
        if (paramName == null)
            return -1;
        Field cursorField;
        try {
            Class c = Class.forName("oracle.jdbc.driver.OracleTypes");
            cursorField = c.getField(paramName);
            return cursorField.getInt(c);
        } catch (Throwable th) {
            th.printStackTrace();
            return -1;
        }
    }

    public Map<Integer, TreeMap<String,String>> getFormattedData(ResultSet rs ) throws SQLException, ParseException{
        Map<Integer, TreeMap<String,String>>  data = new TreeMap<Integer, TreeMap<String,String>>();

        List<String> coulmnNames = new ArrayList<String>();
        ResultSetMetaData rsmd = rs.getMetaData();
        int columnCount = rsmd.getColumnCount();
        for (int i = 1; i < columnCount + 1; i++ ) {
            coulmnNames.add(rsmd.getColumnName(i)) ;
        }
        int noOfRecords =0 ;
        while (rs.next()) {
            TreeMap<String,String> values = new TreeMap<String, String>();
            for (String name : coulmnNames) {
                if(name.equalsIgnoreCase("Submitted On")){
                    if(rs.getString(name)!=null){
                        String submittedOn =rs.getString(name);
                        values.put(name, submittedOn);  
                    }else{
                        values.put(name, null);
                    }

                }else{
                    values.put(name, rs.getString(name));   
                }

            }
            data.put(++noOfRecords, values);
        }
        return data;
    }

    public void deleteBatchJobRecord(String reportName) throws SQLException{
        try {
            String deleteRecord = "delete from TBATCH_RUN_LOG where SYSTEM_ID=? and RUN_ID=(Select MAX(RUN_ID) "
                + "from TBATCH_RUN_LOG where SYSTEM_ID=?)";
            PreparedStatement pstmt = connection.getDBConnection()
            .prepareStatement(deleteRecord);
            switch (ReportType.valueOf(reportName)) {
            case REPORTCALL_REPORT:
                pstmt.setInt(1, 7);
                pstmt.setInt(2, 7);
                break;
            case REPORTLEAD_REPORT:
                pstmt.setInt(1, 8);
                pstmt.setInt(2, 8);
                break;
            default:
                break;
            }
            int result = pstmt.executeUpdate();
            System.out.println(" record deleted " + result);
        } finally {
            connection.close();
        }
    }
}

CsvWriter

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.Map;
import java.util.ResourceBundle;
import java.util.Set;
import java.util.TreeMap;


public class CsvWriter {

    private static Map<String,ArrayList<String>> headers = new HashMap<String, ArrayList<String>>(); 

    static{
        ArrayList<String> callMe = new ArrayList<String>();
        callMe.add("Mobile Phone");
        callMe.add("Call Time");
        callMe.add("Submitted On");
        callMe.add("First Name");
        callMe.add("Last Name");
        callMe.add("Email");
        ArrayList<String> leadGen = new ArrayList<String>();
        leadGen.add("Title");
        leadGen.add("First Name");
        leadGen.add("Last Name");
        leadGen.add("Mobile Phone");
        leadGen.add("Product Interested");
        leadGen.add("Submitted On");
        headers.put("CALLME_REPORT", callMe);
        headers.put("LEADGEN_REPORT", leadGen);
    }

    private FileWriter fileWriter = null;
    private String folderName = null;
    public enum ReportType{CALLME_REPORT,LEADGEN_REPORT};

    public FileWriter getFileWriter() {
        return fileWriter;
    }

    public void setFileWriter(FileWriter fileWriter) {
        this.fileWriter = fileWriter;
    }

    final ResourceBundle BUNDLE = ResourceBundle
            .getBundle(AppConstants.CONFIG_PATH);

    public CsvWriter() {
        folderName = BUNDLE.getString("FOLDER_PATH");
    }


    public String[] writeDetailsToFile(String reportType,Map<Integer, TreeMap<String,String>> values,String startDate,String endDate) throws IOException,Exception {
        String filePath=null;
        String fileName =null;
        String[] details=null;
        try{

            //BatchJLogger.logMessage(" Started Execution of method writeDetailsToFile " );
            BatchJLogger.logMessage(" Started Execution of method openXls " );

            details = createFileName(reportType, startDate, endDate);
            fileName=details[0];
            filePath = folderName + File.separator + fileName;
            fileWriter = new FileWriter(filePath);
            File f = new File(filePath);
            BatchJLogger.logMessage(" file created "+f.exists() );

            fileWriter.write("Report Name");
            fileWriter.write(",");
            switch (ReportType.valueOf(reportType)) {
            case CALLME_REPORT:
                fileWriter.write("Call Me");
                break;
            case LEADGEN_REPORT:
                fileWriter.write("Lead Gen");
                break;
            default:
                break;
            }
            fileWriter.write(",");
            fileWriter.write("Date ");
            fileWriter.write(",");
            fileWriter.write(CsvWriter.getCurrentDate());
            fileWriter.write("\n");
            fileWriter.write("\n");
            ArrayList<String> cloumnNames = headers.get(reportType);
            int fileHeader = 0;
            for (String columnName : cloumnNames) {
                fileWriter.write(columnName);
                if(fileHeader < cloumnNames.size()){
                    fileWriter.write(",");
                }
                fileHeader++;
            }
            fileWriter.write("\n");


            Set<Integer> recordSet = values.keySet();
            for (Integer record : recordSet) {
                TreeMap<String, String> data = values.get(record);
                int columnCount = 0;
                for (String columnName : cloumnNames) {
                    String columnData=data.get(columnName);
                    if((columnName.equalsIgnoreCase("Mobile Phone")||columnName.equalsIgnoreCase("Submitted On")) && columnData!=null ){
                        fileWriter.write("'");
                    }
                    fileWriter.write(BatchJUtil.checknull(columnData));
                    if(columnCount < cloumnNames.size()){
                        fileWriter.write(",");
                    }
                    columnCount++;
                }
                fileWriter.write("\n");
            }
        }
        finally{
            fileWriter.flush();
            fileWriter.close(); 
            return new String[]{filePath,fileName,details[1]};
        }

        //BatchJLogger.logMessage(" end of Execution of method writeDetailsToFile " );
    }

    public String[] createFileName(String reportType,String startDate,String endDate) throws ParseException{
        String[] data =null;
        String fileName=null;
        String toDaysDate=null;
        if(startDate!=null && startDate.length()!=0){
            startDate=BatchJUtil.convertformat(startDate);
            endDate=BatchJUtil.convertformat(endDate);
            toDaysDate=startDate+"_To_"+endDate;
        }else{
            DateFormat dateFormat = new SimpleDateFormat("dd-MMM-yyyy");
            Calendar cal = Calendar.getInstance();
            String date = dateFormat.format(cal.getTime());
            String[] parsedDate = date.split("-");
            int numDay = Integer.parseInt(parsedDate[0]);
            String  month = parsedDate[1];
            int numYear = Integer.parseInt(parsedDate[2]);
            toDaysDate =  BatchJUtil.checkNumber(numDay) + "-"+month+ "-" + BatchJUtil.checkNumber(numYear);    
        }

        switch (ReportType.valueOf(reportType)) {
        case CALLME_REPORT:
            fileName="reprot_call_"+toDaysDate+".csv";
            break;
        case LEADGEN_REPORT:
            fileName="reprot_lead_"+toDaysDate+".csv";
            break;
        default:
            break;
        }
        data=new String[]{fileName,toDaysDate};
        return data;
    }

    public static String getCurrentDate(){
        DateFormat dateFormat = new SimpleDateFormat("dd-MMM-yyyy");
        Calendar cal = Calendar.getInstance();
        //cal.add(Calendar.DATE, -1);
        return dateFormat.format(cal.getTime());
    }

    public static String getPreviousDate(){
        DateFormat dateFormat = new SimpleDateFormat("dd-MMM-yyyy");
        Calendar cal = Calendar.getInstance();
        cal.add(Calendar.DATE, -1);
        return dateFormat.format(cal.getTime());
    }
}

Answer 1

这段代码需要一些重构。以下是我的建议：

1)由于您有某些类型的报告，它们似乎有一些共同的行为，所以可以使用基类和一些经典继承。因此，您必须为所有情况编写一次执行逻辑，在不需要更改的情况下，在子类中重写，在子类中需要更改。你的界面就过时了。

"startDate“、"endDate”和"reportType“将是类的成员变量。充其量，某些日期类型的起始/结束日期和reportType作为枚举。

( 2)在对象中读取新对象是不好的。这会导致代码，这是很难测试的。而不是

ReportDao reportDao = new ReportDao();

您可以通过“构造函数注入”“注入”Dao。如果您采取步骤(1)，您将不得不拆分您的关注点：( a)您需要一个对象来表示报表本身；( b)您需要一个对象，该对象创建不同类型的报告。会有合适的地方注射你的刀。

( 3)这些细节是什么？

String[] details= new String[5];

会有人知道吗？好吧我承认至少你知道。但是，想想一个大一新生，对于你的代码来说，这将需要(太多的)时间，使他的头脑在代码上。

4)缩短你的方法。

public Object[] getReportRecords(CallableStatement cstmt,String startDate, String endDate,String reportType)
                throws SQLException,Exception {
            Object[]  queryValues = new Object[3];
            ResultSet rs = null;
            try{
                BatchJLogger.logMessage(" Started Execution of method getReportRecords ");

                String procedure = "{call GET_DAILY_RPT_REC (?,?,?,?,?,?)}";
                cstmt = connection.createCallableStatement(procedure);
                int procId=7;
                switch (ReportType.valueOf(reportType)) {
                case REPORTCALL_REPORT:
                    cstmt.setInt(1, 7);
                    break;
                case REPORTLEAD_REPORT:
                    cstmt.setInt(1, 8);
                    break;
                default:
                    break;
                }
                if(startDate!=null){
                    cstmt.setTimestamp(2, BatchJUtil.convertToTimeStamp(startDate,true));
                    cstmt.setTimestamp(3, BatchJUtil.convertToTimeStamp(endDate,false));
                }else{
                    cstmt.setTimestamp(2, null);
                    cstmt.setTimestamp(3, null);
                }
                cstmt.registerOutParameter(4,
                        getOracleParamReturnType("CURSOR"));
                cstmt.registerOutParameter(5,
                        java.sql.Types.VARCHAR);
                cstmt.registerOutParameter(6,
                        java.sql.Types.VARCHAR);
                cstmt.execute();
                rs = connection.getResultSet(cstmt, 4);
                System.out.println(" out params form proc");
                System.out.println(cstmt.getString(5));
                System.out.println(cstmt.getString(6));
                Timestamp cronJobStDate=cstmt.getTimestamp(6);
                String formattedDate=null;
                if(cronJobStDate!=null){
                    formattedDate=cronJobStDate.toString();
                    formattedDate=BatchJUtil.convertformat(formattedDate);
                }
                String changeInStDate=cstmt.getString(5);
                queryValues[0]=rs;
                queryValues[1]=changeInStDate;
                queryValues[2]=cstmt.getString(6);

                BatchJLogger.logMessage(" End Execution of method getReportRecords ");
                return queryValues;
            }catch (Exception e) {
                e.printStackTrace();
                connection.close();
            }

            return queryValues;


        }    

这很难读懂。

除非您的应用程序是一个控制台应用程序：

System.out.println(" out params form proc");
System.out.println(cstmt.getString(5));
System.out.println(cstmt.getString(6));

根本不应该引用控制台。

( 5)也许将报告的编写外包给报告编写班是有意义的。这里有一个Writer列表，它将报告作为输入并生成所需的输出。

public class ReportWriter{
     public List<ReportOuptput> = new ArrayList<ReportOuptput>();
     // ...
     public void addOutputChannel(ReportOutput ... channels);
     // ...
     public void writeReport(Report report);
}

6)使用更好的命名:很难理解，是什么样子的

Map<Integer, TreeMap<String, String>>

是。好的:整数值到树状图的映射，树状图将一个字符串映射到另一个字符串。让我们深入了解一下代码：

public Map<Integer, TreeMap<String,String>> getFormattedData(ResultSet rs ) throws SQLException, ParseException{
        Map<Integer, TreeMap<String,String>>  data = new TreeMap<Integer, TreeMap<String,String>>();

是“数据”..。和

TreeMap<String,String> values = new TreeMap<String, String>();

这就是价值观。到目前为止，人们不知道这段代码是关于什么的。如果你在处理书，你有一本书，它由页组成，有线条，有字符。所以这就是该走的路了。

7)下面的代码是做什么的？

for (String name : coulmnNames) {
    if(name.equalsIgnoreCase("Submitted On")){
        if(rs.getString(name)!=null){
            String submittedOn =rs.getString(name);
            values.put(name, submittedOn);  
        }else{
            values.put(name, null);
        }

    }else{
        values.put(name, rs.getString(name));   
    }

}

有效地：

String submittedOn =rs.getString(name);
values.put(name, submittedOn);

values.put(name, null);

在“空”的情况下，它将“空”放在那里。至少：

values.put(name, rs.getString(name));

所以，如果我做得对:它提出了什么，它在价值观中找到了什么。

我想，你还有很多工作要做。