说明:在先前的实验五--使用动态网页删除一条记录项目中修改teacherView.jsp(或deleteTeacher.jsp),新增editTeacher.jsp 和editTeacher.java. 并修改相关代码片段的参数至符合自己项目结构的要求。
teacherView.jsp(或deleteTeacher.jsp):
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8" import="java.sql.*" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>study2教师表jsp</title>
</head>
<body>
SQLserver2019 study2 教师表:
<table border = "1">
<tr>
<td>教师编号</td>
<td>教师姓名</td>
<td>教师性别</td>
<td>教师学历</td>
<td>教师职称</td>
<td>单位编号</td>
<!--指导书新增操作列-->
<td>操作</td>
</tr>
<%
String url = "jdbc:sqlserver://localhost:10086;DatabaseName=study2";
Connection con = null;
Statement stmt=null;
String query="SELECT * FROM teacher";
try{
//加载jdbc驱动程序
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//建立连接
con=DriverManager.getConnection(url,"sa1","202005295");
//创建Statement语句
stmt=con.createStatement();
//执行SQL语句
ResultSet rs=stmt.executeQuery(query);
while (rs.next()){
String teaId = rs.getString(1);
String teaName = rs.getString(2);
String teaSex = rs.getString(3);
String teaXueli = rs.getString(4);
String teaZhicheng = rs.getString(5);
String deptId = rs.getString(6);
out.println("<tr><td>"+teaId+"</td><td>"+teaName+"</td><td>"+teaSex+"</td><td>"+teaXueli+"</td><td>"+teaZhicheng+"</td><td>"+deptId+ "</td><td><a href='" + "http://localhost:8080/%E6%95%B0%E6%8D%AE%E5%BA%93%E6%8A%80%E6%9C%AFA%E5%AE%9E%E9%AA%8C%E5%9B%9B+/Jsp/editTeacher.jsp?teaID=" + teaId + "'>修改</a><a href='"+ "http://localhost:8080/%E6%95%B0%E6%8D%AE%E5%BA%93%E6%8A%80%E6%9C%AFA%E5%AE%9E%E9%AA%8C%E5%9B%9B+/deleteTeacher.do?teaID=" + teaId +"'>删除</a></td></tr>");
}
}catch (ClassNotFoundException e){
out.print("类没有找到异常:");
out.println(e.getMessage());
}catch (SQLException e){
out.println("SQL异常:"+e.getMessage());
}finally{
if (stmt != null){
try{
stmt.close();
}catch (SQLException e){
}
stmt=null;
}
if (con != null){
try{
con.close();//关闭连接
}catch (SQLException e){
}
con = null;
}
}
%>
</table>
</body>
</html>
editTeacher.jsp:
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8" import="java.sql.*" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>editTeacher.jsp</title>
</head>
<body>
<h1>editTeacher</h1>
<!-- 显示上一页所选中的数据 -->
<form action="/数据库技术A实验四+/editTeacher.do" method="post">
教师编号:<input type="text" name="teaid" readonly id="teaid">
<br>
教师姓名:<input type="text" name="teaname" id="teaname">
<br>
<!-- 教师性别:<input type="text" name="teasex" id="teasex"> -->
教师性别:<select name="teasex" id="teasex">
<option value="男">男</option>
<option value="女">女</option>
</select>
<br>
教师学历:<input type="text" name="teaxueli" id="teaxueli">
<br>
教师职称:<input type="text" name="teazhicheng" id="teazhicheng">
<br>
<!-- 单位编号:<input type="text" name="deptid" id="deptid"> -->
单位编号:<select name="deptid" id="deptid">
<%
String url1 = "jdbc:sqlserver://localhost:10086;DatabaseName=study2";
Connection con1 = null;
Statement stmt1=null;
try{
String query="SELECT * FROM 单位表$";
//加载jdbc驱动程序
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//建立连接
con1=DriverManager.getConnection(url1,"sa1","202005295");
//创建Statement语句
stmt1=con1.createStatement();
//执行SQL语句
ResultSet rs=stmt1.executeQuery(query);
while (rs.next()){
String deptId = rs.getString(1);
String deptName = rs.getString(2);
//渲染所有单位信息至下列列表选项中
out.println("<option value='" + deptId +"'>" + deptId + " "+ deptName + "</option>");
}
}catch (ClassNotFoundException e){
out.print("类没有找到异常:");
out.println(e.getMessage());
}catch (SQLException e){
out.println("SQL异常:"+e.getMessage());
}finally{
if (stmt1 != null){
try{
stmt1.close();
}catch (SQLException e){
}
stmt1=null;
}
if (con1 != null){
try{
con1.close();//关闭连接
}catch (SQLException e){
}
con1 = null;
}
}
%>
</select>
<br>
<input type="submit" value="提交">
</form>
<%
String url = "jdbc:sqlserver://localhost:10086;DatabaseName=study2";
Connection con = null;
Statement stmt=null;
try{
//获取前端浏览器传入的教师编号参数
String teaID = request.getParameter("teaID");
//CONVERT(nvarchar," + teaID +")
//String query="SELECT * FROM teacher where 教师编号 = CONVERT(nvarchar," + teaID +")";
String query="SELECT * FROM teacher where 教师编号 = CONVERT(nvarchar,'" + teaID +"')";
//加载jdbc驱动程序
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//建立连接
con=DriverManager.getConnection(url,"sa1","202005295");
//创建Statement语句
stmt=con.createStatement();
//执行SQL语句
ResultSet rs=stmt.executeQuery(query);
while (rs.next()){
String teaId = rs.getString(1);
String teaName = rs.getString(2);
String teaSex = rs.getString(3);
String teaXueli = rs.getString(4);
String teaZhicheng = rs.getString(5);
String deptId = rs.getString(6);
out.println("<script>");
out.println("document.querySelector('#teaid').value='" + teaId + "'");
out.println("document.querySelector('#teaname').value='" + teaName + "'");
out.println("document.querySelector('#teasex').value='" + teaSex + "'");
out.println("document.querySelector('#teaxueli').value='" + teaXueli + "'");
out.println("document.querySelector('#teazhicheng').value='" + teaZhicheng + "'");
out.println("document.querySelector('#deptid').value='" + deptId + "'");
out.println("</script>");
}
}catch (ClassNotFoundException e){
out.print("类没有找到异常:");
out.println(e.getMessage());
}catch (SQLException e){
out.println("SQL异常:"+e.getMessage());
}finally{
if (stmt != null){
try{
stmt.close();
}catch (SQLException e){
}
stmt=null;
}
if (con != null){
try{
con.close();//关闭连接
}catch (SQLException e){
}
con = null;
}
}
%>
</body>
</html>
editTeacher.java:
package Servlet1;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
* Servlet implementation class editTeacher
*/
@WebServlet("/editTeacher.do")
public class editTeacher extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public editTeacher() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
response.getWriter().append("Served at: ").append(request.getContextPath());
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
request.setCharacterEncoding("UTF-8");
String driveName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
try {
String teaid = request.getParameter("teaid");
String teaname = request.getParameter("teaname");
String teasex = request.getParameter("teasex");
String teaxueli = request.getParameter("teaxueli");
String teazhicheng = request.getParameter("teazhicheng");
String deptid = request.getParameter("deptid");
Class.forName(driveName);
String url = "jdbc:sqlserver://localhost:10086;DatabaseName=study2";;
Connection con = DriverManager.getConnection(url, "sa1","202005295");
Statement statement = con.createStatement();
//delete from teacher where teacher.教师编号 = CONVERT(nvarchar,313368)
String sql ="update teacher set 教师编号='" + teaid +"',教师姓名='" + teaname + "',教师性别='" + teasex + "',教师学历='" + teaxueli + "',教师职称='" + teazhicheng + "',单位编号='" + deptid +"' where 教师编号=CONVERT(nvarchar,'" + teaid +"')";
Boolean flag = statement.execute(sql);
// 执行完成之后返回的网页地址(servlet或jsp网页)
//网址内中文乱码解决:服务器默认接受方式字符编码与UTF-8编码格式互相转换
String UTF_8text1 = "数据库技术A实验四+";
// String UTF_8text2 = "中文路径2";
// String UTF_8text3 = "中文路径3";
response.sendRedirect("/"+new String(UTF_8text1.getBytes("utf-8"),"iso-8859-1")+"/Jsp/teacherView.jsp");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}