12.10
今天写了一个学生的增删改查的系统；
具体代码如下：
dao层：
package com.pp;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class dao {
public void insert(student L) {
String sql="insert into student(xuehao,name,banji,kecheng,teacher,score) values(?,?,?,?,?,?)";
Connection con;
try {
con = DBUtil.getConnection();
PreparedStatement pa=con.prepareStatement(sql);
pa.setString(1,L.getXuehao());
pa.setString(2,L.getName());
pa.setString(3,L.getBanji());
pa.setString(4,L.getKecheng());
pa.setString(5,L.getTeacher());
pa.setString(6,L.getScore());
pa.execute();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void delete(String xuehao) {
String sql="delete from student where xuehao=?";
Connection con;
try {
con = DBUtil.getConnection();
PreparedStatement pa=con.prepareStatement(sql);
pa.setString(1,xuehao);
pa.execute();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void update(String score,String kecheng,String xuehao,String teacher) {
String sql="update student set score=? where kecheng=? and xuehao=? and teacher=?";
Connection con;
try {
con = DBUtil.getConnection();
PreparedStatement pa=con.prepareStatement(sql);
pa.setString(1,score);
pa.setString(2,kecheng);
pa.setString(3,xuehao);
pa.setString(4,teacher);
pa.execute();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public List<student> select() {
String sql="select * from student";
student L=null;
List<student> list=new ArrayList();
Connection con;
try {
con = DBUtil.getConnection();
PreparedStatement pa=con.prepareStatement(sql);
ResultSet rs=pa.executeQuery();
while(rs.next()) {
String xuehao=rs.getString("xuehao");
String name=rs.getString("name");
String banji=rs.getString("banji");
String kecheng=rs.getString("kecheng");
String teacher=rs.getString("teacher");
String score=rs.getString("score");
L=new student(xuehao,name,banji,kecheng,teacher,score);
list.add(L);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
public boolean selectp(String num) {
boolean panduan=false;
String sql="select * from password";
Connection con;
try {
con = DBUtil.getConnection();
PreparedStatement pa=con.prepareStatement(sql);
ResultSet rs=pa.executeQuery();
while(rs.next()) {
String numm=rs.getString("num");
String password=rs.getString("password");
if(num.equals(numm)&&num.equals(password)) {
panduan=true;
break;
}
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return panduan;
}
}
servlet层：
package com.pp;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
* Servlet implementation class servlet
*/
@WebServlet("/servlet")
public class servlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public servlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
private void login(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{
String teacher=request.getParameter("num");
if("123".equals(teacher)&&"123".equals(teacher)) {
request.getSession().setAttribute("teacher",teacher);
request.getRequestDispatcher("Course.jsp").forward(request, response);
}
else {
response.sendRedirect("login.jsp?error=yes");
}
}
private void course(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{
String course=request.getParameter("course");
if("math".equals(course)) {
request.getSession().setAttribute("course","高数");
request.getRequestDispatcher("gongneng.jsp").forward(request, response);
}
else if("java".equals(course)) {
request.getSession().setAttribute("course","java");
request.getRequestDispatcher("gongneng.jsp").forward(request, response);
}
else if("lisan".equals(course)) {
request.getSession().setAttribute("course","离散");
request.getRequestDispatcher("gongneng.jsp").forward(request, response);
}
}
private void addstu(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String xuehao=request.getParameter("xuehao");
String name=request.getParameter("name");
String banji=request.getParameter("banji");
String kecheng=request.getSession().getAttribute("course").toString();
String teacher=request.getSession().getAttribute("teacher").toString();
String score="0";
student stu=new student(xuehao,name,banji,kecheng,teacher,score);
dao L=new dao();
L.insert(stu);
response.sendRedirect("addstu.jsp?addstu=yes");
}
private void deletestu(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String xuehao=request.getParameter("xuehao");
dao L=new dao();
L.delete(xuehao);
response.sendRedirect("deletestu.jsp?delete=yes");
}
private void dengji(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
dao L=new dao();
String kecheng=request.getSession().getAttribute("course").toString();
String teacher=request.getSession().getAttribute("teacher").toString();
List<student> list=L.select();
for(int i=0;i<list.size();i++) {
if(!list.get(i).getTeacher().equals(teacher)||!list.get(i).getKecheng().equals(kecheng)) {
list.remove(i);
i--;
}
}
request.setAttribute("list",list);
request.getRequestDispatcher("dengji.jsp").forward(request, response);
}
private void dengji2(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String xuehao=request.getParameter("xuehao");
String teacher=request.getSession().getAttribute("teacher").toString();
String kecheng=request.getSession().getAttribute("course").toString();
String score=request.getParameter("score");
dao L=new dao();
L.update(score, kecheng, xuehao, teacher);
response.sendRedirect("dengjier.jsp?dengji=yes");
}
private void update(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String xuehao=request.getParameter("xuehao");
String teacher=request.getSession().getAttribute("teacher").toString();
String kecheng=request.getSession().getAttribute("course").toString();
String score=request.getParameter("score");
dao L=new dao();
L.update(score, kecheng, xuehao, teacher);
response.sendRedirect("update.jsp?update=yes");
}
private void numselect(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String xuehao=request.getParameter("xuehao");
String kecheng=request.getSession().getAttribute("course").toString();
String teacher=request.getSession().getAttribute("teacher").toString();
dao L=new dao();
List<student> list=L.select();
for(int i=0;i<list.size();i++) {
if(!list.get(i).getXuehao().equals(xuehao)||!list.get(i).getKecheng().equals(kecheng)||!list.get(i).getTeacher().equals(teacher)) {
list.remove(i);
i--;
}
}
request.setAttribute("list", list);
request.getRequestDispatcher("selectjieguo.jsp").forward(request, response);
}
private void nameselect(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String name=request.getParameter("name");
String kecheng=request.getSession().getAttribute("course").toString();
String teacher=request.getSession().getAttribute("teacher").toString();
dao L=new dao();
List<student> list=L.select();
for(int i=0;i<list.size();i++) {
if(!list.get(i).getName().equals(name)||!list.get(i).getKecheng().equals(kecheng)||!list.get(i).getTeacher().equals(teacher)) {
list.remove(i);
i--;
}
}
request.setAttribute("list", list);
request.getRequestDispatcher("selectjieguo.jsp").forward(request, response);
}
private void paixu(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String kecheng=request.getSession().getAttribute("course").toString();
String teacher=request.getSession().getAttribute("teacher").toString();
String paixu=request.getParameter("paixu");
dao L=new dao();
int k;
List<student> list=L.select();
for(int i=0;i<list.size();i++) {
if(!list.get(i).getTeacher().equals(teacher)||!list.get(i).getKecheng().equals(kecheng)) {
list.remove(i);
i--;
}
}
if(list.size()>1)
Collections.sort(list);
if("sheng".equals(paixu)) {
request.setAttribute("list", list);
}
else if("jiang".equals(paixu)) {
List<student> list2=new ArrayList();
for(int i=0;i<list.size();i++) {
list2.add(list.get(list.size()-i-1));
}
request.setAttribute("list", list2);
}
request.getRequestDispatcher("paixu.jsp").forward(request, response);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
request.setCharacterEncoding("UTF-8");
String method=request.getParameter("method");
if("login".equals(method)) {
login(request,response);
}
else if("course".equals(method)) {
course(request,response);
}
else if("addstu".equals(method)) {
addstu(request,response);
}
else if("deletestu".equals(method)) {
deletestu(request,response);
}
else if("dengji".equals(method)) {
dengji(request,response);
}
else if("update".equals(method)) {
update(request,response);
}
else if("numselect".equals(method)) {
numselect(request,response);
}
else if("nameselect".equals(method)) {
nameselect(request,response);
}
else if("dengji2".equals(method)) {
dengji2(request,response);
}
else if("paixu".equals(method)) {
paixu(request,response);
}
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}
student层：
package com.pp;
public class student implements Comparable<student> {
private String xuehao;
private String name;
private String banji;
private String kecheng;
private String teacher;
private String score;
public String getXuehao() {
return xuehao;
}
public void setXuehao(String xuehao) {
this.xuehao = xuehao;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getBanji() {
return banji;
}
public void setBanji(String banji) {
this.banji = banji;
}
public String getKecheng() {
return kecheng;
}
public void setKecheng(String kecheng) {
this.kecheng = kecheng;
}
public String getTeacher() {
return teacher;
}
public void setTeacher(String teacher) {
this.teacher = teacher;
}
public String getScore() {
return score;
}
public void setScore(String score) {
this.score = score;
}
public student(String xuehao, String name, String banji, String kecheng, String teacher, String score) {
super();
this.xuehao = xuehao;
this.name = name;
this.banji = banji;
this.kecheng = kecheng;
this.teacher = teacher;
this.score = score;
}
@Override
public int compareTo(student o) {
int i = Integer.parseInt(this.getScore())-Integer.parseInt(o.getScore());
return i;
}
}
联库操作：
package com.pp;
import java.sql.*;
public class DBUtil{
private DBUtil() {}
static {
try {
Class.forName("com.mysql.jdbc.Driver");
}catch(ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException{
return DriverManager.getConnection("jdbc:mysql://localhost:3306/user?serverTimezone=UTC&useUnicode=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=utf-8","root","123456");
}
public static void close(Connection conn,Statement ps,ResultSet rs) {
if(rs!=null) {
try {
rs.close();
}catch(SQLException s) {
s.printStackTrace();
}
}
if(ps!=null) {
try {
ps.close();
}catch(SQLException s) {
s.printStackTrace();
}
}
if(conn!=null) {
try {
conn.close();
}catch(SQLException s) {
s.printStackTrace();
}
}
}
}
界面：
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>增加学生对象页面</title>
</head>
<body>
<script>
var addstus='<%=request.getParameter("addstu")%>';
if(addstus=="yes"){
alert("添加成功！");
}
</script>
<form action="servlet?method=addstu" method="post">
<table align="center">
<tr>
<td>学号:</td>
<td>
<input type="text" name="xuehao">
</td>
</tr>
<tr>
<td>姓名:</td>
<td>
<input type="text" name="name">
</td>
</tr>
<tr>
<td>班级:</td>
<td>
<input type="text" name="banji">
</td>
</tr>
<tr align="center">
<td>
<input type="submit" value="确定">
<input type="submit" value="返回" formaction="gongneng.jsp" formmethod="post">
</td>
</tr>
</table>
</form>
</body>
</html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>选择课程 </title>
</head>
<body>
<form>
<table align="center" border="1">
<tr align="center">
<td>
<input type="submit" value="高数" formaction="servlet?method=course&course=math" formmethod="post">
</td>
</tr>
<tr align="center">
<td>
<input type="submit" value="java" formaction="servlet?method=course&course=java" formmethod="post">
</td>
</tr>
<tr align="center">
<td>
<input type="submit" value="离散" formaction="servlet?method=course&course=lisan" formmethod="post">
</td>
</tr>
<tr align="center">
<td>
<input type="submit" value="退出登录" formaction="login.jsp" formmethod="post">
</td>
</tr>
</table>
</form>
</body>
</html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>删除学生对象页面</title>
</head>
<body>
<script>
var delet='<%=request.getParameter("delete")%>';
if(delet=='yes'){
alert("删除成功!");
}
</script>
<form action="servlet?method=deletestu" method="post">
<table align="center">
<tr>
<td>请输入要删除学生的学号:</td>
<td>
<input type="text" name="xuehao">
</td>
</tr>
<tr align="center">
<td>
<input type="submit" value="确定">
<input type="submit" value="返回" formaction="gongneng.jsp" formmethod="post">
</td>
</tr>
</table>
</form>
</body>
</html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>登记分数页面</title>
</head>
<body>
<form>
<table>
<tr>
<td>学号</td>
<td>姓名</td>
<td>班级</td>
<td>课程</td>
<td>教师</td>
<td>分数</td>
</tr>
<tr>
<c:forEach items="${list}" var="item">
<tr>
<td><input type="submit" value="${item.xuehao}" formaction="dengjier.jsp?xuehao=${item.xuehao}" formmethod="post"></td>
<td>${item.name}</td>
<td>${item.banji}</td>
<td>${item.kecheng}</td>
<td>${item.teacher}</td>
<td>${item.score}</td>
</tr>
</c:forEach>
</tr>
<tr>
<td>
<input type="submit" value="返回" formaction="gongneng.jsp" formmethod="post">
</td>
</tr>
</table>
</form>
</body>
</html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>登记分数</title>
</head>
<body>
<script>
var dengji='<%=request.getParameter("dengji")%>';
if(dengji=='yes'){
alert("登记分数成功!");
}
</script>
<form action="servlet?method=dengji2" method="post">
<table align="center">
<tr>
<td>请输入分数:</td>
<td>
<input type="text" name="score">
</td>
<td>
<input type="hidden" value="<%=request.getParameter("xuehao")%>" name="xuehao">
</td>
</tr>
<tr align="center">
<td>
<input type="submit" value="确定">
<input type="submit" value="返回" formaction="gongneng.jsp" formmethod="post">
</td>
</tr>
</table>
</form>
</body>
</html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>功能选择界面</title>
</head>
<body>
<form>
<table align="center" border="1">
<tr align="center">
<td>
<input type="submit" value="增加学生对象" formaction="addstu.jsp" formmethod="post">
</td>
</tr>
<tr align="center">
<td>
<input type="submit" value="删除学生对象" formaction="deletestu.jsp " formmethod="post">
</td>
</tr>
<tr align="center">
<td>
<input type="submit" value="登记分数" formaction="servlet?method=dengji" formmethod="post">
</td>
</tr>
<tr align="center">
<td>
<input type="submit" value="修改分数" formaction="update.jsp" formmethod="post">
</td>
</tr>
<tr align="center">
<td>
<input type="submit" value="学号查询" formaction="numselect.jsp" formmethod="post">
</td>
</tr>
<tr align="center">
<td>
<input type="submit" value="姓名查询" formaction="nameselect.jsp" formmethod="post">
</td>
</tr>
<tr align="center">
<td>
<input type="submit" value="升序" formaction="servlet?method=paixu&paixu=sheng" formmethod="post">
</td>
</tr>
<tr align="center">
<td>
<input type="submit" value="降序" formaction="servlet?method=paixu&paixu=jiang" formmethod="post">
</td>
</tr>
<tr align="center">
<td>
<input type="submit" value="返回" formaction="Course.jsp" formmethod="post">
</td>
</tr>
</table>
</form>
</body>
</html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>登录界面</title>
</head>
<script>
var error='<%=request.getParameter("error")%>';
if(error=='yes'){
alert('账号或密码错误!');
}
</script>
<body>
<form action="servlet?method=login" method="post">
<table align="center" border="1">
<tr>
<td>账号:</td>
<td>
<input type="text" name="num">
</td>
</tr>
<tr>
<td>密码:</td>
<td>
<input type="text" name="password">
</td>
</tr>
<tr align="center">
<td colspan="2">
<input type="submit" value="登录">
</td>
</tr>
</table>
</form>
</body>
</html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>姓名查询页面</title>
</head>
<body>
<form action="servlet?method=nameselect" method="post">
<table align="center">
<tr>
<td>
请输入查询学生姓名:
</td>
<td>
<input type="text" name="name">
</td>
</tr>
<tr align="center">
<td>
<input type="submit" value="确定">
</td>
</tr>
</table>
</form>
</body>
</html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>学号查询页面</title>
</head>
<body>
<form action="servlet?method=numselect" method="post">
<table align="center">
<tr>
<td>
请输入查询学生学号:
</td>
<td>
<input type="text" name="xuehao">
</td>
</tr>
<tr align="center">
<td>
<input type="submit" value="确定">
</td>
</tr>
</table>
</form>
</body>
</html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>排序页面</title>
</head>
<body>
<form>
<table>
<tr>
<td>学号</td>
<td>姓名</td>
<td>班级</td>
<td>课程</td>
<td>教师</td>
<td>分数</td>
</tr>
<tr>
<c:forEach items="${list}" var="item">
<tr>
<td>${item.xuehao}</td>
<td>${item.name}</td>
<td>${item.banji}</td>
<td>${item.kecheng}</td>
<td>${item.teacher}</td>
<td>${item.score}</td>
</tr>
</c:forEach>
</tr>
<tr>
<td>
<input type="submit" value="返回" formaction="gongneng.jsp" formmethod="post">
</td>
</tr>
</table>
</form>
</body>
</html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>查询结果页面</title>
</head>
<body>
<form>
<table>
<tr>
<td>学号</td>
<td>姓名</td>
<td>班级</td>
<td>课程</td>
<td>教师</td>
<td>分数</td>
</tr>
<tr>
<c:forEach items="${list}" var="item">
<tr>
<td>${item.xuehao}</td>
<td>${item.name}</td>
<td>${item.banji}</td>
<td>${item.kecheng}</td>
<td>${item.teacher}</td>
<td>${item.score}</td>
</tr>
</c:forEach>
</tr>
<tr>
<td>
<input type="submit" value="返回" formaction="gongneng.jsp" formmethod="post">
</td>
</tr>
</table>
</form>
</body>
</html>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>修改分数页面</title>
</head>
<body>
<script>
var update='<%=request.getParameter("update")%>';
if(update=='yes'){
alert("修改成功!");
}
</script>
<form action="servlet?method=update" method="post">
<table align="center">
<tr>
<td>请输入要修改学生学号:</td>
<td>
<input type="text" name="xuehao">
</td>
</tr>
<tr>
<td>请输入修改分数:</td>
<td>
<input type="text" name="score">
</td>
</tr>
<tr align="center">
<td>
<input type="submit" value="确定">
<input type="submit" value="返回" formaction="gongneng.jsp" formmethod="post">
</td>
</tr>
</table>
</form>
</body>
</html>
明天准备继续学习web。