1、环境搭建
1. 创建一个空项目
2. 项目原型导入
copy原型中的src和WebRoot
发布,运行
3. 功能分析
添加客户
查看所有客户
编辑客户
删除客户
多条件组合查询
4. 导jar包
mysql驱动
c3p0(它的配置文件)
itcast-tools.1.4.2.jar
commons-bean-utils.jar
commons-logging.jar
commons-dbutils.jar
5.页面分析
index.jsp:它什么都没做,直接转发到frame.jsp;
frame.jsp:框架页,包含top.jsp和webcome.jsp
top.jsp:
一个标题
链接1:添加客户,请求add.jsp;
链接2:查看客户,请求list.jsp;
链接3:高级搜索,请求query.jsp;
add.jsp:一个添加客户的表单;
list.jsp:一个<table>用来显示所有客户;
query.jsp:一个高级搜索的表单;
edit.jsp:一个修改客户的表单;
msg.jsp:用来显示信息(添加客户成功)
2、功能步骤流程:
添加客户
查询客户
编辑客户
编辑分为两步:
1.通过cid查询 回显
list.jsp(编辑链接)CustomerServlet#preEdit() edit.jsp(把查询出的结果显示到表单中)
2.编辑提交保存
edit.jsp(表单页面)CustomerServlet#edit() msg.jsp(显示成功信息)
删除客户
多条件组合查询
3、dao
package dao;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
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 cn.itcast.jdbc.TxQueryRunner;
import domain.Customer;
import domain.PageBean;
/**
* 持久层
*
* @author 一万年行不行
*
*/
public class CustomerDao {
//使用工具包操作数据库,只需提供 模板、参数、执行即可
private QueryRunner qr = new TxQueryRunner();
//添加客戶
public void add(Customer c) {
//准备sql模板
String sql = "insert into t_customer values(?,?,?,?,?,?,?)";
// 准备参数模板: 将可变参数存储到数组里。
Object[] params = { c.getCid(), c.getCname(), c.getGender(),
c.getBirthday(), c.getCellphone(), c.getEmail(),
c.getDescription() };
try{
//执行sql模板,添加参数
qr.update(sql, params);
}catch(SQLException e){
throw new RuntimeException(e);
}
}
//查詢所有客戶
public PageBean<Customer> findAll(int pc, int ps){ //返回值为
try{
/*
* 创建PageBean对象pb 用来装载数据,方便页面间传送
* 设置pb的pc和ps 给pb设置当前页码和当前页的记录数
* 得到tr,设置给pb 得到总记录数,用来计算共用有多少页
* 通过查询得到beanList 设置给pb
* 返回pb
*/
PageBean<Customer> pb = new PageBean<Customer>();
pb.setPc(pc); //设置pb的pc和ps
pb.setPs(ps);
//得到tr(总记录数)
String sql = "select count(*) from t_customer";
//查询返回结果为Object 需要用Number接收,然后转为int
Number num = (Number)qr.query(sql, new ScalarHandler());
int tr = num.intValue();
pb.setTr(tr);
//得到beanList beanList是用来存储多个JavaBean的集合
//使用方言查询 , 得到每页数据的集合
sql = "select * from t_customer limit ?,?";
List<Customer> beanList = qr.query(sql,
new BeanListHandler<Customer>(Customer.class),
(pc-1)*ps,ps);
pb.setBeanList(beanList); //将当前页记录设置给pb
return pb;
}catch(SQLException e){
throw new RuntimeException(e);
}
}
//通过cid查询
public Customer load(String cid){ //返回值为
try{
//准备sql模板
String sql = "select * from t_customer where cid=?";
//执行查询并返回
return qr.query(sql, new BeanHandler<Customer>(Customer.class), cid);
}catch(SQLException e){
throw new RuntimeException(e);
}
}
//保存编辑
public void edit(Customer c) {
try {
String sql = "update t_customer set cname=?,gender=?,birthday=?," +
"cellphone=?,email=?,description=? where cid=?";
Object[] params = {c.getCname(), c.getGender(),
c.getBirthday(), c.getCellphone(), c.getEmail(),
c.getDescription(), c.getCid()};
qr.update(sql, params);
} catch(SQLException e) {
throw new RuntimeException(e);
}
}
//删除客户所有信息
public void delete(String cid) {
String sql = "delete from t_customer where cid=?";
try {
qr.update(sql,cid);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
//多条件组合查询.
public List<Customer> query(Customer criteria) {
try {
//给出sql模板
//先给出sql前缀
StringBuilder sql = new StringBuilder("select * from t_customer where 1=1");
//判断条件,向sql中追加where子句.
//给出参数,无法统一确定有几个参数,所以需要在添加条件时,直接赋值
//有几个问号就有几个参数,
//创建一个ArrayList对象集合,用来装载参数值.(有几个装几个)
List<Object> params = new ArrayList<Object>();
String cname = criteria.getCname();
if(cname != null && !cname.trim().isEmpty()){
sql.append(" and cname like?"); //模糊查询姓名
params.add("%" + cname + "%"); //
}
String gender = criteria.getGender();
if(gender != null && !gender.trim().isEmpty()){
sql.append(" and gender=?");
params.add(gender);
}
String cellphone = criteria.getCellphone();
if(cellphone != null && !cellphone.trim().isEmpty()){
sql.append(" and cellphone like ?"); //模糊查询电话
params.add("%" + cellphone + "%");
}
String email = criteria.getEmail();
if(email != null && !email.trim().isEmpty()){
sql.append(" and email like ?");
params.add("%" + email + "%");
}
//注:添加条件时注意空格
//2.给出参数,无法统一确定有几个参数,所以需要在添加条件时,根据问号给出参数
//3.调用query方法,使用结果集处理器:BeanListHandler
//参数一:将StringBuilder转为String
//参数二:将集合转为参数数组.
return qr.query(sql.toString(),
new BeanListHandler<Customer>(Customer.class),
params.toArray());
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
4、servlet
//知识点:
//servlet继承BaseServlet 然后BaseServlet继承HttpServlet
//原因:1. baseServlet中可以实现一个servlet调度多个方法。
// 2.方法完成后会自动转发或重定向到 指定页面 (在本页面中用return指定)
public class CustomerServlet extends BaseServlet {
private CustomerService customerService = new CustomerService();
//1.添加客户
public String add(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// 1. 封装表单数据
// 一句封装: 将实例对象中所有参数封装保存到map中。
//工具包:itcast-tools-1.4.3.jar commons-logging-1.2.jar
//参数解释:1:返回的是一个Map类型的值,纪录参数和参数值的对应关系
// 2.反射得到Customer实例
Customer c = CommonUtils.toBean(request.getParameterMap(), Customer.class);
// 2. 补全:cid 使用uuid
c.setCid(CommonUtils.uuid());
// 3. 使用service完成添加
customerService.add(c);
//4. 保存反馈信息到request域中
request.setAttribute("msg", "恭喜,添加客户成功");
//5. 转发到msg.jsp
return "f:/msg.jsp"; //f表示转发 r表示重定向
}
// 2.
// //查询所有客户
// public String findAll(HttpServletRequest request, HttpServletResponse response)
// throws ServletException, IOException {
//
// //调用service得到所用客户并保存到request
// request.setAttribute("cstmList", customerService.findAll());
//
// //转发到list.jsp
// return "f:/list.jsp";
// }
//分页查询所有客户
public String findAll(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
/*
* 1.获取页面传递的pc
* pc不存在,默认为首页 pc=1
* 存在,将字符串参数转为int型
* 2.给定ps的值
* 3.使用pc和ps调用service方法,得到PageBean,保存到request中
* 4.转发到list.jsp中.
*/
//1.得到pc 使用下面方法.
int pc = getPc(request);
//2.给定ps的值
int ps = 10; //每页十条记录
//3.使用pc和ps调用service方法,得到PageBean,
PageBean<Customer> pb = customerService.findAll(pc,ps);
//保存到request中
request.setAttribute("pb", pb);
return "f:/list.jsp";
}
//得到pc方法
private int getPc(HttpServletRequest request){
String value = request.getParameter("pc");
if(value == null || value.trim().isEmpty()){
return 1;
}
return Integer.parseInt(value);
}
//3.编辑客户
public String preEdit(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//得到参数cid
String cid = request.getParameter("cid");
//通过cid查询用户 并接收查询到的客户
Customer cstm = customerService.load(cid);
//把查询到的Customer保存到request域中
request.setAttribute("cstm", cstm);
//转发到表单中显示
return "f:/edit.jsp";
}
//提交编辑
public String edit(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//封装表单数据
Customer c = CommonUtils.toBean(request.getParameterMap(), Customer.class);
//执行编辑
customerService.edit(c);
//保存反馈信息到request域中
request.setAttribute("msg", "恭喜,修改客户成功");
//转发到表单中显示
return "f:/msg.jsp";
}
//4.删除客户
public String delete(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//得到参数cid
String cid = request.getParameter("cid");
//通过cid查询用户 并删除查询到的客户
customerService.delete(cid);
//保存反馈信息到request域中
request.setAttribute("msg", "恭喜,删除客户成功");
//转发到表单中显示
return "f:/msg.jsp";
}
//高级查询--多组合查询
public String query(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//封装表单数据,只有四个属性;
Customer criteria = CommonUtils.toBean(request.getParameterMap(), Customer.class);
//调用service方法,得到结果集List<Customer>
List<Customer> cstmList = customerService.query(criteria);
//保存到request中
request.setAttribute("cstmList", cstmList);
//转发到list.jsp
return "f:/list.jsp";
}
}
5、jsp
框架页面
<!-- 将页面分为上下两部分,上面为20% 下面为80% -->
<frameset rows="20%,*">
<frame src="<c:url value='/top.jsp'/>" name="top"/>
<frame src="<c:url value='/welcome.jsp'/>" name="main"/>
</frameset>
编辑页面
<script type="text/javascript">
$(function() {
$("#birthday").datepick({dateFormat:"yy-mm-dd"});
});
function add() {
$(".error").text("");
var bool = true;
if(!$(":text[name=cname]").val()) {
$("#cnameError").text("客户名称不能为空");
bool = false;
}
if(!$("#male").attr("checked") && !$("#female").attr("checked")) {
$("#genderError").text("客户性别不能为空");
bool = false;
}
if(!$(":text[name=cellphone]").val()) {
$("#cellphoneError").text("手机不能为空");
bool = false;
}
if(!$(":text[name=email]").val()) {
$("#emailError").text("email不能为空");
bool = false;
}
if(bool) {
$("form").submit();
}
}
</script>
<style type="text/css">
.error {color:red;}
</style>
</head>
<body>
<h3 align="center">编辑客户</h3>
<form action="<c:url value='/CustomerServlet'/>" method="post">
<%--
要调用CustomerServlet#edit
要传递cid
--%>
<input type="hidden" name="method" value="edit"/>
<input type="hidden" name="cid" value="${cstm.cid }"/>
<table border="0" align="center" width="40%" style="margin-left: 100px;">
<tr>
<td width="100px">客户名称</td>
<td width="40%">
<input type="text" name="cname" value="${cstm.cname }"/>
</td>
<td align="left">
<label id="cnameError" class="error"> </label>
</td>
</tr>
<tr>
<td>客户性别</td>
<td>
<!--回显性别 -->
<input type="radio" name="gender" value="男" id="male" <c:if test="${cstm.gender eq '男' }">checked='checked'</c:if> />
<label for="male">男</label>
<input type="radio" name="gender" value="女" id="female" <c:if test="${cstm.gender eq '女' }">checked='checked'</c:if> />
<label for="female">女</label>
</td>
<td>
<label id="genderError" class="error"> </label>
</td>
</tr>
<tr>
<td>客户生日</td>
<td>
<input type="text" name="birthday" id="birthday" readonly="readonly" value="${cstm.birthday }"/>
</td>
<td>
<label id="birthdayError" class="error"> </label>
</td>
</tr>
<tr>
<td>手机</td>
<td>
<input type="text" name="cellphone" value="${cstm.cellphone }"/>
</td>
<td>
<label id="cellphoneError" class="error"> </label>
</td>
</tr>
<tr>
<td>邮箱</td>
<td>
<input type="text" name="email" value="${cstm.email }"/>
</td>
<td>
<label id="emailError" class="error"> </label>
</td>
</tr>
<tr>
<td>描述</td>
<td>
<textarea rows="5" cols="30" name="description">${cstm.description }</textarea>
</td>
<td>
<label id="descriptionError" class="error"> </label>
</td>
</tr>
<tr>
<td> </td>
<td>
<input type="button" value="编辑客户" onclick="add()"/>
<input type="reset" value="重置"/>
</td>
<td> </td>
</tr>
</table>
</form>
</body>
添加页面
<script type="text/javascript">
$(function() {
$("#birthday").datepick({dateFormat:"yy-mm-dd"});
});
function add() {
$(".error").text("");
var bool = true;
if(!$(":text[name=cname]").val()) {
$("#cnameError").text("客户名称不能为空");
bool = false;
}
if(!$("#male").attr("checked") && !$("#female").attr("checked")) {
$("#genderError").text("客户性别不能为空");
bool = false;
}
if(!$(":text[name=cellphone]").val()) {
$("#cellphoneError").text("手机不能为空");
bool = false;
}
if(!$(":text[name=email]").val()) {
$("#emailError").text("email不能为空");
bool = false;
}
if(bool) {
$("form").submit();
}
}
</script>
<style type="text/css">
.error {color:red;}
</style>
</head>
<body>
<h3 align="center">添加客户</h3>
<form action="<c:url value='/CustomerServlet'/>" method="post" >
<!-- 向servlet中传递一个名为method的参数,其值表示要调用serlet中的哪个方法 -->
<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="cname"/>
</td>
<td align="left">
<label id="cnameError" class="error"> </label>
</td>
</tr>
<tr>
<td>客户性别</td>
<td>
<input type="radio" name="gender" value="男" id="male"/>
<label for="male">男</label>
<input type="radio" name="gender" value="女" id="female"/>
<label for="female">女</label>
</td>
<td>
<label id="genderError" class="error"> </label>
</td>
</tr>
<tr>
<td>客户生日</td>
<td>
<input type="text" name="birthday" id="birthday" readonly="readonly"/>
</td>
<td>
<label id="birthdayError" class="error"> </label>
</td>
</tr>
<tr>
<td>手机</td>
<td>
<input type="text" name="cellphone"/>
</td>
<td>
<label id="cellphoneError" class="error"> </label>
</td>
</tr>
<tr>
<td>邮箱</td>
<td>
<input type="text" name="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="button" value="添加客户" onclick="add()"/>
<input type="reset" value="重置"/>
</td>
<td> </td>
</tr>
</table>
</form>
</body>
组合搜索
<body>
<h3 align="center">高级搜索</h3>
<form action="<c:url value='/CustomerServlet'/>" method="post">
<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="cname"/>
</td>
</tr>
<tr>
<td>客户性别</td>
<td>
<select name="gender">
<option value="">==请选择性别==</option>
<option value="男">男</option>
<option value="女">女</option>
</select>
</td>
</tr>
<tr>
<td>手机</td>
<td>
<input type="text" name="cellphone"/>
</td>
<td>
<label id="cellphoneError" class="error"> </label>
</td>
</tr>
<tr>
<td>邮箱</td>
<td>
<input type="text" name="email"/>
</td>
<td>
<label id="emailError" class="error"> </label>
</td>
</tr>
<tr>
<td> </td>
<td>
<input type="submit" value="搜索"/>
<input type="reset" value="重置"/>
</td>
<td> </td>
</tr>
</table>
</form>
</body>
循环显示所有
<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>
<th>操作</th>
</tr>
<c:forEach items="${pb.beanList }" var="cstm">
<tr>
<td>${cstm.cname }</td>
<td>${cstm.gender }</td>
<td>${cstm.birthday }</td>
<td>${cstm.cellphone }</td>
<td>${cstm.email }</td>
<td>${cstm.description }</td>
<td>
<a href="<c:url value='/CustomerServlet?method=loadForEdit&cid=${cstm.cid }'/>">编辑</a>
<a href="<c:url value='/CustomerServlet?method=delete&cid=${cstm.cid }'/>">删除</a>
</td>
</tr>
</c:forEach>
</table>
<br/>
<center>
第${pb.pc }页/共${pb.tp }页
<a href="<c:url value='/CustmerServlet?method=findAll&pc=1'/> ">首页</a>
<c:if test="${pb.pc>1 }">
<a href="<c:url value='/CustmerServlet?method=findAll&pc=${pb.pc-1}'/> ">上一页</a>
</c:if>
<c:if test="${pb.pc < pb.tp }">
<a href="<c:url value='/CustmerServlet?method=findAll&pc=${pb.pc+1}'/> ">下一页</a>
</c:if>
<a href="<c:url value='/CustmerServlet?method=findAll&pc=${pb.tp }'/> ">尾页</a>
</center>
</body>