jsp牛刀小试在线报名系统
首先,我们需要按照需求确定我们要做的报名系统的功能。即,我们只完成以下三个简单功能:
- 在线注册
- 查询报名详情
- 导出excel
其次,确定我们项目中所涉及的对象及关系。在这里,我们只有两个对象,一个为报名人员,一个为管理人员。根据这两个对象和报名所用到的信息,我们设计如下对象
我们把所有的对象都放在po包下,不要忘记自动生成get、set方法,重写toString()和构造方法。
package com.bzucsdn.po;
public class Member {
public String name;
public String grade;
public String department;
public String classes;
public String phone;
public String qq;
public String email;
public String remarks;
}
package com.bzucsdn.po;
public class Admin{
public String name;
public String password;
}
接下来,根据我们的对象,设计数据库
create table member(
id int primary key,
name char(20),
grade char(6),
department char(10),
classes char(10),
phone char(11),
qq char(12),
email char(20),
remarks char(200)
);
create table admin(
aname char(10),
apassword char(20)
);
实现注册报名,我们需要把人员信息存入到数据库,查询信息,我们需要把信息从数据库中取出来,下面我们就完成这两个数据库操作。
我们这里对数据库的操作用到了dbutil工具
- 首先,在util包中对数据库的连接关闭操作做好封装
package com.bzucsdn.util;
import java.sql.Connection;
import java.sql.DriverManager;
import org.apache.commons.dbutils.DbUtils;
public class DBUtil {
//0定义组件
private static Connection conn=null;
private static String strUserName="root";
private static String strPassword="yourPassWord";
private static String strUrl="jdbc:mysql://127.0.0.1:3306/bzucsdn?characterEncoding=utf8";
//获取链接
public static Connection getConnection() {
DbUtils.loadDriver("com.mysql.jdbc.Driver");
if (conn==null) {
try {
conn=DriverManager.getConnection(strUrl, strUserName, strPassword);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
return null;
}
}
return conn;
}
//关闭链接
public static void closeConnection() {
try {
if (!conn.isClosed()) {
conn.close();
}
if (conn!=null) {
conn=null;
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
- 其次,在dao包中,完成对数据库的操作
//首先是member的操作
package com.bzucsdn.dao;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Map;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapHandler;
import com.bzucsdn.po.Member;
import com.bzucsdn.util.DBUtil;
public class memberDao {
private QueryRunner qr=null;
private String strSql="";
private int intResult=-1;
public memberDao(){
qr=new QueryRunner();
}
public int memberInsert(Member mbr) {
// TODO Auto-generated method stub
Connection conn = DBUtil.getConnection();
int i=1;
BigInteger id=new BigInteger("0") ;
try {
strSql = "select LAST_INSERT_ID()";
Map<String, Object> map = qr.query(conn, strSql, new MapHandler());
id = (BigInteger)map.get("LAST_INSERT_ID()");
strSql = "insert into member (name,grade,department,classes,phone,qq,email,remarks) values(?,?,?,?,?,?,?,?)";
Object[] parms = {mbr.getName(),mbr.getGrade(),mbr.getDepartment(),mbr.getClasses(),mbr.getPhone(),mbr.getQq(),mbr.getEmail(),mbr.getBeizhu()};
i = qr.update(conn, strSql, parms);
DBUtil.closeConnection();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return 1;
}
return 0;
}
}
//其次是admin的操作
package com.bzucsdn.dao;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
import com.bzucsdn.util.DBUtil;
public class adminDao {
private QueryRunner qr=null;
private String strSql="";
public adminDao(){
qr=new QueryRunner();
}
public List<Map<String, Object>> getAllMember() {
strSql="select * from member ";
List<Map<String, Object>> map=null;
try {
map = qr.query(DBUtil.getConnection(), strSql, new MapListHandler());
DBUtil.closeConnection();
return map;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
DBUtil.closeConnection();
return map;
}
}
这样就完成了对数据库的操作。那么接下来,就是需要完成servlet的功能了
同样我们把所有的web-api功能都放到servlet包下
- 首先是member的操作
package com.bzucsdn.servlet;
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.bzucsdn.po.Member;
import com.bzucsdn.dao.userDao;
public class User extends HttpServlet {
public User() {
super();
}
private Member mbr = new Member();
public void destroy() {
super.destroy(); // Just puts "destroy" string in log
}
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doPost(request, response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
mbr.setName(request.getParameter("Name").trim());
mbr.setGrade(request.getParameter("Grade").trim());
mbr.setDepartment(request.getParameter("Department").trim());
mbr.setClasses(request.getParameter("Classes").trim());
mbr.setPhone(request.getParameter("Phone").trim());
mbr.setQq(request.getParameter("QQ").trim());
mbr.setEmail(request.getParameter("Email").trim());
mbr.setBeizhu(request.getParameter("remarks").trim());
memberDao md = new userDao();
System.out.println(md.getName()+"注册");
response.getWriter().println(md.memberInsert(mbr));
}
public void init() throws ServletException {
// Put your code here
}
}
- 其次是admin端查询报名人员信息的功能
package com.bzu.servlet;
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.bzucsdn.dao.adminDao;
import com.bzucsdn.excel.ToExcel;
public class Admin extends HttpServlet {
public Admin() {
super();
}
public void destroy() {
super.destroy(); // Just puts "destroy" string in log
// Put your code here
}
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
doPost(request, response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
adminDao all= new adminDao();
List<Map<String, Object>> listmap = all.getAllMember();
JSONArray json = new JSONArray();
JSONObject m = new JSONObject();
for (Map<String, Object> maptemp : list) {
m =JSONObject.fromObject(maptemp);
json.add(m);
}
response.getWriter().println(json);
}
public void init() throws ServletException {
// Put your code here
}
}
- 然后,我们用poi实现人员信息导出到excel
package com.bzucsdn.excel;
import java.io.File;
import java.io.FileOutputStream;
import java.text.DateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.bzucsdn.dao.adminDao;
public class ToExcel {
public String init() throws Exception
{
//create work book, and the sheet .
Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
CreationHelper creationHelper = wb.getCreationHelper();
Sheet sheet = wb.createSheet("new sheet");
//get the data from database
adminDao listuser= new adminDao();
List<Map<String,Object>> listmap=null;
listmap=listuser.getMember();
int i=1,j=0;
//create the head row and cells
Row[] row = new Row[10000];
Cell[] cell = new Cell[12];
row[0] = sheet.createRow(0);
cell[0] = row[0].createCell(0);
cell[0].setCellValue("姓名");
cell[1] = row[0].createCell(2);
cell[1].setCellValue("年级");
cell[2] = row[0].createCell(1);
cell[2].setCellValue("系别");
cell[3] = row[0].createCell(2);
cell[3].setCellValue("年级");
cell[4] = row[0].createCell(3);
cell[4].setCellValue("手机");
cell[5] = row[0].createCell(4);
cell[5].setCellValue("QQ");
cell[6] = row[0].createCell(5);
cell[6].setCellValue("邮箱");
cell[7] = row[0].createCell(6);
cell[7].setCellValue("备注");
//create rows and cells then input the data to the document
for (Map<String, Object> map : listmap) {
j=0;
row[i] = sheet.createRow(i);
cell[j] = row[i].createCell(j);
cell[j].setCellValue(map.get("name").toString());
j++;
cell[j] = row[i].createCell(j);
cell[j].setCellValue(map.get("grade").toString());
j++;
cell[j] = row[i].createCell(j);
cell[j].setCellValue(map.get("department").toString());
j++;
cell[j] = row[i].createCell(j);
cell[j].setCellValue(map.get("classes").toString());
j++;
cell[j] = row[i].createCell(j);
cell[j].setCellValue(map.get("phone").toString());
j++;
cell[j] = row[i].createCell(j);
cell[j].setCellValue(map.get("qq").toString());
j++;
cell[j] = row[i].createCell(j);
cell[j].setCellValue(map.get("email").toString());
j++;
cell[j] = row[i].createCell(j);
cell[j].setCellValue(map.get("beizhu").toString());
j++;
i++;
}
//out put the doucument
Date date = new Date();
String fileName="报名信息"+DateFormat.getDateInstance().format(date);
File file = new File("c:/WebAPI/"+fileName+".xlsx");
FileOutputStream fileOut = new FileOutputStream(file);
wb.write(fileOut);
fileOut.close();
return fileName;
}
}
*该功能可继续完善*
好,那么以上呢,就是服务器端的工作,剩下的浏览器端的工作就简单了,我们暂时忽略美工,只实现以下两个基本页面
- 用户报名页面
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>滨州学院csdn高校俱乐部第二届编程比赛报名系统</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<link rel="stylesheet" type="text/css" href="css/global.css">
<script type="text/javascript" src="js/jquery-1.9.1.js"></script>
<script type="text/javascript">
function SubmitForm() {
var name = document.getElementById("uname").value;
var grade = document.getElementById("ugrade").value;
var department = document.getElementById("udepart").value;
var classes = document.getElementById("uclasses").value;
var phone = document.getElementById("uphone").value;
var qq = document.getElementById("uqq").value;
var email = document.getElementById("uemail").value;
var beizhu = document.getElementById("ubeizhu").value;
$.post("User",
{
Name:name,
Department:department,
Grade:grade,
Classes:classes,
Phone:phone,
QQ:qq,
Email:email,
Beizhu:beizhu
},
function(data){
if(data==0)
alert("恭喜您已报名成功~");
});
}
</script>
</head>
<body>
<br>
<div id="bd" style="margin-top: 20px;">
<div >
<form id="form1" name="form1" method="post" action="Login" class="targetCopy">
<p>滨州学院csdn高校俱乐部第二届编程比赛报名系统</p>
<div class="code">
<table width="272" border="0" align="center">
<tr><td width="61">姓名:</td>
<td width="195"><input id="uname" type="text" maxlength="10" /></td>
</tr>
<tr><td width="61">年级:</td>
<td width="195"><input id="ugrade" type="text" maxlength="10" /></td>
</tr>
<tr><td width="61">系院:</td>
<td width="195"><input id="udepart" type="text" maxlength="10" /></td>
</tr>
<tr><td width="61">班级:</td>
<td width="195"><input id="uclasses" type="text" maxlength="10" /></td>
</tr>
<tr><td width="61">手机:</td>
<td width="195"><input id="uphone" type="text" maxlength="11" /></td>
</tr>
<tr><td width="61">QQ:</td>
<td width="195"><input id="uqq" type="text" maxlength="11" /></td>
</tr>
<tr><td width="61">邮箱:</td>
<td width="195"><input id="uemail" type="text" maxlength="20" /></td>
</tr>
<tr><td width="61">备注:</td>
<td width="195"><input id="ubeizhu" type="text" maxlength="100" /></td>
</tr>
<tr align="center" valign="center" cellpadding="5px";>
<td></td><td style="margin-top:20px;"><div id="btps"><input type="button" value="提交" onclick="SubmitForm()" /> <input type="reset" name="Submit" value="重置" /> <a href=""></a></div></td>
</tr>
</table>
</div>
</form>
</div>
</div>
</body>
</html>
- 查询报名信息界面
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>滨州学院csdn高校俱乐部第二届编程比赛报名系统</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
<script type="text/javascript" src="js/jquery-1.9.1.js"></script>
<script type="text/javascript">
var showHtml;
var headtable;
$.get("Admin",null,
function(json){
headtable="<tr class=\"text-c\"><th>姓名</th><th>年级</th><th>系别</th><th>班级</th><th>手机号</th><th>qq号</th><th>Email</th><th>备注</th></tr>";
var obj = eval ("(" + json + ")");
for(int i=0; i<obj.length();i++)
{
showHtml+="<tr class=\"text-c\"><th>"+obj[i].name+"</th><th>"+obj[i].grade+"</th><th>"+obj[i].department+"</th><th>"+obj[i].classes+"</th><th>"+obj[i].phone+"</th><th>"+obj[i].qq+"</th><th>"+obj[i].email+"</th><th>"+obj[i].remarks+"</th><td class=\"f-14 user-manage\"></td></tr>";
};
document.getElementById("showuser").innerHTML="<table class=\"table table-border table-bordered table-hover table-bg table-sort\">"+headtable+showHtml+"</thead></table>";
});
</script>
</head>
<body>
<div id="showuser"></div>
</body>
</html>
- 那么最后一步呢,就是配置我们的web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app version="3.0"
xmlns="http://java.sun.com/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd">
<servlet>
<servlet-name>Member</servlet-name>
<servlet-class>com.bzu.servlet.Member</servlet-class>
</servlet>
<servlet>
<servlet-name>Admin</servlet-name>
<servlet-class>com.bzu.servlet.Admin</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>User</servlet-name>
<url-pattern>/User</url-pattern>
</servlet-mapping>
<servlet-mapping>
<servlet-name>Admin</servlet-name>
<url-pattern>/Admin</url-pattern>
</servlet-mapping>
</web-app>
好了,以上就是最基础的功能,还有一个导出excel按钮和管理员登录验证呢,暂时还没加,不过思路已经很明显了,没什么技术难度了,剩下的只是时间和工作量的问题了,暂时就不加了先。
弄好了访问一下127.0.0.1:8080和127.0.0.1:8080/members.jsp是不是挺有成就感的?
好了,有什么疑问可以留言。要上床睡了~
最后,贴出项目包结构的截图和jar包还有用到的css和js文件的下载地址
jar包链接:http://pan.baidu.com/s/1dDyEJS1 密码:xztz
css&js链接:http://pan.baidu.com/s/1ntope33 密码:13l9
之前的报名界面
导出的工作表
刚刚上道jsp,欢迎各位指点~