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

最新推荐文章于 2025-03-01 17:00:00 发布

原创最新推荐文章于 2025-03-01 17:00:00 发布 · 745 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#servlet #class #bean #constructor #hashmap #encoding

本文介绍了一个使用 Spring MVC 实现的数据分页及 Excel 导出的应用案例。通过整合 Spring、MyBatis（iBatis）、SpringMVC 等技术，实现了基于条件查询的分页展示邮件列表，并提供了 Excel 导出功能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

配置文件 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-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-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>

-->

<bean id="dataSource"

class="org.apache.commons.dbcp.BasicDataSource"

destroy-method="close">

</bean>

</bean>

</bean>

</property>

</bean>

<bean id="mailDAO"

class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean">

</property>

<list>

<value>org.metas.dao.MailDAO</value>

</list>

</property>

</property>

<props>

<prop key="query">PROPAGATION_REQUIRED, readOnly</prop>

</props>

</property>

</bean>

</bean>

<constructor-arg>

</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"

<bean id="beanNameUrlMapping"

class="org.springframework.web.servlet.handler.BeanNameUrlHandlerMapping">

</bean>

</bean>

<bean name="/query.do"

class="org.metas.web.query.StatisticController">

</property>

</bean>

<bean name="/mailList.do"

class="org.metas.web.query.MailListController">

</property>

</property>

</bean>

<bean name="/viewDetail.do"

class="org.metas.web.query.ViewCustomersController">

</property>

</bean>

<bean name="/export.do"

class="org.metas.web.query.DownloadController">

</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">

<settings cacheModelsEnabled="true"

enhancementEnabled="true"

lazyLoadingEnabled="true"

maxRequests="128"

maxSessions="10"

maxTransactions="5"

useStatementNamespaces="false"

defaultStatementTimeout="5"

</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">

</resultMap>

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 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 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);

}