JDBC+Servlet+JSP的学生案例增删改查
数据库信息传输到页面实现。
先进行学生信息页面展示：
接口IStudentDao
public interface IStudentDao { /** * 保存操作 * @param stu 学生对象，封装了需要保存的对象 */ void save(Student stu); /** * 删除操作 * @param id 被删除学生的主键操作 */ void delete(Long id); /** * * @param id 被更改学生的主键值 * @param newStu 学生新的信息 */ void update(Student newStu); /** * 查询指定id的学生对象 * @param id 被查询学生的主键值 * @return 如果id存在，返回学生对象，否则为null */ Student get(Long id); /** * 查询并返回所有学生对象 * @return 如果结果集为空，返回一个空的list对象 */ List<Student> listall(); }
IStudentDaoImpl
public class IStudentDaoImpl implements IStudentDao{ public void save(Student stu) { String sql ="insert into t_student (name,age) values (?,?)"; PreparedStatement ps = null; //贾琏 Connection conn = null; try { conn = JDBCUtil.getConn(); ps = conn.prepareStatement(sql);//欲 ps.setString(1, stu.getName()); ps.setInt(2, stu.getAge()); ps.executeUpdate();//执行 } catch (SQLException e) { e.printStackTrace(); } JDBCUtil.close(conn, ps, null);//事务 } @Override public void delete(Long id) { String sql ="delete from t_student where id = ?"; PreparedStatement ps =null; //贾琏 Connection conn = null; try { conn = JDBCUtil.getConn(); ps =conn.prepareStatement(sql); ps.setLong(1, id); ps.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } JDBCUtil.close(conn, ps, null); } //update t_student set name='xx',age=17 where id=12 @Override public void update(Student stu) { String sql="update t_student set name =? ,age=? where id=?"; PreparedStatement ps =null; //贾琏 Connection conn =null; try { conn = JDBCUtil.getConn(); ps=conn.prepareStatement(sql); ps.setString(1, stu.getName()); ps.setInt(2, stu.getAge()); ps.setLong(3, stu.getId()); ps.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } JDBCUtil.close(conn, ps, null); } public Student get(Long id) { String sql ="select * from t_student where id=?"; PreparedStatement ps =null; //贾琏 Connection conn =null; ResultSet rs = null; try { conn = JDBCUtil.getConn(); ps = conn.prepareStatement(sql); ps.setLong(1, id); rs= ps.executeQuery(); if (rs.next()) { Student stu = new Student(); stu.setId(rs.getLong("id")); stu.setName(rs.getString("name")); stu.setAge(rs.getInt("age")); return stu; } } catch (Exception e) { e.printStackTrace(); } JDBCUtil.close(conn, ps, rs); return null; } @Override public List<Student> listall() { List<Student> list = new ArrayList<>(); String sql ="select * from t_student"; PreparedStatement ps = null; Connection conn = null; ResultSet rs =null; try { conn = JDBCUtil.getConn(); ps = conn.prepareStatement(sql); rs = ps.executeQuery(); while(rs.next()){ Student stu = new Student(); stu.setId(rs.getLong("id")); stu.setName(rs.getString("name")); stu.setAge(rs.getInt("age")); list.add(stu); } } catch (Exception e) { e.printStackTrace(); }finally { JDBCUtil.close(conn, ps, rs); } return list; }
domain类
public class Student { private Long id; private String name; private Integer age; public Student(){} public Student(String name,Integer age){ this.age=age; this.name=name; } public Long getId() { return id; } public void setId(Long id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } @Override public String toString() { return "Student [id=" + id + ", name=" + name + ", age=" + age + "]"; } }
设计个util类，方便维护
public class JDBCUtil { private static DataSource ds =null; static{ //当JDBCUtil执行后，直接加载至JVM就立即执行 try { Properties p = new Properties(); p.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties")); ds = DruidDataSourceFactory.createDataSource(p); } catch (Exception e) { e.printStackTrace(); } } public static Connection getConn(){ try { return ds.getConnection(); } catch (SQLException e) { e.printStackTrace(); } return null; } public static void close(Connection conn,Statement st,ResultSet rs){ try { if (rs!=null) { rs.close(); } } catch (Exception e) { }finally { try { if (st!=null) { st.close(); } } catch (Exception e) { e.printStackTrace(); }finally { try { if (conn!=null) { conn.close(); } } catch (Exception e) { e.printStackTrace(); } } } } }
在网页展示全部信息，将数据用student传输过去，在前台获取。
@WebServlet("/student/list")
public class ListStudentServlet extends HttpServlet{
private static final long serialVersionUID = 1L;
private IStudentDao dao;
@Override
public void init() throws ServletException {
dao = new IStudentDaoImpl();
}
@Override
protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//1.接收请求参数封装成对象
//2.调用业务方法处理请求
List<Student> list = dao.listall();
req.setAttribute("students", list);
//3.控制界面跳转
req.getRequestDispatcher("/WEB-INF/view/student/list.jsp").forward(req, resp);
}
}
${student}获取后端传输过来的数据。然后c:forEach展示。
<h2 align="center"> 学生列表</h2> <a href="/student/edit">添加学生</a> <table border="1" width="50%" cellpadding="0" cellspacing="0" align="center"> <tr style="background-color: orange;"> <th>编号</th> <th>姓名</th> <th>年龄</th> <th>操作</th> </tr> <c:forEach items="${students}" var="s" varStatus="vs"> <tr style='background-color: ${vs.count %2==0 ?"LavenderBlush":"gray"};'> <td>${s.id }</td> <td>${s.name }</td> <td>${s.age }</td> <td> <a href="/student/delete?id=${s.id}">删除</a> | <a href="/student/edit?id=${s.id}">编辑</a> </td> </tr> </c:forEach> </table>
删除操作传输一个id值，/student/delete?id=${s.id}删除指定的一条数据。然后返回到原来的界面
@WebServlet("/student/delete")
public class DeleteStudentServlet extends HttpServlet{
private static final long serialVersionUID= 1L;
private IStudentDao dao;
@Override
public void init() throws ServletException {
dao=new IStudentDaoImpl();
}
@Override
protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//1.接收请求参数封装成对象
Long id = Long.valueOf(req.getParameter("id"));
//2.调用业务方法处理请求
dao.delete(id);
//3.控制界面跳转
resp.sendRedirect("/student/list");
}
}
增加和删除操作的区别在于传输的数据是否有id值，需要进行判断，当传输的数据没有id的时候，就进行增加操作
@WebServlet("/student/edit")
public class EditStudentServlet extends HttpServlet{
private static final long serialVersionUID=1L;
private IStudentDao dao;
@Override
public void init() throws ServletException {
dao=new IStudentDaoImpl();
}
protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//1.接收请求参数封装成对象
String sid = req.getParameter("id");
//2.调用业务方法处理请求
if (hasLength(sid)) {
Student stu = dao.get(Long.valueOf(sid));
req.setAttribute("student", stu);//传递给edit.jsp,用于回显被编辑的学生。
}
//3.控制界面跳转
req.getRequestDispatcher("/WEB-INF/view/student/edit.jsp").forward(req, resp);
}
private boolean hasLength(String str){
return str!=null &&!"".equals(str.trim());
}
}
edit.jsp
<form action="/student/save" method="post"> <input type="hidden" name="id" value="${student.id }"> 姓名<input type="text" name="name" required value="${student.name }"/><br/> 年纪<input type="number" name="age" required value="${student.age }"><br/> <input type="submit" value="${student==null ?"保存学生信息":"修改学生信息"}"> </form>
根据id值有无判断进行什么操作
@WebServlet("/student/save")
//保存学生信息
public class SaveStudentServlet extends HttpServlet{
private static final long serialVersionUID =1L;
private IStudentDao dao;
@Override
public void init() throws ServletException {
dao = new IStudentDaoImpl();
}
protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//1.接收请求参数封装成对象
req.setCharacterEncoding("UTF-8");
String name = req.getParameter("name");
Integer age = Integer.valueOf(req.getParameter("age"));
Student stu = new Student(name, Integer.valueOf(age));
//2.调用业务方法处理请求
String id = req.getParameter("id");
if (hasLength(id)) {//更新保存
stu.setId(Long.valueOf(id));
dao.update(stu);
}else { //新增
dao.save(stu);
}
//3.控制界面跳转
resp.sendRedirect("/student/list");
}
private boolean hasLength(String str){
return str!=null &&!"".equals(str.trim());
}
}
以上就可以进行简单的增删改查操作
