数据库技术A实验六--使用触发器

信管201张辉

已于 2024-05-01 15:48:06 修改

阅读量371

点赞数

分类专栏：数据库技术A实验2022 文章标签：数据库 servlet java

于 2022-11-09 22:28:16 首次发布

本文链接：https://blog.csdn.net/weixin_57107807/article/details/127779350

版权

数据库技术A实验2022 专栏收录该内容

6 篇文章 1 订阅

订阅专栏

说明：创建SQLserver触发器，在已建立的实验四动态网页项目中，修改deptView.jsp，新增deleteDept.java.并修改相关代码片段的参数至符合自己项目结构的要求。

SQLserver触发器：

CREATE TRIGGER dept_deleted
ON 单位表$ FOR DELETE
AS
DELETE FROM teacher WHERE 单位编号=(SELECT 单位编号 FROM DELETED)

deptView.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>
</tr>
<%
String url = "jdbc:sqlserver://localhost:10086;DatabaseName=study2";
Connection con = null;
Statement stmt=null;
String query="SELECT * FROM 单位表$";
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 deptId = rs.getString(1);
		String deptName = rs.getString(2);
		//"'>修改</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>"
		out.println("<tr><td>"+deptId+"</td><td>"+deptName+"</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+/deleteDept.do?deptID="+ deptId+"'>删除</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>

deleteDept.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 deleteDept
 */
@WebServlet("/deleteDept.do")
public class deleteDept extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public deleteDept() {
        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());
		//删除方法
				request.setCharacterEncoding("UTF-8");
				String driveName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
				try {
					//获取前端参数
					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 ="delete from 单位表$ where 单位编号 = CONVERT(nvarchar,'" + deptID +"');";
					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/deptView.jsp");
				} catch (ClassNotFoundException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
	}

	/**
	 * @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);
	}

}