新建学生信息数据库
1.添加记录模块
<%@ page contentType="text/html" pageEncoding="UTF-8"%> <html> <head> <title>添加新学生</title> </head> <body> <form action="stuinsert2.jsp" method="post"> <table heigth="252"> <tr><td>学号：</td><td><input type="text"></td></tr> <tr><td>姓名</td><td><input type="text"></td></tr> <tr><td>性别</td><td><input type="text"></td></tr> <tr><td>年龄</td><td><input type="text"></td></tr> <tr><td>专业</td><td><input type="text"></td></tr> <tr> <td colspan="2"> <input type="submit" value="提交"> <input type="reset" value="取消"> </td> </tr> </table> </form> </body> </html>
<%@ page language="java" import="java.sql.*" pageEncoding="UTF-8"%> <html> <head> <title>添加学生处理程序</title> </head> <body> <% Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); String url="jdbc:sqlserver://localhost:1433; DatabaseName=p"; String user="sa"; String password="123456"; Connection conn=DriverManager.getConnection(url,user,password); request.setCharacterEncoding("UTF-8"); String id=request.getParameter("id"); String name=request.getParameter("name"); String sex=request.getParameter("sex"); int age=Integer.parseInt(request.getParameter("age")); String sdept=request.getParameter("sdept"); String sql=String.format("select * from student where Sno=%s",id); Statement stmt=conn.createStatement(); PreparedStatement pstmt; ResultSet rs=stmt.executeQuery(sql); if(!(rs.next())){ pstmt=conn.prepareStatement("insert into Student values(?,?,?,?,?)"); pstmt.setString(1,id); pstmt.setString(2,name); pstmt.setString(3,sex); pstmt.setInt(4,age); pstmt.setString(5,sdept); pstmt.executeUpdate(); out.print("添加成功"); %> <a href="https://www.jb51.net/stuinsert.jsp" >返回菜单</a><br> <a href="https://www.jb51.net/main.jsp" ></a> <%}else{ out.println("该学号已存在");%> <a href="https://www.jb51.net/stuinsert.jsp" >重新添加</a> <%}%> </body> </html>
2.查询(all)模块
<%@ page language="java" import="java.sql.*" pageEncoding="UTF-8"%> <%@ page import="javax.sql.*" %> <%@ page import="javax.naming.*" %> <html> <head> <title>信息查看页面</title> </head> <body> <center> <% DataSource ds=null; InitialContext ctx=new InitialContext(); ds=(DataSource)ctx.lookup("java:comp/env/jdbc/sqlserver"); Connection conn=ds.getConnection(); PreparedStatement pstmt=conn.prepareStatement("select * from Student"); ResultSet rs=pstmt.executeQuery(); %> <table bgcolor="ccceee"> <tr bgcolor="CCCCCC"> <td>记录条数</td><td>学号</td><td>姓名</td> <td>性别</td><td>年龄</td><td>专业</td> </tr> <% while(rs.next()){ %> <tr> <td><%=rs.getRow()%></td> <td><%=rs.getString(1)%></td> <td><%=rs.getString(2)%></td> <td><%=rs.getString(3)%></td> <td><%=rs.getString(4)%></td> <td><%=rs.getString(5)%></td> </tr> <%}%> </center> <%if(rs!=null){rs.close();} if(pstmt!=null){pstmt.close();} if(conn!=null){conn.close();} %> </table> </body> </html>
条件查询模块
<%@ page language="java" pageEncoding="UTF-8"%> <html> <head> <title>My JSP 'stufind2.jsp' starting page</title> </head> <body> 请选择查询条件<hr size="3"> <form action="stufind3.jsp" method="post"> 性别<input type="radio" value="男" checked="checked">男 <input type="radio" value="女">女 年龄范围：<p> 最小：<input type="text"><br><br> 最大：<input type="text"><p> <input type="submit" value="提交"> <input type="reset" value="取消"> </form> </body> </html>
<%@ page contentType="text/html" import="java.sql.*" pageEncoding="UTF-8"%> <html> <head> <title>条件查询处理页面</title> </head> <body> <center> <%Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); String user="sa"; String password="123456"; String url="jdbc:sqlserver://localhost:1433;DatabaseName=p"; Connection conn=DriverManager.getConnection(url,user,password); request.setCharacterEncoding("UTF-8"); String sex=request.getParameter("sex"); int age1=Integer.parseInt(request.getParameter("a1")); int age2=Integer.parseInt(request.getParameter("a2")); String sql="select * from Student where Ssex=? and Sage>=? and Sage<=?"; PreparedStatement pstmt=conn.prepareStatement(sql); pstmt.setString(1,sex); pstmt.setInt(2,age1); pstmt.setInt(3,age2); ResultSet rs=pstmt.executeQuery(); %> <table bgcolor="ccceee"> <tr bgcolcor="CCCCCC"> <td>记录条数</td><td>学号</td><td>姓名</td> <td>性别</td><td>年龄</td><td>专业</td> </tr> <%while(rs.next()){ %> <tr> <td><%=rs.getRow()%></td> <td><%=rs.getString(1)%></td> <td><%=rs.getString(2)%></td> <td><%=rs.getString(3)%></td> <td><%=rs.getString(4)%></td> <td><%=rs.getString(5)%></td> </tr> <%}%> </table> <%if(rs!=null){rs.close();} if(pstmt!=null){pstmt.close();} if(conn!=null){conn.close();} %> </center> </body> </html>
3.修改模块