Javaweb----------基于MVC分层开发思想的一个小项目demo（登录、添加、修改、删除、登出、将信息列表导出成Excel表格）

最新推荐文章于 2022-01-26 12:38:36 发布

原创最新推荐文章于 2022-01-26 12:38:36 发布 · 350 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#javaweb

笔记专栏收录该内容

7 篇文章

订阅专栏

这是一个结合了前端和后端的Javaweb项目，采用MVC设计模式，实现了登录、添加、修改、删除、登出等功能，并且能够将信息列表导出为Excel表格。项目结构清晰，包括controller、dao、service、domain和util层。利用数据库连接池、动态代理和工具类进行数据库操作，提供了详细的业务逻辑流程。

其实也是基于以前很多demo的一个综合

准备

需要用到的jar包（包含数据库、POI、德鲁伊数据库池）：百度网盘
提取码：Sjar

结构

在这里插入图片描述

前端页面

只是做简单的组件
用到了bootstarp来做简单的修饰
我这里仅提供用到的一些资源
bootstrap：百度网盘
提取码：bost
jquery:百度网盘
提取码：jqry
看结构要知道这些页面是在哪里创建的

login.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
	String basePath = request.getScheme() + "://" + request.getServerName() + ":" + 	request.getServerPort() + request.getContextPath() + "/";
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>登录页</title>
<!--jquery -->
<script type="text/javascript" src="js/jquery-3.3.1.min.js"></script>
<!-- bootstrap -->
<link type="text/css" rel="stylesheet" href="bootstrap3/css/bootstrap.min.css" />
<script type="text/javascript" src="bootstrap3/js/bootstrap.min.js"></script>
<base href="<%=basePath%>">
</head>
<body>

	<div>
		<h3 align="center">信息管理系统 <small>登录页</small></h3>
	</div>
	<div align="center">
		<form action="user/login.do" method="post">
		
			账号：<input type="text" name="username"/><br/><br/>
			密码：<input type="text" name="password"/><br/><br/>
		
			<input type="submit" value="提交" class="btn btn-primary btn-sm"/>
		
		</form>
	</div>
	

</body>
</html>

在这里插入图片描述
add.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
	String basePath = request.getScheme() + "://" + request.getServerName() + ":" + 	request.getServerPort() + request.getContextPath() + "/";
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<base href="<%=basePath%>">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>信息添加页</title>
<!--jquery -->
<script type="text/javascript" src="js/jquery-3.3.1.min.js"></script>
<!-- bootstrap -->
<link type="text/css" rel="stylesheet" href="bootstrap3/css/bootstrap.min.css" />
<script type="text/javascript" src="bootstrap3/js/bootstrap.min.js"></script>
<script type="text/javascript">

	$(function(){
		
		$("#submitBtn").click(function(){
			
			//取得表单的jquery对象  调用提交表单方法
			$("#myForm").submit();
		
		})
	
	})
	

</script>
</head>
<body>
	
	<div>
		<h3 align="center">信息管理系统 <small>信息添加页</small></h3>
	</div>
	
	<div align="center">
		<form action="student/save.do" method="post" id="myForm">
		
		请输入姓名：<input type="text" name="name"/><br/><br/>
		请输入年龄：<input type="text" name="age"/><br/><br/>
		<!-- <input type="submit" value="提交"/> -->
		<button type="button" id="submitBtn" class="btn btn-primary btn-sm">提交表单</button>
		
		</form>
	</div>
	
	
</body>
</html>

在这里插入图片描述
edit.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"
    %>
<%
	String basePath = request.getScheme() + "://" + request.getServerName() + ":" + 	request.getServerPort() + request.getContextPath() + "/";
%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<base href="<%=basePath%>">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>修改页</title>
<!--jquery -->
<script type="text/javascript" src="js/jquery-3.3.1.min.js"></script>
<!-- bootstrap -->
<link type="text/css" rel="stylesheet" href="bootstrap3/css/bootstrap.min.css" />
<script type="text/javascript" src="bootstrap3/js/bootstrap.min.js"></script>
<script type="text/javascript">

	$(function(){
		
		edit();
		
		function edit(){
			
			var id = "${param.id}";
			
			$.ajax({
				url : "student/edit.do",
				data : {
					//要传id
					"id" : id
				},
				type : "get",
				dataType : "json",
				success : function(data){
					
					//jquery方式赋值
					$("#myId").val(data.id);
					$("#myName").val(data.name);
					$("#myAge").val(data.age);
					
				}
				
			})
			
			
		}
		
		$("#submitBtn").click(function(){
			
			//取得表单的jquery对象  调用提交表单方法
			$("#myForm").submit();
		
		})
	
	})
	

</script>
</head>
<body>
	
	<div>
		<h3 align="center">信息管理系统 <small>信息修改页</small></h3>
	</div>
	<div align="center">
		<form action="student/update.do" method="post" id="myForm">
			<input type="hidden" name="id" id="myId"/>
			请编辑姓名：<input type="text" name="name" id="myName"/><br/><br/>
			请编辑年龄：<input type="text" name="age" id="myAge"/><br/><br/>
			<!-- <input type="submit" value="提交"/> -->
			<button type="button" id="submitBtn" class="btn btn-primary btn-sm">提交表单</button>
		
		</form>
		
	</div>
	
</body>
</html>

在这里插入图片描述
index.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"
    %>
<%
	String basePath = request.getScheme() + "://" + request.getServerName() + ":" + 	request.getServerPort() + request.getContextPath() + "/";
%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<base href="<%=basePath%>">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<style type="text/css">
td {
	text-align: center;
}

a {
	text-decoration: none;
}

</style>
<!--jquery -->
<script type="text/javascript" src="js/jquery-3.3.1.min.js"></script>
<!-- bootstrap -->
<link type="text/css" rel="stylesheet" href="bootstrap3/css/bootstrap.min.css" />
<script type="text/javascript" src="bootstrap3/js/bootstrap.min.js"></script>
<script type="text/javascript">

	$(function(){
		
		list();
		
		function list(){
			
			$("#tBody").html("");
			
			$.ajax({
				url : "student/list.do",
				type : "get",
				dataType : "json",
				success : function(data){
					
					var i = 1;
					$(data.sList).each(function(){
						
						$("#tBody").append("<tr><td>"+(i++)
								+"</td><td>"+this.id
								+"</td><td>"+this.name
								+"</td><td>"+this.age
								+"</td><td><a href='jsp/student/edit.jsp?id="+this.id
								+"'>修改</a>||<a href='student/delete.do?id="+this.id+"'>删除</a></td></tr>"); 
						
					})
					
					
				}
				
			})
			
		}
		
		
		$("#addBtn").click(function(){
			
			window.location.href = "jsp/student/add.jsp";
			
		})
		
		$("#exportAllBtn").click(function(){
			
			if(window.confirm("导出全部吗？")){
				window.location.href = "student/exportAll.do";
			}
			
		})
		
		$("#logoutBtn").click(function(){
			
			if(window.confirm("确定退出吗？")){
				window.location.href = "user/logout.do";
			}
			
		})
		
		
	})
	
</script>
</head>
<body>
	
	你好：${username}
	<hr/>
	
			<div>
				<h1 align="center">信息管理系统 <small>信息展示页</small></h1>
			</div>
	
	<table border="0"  width="70%" cellspacing="0" cellpadding="6">
		<tr>
			<td style="text-align: left;">
				<button type="button" class="btn btn-primary" id="addBtn">
					<span class="glyphicon glyphicon-plus" aria-hidden="true"></span>新增
				</button>&nbsp;&nbsp;
				<button type="button" class="btn btn-default" id="exportAllBtn">
					<span class="glyphicon glyphicon-export" aria-hidden="true"></span>导出所有
				</button>&nbsp;&nbsp;
				<button type="button" class="btn btn-success" id="logoutBtn">
					<span class="glyphicon glyphicon-off" aria-hidden="true"></span>退出
				</button>
			</td>
		</tr>
	</table>
	
	<table border="1" align="center" width="70%" cellspacing="0" cellpadding="7" class="table table-striped">
		
		<thead>
			<tr>
				<td>
					序号
				</td>
				<td>
					编号
				</td>
				<td>
					姓名
				</td>
				<td>
					年龄
				</td>
				<td>
					操作
				</td>
			</tr>
		</thead>
		
		<tbody id="tBody">
			
		</tbody>
			
	</table>
	
</body>
</html>

在这里插入图片描述

分层开发

根据结构创建好相关的类和接口
controller层:（创建的是servlet）实现业务的调度
在这里插入图片描述
dao层：（创建的是两个接口，然后再在子包里创建实现类分别实现对应接口）处理jdbc的操作

service层：（也是两个接口，两个实现类分别实现对应接口）处理业务

domain层：存放字段类

util层：存放工具类
在这里插入图片描述

开始

字段类部分

Student.java

package com.domain;

public class Student {
	
	private String id;
	private String name;
	private int age;
	
	public Student(){}
	
	public Student(String id, String name, int age) {
		this.id = id;
		this.name = name;
		this.age = age;
	}
	
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getAge() {
		return age;
	}
	public void setAge(int age) {
		this.age = age;
	}
	
	
	
}

数据库部分

在这里插入图片描述

工具类部分

工具类有几个
DBUtil.java

package com.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;

public class DBUtil {

	private DBUtil() {
	}

	private static Properties prop = new Properties();
	// druid数据源 看做为数据库连接池
	// 将来我们取连接，从dds对象中取连接就可以了
	private static DruidDataSource dds = null;

	static {

		try {
			// db_server.properties
			prop.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("db_server.properties"));
			dds = (DruidDataSource) DruidDataSourceFactory.createDataSource(prop);
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	private static ThreadLocal<Connection> t = new ThreadLocal<Connection>();

	// 创建/取得 连接
	/*
	 * 
	 * 当service层调用到getConn方法的时候，创建一个连接返回给service层，同时将此连接存放到t对象中
	 * 
	 * 当dao层调用到getConn方法的时候，t对象中一定有conn了（service层执行时创建并保存的conn，
	 * 同时也是service层开启事务的那个conn） 将conn从t对象中取得，直接返回即可
	 */
	public static Connection getConn() throws SQLException {

		Connection conn = t.get();

		if (conn == null) {

			//conn = DriverManager.getConnection(URL, USER, PASSWORD);
			conn = dds.getConnection();
			t.set(conn);

		}

		return conn;

	}

	// 关闭资源
	public static void myClose(Connection conn, PreparedStatement ps, ResultSet rs) throws SQLException {

		// 关闭的顺序为按照 创建的顺序 逆序关闭
		if (rs != null) {

			rs.close();

		}

		if (ps != null) {

			ps.close();

		}

		if (conn != null) {
			
			//将连接回收到数据库连接池中
			conn.close();

			// 必须加
			t.remove();

		}

	}

}

使用到了数据库连接池代替了传统做法，所以还要搭配这个配置文件（存放数据库url、uasename、password）
db_server.properties

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
username=root
password=root
initialSize=20
maxActive=300
maxWait=60000
timeBetweenEvictionRunsMillis=60000
minEvictableIdleTimeMillis=300000

UUIDUtil.java 用来保证ID唯一性

package com.util;

import java.util.UUID;

public class UUIDUtil {
	
	public static String getUUID(){
		
		return UUID.randomUUID().toString().replaceAll("-","");
		
	}
	
}

TransactionInvocationHandler.java 实现动态代理

package com.util;

import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.Connection;

public class TransactionInvocationHandler implements InvocationHandler{

	private Object target;
	
	public TransactionInvocationHandler(Object target){
		
		this.target = target;
		
	}
	
	/*
	 * 该方法为代理类的业务方法
	 * 应该由两部分代理所组成
	 * 
	 * 一部分为代码的增强 ---> 事务
	 * 
	 * 一部分为业务逻辑的实现  
	 * 业务逻辑的实现，就是真正实现类调用业务方法的实现
	 * 
	 */
	@Override
	public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
		
		Connection conn = null;
		Object obj = null;
		
		try{
			conn = DBUtil.getConn();
			conn.setAutoCommit(false);
			
			//使用反射机制来实现业务逻辑
			obj = method.invoke(target, args);
					
			conn.commit();
		}catch(Exception e){
			conn.rollback();
			e.printStackTrace();
		}finally{
			
			DBUtil.myClose(conn, null, null);
			
		}
		
		
		return obj;
	}
	
	/*
	 * 取得代理实现类的对象
	 */
	public Object getProxy(){
		
		return Proxy.newProxyInstance(target.getClass().getClassLoader(), target.getClass().getInterfaces(), this);
		
	}
	

}

搭配工厂 ServiceFactory.java

package com.util;

/*
 * 工厂的作用，造代理实现类对象用的工厂
 * 传真正实现类对象，取代理实现类对象
 */
public class ServiceFactory {

	public static Object getService(Object service){
		
		return new TransactionInvocationHandler(service).getProxy();
		
	}
	
}

ExcelWriter.java (选择性使用吧，是我闹着玩加了一个导出成Excel表格功能才用的实现类)

package com.util;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.List;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelWriter<T> {

	/**
	 * 根据数据返回workbook
	 * @param dataList 数据
	 * @param sheetName 表格名称
	 * @param clazz 类型
	 * @return 工作簿
	 */
	public XSSFWorkbook getWorkbook(List<T> dataList , String sheetName , Class clazz) {
		XSSFWorkbook workbook = new XSSFWorkbook();
		XSSFSheet sheet = workbook.createSheet(sheetName);
		XSSFRow row0 = sheet.createRow(0);
		Field[] fields = clazz.getDeclaredFields(); 
		for (int i = 0; i < fields.length; i++) {
			Field field = fields[i];
			String fieldName = field.getName();
			XSSFCell cell = row0.createCell(i);
			cell.setCellValue(fieldName);
		}
		try {
			for (int i = 0; i < dataList.size(); i++) {
				T obj = dataList.get(i);
				XSSFRow row = sheet.createRow(i + 1);
				for (int j = 0; j < fields.length; j++) {
					String propertyName = fields[j].getName();
					String getMethodName = "get" + propertyName.substring(0, 1).toUpperCase() + propertyName.substring(1);
					Method getMethod = clazz.getDeclaredMethod(getMethodName);
					Object value = getMethod.invoke(obj);
					XSSFCell cell = row.createCell(j);
					cell.setCellValue(value == null ? "" : value.toString());
				}
			}
		} catch (NoSuchMethodException e) {
			e.printStackTrace();
		} catch (SecurityException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		}
		return workbook;
	}
}

后台部分

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5">
  <display-name>StuMaNaGer2</display-name>
  <welcome-file-list>
    <welcome-file>login.jsp</welcome-file>
  </welcome-file-list>
  <servlet>
    <servlet-name>userLogin</servlet-name>
    <servlet-class>com.controller.UserController</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>userLogin</servlet-name>
    <url-pattern>/user/login.do</url-pattern>
  </servlet-mapping>
  <servlet>
    <servlet-name>userLogout</servlet-name>
    <servlet-class>com.controller.UserController</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>userLogout</servlet-name>
    <url-pattern>/user/logout.do</url-pattern>
  </servlet-mapping>
  <servlet>
    <servlet-name>studentList</servlet-name>
    <servlet-class>com.controller.StudentController</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>studentList</servlet-name>
    <url-pattern>/student/list.do</url-pattern>
  </servlet-mapping>
  <servlet>
    <servlet-name>studentSave</servlet-name>
    <servlet-class>com.controller.StudentController</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>studentSave</servlet-name>
    <url-pattern>/student/save.do</url-pattern>
  </servlet-mapping>
  <servlet>
    <servlet-name>studentEdit</servlet-name>
    <servlet-class>com.controller.StudentController</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>studentEdit</servlet-name>
    <url-pattern>/student/edit.do</url-pattern>
  </servlet-mapping>
  <servlet>
    <servlet-name>studentUpdate</servlet-name>
    <servlet-class>com.controller.StudentController</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>studentUpdate</servlet-name>
    <url-pattern>/student/update.do</url-pattern>
  </servlet-mapping>
  <servlet>
    <servlet-name>studentDelete</servlet-name>
    <servlet-class>com.controller.StudentController</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>studentDelete</servlet-name>
    <url-pattern>/student/delete.do</url-pattern>
  </servlet-mapping>
  <servlet>
    <servlet-name>studentexportAll</servlet-name>
    <servlet-class>com.controller.StudentController</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>studentexportAll</servlet-name>
    <url-pattern>/student/exportAll.do</url-pattern>
  </servlet-mapping>
</web-app>

画个图，看看业务逻辑：
在这里插入图片描述
所以我会采用自上而下的方式来写
先写controller层
UserController.java

package com.controller;

import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.service.UserService;
import com.service.impl.UserServiceImpl;
import com.util.ServiceFactory;


public class UserController extends HttpServlet {
	
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		System.out.println("进入到用户模块");
		
		 
		//要用到业务层进行调度
		UserService userService = (UserService) ServiceFactory.getService(new UserServiceImpl());
		
		String path = request.getServletPath();
		//登录
		if ("/user/login.do".equals(path)) {
			
			login(request,response,userService);
			
			//退出
		}else if ("/user/logout.do".equals(path)) {
			
			logout(request,response,userService);
			
		}
		
		
		
		
		
	}



	private void logout(HttpServletRequest request, HttpServletResponse response, UserService userService) throws ServletException,IOException {
		
		System.out.println("进入到退出操作");
		
		//退出操作只需要把当前的session给摧毁掉，然后重定向到登录页
		//不涉及数据的操作
		request.getSession().invalidate();
		response.sendRedirect(request.getContextPath() + "/login.jsp");
		
	}



	private void login(HttpServletRequest request, HttpServletResponse response, UserService userService) throws ServletException,IOException {
		
		System.out.println("进入到登录操作");
		
		//接收表单数据
		String username = request.getParameter("username");
		String password = request.getParameter("password");
			
		//要完成登录验证业务  调用service层对象来完成业务
		//注意login()方法此时还没有创建  点击它创建到service层中
		boolean flag = userService.login(username,password);
				
		if (flag) {
					
			request.getSession().setAttribute("username", username);
					
			//登录成功，跳转到目标页面
			response.sendRedirect(request.getContextPath() + "/jsp/student/index.jsp");
					
		} else {
			//登录失败
			response.sendRedirect(request.getContextPath() + "/login.jsp");
					
		}
		
	}



	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		this.doGet(request, response);
	}

}

UserService.java

package com.service;

public interface UserService {

	//在UserServiceImpl中实现
	boolean login(String username, String password);

}

UserServiceImpl.java

package com.service.impl;

import com.dao.UserDao;
import com.dao.impl.UserDaoImpl;
import com.service.UserService;

public class UserServiceImpl implements UserService {

	//要用到dao层
	private UserDao userDao = new UserDaoImpl();

	@Override
	public boolean login(String username, String password) {
		
		//注意这是dao层的login()方法，此时还没创建，点击它进行创建到dao层
		int count = userDao.login(username,password);

		boolean flag = true;
		
		//是否存在用户
		if (count != 1) {
			flag = false;
		}
		
		return flag;
	}
	
}

UserDao.java

package com.dao;

public interface UserDao {

	//在UserDaoImpl中实现
	int login(String username, String password);

}

UserDaoImpl.java

package com.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.dao.UserDao;
import com.util.DBUtil;

public class UserDaoImpl implements UserDao {

	@Override
	public int login(String username, String password) {
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select count(*) from tbl_user where username=? and password=?";
		int count = 0;
		
		try {
			conn = DBUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1, username);
			ps.setString(2, password);
			
			rs = ps.executeQuery();
			
			if (rs.next()) {
				
				count = rs.getInt(1);
				
			}
			
			
		} catch (SQLException e) {
			
			e.printStackTrace();
			throw new RuntimeException();
			
		}finally {
			
			try {
				DBUtil.myClose(null, ps, rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
		}
		
		return count;
	}

}

根据登录的这个操作，可以很直观的了解自上而下分层开发的整体思路
接下来才是重头戏
StudentController.java

package com.controller;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.Iterator;
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.poi.xssf.usermodel.XSSFWorkbook;

import com.domain.Student;
import com.service.StudentService;
import com.service.impl.StudentServiceImpl;
import com.util.ExcelWriter;
import com.util.ServiceFactory;


public class StudentController extends HttpServlet {
	
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		System.out.println("进入到学生管理操作");
		
		StudentService studentService = (StudentService) ServiceFactory.getService(new StudentServiceImpl());
		
		String path = request.getServletPath();
		
		if ("/student/list.do".equals(path)) {
			
			list(request,response,studentService);
			
		} else if ("/student/save.do".equals(path)) {
			
			save(request,response,studentService);
			
		} else if ("/student/edit.do".equals(path)) {
			
			edit(request,response,studentService);
			
		} else if ("/student/update.do".equals(path)) {
			
			update(request,response,studentService);
			
		} else if ("/student/delete.do".equals(path)) {
			
			delete(request,response,studentService);
			
		} else if ("/student/exportAll.do".equals(path)) {
			
			export(request,response,studentService);
			
		} 
		
		
	}

	//将信息列表导出成Excel表
	private void export(HttpServletRequest request, HttpServletResponse response, StudentService studentService) throws IOException {
		
		System.out.println("进入到学生列表导出操作");
		//文件下载的时候需要设置响应的内容类型
		response.setContentType("application/vnd.ms-excel");  
		//设置附件
        response.setHeader("Content-disposition", "attachment;filename=Student"+System.currentTimeMillis()+".xlsx"); 
		
		//获取数据  此时的exportAll()还没有创建，点击创建在StudentService
		List<Student> sList = studentService.exportAll();
		//获取workBook 用到工具类ExcelWriter
		ExcelWriter<Student> ew = new ExcelWriter<Student>();
		XSSFWorkbook workbook = ew.getWorkbook(sList, "学生列表", Student.class);
		//响应到浏览器 通过response对象获取响应流，该响应流指向发送请求的浏览器
		workbook.write(response.getOutputStream());
	
	}

	private void list(HttpServletRequest request, HttpServletResponse response, StudentService studentService) throws ServletException, IOException{

		System.out.println("进入到学生列表操作");

		//此时的getAll()还没有创建，点击创建在StudentService
		List<Student> sList = studentService.getAll();
		
		StringBuffer buf = new StringBuffer();
		
		//{"sList":[{"id":"?","name":"?","age":?},{},{}]}
		buf.append("{\"sList\":[");
		
		for(int i = 0; i < sList.size(); i++) {
			
			Student s = sList.get(i);
			
			//buf.append("{\"id\":\""+s.getId()+"\",\"name\":\""+s.getName()+"\",\"age\":"+s.getAge()+"}");
			//拼接ajax
			buf.append("{\"id\":\"");
			buf.append(s.getId());
			buf.append("\",\"name\":\"");
			buf.append(s.getName());
			buf.append("\",\"age\":");
			buf.append(s.getAge());
			buf.append("}");
			
			if (i < sList.size() - 1) {
				buf.append(",");
			}
			
		}
		
		buf.append("]}");	
		
		System.out.println(buf.toString());
		
		//响应流的方式响应回去
		PrintWriter out = response.getWriter();
		out.print(buf.toString());
		out.close();
		
	}

	private void save(HttpServletRequest request, HttpServletResponse response, StudentService studentService) throws ServletException, IOException{
		
		System.out.println("进入到学生添加操作");
		
		//接收表单数据
		String name = request.getParameter("name");
		String ageStr = request.getParameter("age");
		int age = Integer.valueOf(ageStr);
		
		//不同类型，用对象存数据，进行传递
		Student s = new Student();
		s.setName(name);
		s.setAge(age);
		
		//业务层来处理业务 此时的save()还没有创建，点击创建在StudentService
		studentService.save(s);
		
		response.sendRedirect(request.getContextPath() + "/jsp/student/index.jsp");
	}

	private void edit(HttpServletRequest request, HttpServletResponse response, StudentService studentService) throws ServletException, IOException{
		
		System.out.println("进入到根据id取单条操作");
		
		String id = request.getParameter("id");
		
		Student s = studentService.getById(id);
		
		//{"id":"?","name":"?","age":?}
		String str = "{\"id\":\""+s.getId()+"\",\"name\":\""+s.getName()+"\",\"age\":"+s.getAge()+"}";
		
		PrintWriter out = response.getWriter();
		out.print(str);
		out.close();
	}

	private void update(HttpServletRequest request, HttpServletResponse response, StudentService studentService) throws ServletException, IOException{

		System.out.println("进入到学生修改操作");
		
		// 接收表单数据
		String id = request.getParameter("id");
		String name = request.getParameter("name");
		String ageStr = request.getParameter("age");
		int age = Integer.valueOf(ageStr);

		// 不同类型，用对象存数据，进行传递
		Student s = new Student();
		s.setId(id);
		s.setName(name);
		s.setAge(age);

		// 业务层来处理业务 此时的update()还没有创建，点击创建在StudentService
		studentService.update(s);

		response.sendRedirect(request.getContextPath() + "/jsp/student/index.jsp");
		
	}

	private void delete(HttpServletRequest request, HttpServletResponse response, StudentService studentService) throws ServletException, IOException{
		
		System.out.println("进入到学生删除操作");
		
		//接收需要删除记录的id
		String id = request.getParameter("id");
		
		//此时的delete()还没有创建，点击创建在StudentService
		studentService.delete(id);
		response.sendRedirect(request.getContextPath() + "/jsp/student/index.jsp");
		
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		this.doGet(request, response);
	}

}

StudentService.java 业务接口，在StudentServiceImpl中实现

package com.service;

import java.util.List;

import com.domain.Student;

public interface StudentService {

	List<Student> getAll();

	void save(Student s);

	Student getById(String id);

	void update(Student s);

	void delete(String id);

	List<Student> exportAll();

}

StudentServiceImpl.java

package com.service.impl;

import java.util.List;

import com.dao.StudentDao;
import com.dao.impl.StudentDaoImpl;
import com.domain.Student;
import com.service.StudentService;

public class StudentServiceImpl implements StudentService {

	//要用到dao层
	private StudentDao studentDao = new StudentDaoImpl();

	@Override
	public List<Student> getAll() {
		//此时的getAll()还没有创建，点击在StudentDao中创建
		List<Student> sList = studentDao.getAll();
		
		return sList;
	}

	//主要是进行数据库的数据插入操作，dao层来处理
	@Override
	public void save(Student s) {
		//此时的save()还没有创建，点击在StudentDao中创建
		studentDao.save(s);
		
	}

	@Override
	public Student getById(String id) {
		//此时的getById()还没有创建，点击在StudentDao中创建
		Student s = studentDao.getById(id);
		return s;
	}

	@Override
	public void update(Student s) {
		//此时的update()还没有创建，点击在StudentDao中创建
		studentDao.update(s);
		
	}

	@Override
	public void delete(String id) {
		//此时的delete()还没有创建，点击在StudentDao中创建
		studentDao.delete(id);
		
	}

	@Override
	public List<Student> exportAll() {
		//此时的exportAll()还没有创建，点击在StudentDao中创建
		return studentDao.exportAll();
	}

}

StudentDao.java 操作数据的接口，在StudentDaoImpl中实现

package com.dao;

import java.util.List;

import com.domain.Student;

public interface StudentDao {

	List<Student> getAll();

	void save(Student s);

	Student getById(String id);

	void update(Student s);

	void delete(String id);

	List<Student> exportAll();

}

StudentDaoImpl.java

package com.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.dao.StudentDao;
import com.domain.Student;
import com.util.DBUtil;
import com.util.UUIDUtil;

public class StudentDaoImpl implements StudentDao {

	@Override
	public List<Student> getAll() {
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select id,name,age from tbl_student";
		List<Student> sList = new ArrayList<Student>();
		
		try {
			conn = DBUtil.getConn();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			
			while (rs.next()) {
				
				Student s = new Student();
				
				s.setId(rs.getString(1));
				s.setName(rs.getString(2));
				s.setAge(rs.getInt(3));
				
				sList.add(s);
				
			}
			
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException();
		} finally {
			try {
				DBUtil.myClose(null, ps, rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		 
		
		return sList;
	}

	
	@Override
	public void save(Student s) {
		
		Connection conn = null;
		PreparedStatement ps = null;
		String sql = "insert into tbl_student(id,name,age) values(?,?,?)";
		
		try {
			conn = DBUtil.getConn();
			ps = conn.prepareStatement(sql);
			
			//取数据，然后更新插入
			ps.setString(1, UUIDUtil.getUUID());
			ps.setString(2, s.getName());
			ps.setInt(3, s.getAge());
			
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException();
		}finally {
			try {
				DBUtil.myClose(null, ps, null);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
	}


	@Override
	public Student getById(String id) {
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select name,age from tbl_student where id=?";
		Student s = new Student();
		
		try{
			
			conn = DBUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1,id);
			rs = ps.executeQuery();
			
			if(rs.next()){
				
				s.setId(id);
				s.setName(rs.getString(1));
				s.setAge(rs.getInt(2));
				
			}
			
			
		}catch(SQLException e){
			e.printStackTrace();
			throw new RuntimeException();
		}finally{
			
			try {
				DBUtil.myClose(null, ps, rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
		}
		
		return s;
	}


	@Override
	public void update(Student s) {
		
		Connection conn = null;
		PreparedStatement ps = null;
		String sql = "update tbl_student set name=?,age=? where id=?";
		
		try{
			
			conn = DBUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1,s.getName());
			ps.setInt(2,s.getAge());
			ps.setString(3,s.getId());
			
			ps.executeUpdate();
			
			
		}catch(SQLException e){
			e.printStackTrace();
			throw new RuntimeException();
		}finally{
			
			try {
				DBUtil.myClose(null, ps, null);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
		}
		
	}


	@Override
	public void delete(String id) {
		
		Connection conn = null;
		PreparedStatement ps = null;
		String sql = "delete from tbl_student where id=?";
		
		try{
			
			conn = DBUtil.getConn();
			ps = conn.prepareStatement(sql);
			ps.setString(1,id);
			
			ps.executeUpdate();
			
			
		}catch(SQLException e){
			e.printStackTrace();
			throw new RuntimeException();
		}finally{
			
			try {
				DBUtil.myClose(null, ps, null);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
		}
		
	}


	@Override
	public List<Student> exportAll() {
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select * from tbl_student";
		List<Student> sList = new ArrayList<Student>();
		
		try {
			conn = DBUtil.getConn();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			
			while (rs.next()) {
				
				Student s = new Student();
				
				s.setId(rs.getString(1));
				s.setName(rs.getString(2));
				s.setAge(rs.getInt(3));
				
				sList.add(s);
				
			}
			
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException();
		} finally {
			try {
				DBUtil.myClose(null, ps, rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		 
		
		return sList;
	}

}