今日主题：JavaWeb后端jsp之增删改查
实体类：
Student.java:
package cn.itcast.model.entity;
public class Student {
private int sid;
private String sname;
private int score;
//private Sex sex;用外键对象替代外键字段。
private int sexid;
public int getSid() {
return sid;
}
public void setSid(int sid) {
this.sid = sid;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public int getScore() {
return score;
}
public void setScore(int score) {
this.score = score;
}
public int getSexid() {
return sexid;
}
public void setSexid(int sexid) {
this.sexid = sexid;
}
public Student(int sid, String sname, int score, int sexid) {
super();
this.sid = sid;
this.sname = sname;
this.score = score;
this.sexid = sexid;
}
}
Sex.java:
package cn.itcast.model.entity;
public class Sex {
private int sexid;
private String sexname;
public int getSexid() {
return sexid;
}
public void setSexid(int sexid) {
this.sexid = sexid;
}
public String getSexname() {
return sexname;
}
public void setSexname(String sexname) {
this.sexname = sexname;
}
public Sex(int sexid, String sexname) {
super();
this.sexid = sexid;
this.sexname = sexname;
}
}
Dao类：
StudentDao.java:
package cn.itcast.model.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;
import java.util.ArrayList;
import java.util.List;
import cn.itcast.model.entity.Student;
public class StudentDao {
//删除多条记录
public int deleteByIds(String[] sids) {
// 声明Connection对象
Connection con = null;
PreparedStatement psm = null;
// 驱动程序名
String driver = "com.mysql.jdbc.Driver";
// URL指向要访问的数据库名mydata
String url = "jdbc:mysql://localhost:3306/test5";
// MySQL配置时的用户名
String user = "root";
// MySQL配置时的密码
String password = "123456";
// 遍历查询结果集
try {
// 加载驱动程序
Class.forName(driver);
// 1.getConnection()方法，连接MySQL数据库！！
con = (Connection) DriverManager.getConnection(url, user, password);
if (!con.isClosed())
System.out.println("Succeeded connecting to the Database!");
// 2.创建statement类对象，用来执行SQL语句！！
String sql6 = "delete from student where sid in (";
for(String str:sids) {
sql6+="?"+",";
}
sql6=sql6.substring(0, sql6.length()-1);
sql6+=")";
psm = con.prepareStatement(sql6);
for(int i=1;i<=sids.length;i++) {
psm.setString(i, sids[i-1]);
}
int rs=psm.executeUpdate();//获得结果集游标
return rs;
} catch (ClassNotFoundException e) {
// 数据库驱动类异常处理
e.printStackTrace();
return 0;
} catch (SQLException e) {
// 数据库连接失败异常处理
e.printStackTrace();
return 0;
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
return 0;
} finally {
try {
psm.close();
con.close();
} catch (Exception e) {
// TODO: handle exception
}
}
}
//查询指定的信息
public Student findOnly(int sid){
// 声明Connection对象
Connection con = null;
PreparedStatement psm = null;
ResultSet rs = null;
// 驱动程序名
String driver = "com.mysql.jdbc.Driver";
// URL指向要访问的数据库名mydata
String url = "jdbc:mysql://localhost:3306/test5";
// MySQL配置时的用户名
String user = "root";
// MySQL配置时的密码
String password = "123456";
// 遍历查询结果集
try {
// 加载驱动程序
Class.forName(driver);
// 1.getConnection()方法，连接MySQL数据库！！
con = (Connection) DriverManager.getConnection(url, user, password);
if (!con.isClosed())
System.out.println("Succeeded connecting to the Database!");
// 2.创建statement类对象，用来执行SQL语句！！
String sql5 = "select * from student where sid=?";
psm = con.prepareStatement(sql5);
psm.setInt(1, sid);
rs=psm.executeQuery();//获得结果集游
if(rs.next()) {
Student s=new Student(rs.getInt("sid"),rs.getString("sname"),rs.getInt("score"),rs.getInt("sexid"));
return s;
}else {
return null;
}
} catch (ClassNotFoundException e) {
// 数据库驱动类异常处理
e.printStackTrace();
return null;
} catch (SQLException e) {
// 数据库连接失败异常处理
e.printStackTrace();
return null;
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
return null;
} finally {
try {
psm.close();
rs.close();
con.close();
} catch (Exception e) {
// TODO: handle exception
}
}
}
//修改
public boolean Update(Student stu){
// 声明Connection对象
Connection con = null;
PreparedStatement psm = null;
// 驱动程序名
String driver = "com.mysql.jdbc.Driver";
// URL指向要访问的数据库名mydata
String url = "jdbc:mysql://localhost:3306/test5";
// MySQL配置时的用户名
String user = "root";
// MySQL配置时的密码
String password = "123456";
// 遍历查询结果集
try {
// 加载驱动程序
Class.forName(driver);
// 1.getConnection()方法，连接MySQL数据库！！
con = (Connection) DriverManager.getConnection(url, user, password);
if (!con.isClosed())
System.out.println("Succeeded connecting to the Database!");
// 2.创建statement类对象，用来执行SQL语句！！
String sql4= "update student set sname=?,score=?,sexid=? where sid=?";
psm = con.prepareStatement(sql4);
psm.setString(1, stu.getSname());
psm.setInt(2, stu.getScore());
psm.setInt(3,stu.getSexid());
psm.setInt(4, stu.getSid());
int rs=psm.executeUpdate();//获得结果集游标
return rs==1;
} catch (ClassNotFoundException e) {
// 数据库驱动类异常处理
e.printStackTrace();
return false;
} catch (SQLException e) {
// 数据库连接失败异常处理
e.printStackTrace();
return false;
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
return false;
} finally {
try {
psm.close();
con.close();
} catch (Exception e) {
// TODO: handle exception
}
}
}
//添加记录
public boolean Add(Student stu) {
// 声明Connection对象
Connection con = null;
PreparedStatement psm = null;
// 驱动程序名
String driver = "com.mysql.jdbc.Driver";
// URL指向要访问的数据库名mydata
String url = "jdbc:mysql://localhost:3306/test5";
// MySQL配置时的用户名
String user = "root";
// MySQL配置时的密码
String password = "123456";
// 遍历查询结果集
try {
// 加载驱动程序
Class.forName(driver);
// 1.getConnection()方法，连接MySQL数据库！！
con = (Connection) DriverManager.getConnection(url, user, password);
if (!con.isClosed())
System.out.println("Succeeded connecting to the Database!");
// 2.创建statement类对象，用来执行SQL语句！！
String sql3 = "insert into student(sname,score,sexid) values(?,?,?)";
psm = con.prepareStatement(sql3);
psm.setString(1, stu.getSname());
psm.setInt(2, stu.getScore());
psm.setInt(3,stu.getSexid());
int rs=psm.executeUpdate();//获得结果集游标
return rs==1;
} catch (ClassNotFoundException e) {
// 数据库驱动类异常处理
e.printStackTrace();
return false;
} catch (SQLException e) {
// 数据库连接失败异常处理
e.printStackTrace();
return false;
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
return false;
} finally {
try {
psm.close();
con.close();
} catch (Exception e) {
// TODO: handle exception
}
}
}
//删除记录
public boolean deleteById(int sid) {
// 声明Connection对象
Connection con = null;
PreparedStatement psm = null;
// 驱动程序名
String driver = "com.mysql.jdbc.Driver";
// URL指向要访问的数据库名mydata
String url = "jdbc:mysql://localhost:3306/test5";
// MySQL配置时的用户名
String user = "root";
// MySQL配置时的密码
String password = "123456";
// 遍历查询结果集
try {
// 加载驱动程序
Class.forName(driver);
// 1.getConnection()方法，连接MySQL数据库！！
con = (Connection) DriverManager.getConnection(url, user, password);
if (!con.isClosed())
System.out.println("Succeeded connecting to the Database!");
// 2.创建statement类对象，用来执行SQL语句！！
String sql1 = "delete from student where sid=?";
psm = con.prepareStatement(sql1);
psm.setInt(1, sid);
int rs=psm.executeUpdate();//获得结果集游标
return rs==1;
} catch (ClassNotFoundException e) {
// 数据库驱动类异常处理
e.printStackTrace();
return false;
} catch (SQLException e) {
// 数据库连接失败异常处理
e.printStackTrace();
return false;
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
return false;
} finally {
try {
psm.close();
con.close();
} catch (Exception e) {
// TODO: handle exception
}
}
}
//查询
public List<Student> findAll(){
// 声明Connection对象
Connection con = null;
Statement statement = null;
ResultSet rs = null;
// 驱动程序名
String driver = "com.mysql.jdbc.Driver";
// URL指向要访问的数据库名mydata
String url = "jdbc:mysql://localhost:3306/test5";
// MySQL配置时的用户名
String user = "root";
// MySQL配置时的密码
String password = "123456";
// 遍历查询结果集
try {
// 加载驱动程序
Class.forName(driver);
// 1.getConnection()方法，连接MySQL数据库！！
con = (Connection) DriverManager.getConnection(url, user, password);
if (!con.isClosed())
System.out.println("Succeeded connecting to the Database!");
// 2.创建statement类对象，用来执行SQL语句！！
String sql2 = "select * from student";
statement = (Statement) con.createStatement();
rs=statement.executeQuery(sql2);//获得结果集游标
List<Student> list=new ArrayList<Student>();
while (rs.next()) {
list.add(new Student(rs.getInt("sid"),rs.getString("sname"),rs.getInt("score"),rs.getInt("sexid")));
}
return list;
} catch (ClassNotFoundException e) {
// 数据库驱动类异常处理
e.printStackTrace();
return null;
} catch (SQLException e) {
// 数据库连接失败异常处理
e.printStackTrace();
return null;
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
return null;
} finally {
try {
statement.close();
rs.close();
con.close();
} catch (Exception e) {
// TODO: handle exception
}
}
}
}
SexDao.java:
package cn.itcast.model.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import cn.itcast.model.entity.Sex;
import cn.itcast.model.entity.Student;
public class SexDao {
//查询
public List<Sex> findAll(){
// 声明Connection对象
Connection con = null;
Statement statement = null;
ResultSet rs = null;
// 驱动程序名
String driver = "com.mysql.jdbc.Driver";
// URL指向要访问的数据库名mydata
String url = "jdbc:mysql://localhost:3306/test5";
// MySQL配置时的用户名
String user = "root";
// MySQL配置时的密码
String password = "123456";
// 遍历查询结果集
try {
// 加载驱动程序
Class.forName(driver);
// 1.getConnection()方法，连接MySQL数据库！！
con = (Connection) DriverManager.getConnection(url, user, password);
if (!con.isClosed())
System.out.println("Succeeded connecting to the Database!");
// 2.创建statement类对象，用来执行SQL语句！！
String sql2 = "select * from sex";
statement = (Statement) con.createStatement();
rs=statement.executeQuery(sql2);//获得结果集游标
List<Sex> list=new ArrayList<Sex>();
while (rs.next()) {
list.add(new Sex(rs.getInt("sexid"),rs.getString("sexname")));
}
return list;
} catch (ClassNotFoundException e) {
// 数据库驱动类异常处理
e.printStackTrace();
return null;
} catch (SQLException e) {
// 数据库连接失败异常处理
e.printStackTrace();
return null;
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
return null;
} finally {
try {
statement.close();
rs.close();
con.close();
} catch (Exception e) {
// TODO: handle exception
}
}
}
}
查：
StudentServlet.java:
package cn.itcast.controller;
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 cn.itcast.model.dao.StudentDao;
import cn.itcast.model.entity.Student;
/**
* Servlet implementation class StudentServlet
*/
@WebServlet(name="/StudentServlet",urlPatterns = {"/StudentServlet"})
//默认urlPatterns和name相同，后面的urlpatterns可以不写
public class StudentServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public StudentServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//没有请求参数，不需要做请求解码
response.setContentType("text/html;charset=utf-8");
//用控制器调用Dao类
StudentDao dao=new StudentDao();
//获取结果
List<Student> list=dao.findAll();
//传递结果数据到服务器jsp页面
//存储到request或者session作用域
request.setAttribute("list", list);
//派发页面（转发，重定向）
request.getRequestDispatcher("/show.jsp").forward(request, response);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(req, resp);
}
}
show.jsp:
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="ISO-8859-1">
<title>Insert title here</title>
</head>
<c:if test="${not empty msg }">
<script>
alert("${msg}");
</script>
</c:if>
<body>
<form name="form" method="post" action="StudentMultipleDeleteServlet">
<table border="1">
<tr>
<td>选择</td>
<td>学号</td>
<td>姓名</td>
<td>成绩</td>
<td>性别</td>
<td>操作</td>
<td>操作</td>
<td>操作</td>
</tr>
<c:forEach items="${list}" var="stu">
<tr>
<td><input name="delete" type="checkbox" value="${stu.sid}" /></td>
<td>${stu.sid}</td>
<td>${stu.sname}</td>
<td>${stu.score}</td>
<td>${stu.sexid}</td>
<td><a href="StudentDeleteServlet?sid=${stu.sid }">删除</a></td>
<td><a href="StudentSelectServlet?sid=${stu.sid }">修改</a></td>
<td><a href="add.html">添加</a></td>
</tr>
</c:forEach>
<td><input type="submit" value="批量删除"></td>
</table>
</form>
</body>
</html>
删：
StudentDeleteServlet.java:
package cn.itcast.controller;
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 cn.itcast.model.dao.StudentDao;
import cn.itcast.model.entity.Student;
/**
* Servlet implementation class StudentDeleteServlet
*/
@WebServlet("/StudentDeleteServlet")
public class StudentDeleteServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public StudentDeleteServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
//读取sid参数
int sid=Integer.parseInt(request.getParameter("sid"));
request.setAttribute("sid", sid);
//调用dao对象删除
StudentDao dao=new StudentDao();
boolean a=dao.deleteById(sid);
if(a) {
//跳转网页
//提供弹窗
String msg="删除成功！";
request.setAttribute("msg", msg);
request.getRequestDispatcher("/StudentServlet").forward(request, response);
}else {
//在服务器拼出完整的html给前端
String html="<html><body>删除失败！</body></html>";
response.getWriter().print(html);
response.getWriter().flush();
response.getWriter().close();
}
}
}
改：
StudentSelectServlet.java:
package cn.itcast.controller;
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 cn.itcast.model.dao.SexDao;
import cn.itcast.model.dao.StudentDao;
import cn.itcast.model.entity.Sex;
import cn.itcast.model.entity.Student;
/**
* Servlet implementation class StudentSelectServlet
*/
@WebServlet("/StudentSelectServlet")
public class StudentSelectServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public StudentSelectServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
//接收参数
int sid=Integer.parseInt(request.getParameter("sid"));
StudentDao sd=new StudentDao();
Student s=sd.findOnly(sid);
//存到作用域
request.setAttribute("sid", sid);
request.setAttribute("sname", s.getSname());
request.setAttribute("score", s.getScore());
request.setAttribute("sexid",s.getSexid());
SexDao sexd=new SexDao();
List<Sex> list=sexd.findAll();
request.setAttribute("list",list);
request.getRequestDispatcher("update.jsp").forward(request, response);
}
}
update.jsp:
<%@ page language="java" import="cn.itcast.model.entity.Student" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
<form action="StudentUpdateServlet" method="post">
学号：<input type="text" id="sid" name="sid" value="${sid}"><br />
名字：<input type="text" id="username" name="sname" value="${sname}"><br />
分数：<input type="text" id="score" name="score" value="${score}"><br />
性别：<select name="select">
<c:forEach items="${list}" var="a">
<option value="${a.sexid}"
<c:if test="${sexid==a.sexid}">selected</c:if>>
${a.sexname }</option>
</c:forEach>
</select><br />
<input type="submit" value="添加">
</form>
</body>
</html>
加：
add.html:
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<form action="StudentAddServlet" method="post">
名字：<input type="text" id="username" name="name"><br />
分数：<input type="text" id="score" name="score"><br />
性别：<input type="text" id="sexid" name="sexid"><br />
<input type="submit" value="添加">
</form>
</body>
</html>
StudentAddServlet.java:
package cn.itcast.controller;
import java.io.IOException;
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 cn.itcast.model.dao.StudentDao;
import cn.itcast.model.entity.Student;
/**
* Servlet implementation class StudentAddServlet
*/
@WebServlet("/StudentAddServlet")
public class StudentAddServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public StudentAddServlet() {
super();
// TODO Auto-generated constructor stub
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
//处理接收数据
String sname=request.getParameter("name");
int score=Integer.parseInt(request.getParameter("score"));
int sexid=Integer.parseInt(request.getParameter("sexid"));
//插入数据
StudentDao stu=new StudentDao();
Student s=new Student(-1,sname,score,sexid);
boolean ss=stu.Add(s);
if(ss) {
String msg="添加成功！";
request.setAttribute("msg", msg);
request.getRequestDispatcher("/StudentServlet").forward(request, response);
}else {
//在服务器拼出完整的html给前端
String html="<html><body>删除失败！</body></html>";
response.getWriter().print(html);
response.getWriter().flush();
response.getWriter().close();
}
}
}
批量删除：
StudentMultipleDeleteServlet.java:
package cn.itcast.controller;
import java.io.IOException;
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 cn.itcast.model.dao.StudentDao;
/**
* Servlet implementation class StudentMultipleDeleteServlet
*/
@WebServlet("/StudentMultipleDeleteServlet")
public class StudentMultipleDeleteServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public StudentMultipleDeleteServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(req, resp);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
//读取sid参数
String[] ss=request.getParameterValues("delete");
int count;
//调用dao对象删除
StudentDao dao=new StudentDao();
count=dao.deleteByIds(ss);
if(count!=0) {
String msg="删除了"+count+"条记录！";
request.setAttribute("msg", msg);
request.getRequestDispatcher("/StudentServlet").forward(request, response);
}else {
//在服务器拼出完整的html给前端
String html="<html><body>删除失败！</body></html>";
response.getWriter().print(html);
response.getWriter().flush();
response.getWriter().close();
}
}
}