SpringMVC_CRM系统_页面加载数据4

现在我们开始处理页面的数据显示

先填充这三个下拉框中的数据
新建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上源码

SpringMVC_CRM系统_页面加载数据4

总结

推荐阅读更多精彩内容