二十二：交易--查询

功能需求

输入查询的条件，点击查询按钮，得到查询结果，实现翻页查询。

流程图

 

代码实现

1.TranMapper

    /**
     * 主页-查询交易
     */
    List<Tran> selectAllTranDetail(Map<String,Object> map);
    /**
     * 交易的数量
     */
    int selectAllTranCount(Map<String,Object> map);

TranMapper.xml

    <select id="selectAllTranDetail" resultMap="BaseResultMap">
        select t.id,
        u.name owner,
        t.name,
        c.name customer_id,
        ifnull(dicv1.value, ' ') as stage,
        ifnull(dicv2.value, ' ') as type,
        ifnull(dicv3.value, ' ') as source,
        co.fullname contacts_id
        from tbl_tran t
        join tbl_user u on u.id = t.owner
        join tbl_customer c on c.id = t.customer_id
        join tbl_contacts co on co.id = t.contacts_id
        left join tbl_dic_value dicv1 on dicv1.id = t.stage
        left join tbl_dic_value dicv2 on dicv2.id = t.type
        left join tbl_dic_value dicv3 on dicv3.id = t.source
        <where>
            <if test="owner!=null and owner!=''">
                and u.name like "%"#{owner}"%"
            </if>
            <if test="name!=null and name!=''">
                and t.name like "%"#{name}"%"
            </if>
            <if test="customerName!=null and customerName!=''">
                and c.name like "%"#{customerName}"%"
            </if>
            <if test="stage!=null and stage!=''">
                and dicv1.value like "%"#{stage}"%"
            </if>
            <if test="type!=null and type!=''">
                and dicv2.value like "%"#{type}"%"
            </if>
            <if test="source!=null and source!=''">
                and dicv3.value like "%"#{source}"%"
            </if>
            <if test="contactName!=null and contactName!=''">
                and co.fullname like "%"#{contactName}"%"
            </if>
        </where>
        order by t.create_time desc
        limit #{pageNo}, #{pageSize}
    </select>
    <select id="selectAllTranCount" resultType="java.lang.Integer">
        select count(*)
        from tbl_tran t
        join tbl_user u on u.id = t.owner
        join tbl_customer c on c.id = t.customer_id
        join tbl_contacts co on co.id = t.contacts_id
        left join tbl_dic_value dicv1 on dicv1.id = t.stage
        left join tbl_dic_value dicv2 on dicv2.id = t.type
        left join tbl_dic_value dicv3 on dicv3.id = t.source
        <where>
            <if test="owner!=null and owner!=''">
                and u.name like "%"#{owner}"%"
            </if>
            <if test="name!=null and name!=''">
                and t.name like "%"#{name}"%"
            </if>
            <if test="customerName!=null and customerName!=''">
                and c.name like "%"#{customerName}"%"
            </if>
            <if test="stage!=null and stage!=''">
                and dicv1.value like "%"#{stage}"%"
            </if>
            <if test="type!=null and type!=''">
                and dicv2.value like "%"#{type}"%"
            </if>
            <if test="source!=null and source!=''">
                and dicv3.value like "%"#{source}"%"
            </if>
            <if test="contactName!=null and contactName!=''">
                and co.fullname like "%"#{contactName}"%"
            </if>
        </where>
    </select>

2.TranService

	/**
	 * 主页：线索明细
	 */
	List<Tran> queryAllTranDetail(Map<String,Object> map);
	/**
	 * 线索的全部数量
	 */
	int queryAllTranCount(Map<String,Object> map);

@Service
public class TranServiceImpl implements TranService {
	@Autowired
	private TranMapper tranMapper;
	@Override
	public List<Tran> queryAllTranDetail(Map<String, Object> map) {
		return tranMapper.selectAllTranDetail(map);
	}

	@Override
	public int queryAllTranCount(Map<String,Object> map) {
		return tranMapper.selectAllTranCount(map);
	}
}

3.TranController

public class TranController {
	@Autowired
	private TranService tranService;
	@Autowired
	private DicValueService dicValueService;

	/**
	 * 主页：点击菜单“交易”，跳转到主页
	 */
	@RequestMapping("/workbench/transaction/index.do")
	public String transactionIndex(HttpServletRequest request) {
		// 加载字典
		List<DicValue> sourceList = dicValueService.queryDicValueByTypeCode("source");
		List<DicValue> stageList = dicValueService.queryDicValueByTypeCode("stage");
		List<DicValue> transactionTypeList = dicValueService.queryDicValueByTypeCode("transactionType");
		// 保存请求域
		request.setAttribute("sourceList",sourceList);
		request.setAttribute("stageList",stageList);
		request.setAttribute("transactionTypeList",transactionTypeList);
		return "workbench/transaction/index";
	}

	/**
	 * 主页：搜索交易记录列表
	 */
	@RequestMapping("/workbench/transaction/transactionListIndex.do")
	public @ResponseBody
	Object transactionListIndex(int pageNo, int pageSize,String owner,String name,String customerName,String stage,String type,String source,String contactName) {
		// 获取参数
		HashMap<String, Object> map = new HashMap<>();
		map.put("pageNo", (pageNo-1) * pageSize);
		map.put("pageSize", pageSize);
		map.put("owner", owner);
		map.put("name", name);
		map.put("customerName", customerName);
		map.put("stage", stage);
		map.put("type", type);
		map.put("source", source);
		map.put("contactName", contactName);
		// 调用service
		List<Tran> tranAllList = tranService.queryAllTranDetail(map);
		int totalRows = tranService.queryAllTranCount(map);
		// 返回json
		Map<String, Object> retMap = new HashMap<>();
		retMap.put("tranAllList",tranAllList);
		retMap.put("totalRows",totalRows);
		return retMap;
	}
}

4.前端index.jsp

        // 加载线索明细
        function loadAllTran(pageNo, pageSize) {
            // 收集查询参数
            var owner = $.trim($("#query-owner").val());
            var name =$.trim($("#query-name").val());
            var customerName = $.trim($("#query-customername").val());
            var stage = $("#query-stage option:selected").val();
            var type = $("#query-type option:selected").val();
            var source = $("#query-source option:selected").val();
            var contactName = $.trim($("#query-contactname").val());
            // 发送请求
            $.ajax({
                url: 'workbench/transaction/transactionListIndex.do',
                data: {
                    pageNo: pageNo,
                    pageSize: pageSize,
                    owner:owner,
                    name:name,
                    customerName:customerName,
                    stage:stage,
                    type:type,
                    source:source,
                    contactName:contactName
                },
                type: 'post',
                dataType: 'json',
                success: function (data) {
                    var htmlStr = "";
                    $.each(data.tranAllList, function (index, obj) {
                        htmlStr += "<tr>";
                        htmlStr += "<td><input type=\"checkbox\" style='width: 15px;height: 15px' value=\"" + obj.id + "\"/></td>";
                        htmlStr += "<td><a style=\"text-decoration: none; cursor: pointer;\"onclick=\"window.location.href='';\">" + obj.name + "</a></td>";
                        htmlStr += "<td>" + obj.customerId + "</td>";
                        htmlStr += "<td>" + obj.stage + "</td>";
                        htmlStr += " <td>" + obj.type + "</td>";
                        htmlStr += " <td>" + obj.owner + "</td>";
                        htmlStr += " <td>" + obj.source + "</td>";
                        htmlStr += " <td>" + obj.contactsId + "</td>";
                        htmlStr += " </tr>";
                    });
                    $("#tBody").html(htmlStr);
                    //计算页码数
                    var totalPages = data.totalRows / pageSize;
                    if (data.totalRows % pageSize > 0) {
                        totalPages = parseInt(data.totalRows / pageSize) + 1;
                    }
                    // 调用页码翻页函数
                    $("#pagDiv").bs_pagination({
                        currentPage: pageNo,//当前页码数
                        rowsPerPage: pageSize,//每页显示条数
                        totalRows: data.totalRows,//总条数
                        totalPages: totalPages,//总页数
                        visiblePageLinks: 5,//最多显示的卡片数
                        showGoToPage: true,//显示“跳转到部分
                        showRowsPerPage: true,//每页显示条数
                        showRowsInfo: false,//是否显示记录的信息
                        //用户切换页码，触发函数，返回页码之后的pageNo和pageSize
                        onChangePage: function (event, pageObj) {
                            loadAllTran(pageObj.currentPage, pageObj.rowsPerPage);
                        }
                    });
                }

            });
        }