一、本期的内容,达成以下效果
首先我使用SQL server建立了三个表
以下为我建立的文件:
同时还需要导入这些包:
二、展示代码
使全部数据展示在主界面,建立servlet:index.do:
package com.servlet;
import java.io.IOException;
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;import com.dao.StudentDao;
import com.entity.Class;
import com.entity.Student;
import com.entity.Teacher;/**
* Servlet implementation class IndexServlet
*/
@WebServlet("/index.do")
public class IndexServlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
StudentDao sd=new StudentDao();
List<Student> list=sd.getAll();
request.setAttribute("list", list);
List<Teacher> teacher=sd.getTeacher();
request.setAttribute("teacher", teacher);
List<Class> sclass=sd.getclass();
request.setAttribute("sclass", sclass);
request.getRequestDispatcher("Index.jsp").forward(request, response);
}}
主界面展示:Index.jsp:
<%@ 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>Insert title here</title>
</head>
<body>
<table border="1">
<tr >
<td colspan="6">
<form action="key.do">
教员:<select class="select" name="teacher">
<option value="null">--选择教员--</option>
<c:forEach var="y" items="${ teacher }"><option value="${ y.teacher }" ${ y.teacher==teacher?"selected":"" }>${y.teacher }</option>
</c:forEach>
</select>
班级:<select class="select" name="sclass">
<option value="null">--选择班级--</option>
<c:forEach var="b" items="${ sclass }"><option value="${ b.sclass }" ${ b.sclass==sclass?"selected":"" }>${b.sclass }</option>
</c:forEach>
</select>
学生爱好:<input type="checkbox" value="篮球" name="shobby" <c:forEach var="i" items="${sah }">
<c:if test="${i=='篮球' }">checked</c:if>
</c:forEach>
>篮球
<input type="checkbox" value="足球" name="shobby" <c:forEach var="i" items="${sah }">
<c:if test="${i=='足球' }">checked</c:if>
</c:forEach>
>足球
<input type="checkbox" value="唱歌" name="shobby" <c:forEach var="i" items="${sah }">
<c:if test="${i=='唱歌' }">checked</c:if>
</c:forEach>
>唱歌
<input type="checkbox" value="跳舞" name="shobby" <c:forEach var="i" items="${sah }">
<c:if test="${i=='跳舞' }">checked</c:if>
</c:forEach>
>跳舞
<button>查询</button>
</form>
<button οnclick="location.href='Add.jsp'">增加学生</button>
</td>
</tr>
<tr>
<td>学生id</td>
<td>学生姓名</td>
<td>学生教员</td>
<td>所在班级</td>
<td>学生爱好</td>
<td>操作</td>
</tr>
<c:forEach var="i" items="${list }">
<tr>
<td>${i.sid }</td>
<td>${i.sname }</td>
<td>${i.tname }</td>
<td>${i.sclass }</td>
<td>${i.shobby }</td>
<td>
<a href="see.do?sid=${i.sid }">修改</a>
<a οnclick="return confirm('确定要删除吗?')" href="del.do?sid=${i.sid }">删除</a>
</td>
</tr>
</c:forEach>
</table>
</body>
</html>
所呈现出的效果(有点简陋):
接着一一完成它的功能:
模糊查询(从Index.jsp跳到key.do):
package com.servlet;
import java.io.IOException;
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;import com.dao.StudentDao;
import com.entity.Class;
import com.entity.Student;
import com.entity.Teacher;@WebServlet("/key.do")
public class KeyServlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
StudentDao sd=new StudentDao();
String steacher= request.getParameter("teacher");
String ah = request.getParameter("shobby");
String ssclass = request.getParameter("sclass");
if(steacher==null&&ssclass==null) {
List<Student> list=sd.getAll();
request.setAttribute("list", list);
List<Teacher> teacher=sd.getTeacher();
request.setAttribute("teacher", teacher);
List<Class> sclass=sd.getclass();
request.setAttribute("sclass", sclass);
request.getRequestDispatcher("Index.jsp").forward(request, response);
}else {
List<Student> list=sd.getkey(steacher,ssclass,ah);
request.setAttribute("list", list);
List<Class> sclass=sd.getclass();
List<Teacher> teacher=sd.getTeacher();
request.setAttribute("teacher", teacher);
request.setAttribute("sclass", sclass);
request.getRequestDispatcher("Index.jsp").forward(request, response);
}
}}
结果:
接着我们来增加学生:
首先得有增加界面:Add.jsp:
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<form action="add.do" method="post">
姓名:<input name="sname"><br>
教员:<select class="select" name="steacher">
<option value="小哥">小哥</option>
<option value="晓哥">晓哥</option>
<option value="小葛">小葛</option>
<option value="胡哥">胡哥</option>
</select>
<br>
班级:<select class="select" name="sclass">
<option value="一班">一班</option>
<option value="二班">二班</option>
<option value="三班">三班</option>
<option value="四班">四班</option>
</select>
<br>
爱好:<input type="checkbox" value="篮球" name="shobby">篮球
<input type="checkbox" value="足球" name="shobby">足球
<input type="checkbox" value="唱歌" name="shobby">唱歌
<input type="checkbox" value="跳舞" name="shobby">跳舞
<br>
<button>增加</button>
<button type="reset">重置</button>
</form>
</body>
</html>
得到数据servlet:add.do:
package com.servlet;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.Arrays;import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;import com.dao.StudentDao;
import com.entity.Student;/**
* Servlet implementation class AddServlet
*/
@WebServlet("/add.do")
public class AddServlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
PrintWriter out = response.getWriter();
String sname=request.getParameter("sname");
String steacher=request.getParameter("steacher");
String sclass=request.getParameter("sclass");
String[] shobby=request.getParameterValues("shobby");
String sh="";
for (String s : shobby) {
sh=sh+s+",";
}
StudentDao sd=new StudentDao();
int d=sd.add(new Student(0, sname, steacher, sclass, sh));
if(d>0) {
out.print("<script>alert('增加成功');location.href='index.do'</script>");
}else {
out.print("<script>alert('增加失败');location.href='index.do'</script>");
}
}
}
展示结果:
然后修改信息:
首先得到个人信息:
servlet得到信息:see.do:
package com.servlet;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;import com.dao.StudentDao;
import com.entity.Student;/**
* Servlet implementation class SeeServlet
*/
@WebServlet("/see.do")
public class SeeServlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String id=request.getParameter("sid");
StudentDao sd=new StudentDao();
Student s=sd.see(Integer.parseInt(id));
String shobby=s.getShobby();
String [] sh=shobby.split(",");
request.setAttribute("s", s);
request.setAttribute("sah", sh);
request.getRequestDispatcher("Upd.jsp").forward(request, response);
}
}
在将信息展示在界面Upd.do:
<%@ 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>Insert title here</title>
</head>
<body>
<form action="upd.do" method="post">
<input name="sid" type="hidden" value="${s.sid}" >
姓名:<input name="sname" value="${s.sname }"><br>
教员:<select class="select" name="steacher"">
<option value="${s.tname }" ${ s.tname==steacher?"selected":"" }>${ s.tname}</option>
</select>
<br>
班级:<select class="select" name="sclass" >
<option value="${s.sclass }" ${ s.sclass==sclass?"selected":"" }>${s.sclass }</option>
</select>
<br>
爱好:<input type="checkbox" value="篮球" name="shobby" <c:forEach var="i" items="${sah }">
<c:if test="${i=='篮球' }">checked</c:if>
</c:forEach>
>篮球
<input type="checkbox" value="足球" name="shobby" <c:forEach var="i" items="${sah }">
<c:if test="${i=='足球' }">checked</c:if>
</c:forEach>
>足球
<input type="checkbox" value="唱歌" name="shobby" <c:forEach var="i" items="${sah }">
<c:if test="${i=='唱歌' }">checked</c:if>
</c:forEach>
>唱歌
<input type="checkbox" value="跳舞" name="shobby" <c:forEach var="i" items="${sah }">
<c:if test="${i=='跳舞' }">checked</c:if>
</c:forEach>
>跳舞
<br>
<button>修改</button>
<button type="reset">重置</button>
</form>
</body>
</html>
将信息显示如下:
然后进行修改upd.do:
package com.servlet;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.Arrays;import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;import com.dao.StudentDao;
import com.entity.Student;/**
* Servlet implementation class UpdServlet
*/
@WebServlet("/upd.do")
public class UpdServlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
PrintWriter out = response.getWriter();
String sid=request.getParameter("sid");
String sname=request.getParameter("sname");
String steacher=request.getParameter("steacher");
String sclass=request.getParameter("sclass");
String[] shobby=request.getParameterValues("shobby");
String sh="";
for (String s : shobby) {
sh+=s+",";
}
StudentDao sd=new StudentDao();
int n=sd.upd(new Student(Integer.parseInt(sid), sname, steacher, sclass, sh));
if(n>0) {
out.print("<script>alert('修改成功');location.href='index.do'</script>");
}else {
out.print("<script>alert('修改成功');location.href='index.do'</script>");
}
}}
接着删除,写个servlet就好了:
package com.servlet;
import java.io.IOException;
import java.io.PrintWriter;import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;import com.dao.StudentDao;
/**
* Servlet implementation class DelServlet
*/
@WebServlet("/del.do")
public class DelServlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
PrintWriter out = response.getWriter();
String sid=request.getParameter("sid");
StudentDao sd=new StudentDao();
int n=sd.delStu(Integer.parseInt(sid));
if(n>0) {
out.print("<script>alert('删除成功');location.href='index.do'</script>");
}else {
out.print("<script>alert('删除失败');location.href='index.do'</script>");
}
}
}
具有弹出框,给出了选择,以免误删:
当然不要忘了我写的所有方法:
package com.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;import com.entity.Class;
import com.entity.Student;
import com.entity.Teacher;
import com.util.DBHelper;
public class StudentDao {
Connection con=null;
PreparedStatement ps=null;
ResultSet rs=null;
/**
* 查看所有学生
* @return
*/
public List<Student> getAll(){
List<Student> list=new ArrayList<Student>();
try {
con=DBHelper.getCon();
String sql="select * from student";
ps=con.prepareStatement(sql);
rs=ps.executeQuery();
while(rs.next()) {
int sid=rs.getInt(1);
String sname=rs.getString(2);
String tname=rs.getString(3);
String sclass=rs.getString(4);
String shobby=rs.getString(5);
Student s=new Student(sid, sname, tname, sclass, shobby);
list.add(s);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
DBHelper.myClose(con, ps, rs);
}
return list;
}
/**
* 得到所有老师
* @return
*/
public List<Teacher> getTeacher(){
List<Teacher> list=new ArrayList<Teacher>();
try {
con=DBHelper.getCon();
String sql="select * from teacher";
ps=con.prepareStatement(sql);
rs=ps.executeQuery();
while(rs.next()) {
String tname=rs.getString(1);
Teacher s=new Teacher(tname);
list.add(s);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
DBHelper.myClose(con, ps, rs);
}
return list;
}
/**
* 得到所有班级
* @return
*/
public List<Class> getclass(){
List<Class> list=new ArrayList<Class>();
try {
con=DBHelper.getCon();
String sql="select * from tb_class";
ps=con.prepareStatement(sql);
rs=ps.executeQuery();
while(rs.next()) {
String sclass=rs.getString(1);
Class s=new Class(sclass);
list.add(s);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
DBHelper.myClose(con, ps, rs);
}
return list;
}
/**
* 增加
* @param s
* @return
*/
public int add(Student s) {
try {
con=DBHelper.getCon();
String sql="insert into student values(?,?,?,?)";
ps=con.prepareStatement(sql);
ps.setString(1, s.getSname());
ps.setString(2, s.getTname());
ps.setString(3, s.getSclass());
ps.setString(4, s.getShobby());
return ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally {
DBHelper.myClose(con, ps, rs);
}
return 0;
}
/**
* 删除
* @param sid
* @return
*/
public int delStu(int sid) {
try {
con=DBHelper.getCon();
String sql="delete student where sid=?";
ps=con.prepareStatement(sql);
ps.setInt(1, sid);
return ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally {
DBHelper.myClose(con, ps, rs);
}
return 0;
}
/**
* 查看单个学生
* @param sid
* @return
*/
public Student see(int sid) {
try {
con=DBHelper.getCon();
String sql="select * from student where sid=?";
ps=con.prepareStatement(sql);
ps.setInt(1, sid);
rs=ps.executeQuery();
while(rs.next()) {
int id=rs.getInt(1);
String sname=rs.getString(2);
String tname=rs.getString(3);
String sclass=rs.getString(4);
String shobby=rs.getString(5);
Student s=new Student(id, sname,tname, sclass,shobby);
return s;
}
} catch (Exception e) {
e.printStackTrace();
}finally {
DBHelper.myClose(con, ps, rs);
}
return null;
}
/**
* 修改
* @param s
* @return
*/
public int upd(Student s) {
try {
con=DBHelper.getCon();
String sql="update student set sname=?,tname=?,sclass=?,shobby=? where sid=?";
ps=con.prepareStatement(sql);
ps.setString(1, s.getSname());
ps.setString(2, s.getTname());
ps.setString(3, s.getSclass());
ps.setString(4, s.getShobby());
ps.setInt(5, s.getSid());
return ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally {
DBHelper.myClose(con, ps, rs);
}
return 0;
}
/**
* 模糊查询
* @param key1教员名字关键字
* @param key2班级关键字
* @param key3爱好关键字
* @return
*/
public List<Student> getkey(String teacher,String sclass,String shobby){
List<Student> list = new ArrayList<Student>();
try {
con=DBHelper.getCon();
ps = con.prepareStatement("select * from student where tname like ? and sclass like ? and shobby like ?");
ps.setString(1, "%"+teacher+"%");
ps.setString(2, "%"+sclass+"%");
ps.setString(3, "%"+shobby+"%");
rs = ps.executeQuery();
while (rs.next()) {
Student s=new Student();
s.setSid(rs.getInt(1));
s.setSname(rs.getString(2));
s.setTname(rs.getString(3));
s.setSclass(rs.getString(4));
s.setShobby(rs.getString(5));
list.add(s);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBHelper.myClose(con, ps, rs);
}
return list;
}
}