侧边栏壁纸
博主头像
coydone博主等级

记录学习,分享生活的个人站点

  • 累计撰写 306 篇文章
  • 累计创建 51 个标签
  • 累计收到 0 条评论

目 录CONTENT

文章目录

JavaWeb的CRUD案例

coydone
2022-02-13 / 0 评论 / 0 点赞 / 330 阅读 / 13,292 字 / 正在检测是否收录...
温馨提示:
本文最后更新于 2022-04-09,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。

项目技术

使用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&amp;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);

    }
}

效果图

0

评论区