这次实验主要是对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>