SpringMVC_CRM系统_页面加载数据4

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

  1. 先填充这三个下拉框中的数据


  2. 新建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);
    }
}

  1. 在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";
    }
}

  1. 调用 http://localhost:8080/customer,可以看到页面上的下拉框有数据了

  2. 接下来我们根据筛选条件来查询客户信息
    分析页面元素,得出要是用的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
  1. 创建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。。。。。。
}

  1. 创建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。。。。。。
}
  1. 创建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);

}

  1. 同目录下创建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>
  1. 创建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;
    }
}
  1. 修改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";
    }
  1. 访问http://localhost:8080/customer/list,可以看到页面有数据了,而且可以根据筛选条件查询客户信息

总结

本章记录了如果显示页面数据和查询数据,下一章讲解数据修改。

本系列源码存放在github上 源码

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
平台声明:文章内容(如有图片或视频亦包括在内)由作者上传并发布,文章内容仅代表作者本人观点,简书系信息发布平台,仅提供信息存储服务。

推荐阅读更多精彩内容

  • Spring Cloud为开发人员提供了快速构建分布式系统中一些常见模式的工具(例如配置管理,服务发现,断路器,智...
    卡卡罗2017阅读 134,991评论 19 139
  • Android 自定义View的各种姿势1 Activity的显示之ViewRootImpl详解 Activity...
    passiontim阅读 173,540评论 25 708
  • iOS网络架构讨论梳理整理中。。。 其实如果没有APIManager这一层是没法使用delegate的,毕竟多个单...
    yhtang阅读 5,259评论 1 23
  • 上班第五天,冷冷清清的校园只剩下孤零零的我们几个年轻教师。苦于食堂没有饭吃,我们几个慢慢悠悠地去隔壁小超市买吃的。...
    月下小翁阅读 597评论 0 0
  • 前言 最近在学习廖雪峰老师的 Python3.0 教程, 在安装 MySQL 的时候遇到了一些问题,现把我的整个安...
    Hi川阅读 415评论 0 0