jsp实验2.mysql数据库操作

来源：互联网发布：django ajax json 编辑：程序博客网时间：2024/05/16 03:35

这次实验主要是对Mysql数据库进行增，删，改，查的操作：

1、编制普通浏览页面(browseStu2.jsp)，可以链接到其它页面，新增链接到addStu.jsp，修改链接到updateStu.jsp，删除链接到deleteStu.jsp，修改和删除采用url传递记录的id。

完整代码如下：

<%@ page language="java" import="java.util.*,java.sql.*"          contentType="text/html; charset=utf-8"%><%request.setCharacterEncoding("utf-8");String msg ="";String connectString = "jdbc:mysql://localhost:3306/teaching17"+ "?autoReconnect=true&useUnicode=true"+ "&characterEncoding=UTF-8";     StringBuilder table=new StringBuilder("");    table.append("<table>");    table.append("<tr> <th>id</th><th>学号</th><th>姓名</th><th>操作</th> </tr>");        try{  Class.forName("com.mysql.jdbc.Driver");  Connection con=DriverManager.getConnection(connectString,                  "root", "0103");  Statement stmt=con.createStatement();  ResultSet rs=stmt.executeQuery("select * from stu");  while(rs.next()) { table.append("<tr>");             table.append("<td>"+rs.getString("id")+"</td>");             table.append("<td>"+rs.getString("num")+"</td>");             table.append("<td>"+rs.getString("name")+"</td>");             table.append("<td>"+             "<a href='updateStu.jsp?pid="+rs.getString("id")+"'>修改</a>"+""+             "<a href='deleteStu.jsp?pid="+rs.getString("id")+"'>删除</a>"+              "</td>");             table.append("</tr>");  }  table.append("</table>");  rs.close();  stmt.close();  con.close();}catch (Exception e){  msg = e.getMessage();}%><!DOCTYPE HTML><html><style>td,th{width : 10rem;height : 2rem;border:solid 1px black}a:link,a:visited {color:blue;}.container{margin:0 auto;width:500px;text-align:center;}table {border-collapse:collapse}</style><head><title>sql</title></head><body><div class="container"><h1>浏览学生名单</h1>  <%=table%><br><br>  <div style= "text-align : center "><a href="addStu.jsp" >新增</a></div><br><br></div></body></html>

运行结果如下：

2、编制数据输入功能，返回链接到浏览页面。即addStu.jsp:

<%@ page language="java" import="java.util.*,java.sql.*" contentType="text/html; charset=utf-8"%><% request.setCharacterEncoding("utf-8");String msg = "";String connectString = "jdbc:mysql://loaclhost:3306/teaching17"+ "?autoReconnect=true&useUnicode=true&characterEncoding=UTF-8";String user="root"; String pwd="0103";String num = request.getParameter("num");String name = request.getParameter("name");if(request.getMethod().equalsIgnoreCase("post")){try{Class.forName("com.mysql.jdbc.Driver");Connection con = DriverManager.getConnection(connectString,user, pwd);Statement stmt = con.createStatement();String fmt="insert into stu(num,name) values('%s','%s')";String sql = String.format(fmt,num,name);int cnt = stmt.executeUpdate(sql);if(cnt>0) msg = "保存成功!";stmt.close();con.close();}catch (Exception e){msg = e.getMessage();}}%><!DOCTYPE HTML><html><head><title>新增学生记录</title><style>body{font-family:微软雅黑,宋体;}a:link,a:visited { color:blue; }.container{margin:0 auto;width:500px;text-align:center;}form { line-height:50px; }a{margin-top : 300px;}</style></head><body><div class="container"><h1>新增学生记录</h1><form action="addStu.jsp" method="post" name="f">学号:<input id="num" name="num" type="text" ><br>姓名:<input id="name" type="text" name="name" ><br><input type="submit" name="sub" value="保存"></form><%=msg%><br><a href='browseStu.jsp'>返回</a></div></body></html>

3、编制数据修改功能，返回链接到浏览页面,即updateStu.jsp

<%@ page language="java" import="java.util.*,java.sql.*" contentType="text/html; charset=utf-8"%><% request.setCharacterEncoding("utf-8");String msg = "";String connectString = "jdbc:mysql://172.18.187.230:3306/teaching17"+ "?autoReconnect=true&useUnicode=true&characterEncoding=UTF-8";String user="root"; String pwd="0103";String updateButton = request.getParameter("update"); String clearButton = request.getParameter("clear"); String param = request.getParameter("pid");String pid = "";if(param != null && !param.isEmpty()){pid += param;}String num = request.getParameter("num");if(num==null) num="";String name = request.getParameter("name");if(name==null) name="";String sql = "";try{Class.forName("com.mysql.jdbc.Driver");Connection con = DriverManager.getConnection(connectString,user, pwd);Statement stmt = con.createStatement();if(updateButton != null){String fmt="update stu set num='%s',name='%s' where id=%d"; sql = String.format(fmt,num,name,Integer.parseInt(pid)); int cnt = stmt.executeUpdate(sql); if(cnt>0) msg = "修改成功!";}else{ String fmt="select * from stu where id=%d"; sql = String.format(fmt,Integer.parseInt(pid)); ResultSet rs = stmt.executeQuery(sql); if(rs.next()){         num = rs.getString("num");         name = rs.getString("name");     }}stmt.close();con.close();}catch (Exception e){msg = e.getMessage();}%><!DOCTYPE HTML><html><head><title>新增学生记录</title><style>body{font-family:微软雅黑,宋体;}a:link,a:visited { color:blue; }.container{margin:0 auto;width:500px;text-align:center;}form { line-height:50px; }a{margin-top : 300px;}</style></head><body><div class="container"><h1>修改学生记录</h1><form action="updateStu.jsp?pid=<%=pid%>" method="post">学号:<input id="num" name="num" type="text" value=<%=num %> ><br>姓名:<input id="name" type="text" name="name" value=<%=name %>><br><input type="submit" name="update" value="修改"><input type="submit" name="clear" value="清空"></form><%=msg%><br><a href='browseStu.jsp'>返回</a></div></body></html>

4、编制数据删除功能，返回链接到浏览页面

<%@ page language="java" import="java.util.*,java.sql.*" contentType="text/html; charset=utf-8"%><% request.setCharacterEncoding("utf-8");String msg = "";String connectString = "jdbc:mysql://localhost:3306/teaching17"+ "?autoReconnect=true&useUnicode=true&characterEncoding=UTF-8";String user="root"; String pwd="0103";String param = request.getParameter("pid");String pid = "";if(param != null && !param.isEmpty()){pid += param;}try{Class.forName("com.mysql.jdbc.Driver");Connection con = DriverManager.getConnection(connectString,user, pwd);Statement stmt = con.createStatement();String fmt="delete from stu where id=%s";String sql = String.format(fmt,pid);int cnt = stmt.executeUpdate(sql);if(cnt>0) msg = "删除成功!";stmt.close();con.close();     }catch (Exception e){msg = e.getMessage();}%><!DOCTYPE HTML><html><head><title>新增学生记录</title><style>body{font-family:微软雅黑,宋体;}a:link,a:visited { color:blue; }.container{margin:0 auto;width:500px;text-align:center;}form { line-height:50px; }</style></head><body><div class="container"><h1>删除学生记录</h1><p><%=msg%></p><br><a href='browseStu.jsp'>返回</a></div></body></html>

5、编制翻页浏览页面(browseStu.jsp)，可以链接到其它页面，新增链接到addStu.jsp，修改链接到updateStu.jsp，删除链接到deleteStu.jsp，修改和删除采用url传递记录的id。

<%@ page language="java" import="java.util.*,java.sql.*" contentType="text/html; charset=utf-8"%><% request.setCharacterEncoding("utf-8");String msg ="";Integer pgno = 0; //当前页号Integer pgcnt = 4; //每页行数String param = request.getParameter("pgno");if(param != null && !param.isEmpty()){pgno = Integer.parseInt(param);}param = request.getParameter("pgcnt");if(param != null && !param.isEmpty()){pgcnt = Integer.parseInt(param);}int pgprev = (pgno>0)?pgno-1:0;int pgnext = pgno+1;String connectString = "jdbc:mysql://localhost:3306/teaching17"+ "?autoReconnect=true&useUnicode=true&characterEncoding=UTF-8"; String user="root";String pwd="0103";StringBuilder table = new StringBuilder();try{Class.forName("com.mysql.jdbc.Driver");Connection con=DriverManager.getConnection(connectString, user, pwd);Statement stmt=con.createStatement();String sql=String.format("select * from stu limit %d,%d", pgno*pgcnt,pgcnt);ResultSet rs=stmt.executeQuery(sql);table.append("<table><tr><th>id</th><th>学号</th><th>姓名</th>"+"<th>-</th></tr>");int pgcount = 0;while(rs.next()) {pgcount++;table.append(String.format("<tr><td>%s</td><td>%s</td><td>%s</td><td>%s %s</td></tr>",rs.getString("id"),rs.getString("num"),rs.getString("name"),"<a href='updateStu.jsp?pid="+rs.getString("id")+"'>修改</a>","<a href='deleteStu.jsp?pid="+rs.getString("id")+"'>删除</a>"));}if(pgcount < 4) {pgprev = (pgno>0)?pgno-1:0;pgnext = pgno;}table.append("</table>");rs.close(); stmt.close(); con.close();}catch (Exception e){msg = e.getMessage();}%><!DOCTYPE HTML><html><head><title>浏览学生名单</title><style>table{border-collapse: collapse;}td,th{border: solid grey 1px;width : 10rem;height : 2rem;}a:link,a:visited{color:blue}.container{margin:0 auto;width:500px;text-align:center;}div {margin-top : 20px;}</style></head><body><div class="container"><h1>浏览学生名单</h1><%=table%><div style="float:left"><a href="addStu.jsp">新增</a></div><div style="float:right"><a href="browseStu.jsp?pgno=<%=pgprev%>&pgcnt=<%=pgcnt%>">上一页</a><a href="browseStu.jsp?pgno=<%=pgnext%>&pgcnt=<%=pgcnt%>">下一页</a></div><br><br><%=msg%><br><br></div></body></html>

0 0