实验目的及要求:
- 掌握在JSP中使用JDBC访问数据库的方法;
- 掌握查询数据表的方法;
- 掌握修改数据表中记录的方法;
- 掌握查询内容分页显示技术;
- 掌握删除数据表中记录的方法;
- 掌握查询内容分页显示技术。
实验内容:
编写一个JSP项目,利用JDBC技术实现对数据的访问(增,删,改,查及分页显示)操作。
实验关键代码:(重复代码可以不提交)
实验要求1,2,3请老师查看学习通作业(数据查询与插入操作),该作业中包含1,2,3.
Example6_6.jsp
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%@ page contentType="text/html;charset=utf-8" %>
<html>
<body>
<h1><center>更新学生信息表</center></h1>
<form action="example6_6_receive.jsp" method="post">
<table border="1" align="center" bgcolor="yellow">
<tr><td>学号</td><td><input type="text" name="sno"></td></tr>
<tr><td>姓名</td><td><input type="text" name="name"></td></tr>
<tr><td>出生日期</td><td><input type="text" name="birth"></td></tr>
<tr><td>电话号码</td><td><input type="text" name="phone"></td></tr>
<tr><td>地址</td><td><input type="text" name="address"></td></tr>
<tr><td>性别</td><td><input type="text" name="sex"></td></tr>
<tr><td>年级</td><td><input type="text" name="grade"></td></tr>
<tr><td>专业</td><td><input type="text" name="major"></td></tr>
<tr><td>班级</td><td><input type="text" name="classes"></td></tr>
<tr><td><input type="submit" name="tijiao" value="提交"></td><td><input type="reset" name="tijiao" value="重置"></td></tr>
</table>
</form>
</body>
</html>
Example6_6-2.jsp
<%@ page contentType="text/html; charset=utf-8" import="java.sql.*" %>
<HTML>
<BODY>
<HR>
<CENTER>
<%request.setCharacterEncoding("utf-8");//处理中文乱码问题 %>
<%!
String driverName = "com.mysql.jdbc.Driver";
String dbURL = "jdbc:mysql://127.0.0.1:3306/smdb?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC&user=root&password=123456";
Connection con;
PreparedStatement pst;
ResultSet rs;
int temp;
%>
<%
String sno=request.getParameter("sno");
String name=request.getParameter("name");//获取前台用户输入
String birth=request.getParameter("birth");
String phone=request.getParameter("phone");
String address=request.getParameter("address");
String sex=request.getParameter("sex");
String grade=request.getParameter("grade");
String major=request.getParameter("major");
String classes=request.getParameter("classes");
%>
<%
try {
Class.forName(driverName);
con = DriverManager.getConnection(dbURL);
out.print("连接成功!");
String sql="update student set name=?,birth=?,phone=?,address=?,sex=?,grade=?,major=?,classes=? where sno=?";
pst=con.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
pst.setString(9,sno);
pst.setString(1,name);
pst.setString(2,birth);
pst.setString(3,phone);
pst.setString(4,address);
pst.setString(5,sex);
pst.setString(6,grade);
pst.setString(7,major);
pst.setString(8,classes);
//给IN参数赋值
temp=pst.executeUpdate();
}
catch(Exception e) {
e.printStackTrace();
}
if(temp!=0){
response.sendRedirect("example6_1.jsp");
}
else{
out.print("更新数据失败");
}
pst.close();//关闭Statement对象
con.close();//关闭Connection对象
%>
</TABLE>
</CENTER>
<a href="main.jsp">返回</a>
</BODY>
</HTML>
Example6_7.jsp
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<html>
<body>
<body>
<form action="example6_7_receive.jsp" >
输入要删除图书的学号:
<input name="sno" type="text">
<input type="submit" value="提交">
</form>
</body>
</body>
</html>
Example6_7-2.jsp
<%@ page contentType="text/html; charset=utf-8" import="java.sql.*" %>
<HTML>
<BODY>
<HR>
<CENTER>
<%!
String driverName = "com.mysql.jdbc.Driver";
String dbURL = "jdbc:mysql://127.0.0.1:3306/smdb?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC&user=root&password=123456";
Connection con;
PreparedStatement pst;
ResultSet rs;
int temp;
%>
<%
String sno=request.getParameter("sno");//获取前台用户输入
%>
<%
try {
Class.forName(driverName);
con = DriverManager.getConnection(dbURL);
out.print("连接成功!");
pst=con.prepareStatement("delete from student where sno=?");//预编设语句
pst.setString(1,sno);
//给IN参数赋值
temp=pst.executeUpdate();
}
catch(Exception e) {
e.printStackTrace();
}
if(temp!=0){
response.sendRedirect("example6_1.jsp");
}
else{
out.print("删除数据失败");
}
pst.close();//关闭Statement对象
con.close();//关闭Connection对象
%>
</TABLE>
<a href="main.jsp">返回</a>
</CENTER>
</BODY>
</HTML>
Example6_8.jsp:
<%@ page contentType="text/html; charset=utf-8" import="java.sql.*" %>
<html>
<head>
<style type="text/css">
<!--
.style1 {
font-size: 24px;
color: #3300FF;
}
-->
</style>
</head>
<body>
<div align="center"><span class="style1">分页显示记录</span><BR>
</div>
<BR>
<table border=2 bordercolor="#FF0000" align="center">
<tr>
<td>学号</td>
<td>姓名</td>
<td>出生日期</td>
<td>电话号码</td>
<td>地址</td>
<td>性别</td>
<td>年级</td>
<td>专业</td>
<td>班级</td>
</tr>
<% Class.forName("com.mysql.jdbc.Driver");
String url="jdbc:mysql://localhost/smdb?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC&user=root&password=123456";
Connection conn= DriverManager.getConnection(url);
int intPageSize; //一页显示的记录数
int intRowCount; //记录总数
int intPageCount; //总页数
int intPage; //待显示页码
String strPage;
int i;
intPageSize =4; //设置一页显示的记录数
strPage = request.getParameter("page"); //取得待显示页码
if(strPage==null){
//表明在QueryString中没有page这一个参数,此时显示第一页数据
intPage = 1;
} else{
//将字符串转换成整型
intPage = Integer.parseInt(strPage);
if(intPage<1) intPage = 1;
}
Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
String sql="select * from student";
ResultSet rs=stmt.executeQuery(sql);
rs.last(); //光标指向查询结果集中最后一条记录
intRowCount = rs.getRow(); //获取记录总数
intPageCount = (intRowCount+intPageSize-1) / intPageSize; //记算总页数
if(intPage>intPageCount)
intPage = intPageCount;//调整待显示的页码
if(intPageCount>0)
{
rs.absolute((intPage-1) * intPageSize + 1); //将记录指针定位到待显示页的第一条记录上
//显示数据
i = 0;
while(i<intPageSize && !rs.isAfterLast()) {%>
<tr>
<td><%=rs.getString("sno")%> </td>
<td><%=rs.getString("name")%> </td>
<td><%=rs.getString("birth")%> </td>
<td><%=rs.getString("phone")%> </td>
<td><%=rs.getString("address")%> </td>
<td><%=rs.getString("sex")%> </td>
<td><%=rs.getString("grade")%> </td>
<td><%=rs.getString("major")%> </td>
<td><%=rs.getString("classes")%> </td>
</tr>
<% rs.next();
i++;
}
}
%>
</table>
<hr color="#999999" >
<div align="center">第<%=intPage%>页 共<%=intPageCount%>页
<%if(intPage<intPageCount){%>
<a href="example6_8.jsp?page=<%=intPage+1%>">下一页</a>
<%}%>
<%if(intPage>1){%>
<a href="example6_8.jsp?page=<%=intPage-1%>">上一页</a>
<%}%>
<%rs.close();
stmt.close();
conn.close();
%>
</div>
<div align=center><a href="main.jsp"><br/>返回主页</div>
</body>
</html>
实验结果:
数据表更新前:
数据表更新后:
删除前:
删除后
分页显示: