使用说明：
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>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);
}
}
}
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;
}
}
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;
}
}
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;
}
}
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);
}
}
<?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><%@ page contentType="text/html;charset=UTF-8" language="java" %>
<jsp:forward page="/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>
<%@ 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>
<%@ 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>
<%@ 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>
<%@ 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>
<%@ 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;
扫一扫
2703
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
