现在我们开始处理页面的数据显示
-
先填充这三个下拉框中的数据
- 新建service层
public interface BaseDictService {
public List<BaseDict> queryBaseDictByDictTypeCode(String dictTypeCode) ;
}
@Service
public class BaseDictServiceImpl implements BaseDictService{
@Autowired
BaseDictDao baseDictDao;
public List<BaseDict> queryBaseDictByDictTypeCode(String dictTypeCode) {
return baseDictDao.queryBaseDictByDictTypeCode(dictTypeCode);
}
}
- 在CustomerController中调用service
@Controller
public class CustomerController {
@Autowired
BaseDictService baseDictService;
@RequestMapping("/list")
public String customer(Model model){
// 客户来源
List<BaseDict> fromType = this.baseDictService.queryBaseDictByDictTypeCode("002");
// 所属行业
List<BaseDict> industryType = this.baseDictService.queryBaseDictByDictTypeCode("001");
// 客户级别
List<BaseDict> levelType = this.baseDictService.queryBaseDictByDictTypeCode("006");
// 把前端页面需要显示的数据放到模型中
model.addAttribute("fromType", fromType);
model.addAttribute("industryType", industryType);
model.addAttribute("levelType", levelType);
return "customer";
}
}
-
调用
http://localhost:8080/customer,可以看到页面上的下拉框有数据了
接下来我们根据筛选条件来查询客户信息
分析页面元素，得出要是用的sql语句：
SELECT
a.cust_id,
a.cust_name,
a.cust_user_id,
a.cust_create_id,
b.dict_item_name cust_source,
c.dict_item_name cust_industry,
d.dict_item_name cust_level,
a.cust_linkman,
a.cust_phone,
a.cust_mobile,
a.cust_zipcode,
a.cust_address,
a.cust_createtime
FROM
customer a
LEFT JOIN base_dict b ON a.cust_source = b.dict_id
LEFT JOIN base_dict c ON a.cust_industry = c.dict_id
LEFT JOIN base_dict d ON a.cust_level = d.dict_id
WHERE
a.cust_name LIKE '%马%'
AND a.cust_source = '6'
AND a.cust_industry = '2'
AND a.cust_level = '22'
LIMIT 0, 10
- 创建pojo
public class Customer {
private Long cust_id;
private String cust_name;
private Long cust_user_id;
private Long cust_create_id;
private String cust_source;
private String cust_industry;
private String cust_level;
private String cust_linkman;
private String cust_phone;
private String cust_mobile;
private String cust_zipcode;
private String cust_address;
private Date cust_createtime;
get/set。。。。。。
}
- 创建queryVo
public class QueryVo {
private String custName;
private String custSource;
private String custIndustry;
private String custLevel;
// 当前页码数
private Integer page = 1;
// 数据库从哪一条数据开始查
private Integer start;
// 每页显示数据条数
private Integer rows = 10;
get/set。。。。。。
}
- 创建CustomerDao
package com.spring.crm.dao;
import com.spring.crm.pojo.Customer;
import com.spring.crm.vo.QueryVo;
import java.util.List;
@Repository
public interface CustomerDao {
/**
* 根据queryVo分页查询数据
*
* @param queryVo
* @return
*/
List<Customer> queryCustomerByQueryVo(QueryVo queryVo);
/**
* 根据queryVo查询数据条数
*
* @param queryVo
* @return
*/
int queryCountByQueryVo(QueryVo queryVo);
}
- 同目录下创建CustomerDao.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">
<mapper namespace="com.spring.crm.dao.CustomerDao">
<sql id="customerQueryVo">
<where>
<if test="custName != null and custName != ''">
AND a.cust_name LIKE '%${custName}%'
</if>
<if test="custSource != null and custSource != ''">
AND a.cust_source = #{custSource}
</if>
<if test="custIndustry != null and custIndustry != ''">
AND a.cust_industry = #{custIndustry}
</if>
<if test="custLevel != null and custLevel != ''">
AND a.cust_level = #{custLevel}
</if>
</where>
</sql>
<!-- 根据queryVo分页查询数据 -->
<select id="queryCustomerByQueryVo" parameterType="com.spring.crm.vo.QueryVo"
resultType="Customer">
SELECT
a.cust_id,
a.cust_name,
a.cust_user_id,
a.cust_create_id,
b.dict_item_name cust_source,
c.dict_item_name cust_industry,
d.dict_item_name cust_level,
a.cust_linkman,
a.cust_phone,
a.cust_mobile,
a.cust_zipcode,
a.cust_address,
a.cust_createtime
FROM
customer a
LEFT JOIN base_dict b ON a.cust_source = b.dict_id
LEFT JOIN base_dict c ON a.cust_industry = c.dict_id
LEFT JOIN base_dict d ON a.cust_level = d.dict_id
<include refid="customerQueryVo" />
<if test="start != null">
LIMIT #{start}, #{rows}
</if>
</select>
<!-- 根据queryVo查询数据条数 -->
<select id="queryCountByQueryVo" parameterType="com.spring.crm.vo.QueryVo"
resultType="int">
SELECT count(1) FROM customer a
<include refid="customerQueryVo" />
</select>
</mapper>
- 创建service
package com.spring.crm.service;
import com.spring.crm.common.Page;
import com.spring.crm.pojo.Customer;
import com.spring.crm.vo.QueryVo;
public interface CustomerService {
/**
* 根据条件分页查询客户
*
* @param queryVo
* @return
*/
Page<Customer> queryCustomerByQueryVo(QueryVo queryVo);
}
package com.spring.crm.service;
import com.spring.crm.common.Page;
import com.spring.crm.dao.CustomerDao;
import com.spring.crm.pojo.Customer;
import com.spring.crm.vo.QueryVo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class CustomerServiceImpl implements CustomerService {
@Autowired
private CustomerDao customerDao;
public Page<Customer> queryCustomerByQueryVo(QueryVo queryVo) {
// 设置查询条件,从哪一条数据开始查
queryVo.setStart((queryVo.getPage() - 1) * queryVo.getRows());
// 查询数据结果集
List<Customer> list = this.customerDao.queryCustomerByQueryVo(queryVo);
// 查询到的数据总条数
int total = this.customerDao.queryCountByQueryVo(queryVo);
// 封装返回的page对象
Page<Customer> page = new Page<Customer>(total, queryVo.getPage(), queryVo.getRows(), list);
return page;
}
}
- 修改Controller
@RequestMapping("/customer/list")
public String queryCustomerList(QueryVo queryVo, Model model) {
try {
// 解决get请求乱码问题
if (StringUtils.isNotBlank(queryVo.getCustName())) {
queryVo.setCustName(new String(queryVo.getCustName().getBytes("ISO-8859-1"), "UTF-8"));
}
} catch (Exception e) {
e.printStackTrace();
}
// 客户来源
List<BaseDict> fromType = this.baseDictService.queryBaseDictByDictTypeCode("002");
// 所属行业
List<BaseDict> industryType = this.baseDictService.queryBaseDictByDictTypeCode("001");
// 客户级别
List<BaseDict> levelType = this.baseDictService.queryBaseDictByDictTypeCode("006");
// 把前端页面需要显示的数据放到模型中
model.addAttribute("fromType", fromType);
model.addAttribute("industryType", industryType);
model.addAttribute("levelType", levelType);
// 分页查询数据
Page<Customer> page = this.customerService.queryCustomerByQueryVo(queryVo);
// 把分页查询的结果放到模型中
model.addAttribute("page", page);
// 数据回显
model.addAttribute("custName", queryVo.getCustName());
model.addAttribute("custSource", queryVo.getCustSource());
model.addAttribute("custIndustry", queryVo.getCustIndustry());
model.addAttribute("custLevel", queryVo.getCustLevel());
return "customer";
}
- 访问
http://localhost:8080/customer/list，可以看到页面有数据了，而且可以根据筛选条件查询客户信息
总结
本章记录了如果显示页面数据和查询数据，下一章讲解数据修改。
本系列源码存放在github上 源码
