学生信息增删改查
学生信息增删改查几天改改写写差不多了,报错很多,改来改去也是很麻烦,这一个错哪一个错。
添加操作还是找人问的,自己看了半天改不对。
navicat:
eclipse:
1.Bean层:封装
1 package Bean; 2 3 public class bean { 4 private String xuehao; 5 private String xingming; 6 private String banji; 7 private String kecheng; 8 private String fenshu; 9 public String getXuehao() { 10 return xuehao; 11 } 12 public void setXuehao(String xuehao) { 13 this.xuehao = xuehao; 14 } 15 public String getXingming() { 16 return xingming; 17 } 18 public void setXingming(String xingming) { 19 this.xingming = xingming; 20 } 21 public String getBanji() { 22 return banji; 23 } 24 public void setBanji(String banji) { 25 this.banji = banji; 26 } 27 public String getKecheng() { 28 return kecheng; 29 } 30 public void setKecheng(String kecheng) { 31 this.kecheng = kecheng; 32 } 33 public String getFenshu() { 34 return fenshu; 35 } 36 public void setFenshu(String fenshu) { 37 this.fenshu = fenshu; 38 } 39 40 }
2.Dao层:增删改,判重之类方法
1 package dao; 2 import util.DBUtil; 3 4 import java.sql.Connection; 5 import java.sql.PreparedStatement; 6 import java.sql.SQLException; 7 import java.sql.*; 8 import java.util.*; 9 10 11 12 public class UserDao { 13 //增加 14 public void tianjia(String xuehao,String xingming,String banji,String kecheng,String fenshu) throws Exception 15 { 16 Connection connection = DBUtil.getConnection(); 17 PreparedStatement preparedStatement=null; 18 try { 19 String sql = "insert into xuesheng () values (?,?,?,?,?)"; 20 preparedStatement=connection.prepareStatement(sql); 21 preparedStatement.setString(1,xuehao); 22 preparedStatement.setString(2,xingming); 23 preparedStatement.setString(3,banji); 24 preparedStatement.setString(4,kecheng); 25 preparedStatement.setString(5,fenshu); 26 27 28 preparedStatement.executeUpdate(); 29 //System.out.println("添加成功"); 30 31 } catch (SQLException e) { 32 e.printStackTrace(); 33 }finally{ 34 35 } 36 37 38 } 39 //删除 40 public void shanchu(String xuehao) throws Exception 41 { 42 Connection connection = DBUtil.getConnection(); 43 PreparedStatement preparedStatement=null; 44 try { 45 String sql = "delete from xuesheng where xuehao = ?"; 46 preparedStatement=connection.prepareStatement(sql); 47 preparedStatement.setString(1,xuehao); 48 preparedStatement.executeUpdate(); 49 //System.out.println("删除成功"); 50 51 } catch (SQLException e) { 52 e.printStackTrace(); 53 }finally{ 54 55 } 56 } 57 //修改 58 public void xiugai(String xuehao, String xingming, String banji, String kecheng , String fenshu) throws Exception 59 { 60 Connection connection = DBUtil.getConnection(); 61 PreparedStatement preparedStatement=null; 62 try { 63 //身份证号码、性别、民族、受教育程度 64 String sql = "update xuesheng set xingming=?, banji=?, kecheng=?, fenshu=? where xuehao=?"; 65 preparedStatement=connection.prepareStatement(sql); 66 preparedStatement.setString(1,xingming); 67 preparedStatement.setString(2,banji); 68 preparedStatement.setString(3,kecheng); 69 preparedStatement.setString(4,fenshu); 70 preparedStatement.setString(5,xuehao); 71 preparedStatement.executeUpdate(); 72 73 } catch (SQLException e) { 74 e.printStackTrace(); 75 }finally{ 76 77 } 78 } 79 80 81 82 //判重盘存在 83 public boolean isSame(String xuehao) throws Exception 84 { 85 Connection connection = DBUtil.getConnection(); 86 PreparedStatement preparedStatement=null; 87 ResultSet rs=null; 88 try { 89 String sql = "select * from xuesheng"; 90 preparedStatement=connection.prepareStatement(sql); 91 rs=preparedStatement.executeQuery(); 92 while(rs.next()){ 93 if( xuehao.equals(rs.getObject(1))||xuehao.equals(rs.getObject(2)) ) 94 return true; 95 } 96 //preparedStatement.executeUpdate(); 97 98 } catch (SQLException e) { 99 e.printStackTrace(); 100 }finally{ 101 102 } 103 return false; 104 } 105 106 }
3.Util:连接数据库
package util; import java.sql.Connection; import java.sql.DriverManager; public class DBUtil { public static Connection getConnection() throws Exception { Class.forName("com.mysql.cj.jdbc.Driver"); Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/baidu_db", "root", "258996"); return con; } }
4.zhujiemian.jsp:跳转其他功能页
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>主页</title> </head> <body> <p style="text-align:center;color: black; font-family: 宋体; font-size: 20px"> <br> <input type="button" value="添加学生信息" onclick="location.href='add.jsp'" /> <br> <br> <input type="button" value="删除学生信息" onclick="location.href='delete.jsp'" /> <br> <br> <input type="button" value="修改学生信息" onclick="location.href='xiugai.jsp'" /> <br> <br> <input type="button" value="查询学生信息" onclick="location.href='chaxun.jsp'" /> <br> </body> </html>
5.add.jsp:添加界面
<%@ 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="add1.jsp" method="get"> <p style="text-align:center;color: black; font-family: 宋体; font-size: 20px"> 学号:<input type="text" name="xuehao"><br> 姓名:<input type="text" name="xingming"><br> 课程:<input type="text" name="banji"> <br> 班级:<input type="text" name="kecheng"><br> 分数:<input type="text" name="fenshu"> <br> <input type="submit" value="提交" /> <input type="reset" value="重置" /> <br> <input type="button" value="返回菜单" onclick="location.href='zhujiemian.jsp'" /> <br> </body> </html>
6.add1.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> <jsp:useBean id="dao" class="dao.UserDao" scope="page" /> <% String day=(String)request.getParameter("xuehao"); String guanjianzi=(String)request.getParameter("xingming"); String sum=(String)request.getParameter("banji"); String days=(String)request.getParameter("kecheng"); String zuichang=(String)request.getParameter("fenshu"); dao.tianjia(day,guanjianzi,sum,days,zuichang); out.print("<script language='javaScript'> alert('添加成功');</script>"); response.setHeader("refresh", "0;url=xianshi.jsp");%> </body> </html>
7.add2.jsp:实际写的是添加完后跳转xianshi.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ page import="util.DBUtil"%> <%@ page import="java.sql.*"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>Insert title here</title> </head> <body> <jsp:useBean id="dao" class="dao.UserDao" scope="page" /> <table border="1"style="text-align:center;"> <tr> <td align="center" width=6%>学号</td> <td align="center" width=8%>姓名</td> <td align="center" width=10%>课程</td> <td align="center" width=10%>班级</td> <td align="center" width=6%>分数</td> <% Connection connection = DBUtil.getConnection(); PreparedStatement preparedStatement=null; ResultSet rs=null; try { String sql = "select * from xuesheng"; preparedStatement=connection.prepareStatement(sql); rs=preparedStatement.executeQuery(); while(rs.next()){ %> <tr> <td align="center"><%=rs.getObject(1) %></td> <td align="center"><%=rs.getObject(2) %></td> <td align="center"><%=rs.getObject(3) %></td> <td align="center"><%=rs.getObject(4) %></td> <td align="center"><%=rs.getObject(5) %></td> </tr> <% } } catch (SQLException e) { e.printStackTrace(); }finally{ //util.close(rs); //util.close(preparedStatement); //util.close(connection); } %> </tr> </body> </html>
8.delete.jsp
<%@ 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="delete1.jsp" method="get"> <p style="text-align:center;color: black; font-family: 宋体; font-size: 20px"> 信息删除页面 <br> 要删除信息的学生学号: <input type="text" name="xuehao" /> <br> <br><input type="submit" value="确定" /> <input type="reset" value="重置" /> <br> <br> <input type="button" value="返回菜单" onclick="location.href='zhujiemian.jsp'" /> <br> </p> </form> </body> </html>
9.delete1.jsp
<%@ page language="java" import="java.sql.*" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ page import="util.DBUtil"%> <%@ page import="java.sql.*"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> </head> <body> <jsp:useBean id="dao" class="dao.UserDao" scope="page" /> <% String xuehao=(String)request.getParameter("xuehao"); if(xuehao==""){ out.print("<script language='javaScript'> alert('输入为空'); window.history.back(-1); </script>"); } else if(!dao.isSame(xuehao)) { out.print("<script language='javaScript'> alert('该学号学生不存在'); window.history.back(-1); </script>"); } else{ %> <table border="1"style="text-align:center;"> <tr> <td align="center" width=6%>学号</td> <td align="center" width=8%>姓名</td> <td align="center" width=10%>班级</td> <td align="center" width=10%>课程</td> <td align="center" width=6%>分数</td> <td align="center" width=3%>删除</td> </tr> <% Connection connection = DBUtil.getConnection(); PreparedStatement preparedStatement=null; ResultSet rs=null; try { String sql = "select * from xuesheng"; preparedStatement=connection.prepareStatement(sql); rs=preparedStatement.executeQuery(); while(rs.next()){ if(xuehao.equals(rs.getObject(1))) { %> <tr> <td align="center"><%=rs.getObject(1) %></td> <td align="center"><%=rs.getObject(2) %></td> <td align="center"><%=rs.getObject(3) %></td> <td align="center"><%=rs.getObject(4) %></td> <td align="center"><%=rs.getObject(5) %></td> <td align="center"><a style="color:blue" href='delete2.jsp?xuehao=<%=rs.getObject(1) %>' onclick="javascript:return del()">删除</a> </td> </tr> <% } } } catch (SQLException e) { e.printStackTrace(); }finally{ //util.close(rs); //util.close(preparedStatement); //util.close(connection); } %> </table> <% } %> <script> function del(){ var r = confirm("确定要删除吗?") if (r == true) { return true; } else { return false; } } </script> <p style="text-align:center;color: black; font-family: 宋体; font-size: 20px"> <br> <input type="button" value="返回菜单" onclick="location.href='menu.jsp'" /> <br> </p> </body> </html>
10.delete2.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> </head> <body> <input type="button" onclick="openResult()" value="删除"/> <jsp:useBean id="dao" class="dao.UserDao" scope="page" /> <% String xuehao=(String)request.getParameter("xuehao"); dao.shanchu(xuehao); out.print("<script language='javaScript'> alert('删除成功');</script>"); response.setHeader("refresh", "0;url=delete.jsp"); %> <p style="text-align:center;color: black; font-family: 宋体; font-size: 20px"> <br> <input type="button" value="返回菜单" onclick="location.href='zhujiemian.jsp'" /> <br> </p> </body> </html>
11.xiugai.jsp
<%@page import="dao.UserDao"%> <%@ 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="xiugai1.jsp" method="get"> <p style="text-align:center;color: black; font-family: 宋体; font-size: 20px"> <br> 输入要修改的学生学号 <input type="text" name="xuehao" /><br> <br><input type="submit" value="提交" /> <input type="reset" value="重置" /> <br> <br> <input type="button" value="返回菜单" onclick="location.href='zhujiemian.jsp'" /> <br> </p> </form> </body> </html>
12.xiugai1.jsp
<%@ page language="java" import="java.sql.*" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ page import="util.DBUtil"%> <%@ page import="java.sql.*"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>修改</title> </head> <body> <jsp:useBean id="dao" class="dao.UserDao" scope="page" /> <% String xuehao=(String)request.getParameter("xuehao"); if(xuehao==""){ out.print("<script language='javaScript'> alert('输入为空'); window.history.back(-1); </script>"); } else if(!dao.isSame(xuehao)) { out.print("<script language='javaScript'> alert('该学号学生不存在'); window.history.back(-1); </script>"); } else{ %> <table border="1"style="text-align:center;"> <tr> <td align="center" width=6%>学号</td> <td align="center" width=8%>姓名</td> <td align="center" width=10%>班级</td> <td align="center" width=10%>课程</td> <td align="center" width=6%>分数</td> <td align="center" width=3%>修改</td> </tr> <% Connection connection = DBUtil.getConnection(); PreparedStatement preparedStatement=null; ResultSet rs=null; try { String sql = "select * from xuesheng"; preparedStatement=connection.prepareStatement(sql); rs=preparedStatement.executeQuery(); while(rs.next()){ if(xuehao.equals(rs.getObject(1))) { %> <tr> <td align="center"><%=rs.getObject(1) %></td> <td align="center"><%=rs.getObject(2) %></td> <td align="center"><%=rs.getObject(3) %></td> <td align="center"><%=rs.getObject(4) %></td> <td align="center"><%=rs.getObject(5) %></td> <td align="center"><a style="color:blue" href='xiugai2.jsp?xuehao=<%=rs.getObject(1) %>'>修改</a> </td> </tr> <% } } } catch (SQLException e) { e.printStackTrace(); }finally{ //util.close(rs); //util.close(preparedStatement); //util.close(connection); } %> </table> <% } %> <p style="text-align:center;color: black; font-family: 宋体; font-size: 20px"> <br> <input type="button" value="返回菜单" onclick="location.href='zhujiemian.jsp'" /> <br> </p> </body> </html>
13.xiugai2.jsp
<%@ page language="java" import="java.sql.*" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ page import="util.DBUtil"%> <%@ page import="java.sql.*"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> </head> <body> <jsp:useBean id="dao" class="dao.UserDao" scope="page" /> <% String xuehao=(String)request.getParameter("xuehao"); session.setAttribute("xuehao",xuehao); %> <table border="1"style="text-align:center;"> <tr> <td align="center" width=6%>学号</td> <td align="center" width=8%>姓名</td> <td align="center" width=10%>班级</td> <td align="center" width=10%>课程</td> <td align="center" width=6%>分数</td> </tr> <% Connection connection = DBUtil.getConnection(); PreparedStatement preparedStatement=null; ResultSet rs=null; try { String sql = "select * from xuesheng"; preparedStatement=connection.prepareStatement(sql); rs=preparedStatement.executeQuery(); while(rs.next()){ if(xuehao.equals(rs.getObject(1))) { %> <tr> <td align="center"><%=rs.getObject(1) %></td> <td align="center"><%=rs.getObject(2) %></td> <td align="center"><%=rs.getObject(3) %></td> <td align="center"><%=rs.getObject(4) %></td> <td align="center"><%=rs.getObject(5) %></td> </tr> <% } } } catch (SQLException e) { e.printStackTrace(); }finally{ //util.close(rs); //util.close(preparedStatement); //util.close(connection); } %> </table> <form action="xiugai3.jsp" method="get"> <p style="text-align:center;color: black; font-family: 宋体; font-size: 20px"> <br> <br> 修改信息 <br> 姓名 <input type="text" name="xingming" /><br> <br> 班级 <input type="text" name="banji" /><br> <br> 课程 <input type="text" name="kecheng" /><br> <br> 分数 <input type="text" name="fenshu" /><br> <br><input type="submit" value="提交" /> <input type="reset" value="重置" /> <br> <br> <input type="button" value="返回菜单" onclick="location.href='zhujiemian.jsp'" /> <br> </p> </form> </body> </html>
14.xiugai3.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ page import="util.DBUtil"%> <%@ page import="java.sql.*"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> </head> <body> <jsp:useBean id="dao" class="dao.UserDao" scope="page" /> <% String xuehao=(String)session.getAttribute("xuehao"); String xingming=(String)request.getParameter("xingming"); String banji=(String)request.getParameter("banji"); String kecheng=(String)request.getParameter("kecheng"); String fenshu=(String)request.getParameter("fenshu"); if(xingming==""||banji==""||kecheng==""||fenshu=="") { out.print("<script language='javaScript'> alert('输入为空');window.history.back(-1);</script>"); } else if(!dao.isSame(xuehao)) { out.print("<script language='javaScript'> alert('学号不正确');window.history.back(-1);</script>"); } else { dao.xiugai(xuehao,xingming, banji, kecheng, fenshu); out.print("<script language='javaScript'> alert('修改成功');</script>"); response.setHeader("refresh", "0;url=xiugai.jsp"); } %> <p style="text-align:center;color: black; font-family: 宋体; font-size: 20px"> <br> <input type="button" value="返回菜单" onclick="location.href='zhujiemian.jsp'" /> <br> </p> </body> </html>
15.chaxun.jsp
<%@ 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="chaxun1.jsp" method="get"> <p style="text-align:center;color: black; font-family: 宋体; font-size: 20px"> 输入要查询信息的学号/姓名/班级/课程(支持模糊查询): <br> <input type="text" name="data" /> <br> <br><input type="submit" value="提交" /> <input type="reset" value="重置" /> <br> <br> <input type="button" value="返回菜单" onclick="location.href='zhujiemian.jsp'" /> <br> </p> </form> </body> </html>
16.chaxun1.jsp
<%@ page language="java" import="java.sql.*" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ page import="util.DBUtil"%> <%@ page import="java.sql.*"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>查询</title> </head> <body style="text-align:center"> 查询结果 <p> <jsp:useBean id="dao" class="dao.UserDao" scope="page" /> <% String data=(String)request.getParameter("data"); if(data==""){ out.print("<script language='javaScript'> alert('输入为空'); window.history.back(-1); </script>"); } else { %> <table border="1"style="text-align:center;"> <tr> <td align="center" width=6%>学号</td> <td align="center" width=3%>姓名</td> <td align="center" width=10>班级</td> <td align="center" width=10%>课程</td> <td align="center" width=10%>分数</td> </tr> <% int i=0; Connection connection = DBUtil.getConnection(); PreparedStatement preparedStatement=null; ResultSet rs=null; try { String sql= " select * from xuesheng where xuehao like ? or xingming like ? or banji like ? or kecheng like ? "; preparedStatement=connection.prepareStatement(sql); preparedStatement.setString(1,"%"+data+"%"); preparedStatement.setString(2,"%"+data+"%"); preparedStatement.setString(3,"%"+data+"%"); preparedStatement.setString(4,"%"+data+"%"); rs=preparedStatement.executeQuery(); while(rs.next()) { i++; %> <tr> <td align="center"><a style="color:blue" href='chaxunshow.jsp?fenshu=<%=rs.getObject(5) %>'><%=rs.getObject(1) %></a> </td> <td align="center"><%=rs.getObject(2) %></td> <td align="center"><%=rs.getObject(3) %></td> <td align="center"><%=rs.getObject(4) %></td> <td align="center"><%=rs.getObject(5) %></td> </tr> <% } if(i==0) { out.print("<script language='javaScript'> alert('没有查询到有关信息'); window.history.back(-1); </script>"); } } catch (SQLException e) { e.printStackTrace(); }finally{ // util.close(rs); //util.close(preparedStatement); //util.close(connection); } } %> </table> <p style="text-align:center;color: black; font-family: 宋体; font-size: 20px"> <br> <input type="button" value="返回菜单" onclick="location.href='zhujiemian.jsp'" /> <br> </p> </body> </html>
17.chaxunshow.jsp
<%@ page language="java" import="java.sql.*" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ page import="util.DBUtil"%> <%@ page import="java.sql.*"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>详细信息</title> </head> <body> <jsp:useBean id="dao" class="dao.UserDao" scope="page" /> <% String xuehao = (String)request.getParameter("xuehao"); Connection connection = DBUtil.getConnection(); PreparedStatement preparedStatement=null; ResultSet rs=null; try { String sql = "select * from xuesheng"; preparedStatement=connection.prepareStatement(sql); rs=preparedStatement.executeQuery(); while(rs.next()){ if(xuehao.equals(rs.getObject(1))) { %> <table border="1"style="text-align:center;"> <tr> <td align="center" width=6%>学号</td> <td align="center" width=8%>姓名</td> <td align="center" width=10%>班级</td> <td align="center" width=10%>课程</td> <td align="center" width=6%>分数</td> </tr> <tr> <td align="center"><%=rs.getObject(1) %></td> <td align="center"><%=rs.getObject(2) %></td> <td align="center"><%=rs.getObject(3) %></td> <td align="center"><%=rs.getObject(4) %></td> <td align="center"><%=rs.getObject(5) %></td> </tr> <% } } } catch (SQLException e) { e.printStackTrace(); }finally{ //util.close(rs); //util.close(preparedStatement); //util.close(connection); } %> </table> <p style="text-align:center;color: black; font-family: 宋体; font-size: 20px"> <input type="button" name="back" onclick="javascript:window.history.back(-1);" value=返回上一页> <input type="button" value="返回菜单" onclick="location.href='zhujiemian.jsp'" /> <br> </p> </body> </html>
标签:preparedStatement,String,博客,jsp,sql,xuehao,public,2.24 From: https://www.cnblogs.com/qq2143187807/p/17153383.html