增删改查使用

最新推荐文章于 2022-10-24 16:27:53 发布

xupen.wu

最新推荐文章于 2022-10-24 16:27:53 发布

阅读量175

点赞数

本文链接：https://blog.csdn.net/xupeng1997/article/details/105462333

版权

在这里插入图片描述

package org.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBUtil {
	static Connection conn = null;
	static Statement stat = null;
	static PreparedStatement pstm = null;
	static ResultSet rs = null;
	//通用的 增, 删 ,改 ,操作
	public static int executeUpdate(String sql, Object... params) {
		int rows = 0;
		try {
			conn = getConnection();//静态方法是直接调用驱动加载
			pstm = conn.prepareStatement(sql);
			for (int i = 0; i < params.length; i++) {
				pstm.setObject(i + 1, params[i]);
			}
			rows = pstm.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeALL(rs, stat, conn);
		}
		return rows;
	}
	public static Connection getConnection() throws SQLException {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
			String user = "scott";
			String password = "tiger";
			conn = DriverManager.getConnection(url, user, password);// 连接数据库
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}
	//关闭的方法
	public static void closeALL(ResultSet rs, Statement stat, Connection conn) {
		try {
			if (rs != null) {
				rs.close();
			}
			if (stat != null) {
				stat.close();
			}
			if (conn != null) {
				conn.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
}

package org.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.enrry.Student;
public class StuDoa {
	Connection conn = null;
	PreparedStatement pastm = null;
	ResultSet Query = null;

	public boolean select(int id) {
		return queyStudent(id) == null ? true : false;//如果反回true 说明 没有数据库数据 则可以添加
	}
	// 查询返回学生的一条数据；判断true 或false;
	public Student queyStudent(int id) {
		Student student = null;
		try {
			int ros = -1;
			conn = DBUtil.getConnection();
			String sql = "SELECT * FROM stu WHERE id = ?";
			pastm = conn.prepareStatement(sql);
			pastm.setInt(1, id);
			Query = pastm.executeQuery();
			while (Query.next()) {
				int id1 = Query.getInt("id");
				String name = Query.getString("name");
				String sex = Query.getString("sex");
				String classname = Query.getString("classname");
				String dormitory = Query.getString("dormitory");
				student = new Student(id1, name, sex, classname, dormitory);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeALL(Query, pastm, conn);
		}
		return student;
	}
	// 添加
	public boolean AddStudent(Student student) {
		try {
			int date = 0;
			conn = DBUtil.getConnection();
			String sql = "INSERT  INTO stu (id,name,sex,classname,Dormitory) VALUES(?,?,?,?,?)";
			pastm = conn.prepareStatement(sql);
			pastm.setInt(1, student.getId());
			pastm.setString(2, student.getName());
			pastm.setString(3, student.getSex());
			pastm.setString(4, student.getClassname());
			pastm.setString(5, student.getDormitory());
			date = pastm.executeUpdate();
			if (date > 0) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			DBUtil.closeALL(Query, pastm, conn);
		}
	}

//删除学生；
	public boolean DeletetStudent(int id) {
		PreparedStatement pastm2 = pastm;
		try {
			conn = DBUtil.getConnection();
			String sql = "delete FROM stu WHERE id = ?";
			pastm = conn.prepareStatement(sql);
			pastm.setInt(1, id);
			int dele = pastm.executeUpdate();
			if(dele>0) {
				return true;
			}else {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		finally {
			DBUtil.closeALL(Query, pastm2, conn);
		}
	
	}
//更新
	public boolean UpdateStudent(int id,Student student) {
		try {
			int update = 0;
			conn = DBUtil.getConnection();
			String sql = "update  stu set name=?,Sex=?,Classname=?,Dormitory=? WHERE id = ?";
			pastm = conn.prepareStatement(sql);
			
			pastm.setInt(5, id);
			//修改后的内容
			pastm.setString(1, student.getName());
			pastm.setString(2, student.getSex());
			pastm.setString(3, student.getClassname());
			pastm.setString(4, student.getDormitory());
			update = pastm.executeUpdate();
			if (update > 0) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			DBUtil.closeALL(Query, pastm, conn);
		}
	}

	//查询全部的学生
	public List<Student> QueyALLStudent() {
		List<Student>list  = new ArrayList<Student>();
		Student student = null;
		try {
			conn = DBUtil.getConnection();
			String sql = "SELECT * FROM stu  WHERE  STATUS='0'";
			pastm = conn.prepareStatement(sql);
			Query = pastm.executeQuery();
			while(Query.next()) {
				int id1 = Query.getInt("id");
				String name = Query.getString("name");
				String sex = Query.getString("sex");
				String classname = Query.getString("classname");
				String dormitory = Query.getString("dormitory");
				student = new Student(id1, name, sex, classname, dormitory);
				list.add(student);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeALL(Query, pastm, conn);
		}
		return list;
	}
}

package org.enrry;
public class Student {
private int id;
private String name;
private String sex;
private String classname;
private String dormitory;
public int getId() {
	return id;
}
public void setId(int id) {
	this.id = id;
}
public String getName() {
	return name;
}
public void setName(String name) {
	this.name = name;
}
public String getSex() {
	return sex;
}
public void setSex(String sex) {
	this.sex = sex;
}
public String getClassname() {
	return classname;
}
public void setClassname(String classname) {
	this.classname = classname;
}
public String getDormitory() {
	return dormitory;
}
public void setDormitory(String dormitory) {
	this.dormitory = dormitory;
}
@Override
public String toString() {
	return "Student [id=" + id + ", name=" + name + ", sex=" + sex + ", classname=" + classname + ", dormitory="
			+ dormitory + "]";
}
public Student(int id, String name, String sex, String classname, String dormitory) {
	super();
	this.id = id;
	this.name = name;
	this.sex = sex;
	this.classname = classname;
	this.dormitory = dormitory;
}
public Student(String name, String sex, String classname, String dormitory) {
	super();
	this.name = name;
	this.sex = sex;
	this.classname = classname;
	this.dormitory = dormitory;
}
}

package org.service;

import java.util.List;

import org.dao.StuDoa;
import org.enrry.Student;

public class Studervice {
	StuDoa  stu = new StuDoa();
	//删除
	public boolean deleteStu(int id) {//先判断id 是否存在；
		if(!stu.select(id)) {
			return stu.DeletetStudent(id);
		}
			return false;
		}
	//更新
	public boolean UpdateStu(int id,Student student) {//先判断id 是否存在；
		if(!stu.select(id)) {//存在 true;
			return stu.UpdateStudent(id,student);//返回true;
		}
			return false;
		}
	//查询全部
	public List<Student> querAllStu() {
		return stu.QueyALLStudent();
	}
	
	public Student queryStu(int id) {
		return stu.queyStudent(id);
		
	}
	
	//添加
public boolean addStu(Student student) {
	if(stu.select(student.getId())) {
		return	stu.AddStudent(student);
	}
		return false;
}
}

package org.servlet;
import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.enrry.Student;
import org.service.Studervice;

public class addServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
	response.setContentType("text/html; charset=UTF-8");
	request.setCharacterEncoding("UTF-8");
	int id =Integer.parseInt(request.getParameter("id"));
	String name = request.getParameter("name");
	String sex = request.getParameter("sex");
	String classname = request.getParameter("classname");
	String dormitory = request.getParameter("dormitory");
	Student student = new Student(id, name, sex, classname, dormitory);
	Studervice stu = new Studervice();
	boolean addStudent = stu.addStu(student);
	PrintWriter out = response.getWriter();
	/*if(addStudent) {
		request.setAttribute("error", "AddError");
		out.print("<script>alert('添加成功')</script>");
	}else {
		out.print("<script>alert('添加失败')</script>");
	}*/
	if(addStudent) {//如果成功失败 给个提示
		request.setAttribute("right", "AddRiget");
	}else {
		request.setAttribute("right", "AddError");
	}
	//response.sendRedirect("querAllStu");//修改成功访问查询页面；
	request.getRequestDispatcher("querAllStu").forward(request, response);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
	doGet(req, resp);
}
}

package org.servlet;

import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.service.Studervice;

public class DeleteServlet extends HttpServlet {
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	@Override
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		response.setContentType("text/html; charset=UTF-8");
		request.setCharacterEncoding("UTF-8");
		int id =Integer.parseInt(request.getParameter("id"));
		Studervice stu = new Studervice();
		 PrintWriter out = response.getWriter();
		boolean delete = stu.deleteStu(id);//执行这个方法返回的是true 
		if(delete) {
			request.setAttribute("Delright", "DelRiget");
		}else {
			request.setAttribute("Delright", "DelError");
		}
		//response.sendRedirect("querAllStu");//修改成功访问查询页面；
		request.getRequestDispatcher("querAllStu").forward(request, response);
	}
	@Override
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doGet(request, response);
	}
}

package org.servlet;


import java.io.IOException;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.enrry.Student;
import org.service.Studervice;
public class querAllStu extends HttpServlet {
	private static final long serialVersionUID = 1L;
	@Override
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		response.setContentType("text/html; charset=UTF-8");
		request.setCharacterEncoding("UTF-8");
		Studervice stu = new Studervice();
		List<Student> st = stu.querAllStu();
	    request.setAttribute("Student", st);
		//转发传数据
		request.getRequestDispatcher("index.jsp").forward(request, response);
	}
	@Override
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    doGet(request, response);
	}
}

package org.servlet;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.enrry.Student;
import org.service.Studervice;
//根据id进行查询；
public class queryStu extends HttpServlet {
	private static final long serialVersionUID = 1L;
	@Override
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		response.setContentType("text/html; charset=UTF-8");
		request.setCharacterEncoding("UTF-8");
		int id =Integer.parseInt(request.getParameter("id"));
		Studervice stu = new Studervice();
		Student st = stu.queryStu(id);
		request.setAttribute("Student", st);
		request.getRequestDispatcher("update.jsp").forward(request, response);
	}
	@Override
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    doGet(request, response);
	}
}

package org.servlet;

import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.enrry.Student;
import org.service.Studervice;

public class UpdateStu extends HttpServlet {
	private static final long serialVersionUID = 1L;
	@Override
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		response.setContentType("text/html; charset=UTF-8");
		request.setCharacterEncoding("UTF-8");
		int id =Integer.parseInt(request.getParameter("id"));
		String name = request.getParameter("name");
		String sex = request.getParameter("sex");
		String classname = request.getParameter("classname");
		String dormitory = request.getParameter("dormitory");
		Student stud = new Student( name, sex, classname, dormitory);
		Studervice stu = new Studervice();
		boolean update = stu.UpdateStu(id, stud);
		 PrintWriter out = response.getWriter();
		if(update) {
			request.setAttribute("Upright", "UpdaRiget");
		}else {
			request.setAttribute("Upright", "Uperror");
		}
		//response.sendRedirect("querAllStu");//修改成功访问查询页面；
		request.getRequestDispatcher("querAllStu").forward(request, response);
	}
	@Override
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doGet(request, response);
	}
}

```java
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd" id="WebApp_ID" version="3.1">
  <display-name>pinguo</display-name>
  <welcome-file-list>
    <welcome-file>querAllStu</welcome-file>
    <welcome-file>index.htm</welcome-file>
    <welcome-file>index.jsp</welcome-file>
    <welcome-file>default.html</welcome-file>
    <welcome-file>default.htm</welcome-file>
    <welcome-file>default.jsp</welcome-file>
  </welcome-file-list>
  
  <servlet>
   <servlet-name>querAllStu</servlet-name>
  <servlet-class>org.servlet.querAllStu</servlet-class>
  </servlet>
  <servlet-mapping>
  <servlet-name>querAllStu</servlet-name>
  <url-pattern>/querAllStu</url-pattern>
  </servlet-mapping>
  
  <servlet>
   <servlet-name>UpdateStu</servlet-name>
  <servlet-class>org.servlet.UpdateStu</servlet-class>
  </servlet>
  <servlet-mapping>
  <servlet-name>UpdateStu</servlet-name>
  <url-pattern>/UpdateStu</url-pattern>
  </servlet-mapping>
  

  <servlet>
   <servlet-name>addServlet</servlet-name>
  <servlet-class>org.servlet.addServlet</servlet-class>
  </servlet>
  <servlet-mapping>
  <servlet-name>addServlet</servlet-name>
  <url-pattern>/addServlet</url-pattern>
  </servlet-mapping>
  

   <servlet>
   <servlet-name>DeleteServlet</servlet-name>
  <servlet-class>org.servlet.DeleteServlet</servlet-class>
  </servlet>
  <servlet-mapping>
  <servlet-name>DeleteServlet</servlet-name>
  <url-pattern>/DeleteServlet</url-pattern>
  </servlet-mapping>
  
  <servlet>
   <servlet-name>queryStu</servlet-name>
  <servlet-class>org.servlet.queryStu</servlet-class>
  </servlet>
  <servlet-mapping>
  <servlet-name>queryStu</servlet-name>
  <url-pattern>/queryStu</url-pattern>
  </servlet-mapping>
 
</web-app>
```java

```jsp
<%@page import="org.apache.jasper.tagplugins.jstl.core.ForEach"%>
<%@page import="java.util.List"%>
<%@page import="org.enrry.Student"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>学生信息表</title>
</head>
<body>
<%
String right =(String)request.getAttribute("right");
if(right!=null){
if(right.equals("AddRiget")){
	out.print("<script>alert('添加成功');</script>");
}else if(right.equals("AddError")){
	out.print("<script>alert('添加失败');</script>");
}else {}
}

String Upright =(String)request.getAttribute("Upright");
if(Upright!=null){
if(Upright.equals("UpdaRiget")){
	out.print("<script>alert('更新成功');</script>");
}else if(Upright.equals("Uperror")){
	out.print("<script>alert('更新失败');</script>");
}else {}
}

String Delright =(String)request.getAttribute("Delright");
if(Delright!=null){
if(Delright.equals("DelRiget")){
	out.print("<script>alert('删除成功');</script>");
}else if(Delright.equals("DelError")){
	out.print("<script>alert('删除失败');</script>");
}else {}
}

%>
<form>
<table width="350px" height="10px" border="1px">
<tr>
<td>id</td>
<td>姓名</td>
<td>性别</td>
<td>班级</td>
<td>宿舍</td>
<td>操作</td>
</tr>
<%
List<Student> list = (List<Student>)request.getAttribute("Student");
for(Student s:list){
	%>
	<tr>
<td><%=s.getId()%></td>
<td><%=s.getName() %></td>
<td><%=s.getSex() %></td>
<td><%=s.getClassname()%></td>
<td><%=s.getDormitory()%></td>
<td><a href="DeleteServlet?id=<%=s.getId()%>">删除</a>
<a href="queryStu?id=<%=s.getId()%>">更新</a>
</td>
</tr>
	<%	
}
%>
</table>
</form>
<a href="login.jsp">添加</a>
</body>
</html>

<%@page import="org.enrry.Student"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>更新</title>
</head>
<body>
<%
Student st =(Student)request.getAttribute("Student");
String name = st.getName();
String Sex = st.getSex();
String Classname = st.getClassname();
String Dormitory = st.getDormitory();
%>
<form action="UpdateStu" method="post">
<table>
<tr><td>学号：<input type="text" name="id" value=<%=st.getId()%> readonly="readonly"></td></tr>
<tr><td>姓名：<input type="text" name="name" value=<%=name %>></td></tr>
<tr><td>性别：<input type="text" name="sex"  value=<%=Sex %>></td></tr>
<tr><td>班级：<input type="text" name="classname"  value=<%=Classname %>></td></tr>
<tr><td>宿舍：<input type="text" name="dormitory" value=<%=Dormitory %>></td></tr>
<tr><td><input type="submit" value="更新"></td></tr>
</table>
</form>
</body>
</html>

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<form action="addServlet"method="post"><br>
<table>
<tr><td>学号：<input type="text" name="id"></td></tr>
<tr><td>姓名：<input type="text" name="name"></td></tr>
<tr><td>性别：<input type="text" name="sex"></td></tr>
<tr><td>班级：<input type="text" name="classname"></td></tr>
<tr><td>宿舍：<input type="text" name="dormitory"></td></tr>
<tr><td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<input type="submit" value="提交">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<input type="reset" value="重置">
<a href="querAllStu">返回</a>

</td></tr>
</table>
</form>
</body>
</html>