考试卡的增删改查

Ka类：

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 passage.Card;
import passage.Student;
import passage.Card;
import tool.Tool;

public class Ka {
	public void add(Card stu) {
		Connection conn=Tool.getConnection();
		String sql="insert into biao (cid,cname,cteacher,couid,counat,credit,couclass,coum,testway,testdata,testcount,testm,testgrade,testeva,testa) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		try {
			PreparedStatement ps=conn.prepareStatement(sql);
			ps.setString(1,stu.getCardId());
			ps.setString(2,stu.getCourseName());
	        ps.setString(3, stu.getCourseTeacher());
	        ps.setString(4, stu.getCourseID());
	        ps.setString(5, stu.getCourseNature());
	        ps.setString(6, stu.getCredit());
	        ps.setString(7, stu.getCourseClass());
	        ps.setString(8,stu.getCourseMajor());
			ps.setString(9,stu.getTestWay());
	        ps.setString(10, stu.getTestData());
	        ps.setString(11, stu.getTestCount());
	        ps.setString(12, stu.getTestMethod());
	        ps.setString(13, stu.getTestGrade());
	        ps.setString(14, stu.getTestEvaluation());
	        ps.setString(15, stu.getTestAnalysis());
	        ps.executeUpdate();
		}catch(SQLException e) {
	        e.printStackTrace();
	    }finally{
	    	Tool.release(conn, null, null);
	    }
		
	}
	public void del(String num) {//删除
		Connection connn=null;
		PreparedStatement preparedStatement=null;
	    try {
	        connn= Tool.getConnection();
	        String sql="delete from biao where cid=?";
	        preparedStatement=connn.prepareStatement(sql);
	        preparedStatement.setString(1,num);
	        preparedStatement.executeUpdate();
	        } catch (SQLException e) {
	            e.printStackTrace();
	        }finally {
	            Tool.release(connn,preparedStatement,null);
	        }
	}
	public List<Card> findAll(){//浏览
        List<Card> list=new ArrayList<>();
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        try {
            //调用Tool连接mysql数据库
            connection= Tool.getConnection();
            String sql="select * from biao";//查询语句
            preparedStatement=connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                //从resultSet拿出每个属性数据
			String cid=resultSet.getString("cid");
			String cname=resultSet.getString("cname");
			String time=resultSet.getString("cteacher");
			String couid=resultSet.getString("couid");
			String counat=resultSet.getString("counat");
			String credit=resultSet.getString("credit");
			String couclass=resultSet.getString("couclass");
			String coum=resultSet.getString("coum");
			String testway=resultSet.getString("testway");
			String testdata=resultSet.getString("testdata");
			String testcount=resultSet.getString("testcount");
			String testm=resultSet.getString("testm");
			String testgrade=resultSet.getString("testgrade");
			String testeva=resultSet.getString("testeva");
			String testa=resultSet.getString("testa");
              
			//String name,String bianhao, String time, String jieci, String xueyuan, String teanum, String teaname,
			//String renshu
                Card student = new Card(cid,null,cname,time,couid,counat,credit,couclass,coum,testway,testdata,testcount,testm,testgrade,testeva,testa,null,null,null,null,null);
                list.add(student);//可能多条数据，放到集合中
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //调用JDBCTools，关闭connection，preparedStatement，resultSet
            Tool.release(connection,preparedStatement,resultSet);
        }
        return list;
    }
	public Card finds(String num) {//查询
	Connection conn=Tool.getConnection();
	PreparedStatement pre=null;
	ResultSet resultSet=null;
	Card stu=null;
	String sql="SELECT *FROM biao where cid=? ";
	try {
	pre=conn.prepareStatement(sql);
	pre.setString(1, num);
	resultSet=pre.executeQuery();
	while(resultSet.next()) {
		String cid=resultSet.getString("cid");
			String cname=resultSet.getString("cname");
			String time=resultSet.getString("cteacher");
			String couid=resultSet.getString("couid");
			String counat=resultSet.getString("counat");
			String credit=resultSet.getString("credit");
			String couclass=resultSet.getString("couclass");
			String coum=resultSet.getString("coum");
			String testway=resultSet.getString("testway");
			String testdata=resultSet.getString("testdata");
			String testcount=resultSet.getString("testcount");
			String testm=resultSet.getString("testm");
			String testgrade=resultSet.getString("testgrade");
			String testeva=resultSet.getString("testeva");
			String testa=resultSet.getString("testa");
			 stu = new Card(cid,null,cname,time,couid,counat,credit,couclass,coum,testway,testdata,testcount,testm,testgrade,testeva,testa,null,null,null,null,null);
	}
	}
catch(SQLException e) {
		
        e.printStackTrace();
    }finally{
    	Tool.release(conn, pre, resultSet);
    }
	return stu;
}
	public void Up(String cid,String kname,String teachername,String kenum,String xingzhi,String xuefen,String ban,String zhuanye,String fangshi,String time,String ren,String mingti,String zucheng,String kf,String fenxi ) {//修改
	Connection connn=null;
	PreparedStatement preparedStatement=null;
	try {
		connn=Tool.getConnection();
        String sql="update biao set cname=?,cteacher=?,couid=?,counat=?,credit=?,couclass=?,coum=?,testway=?,testdata=?,testcount=?,testm=?,testgrade=?,testeva=?,testa=? where cid=?";
        preparedStatement=connn.prepareStatement(sql);
        preparedStatement.setString(1,kname);
        preparedStatement.setString(2,teachername);
        preparedStatement.setString(3,kenum);
        preparedStatement.setString(4,xingzhi);
        preparedStatement.setString(5,xuefen);
        preparedStatement.setString(6,ban);
         preparedStatement.setString(7,zhuanye);
        preparedStatement.setString(8,fangshi);
        preparedStatement.setString(9,time);
        preparedStatement.setString(10,ren);
        preparedStatement.setString(11,mingti);
        preparedStatement.setString(12,zucheng);
         preparedStatement.setString(13,kf);
        preparedStatement.setString(14,fenxi);
        preparedStatement.setString(15,cid);
        preparedStatement.executeUpdate();
	}
	catch(SQLException e) {
        e.printStackTrace();
    } finally {
        Tool.release(connn,preparedStatement,null);
    }
}
	public String  find(String name,String kclass,String zhuanye) {//查询
		Connection conn=Tool.getConnection();
		PreparedStatement pre=null;
		ResultSet resultSet=null;
		String stu=null;
			if(name!=null&&kclass!=null&&zhuanye!=null) {
				try {
				String sql="SELECT *FROM biao where cname=? and couclass=? and coum=?";
		pre=conn.prepareStatement(sql);
		pre.setString(1, name);
		pre.setString(2, kclass);
		pre.setString(3, zhuanye);
		resultSet=pre.executeQuery();
		while(resultSet.next()) {
			String cid=resultSet.getString("cid");
				String cname=resultSet.getString("cname");
				String couclass=resultSet.getString("couclass");
				String coum=resultSet.getString("coum");
					 stu="编号:"+cid+" 课程名称:"+cname+" 授课班级:"+couclass+" 授课专业:"+coum;
		}
				}
				catch(SQLException e) {
					
			        e.printStackTrace();
			    }finally{
			    	Tool.release(conn, pre, resultSet);
			    }
		}else if(name==null&&kclass!=null&&zhuanye!=null) {
			try {
			String sql="SELECT *FROM biao where couclass=? and coum=?";
			pre=conn.prepareStatement(sql);
			pre.setString(1, kclass);
			pre.setString(2, zhuanye);
			resultSet=pre.executeQuery();
			while(resultSet.next()) {
				String cid=resultSet.getString("cid");
					String cname=resultSet.getString("cname");
					String couclass=resultSet.getString("couclass");
					String coum=resultSet.getString("coum");
						 stu="编号:"+cid+" 课程名称:"+cname+" 授课班级:"+couclass+" 授课专业:"+coum;
			}
			}	catch(SQLException e) {
				
		        e.printStackTrace();
		    }finally{
		    	Tool.release(conn, pre, resultSet);
		    }
		}else if(name==null&&kclass!=null&&zhuanye==null) {
			try {
			String sql="SELECT *FROM biao where couclass=?";
			pre=conn.prepareStatement(sql);
			pre.setString(1, kclass);
			resultSet=pre.executeQuery();
			while(resultSet.next()) {
				String cid=resultSet.getString("cid");
					String cname=resultSet.getString("cname");
					String couclass=resultSet.getString("couclass");
					String coum=resultSet.getString("coum");
						 stu="编号:"+cid+" 课程名称:"+cname+" 授课班级:"+couclass+" 授课专业:"+coum;
			}
			}	catch(SQLException e) {
				
		        e.printStackTrace();
		    }finally{
		    	Tool.release(conn, pre, resultSet);
		    }
		}else if(name==null&&kclass==null&&zhuanye!=null) {
			try {
			String sql="SELECT *FROM biao where coum=?";
			pre=conn.prepareStatement(sql);
			pre.setString(1,zhuanye);
			resultSet=pre.executeQuery();
			while(resultSet.next()) {
				String cid=resultSet.getString("cid");
					String cname=resultSet.getString("cname");
					String couclass=resultSet.getString("couclass");
					String coum=resultSet.getString("coum");
						 stu="编号:"+cid+" 课程名称:"+cname+" 授课班级:"+couclass+" 授课专业:"+coum;
			}}	catch(SQLException e) {
				
		        e.printStackTrace();
		    }finally{
		    	Tool.release(conn, pre, resultSet);
		    }
		}else if(name!=null&&kclass==null&&zhuanye!=null) {
			try {
			String sql="SELECT *FROM biao where cname=? and coum=?";
			pre=conn.prepareStatement(sql);
			pre.setString(1, name);
			pre.setString(2, zhuanye);
			resultSet=pre.executeQuery();
			while(resultSet.next()) {
				String cid=resultSet.getString("cid");
					String cname=resultSet.getString("cname");
					String couclass=resultSet.getString("couclass");
					String coum=resultSet.getString("coum");
						 stu="编号:"+cid+" 课程名称:"+cname+" 授课班级:"+couclass+" 授课专业:"+coum;
			}
			}	catch(SQLException e) {
				
		        e.printStackTrace();
		    }finally{
		    	Tool.release(conn, pre, resultSet);
		    }
		}else if(name!=null&&kclass==null&&zhuanye==null) {
			try {
			String sql="SELECT *FROM biao where cname=? ";
			pre=conn.prepareStatement(sql);
			pre.setString(1, name);
			resultSet=pre.executeQuery();
			while(resultSet.next()) {
				String cid=resultSet.getString("cid");
					String cname=resultSet.getString("cname");
					String couclass=resultSet.getString("couclass");
					String coum=resultSet.getString("coum");
						 stu="编号:"+cid+" 课程名称:"+cname+" 授课班级:"+couclass+" 授课专业:"+coum;
			}
			}	catch(SQLException e) {
				
		        e.printStackTrace();
		    }finally{
		    	Tool.release(conn, pre, resultSet);
		    }
		}else {
			try {
			String sql="SELECT *FROM biao where cname=? and kclass=?";
			pre=conn.prepareStatement(sql);
			pre.setString(1, name);
			pre.setString(2, kclass);
			resultSet=pre.executeQuery();
			while(resultSet.next()) {
				String cid=resultSet.getString("cid");
					String cname=resultSet.getString("cname");
					String couclass=resultSet.getString("couclass");
					String coum=resultSet.getString("coum");
						 stu="编号:"+cid+" 课程名称:"+cname+" 授课班级:"+couclass+" 授课专业:"+coum;
			}
		}
	catch(SQLException e) {
			
	        e.printStackTrace();
	    }finally{
	    	Tool.release(conn, pre, resultSet);
	    }
		}
		return stu;
		
	}
}

　　Kasev类：

import java.io.IOException;
import java.util.List;
import passage.Card;
import passage.Student;
import passage.Card;
import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
@WebServlet("/ka")
public class Kasev extends HttpServlet{
	Ka d=new Ka();
	public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{
		String method=request.getParameter("method");
		if (method==null){
            method="findall";
        }
		switch (method){
		case "add":
			String cid= request.getParameter("num");
			String kname=request.getParameter("kname");
			String teachername=request.getParameter("teachername");
			String kenum=request.getParameter("kenum");
			String xingzhi=request.getParameter("xingzhi");
			String xuefen=request.getParameter("xuefen");
			String ban=request.getParameter("ban");
			String zhuanye=request.getParameter("zhuanye");
			String fangshi=request.getParameter("fangshi");
			String time=request.getParameter("time");
			String ren=request.getParameter("ren");
			String mingti=request.getParameter("mingti");
			String zucheng=request.getParameter("zucheng");
			String kf=request.getParameter("kf");
			String fenxi=request.getParameter("fenxi");
			Card c=new Card(cid,null,kname,teachername,kenum,xingzhi,xuefen,ban,zhuanye,fangshi,time,ren,mingti,zucheng,kf,fenxi,null,null,null,null,null);
			d.add(c);
			request.setAttribute("test", "添加并且保存成功");
	   		request.getRequestDispatcher("xts.jsp").forward(request, response);
	   		break;
		case "delete":
			String num2=request.getParameter("num");
            d.del(num2);
            request.setAttribute("test", "删除成功");
    		request.getRequestDispatcher("xts.jsp").forward(request, response);
            break;
		case "findall":
			 List<Card> list = d.findAll();
	            request.setAttribute("test", list);
	    		request.getRequestDispatcher("mmesss.jsp").forward(request, response);
	    		break;
		case "findalls":
			 List<Card> lists = d.findAll();
	            request.setAttribute("test", lists);
	    		request.getRequestDispatcher("ll.jsp").forward(request, response);
	    		break;
		case "findByid":String num3=request.getParameter("num");
    		Card s=d.finds(num3);
    		request.setAttribute("test", s);
    		request.getRequestDispatcher("updatec.jsp").forward(request, response);
            break;
		case "findByids":
			String knames= request.getParameter("kname");
			String keclass=request.getParameter("keclass");
			String zhuanyes=request.getParameter("zhuanye");
			String res=d.find(knames, keclass, zhuanyes);
			request.setAttribute("test", res);
	   		request.getRequestDispatcher("xts.jsp").forward(request, response);
			break;
		case "up":
			String cid1= request.getParameter("num");
			String kname1=request.getParameter("kname");
			String teachername1=request.getParameter("teachername");
			String kenum1=request.getParameter("kenum");
			String xingzhi1=request.getParameter("xingzhi");
			String xuefen1=request.getParameter("xuefen");
			String ban1=request.getParameter("ban");
			String zhuanye1=request.getParameter("zhuanye");
			String fangshi1=request.getParameter("fangshi");
			String time1=request.getParameter("time");
			String ren1=request.getParameter("ren");
			String mingti1=request.getParameter("mingti");
			String zucheng1=request.getParameter("zucheng");
			String kf1=request.getParameter("kf");
			String fenxi1=request.getParameter("fenxi");
			d.Up(cid1,kname1,teachername1,kenum1,xingzhi1,xuefen1,ban1,zhuanye1,fangshi1,time1,ren1,mingti1,zucheng1,kf1,fenxi1);
			request.setAttribute("test", "修改成功");
	   		request.getRequestDispatcher("xts.jsp").forward(request, response);
	   		break;
		}
		}
	@Override
    public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{
    	doPost(request,response);
}
}

　　zhuanjiao.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>
<title>Insert title here</title>
</head>
<body>
<center>
<p><a href="addk.jsp">新增试卷审批卡</a>
	</p>
<p><a href="ka?method=findall">修改或删除试卷审批卡</a>
	</p>
	<p><a href="ka?method=findalls">浏览试卷审批卡信息</a>
	</p>
	<p><a href="find.jsp">查询考试信息</a>
	</p>
	</center>
</body>
</html>

　　addk.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>
<title>Insert title here</title>
</head>
<body>
 <form action="ka" method="post">
<center>
<p>试卷编号
              <input type="text" name="num"/>
              </p>
<p>课程名称
              <input type="text" name="kname"/>
              </p>
              <p>任课教师
              <input type="text" name="teachername"/>
              </p>
              <p>课程代码
              <input type="text" name="kenum"/>
              </p>
              <p>课程性质：
            	<input type="radio" name="xingzhi" value="必修" checked/>必修
				<input type="radio" name="xingzhi" value="选修"/>必修
              </p>
              <p>学分
              <input type="text" name="xuefen"/>
              </p>
              <p>授课班级
              <input type="text" name="ban"/>
              </p>
              <p>授课专业
              <input type="text" name="zhuanye"/>
              </p>
              <p>考试方式：
            	<input type="radio" name="fangshi" value="闭卷" checked/>闭卷
				<input type="radio" name="fangshi" value="开卷"/>开卷
				<input type="radio" name="fangshi" value="其他"/>其他
              </p>
              <p>考试时间
              <input type="text" name="time"/>
              </p>
              <p>考试人数
              <input type="text" name="ren"/>
              </p>
              <p>命题方式
              <input type="text" name="mingti"/>
              </p>
              <p>成绩组成
              <input type="text" name="zucheng"/>
             
              </p>
              <p>考核与评价方式
              <input type="text" name="kf"/>
              </p>
              <p>考核内容合理性分析
              <input type="text" name="fenxi"/>
              </p>
              <input type="hidden" name="method" value="add"/>
              <p>
        <input type="submit" value="提交">
        </p>
</center>
</form>
</body>
</html>

　　xts.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>
<title>Insert title here</title>
</head>
<body>
<center>
<%=request.getAttribute("test")%>

        </p>
        </center>
        </form>
</body>
</html>

　　mmesss.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>
<title>Insert title here</title>
</head>
<body>
<center>
<c:forEach items="${test}" var="list">
<tr>
<td>${list.getCardId()}</td>
<td>${list.getCourseName()}</td>
<td>${list.getCourseTeacher()}</td>
<td>${list.getCourseID()}</td>
<td>${list.getCourseNature()}</td>
<td>${list.getCredit()}</td>
<td>${list.getCourseClass()}</td>
<td>${list.getCourseMajor()}</td>
<td>${list.getTestWay()}</td>
<td>${list.getTestData()}</td>
<td>${list.getTestCount()}</td>
<td>${list.getTestMethod()}</td>
<td>${list.getTestGrade()}</td>
<td>${list.getTestEvaluation()}</td>
<td>${list.getTestAnalysis()}</td>
<td>
<td>
          <a href="ka?method=delete&num=${list.getCardId()}">删除</a>
        <a href="ka?method=findByid&num=${list.getCardId()}">修改</a>
        </td>
        <br></br>
</tr>
</c:forEach>
</center>
</body>
</html>

　　ll.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>
<title>Insert title here</title>
</head>
<body>
<center>
<c:forEach items="${test}" var="list">
<tr>
<td>${list.getCardId()}</td>
<td>${list.getCourseName()}</td>
<td>${list.getCourseTeacher()}</td>
<td>${list.getCourseID()}</td>
<td>${list.getCourseNature()}</td>
<td>${list.getCredit()}</td>
<td>${list.getCourseClass()}</td>
<td>${list.getCourseMajor()}</td>
<td>${list.getTestWay()}</td>
<td>${list.getTestData()}</td>
<td>${list.getTestCount()}</td>
<td>${list.getTestMethod()}</td>
<td>${list.getTestGrade()}</td>
<td>${list.getTestEvaluation()}</td>
<td>${list.getTestAnalysis()}</td>
<td>
<td>
        </td>
        <br></br>
</tr>
</c:forEach>
</center>
</body>
</html>

　　updatec.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>
    <title>Title</title>
</head>
<body>
<center>
<form action="ka" method="post">
    试卷编号: <input type="text" name="num" value="${test.getCardId()}" readonly/><br/>
    课程名称：<input type="text" name="kname" value="${test.getCourseName()}"/><br/>
    任课教师：<input type="text" name="teachername" value="${test.getCourseTeacher()}"/><br/>
    课程代码：<input type="text" name="kenum" value="${test.getCourseID()}"/><br/>
    课程性质：<input type="text" name="xingzhi" value="${test.getCourseNature()}"/><br/>
    学分：<input type="text" name="xuefen" value="${test.getCredit()}"/><br/>
    授课班级：<input type="text" name="ban" value="${test.getCourseClass()}"/><br/>
    授课专业：<input type="text" name="zhuanye" value="${test.getCourseMajor()}"/><br/>
    考试方式：<input type="text" name="fangshi" value="${test.getTestWay()}"/><br/>
    考试时间：<input type="text" name="time" value="${test.getTestData()}"/><br/>
    考试人数：<input type="text" name="ren" value="${test.getTestCount()}"/><br/>
    命题方式：<input type="text" name="mingti" value="${test.getTestMethod()}"/><br/>
    成绩组成：<input type="text" name="zucheng" value="${test.getTestGrade()}"/><br/>
    考核与评价方式：<input type="text" name="kf" value="${test.getTestEvaluation()}"/><br/>
    考核内容合理性分析：<input type="text" name="fenxi" value="${test.getTestAnalysis()}"/><br/>
    <input type="hidden" name="method" value="up"/>
    <input type="submit" value="修改"/>
</form>
</center>
</body>
</html>

　　最后，查询的综合查询不够完善，浏览信息未达到要求。