1. sql
<!-- 全部经销商在售商品列表 -->
<select id="findAllDealerCarBaseInfo" parameterType="com.ujche.common.PageRequest" resultType="com.ujche.model.DealerCarBaseInfo">
SELECT
de.carId AS carId,
base.carName AS carName,
de.dealerId AS 'dealerId',
dd.dealerName as dealerName,
brand.brandName as brandName,
see.seriesName AS seriesName,
type.typeName AS typeName,
vv.carTypeName AS carTypeName,
de.carSellPrice AS carSellPrice,
de.carCount AS carCount
FROM
dealer_onsell AS de
LEFT JOIN dealer_dealerinfo dd ON de.dealerId = dd.dealerId
LEFT JOIN car_carbaseinfo base on base.carId = de.carId
LEFT JOIN car_brand brand ON brand.bid = base.carBrandId
LEFT JOIN car_series see ON see.sid = base.carSeriesId
LEFT JOIN car_type type ON type.tid = base.carTypeId
LEFT JOIN car_vehicletype vv ON vv.ctid = base.carVehicleTypeId
<where>
<if test="whereStr != null">
${whereStr}
</if>
</where>
ORDER BY base.addtime desc
LIMIT #{startRow},#{pageSize}
</select>
<!-- 经销商在售商品count分页用 -->
<!-- 全部经销商在售商品列表 -->
<select id="findAllDealerCarBaseInfoCount" parameterType="com.ujche.common.PageRequest" resultType="Integer">
SELECT
count(de.carId) AS total
FROM
dealer_onsell AS de
LEFT JOIN dealer_dealerinfo dd ON de.dealerId = dd.dealerId
LEFT JOIN car_carbaseinfo base on base.carId = de.carId
LEFT JOIN car_brand brand ON brand.bid = base.carBrandId
LEFT JOIN car_series see ON see.sid = base.carSeriesId
LEFT JOIN car_type type ON type.tid = base.carTypeId
LEFT JOIN car_vehicletype vv ON vv.ctid = base.carVehicleTypeId
<where>
<if test="whereStr != null">
${whereStr}
</if>
</where>
</select>
2. serviceimpl
@Override
public PageResult<DealerCarBaseInfo> findAllDealerCarBaseInfo(PageRequest pageRequest){
PageResult<DealerCarBaseInfo> p = new PageResult(pageRequest.getPageNum(),
pageRequest.getPageSize());
p.setResult(dealerOnsellMapper.findAllDealerCarBaseInfo(pageRequest));
p.setTotal(dealerOnsellMapper.findAllDealerCarBaseInfoCount(pageRequest));
return p;
}
3.pageRequest
/**
* Description: 分页 Author: liuzh Update: liuzh(2014-04-16 10:56)
*/
public class PageResult<T> {
private int pageNum;
private int pageSize;
private int startRow;
private int endRow;
private long total;
private int pages;
private List<T> result;
private String pagerStr;
/* 承载条件 */
private Map<String, Object> map;
public String getPagerStr() {
return pagerStr;
}
public void setPagerStr(String pagerStr) {
this.pagerStr = pagerStr;
}
public PageResult() {
}
public PageResult(int pageNum, int pageSize) {
this.pageNum = pageNum;
this.pageSize = pageSize;
this.startRow = pageNum > 0 ? (pageNum - 1) * pageSize : 1;
this.endRow = pageNum > 0 ? pageNum * pageSize : pageSize;
}
public PageResult(int pageNum, int pageSize, int total) {
this.pageNum = pageNum;
this.pageSize = pageSize;
this.total = total;
this.startRow = pageNum > 0 ? (pageNum - 1) * pageSize : 1;
this.endRow = pageNum > 0 ?
(pageNum * pageSize > total ? total : pageNum * pageSize)
: (pageSize > total ? total : pageSize);
}
public List<T> getResult() {
return result;
}
public void setResult(List<T> result) {
long totalPages = this.total / this.pageSize
+ ((this.total % this.pageSize == 0) ? 0 : 1);
this.pages = (int) totalPages;
this.result = result;
}
public int PagesBySelect() {
// return pages;
long totalPages = this.total / this.pageSize
+ ((this.total % this.pageSize == 0) ? 0 : 1);
return (int) totalPages;
}
public int getPages() {
long totalPages = this.total / this.pageSize
+ ((this.total % this.pageSize == 0) ? 0 : 1);
this.pages = (int) totalPages;
return pages;
}
/*
* public void setPages(int pages) { this.pages = pages; }
*/
public int getEndRow() {
return endRow;
}
public void setEndRow(int endRow) {
this.endRow = endRow;
}
public int getPageNum() {
return pageNum;
}
public void setPageNum(int pageNum) {
this.pageNum = pageNum;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public int getStartRow() {
return startRow;
}
public void setStartRow(int startRow) {
this.startRow = startRow;
}
public long getTotal() {
return total;
}
public void setTotal(long total) {
this.total = total;
}
public Map<String, Object> getMap() {
return map;
}
public void setMap(Map<String, Object> map) {
this.map = map;
}
@Override
public String toString() {
return "Page{" + "pageNum=" + pageNum + ", pageSize=" + pageSize
+ ", startRow=" + startRow + ", endRow=" + endRow + ", total="
+ total + ", pages=" + pages + '}';
}
}
4. controller
/**
* 经销商商品查询
* @return
*/
@RequestMapping("/selectDealerProduce")
public ModelAndView AllDealerOnSet(HttpServletRequest request,PageRequest pageRequest,dealerOnsell onsell){
StringBuffer sb = new StringBuffer();
StringBuffer params = new StringBuffer();
if(onsell.carIDorName != null && onsell.carIDorName != ""){//商品编号、商品名称
sb.append(" and ( base.carId = '"+onsell.carIDorName+"' or base.carName like '%"+onsell.carIDorName+"%' ) ");
params.append("carIDorName="+onsell.carIDorName+"&");
request.setAttribute("carIDorName", onsell.carIDorName);
}
if(onsell.dealerNameOrdealerCort != null&& onsell.dealerNameOrdealerCort!= ""){//经销商
sb.append(" and ( dd.dealerId = '"+onsell.dealerNameOrdealerCort+"' or dd.dealerName like '%"+onsell.dealerNameOrdealerCort+"%' ) ");
params.append("dealerNameOrdealerCort="+onsell.dealerNameOrdealerCort+"&");
request.setAttribute("dealerNameOrdealerCort", onsell.dealerNameOrdealerCort);
}
if(onsell.brandName > 0){//品牌
sb.append(" and ( brand.bid = "+onsell.brandName+" ");
if(onsell.seriesName > 0){//车系
sb.append(" and see.sid = "+onsell.seriesName+" ");
params.append("seriesName="+onsell.seriesName+"&");
request.setAttribute("seriesName", onsell.seriesName);
}
sb.append(" ) ");
params.append("brandName="+onsell.brandName+"&");
request.setAttribute("brandName", onsell.brandName);
}
if(onsell.carTypeName > 0){//外形
sb.append(" and vv.ctid = "+onsell.carTypeName+" ");
params.append("carTypeName="+onsell.carTypeName+"&");
request.setAttribute("carTypeName", onsell.carTypeName);
}
request.setAttribute("seriesName", onsell.seriesName);
request.setAttribute("carTypeName", onsell.carTypeName);
pageRequest.setWhereStr(sb.toString());
PageResult u = dealerOnsellService.findAllDealerCarBaseInfo(pageRequest);
Pager pager = new Pager();
pager.setCurPage(pageRequest.getPageNum());
pager.setPageSize(pageRequest.getPageSize());
pager.setTotalCount((int) (u.getTotal()));
pager.setTotalPage(u.getPages());
pager.setParams(params.toString());
pager.setCurUrl("selectDealerProduce.do");
u.setPagerStr(pager.doStartTag());
//获取全部外形
List<CarVehicleType> carVTypes = carVTypeService.getAllCarVehicleType();
//获取全部品牌
List<CarBrand> brands = carBrandService.selectAllCarBrand();
Map<String, Object> map = new HashMap<String, Object>();
map.put("pageresult", u);
map.put("carVTypes", carVTypes);
map.put("brands", brands);
return new ModelAndView("/admin/dealerOnSell/dealerOnSellItemList",map);
}
5. jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%> <%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt" %> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>经销商商品查询 </title> </head> <body> <div class="section"> <div class="position"><a href="${pageContext.request.contextPath}/admin/index.jsp" target="_top">首页</a> > 进销存管理> <a href="${pageContext.request.contextPath}/admin/carInfo/selectDealerProduce.do">经销商商品查询</a> </div> <div class="kl"> <form id="form1" action="${pageContext.request.contextPath }/admin/dealerOnsell/selectDealerProduce.do" method="POST"> <p class="fiter clearfix"> <span class="fTit left">商品信息：</span> <input type="text" name="carIDorName" id="carIDorName" placeholder="商品编号、商品名称" class="txt left" value="${carIDorName }"> </p> <p class="fiter clearfix"> <span class="fTit left">经销商信息：</span> <input type="text" name="dealerNameOrdealerCort" id="dealerNameOrdealerCort" placeholder="经销商名称、经销商账号" class="txt left" value="${dealerNameOrdealerCort }"> </p> <div class="clear"></div> <div class="fitShi"></div> <p class="fiter clearfix"> <span class="fTit left">品牌：</span> <select class="select left mr10" name="brandName" id="brandName" > <option value="0">请选择品牌</option> <c:forEach items="${brands }" var="brand"> <option value="${brand.bid }" <c:if test="${brandName == brand.bid}">selected="selected"</c:if> >${brand.brandName }</option> </c:forEach> </select> <span class="fTit left">车系：</span> <select class="select left mr10" name="seriesName" id="seriesName" style=" 139px;"> <option value="0">请选择车系</option> </select> <span class="fTit left">外形：</span> <select class="select left mr10" name="carTypeName" id="carTypeName"> <option value="0">请选择外形</option> <c:forEach items="${carVTypes }" var="carVType"> <option value="${carVType.ctid }"<c:if test="${carTypeName == carVType.ctid}">selected="selected"</c:if> >${carVType.carTypeName }</option> </c:forEach> </select> </p> <button type="submit" class="sBtn left" id="subDealerOnsell">查询</button> <button type="button" class="sBtn left" id="onclear">清空</button> <div class="clear"></div> </form> </div> <div class="mCon"> <div class="mBd"> <table width="100%" class="b6"> <tbody> <tr> <th>商品编号</th> <th>商品名称</th> <th>品牌</th> <th>车系</th> <th>车型</th> <th>外形</th> <th> 经销商名称 </th> <th>售价(元)</th> <th>库存</th> <th>操作</th> </tr> <c:forEach items="${pageresult.result}" var="result"> <tr lang="${result.carId }"> <td>${result.carId }</td> <td>${result.carName }</td> <td>${result.branName }</td> <td>${result.seriesName }</td> <td>${result.typeName }</td> <td>${result.carTypeName }</td> <td> ${result.dealerName } </td> <td>${result.carSellPrice }</td> <td>${result.carCount }</td> <td> <a href="${pageContext.request.contextPath }/admin/carInfo/getCarInfoForShowByCid.do?cid=${result.carId }&dealerId=${result.dealerId}" class="ck">查看</a> </td> </tr> </c:forEach> <c:if test="${empty pageresult.result}"> <tr class="tableHeader" align="center" height="37"> <td colspan="9" style="text-align:center;">暂无信息...</td> </tr> </c:if> </tbody> </table> </div> </div> <div id="Pagination" class="pageBox" style="margin-top: 100px;">${pageresult.pagerStr }</div> </div> <script type="text/javascript"> $(function(){ /** *清空 */ $("#onclear").click(function(){ $("#carIDorName").val(""); $("#brandName option:first").attr("selected","selected"); $("#seriesName option:first").attr("selected","selected"); $("#carTypeName option:first").attr("selected","selected"); $("#sell option:first").attr("selected","selected"); }); /** *级联 品牌-车系 */ $("#brandName").change(function(){ var brandCode = $(this).val(); $.ajax({ url:"brandChange.do", data:{brandCode:brandCode}, dataType:"json", success:function(data){ var arr = eval(data); var str = "<option value='0'>请选择车系</option>"; $("#seriesName").html(str+arr.option); } }); }); }); </script> </body> </html>