CRM客户关系关系系统复习
4.1、pageHelper，断点调试，所有查询实现
目标：把自定义的查询改成pageHelper格式的查询
逻辑实现：
需求： 销售机会的分页查询，查询的是devresult=0，所有未开发的销售机会（state=0/1）
未开发 : 分配与否无关紧要 devresult=0
开发中: 已经分配，devresult=1 并且有销售计划devPlan表的关联数据
开发成功： 已经分配，devresult=2
开发失败： 已经分配，devresult=3
条件查询：
1.pageHelper+逆向工程
2.pageHelper+自定义mapper
添加：
1.请求处理完成，http://localhost:8080/user/customerManagerComboList。combobox组件发送的请求
新增俩组件:
页面已经加载了，不显示
easyui-dialog 对话框 默认是closed=true关闭的
点击创建按钮 $("#dlg").dialog("open")打开
easyui-combobox 下拉列表框 url发送请求请求所有的销售经理
先在pom文件里<!--log4j-->上边导入两个依赖(这两个方便查看后端自己写的sql语句)
<!--slf4j-api-->
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.7.6</version>
</dependency>
<!--slf4j-log4j12-->
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>1.7.6</version>
</dependency>
1、导入依赖
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.0.3</version>
</dependency>
2、applicationContext.xml中完成配置，配置拦截器，拦截sql语句 加入limit关键字
<!-- 配置SqlSessionFactory -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<!-- 配置mybatis核心配置文件 -->
<property name="configLocation" value="classpath:conf.xml"/>
<!-- 配置数据源 -->
<property name="dataSource" ref="dataSource"/>
<property name="plugins">
<array>
<bean class="com.github.pagehelper.PageInterceptor">
<property name="properties">
<props>
<!--数据库方言-->
<prop key="helperDialect">mysql</prop>
<!--分页合理化-->
<prop key="reasonable">true</prop>
</props>
</property>
</bean>
</array>
</property>
</bean>
逆向工程两张表t_sale_chance t_cus_dev_plan
public class DatagridResult {
//总条数
private long total;
//分页的集合
private List<?> rows;
public long getTotal() {
return total;
}
public void setTotal(long total) {
this.total = total;
}
public List<?> getRows() {
return rows;
}
public void setRows(List<?> rows) {
this.rows = rows;
}
}
修改完SaleChanceServiceImpl，把3、中bean类中写的PageInfo删掉继续修改代码
SaleChanceServiceImpl
@Service
public class SaleChanceServiceImpl implements ISaleChanceService {
@Autowired
private TSaleChanceMapper saleChanceMapper;
@Override
public DatagridResult selectSaleChanceByPage(int page,int rows) {
//1.设置分页属性
//第一个参数是查询的页数,第二个参数是每页显示条数
PageHelper.startPage(page, rows);
//2.查询数据库
//where devresult=0
TSaleChanceExample example = new TSaleChanceExample();
example.createCriteria().andDevresultEqualTo(0);
List<TSaleChance> saleChanceList = saleChanceMapper.selectByExample(example);
//3.saleChanceList 转换成pagehelper提供的pageInfo对象
//pageInfo会包含所有分页所需的内容
PageInfo<TSaleChance> pageInfo = new PageInfo<>(saleChanceList);
//3.封装DatagridResult对象
DatagridResult datagridResult = new DatagridResult();
datagridResult.setRows(pageInfo.getList());
//pageInfo里的total是long类型的，把我们的int改成long生成get，set方法即可
datagridResult.setTotal(pageInfo.getTotal());
return datagridResult;
}
}
ISaleChanceService
public interface ISaleChanceService {
//分页查询salechance销售机会
DatagridResult selectSaleChanceByPage(int page,int rows);
}
其中tsalechanceMapper和.xml中的自定义方法及自定义查询sql删掉
SaleChanceController
@RestController
@RequestMapping("saleChance")
public class SaleChanceController {
@Autowired
private ISaleChanceService saleChanceService;
//SaleChance/list/0 检索销售机会的
@RequestMapping("list/{devResult}")
//page 当前页 rows每页显示条数
public DatagridResult list(@PathVariable int devResult, int page,int rows){
return saleChanceService.selectSaleChanceByPage(page,rows);
}
}
状态码：
200成功
500服务器内部错误
404资源未找到
405请求方式不匹配
4.2、下一个查询小功能
由于数据较少，saleChanceManage.jsp里加个script
<script>
$(function () {
$("#dg").datagrid({
pageSize : 1,
pageList : [1,2,3,4,5]
})
})
</script>
代码模块
SaleChanceController,新增TSaleChance接受前台提交的查询条件
@RestController
@RequestMapping("saleChance")
public class SaleChanceController {
@Autowired
private ISaleChanceService saleChanceService;
//SaleChance/list/0 检索销售机会的
@RequestMapping("list/{devResult}")
//page 当前页 rows每页显示条数
//通过TSaleChance接受前台提交的查询条件
public DatagridResult list(TSaleChance saleChance, @PathVariable int devResult, int page, int rows){
saleChance.setDevresult(devResult);
return saleChanceService.selectSaleChanceByPage(saleChance,page,rows);
}
}
ISaleChanceService，新增查询条件TSaleChance saleChance
public interface ISaleChanceService {
//分页查询salechance销售机会 saleChance是查询条件
DatagridResult selectSaleChanceByPage(TSaleChance saleChance, int page, int rows);
}
SaleChanceServiceImpl方法一：pageHelper+逆向工程
@Service
public class SaleChanceServiceImpl implements ISaleChanceService {
@Autowired
private TSaleChanceMapper saleChanceMapper;
@Override
public DatagridResult selectSaleChanceByPage(TSaleChance saleChance,int page,int rows) {
//1.设置分页属性
//第一个参数是查询的页数,第二个参数是每页显示条数
PageHelper.startPage(page, rows);
//2.查询数据库
//where devresult=0
TSaleChanceExample example = new TSaleChanceExample();
TSaleChanceExample.Criteria criteria = example.createCriteria();
if (saleChance.getDevresult() !=null){//devresult 开发状态
criteria.andDevresultEqualTo(saleChance.getDevresult());
}
if (StringUtils.isNotBlank(saleChance.getCustomername())){//客户名
criteria.andCustomernameLike("%"+ saleChance.getCustomername() +"%");
}
if (StringUtils.isNotBlank(saleChance.getOverview())){//概要
criteria.andOverviewLike("%"+ saleChance.getOverview() +"%");
}
if (StringUtils.isNotBlank(saleChance.getCreateman())){//创建人
criteria.andCreatemanEqualTo(saleChance.getCreateman());
}
if (saleChance.getState() != null){//分配状态
criteria.andStateEqualTo(saleChance.getState());
}
List<TSaleChance> saleChanceList = saleChanceMapper.selectByExample(example);
//3.saleChanceList 转换成pagehelper提供的pageInfo对象
//pageInfo会包含所有分页所需的内容
PageInfo<TSaleChance> pageInfo = new PageInfo<>(saleChanceList);
//3.封装DatagridResult对象
DatagridResult datagridResult = new DatagridResult();
datagridResult.setRows(pageInfo.getList());
datagridResult.setTotal(pageInfo.getTotal());
return datagridResult;
}
}
SaleChanceServiceImpl方法二：pageHelper+自定义mapper
@Service
public class SaleChanceServiceImpl implements ISaleChanceService {
@Autowired
private TSaleChanceMapper saleChanceMapper;
@Override
public DatagridResult selectSaleChanceByPage(TSaleChance saleChance,int page,int rows) {
//1.设置分页属性
//第一个参数是查询的页数,第二个参数是每页显示条数
PageHelper.startPage(page, rows);
//2.调用自定义的查询方法
List<TSaleChance> saleChanceList = saleChanceMapper.selectTiaojians(saleChance);
//3.saleChanceList 转换成pagehelper提供的pageInfo对象
//pageInfo会包含所有分页所需的内容
PageInfo<TSaleChance> pageInfo = new PageInfo<>(saleChanceList);
//3.封装DatagridResult对象
DatagridResult datagridResult = new DatagridResult();
datagridResult.setRows(pageInfo.getList());
datagridResult.setTotal(pageInfo.getTotal());
return datagridResult;
}
}
接着自定义：TSaleChanceMapper里生成方法
//条件查询销售机会
List<TSaleChance> selectTiaojians(TSaleChance saleChance);
接着TSaleChanceMapper.xml里写sql
<select id="selectTiaojians" resultMap="BaseResultMap" parameterType="tsalechance">
select * from t_sale_chance
<where>
<if test="devresult != null">
and devResult = #{devresult}
</if>
<if test="customername != null and customername !=''">
and customerName like "%${customername}%"
</if>
<if test="overview != null and overview !=''">
and overview like "%${overview}%"
</if>
<if test="createman != null and createman != ''">
and createMan = #{createman}
</if>
<if test="state != null">
and state = #{state}
</if>
</where>
</select>
自定义结束，SaleChanceServiceImpl里干净
4.3、添加
1.请求处理完成，http://localhost:8080/user/customerManagerComboList。combobox组件发送的请求
新增俩组件：
页面已经加载了，不显示
easyui-dialog 对话框 默认是closed=true关闭的
点击创建按钮 $("#dlg").dialog("open")打开
easyui-combobox 下拉列表框 url发送请求请求所有的销售经理
2.写代码把能指派给的所有客户经理显示出来
UserController
@Controller
@RequestMapping("user")
public class UserController {
@Autowired
private IUserService userService;
@RequestMapping("login")
public String login(String username, String password, HttpSession session){
//1.根据账号密码检索数据库
TUser user = userService.login(username,password);
if (user != null){
//2.如果匹配，纳入session 转发到首页main.jsp
user.setPassword(null);
session.setAttribute("user",user);
return "main";
}else{//3.如果不匹配，跳转到登陆
//存放错误信息
session.setAttribute("msg","您输入的账号或密码有误！请重新输入！");
return "redirect:/login.jsp";
}
}
@RequestMapping("rights")
@ResponseBody
public TUser rights(String name){
return userService.getRightByUsername(name);
}
//新增的
@RequestMapping("customerManagerComboList")
@ResponseBody
public List<TUser> customerManagerComboList(){
return userService.selectCustomerManager();
}
}
IUserService
public interface IUserService {
/**
* 登陆
* @param username 用户名
* @param password 密码
* @return 查询结果
*/
TUser login(String username, String password);
//根据用户名查询所有权限
TUser getRightByUsername(String name);
//查询所有客户经理
List<TUser> selectCustomerManager();
}
UserServiceImpl
@Service
public class UserServiceImpl implements IUserService {
@Autowired
private TUserMapper userMapper;
@Override
public TUser login(String username, String password) {
TUserExample example = new TUserExample();
//设置查询条件 where usr_name = ? and usr_password = ?
example.createCriteria().andUsernameEqualTo(username)
.andPasswordEqualTo(password);
List<TUser> tUsers = userMapper.selectByExample(example);
if (tUsers != null && tUsers.size() == 1){
return tUsers.get(0);
}
return null;
}
@Override
public TUser getRightByUsername(String name) {
return userMapper.selectRightsByUsername(name);
}
@Override
public List<TUser> selectCustomerManager() {
TUserExample example = new TUserExample();
example.createCriteria().andRolenameEqualTo("客户经理");
return userMapper.selectByExample(example);
}
}
