現(xiàn)在我們開始處理頁面的數(shù)據(jù)顯示
-
先填充這三個下拉框中的數(shù)據(jù)
- 新建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中調(diào)用service
@Controller
public class CustomerController {
@Autowired
BaseDictService baseDictService;
@RequestMapping("/list")
public String customer(Model model){
// 客戶來源
List<BaseDict> fromType = this.baseDictService.queryBaseDictByDictTypeCode("002");
// 所屬行業(yè)
List<BaseDict> industryType = this.baseDictService.queryBaseDictByDictTypeCode("001");
// 客戶級別
List<BaseDict> levelType = this.baseDictService.queryBaseDictByDictTypeCode("006");
// 把前端頁面需要顯示的數(shù)據(jù)放到模型中
model.addAttribute("fromType", fromType);
model.addAttribute("industryType", industryType);
model.addAttribute("levelType", levelType);
return "customer";
}
}
-
調(diào)用
http://localhost:8080/customer,可以看到頁面上的下拉框有數(shù)據(jù)了
接下來我們根據(jù)篩選條件來查詢客戶信息
分析頁面元素，得出要是用的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
- 創(chuàng)建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。整吆。决侈。仑最。刷钢。爽丹。
}
- 創(chuàng)建queryVo
public class QueryVo {
private String custName;
private String custSource;
private String custIndustry;
private String custLevel;
// 當(dāng)前頁碼數(shù)
private Integer page = 1;
// 數(shù)據(jù)庫從哪一條數(shù)據(jù)開始查
private Integer start;
// 每頁顯示數(shù)據(jù)條數(shù)
private Integer rows = 10;
get/set卤橄。绿满。。窟扑。喇颁。漏健。
}
- 創(chuàng)建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 {
/**
* 根據(jù)queryVo分頁查詢數(shù)據(jù)
*
* @param queryVo
* @return
*/
List<Customer> queryCustomerByQueryVo(QueryVo queryVo);
/**
* 根據(jù)queryVo查詢數(shù)據(jù)條數(shù)
*
* @param queryVo
* @return
*/
int queryCountByQueryVo(QueryVo queryVo);
}
- 同目錄下創(chuàng)建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>
<!-- 根據(jù)queryVo分頁查詢數(shù)據(jù) -->
<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>
<!-- 根據(jù)queryVo查詢數(shù)據(jù)條數(shù) -->
<select id="queryCountByQueryVo" parameterType="com.spring.crm.vo.QueryVo"
resultType="int">
SELECT count(1) FROM customer a
<include refid="customerQueryVo" />
</select>
</mapper>
- 創(chuàng)建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 {
/**
* 根據(jù)條件分頁查詢客戶
*
* @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) {
// 設(shè)置查詢條件,從哪一條數(shù)據(jù)開始查
queryVo.setStart((queryVo.getPage() - 1) * queryVo.getRows());
// 查詢數(shù)據(jù)結(jié)果集
List<Customer> list = this.customerDao.queryCustomerByQueryVo(queryVo);
// 查詢到的數(shù)據(jù)總條數(shù)
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");
// 所屬行業(yè)
List<BaseDict> industryType = this.baseDictService.queryBaseDictByDictTypeCode("001");
// 客戶級別
List<BaseDict> levelType = this.baseDictService.queryBaseDictByDictTypeCode("006");
// 把前端頁面需要顯示的數(shù)據(jù)放到模型中
model.addAttribute("fromType", fromType);
model.addAttribute("industryType", industryType);
model.addAttribute("levelType", levelType);
// 分頁查詢數(shù)據(jù)
Page<Customer> page = this.customerService.queryCustomerByQueryVo(queryVo);
// 把分頁查詢的結(jié)果放到模型中
model.addAttribute("page", page);
// 數(shù)據(jù)回顯
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，可以看到頁面有數(shù)據(jù)了橘霎，而且可以根據(jù)篩選條件查詢客戶信息
總結(jié)
本章記錄了如果顯示頁面數(shù)據(jù)和查詢數(shù)據(jù)蔫浆，下一章講解數(shù)據(jù)修改。
本系列源碼存放在github上 源碼
