今日主题: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 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 list=new ArrayList();
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 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 list=new ArrayList();
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 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:
pageEncoding="utf-8"%>
Insert title herealert("${msg}");
选择 | 学号 | 姓名 | 成绩 | 性别 | 操作 | 操作 | 操作 |
${stu.sid} | ${stu.sname} | ${stu.score} | ${stu.sexid} | 删除 | 修改 | 添加 |
删:
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="
删除失败!";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 list=sexd.findAll();
request.setAttribute("list",list);
request.getRequestDispatcher("update.jsp").forward(request, response);
}
}
update.jsp:
pageEncoding="utf-8"%>
Insert title here学号:
名字:
分数:
性别:
selected>
${a.sexname }
加:
add.html:
Insert title here名字:
分数:
性别:
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="
删除失败!";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="
删除失败!";response.getWriter().print(html);
response.getWriter().flush();
response.getWriter().close();
}
}
}
界面:
欢迎关注:java后端指南