实现销售榜单下载功能

下载过程

将已销售商品的信息，按照商品销量从高到低排序后，通过响应输出流送到浏览器，再保存到本地磁盘的扩展名为“.csv”的文件中。

创建下载页面

<form id="Form1" name="Form1" action="${pageContext.request.contextPath}/download" method="post">                  
	<table cellSpacing="1" cellPadding="0" width="100%" align="center" bgColor="#f5fafe" border="0">               
		<tbody>                                                                                                    
			<tr>                                                                                                   
				<td class="ta_01" align="center" bgColor="#afd1f3">                                                
					<strong>查 询 条 件</strong>                                                                       
				</td>                                                                                              
			</tr>                                                                                                  
			<tr>                                                                                                   
				<td>                                                                                               
					<table cellpadding="0" cellspacing="0" border="0" width="100%">                                
						<tr>                                                                                       
							<td height="22" align="center" bgColor="#f5fafe" class="ta_01">                        
								请输入年份                                                                              
							</td>                                                                                  
							<td class="ta_01" bgColor="#ffffff">                                                   
								<input type="text" name="year" size="15" value="" id="Form1_userName" class="bg" />
							</td>                                                                                  
							<td height="22" align="center" bgColor="#f5fafe" class="ta_01">                        
								请选择月份                                                                              
							</td>                                                                                  
							<td class="ta_01" bgColor="#ffffff">                                                   
								<select name="month" id="month">                                                   
									<option value="0">--选择月份--</option>                                            
									<option value="1">一月</option>                                                  
									<option value="2">二月</option>                                                  
									<option value="3">三月</option>                                                  
									<option value="4">四月</option>                                                  
									<option value="5">五月</option>                                                  
									<option value="6">六月</option>                                                  
									<option value="7">七月</option>                                                  
									<option value="8">八月</option>                                                  
									<option value="9">九月</option>                                                  
									<option value="10">十月</option>                                                 
									<option value="11">十一月</option>                                                
									<option value="12">十二月</option>                                                
								</select>                                                                          
							</td>                                                                                  
						</tr>                                                                                      
						<tr>                                                                                       
							<td width="100" height="22" align="center" bgColor="#f5fafe" class="ta_01">            
							</td>                                                                                  
							<td class="ta_01" bgColor="#ffffff">                                                   
								<font face="宋体" color="red"> &nbsp;</font>                                         
							</td>                                                                                  
							<td align="right" bgColor="#ffffff" class="ta_01">                                     
								<br /><br />                                                                       
							</td>                                                                                  
							<td align="center" bgColor="#ffffff" class="ta_01">                                    
								<input type="submit" id="search" name="search" value="下载" class="button_view">     
									&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;                                                 
								<input type="reset" name="reset" value="重置" class="button_view" />                 
							</td>                                                                                  
						</tr>                                                                                      
					</table>                                                                                       
				</td>                                                                                              
			</tr>                                                                                                  
		</tbody>                                                                                                   
	</table>                                                                                                       
</form>                                                                                                            

创建Servlet

public class DownloadServlet extends HttpServlet {
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doPost(request, response);
	}
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		String year = request.getParameter("year");
		String month = request.getParameter("month");
		ProductService service = new ProductService();
		List<Object[]> ps = service.download(year,month);
		String fileName=year+"年"+month+"月销售榜单.csv";	
		response.setContentType(this.getServletContext().getMimeType(fileName));
		response.setHeader("Content-Disposition", "attachement;filename="+new String(fileName.getBytes("GBK"),"iso8859-1"));		
		response.setCharacterEncoding("gbk");		
		PrintWriter out = response.getWriter();
		out.println("商品名称,销售数量");
		for (int i = 0; i < ps.size(); i++) {
			Object[] arr=ps.get(i);
			out.println(arr[0]+","+arr[1]);			
		}
		out.flush();
		out.close();
	}
}

编写Service层代码

// 下载销售榜单
	public List<Object[]> download(String year, String month) {
		List<Object[]> salesList = null;
		try {
			salesList = dao.salesList(year, month);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return salesList;
	}

编写DAO层的代码

// 销售榜单                                                                   
public List<Object[]> salesList(String year, String month)                
		throws SQLException {                                             
	String sql = "SELECT products.name,SUM(orderitem.buynum) totalsalnum "
			+ "FROM orders,products,orderItem "                           
			+ "WHERE orders.id=orderItem.order_id "                       
			+ "AND products.id=orderItem.product_id "                     
			+ "AND orders.paystate=1 "                                    
			+ "and year(ordertime)=? "                                    
			+ "and month(ordertime)=? "                                   
			+ "GROUP BY products.name "                                   
			+ "ORDER BY totalsalnum DESC";                                
	QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
	return runner.query(sql, new ArrayListHandler(), year, month);        
}