从数据库生成电子邮件报告

Question

我写了一个小批作业，它将从db收集数据并发送邮件给用户。请您根据设计原则以及Db和文件编写器的最佳实践对代码进行检查。我正在写这段代码中的文件，并以邮件的形式发送。

用户将使用要生成的报表类型以及startdate和enddate调用该类。

可以有3-4种类型的报表生成类型，这样用户就可以给出要生成的报表类型的逗号分隔列表。我采用报告类型并生成与其相关的类。

我想编写一个可扩展的代码或可维护的code.In，这些类集CSVwriter和Report确实起着主要作用。我希望保持一个过程从不同的报表获取数据，因此对所有不同的报告都使用了reports方法getDataForRepot。同样，CSV编写器用于为所有报表将数据写入csv文件。

请仔细检查Reprot和CSVwriter class.Is是否需要VO或DTO？

下面是调用报表生成器类的主类。

import java.util.ResourceBundle;


public class ReportsManager {

    public static void main(String[] args) {

        try {

        if (args.length > 0) {
            String[] reportsList = null;
            if (args[0] != null && args[0].trim().length() != 0) {
                reportsList = args[0].split(",");
            }
            String startDate = null;
            if (args[1] != null && args[1].trim().length() != 0) {
                startDate = args[1];
            }
            String endDate = null;
            if (args[2] != null && args[2].trim().length() != 0) {
                startDate = args[2];
            }

            final ResourceBundle BUNDLE = ResourceBundle
            .getBundle(AppConstants.CONFIG_PATH);
            for (int i = 0; i < reportsList.length; i++) {
                final String clazzName = BUNDLE.getString(reportsList[i]);

                BatchJLogger.logMessage(" Report Generation Started for  "
                        + reportsList[i]);
                String fileDetails[] = null;
                ReportsInterface reports = (ReportsInterface) Class
                .forName(clazzName).newInstance();
                fileDetails = reports.execute(startDate, endDate,
                        reportsList[i]);

                String strBody = reports.getMailContent();
                MailSender mailSender = new MailSender(reportsList[i]);
                mailSender.sendMail(strBody, fileDetails[0],
                        fileDetails[1], true);
            }
        } else {
            System.err
            .println(" Please enter the report type to be generated");
        }

    } catch (IOException ex) {
        ex.printStackTrace();
    } catch (SQLException ex) {
        ex.printStackTrace();
    } catch (Exception ex) {
        ex.printStackTrace();
    }
}
}

下面是所有报表类型都应该实现的接口

public interface ReportsInterface {

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

    /**
     * called after execute. 
     */
    public String getMailContent();
}

下面是一个报表类，它创建文件，然后使用存储过程调用Db获取数据，然后将内容写入文件并发送邮件

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Vector;


public class UserDetailsReport implements ReportsInterface {

  private Map<Integer, TreeMap<String,String>> userDetialsData =null;
String dateTimeStamp;

    @Override
    public String[] execute(String startDate, String endDate,String  reportType) throws IOException,SQLException,Exception{
    String[] fileName=null;
    ReportDao reportDao = new ReportDao();
     userDetialsData = reportDao.getDataForRepot(startDate,
            endDate,reportType);
        CsvWriter csvWriter = new CsvWriter();

        fileName=csvWriter.writeDetailsToFile(reportType, callMeReportData,startDate,endDate);
        dateTimeStamp=fileName[2];
    return fileName;

}

    public String getMailContent(){
        StringBuilder body = new StringBuilder();
        body.append("\n");
        body.append("\n");
        body.append("Please find attached report.");
        body.append("\n");
        body.append("\n");
        body.append("\n");
        body.append("\n");
        body.append("Thanks,");
        return body.toString(); 
    }

}

下面是创建数据并将数据写入csv文件的类。

    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 {
    // this will hold the list of header for each reprot so that i dont have to write any specific method for a particular report
    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("First Name");
        leadGen.add("Last Name");
        leadGen.add("Mobile Phone");
        leadGen.add("Product Interested");
        leadGen.add("Submitted On");
        headers.put("USERDETAILS_REPORT", callMe);
        headers.put("SOMEOTHER_REPORT", leadGen);
    }

    private FileWriter fileWriter = null;
    private String folderName = null;
    public enum ReportType{USERDETAILS_REPORT,SOMEOTHER_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");
    }

    // this method will be used by all the reprots

    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 USERDETAILS_REPORT:
                fileWriter.write("USER DETILS");
                break;
            case SOMEOTHER_REPORT:
                fileWriter.write("SOME");
                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 USERDETAILS_REPORT:
            fileName="UserDetails_"+toDaysDate+".csv";
            break;
        case SOMEOTHER_REPORT:
            fileName="SomeOther_"+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();
        return dateFormat.format(cal.getTime());
    }


}

这是从Db获取连接的类。

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;
import java.util.Vector;
import javax.sql.DataSource;


public class RConnection {
    private Connection mConnection;
    private Statement mStatement;
    private static Object lockObject = new Object();
    private static int openCount = 0;
    private static int closeCount = 0;
    private static String dataSourceName = "somedb";
    private static DataSource dataSource = null;
    private Vector statementVector= new Vector();
    private Vector resultSetVector= new Vector();


    public CallableStatement createCallableStatement(String sql) throws SQLException
    {
        CallableStatement cstmt = null;
        try
        {
            cstmt = this.getDBConnection().prepareCall(sql);
            statementVector.add(cstmt);
        }
        catch(SQLException e)
        {       
            e.printStackTrace();
            throw new SQLException("Can not create CallableStatement for sql "+e.getMessage());
        }
        return cstmt;
    }


    public ResultSet getResultSet(CallableStatement cstmt, int paramNo) throws SQLException
    {
        ResultSet rs=null;
        try
        {
            rs=(ResultSet) cstmt.getObject(paramNo);
            resultSetVector.add(rs);
        }
        catch(SQLException e)
        {
            e.printStackTrace();
            throw new SQLException("Can not retrieve ResultSet for this CallableStatement "+e.getMessage());
        }
        return rs;
    }


    public static int getOpenConnections()
    {
        return openCount;
    }
    public static int getCloseConnections()
    {
        return closeCount;
    }
    /**
    /**
     * Constructor being made Private, Singleton implementation
     */
    public RConnection()
    {
        this.connect();
    }


    /**
     * This is the function that is used to connect to the
     * database using jdbc
     */
    public void connect()
    {
        try
        {
            String errorString = "Error obtaining database connection.";
            final ResourceBundle BUNDLE = ResourceBundle.getBundle(AppConstants.CONFIG_PATH);

                try {

                    String DB_USERNAME = BUNDLE.getString(AppConstants.DB_USERNAME);
                    String DB_PASSWORD = BUNDLE.getString(AppConstants.DB_PASSWORD)
                    String DB_URL = BUNDLE.getString(AppConstants.DB_URL);
                    Class.forName(AppConstants.DRIVER_CLASS_NAME);
                    mConnection = DriverManager.getConnection(DB_URL, DB_USERNAME,
                            DB_PASSWORD);
                    mConnection.setAutoCommit(false);       
                }catch (NullPointerException e) {                   
                    throw new Exception(errorString+":"+e.getMessage());
                }
                catch(SQLException sqle) {
                    throw new SQLException(errorString+":"+sqle.toString());
                }


            if (mConnection == null) {
                throw new SQLException(errorString);
            } else {
                synchronized (lockObject) {
                    openCount++;                    
                }
            }
        }
        catch(Exception ex)
        {
                ex.printStackTrace();
throw new SQLException(e.getMessage());
        }
    }

    /** Return Connection **/
    public Connection getDBConnection()
    {
        return mConnection;
    }

    /**
     * Execute any plain SQL query and returns the ResultSet
     */
    public ResultSet executeSQL(String query)  throws SQLException
    {
        ResultSet lRSet = null;
        try
        {
            // close the statement if already open
            if(mStatement != null) mStatement.close();
            mStatement = mConnection.createStatement();
            lRSet = mStatement.executeQuery(query);
        }
        catch(SQLException ex)
        {
            System.err.println("Error Query : " + query);
            ex.printStackTrace();
                throw new SQLException(ex.getMessage());
        }

        return lRSet;
    }

    public void commit()
    {
        try
        {
            if(mStatement != null) mStatement.close();
            if(mConnection != null && !mConnection.getAutoCommit()) mConnection.commit();
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
            throw new Exception(ex.getMessage());
        }
    }

    public void close()
    {
        try
        {
            if ((mConnection != null) && !mConnection.isClosed()) {
                if(!resultSetVector.isEmpty())
                {
                    //ok close all result sets once more
                    int rsSize = resultSetVector.size();
                    for(int i=0;i<rsSize;i++)
                    {
                        ResultSet rset=(ResultSet)resultSetVector.get(i);
                        try
                        {
                            rset.close();
                        }
                        catch(SQLException e)
                        {
                                throw new SQLException(e.getMessage());
                        }
                    }
                }

                if(!statementVector.isEmpty())
                {
                    //ok close all statements once more
                    int stmtSize = statementVector.size();
                    for(int i=0;i<stmtSize;i++)
                    {
                        CallableStatement cstmt=(CallableStatement)statementVector.get(i);
                        try
                        {
                            cstmt.close();
                        }
                        catch(SQLException e)
                        {
                            //ignore this
                        }
                    }
                }

                mConnection.close();
                synchronized (lockObject) {
                    closeCount++;                   
                }
            }
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
               throw new Exception(ex.getMessage());
        }
    }




} //end of Rconnection

下面是道类

 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.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{USERDETAILS_REPORT,SOMEOTHER_REPORT};
    public final static int NO_OF_RECORDS=6;

    public Map<Integer, TreeMap<String,String>> 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;
        try {
            stmt = connection.getDBConnection().createStatement();
            rs = getReportRecords(cstmt,startDate,endDate,reportType);
            Map<Integer, TreeMap<String,String>> values= getFormattedData(rs);
            BatchJLogger.logMessage(" No of records fetched  "+values.size() );
            BatchJLogger.logMessage(" End Execution of method getDataForRepot " );
            return values;

        }  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 ResultSet getReportRecords(CallableStatement cstmt,String startDate, String endDate,String reportType)
            throws SQLException,Exception {

        ResultSet rs = null;
        try{
            BatchJLogger.logMessage(" Started Execution of method getReportRecords ");

            String procedure = "{call SOME_PROC (?,?,?,?)}";
            cstmt = connection.createCallableStatement(procedure);
            int procId=7;
            switch (ReportType.valueOf(reportType)) {
            case USERDETAILS_REPORT:
                cstmt.setInt(1, 7);
                break;
            case SOMEOTHER_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.execute();
            rs = connection.getResultSet(cstmt, 4);
            BatchJLogger.logMessage(" End Execution of method getReportRecords ");
        }catch (Exception e) {
            e.printStackTrace();
            connection.close();
        }finally{
            return rs;  
        }


    }

    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 BATCH where SOME_ID=? and RUN_ID=(Select MAX(RUN_ID) "
                + "from BATCH where SOME_ID=?)";
            PreparedStatement pstmt = connection.getDBConnection()
            .prepareStatement(deleteRecord);
            switch (ReportType.valueOf(reportName)) {
            case USERDETAILS_REPORT:
                pstmt.setInt(1, 7);
                pstmt.setInt(2, 7);
                break;
            case SOMEOTHER_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();
        }

    }

}

Answer 1

1. 由于应用程序无法从大多数不应该捕获和忽略(eat)异常的异常中恢复，而是捕获它，打印堆栈跟踪并将其传递给它。
2. NullPointerException String[] reportsList = args0.split("，")；当您不为应用程序提供任何参数(args.length == 0 -> args0 == NULL)时，它将引发NullPointerException
3. 最后，不需要finally块{ System.exit(0)；在默认情况下，一旦到达main结束时，应用程序将返回0返回代码，因此不需要此代码。
4. 不传递异常原因会抛出新的SQLException(“不能为sql创建CallableStatement ")；当您用新异常包装捕获的异常(在本例中是这样)时，您应该将捕获的异常传递到新异常的构造函数中，否则将失去异常的实际原因。
5. 关闭不在finally块中的JDBC对象rs = getReportRecords(cstmt，reportType)；rs.next (rs.next()) { str =新字符串6；for (int i= 0；i< 6；i++) { str我 = rs.getString(i + 1)；} vecDetails.addElement(str)；} if (rs != null) rs.close()；if (stmt !=空) stmt.close()；您的代码不能保证始终关闭ResultSet和语句对象。应该从close块调用finally方法。
6. 在非多线程代码中，应该首选Vector而不是ArrayList ArrayList而不是Vector。
7. 不使用泛型。
8. 不必要的转换字符串str[] = (String[]) null；
9. 魔术数字str =新字符串6；硬编码魔术数字的使用应该尽可能减少，在不可能的情况下，应该将它们定义为final static，并给出有意义的名称。
10. 捕获NullPointerException }catch (NullPointerException e) {抛出新的SQLException(errorString+)：“+e.getMessage()；}您的代码不应该捕获未检查的NullPointerException (指示编程错误)并抛出检查SQLException (指示数据库问题)。

这不是一个完整的清单，我认为只有当这几个问题是固定的，我们才能开始设计这个应用程序。

您可以使用以下工具之一：

• 发现虫
• PMD
• 校验方式

进一步提高代码的质量

Answer 2

Adam已经做得很好了，但以下是一些其他的评论：

1) ResultSets缓存在resultSetVector中。这可能会导致非常严重的内存泄漏。我必须承认我没有阅读和理解所有的东西，但是缓存ResultSets似乎很奇怪。

( 2)不使用仿制药和矢量药确实显得过时了。然而，这段代码可能是线程安全的，而有些向量的代码可能不能被列表的代码所替代。如果RConnection本来是这样的话，它需要更多的工作，因为它远离线程安全，尽管有向量和lockObject。