在数据先建三个表,分别是:学生表、教员表、班级表
项目中所建的文件
首页:
所有的dao方法:
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.Bj;
import com.entity.Teacher;
import com.entity.Student;
import com.util.DBHelper;
public class StudentDao implements IStudentDao {
Connection con;
PreparedStatement ps;
ResultSet rs;
/**
* 查询所有的方法
*/
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()) {
Student s = new Student(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4),
rs.getString(5));
list.add(s);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBHelper.myClose(con, ps, rs);
}
return list;
}
/**
* 查询班级
*/
public List<Bj> getBj() {
List<Bj> list = new ArrayList<Bj>();
try {
con = DBHelper.getCon();
String sql = "select * from class";
ps = con.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
Bj b = new Bj(rs.getInt(1), rs.getString(2));
list.add(b);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBHelper.myClose(con, ps, rs);
}
return list;
}
/**
* 查询教员
*/
public List<Teacher> getJy() {
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()) {
Teacher y = new Teacher(rs.getInt(1), rs.getString(2));
list.add(y);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBHelper.myClose(con, ps, rs);
}
return list;
}
/**
* 删除
*/
public int del(int sid) {
try {
con = DBHelper.getCon();
String sql = "delete student where sid=?";
ps = con.prepareStatement(sql);
ps.setInt(1, sid);
int n = ps.executeUpdate();
return n;
} catch (Exception e) {
e.printStackTrace();
} finally {
DBHelper.myClose(con, ps, rs);
}
return 0;
}
/**
* 增加
*/
public int add(Student s) {
try {
con = DBHelper.getCon();
String sql = "insert into student values(?,?,?,?)";
ps = con.prepareStatement(sql);
ps.setString(1, s.getBname());
ps.setString(2, s.getYname());
ps.setString(3, s.getSname());
ps.setString(4, s.getSah());
int n = ps.executeUpdate();
return n;
} catch (Exception e) {
e.printStackTrace();
} finally {
DBHelper.myClose(con, ps, rs);
}
return 0;
}
/**
* 根据id查看
*/
public Student getById(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()) {
Student s = new Student(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4),
rs.getString(5));
return s;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBHelper.myClose(con, ps, rs);
}
return null;
}
/**
* 修改
*/
public int upd(Student s) {
try {
con = DBHelper.getCon();
String sql = "update student set sclass=?,steacher=?,sname=?,sah=? where sid=?";
ps = con.prepareStatement(sql);
ps.setString(1, s.getBname());
ps.setString(2, s.getYname());
ps.setString(3, s.getSname());
ps.setString(4, s.getSah());
ps.setInt(5, s.getSid());
int n = ps.executeUpdate();
return n;
} catch (Exception e) {
e.printStackTrace();
} finally {
DBHelper.myClose(con, ps, rs);
}
return 0;
}
/**
* 模糊查询
*/
public List<Student> getAll(String jy, String bj, String ah) {
List<Student> list = new ArrayList<Student>();
try {
con = DBHelper.getCon();
String sql = "select * from student where sclass like ? and steacher like ? and sah like ?";
ps = con.prepareStatement(sql);
ps.setString(1, "%" + bj + "%");
ps.setString(2, "%" + jy + "%");
ps.setString(3, "%" + ah + "%");
rs = ps.executeQuery();
while (rs.next()) {
Student s = new Student(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4),
rs.getString(5));
list.add(s);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBHelper.myClose(con, ps, rs);
}
return list;
}
}
增加界面
添加成功点确定会跳入主界面
add.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!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>Insert title here</title>
<style type="text/css">
table,tr{
border-collapse: collapse;
}
td{
text-align: center;
}
</style>
</head>
<body>
<h3 align="center">新增页面</h3>
<form action="add.do">
<table border="" width="500px" height="400px" align="center">
<tr>
<td>名字 </td>
<td><input type="text" name="sname"></td>
</tr>
<tr>
<td>教员:</td>
<td>
<select name="yname">
<option value="">--请选择教员--</option>
<option value="张哥">张哥</option>
<option value="李哥">李哥</option>
<option value="王哥">王哥</option>
<option value="赵哥">赵哥</option>
</select>
</td>
</tr>
<tr>
<td>班级:</td>
<td>
<select name="bname">
<option value="">--请选择班级--</option>
<option value="一班">一班</option>
<option value="二班">二班</option>
<option value="三班">三班</option>
<option value="四班">四班</option>
</select>
</td>
</tr>
<tr>
<td>
爱好:</td>
<td>
<input type="checkbox" name="sah" value="篮球" >篮球
<input type="checkbox" name="sah" value="足球" >足球
<input type="checkbox" name="sah" value="唱歌" >唱歌
<input type="checkbox" name="sah" value="跳舞" >跳舞
</td>
</tr>
<tr>
<td>按键</td>
<td><button>确认</button> <input type="reset"/></td>
</tr>
</table>
</form>
<h3 align="center"><a href="index.do">返回首页</a></h3>
</body>
</html>
addservler
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.IStudentDao;
import com.dao.StudentDao;
import com.entity.Student;
/**
* Servlet implementation class AddServlrt
*/
@WebServlet("/add.do")
public class AddServlrt extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public AddServlrt() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
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");
String sname=request.getParameter("sname");
String yname=request.getParameter("yname");
String bname=request.getParameter("bname");
String[] ah=request.getParameterValues("sah");
String sah = "" ;
for (String a : ah) {
sah+=a+",";
}
Student s=new Student(0, bname, yname, sname, sah);
IStudentDao isb=new StudentDao();
int i = isb.add(s);
PrintWriter out = response.getWriter();
if(i>0) {
out.print("<script>alert('增加成功');location.href='index.do'</script>");
}else {
out.print("<script>alert('增加失败');location.href='index.do'</script>");
}
}
}
删除
在删除时会确认一遍你是否要删除,防止误删
点完确定后会显示删除成功
删除成功后跳入主界面
delServlet
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.IStudentDao;
import com.dao.StudentDao;
/**
* Servlet implementation class DelServlet
*/
@WebServlet("/del.do")
public class DelServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public DelServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
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();
int tid=Integer.parseInt(request.getParameter("sid"));
IStudentDao isb=new StudentDao();
int n=isb.del(tid);
if(n>0) {
out.print("<script>alert('删除成功');location.href='index.do'</script>");
}else {
out.print("<script>alert('删除失败');location.href='index.do'</script>");
}
}
}
修改
修改前:
修改的界面:
修改成功后会跳入主界面
upd 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 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>Insert title here</title>
<style type="text/css">
table, tr {
border-collapse: collapse;
}
td {
text-align: center;
}
</style>
</head>
<body>
<h3 align="center">修改页面</h3>
<form action="upd.do">
<table border="" width="500px" height="400px" align="center">
<input type="hidden" name="sid" value="${s.sid}">
<td>名字</td>
<td><input type="text" name="sname" value="${s.sname }"></td>
</tr>
<tr>
<td>教员:</td>
<td><select name="yname">
<option value="张哥" ${s.yname=="张哥"?"selected":"" }>张哥</option>
<option value="李哥" ${s.yname=="李哥"?"selected":"" }>李哥</option>
<option value="王哥" ${s.yname=="王哥"?"selected":"" }>王哥</option>
<option value="赵哥" ${s.yname=="赵哥"?"selected":"" }>赵哥</option>
</select></td>
</tr>
<tr>
<td>班级:</td>
<td><select name="bname">
<option value="一班" ${s.bname=="一班"?"selected":"" }>一班</option>
<option value="二班" ${s.bname=="二班"?"selected":"" }>二班</option>
<option value="三班" ${s.bname=="三班"?"selected":"" }>三班</option>
<option value="四班" ${s.bname=="四班"?"selected":"" }>四班</option>
</select></td>
</tr>
<tr>
<td>爱好:</td>
<td>
<input type="checkbox" name="sah" value="篮球"
<c:forEach var="i" items="${ ah }">
<c:if test="${ i=='篮球' }"> checked</c:if>
</c:forEach>>篮球
<input type="checkbox" name="sah" value="足球"
<c:forEach var="i" items="${ ah }">
<c:if test="${ i=='足球' }"> checked</c:if>
</c:forEach>>足球
<input type="checkbox" name="sah" value="唱歌"
<c:forEach var="i" items="${ ah }">
<c:if test="${ i=='唱歌' }"> checked</c:if>
</c:forEach>>唱歌
<input type="checkbox" name="sah" value="跳舞"
<c:forEach var="i" items="${ ah }">
<c:if test="${ i=='跳舞' }"> checked</c:if>
</c:forEach>>跳舞
</td>
</tr>
<tr>
<td>按键</td>
<td><button>确认修改</button></td>
</tr>
</table>
</form>
<h3 align="center">
<a href="index.do">返回首页</a>
</h3>
</body>
</html>
updservlet
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.biz.IXsBao;
import com.biz.XsBao;
import com.dao.IStudentDao;
import com.dao.StudentDao;
import com.entity.Student;
/**
* Servlet implementation class UpdServlet
*/
@WebServlet("/upd.do")
public class UpdServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public UpdServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
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");
String sname=request.getParameter("sname");
String yname=request.getParameter("yname");
String bname=request.getParameter("bname");
String[] ah=request.getParameterValues("sah");
String sah = "" ;
for (String a : ah) {
sah+=a+",";
}
int sid=Integer.parseInt(request.getParameter("sid"));
Student s=new Student(sid, bname, yname, sname, sah);
IStudentDao isb=new StudentDao();
int i = isb.upd(s);
PrintWriter out = response.getWriter();
if(i>0) {
out.print("<script>alert('修改成功');location.href='index.do'</script>");
}else {
out.print("<script>alert('修改失败');location.href='index.do'</script>");
}
}
}
查询
可以查询所有、查询班级、查询教员
还可以根据id进行查询, 也可以进行模糊查询