java使用servlet进行增删改查(含源代码)

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/yuehailin/article/details/79057095

使用说明:

jdk:1.8

tomcat:1.9

数据库:mysql

资源下载链接:http://download.csdn.net/download/yuehailin/10205004

本篇博客使用servlet进行增删改查,c3p0连接数据库,下面是页面的效果展示。

主界面:


查询用户界面:

高级查询(模糊查询):

需要使用的jar包:

博客结构截图:


c3p0-config.xml

<?xml version="1.0" encoding="utf-8" ?>
<c3p0-config>
    <default-config>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/customer</property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <!--这里是你自己的连接数据库的用户名-->
        <property name="user">root</property>
        <!--这里是你自己的连接数据库的密码-->
        <property name="password">123</property>
        <property name="acquireIncrement">3</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">2</property>
        <property name="maxPoolSize">10</property>
    </default-config>
</c3p0-config>
CustomerDao.java

package dao;

import cn.itcast.jdbc.TxQueryRunner;
import domain.Customer;
import domain.PageBean;
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.util.ArrayList;
import java.util.List;


public class CustomerDao
{
    private QueryRunner qr=new TxQueryRunner();

    public void add(Customer c)
    {
        try {
            String sql = "insert into t_customer values(?,?,?,?,?,?)";
            Object[] params = {c.getId(), c.getName(), c.getGender(),
                    c.getPhone(), c.getEmail(), c.getDescription()};

            qr.update(sql, params);
        }catch (Exception e)
        {
            throw new RuntimeException(e);
        }
    }

//    public List<Customer> findAll()
//    {
//        try{
//            String sql="select * from t_customer";
//            return qr.query(sql,new BeanListHandler<Customer>(Customer.class));
//        }catch (Exception e)
//        {
//            throw new RuntimeException(e);
//        }
//    }
    public PageBean<Customer> findAll(int pc, int pr)
    {
        try{
            /*
             *1.他需要创建pageBean对象pb
             * 2.设置pb的pc和pr
             * 3.得到tr,设置给pb
             * 4.得到beanList设置给pb
             * 最后返回给pb
             */
            PageBean<Customer> pb=new PageBean<>();
            pb.setPc(pc);
            pb.setPr(pr);

            String sql="select count(*) from t_customer";
            Number number=(Number) qr.query(sql,new ScalarHandler<>());

            int tr=number.intValue();
            pb.setTr(tr);

            sql="select * from t_customer order by name limit ?,?";
            Object[] params={(pc-1)*pr,pr};
            List<Customer> beanList=qr.query(sql,new BeanListHandler<>(Customer.class),params);

            pb.setBeanList(beanList);

            return pb;
        }catch (Exception e)
        {
            throw new RuntimeException(e);
        }
    }

    public Customer find(String id)
    {
        try {
            String sql = "select * from t_customer where id=?";
            return qr.query(sql, new BeanHandler<Customer>(Customer.class), id);
        }catch (Exception e)
        {
            throw new RuntimeException(e);
        }
    }

    public void edit(Customer customer)
    {
        try{
            String sql="update t_customer set name=?,gender=?,phone=?,email=?,description=? where id=?";
            Object[] params={customer.getName(),customer.getGender(),customer.getPhone(),customer.getEmail(),customer.getDescription(),customer.getId()};

            qr.update(sql,params);
        }catch (Exception e)
        {
            throw new RuntimeException(e);
        }
    }

    public void delete(String id)
    {
        try {
            String sql = "delete from t_customer where id=?";

            qr.update(sql, id);
        }catch (Exception e)
        {
            throw new RuntimeException(e);
        }
    }

//    public List<Customer> query(Customer customer) {
//
//        try {
//            StringBuilder sql = new StringBuilder("select * from t_customer where 1=1 ");
//            List<Object> params = new ArrayList<>();
//
//            String name = customer.getName();
//            if (name != null && !name.trim().isEmpty()) {
//                sql.append("and name like ?");
//                params.add("%"+name+"%");
//            }
//
//            String gender = customer.getGender();
//            if (gender != null && !gender.trim().isEmpty()) {
//                sql.append("and gender=?");
//                params.add(gender);
//            }
//
//            String phone = customer.getPhone();
//            if (phone != null && !phone.trim().isEmpty()) {
//                sql.append("and phone like ?");
//                params.add("%"+phone+"%");
//            }
//
//            String email = customer.getEmail();
//            if (email != null && !email.trim().isEmpty()) {
//                sql.append("and email like ?");
//                params.add("%"+email+"%");
//            }
//
//            return qr.query(sql.toString(), new BeanListHandler<Customer>(Customer.class), params.toArray());
//        }catch (Exception e)
//        {
//            throw new RuntimeException(e);
//        }
//
//
//    }

    public PageBean<Customer> query(Customer customer,int pc,int pr) {


        try {
            PageBean<Customer> pb=new PageBean<>();
            pb.setPc(pc);
            pb.setPr(pr);

            StringBuilder cntSql = new StringBuilder("select count(*) from t_customer ");
            StringBuilder whereSql=new StringBuilder(" where 1=1 ");
            List<Object> params = new ArrayList<>();

            String name = customer.getName();
            if (name != null && !name.trim().isEmpty()) {
                whereSql.append("and name like ?");
                params.add("%"+name+"%");
            }

            String gender = customer.getGender();
            if (gender != null && !gender.trim().isEmpty()) {
                whereSql.append("and gender=?");
                params.add(gender);
            }

            String phone = customer.getPhone();
            if (phone != null && !phone.trim().isEmpty()) {
                whereSql.append("and phone like ?");
                params.add("%"+phone+"%");
            }

            String email = customer.getEmail();
            if (email != null && !email.trim().isEmpty()) {
                whereSql.append("and email like ?");
                params.add("%"+email+"%");
            }

            Number num=qr.query(cntSql.append(whereSql).toString(),new ScalarHandler<>(),params.toArray());

            int tr=num.intValue();
            pb.setTr(tr);

            StringBuilder sql=new StringBuilder("select * from t_customer ");
            StringBuilder lmitSql=new StringBuilder(" limit ?,?");

            params.add((pc-1)*pr);
            params.add(pr);

            List<Customer> beanList=qr.query(sql.append(whereSql).append(lmitSql).toString(),new BeanListHandler<Customer>(Customer.class),params.toArray());
            pb.setBeanList(beanList);

            return pb;
        }catch (Exception e)
        {
            throw new RuntimeException(e);
        }

    }
}
Customer

package domain;


public class Customer
{
    private String id;
    private String name;
    private String gender;
    private String phone;
    private String email;
    private String description;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }
}
PageBean

package domain;

import java.util.List;


public class PageBean<Object>
{
    private int pc;//当前页码page code
    //private int tp;//总页数total pages



    private int tr;//总纪录数tatal records
    private int pr;//每页纪录数page records
    private List<Object> beanList;//当前页的纪录
    private String url;

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public int getPc() {
        return pc;
    }

    public void setPc(int pc) {
        this.pc = pc;
    }

    public int getTp()
    {
        int tp=tr/pr;
        return tr % pr == 0 ? tp : tp + 1 ;
    }



    public int getTr() {
        return tr;
    }

    public void setTr(int tr) {
        this.tr = tr;
    }

    public int getPr() {
        return pr;
    }

    public void setPr(int pr) {
        this.pr = pr;
    }

    public List<Object> getBeanList() {
        return beanList;
    }

    public void setBeanList(List<Object> beanList) {
        this.beanList = beanList;
    }
}
CustomerServlet

package servlet;

import cn.itcast.commons.CommonUtils;
import cn.itcast.servlet.BaseServlet;
import domain.Customer;
import domain.PageBean;
import service.CustomerService;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;


public class CustomerServlet extends BaseServlet {

    private CustomerService customerService = new CustomerService();

    public String add(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        Customer customer = CommonUtils.toBean(request.getParameterMap(), Customer.class);
        customer.setId(CommonUtils.uuid());

        customerService.add(customer);

        request.setAttribute("msg", "恭喜,成功添加客户");

        return "/msg.jsp";
    }

//    public String findAll(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//        List<Customer> customers = customerService.findAll();
//
//        request.setAttribute("cstmList", customers);
//
//        return "/list.jsp";
//    }

    public String findAll(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
       /*
        *1.获取页面传递的pc
        * 2.给定pr的值
        * 3.使用pc和pr调用service方法,得到pageBean,保存到request域
        * 4.转发到list.jsp
        */
       /*
        * 1.得到pc
        *   如果pc参数不存在,说明pc=1
        *   如果pc参数存在,需要转换成int类型
        */
        int pc = getPc(request);

        int pr = 10;//给定pr的值,每页10行纪录

        PageBean<Customer> pb = customerService.findAll(pc, pr);
        pb.setUrl(getUrl(request));

        request.setAttribute("pb", pb);

        return "f:/list.jsp";
    }

    private int getPc(HttpServletRequest request) {
        String value = request.getParameter("pc");
        if (value == null || value.trim().isEmpty()) {
            return 1;
        }
        return Integer.parseInt(value);
    }

    public String preEdit(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String id = request.getParameter("id");
        Customer customer = customerService.find(id);

        request.setAttribute("customer", customer);

        return "/edit.jsp";
    }

    public String edit(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        Customer customer = CommonUtils.toBean(request.getParameterMap(), Customer.class);

        customerService.edit(customer);

        request.setAttribute("msg", "恭喜,编辑客户成功");
        return "/msg.jsp";
    }

    public String delete(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        String id = request.getParameter("id");

        customerService.delete(id);

        request.setAttribute("msg", "恭喜,删除客户成功");

        return "/msg.jsp";
    }

//    public String query(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//
//        Customer customer=CommonUtils.toBean(request.getParameterMap(),Customer.class);
//
//        List<Customer> customers=customerService.query(customer);
//
//        request.setAttribute("cstmList",customers);
//
//        return "/list.jsp";
//
//    }

    public String query(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        Customer customer = CommonUtils.toBean(request.getParameterMap(), Customer.class);

//        System.out.println(getUrl(request));
        customer = encoding(customer);

        int pc = getPc(request);
        int pr = 10;

        PageBean<Customer> pb = customerService.query(customer, pc, pr);

        pb.setUrl(getUrl(request));

        request.setAttribute("pb", pb);
        return "/list.jsp";

    }

    private Customer encoding(Customer customer) throws UnsupportedEncodingException {
        String name = customer.getName();
        String gender = customer.getGender();
        String phone = customer.getPhone();
        String email = customer.getEmail();

        if (name != null && !name.trim().isEmpty()) {
            name = new String(name.getBytes("ISO-8859-1"), "utf-8");
            customer.setName(name);
        }
        if (gender != null && !gender.trim().isEmpty()) {
            gender = new String(gender.getBytes("ISO-8859-1"), "utf-8");
            customer.setGender(gender);
        }
        if (phone != null && !phone.trim().isEmpty()) {
            phone = new String(phone.getBytes("ISO-8859-1"), "utf-8");
            customer.setPhone(phone);
        }
        if (email != null && !email.trim().isEmpty()) {
            email = new String(email.getBytes("ISO-8859-1"), "utf-8");
            customer.setEmail(email);
        }
        return customer;
    }

    private String getUrl(HttpServletRequest request) {
        String contextPath = request.getContextPath();
        String servletPath = request.getServletPath();
        String queryString = request.getQueryString();

        if (queryString.contains("&pc=")) {
            int index = queryString.lastIndexOf("&pc=");
            queryString = queryString.substring(0, index);
        }

        return contextPath + servletPath + "?" + queryString;
    }
}

CustomerService

package service;

import dao.CustomerDao;
import domain.Customer;
import domain.PageBean;


public class CustomerService
{
    CustomerDao customerDao=new CustomerDao();

    public void add(Customer customer)
    {
        customerDao.add(customer);
    }

//    public List<Customer> findAll()
//    {
//        return customerDao.findAll();
//    }
    public PageBean<Customer> findAll(int pc,int pr)
    {
        return customerDao.findAll(pc,pr);
    }

    public Customer find(String id)
    {
        return customerDao.find(id);
    }

    public void edit(Customer customer)
    {
        customerDao.edit(customer);
    }

    public void delete(String id)
    {
        customerDao.delete(id);
    }

    public PageBean<Customer> query(Customer customer,int pc,int pr)
    {
        return customerDao.query(customer,pc,pr);


    }

}
web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd"
         version="3.1">
    <servlet>
        <servlet-name>CustomerServlet</servlet-name>
        <servlet-class>servlet.CustomerServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>CustomerServlet</servlet-name>
        <url-pattern>/CustomerServlet</url-pattern>
    </servlet-mapping>

</web-app>
index.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<jsp:forward page="/frame.jsp"/>
frame.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<html>
<head>
    <title>主页</title>
</head>
<frameset rows="20%,*">
    <frame src="<c:url value='/top.jsp'/>" name="top"/>
    <frame src="<c:url value='/welcome.jsp'/>" name="main"/>
</frameset>
</html>

edit.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri ="http://java.sun.com/jsp/jstl/core"%>
<html>
<head>
    <title>Title</title>
</head>
<body>
<h3 align="center">编辑客户</h3>
<form action="<c:url value='/CustomerServlet'/>" method="post" >
    <input type="hidden" name="method" value="edit"/>
    <input type="hidden" name="id" value="${customer.id}"/>
    <table border="0" align="center" width="40%" style="margin-left: 100px">
        <tr>
            <td width="100px">客户名称</td>
            <td width="40%">
                <input type="text" name="name" value="${customer.name}"/>
            </td>
            <td align="left">
                <label id="nameError" class="error"> </label>
            </td>
        </tr>
        <tr>
            <td>客户性别</td>
            <td>
                <input type="radio" name="gender" value="male" id="male" <c:if test="${customer.gender eq 'male'}"/>checked="checked"/>
                <label for="male">男</label>
                <input type="radio" name="gender" value="female" id="female" <c:if test="${customer.gender eq 'female'}"/> checked="checked"/>
                <label for="female">女</label>
            </td>
            <td>
                <label id="genderError"class="error"> </label>
            </td>
        </tr>
        <tr>
            <td>手机</td>
            <td>
                <input type="text" name="phone" id="phone" value="${customer.phone}"/>
            </td>
            <td>
                <label id="phoneError"class="error"> </label>
            </td>
        </tr>
        <tr>
            <td>邮箱</td>
            <td>
                <input type="text" name="email" id="email" value="${customer.email}"/>
            </td>
            <td>
                <label id="emailError"class="error"> </label>
            </td>
        </tr>
        <tr>
            <td>描述</td>
            <td>
                <textarea rows="5" cols="30" name="description">${customer.description}</textarea>
            </td>
            <td>
                <label id="discriptionError"class="error"> </label>
            </td>
        </tr>
        <tr>
            <td></td>
            <td>
                <input type="submit" name="submit" value="编辑客户"/>
                <input type="reset" name="reset"/>
            </td>
        </tr>
    </table>
</form>


</body>
</html>
add.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>

<html>
<head>
    <title>Title</title>
</head>
<body>
<h3 align="center">添加客户</h3>
<form action="<c:url value='/CustomerServlet'/>" method="post">
    <input type="hidden" name="method" value="add">
    <table border="0" align="center" width="40%" style="margin-left: 100px">
        <tr>
            <td width="100px">客户名称</td>
            <td width="40%">
                <input type="text" name="name"/>
            </td>
            <td align="left">
                <label id="nameError" class="error"> </label>
            </td>
        </tr>
        <tr>
            <td>客户性别</td>
            <td>
                <input type="radio" name="gender" value="male" id="male"/>
                <label for="male">男</label>
                <input type="radio" name="gender" value="female" id="female"/>
                <label for="female">女</label>
            </td>
            <td>
                <label id="genderError" class="error"> </label>
            </td>
        </tr>
        <tr>
            <td>手机</td>
            <td>
                <input type="text" name="phone" id="phone"/>
            </td>
            <td>
                <label id="phoneError" class="error"> </label>
            </td>
        </tr>
        <tr>
            <td>邮箱</td>
            <td>
                <input type="text" name="email" id="email"/>
            </td>
            <td>
                <label id="emailError" class="error"> </label>
            </td>
        </tr>
        <tr>
            <td>描述</td>
            <td>
                <textarea rows="5" cols="30" name="description"></textarea>
            </td>
            <td>
                <label id="descriptionError" class="error"> </label>
            </td>
        </tr>
        <tr>
            <td></td>
            <td>
                <input type="submit" name="submit"/>
                <input type="reset" name="reset"/>
            </td>
        </tr>
    </table>
</form>

</body>
</html>
list.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
    <title>客户列表</title>
</head>
<body>
    <h3 align="center" >客户列表</h3>
    <table border="1" width="70%" align="center">
        <tr>
            <th>客户姓名</th>
            <th>性别</th>
            <th>手机</th>
            <th>邮箱</th>
            <th>描述</th>
            <th>操作</th>
        </tr>
        <c:forEach items="${pb.beanList}" var="cstm">
        <tr>
            <td>${cstm.name}</td>
            <td>${cstm.gender}</td>
            <td>${cstm.phone}</td>
            <td>${cstm.email}</td>
            <td>${cstm.description}</td>
            <td>
                <a href="<c:url value='/CustomerServlet?method=preEdit&id=${cstm.id}'/> ">编辑</a>
                <a href="<c:url value='/CustomerServlet?method=delete&id=${cstm.id}'/> ">删除</a>
            </td>
        </tr>
        </c:forEach>
    </table>
<br/>
<center>
    第${pb.pc}页/共${pb.tp}页
    <a href="${pb.url}&pc=1">首页</a>
    <c:if test="${pb.pc>1}">
        <a href="${pb.url}&pc=${pb.pc-1}">上一页</a>
    </c:if>

    <c:choose>
        <c:when test="${pb.tp<=10}">
            <c:set var="begin" value="1"/>
            <c:set var="end" value="${pb.tp}"/>
        </c:when>
        <c:otherwise>
            <c:set var="begin" value="${pb.pc-5}"/>
            <c:set var="end" value="${pb.pc+4}"/>
            <%--头溢出--%>
            <c:if test="${begin<1}">
                <c:set var="begin" value="1"/>
                <c:set var="end" value="10"/>
            </c:if>
            <%--尾溢出--%>
            <c:if test="${end>pb.tp}">
                <c:set var="end" value="${pb.tp}"/>
                <c:set var="begin" value="${pb.tp-9}"/>
            </c:if>
        </c:otherwise>
    </c:choose>

    <%--循环遍历页码列表--%>
    <c:forEach var="i" begin="${begin}" end="${end}">
        <c:choose>
            <c:when test="${i eq pb.pc}">
                [${i}]
            </c:when>
            <c:otherwise>
                <a href="${pb.url}&pc=${i}">[${i}]</a>
            </c:otherwise>
        </c:choose>

    </c:forEach>


    <c:if test="${pb.pc<pb.tp}">
    <a href="${pb.url}&pc=${pb.pc+1}">下一页</a>
    </c:if>
    <a href="${pb.url}&pc=${pb.tp}">尾页</a>

</center>

</body>
</html>
msg.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
</head>
<body>
<h1 style="color:green;" align="center">${msg}</h1>

</body>
</html>
query.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<html>
<head>
    <title></title>
</head>
<body>
    <h3 align="center">高级搜索</h3>
    <form action="<c:url value="/CustomerServlet"/>" method="get">
        <input type="hidden" name="method" value="query">
        <table border="0" align="center" width="40%" style="margin-left: 100px">
            <tr>
                <td width="100px">客户名称</td>
                <td width="40%">
                    <input type="text" name="name">
                </td>
            </tr>
            <tr>
                <td>客户性别</td>
                <td>
                    <select name="gender">
                        <option value="">==请选择性别==</option>
                        <option value="male">male</option>
                        <option value="female">female</option>
                    </select>
                </td>
            </tr>
            <tr>
            <td>手机</td>
            <td>
                <input type="text" name="phone"/>
            </td>
            </tr>
            <tr>
                <td>邮箱</td>
                <td>
                    <input type="text" name="email"/>
                </td>
            </tr>
            <tr>
                <td> </td>
                <td>

                    <input type="submit" value="搜索"/>
                    <input type="reset" value="重置"/>
                </td>
            </tr>

        </table>
    </form>

</body>
</html>
top.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
    <!-- 他的作用是为本页面所有的表单和超链接指定显示内容的框架-->
    <base target="main">
    <title>My JSP 'top.jsp' starting page</title>
</head>
<body style="text-align: center;">
    <h1>客户关系管理系统</h1>
    <a href="<c:url value='/add.jsp'/>">添加客户</a>
    <a href="<c:url value='/CustomerServlet?method=findAll'/>">查询客户</a>
    <a href="<c:url value='/query.jsp'/>">高级搜索</a>

</body>
</html>
数据库建表语句:

CREATE TABLE `t_customer` (
  `id` varchar(50) NOT NULL DEFAULT '',
  `name` varchar(50) DEFAULT NULL,
  `gender` varchar(10) DEFAULT NULL,
  `phone` varchar(30) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  `description` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




阅读更多
换一批

没有更多推荐了,返回首页