Javaweb 将excel文件内信息传入数据库中并查询

待我称王封你为后i 2022-08-08 00:50 124阅读 0赞

有一个用户需求，即是用户进行登录后，可以上传excel文件，并对上传的excel表进行查询

简单分析下后

根据分析，先导入要用到的jar包（有的jar包javaEE5.0自带了的，就没写出来了）：

c3p0-0.9.1.2.jar                                      //数据库连接池  
commons-dbutils-1.4.jar                     //javabean操作时的神器  
commons-fileupload-1.2.1.jar            //文件上传  
commons-io-1.4.jar                              //文件上传依附的  
jxl.jar                                                         //对excel文件的解析神器  
mysql-connector-java-5.0.8-bin.jar    //mysql的连接包

如有需要，直接在网上搜下，下载就可，这东西，网上一大堆。。。

然后直接开写：

首先是用户登录的jsp页面

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
    <html>
      <head>    
        <title>登录</title>
      </head>
      
      <body>
    	<form action="${pageContext.request.contextPath}/servlet/loginServlet" method="post">
    		用户名：<input name="username" type="text"><br>
    		密码：<input name="password" type="password"><br>
    		<input type="submit" value="登录">
    	</form>
      </body>
    </html>

然后loginServlet的认证处理：

package com.baoxiu123.service;
    
    import java.io.IOException;
    import java.sql.SQLException;
    
    import javax.servlet.ServletException;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    import org.apache.commons.dbutils.QueryRunner;
    import org.apache.commons.dbutils.handlers.BeanHandler;
    
    import com.baoxiu123.domain.User;
    import com.baoxiu123.utils.DaoUtils;
    
    public class loginServlet extends HttpServlet {
    
    	public void doGet(HttpServletRequest request, HttpServletResponse response)
    			throws ServletException, IOException {
    		String username = request.getParameter("username");
    		String password = request.getParameter("password");
    				
    		QueryRunner runner = new QueryRunner(DaoUtils.getSource());
    		User user = null;
    		try {
    			user = runner.query("select * from user where username =? and password = ?",new BeanHandler<User>(User.class),username,password);
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}
    		if(user!=null){
    			System.out.println("登录成功");
    			request.getSession().setAttribute("user", user);
    			response.sendRedirect(request.getContextPath()+"/fileupload.jsp");
    		}else{
    			System.out.println("登录失败");
    			return;
    		}
    		
    		
    	}
    
    	public void doPost(HttpServletRequest request, HttpServletResponse response)
    			throws ServletException, IOException {
    		doGet(request, response);
    	}
    
    }

其中用到的DaoUtils源码：

package com.baoxiu123.utils;
    
    import java.sql.Connection;
    import java.sql.SQLException;
    
    import javax.sql.DataSource;
    
    import com.mchange.v2.c3p0.ComboPooledDataSource;
    
    public class DaoUtils {
    	private DaoUtils(){};
    	private static ComboPooledDataSource source = new ComboPooledDataSource();  
    	public static DataSource getSource(){
    		return source;
    	}
    	public static Connection getConn(){
    		try {
    			return source.getConnection();
    		} catch (SQLException e) {
    			e.printStackTrace();
    			throw new RuntimeException("获取连接错误");
    		}
    	}
    }

这样就可以完成用户的登录了

登录后会跳转到fileupload.jsp

fileupload.jsp源码如下：

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
    <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
    <html>
      <head>    
        <title>Excel文件上传</title>
      </head>
      
      <body>
        这里是文件上传页面
        <c:if test="${sessionScope.user == null}">
      		欢迎光临，游客
      	</c:if>
        <c:if test="${sessionScope.user != null}">
        	在这里您可以上传文件
        	<form action="${pageContext.request.contextPath }/servlet/UploadServlet" method="post" enctype="multipart/form-data">
        	   
        	    在此上传excel文件:<input type="file"  name="queryexcel">
        		<input type="submit" value="上传" />
        	</form>
        	
        	<br>
        	欢迎光临，${sessionScope.user.username }
        </c:if>
      </body>
    </html>

那么对于文件上传的UploadServlet源码如下：

package com.baoxiu123.service;
    
    import java.io.File;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.OutputStream;
    import java.util.List;
    
    import javax.servlet.ServletException;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    import org.apache.commons.fileupload.FileItem;
    import org.apache.commons.fileupload.disk.DiskFileItemFactory;
    import org.apache.commons.fileupload.servlet.ServletFileUpload;
    
    import com.baoxiu123.utils.IoUitls;
    
    public class UploadServlet extends HttpServlet {
    
    	public void doGet(HttpServletRequest request, HttpServletResponse response)
    			throws ServletException, IOException {
    		response.setContentType("text/html;charset=utf-8");
    
    		try{
    			DiskFileItemFactory factory = new DiskFileItemFactory(1024*8, new File(
    					this.getServletContext().getRealPath("/WEB-INF/temp")));
    	
    			ServletFileUpload fileUpload = new ServletFileUpload(factory);
    			fileUpload.setHeaderEncoding("utf-8");
    			List<FileItem> fileitem = fileUpload.parseRequest(request);
    			for(FileItem item : fileitem){
    				if(item.isFormField()){
    					System.out.println("这个不是文件");
    				}else{
    					//得到上传文件的文件名
    					String filename = item.getName();
    					//input得到
    					InputStream input = item.getInputStream();
    					//得到一个输出流
    					OutputStream output = new FileOutputStream(this.getServletContext().getRealPath("/WEB-INF/uploadfile/"+filename));
    					IoUitls.in2out(input, output);
    					IoUitls.close(input, output);
    					
    					//还需要删除临时文件
    					item.delete();
    					//删完后，提示上传成功了
    					response.getWriter().write("上传文件成功");
    					response.sendRedirect(request.getContextPath()+"/servlet/Add2databaseServlet?filename="+filename);
    				}
    			}
    			
    			
    		}catch (Exception e) {
    			e.printStackTrace();
    		}
    		
    	}
    
    	public void doPost(HttpServletRequest request, HttpServletResponse response)
    			throws ServletException, IOException {
    		doGet(request, response);
    	}
    
    }

ioUtils的源码如下：（对input和output的转换）

package com.baoxiu123.utils;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.io.OutputStream;
    
    public class IoUitls {
    	private IoUitls(){
    		
    	}
    	public static void in2out(InputStream in,OutputStream out) throws IOException{
    		byte[] bs = new byte[1024];
    		int i=0;
    		while((i=in.read(bs))!=-1){
    			out.write(bs, 0, i);
    		}
    	}
    	public static void close(InputStream in,OutputStream out) throws IOException{
    		if(in!=null){
    			in.close();
    		}
    		if(out!=null){
    			out.close();
    		}
    	}
    }

这样对于上传完后，就可以进行从excel到数据库的数据插入了

那么Add2databaseServlet的源码如下：

package com.baoxiu123.service;
    
    import java.io.FileInputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.ArrayList;
    import java.util.List;
    
    import javax.servlet.ServletException;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    import jxl.Cell;
    import jxl.Sheet;
    import jxl.Workbook;
    import jxl.read.biff.BiffException;
    
    import com.baoxiu123.dao.DataDao;
    import com.baoxiu123.domain.WaterInfo;
    import com.baoxiu123.factory.BasicFactory;
    
    public class Add2databaseServlet extends HttpServlet {
    
    	public void doGet(HttpServletRequest request, HttpServletResponse response)
    			throws ServletException, IOException {
    		
    		response.setContentType("text/html;charset=utf-8");
    		String file_name = request.getParameter("filename");
    		System.out.println(file_name);
    		if(file_name==null){
    			response.getWriter().write("解析文件错误");
    			return;
    		}
    		String filepath = "/WEB-INF/uploadfile/"+file_name;
    		filepath = getServletContext().getRealPath(filepath);
    		// 创建一个list 用来存储读取的内容
    		List list = new ArrayList();
    		Workbook rwb = null;
    		Cell cell = null;
    
    		// 创建输入流
    		InputStream stream = new FileInputStream(filepath);
    
    		// 获取Excel文件对象
    		try {
    			rwb = Workbook.getWorkbook(stream);
    		} catch (BiffException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    
    		// 获取文件的指定工作表 默认的第一个
    		Sheet sheet = rwb.getSheet(0);
    
    		// 行数(表头的目录不需要，从1开始)
    		for (int i = 5; i < sheet.getRows(); i++) {
    
    			// 创建一个数组 用来存储每一列的值
    			String[] str = new String[sheet.getColumns()];
    
    			// 列数
    			for (int j = 0; j < sheet.getColumns(); j++) {
    
    				// 获取第i行，第j列的值
    				cell = sheet.getCell(j, i);
    				str[j] = cell.getContents();
    
    			}
    			// 把刚获取的列存入list
    			list.add(str);
    		}
    		
    		//获取一个DataDao 准备用于向数据库中插入数据
    		DataDao dao = BasicFactory.getFactory().getInstance(DataDao.class);
    		WaterInfo water = new WaterInfo();
    		water.setFlag(1);
    		for (int i = 0; i < list.size(); i++) {
    			String[] str = (String[]) list.get(i);
    			for (int j = 0; j < str.length; j++) {
    				switch(j){
    				case 0:
    					water.setRoomnum(str[j]);
    					break;
    				case 1:
    					water.setRoomsum(Integer.valueOf(str[j]));
    					break;
    				case 2:
    					water.setStartnum(Integer.valueOf(str[j]));
    					break;
    				case 3:
    					water.setEndnum(Integer.valueOf(str[j]));
    					break;
    				case 4:
    					water.setUsenum(Integer.valueOf(str[j]));
    					break;
    				case 5:
    					water.setOutnum(Integer.valueOf(str[j]));
    					break;
    				case 6:
    					water.setOutmoney(Integer.valueOf(str[j]));
    					break;
    				case 7:
    					water.setOtherinfo(str[j]);
    					break;
    				}
    			}
    			dao.AddData(water);			
    		}
    		
    		response.getWriter().write("Excel文件数据解析成功!!");
    	}
    
    	public void doPost(HttpServletRequest request, HttpServletResponse response)
    			throws ServletException, IOException {
    		doGet(request, response);
    	}
    
    }

这里为了降低其耦合，用工厂来得到一个Dao

其factory源码如下：

package com.baoxiu123.factory;
    
    import java.io.FileReader;
    import java.util.Properties;
    
    import com.sun.org.apache.commons.collections.Factory;
    
    public class BasicFactory {
    	//单例设计模式
    	private static BasicFactory factory = new BasicFactory();
    	private static Properties prop = null;
    	private BasicFactory(){
    		
    	}
    	public static BasicFactory getFactory(){
    		return factory;
    	}
    	
    	static{
    		try{
    			prop = new Properties();
    			prop.load(new FileReader(BasicFactory.class.getClassLoader().getResource("config.properties").getPath()));
    		}catch (Exception e) {
    			e.printStackTrace();
    		}
    	}
    	
    	public <T> T getInstance(Class<T> clazz){
    		try{
    			String name = clazz.getSimpleName();
    			String fullname = prop.getProperty(name);
    			return (T) Class.forName(fullname).newInstance();
    		}catch (Exception e) {
    			e.printStackTrace();
    			throw new RuntimeException();
    		}
    	}
    }

在factory中用到的config.properties文件内容如下（也就是类所在的完整路径）：

DataDao=com.baoxiu123.dao.DataDao

从工厂中得到了Dao后便可以调用dao中的方法进行查询了

然后是查询的dao:

package com.baoxiu123.dao;
    
    import java.util.List;
    
    import org.apache.commons.dbutils.QueryRunner;
    import org.apache.commons.dbutils.handlers.BeanHandler;
    import org.apache.commons.dbutils.handlers.BeanListHandler;
    
    import com.baoxiu123.domain.WaterInfo;
    import com.baoxiu123.utils.DaoUtils;
    
    public class DataDao {	
    	/**
    	 * 将excel文件中的信息添加到数据库中去
    	 */
    	public void AddData(WaterInfo water){
    		String sql = "insert into water values (null,?,?,?,?,?,?,?,?,?)";
    		try{
    			QueryRunner runner = new QueryRunner(DaoUtils.getSource());
    			runner.update(sql, water.getRoomnum(),water.getRoomsum(),water.getStartnum(),water.getEndnum(),water.getUsenum(),water.getOutnum(),water.getOutmoney(),water.getOtherinfo(),water.getFlag());
    		}catch (Exception e) {
    			e.printStackTrace();
    			throw new RuntimeException(e);
    		}
    	}
    	
    	/**
    	 * 从数据库中查询数据,并返回一个寝室的信息
    	 */
    	public WaterInfo QueryData(String roomnum){
    		String sql="select * from water where roomnum = ?"; 
    		try{
    			QueryRunner runner = new QueryRunner(DaoUtils.getSource());
    			return runner.query(sql, new BeanHandler<WaterInfo>(WaterInfo.class),roomnum);
    		}catch (Exception e) {
    			e.printStackTrace();
    			throw new RuntimeException();
    		}
    	}
    }

就这样，就可以完成查询了。

再写个调用查询的servlet就可以了

package com.baoxiu123.web;
    
    import java.io.IOException;
    import java.util.List;
    
    import javax.servlet.ServletException;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    import com.baoxiu123.dao.DataDao;
    import com.baoxiu123.domain.WaterInfo;
    import com.baoxiu123.factory.BasicFactory;
    
    public class GetWater extends HttpServlet {
    
    	public void doGet(HttpServletRequest request, HttpServletResponse response)
    			throws ServletException, IOException {
    		//从传入的参数中获取房间号
    		String roomnum = request.getParameter("roomnum");	
    		DataDao dao = BasicFactory.getFactory().getInstance(DataDao.class);
    		WaterInfo water = dao.QueryData(roomnum);
    		String waterStr = water.toString();
    		response.getWriter().write(waterStr);
    		
    	}
    
    	public void doPost(HttpServletRequest request, HttpServletResponse response)
    			throws ServletException, IOException {
    		doGet(request, response);
    	}
    
    }

当然，由于里面用到了c3p0这个连接池，那么一定少不了它的配置文件

c3p0-config.xml 里的内容如下：

<?xml version="1.0"?>
    <c3p0-config>
      <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql:///test</property>
        <property name="user">username</property>
        <property name="password">password</property>
      </default-config>
    </c3p0-config>

还有两个JavaBean没有写出来，这里就不多写了！！！！

经过我的测试，在浏览器中输入

http://localhost:8080/study/servlet/GetWater?roomnum=1202

得到的结果如下（由于提前在JavaBean中进行了对这个类的toString()方法的复写，为了其它解析的方便，将其写成了json的样子）：

\[\{"id":"2", "roomnum":"1202", "roomsum":"4", "startnum":"6.0", "endnum":"92.0", "usenum":"86.0", "outnum":"0.0", "outmoney":"0.0","otherinfo":"", "flag":"1"\}\]

OK！这个需求就大致完成了，当中还有需多不足的地方，因为这毕竟是我的第一个javaweb小实战。嘿嘿。