项目技术
使用JSP+Servlet+JavaBean实现表的CRUD。
使用MVC三层模式进行两张表的增删改查操作，Dao层使用C3P0+DBUtils工具类，实现了简单的多条件分页查询。
项目源码：tour_curd.zip
项目结构
数据库表结构
旅游表
城市表
项目设置
所需Jar包
c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="user">root</property>
<property name="password">root123</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/hpbbs?useUnicode=true&characterEncoding=UTF-8</property>
<property name="driverClass">com.mysql.jdbc.Driver</property>
</default-config>
<!-- 连接池参数 -->
<!-- 初始连接数 -->
<property name="initialPoolSize">5</property>
<!-- 最大连接数 -->
<property name="maxPoolSize">10</property>
<!-- 最大等待时间 -->
<property name="checkoutTimeout">2000</property>
<!-- 最大空闲回收时间 -->
<property name="maxIdleTime">1000</property>
</c3p0-config>
JDBCUtils工具类
package com.coydone.utils;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
public class JDBCUtils {
private static DataSource dataSource = new ComboPooledDataSource();
private JDBCUtils(){
}
public static DataSource getDataSource(){
return dataSource;
}
public static Connection getConnection(){
Connection connection =null;
try {
connection = dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
}
数据访问层（Dao层）
先创建数据库实体类City和Tour，在Tour类中添加与City表的主外键关系。
//添加表关系
private City city;
//设置相应的getter()和setter()方法
显示多条件分页查询，需要从JSP页面传递的参数有：cityId（根据cityId的值显示cityName的值），价格的最小值min和最大值max，分页的当前页pageNo和每页显示的数量pageSize。在处理分页业务时，我们需要条件查询数据库满足条件的总记录数，这样便于计算分页的总页数等分页信息。
进行增删改操作时，我们只需要返回数据库中受影响的行数即可，其值大于1则说明操作成功，否则操作失败。
TourDao接口
package com.coydone.dao;
import com.coydone.domain.Tour;
import java.util.List;
public interface TourDao {
//多条件分页查询
List<Tour> findAllByLimit(Integer cityId,String min,String max,Integer pageNo,Integer pageSize);
//多条件查询总记录数
Integer findTotalSize(Integer cityId,String min,String max);
//通过id删除
Integer deleteById(Integer id);
//添加
Integer add(Tour tour);
//通过id修改
Integer updateById(Tour tour);
//根据id查询
Tour findById(Integer id);
}
TourDao实现类TourDaoImpl
package com.coydone.dao.impl;
import com.coydone.dao.TourDao;
import com.coydone.domain.City;
import com.coydone.domain.Tour;
import com.coydone.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import java.sql.SQLException;
import java.util.List;
public class TourDaoImpl implements TourDao {
private QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());
@Override
public List<Tour> findAllByLimit(Integer cityId, String min, String max, Integer pageNo,Integer pageSize) {
//因为要显示两张表中的数据
StringBuffer tourSql = new StringBuffer("select * from toursinfo where 1=1 ");
if (cityId != null && !cityId.equals(0)){
tourSql.append(" and cityId ="+cityId);
}
if (min != null && !min.trim().equals("")){
tourSql.append(" and price >= "+min);
}
if (max != null && !max.trim().equals("")){
tourSql.append(" and price <= "+max);
}
//添加分页
tourSql.append(" limit ?,?");
List<Tour> tours = null;
try {
tours = qr.query(tourSql.toString(), new BeanListHandler<Tour>(Tour.class),(pageNo-1)*pageSize,pageSize);
} catch (SQLException e) {
e.printStackTrace();
}
//把每个员工对应的部门信息查询出来
for (Tour tour : tours) {
String citySql = "select * from city where cityId = ?";
City city = null;
try {
city = qr.query(citySql, new BeanHandler<City>(City.class), tour.getCityId());
} catch (SQLException e) {
e.printStackTrace();
}
tour.setCity(city);
}
return tours;
}
@Override
public Integer findTotalSize(Integer cityId, String min, String max){
StringBuffer sql = new StringBuffer("select count(0) from toursinfo where 1=1 ");
if (cityId != null && !cityId.equals(0)){
sql.append(" and cityId ="+cityId);
}
if (min != null && !min.trim().equals("")){
sql.append(" and price >= "+min);
}
if (max != null && !max.trim().equals("")){
sql.append(" and price <= "+max);
}
Long counts = null;
try {
counts = qr.query(sql.toString(), new ScalarHandler<Long>("count(0)"));
} catch (SQLException e) {
e.printStackTrace();
}
return counts.intValue();
}
@Override
public Integer deleteById(Integer id) {
String sql = " delete from toursinfo where id = ? ";
Integer rows = null;
try {
rows = qr.update(sql, id);
} catch (SQLException e) {
e.printStackTrace();
}
return rows;
}
@Override
public Integer add(Tour tour) {
String sql = " insert into toursinfo(introduce,pubTime,price,cityId) values(?,?,?,?) ";
Integer rows = null;
try {
rows = qr.update(sql, tour.getIntroduce(),tour.getPubTime(),tour.getPrice(),tour.getCityId());
} catch (SQLException e) {
e.printStackTrace();
}
return rows;
}
@Override
public Integer updateById(Tour tour) {
String sql = " update toursinfo set introduce = ?,pubTime = ?,price = ?,cityId = ? where id = ?";
Integer rows = null;
try {
rows = qr.update(sql,tour.getIntroduce(),tour.getPubTime(),tour.getPrice(),tour.getCityId(),tour.getId());
} catch (SQLException e) {
e.printStackTrace();
}
return rows;
}
@Override
public Tour findById(Integer id) {
String sql = " select * from toursinfo where id = ? ";
Tour tour = null;
try {
tour = qr.query(sql, new BeanHandler<>(Tour.class), id);
} catch (SQLException e) {
e.printStackTrace();
}
String citysql = "select * from city where cityId ="+tour.getCityId();
City city = null;
try {
city = qr.query(citysql, new BeanHandler<>(City.class));
} catch (SQLException e) {
e.printStackTrace();
}
tour.setCity(city);
return tour;
}
}
业务逻辑层（Service层）
只有分页查询有相关的业务操作，其它的增删改只是在service中调用到层的方法。
进行分页查询的业务时，我们还需要将分页的参数和查询的结果进行封装，形成一个分页的工具类。
PageUtils
package com.coydone.utils;
import java.util.List;
public class PageUtils<T> {
public static final Integer PAGE_SIZE = 4;
// 当前页码
private Integer pageNo;
// 总页码
private Integer pageTotal;
// 当前页显示数量
private Integer pageSize = PAGE_SIZE;
// 总记录数
private Integer totalSize;
// 当前页数据
private List<T> items;
public Integer getPageNo() {
return pageNo;
}
public void setPageNo(Integer pageNo) {
/* 数据边界的有效检查 */
if (pageNo < 1) {
pageNo = 1;
}
if (pageNo > pageTotal) {
pageNo = pageTotal;
}
this.pageNo = pageNo;
}
public Integer getPageTotal() {
return pageTotal;
}
public void setPageTotal(Integer pageTotal) {
this.pageTotal = pageTotal;
}
public Integer getPageSize() {
return pageSize;
}
public void setPageSize(Integer pageSize) {
this.pageSize = pageSize;
}
public Integer getTotalSize() {
return totalSize;
}
public void setTotalSize(Integer totalSize) {
this.totalSize = totalSize;
}
public List<T> getItems() {
return items;
}
public void setItems(List<T> items) {
this.items = items;
}
}
Service层分页查询的实现类
package com.coydone.service.impl;
import com.coydone.dao.TourDao;
import com.coydone.dao.impl.TourDaoImpl;
import com.coydone.domain.Tour;
import com.coydone.service.TourService;
import com.coydone.utils.PageUtils;
import java.util.List;
public class TourServiceImpl implements TourService {
private TourDao tourDao = new TourDaoImpl();
@Override
public PageUtils<Tour> findAllByLimit(Integer cityId, String min, String max, Integer pageNo) {
//将查询结果封装到PageUtils中
PageUtils<Tour> page = new PageUtils<>();
// 设置每页显示的数量
page.setPageSize(PageUtils.PAGE_SIZE);
// 求总记录数
Integer totalSize = tourDao.findTotalSize(cityId,min,max);
// 设置总记录数
page.setTotalSize(totalSize);
// 求总页码
Integer pageTotal = totalSize / PageUtils.PAGE_SIZE;
if (totalSize % PageUtils.PAGE_SIZE > 0) {
pageTotal+=1;
}
// 设置总页码
page.setPageTotal(pageTotal);
// 设置当前页码
page.setPageNo(pageNo);
// 求当前页数据
List<Tour> items = tourDao.findAllByLimit(cityId,min,max,pageNo,PageUtils.PAGE_SIZE);
// 设置当前页数据
page.setItems(items);
return page;
}
}
视图层（Servlet+JSP）
其中Servlet用于获取请求数据，调用Service层业务，转发到JSP页面进行数据的显示。
我们可以通过反射的方式封装Servlet，这样每一个业务对应一个方法，同一张表的操作对应一个请求，具体的业务用不同的请求参数区分，比如/tour为tour表中所有的业务请求，我们要实现查询功能，就让请求参数为list，然后在BaseServlet中根据这个请求参数去找到名为list()的方法，详细的请求在get请求中为/tour?action=list，在post请求中通过设置隐藏域的方式提交，<input type="hidden" name="action" value="list">。这个Servlet称为BaseServlet，它一定要继承HttpServlet。
BaseServlet
public class BaseServlet extends HttpServlet {
@Override
protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setCharacterEncoding("UTF-8");
resp.setContentType("text/html;charset=UTF-8");
String action = req.getParameter("action");
try {
Method method = this.getClass().getDeclaredMethod(action, HttpServletRequest.class, HttpServletResponse.class);
method.invoke(this,req,resp);
} catch (Exception e) {
e.printStackTrace();
}
}
}
Tour表的Servlet
package com.coydone.servlet;
import com.coydone.domain.City;
import com.coydone.domain.Tour;
import com.coydone.service.CityService;
import com.coydone.service.TourService;
import com.coydone.service.impl.CityServiceImpl;
import com.coydone.service.impl.TourServiceImpl;
import com.coydone.utils.PageUtils;
import com.coydone.utils.WebUtils;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
@WebServlet(urlPatterns = "/tour")
public class TourServlet extends BaseServlet {
private TourService tourService = new TourServiceImpl();
//多条件分页查询
public void list(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
Integer pageNo = WebUtils.parseInt(request.getParameter("pageNo"),1);
Integer cityId = WebUtils.parseInt(request.getParameter("cityId"),0);
String min = request.getParameter("min");
String max = request.getParameter("max");
PageUtils<Tour> pages = tourService.findAllByLimit(cityId, min, max, pageNo);
CityService cityService = new CityServiceImpl();
List<City> cities = cityService.findAll();
request.setAttribute("pages", pages);
request.getSession().setAttribute("cities",cities);
request.setAttribute("cityId", cityId);
request.setAttribute("min", min);
request.setAttribute("max", max);
request.getRequestDispatcher("/list.jsp").forward(request,response);
}
//添加
public void add(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String introduce = request.getParameter("introduce");
String price = request.getParameter("price");
String cityId = request.getParameter("cityId");
Date pubTime = null;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
try {
pubTime = sdf.parse(request.getParameter("pubTime"));
} catch (ParseException e) {
e.printStackTrace();
}
Tour tour = new Tour();
tour.setIntroduce(introduce);
tour.setPrice(Double.parseDouble(price));
tour.setCityId(Integer.parseInt(cityId));
tour.setPubTime(pubTime);
Integer rows = tourService.add(tour);
if (rows > 0) { //添加成功 返回到显示页面
response.getWriter().print("<script>alert('添加成功');location.href='"+request.getContextPath()+"/index.jsp'</script>");
} else { //添加失败 重新添加
response.getWriter().print("<script>alert('添加失败');location.href='/add.jsp'</script>");
}
}
//去修改页面
public void toUpdate(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
Integer id = Integer.parseInt(request.getParameter("id"));
Tour tour = tourService.findById(id);
request.setAttribute("tour",tour);
request.getRequestDispatcher("/update.jsp").forward(request,response);
}
//进行修改业务
public void update(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
Integer id = Integer.parseInt(request.getParameter("id"));
String introduce = request.getParameter("introduce");
String price = request.getParameter("price");
String cityId = request.getParameter("cityId");
Date pubTime = null;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
try {
pubTime = sdf.parse(request.getParameter("pubTime"));
} catch (ParseException e) {
e.printStackTrace();
}
Tour tour = new Tour();
tour.setId(id);
tour.setIntroduce(introduce);
tour.setPrice(Double.parseDouble(price));
tour.setCityId(Integer.parseInt(cityId));
tour.setPubTime(pubTime);
Integer rows = tourService.updateById(tour);
if (rows > 0) { //修改成功 返回到显示页面
response.getWriter().print("<script>alert('修改成功');location.href='"+request.getContextPath()+"/index.jsp'</script>");
} else { //修改失败 重新修改
response.getWriter().print("<script>alert('修改失败');location.href='/update.jsp'</script>");
}
}
//删除
public void delete(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
Integer id = Integer.parseInt(request.getParameter("id"));
Integer rows = tourService.deleteById(id);
if (rows > 0) { //删除成功 返回到显示页面
response.getWriter().print("<script>alert('删除成功');location.href='"+request.getContextPath()+"/index.jsp'</script>");
} else { //删除失败
response.getWriter().print("<script>alert('删除失败');location.href='/index.jsp'</script>");
}
}
//景点详情
public void view(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
Integer id = Integer.parseInt(request.getParameter("id"));
Tour tour = tourService.findById(id);
request.setAttribute("tour",tour);
request.getRequestDispatcher("/view.jsp").forward(request,response);
}
}
效果图
评论区