![87e0fd540c16bd1ff006c3a37f77ddcb.png](https://i-blog.csdnimg.cn/blog_migrate/e77f6668959cf891546ccbd4be77cd15.jpeg)
前言:
通过本项目:
1.初步了解了三层架构
2.学习到了c3p0数据库连接池
3.掌握了功能实现思路:
- 显示输出数据库内容:执行servlet,向service层拿数据,service向dao层拿数据,dao执行SQL并返回给servlet数据,servlet将数据转发到jsp
- 修改数据库内容:
jsp将修改的定位参数送到servlet,servlet将定位参数给service层,service给dao层,dao层根据定位参数去拿数据再给servlet,servlet将数据转发给修改的jsp页面
;修改的jsp页面接收用户修改,将修改信息发送到servlet(这里的修改的servlet可以整合在一起,通过url参数来判断是执行修改前的查询功能还是修改后的执行修改功能功能)
将接收到的servlet信息发送到service层,将service层获取信息发送给dao层,dao层执行SQL语句
- 增加功能:将jsp页面获取的数据给servlet,再给service再给dao层,dao层执行增加语句
- 删除功能:将jsp页面获取的定位数据给servlet,再给service再给dao层,dao层执行删除语句
小结:QueryRunner的update方法返回int;query方法返回集合
- 分页
- 建立分页的类
private int currentPage; //当前页
private int totalPage; //总页数
private int pageSize; //每页记录数
private int totalSize; //总的记录数
private List<T> list; //当前页的学生集合
- 将jsp当前页码通过servlet传递给service再给dao层,dao层返回根据当前页查询的信息将查询的信息装到分页类中返给servlet,servlet将数据装入域对象中将数据转给jsp,jsp通过域对象获取信息。
总条数:通过 SQL 语句查询得来的——totalCount
总页数:totalPage= 总条数 % 页面大小(每页条数) == 0 ? 总条数 / 页面大小 : 总条数 / 页面大小 + 1
上一页:prePage= 当前页 - 1 > = 1 ? 当前页 - 1 : 1
下一页:nextPage= 当前页 + 1 <= totalPage ? 当前页 + 1 : totalPage
尾页:lastPage = 总条数 % 页面大小 == 0 ? 总条数 - 页面大小 : 总条数 - 总条数 % 页面大小
- 模糊查询:
通过service实现对多条件查询各条件的判断传递不同值给dao层,以返回不同的结果
一、准备阶段,附上练习代码;CSDN
(一、)认识到了三层架构分为:
①表现层:采用MVC模式
M称为模型,也就是实体类,用于数据的封装与数据的传输;
V为视图,也就是GUI组件,用于数据的展示;
C为控制,也就是事件,用于流程的控制 。
② 业务层:将一个业务中所有的操作封装成一个方法,同时保证方法中所有的数据库更新操作,即保证同时成功或同时失败。避免部分成功、部分失败引起的数据混乱操作
③ 持久层:采用DAO模式,建立实体类和数据库表映射。也就是哪个类对应哪个表,哪个属性对应哪个列。目的就是,完成对象数据和关系数据的转换。
![5b5df95418b361cae2d8134455ed46cb.png](https://i-blog.csdnimg.cn/blog_migrate/4bf905fdc99589c37fa7305e40e0d942.jpeg)
![a0c8e65e59647e00fc3f684641e33eea.png](https://i-blog.csdnimg.cn/blog_migrate/8e0953238079e9109c7985f98b1d2638.jpeg)
(二、)按照项目所需建立数据库表,以及将前端代码导入项目中,改为jsp文件
![420c7cbf2a3c34e4dc0d6ee31fce2892.png](https://i-blog.csdnimg.cn/blog_migrate/f0e1dbe92e4926477f7a41bceda4b291.jpeg)
本项目使用的前端代码为:
add.jsp:
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<%
String basePath = request.getScheme()+":"+"//"+request.getServerName()+":"+request.getServerPort()
+request.getServletContext().getContextPath()+"/";
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>添加学生</title>
</head>
<body>
<form action="<%=basePath%>addSerlet" method="post">
<table border="1" align="center" width="400">
<tr>
<td>姓名</td>
<td>
<input type="text" name="sname">
</td>
</tr>
<tr>
<td>性别</td>
<td>
<input type="radio" name="ssex" value="男">男
<input type="radio" name="ssex" value="女">女
</td>
</tr>
<tr>
<td>年龄</td>
<td>
<input type="text" name="sage">
</td>
</tr>
<tr>
<td>爱好</td>
<td>
<input type="checkbox" name="hobby" value="足球">足球
<input type="checkbox" name="hobby" value="篮球">篮球
<input type="checkbox" name="hobby" value="游泳">游泳
<input type="checkbox" name="hobby" value="旅行">旅行
<input type="checkbox" name="hobby" value="泡妞">泡妞
</td>
</tr>
<tr>
<td>出生年月</td>
<td>
<input type="text" name="birthday">
</td>
</tr>
</table>
<!-- submit自动提交到servlet -->
<center><input type="submit" value="添加"></center>
</form>
</body>
</html>
edit.jsp:
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions" %>
<%
String basePath = request.getScheme()+":"+"//"+request.getServerName()+":"+request.getServerPort()
+request.getServletContext().getContextPath()+"/";
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>修改学生</title>
</head>
<body>
<form action="<%=basePath%>modServlet?&type=m&sid=${stuList[0].sid}" method="post">
<table border="1" align="center" width="400">
<tr>
<td>姓名</td>
<td>
<input type="text" name="sname" value="${stuList[0].sname}">
</td>
</tr>
<tr>
<td>性别</td>
<td>
<input type="radio" name="ssex" value="男" ${stuList[0].ssex eq "男" ?'checked="true"':''}>男
<input type="radio" name="ssex" value="女" ${stuList[0].ssex eq "女" ?'checked="true"':''}>女
</td>
</tr>
<tr>
<td>年龄</td>
<td>
<input type="text" value="${stuList[0].sage}" name="sage">
</td>
</tr>
<tr>
<td>爱好</td>
<td>
<input name="hobby" type="checkbox" value="足球" ${stuList[0].hobby.indexOf("足球") !=-1 ?'checked="true"':''}>足球
<input name="hobby" type="checkbox" value="篮球" ${stuList[0].hobby.indexOf("篮球") !=-1 ?'checked="true"':''}>篮球
<input name="hobby" type="checkbox" value="游泳" ${stuList[0].hobby.indexOf("游泳") !=-1 ?'checked="true"':''}>游泳
<input name="hobby" type="checkbox" value="旅行" ${stuList[0].hobby.indexOf("旅行") !=-1 ?'checked="true"':''}>旅行
<input name="hobby" type="checkbox" value="泡妞" ${stuList[0].hobby.indexOf("泡妞") !=-1 ?'checked="true"':''}>泡妞
</td>
</tr>
<tr>
<td>出生年月</td>
<td>
<input name="birthday" type="text" value="${stuList[0].birthday}">
</td>
</tr>
</table>
<center><input type="submit" value="修改"></center>
</form>
</body>
</html>
index.jsp:
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%
String basePath = request.getScheme()+":"+"//"+request.getServerName()+":"+request.getServerPort()
+request.getServletContext().getContextPath()+"/";
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>首页</title>
</head>
<body>
<center><h3>学生列表</h3></center>
<form action="<%=basePath%>findSerlet" method="post">
<table border="1" align="center" width="600">
<tr>
<td colspan="7">
<input type="text" name="fname">
<select name="fsex">
<option value="" >-请选择-</option>
<option value="男" >男</option>
<option value="女" >女</option>
</select>
<input type="submit" value="查询" name="sname"/>
<a href="<%=basePath%>pageServlet?currentPage=1">分页查询</a>
</td>
</tr>
<tr align="center">
<th>学号</th>
<th>姓名</th>
<th>性别</th>
<th>年龄</th>
<th>爱好</th>
<th>出生年月</th>
<th>操作</th>
</tr>
<c:forEach items="${stuList}" var="s">
<tr align="center">
<td>${s.sid}</td>
<td>${s.sname}</td>
<td>${s.ssex}</td>
<td>${s.sage}</td>
<td>${s.hobby}</td>
<td>${s.birthday}</td>
<td>
<a href="<%=basePath%>modServlet?sid=${s.sid}">修改</a>
<a href="<%=basePath%>delSerlet?sid=${s.sid}">删除</a>
</td>
</tr>
</c:forEach>
<tr>
<td colspan="6"><a href="add.jsp">添加</a></td>
<td></td>
</tr>
</table>
</form>
</body>
</html>
list_page.jsp
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%
String basePath = request.getScheme()+":"+"//"+request.getServerName()+":"+request.getServerPort()
+request.getServletContext().getContextPath()+"/";
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>分页查询</title>
</head>
<body>
<form action="<%=basePath%>findSerlet" method="post">
<table border="1" width="700" align="center">
<tr>
<td colspan="8">
<input type="text" name="fname">
<select name="fsex">
<option value="" >-请选择-</option>
<option value="男" >男</option>
<option value="女" >女</option>
</select>
<input type="submit" value="查询"/>
</td>
</tr>
<tr align="center">
<td>编号</td>
<td>姓名</td>
<td>性别</td>
<td>年龄</td>
<td>生日</td>
<td>爱好</td>
<td>操作</td>
</tr>
<c:forEach items="${pageBean.list}" var="s">
<tr align="center">
<td>${s.sid}</td>
<td>${s.sname}</td>
<td>${s.ssex}</td>
<td>${s.sage}</td>
<td>${s.birthday}</td>
<td>${s.hobby}</td>
<td>
<a href="<%=basePath%>modServlet?sid=${s.sid}">修改</a>
<a href="<%=basePath%>delSerlet?sid=${s.sid}">删除</a>
</td>
</tr>
</c:forEach>
<tr>
<td colspan="8">
第 ${pageBean.currentPage} / ${pageBean.totalPage}
每页显示${pageBean.pageSize}条
总的记录数${pageBean.totalSize}
<c:if test="${pageBean.currentPage != 1 }">
<a href="<%=basePath%>pageServlet?currentPage=1">首页</a>
<a href="<%=basePath%>pageServlet?currentPage=${pageBean.currentPage-1}">上一页</a>
</c:if>
<c:forEach begin="1" end="${pageBean.totalPage}" var="i">
<c:if test="${pageBean.currentPage == i }">
${i}
</c:if>
<c:if test="${pageBean.currentPage != i }">
<a href="<%=basePath%>pageServlet?currentPage=${i}">${i}</a>
</c:if>
</c:forEach>
<c:if test="${pageBean.currentPage != pageBean.totalPage}">
<a href="<%=basePath%>pageServlet?currentPage=${pageBean.currentPage+1}">下一页</a>
<a href="<%=basePath%>pageServlet?currentPage=${pageBean.totalPage}">尾页</a>
</c:if>
</td>
</tr>
</table>
</form>
</body>
</html>
二、构建过程
(一、)在表现层M创建实体(要和数据库相对应)
1、创建学生类,构造方法中最好使用setSname(sname);这种方法去替代this.sname的写法,这样写安全性高且代码逻辑好写
package com.entity;
import java.util.Date;
/**
* @author xican.leo
* @date 2019年4月26日
* @version 1.0
*/
public class Student {
private int sid;
private String sname;
private String ssex;
private int sage;
private String hobby;
private Date birthday;
public int getSid() {
return sid;
}
public void setSid(int sid) {
this.sid = sid;
}
public Student(int sid, String sname, String ssex, int sage, String hobby, Date birthday) {
super();
setSid(sid);
setSname(sname);
setSsex(ssex);
setSage(sage);
setHobby(hobby);
setBirthday(birthday);
}
public Student(String sname, String ssex, int sage, String hobby, Date birthday) { //为修改功能提供构造方法
super();
setSname(sname);
setSsex(ssex);
setSage(sage);
setHobby(hobby);
setBirthday(birthday);
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public String getSsex() {
return ssex;
}
public void setSsex(String ssex) {
this.ssex = ssex;
}
public int getSage() {
return sage;
}
public void setSage(int sage) {
this.sage = sage;
}
public String getHobby() {
return hobby;
}
public void setHobby(String hobby) {
this.hobby = hobby;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
@Override
public String toString() {
return "Student [sid=" + sid + ", sname=" + sname + ", ssex=" + ssex + ", sage=" + sage + ", hobby=" + hobby
+ ", birthday=" + birthday + "]";
}
public Student() {
super();
}
}
2、创建分页类(为分页做准备)
package com.entity;
import java.util.List;
/**
* @author xican.leo
* @date 2019年4月26日
* @version 1.0
* @param <T>
*/
public class PageBean<T> {
private int currentPage; //当前页
private int totalPage; //总页数
private int pageSize; //每页记录数
private int totalSize; //总的记录数
private List<T> list; //当前页的学生集合
public int getCurrentPage() {
return currentPage;
}
public void setCurrentPage(int currentPage) {
this.currentPage = currentPage;
}
public int getTotalPage() {
return totalPage;
}
public void setTotalPage(int totalPage) {
this.totalPage = totalPage;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public int getTotalSize() {
return totalSize;
}
public void setTotalSize(int totalSize) {
this.totalSize = totalSize;
}
public List<T> getList() {
return list;
}
public void setList(List<T> list) {
this.list = list;
}
public PageBean() {
super();
}
public PageBean(int currentPage, int totalPage, int pageSize, int totalSize, List<T> list) {
super();
this.currentPage = currentPage;
this.totalPage = totalPage;
this.pageSize = pageSize;
this.totalSize = totalSize;
this.list = list;
}
@Override
public String toString() {
return "PageStu [currentPage=" + currentPage + ", totalPage=" + totalPage + ", pageSize=" + pageSize
+ ", totalSize=" + totalSize + ", list=" + list + "]";
}
}
(二、)在表现层C完成控制代码:
1、创建一个用于显示学生信息到index.jsp的servlet
package com.servlet;
import java.io.IOException;
import java.sql.SQLException;
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 com.entity.Student;
import com.service.StudentService;
import com.service.impl.StudentServiceImpl;
/**
* @author xican.leo
* @date 2019年4月26日
* @version 1.0
*/
/*
* 查找学生信息,返回集合给index.jsp
*/
@WebServlet("/StudentShowSerlet")
public class StudentShowSerlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
try {
//1.去service层拿学生的集合
StudentService ss = new StudentServiceImpl();
List<Student> findAll = ss.findAll();
/*
for (Student student : findAll) {
System.out.println(student);
}
*/
//2.将集合保存在域对象
request.setAttribute("stuList", findAll);
//3.请求转发到index.jsp
request.getRequestDispatcher("/index.jsp").forward(request, response);
} catch (SQLException e) {
e.printStackTrace();
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
2、创建一个用于增加学生信息到数据库的servlet
package com.servlet;
import java.io.IOException;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
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 com.entity.Student;
import com.mchange.v1.util.ArrayUtils;
import com.service.StudentService;
import com.service.impl.StudentServiceImpl;
/**
* @author xican.leo
* @date 2019年4月26日
* @version 1.0
*/
@WebServlet("/addSerlet")
public class addSerlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
try {
//设置字符集
request.setCharacterEncoding("utf-8");
//获取信息
String sname = request.getParameter("sname");
String ssex = request.getParameter("ssex");
String age = request.getParameter("sage");
int sage = Integer.parseInt(age);
String[] hobby = request.getParameterValues("hobby");
String shobby = ArrayUtils.toString(hobby);//将数组转为字符串
shobby = shobby.substring(1,shobby.length()-1);//截取字符串
String birthday = request.getParameter("birthday");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");//创建日期模板
Date sbirthday = null;
sbirthday = sdf.parse(birthday);
//将数据放入学生对象中
Student stu = new Student(sname,ssex,sage,shobby,sbirthday);
StudentService ss = new StudentServiceImpl();
ss.inserStu(stu);
request.getRequestDispatcher("/StudentShowSerlet").forward(request,response);
// System.out.println(sname+" "+ssex+" "+sage+" "+shobby+" "+sbirthday);
//灏嗘暟鎹粰service灞�
//椤甸潰杞烦
} catch (ParseException | SQLException e) {
e.printStackTrace();
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
3、创建一个用于从数据库中删除学生信息的servlet
package com.servlet;
import java.io.IOException;
import java.sql.SQLException;
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 com.service.impl.StudentServiceImpl;
/**
* @author xican.leo
* @date 2019年4月26日
* @version 1.0
*/
@WebServlet("/delSerlet")
public class delSerlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
try {
//获取id
String id=request.getParameter("sid");
int sid = Integer.parseInt(id);
//向service层走
StudentServiceImpl ss = new StudentServiceImpl();
ss.delStuById(sid);
//转发:
request.getRequestDispatcher("/StudentShowSerlet").forward(request,response);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
4.创建一个用于在数据库中修改学生信息的servlet
package com.servlet;
import java.io.IOException;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
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 com.entity.Student;
import com.mchange.v1.util.ArrayUtils;
import com.service.StudentService;
import com.service.impl.StudentServiceImpl;
/**
* @author xican.leo
* @date 2019年4月26日
* @version 1.0
*/
@WebServlet("/modServlet")
public class modServlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//设置字符集
request.setCharacterEncoding("utf-8");
String type=request.getParameter("type");
if(type!=null &&type.equals("m")){
try {
//获取信息
int sid = Integer.parseInt(request.getParameter("sid"));
String sname = request.getParameter("sname");
String ssex = request.getParameter("ssex");
String age = request.getParameter("sage");
int sage = Integer.parseInt(age);
String[] hobby = request.getParameterValues("hobby");
String shobby="无";
if(hobby !=null){
shobby = ArrayUtils.toString(hobby);//将数组转为字符串
shobby = shobby.substring(1,shobby.length()-1);//截取字符串
}
String birthday = request.getParameter("birthday");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");//创建日期模板
Date sbirthday = null;
sbirthday = sdf.parse(birthday);
System.out.println(ssex+sage+shobby+sbirthday);
//将数据放入学生对象中
Student stu = new Student(sid,sname,ssex,sage,shobby,sbirthday);
StudentService ss = new StudentServiceImpl();
ss.modStu(stu);
//转发
request.getRequestDispatcher("/StudentShowSerlet").forward(request,response);
} catch (ParseException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}else{
//修改前查询将值传递给jsp
try {
//获取id
String id=request.getParameter("sid");
int sid = Integer.parseInt(id);
//向service层走
StudentServiceImpl ss = new StudentServiceImpl();
List<Student> findStuInfo =ss.findStuInfo(sid);
//2.将集合保存在域对象
request.setAttribute("stuList", findStuInfo);
//3.请求转发到edit.jsp
request.getRequestDispatcher("/edit.jsp").forward(request, response);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
5、创建在数据按照条件进行查询的servlet
package com.servlet;
import java.io.IOException;
import java.sql.SQLException;
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 com.entity.Student;
import com.service.StudentService;
import com.service.impl.StudentServiceImpl;
/**
* @author xican.leo
* @date 2019年4月26日
* @version 1.0
*/
@WebServlet("/findSerlet")
public class findSerlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
try {
//设置字符集
request.setCharacterEncoding("utf-8");
//获取sname查询
String sname = request.getParameter("fname");
String ssex = request.getParameter("fsex");
//实例化server层
StudentService ss = new StudentServiceImpl();
List<Student> findStuInfo=null;
findStuInfo =ss.findStuByQuery(sname,ssex);
//2.将集合保存在域对象
request.setAttribute("stuList", findStuInfo);
request.getRequestDispatcher("/index.jsp").forward(request,response);
} catch (SQLException e) {
e.printStackTrace();
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
6、创建一个用于分页查询的servlet
package com.servlet;
import java.io.IOException;
import java.sql.SQLException;
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 com.entity.PageBean;
import com.entity.Student;
import com.service.StudentService;
import com.service.impl.StudentServiceImpl;
/**
* @author xican.leo
* @date 2019年4月26日
* @version 1.0
*/
@WebServlet("/pageServlet")
public class pageServlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
try {
//获取当前页
int currentPage = Integer.parseInt(request.getParameter("currentPage"));
//根据当前页去service层分页查询
StudentService ss = new StudentServiceImpl();
PageBean pageBean = ss.selectByPage(currentPage);
//查询的结果保存到域对象
request.setAttribute("pageBean", pageBean);
//发送到分页的页面
request.getRequestDispatcher("/list_page.jsp").forward(request, response);
} catch (SQLException e) {
e.printStackTrace();
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
}
三、在业务逻辑写service代码:
(一、)创建一个service接口
package com.service;
import java.sql.SQLException;
import java.util.List;
import com.entity.PageBean;
import com.entity.Student;
/**
* @author xican.leo
* @date 2019年4月26日
* @version 1.0
*/
public interface StudentService {
//查询所有学生
public List<Student> findAll() throws SQLException;
//添加学生
public void inserStu(Student stu) throws SQLException;
//删除学生
public void delStuById(int sid) throws SQLException;
//修改学生
//执行查询
public List<Student> findStuInfo(int sid) throws SQLException;
//执行修改
public void modStu(Student stu) throws SQLException;
//查询学生
public List<Student> findStuByQuery(String sname, String ssex)throws SQLException;
//分页查询
public PageBean selectByPage(int currentPage)throws SQLException;
}
(二、)创建一个service接口的实现类
package com.service.impl;
import java.sql.SQLException;
import java.util.List;
import com.dao.StudentDao;
import com.dao.impl.StudentDaoImpl;
import com.entity.PageBean;
import com.entity.Student;
import com.service.StudentService;
/**
* @author xican.leo
* @date 2019年4月26日
* @version 1.0
*/
public class StudentServiceImpl implements StudentService {
@Override
public List<Student> findAll() throws SQLException {
//找dao层拿学生数据
StudentDao sd = new StudentDaoImpl();
return sd.findAll();
}
@Override
public void inserStu(Student stu) throws SQLException {
StudentDao sd = new StudentDaoImpl();
sd.inserStu(stu);
}
@Override
public void delStuById(int sid) throws SQLException {
StudentDao sd = new StudentDaoImpl();
sd.delStu(sid);
}
@Override
public List<Student> findStuInfo(int sid) throws SQLException {
StudentDao sd = new StudentDaoImpl();
return sd.findStuInfo(sid);
}
@Override
public void modStu(Student stu) throws SQLException {
StudentDao sd = new StudentDaoImpl();
sd.modStu(stu);
}
@Override
public List<Student> findStuByQuery(String sname, String ssex) throws SQLException {
StudentDao sd = new StudentDaoImpl();
List<Student> str=null;
if(sname == null || sname == "") {//sname为空
if(ssex == null || ssex== ""){//sex为空
str=sd.findStuByQuery('%' + "%" + '%','%' + ssex + '%');
}else {//sex不为空
str=sd.findStuByQuery('%' + "%" + '%','%' + ssex + '%');
}
}else {//sname不为空
if(ssex == null || ssex== ""){//sex为空
str=sd.findStuByQuery('%' + sname + '%','%' + "%" + '%');
}else {//sex不为空
str=sd.findStuByQuery('%' + sname + '%','%' + ssex + '%');
}
}
return str;
}
@Override
public PageBean selectByPage(int currentPage) throws SQLException {
PageBean pageBean = new PageBean();
//当前页
pageBean.setCurrentPage(currentPage);
// pageSize每页记录数
pageBean.setPageSize(StudentDao.pageSize);
// totalSize总的记录数
StudentDao sd = new StudentDaoImpl();
int totalSize = sd.count();
pageBean.setTotalSize(totalSize);
//计算总页数
int totalPage = totalSize % StudentDao.pageSize == 0 ? totalSize / StudentDao.pageSize : (totalSize / StudentDao.pageSize + 1);
pageBean.setTotalPage(totalPage);
//学生集合
List<Student> list = sd.selectByPage(currentPage);
pageBean.setList(list);
return pageBean;
}
}
四、写持久层前使用C3p0数据库连接工具
(一、)创建的工具类:
package com.util;
import java.sql.Connection;
import java.sql.SQLException;
import org.apache.commons.dbutils.QueryRunner;
import com.mchange.v2.c3p0.ComboPooledDataSource;
/**
* @author xican.leo
* @date 2019年4月26日
* @version 1.0
*/
public class C3p0UtilsConfig {
//这个类是自己写的工具类在这里创建了一个连接池对象所有的连接都存在对象里这个对象就是连接池
private static ComboPooledDataSource dataSource = null;
static {
//创建一个连接池这里的创建的时候在构造函数里传入一个连接池名字它会自动的根据名字去找配置文件
dataSource = new ComboPooledDataSource("c3p0-config");
}
public static ComboPooledDataSource getDataSource(){
//返回这个连接池
return dataSource;
}
/**
*
* @return 返回一个从数据库里拿出来的连接
* @throws SQLException
*/
public static Connection getConnection() throws SQLException {
//返回一条连接
return dataSource.getConnection();
}
// public static void main(String[] args) throws SQLException {
// //测试数据库连接
// QueryRunner qr = new QueryRunner(C3p0UtilsConfig.getDataSource());
// qr.update("update stu set sname = ? where sid = 1","灿");
// }
}
(二、)在持久层写连接数据库相关的代码:
1、创建一个dao层的接口类
package com.dao;
import java.sql.SQLException;
import java.util.List;
import com.entity.Student;
/**
* @author xican.leo
* @date 2019年4月26日
* @version 1.0
*/
public interface StudentDao {
int pageSize = 5;
public List<Student> findAll() throws SQLException;
public void inserStu(Student stu) throws SQLException;
public void delStu(int sid)throws SQLException;
public List<Student> findStuInfo(int sid)throws SQLException;
public void modStu(Student stu)throws SQLException;
public List<Student> findStuByQuery(String sname, String ssex) throws SQLException;
public int count() throws SQLException;
public List<Student> selectByPage(int currentPage) throws SQLException;
}
2、创建dao层的实现类
package com.dao.impl;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import com.dao.StudentDao;
import com.entity.Student;
import com.util.C3p0UtilsConfig;
/**
* @author xican.leo
* @date 2019年4月26日
* @version 1.0
*/
public class StudentDaoImpl implements StudentDao {
@Override
public List<Student> findAll() throws SQLException {
QueryRunner qr = new QueryRunner(C3p0UtilsConfig.getDataSource());
return qr.query("SELECT * FROM student",
new BeanListHandler<Student>(Student.class));
}
@Override
public void inserStu(Student stu) throws SQLException {
// 添加学生
QueryRunner qr = new QueryRunner(C3p0UtilsConfig.getDataSource());
qr.update(
"insert into student values(null,?,?,?,?,?)",
stu.getSname(),
stu.getSsex(),
stu.getSage(),
stu.getHobby(),
stu.getBirthday()
);
}
@Override
public void delStu(int sid) throws SQLException {
QueryRunner qr = new QueryRunner(C3p0UtilsConfig.getDataSource());
qr.update("delete from student where sid=?",sid);
}
@Override
public List<Student> findStuInfo(int sid) throws SQLException {
QueryRunner qr = new QueryRunner(C3p0UtilsConfig.getDataSource());
String sql = "select * from student where sid=?";
//将三个?占位符的实际参数,写在数组中
int params = sid;
return qr.query(sql,new BeanListHandler<Student>(Student.class),params);
}
@Override
public void modStu(Student stu) throws SQLException {
// 修改学生
QueryRunner qr = new QueryRunner(C3p0UtilsConfig.getDataSource());
qr.update(
"update student set sname=?,ssex=?,sage=?,hobby=?,birthday=? WHERE sid=?",
stu.getSname(),
stu.getSsex(),
stu.getSage(),
stu.getHobby(),
stu.getBirthday(),
stu.getSid()
);
}
@Override
public List<Student> findStuByQuery(String sname,String ssex) throws SQLException {
QueryRunner qr = new QueryRunner(C3p0UtilsConfig.getDataSource());
return qr.query("SELECT * FROM `student` where sname like ? and ssex like ?",
new BeanListHandler<Student>(Student.class),sname,ssex);
}
//拿到数据库中的总记录数,返回给service层
@Override
public int count() throws SQLException {
QueryRunner qr = new QueryRunner(C3p0UtilsConfig.getDataSource());
Long lg = qr.query("select count(*) from student", new ScalarHandler<Long>());//ScalarHandler返回的是Long不能是int
return lg.intValue();
}
//通过规定的页面显示条数进行分页查询,使用limit关键字,
@Override
public List<Student> selectByPage(int currentPage) throws SQLException {
QueryRunner qr = new QueryRunner(C3p0UtilsConfig.getDataSource());
return qr.query("SELECT * FROM student LIMIT ?,?",
new BeanListHandler<Student>(Student.class),
(currentPage-1)*pageSize,
pageSize);
}
}