解答：关于邮件统计与下载的题目

配置文件 web.xml

<? xml version = " 1.0 "  encoding = " UTF-8 " ?>
< web - app id = " WebApp_ID "  version = " 2.4 "  xmlns = " http://java.sun.com/xml/ns/j2ee "  xmlns:xsi = " http://www.w3.org/2001/XMLSchema-instance "  xsi:schemaLocation = " http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd " >
     < display - name > StatisticProj </ display - name >
     < welcome - file - list >
         < welcome - file > index.html </ welcome - file >
         < welcome - file > index.htm </ welcome - file >
         < welcome - file > index.jsp </ welcome - file >
         < welcome - file > default .html </ welcome - file >
         < welcome - file > default .htm </ welcome - file >
         < welcome - file > default .jsp </ welcome - file >
     </ welcome - file - list >
     < listener >
         < listener - class >
            org.springframework.web.context.ContextLoaderListener
         </ listener - class >
     </ listener >
     < context - param >
         < param - name > contextConfigLocation </ param - name >
         < param - value >
             / WEB - INF / beans.xml
         </ param - value >
     </ context - param >
    <!--========================================================================
        Servlets
     =========================================================================-->
     < servlet >
         < servlet - name > dispatcher </ servlet - name >
         < servlet - class > org.springframework.web.servlet.DispatcherServlet </ servlet - class >
         < load - on - startup > 0 </ load - on - startup >
     </ servlet >

     < servlet - mapping >
         < servlet - name > dispatcher </ servlet - name >
         < url - pattern >* . do </ url - pattern >
     </ servlet - mapping >
</ web - app >

beans.xml

 

<? xml version = " 1.0 "  encoding = " UTF-8 " ?>
< beans xmlns = " http://www.springframework.org/schema/beans "
       xmlns:xsi = " http://www.w3.org/2001/XMLSchema-instance "
       xsi:schemaLocation = " http://www.springframework.org/schema/beans
       http: // www.springframework.org/schema/beans/spring-beans.xsd"
>
<!--  
   < bean id = " dataSource "   class = " org.springframework.jdbc.datasource.DriverManagerDataSource " >
         < property name = " driverClassName "  value = " oracle.jdbc.OracleDriver "   />
         < property name = " url "  value = " jdbc:oracle:thin:@127.0.0.1:1521:metas "   />
         < property name = " username "  value = " scott "   />
         < property name = " password "  value = " tiger "   />
   </ bean >
-->
<!--  datasource as dbcp  -->
   < bean id = " dataSource "
         class = " org.apache.commons.dbcp.BasicDataSource "
        destroy - method = " close " >
         < property name = " driverClassName "  value = " oracle.jdbc.OracleDriver "   />
         < property name = " url "  value = " jdbc:oracle:thin:@127.0.0.1:1521:metas "   />
         < property name = " username "  value = " scott "   />
         < property name = " password "  value = " tiger "   />
         < property name = " defaultAutoCommit "  value = " true "   />
   </ bean >     
   <!--  jdbc TransactionManager  -->
   < bean id = " transactionManager "   class = " org.springframework.jdbc.datasource.DataSourceTransactionManager " >
     < property name = " dataSource "  ref = " dataSource " ></ property >
   </ bean >
   <!--  mailDAO as target  -->
   < bean id = " mailDAO_targ "   class = " org.metas.dao.impl.MailDAOImpl " >
         < property name = " sqlMapClient " >
             < bean  class = " org.springframework.orm.ibatis.SqlMapClientFactoryBean " >
                 < property name = " configLocation "  value = " /WEB-INF/ibatis-sql-map-config.xml "   />
                 < property name = " dataSource "  ref = " dataSource "   />
             </ bean >
         </ property >
   </ bean >
   <!--  mailDAO from transaction proxy  -->
   < bean id = " mailDAO "  
           class = " org.springframework.transaction.interceptor.TransactionProxyFactoryBean " >
             < property name = " transactionManager " >
                 < ref local = " transactionManager "   />
             </ property >
             < property name = " proxyInterfaces " >
                 < list >
                     < value > org.metas.dao.MailDAO </ value >
                 </ list >
             </ property >
             < property name = " target " >
                 < ref local = " mailDAO_targ "   />
             </ property >
             < property name = " transactionAttributes " >
                 < props >
                     < prop key = " query " > PROPAGATION_REQUIRED, readOnly </ prop >
                 </ props >
             </ property >
   </ bean >
   <!--  excel file exporter  -->
   < bean id = " exporter "   class = " org.metas.web.query.exporter.impl.XlsExporter " >
     < property name = " mailDAO "  ref = " mailDAO " ></ property >
   </ bean >
   <!--  mail list paginate  -->
   < bean id = " mailPaginate "   class = " org.metas.dao.result.impl.PaginateParameterImpl " >
      < constructor - arg >
        < value > 2 </ value >
      </ constructor - arg >
      < constructor - arg >
        < value > select  *  from mail </ value >
      </ constructor - arg >
   </ bean >
</ beans >

dispatcher-servlet.xml

<? xml version = " 1.0 "  encoding = " UTF-8 " ?>
< beans xmlns = " http://www.springframework.org/schema/beans "
       xmlns:xsi = " http://www.w3.org/2001/XMLSchema-instance "
       xsi:schemaLocation = " http://www.springframework.org/schema/beans
       http: // www.springframework.org/schema/beans/spring-beans.xsd"
>
     < import  resource = " beans.xml " />
     <!-- using beanName urlMapping format  -->
     < bean id = " beanNameUrlMapping "
         class = " org.springframework.web.servlet.handler.BeanNameUrlHandlerMapping " >
         < property name = " alwaysUseFullPath "  value = " true "   />
     </ bean >
     <!--  JSP render  -->
     < bean id = " viewResolver "   class = " org.springframework.web.servlet.view.InternalResourceViewResolver " >
       < property name = " viewClass "  value = " org.springframework.web.servlet.view.JstlView " />
       < property name = " suffix "  value = " .jsp " ></ property >
     </ bean >
     <!--  beanName mapping set  -->
     <!--  analysis result controller  -->
     < bean name = " /query.do "
         class = " org.metas.web.query.StatisticController " >
         < property name = " mailDAO " >
          < ref bean = " mailDAO " />
         </ property >
     </ bean >
     <!--  mail list controller  -->
     < bean name = " /mailList.do "
         class = " org.metas.web.query.MailListController " >
         < property name = " mailDAO " >
          < ref bean = " mailDAO " />
         </ property >
         <!--  pagination reference  -->
         < property name = " paginate " >
          < ref bean = " mailPaginate " />
         </ property >
     </ bean >
     <!--  view customer list controller  -->
     < bean name = " /viewDetail.do "
         class = " org.metas.web.query.ViewCustomersController " >
         < property name = " mailDAO " >
          < ref bean = " mailDAO " />
         </ property >
     </ bean >
     <!--  excel file exporting controller  -->
     < bean name = " /export.do "
         class = " org.metas.web.query.DownloadController " >
         < property name = " exporter " >
          < ref bean = " exporter " />
         </ property >
     </ bean >
</ beans >

ibatis-sql-map-config.xml

<? xml version = " 1.0 "  encoding = " UTF-8 " ?>
<! DOCTYPE sqlMapConfig
    PUBLIC  " -//ibatis.apache.org//DTD SQL Map Config 2.0//EN "
     " http://ibatis.apache.org/dtd/sql-map-config-2.dtd " >
< sqlMapConfig >
     < settings cacheModelsEnabled = " true "
        enhancementEnabled = " true "
        lazyLoadingEnabled = " true "
        maxRequests = " 128 "
        maxSessions = " 10 "
        maxTransactions = " 5 "
        useStatementNamespaces = " false "
        defaultStatementTimeout = " 5 "
     />
     < sqlMap resource = " ibatis-mail.xml "   />
</ sqlMapConfig >

ibatis-mail.xml （位于classpath root）

<? xml version = " 1.0 "  encoding = " UTF-8 " ?>
<! DOCTYPE sqlMap
    PUBLIC  " -//ibatis.apache.org//DTD SQL Map 2.0//EN "
     " http://ibatis.apache.org/dtd/sql-map-2.dtd " >
< sqlMap namespace = " mail " >     
     < typeAlias alias = " ResultItem "  type = " org.metas.dao.result.ResultItem "   />
     < typeAlias alias = " CustomerItem "  type = " org.metas.dao.result.CustomerItem "   />
     < typeAlias alias = " MailItem "  type = " org.metas.dao.result.MailItem "   />
     < typeAlias alias = " QryCondition "  type = " org.metas.web.query.condition.QryCondition "   />
     < resultMap id = " mailItem-map "   class = " MailItem " >
          < result property = " mailNo "  column = " MAIL_NO " />
          < result property = " linkedEvent "  column = " LINKED_EVENT " />
          < result property = " sendFrom "  column = " SEND_FROM " />
          < result property = " cc "  column = " CC " />
          < result property = " sendTime "  column = " SEND_TIME " />
     </ resultMap >
     < select id = " query "  parameterClass = " QryCondition "  resultClass = " ResultItem "   >
        select b.invite_count as inviteCnt,b.event_no as eventCode, b.title as eventTitle, count(nvl(a.send_from, 0 )) as responseCnt from mail a, event b where a.linked_event = b.event_no and a.send_time between to_date(#from#, ' YYYY-MM-DD ' ) and to_date(#to#, ' YYYY-MM-DD ' )  group by b.invite_count, b.event_no, b.title  order by b.event_no
     </ select >     
     < select id = " queryCustomer "  parameterClass = " QryCondition "  resultClass = " CustomerItem "   >
        select c.name as name,c.email as email from customer c, mail a where a.linked_event = #eventNo# and a.send_time between to_date(#from#, ' YYYY-MM-DD ' ) and to_date(#to#, ' YYYY-MM-DD ' ) and c.email = a.send_from
     </ select >
     < select id = " totalCount "  resultClass = " int " >
       SELECT count( * ) as totalCnt FROM mail
     </ select >     
     < select id = " queryResultItemByPage "
     parameterClass = " org.metas.dao.result.impl.PaginateParameterImpl "  
     resultClass = " MailItem "
     resultMap = " mailItem-map "
      >
        <! [CDATA[
        SELECT  *  FROM
        (
           SELECT r. * , ROWNUM as row_number FROM
             ( $querySQL$ ) r
           WHERE ROWNUM   <  #endRow#
         ) WHERE #startRow#  <=  row_number
        ]] >
     </ select >
</ sqlMap >

包结构:

org.metas.dao
org.metas.dao.MailDAO.java
org.metas.dao.impl
org.metas.dao.impl.MailDAOImpl.java
org.metas.dao.result
org.metas.dao.result.CustomerItem.java
org.metas.dao.result.MailItem.java
org.metas.dao.result.PaginateParameter.java
org.metas.dao.result.ResultItem.java
org.metas.dao.result.impl
org.metas.dao.result.impl.PaginateParameterImpl.java
org.metas.web.query
org.metas.web.query.DownloadController.java
org.metas.web.query.MailListController.java
org.metas.web.query.StatisticController.java
org.metas.web.query.ViewCustomersController.java
org.metas.web.query.condition
org.metas.web.query.condition.QryCondition.java
org.metas.web.query.condition.impl
org.metas.web.query.condition.impl.QryConditionImpl.java
org.metas.web.query.exporter
org.metas.web.query.exporter.ViewExporter.java
org.metas.web.query.exporter.impl
org.metas.web.query.exporter.impl.XlsExporter.java

接口：

/** */ /**
 * interface for accessing the mail data and the linked data 
 */
package  org.metas.dao;

import  java.util.List;

import  org.metas.dao.result.CustomerItem;
import  org.metas.dao.result.MailItem;
import  org.metas.dao.result.PaginateParameter;
import  org.metas.dao.result.ResultItem;
import  org.metas.web.query.condition.QryCondition;


/** */ /**
 * @author wzh
 *
 */
public   interface  MailDAO  ... {
    /** *//**
     * get analysis result by a query with condition
     * @param condition QryCondition
     * @return analysis result Item
     */
   public List<ResultItem> query(QryCondition condition);
   /** *//**
    * get mail-list in special page
    * @param params paginate parameter
    * @return list as elements of MailItem
    */
   public List<MailItem> queryByPage(PaginateParameter params);
   /** *//**
    * query customer by QryCondition
    * @param condition
    * @return list as elements of CustomerItem
    */
   public List<CustomerItem> queryCustomer(QryCondition condition);
   /** *//**
    * get total mail count from db
    * @param parameter
    * @return total mail count
    */
   public Integer getTotalCount(PaginateParameter parameter);
}

 

package  org.metas.dao.result;

public   interface  PaginateParameter  ... {
    /** *//**
     * get item count per-page
     * @return
     */
  public int getPageSize();
  /** *//**
   * set item count per-page
   * @param size
   */
  public void setPageSize(int size);
  /** *//**
   * get current page number
   * @return
   */
  public int getCurrentPageNo();
  /** *//**
   * set current page number
   * @param current
   */
  public void setCurrentPageNo(int current);
  /** *//**
   * get total item count in all pages
   * @return
   */
  public int getTotalItemCnt();
  /** *//**
   * set total item count
   * @param cnt
   */
  public void setTotalItemCnt(int cnt);
  /** *//**
   * count the page 
   * @return
   */
  public int getPageCnt();
  /** *//**
   * source query SQL for pagination
   * @return
   */
  public String getQuerySQL();
  /** *//**
   * get the start paging number
   * @return
   */
  public int getStartRow();
  /** *//**
   * get the end paging number
   * @return
   */
  public int getEndRow();
  /** *//**
   * mapping for the disabled attribute of next page button for HTML
   * @return
   */
  public boolean isNextDisabled();
  /** *//**
   * mapping for the disabled attribute of previous button for HTML
   * @return
   */
  public boolean isPrevDisabled();
  /** *//**
   * mapping for the disabled attribute of first button for HTML
   * @return
   */
  public boolean isFirstDisabled();
  /** *//**
   * mapping for the disabled attribute of last button for HTML
   * @return
   */
  public boolean isLastDisabled();
}

 

package org.metas.web.query.condition;

public   interface  QryCondition  ... {
    /**//**
     * event no
     * @return
     */
    public Integer getEventNo();
    /**//**
     * from date as String
     * @return
     */
    public String getFrom();
    /**//**
     * to date as String
     * @return
     */
    public String getTo();
}

 

package org.metas.web.query.exporter;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public   interface  ViewExporter  ... {
    /**//**
     * exporting render
     * @param request
     * @param response
     * @throws Exception
     */
   public void process(HttpServletRequest request, HttpServletResponse response)throws Exception;
}

 

spring controller:

package org.metas.web.query;

import java.util.HashMap;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.metas.dao.MailDAO;
import org.metas.web.query.condition.QryCondition;
import org.metas.web.query.condition.impl.QryConditionImpl;
import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.servlet.mvc.Controller;

/**/ /**
 * @author wzh
 *
 */
public   class  StatisticController implements Controller  ... {
    private MailDAO mailDAO;
    public MailDAO getMailDAO() ...{
        return mailDAO;
    }
    public void setMailDAO(MailDAO mailDAO) ...{
        this.mailDAO = mailDAO;
    }
    public ModelAndView handleRequest(HttpServletRequest request, HttpServletResponse response) throws Exception...{
        Map map = new HashMap();
        String from = request.getParameter("from");
        String to = request.getParameter("to");
        QryCondition condition = new QryConditionImpl(from, to);
        map.put("result",this.mailDAO.query(condition));
        map.put("totalCount", this.mailDAO.getTotalCount(null));
        map.put("QryCondition",condition);
        return new ModelAndView("/index",map);
    }
}

 

package org.metas.web.query;

import java.util.HashMap;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.metas.dao.MailDAO;
import org.metas.dao.result.PaginateParameter;
import org.springframework.web.bind.ServletRequestUtils;
import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.servlet.mvc.Controller;

/**/ /**
 * @author wzh
 *
 */
public   class  MailListController implements Controller  ... {
    private MailDAO mailDAO;
    private PaginateParameter paginate;
    public PaginateParameter getPaginate() ...{
        return paginate;
    }
    public void setPaginate(PaginateParameter paginate) ...{
        this.paginate = paginate;
    }
    public MailDAO getMailDAO() ...{
        return mailDAO;
    }
    public void setMailDAO(MailDAO mailDAO) ...{
        this.mailDAO = mailDAO;
    }
    public ModelAndView handleRequest(HttpServletRequest request, HttpServletResponse response) throws Exception...{
        Map map = new HashMap();
        int pageNo = ServletRequestUtils.getIntParameter(request, "pageNo", 0);
        this.paginate.setCurrentPageNo(pageNo);
        this.paginate.setTotalItemCnt(this.mailDAO.getTotalCount(null).intValue());
        map.put("paginate", this.paginate);
        map.put("result",
                this.mailDAO.queryByPage(this.paginate)
               );
        return new ModelAndView("/mail_list",map);
    }
}

 

 

package org.metas.web.query;


import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.metas.web.query.exporter.ViewExporter;
import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.servlet.mvc.Controller;

/**/ /**
 * @author wzh
 *
 */
public   class  DownloadController implements Controller  ... {
    private ViewExporter exporter;
    
    public ModelAndView handleRequest(HttpServletRequest request, HttpServletResponse response) throws Exception...{
        response.reset();
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition","attachment;filename=statistic_result.xls");
        exporter.process(request, response);
        return new ModelAndView();
    }

    public ViewExporter getExporter() ...{
        return exporter;
    }

    public void setExporter(ViewExporter exporter) ...{
        this.exporter = exporter;
    }
}

 

/**/ /**
 * 
 */
package org.metas.web.query;

import java.util.HashMap;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.metas.dao.MailDAO;
import org.metas.web.query.condition.impl.QryConditionImpl;
import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.servlet.mvc.Controller;

/**/ /**
 * @author wzh
 *
 */
public   class  ViewCustomersController implements Controller  ... {
    private MailDAO mailDAO;
    public MailDAO getMailDAO() ...{
        return mailDAO;
    }
    public void setMailDAO(MailDAO mailDAO) ...{
        this.mailDAO = mailDAO;
    }
    public ModelAndView handleRequest(HttpServletRequest request, HttpServletResponse response) throws Exception...{
        Map map = new HashMap();
        map.put("result",
                this.mailDAO.queryCustomer(
                        new QryConditionImpl(new Integer(request.getParameter("eventNo")),
                                request.getParameter("from"),
                                request.getParameter("to"))
                        )
                );
        return new ModelAndView("/view_details",map);
    }
}