任务:
CRM - 客户关系管理系统(已有前端)
1. 添加客户(插入数据)
2. 客户列表
- 显示和模糊查找
- 删除
- 分页
- 修改
3. 添加联系人
4. 联系人列表
前言:
对于以前的三层架构的dao层,不再使用jdbc,用mybatis框架取代之
前置工作:
1.前端:
首先新建一个dynamic web project,把已有的前端文件拷贝到webcontent下,根据报错提示,添加相应jar包,如:jstl.jar
前端路径,el表达式的变量名可以修改,最好是和数据库的字段名一致
2.编码问题
建个package(com.wowowo.filter),把预先写好的通用编码解决方案(过滤器)放入,并在web.xml文件中配置好
可以解决get/post方法的乱码问题
<filter>
<filter-name>filter1</filter-name>
<filter-class>com.wowowo.filter.GenericEncodingFilter</filter-class>
</filter>
<filter-mapping>
<filter-name>filter1</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
3.mybatis加载问题
分别建立2个包
1.com.wowowo.listener
package com.wowowo.listener;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
public class ContextServletListener implements ServletContextListener {
@Override
public void contextDestroyed(ServletContextEvent arg0) {
}
@Override
public void contextInitialized(ServletContextEvent arg0) {
//服务器启动时加载ApplicationContext,创建全局SqlSessionFactory对象
try {
Class.forName("com.wowowo.applicationcontext.ApplicationContext");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
}
2.com.wowowo.applicationcontext
package com.wowowo.applicationcontext;
import java.io.IOException;
import java.io.Reader;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class ApplicationContext {
private static SqlSessionFactory ssf;
static {
String resource = "SqlMapConfig.xml";
Reader reader;
try {
// 读取全局配置文件SqlMapConfig.xml
reader = Resources.getResourceAsReader(resource);
ssf = new SqlSessionFactoryBuilder().build(reader);
} catch (IOException e) {
e.printStackTrace();
}
}
//service层通过本方法获取SqlSessionFactory对象
public static SqlSessionFactory getSqlSessionFactory() {
return ssf;
}
}
数据库问题:
新建一个数据库,导入相应sql文件(表)
在web项目名/src下建立一个dbconfig.properties文件用于配置数据库连接参数
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/crm?useUnicode=true&characterEncoding=utf8
uname=root
upwd=root
在web项目名/src下建立一个SqlMapConfig.xml和log4j.properties(mybatis日志文件,在控制台显示每次数据库访问的操作)
SqlMapConfig.xml(mybatis全局配置文件)
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 读取dbconfig.properties文件下的参数 -->
<properties resource="dbconfig.properties">
</properties>
<!-- 给该包下的所有bean文件起别名 -->
<typeAliases>
<package name="com.wowowo.bean"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<!-- 这里的value是来自dbconfig.properties文件 -->
<property name="driver" value="${driver}" />
<property name="url" value="${url}" />
<property name="username" value="${uname}" />
<property name="password" value="${upwd}" />
</dataSource>
</environment>
</environments>
<!-- 动态代理所在的包名-->
<mappers>
<package name="com.wowowo.mapper"/>
</mappers>
</configuration>
com.wowowo.bean包:用于存放pojo类
com.wowowo.mapper包:用于存放动态代理用到的接口和xml文件
实现功能:
1. 添加客户(插入数据)
保存功能主要是对保存按钮进行设计,保存按钮按下,提交一个表单到对应的servlet(url路径根据前端form标签的action属性设置,也可以自己改掉)
<FORM id=form1 name=form1
action="${pageContext.request.contextPath }/addcustomerServlet"
method=post>
添加功能的servlet设计(url-path=/addcustomerServlet):
package com.wowowo.servlet;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.wowowo.bean.Crm_Customer;
import com.wowowo.service.CustomerService;
import com.wowowo.service.impl.CustomerServiceImpl;
@WebServlet("/addcustomerServlet")
public class AddCustomerServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
private CustomerService us=new CustomerServiceImpl();
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//取值包装成客户对象,客户对象为pojo类,里面的成员属性和数据库表里的字段对应,要有getset方法
Crm_Customer cm=new Crm_Customer();
cm.setCust_name(request.getParameter("custName"));
cm.setCust_level(request.getParameter("custLevel"));
cm.setCust_source(request.getParameter("custSource"));
cm.setCust_linkman(request.getParameter("custLinkman"));
cm.setCust_phone(request.getParameter("custPhone"));
cm.setCust_mobile(request.getParameter("custMobile"));
//调用service层去保存客户对象
us.insert(cm);
//重定向到客户列表,这里也可以用转发
//request.getRequestDispatcher("/listcustomerservlet").forward(request, response);
response.sendRedirect("/crm_mybatis/listcustomerservlet");
}
}
service层的设计:
package com.wowowo.service.impl;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import com.wowowo.applicationcontext.ApplicationContext;
import com.wowowo.bean.Crm_Customer;
import com.wowowo.bean.PageBean;
import com.wowowo.mapper.CustomerMapper;
import com.wowowo.service.CustomerService;
public class CustomerServiceImpl implements CustomerService {
private SqlSessionFactory ssf = ApplicationContext.getSqlSessionFactory();
@Override
public void insert(Crm_Customer cm) {
//获取sqlsession
SqlSession SqlSession = ssf.openSession();
CustomerMapper mapper = SqlSession.getMapper(CustomerMapper.class);
mapper.insert(cm);
//增删改要记得提交表单
SqlSession.commit();
SqlSession.close();
}
mapper.xml文件的配置:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- namespace要和接口名一致 -->
<mapper namespace="com.wowowo.mapper.CustomerMapper">
<!-- insert语句的字段都要指定 -->
<insert id="insert" parameterType="Crm_Customer">
insert into
cst_customer
(cust_name,cust_level,cust_source,cust_linkman,cust_phone,cust_mobile)
values
(#{cust_name},#{cust_level},#{cust_source},#{cust_linkman},#{cust_phone},#{cust_mobile});
</insert>
2. 客户列表
显示,模糊查找
listcustomerservlet:
package com.wowowo.servlet;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.wowowo.bean.Crm_Customer;
import com.wowowo.bean.PageBean;
import com.wowowo.service.CustomerService;
import com.wowowo.service.impl.CustomerServiceImpl;
@WebServlet("/listcustomerservlet")
public class ListCustomerServlet extends HttpServlet {
private CustomerService us=new CustomerServiceImpl();
private static final long serialVersionUID = 1L;
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//获取当前页数和每页显示记录数
int page=1;
//如果页数属性为空(第一次访问),长度为0(text框内没有值),默认为第一页
if(req.getParameter("page")!=null&&req.getParameter("page").length()!=0){
page = Integer.parseInt(req.getParameter("page"));
}
//同上
int pageSize=5;
if(req.getParameter("pageSize")!=null&&req.getParameter("page").length()!=0){
pageSize=Integer.parseInt(req.getParameter("pageSize"));
}
//筛选功能的客户姓名,用于模糊查找,包装成客户对象
String cust_name = req.getParameter("cust_name");
Crm_Customer cm=new Crm_Customer();
cm.setCust_name(cust_name);
//查找总记录数
int total = us.getCustomerCount(cm);
//构造pagebean对象,见02-2MVC
PageBean<Crm_Customer> pb=new PageBean<Crm_Customer>(page,pageSize,total);
pb.setCm(cm);
//调用service层查询客户数据
us.queryCustomer(pb);
//赋值,模糊查找的客户名(以便点击筛选后还能显示),页数,最大页数,每页显示记录数等,麻烦了点,这里应该改前端的。
req.setAttribute("cust_name", pb.getCm().getCust_name());
req.setAttribute("list", pb.getData());
req.setAttribute("pageSize", pb.getPageSize());
req.setAttribute("page", pb.getPage());
req.setAttribute("total", pb.getTotal());
req.setAttribute("totalPage", pb.getTotalPage());
//转发到jsp页面(view层)显示(这里用转发时因为list.jsp的数据是从数据库拿出来的,所以带参传过去)
req.getRequestDispatcher("/jsp/customer/list.jsp").forward(req, resp);
}
}
service层
@Override
public int getCustomerCount(Crm_Customer cm) {
SqlSession SqlSession = ssf.openSession();
CustomerMapper mapper = SqlSession.getMapper(CustomerMapper.class);
//返回Long类型,转换成int
int selectcount = mapper.selectcount(cm).intValue();
SqlSession.close();
return selectcount;
}
@Override
public void queryCustomer(PageBean<Crm_Customer> pb) {
SqlSession SqlSession = ssf.openSession();
CustomerMapper mapper = SqlSession.getMapper(CustomerMapper.class);
List<Crm_Customer> data = mapper.querycustomer(pb);
pb.setData(data);
SqlSession.close();
}
xml文件的配置:
<!-- 查询客户的总数 -->
<select id="selectcount" resultType="Long" parameterType="Crm_Customer">
select count(*) from
cst_customer
<where>
<if test="cust_name!=null">
and cust_name LIKE "%"#{cust_name}"%"
</if>
</where>
</select>
<!-- 有参数则模糊查询相关客户的信息,无参则查询所有客户信息-->
<select id="querycustomer" resultType="Crm_Customer" parameterType="PageBean">
select * from cst_customer
<where>
<if test="cm.cust_name!=null">
and cust_name LIKE "%"#{cm.cust_name}"%"
</if>
</where>
limit ${startPage},${pageSize};
</select>
分页
参考02-2MVC和上面的显示功能
这里有个关键就是又要模糊查询又要分页,所以在pagebean里面添加了一个客户对象依赖,查询方法的参数类型还是pagebean不变,根据cm.cust_name这个属性是否为空来决定模糊查询(见上图)
删除和修改
和插入一样的业务流程,就是修改的sql语句写法要规范
<update id="editCustomer" parameterType="Crm_Customer">
update cst_customer
<set>
<if test="cust_name != null">cust_name=#{cust_name},</if>
<if test="cust_level != null">cust_level=#{cust_level},</if>
<if test="cust_source != null">cust_source=#{cust_source},</if>
<if test="cust_linkman != null">cust_linkman=#{cust_linkman},</if>
<if test="cust_phone != null">cust_phone=#{cust_phone},</if>
<if test="cust_mobile != null">cust_mobile=#{cust_mobile}</if>
</set>
where cust_id=#{cust_id};
</update>
3. 添加联系人(根据客户id添加)
和添加客户的业务逻辑相差无几,故不再多写
这里用到了逆向工程,通过配置xml文件逆向生成pojo类,mapper接口和mapper.xml
逆向工程参考 05-1 https://blog.csdn.net/qq_36194262/article/details/83539540
4. 联系人列表
和客户列表的业务逻辑相差无几,故不再多写
注意:逆向生成的mapper.xml中并没有分页查找的方法,可以手工添加条件
参考 05-1 https://blog.csdn.net/qq_36194262/article/details/83539540
在逆向生成的CstLinkmanExamplel类中,添加以下2个成员属性和getset方法
protected Integer startPage;
protected Integer pageSize;
servlet层:
CstLinkmanExample ce = new CstLinkmanExample();
//有筛选参数就放入CstLinkmanExample对象中
if (request.getParameter("lkmName") != null && request.getParameter("lkmName").length() > 0) {
Criteria cc = ce.createCriteria();
cc.andLkmNameLike("%" + request.getParameter("lkmName") + "%");
}
//获取总记录数(有无筛选参数皆可)
int total = ls.selectcount(ce);
//通过PageBean的构造获取起始页和每页记录数
PageBean<CstLinkman> pb = new PageBean<>(page, pageSize, total);
ce.setPageSize(pageSize);
ce.setStartPage(pb.getStartPage());
//调用查询方法
List<CstLinkman> list = ls.querylist(ce);
mapper.xml文件:在id=selectByExample的sql标签末尾加上
<!-- &在xml格式中表示& -->
<if test="startPage!= null && pageSize!=null">
limit #{startPage} ,#{pageSize}
</if>