java中生成的mapper文件以及pagehelper处理关系型数据库

简介

使用反编译插件很方便，也很快捷，但是难免会遇到一些问题，比如我们在数据库中创建了两个表，这两个表具有相对应的关系，我们想要将这两个表的数据查询展示在同一个页面表格中。这个时候我们该如何使用mapper文件以及pagehelper进行查询分页呢？

话不多说，上代码---

pojo类

package com.lwxf.pojo;

import java.util.Date;

public class Item {
private String itemId;

private Integer itemCat;

private String itemName;

private String itemType;

private String itemFormat;

private Double itemBid;

private Double itemPrice;

private Integer itemCordon;

private String itemMeusure;

private String remark;

private Date createtime;

private Date updatetime;

private String suId;

public String getItemId() {
    return itemId;
}

public void setItemId(String itemId) {
    this.itemId = itemId == null ? null : itemId.trim();
}

public Integer getItemCat() {
    return itemCat;
}

public void setItemCat(Integer itemCat) {
    this.itemCat = itemCat;
}

public String getItemName() {
    return itemName;
}

public void setItemName(String itemName) {
    this.itemName = itemName == null ? null : itemName.trim();
}

public String getItemType() {
    return itemType;
}

public void setItemType(String itemType) {
    this.itemType = itemType == null ? null : itemType.trim();
}

public String getItemFormat() {
    return itemFormat;
}

public void setItemFormat(String itemFormat) {
    this.itemFormat = itemFormat == null ? null : itemFormat.trim();
}

public Double getItemBid() {
    return itemBid;
}

public void setItemBid(Double itemBid) {
    this.itemBid = itemBid;
}

public Double getItemPrice() {
    return itemPrice;
}

public void setItemPrice(Double itemPrice) {
    this.itemPrice = itemPrice;
}

public Integer getItemCordon() {
    return itemCordon;
}

public void setItemCordon(Integer itemCordon) {
    this.itemCordon = itemCordon;
}

public String getItemMeusure() {
    return itemMeusure;
}

public void setItemMeusure(String itemMeusure) {
    this.itemMeusure = itemMeusure == null ? null : itemMeusure.trim();
}

public String getRemark() {
    return remark;
}

public void setRemark(String remark) {
    this.remark = remark == null ? null : remark.trim();
}

public Date getCreatetime() {
    return createtime;
}

public void setCreatetime(Date createtime) {
    this.createtime = createtime;
}

public Date getUpdatetime() {
    return updatetime;
}

public void setUpdatetime(Date updatetime) {
    this.updatetime = updatetime;
}

public String getSuId() {
    return suId;
}

public void setSuId(String suId) {
    this.suId = suId == null ? null : suId.trim();
}

}

//库存商品类
package com.lwxf.pojo;

import java.util.Date;

public class ItemData {
private String dataId;

private Integer itemCount;

private String remark;

private Date createtime;

private Date updatetime;

private String itemeId;

private String itemName;

public String getDataId() {
    return dataId;
}

public void setDataId(String dataId) {
    this.dataId = dataId == null ? null : dataId.trim();
}

public Integer getItemCount() {
    return itemCount;
}

public void setItemCount(Integer itemCount) {
    this.itemCount = itemCount;
}

public String getRemark() {
    return remark;
}

public void setRemark(String remark) {
    this.remark = remark == null ? null : remark.trim();
}

public Date getCreatetime() {
    return createtime;
}

public void setCreatetime(Date createtime) {
    this.createtime = createtime;
}

public Date getUpdatetime() {
    return updatetime;
}

public void setUpdatetime(Date updatetime) {
    this.updatetime = updatetime;
}

public String getItemeId() {
    return itemeId;
}

public void setItemeId(String itemeId) {
    this.itemeId = itemeId == null ? null : itemeId.trim();
}

public String getItemName() {
    return itemName;
}

public void setItemName(String itemName) {
    this.itemName = itemName == null ? null : itemName.trim();
}

}

这里省略 xxxExample类 以及 xxxmapper文件

ItemExample.java
ItemDataExample.java
ItemDataMapper.java
ItemMapper.java
ItemDataMapper.xml
ItemMapper.xml

service层

因为这里用不到itemService 所以省略 StockServiceImpl 即itemDate的service层


package com.lwxf.service.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.lwxf.mapper.ItemDataMapper;
import com.lwxf.mapper.ItemMapper;
import com.lwxf.pojo.Item;
import com.lwxf.pojo.ItemData;
import com.lwxf.pojo.ItemDataExample;
import com.lwxf.pojo.ItemExample;
import com.lwxf.pojo.ItemExample.Criteria;
import com.lwxf.pojo.TbDistributor;
import com.lwxf.pojo.TbDistributorExample;
import com.lwxf.service.StockService;
import com.lwxf.util.IDUtils;
import com.lwxf.util.ItemAndData;
import com.lwxf.util.Result;
import com.lwxf.util.StringUtil;
@Service
public class StockServiceImpl implements StockService {
@Autowired
private ItemDataMapper itemDataMapper;
@Autowired
private ItemMapper itemMapper;

@Override
public Result getList2(int page, int rows, String itemName, Integer itemCat) {

    ItemExample example= new ItemExample();
    Criteria createCriteria = example.createCriteria();
    List<Item> list = null;
    if (itemCat != null) {
        createCriteria.andItemCatEqualTo(itemCat);
    }
    if (StringUtil.isNotEmpty(itemName)) {
        createCriteria.andItemNameLike('%' + itemName + '%');
    }
    list = itemMapper.selectByExample(example);
    //如果两个条件一起没查到  就按照第一个条件查
    if (list.size()==0) {
        createCriteria.andItemCatEqualTo(itemCat);
        list = itemMapper.selectByExample(example);
    }
    List<String> strlist = new ArrayList<>();
    for (Item item : list) {
        String itemName2 = item.getItemName();
        strlist.add(itemName2);
    }

    ItemDataExample example2= new ItemDataExample();
    //如果还是查不到的话 就直接 让list返回null
    if (strlist.size()!=0) {
        example2.createCriteria().andItemNameIn(strlist);
    }else {
        example2.createCriteria().andItemCountEqualTo(-1);
    }
    PageHelper.startPage(page, rows);
    List<ItemData> itemDatas = itemDataMapper.selectByExample(example2);
    //将两个实体类合并
    List<ItemAndData> itemAndDatas = new ArrayList<ItemAndData>();

    for (ItemData itemData : itemDatas) {
        for (Item item : list) {
            if (item!=null&&itemData!=null&&itemData.getItemeId().equals(item.getItemId())) {
                ItemAndData itemAndData = new ItemAndData();
                itemAndData.setItem(item);
                itemAndData.setItemData(itemData);
                itemAndDatas.add(itemAndData);
            }
        }
    }
    Result result = new Result();
    result.setRows(itemAndDatas);
    PageInfo<ItemData>  pageInfo=new PageInfo<ItemData>(itemDatas);
    result.setPageInfo(pageInfo);
    long total = pageInfo.getTotal();
    result.setTotal(total);
    return result;
}

}

Controller层

StockController就是itemDate的控制层

package com.lwxf.controller;

import java.util.Date;

import javax.servlet.http.HttpServletRequest;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;

import com.lwxf.pojo.Item;
import com.lwxf.pojo.ItemData;
import com.lwxf.service.ItemService;
import com.lwxf.service.StockService;
import com.lwxf.util.IDUtils;
import com.lwxf.util.ItemAndData;
import com.lwxf.util.Result;

@Controller
@RequestMapping("admin")
public class StockController {
@Autowired
private StockService stockService;
@Autowired
private ItemService itemService;


@RequestMapping(value="/toGoodsList")
public String orderList(Model model,Integer pageNum,String itemName, Integer itemCat ){
    Result result = stockService.getList2(pageNum, 1, itemName,itemCat);        
    if (result!=null) {
        model.addAttribute("result", result);
        model.addAttribute("itemCat", itemCat);
        return "list";
    }
    return "page/errors";
}

}

}

所需要的工具类ItemAndData 和Result

package com.lwxf.util;

import com.lwxf.pojo.Item;
import com.lwxf.pojo.ItemData;

/*
 * 将item 和itemdata两个实体类合到一起
 */
public class ItemAndData {
    private Item item;
    private ItemData itemData;
    public Item getItem() {
        return item;
    }
    public void setItem(Item item) {
        this.item = item;
    }
    public ItemData getItemData() {
        return itemData;
    }
    public void setItemData(ItemData itemData) {
        this.itemData = itemData;
    }

}


package com.lwxf.util;

import java.io.Serializable;
import java.util.List;

import com.github.pagehelper.PageInfo;



public class Result implements Serializable{

/**
 * 处理pagehelper的工具类
 */
private static final long serialVersionUID = 8075356866146520829L;
private long total;
private List rows;
private PageInfo pageInfo;

public PageInfo getPageInfo() {
    return pageInfo;
}
public void setPageInfo(PageInfo pageInfo) {
    this.pageInfo = pageInfo;
}
public long getTotal() {
    return total;
}
public void setTotal(long total) {
    this.total = total;
}
public List getRows() {
    return rows;
}
public void setRows(List rows) {
    this.rows = rows;
}


}

list.jsp文件

<div class="table-responsive">
                <table class="table table-striped table-bordered table-hover" style="text-align:center;">
                <tr>
                    <td>编号</td>
                    <td>商品所在库</td>
                    <td>商品编号</td>
                    <td>商品名称</td>
                    <td >商品数量</td>
                    <td>备注</td>
                    <td>创建时间</td>
                    <td>修改时间</td>
                    <td>操作</td>

                </tr>

                <div class="put-tablenavol table-height">
                    <c:forEach items="${result.rows}" var="itemAndData" varStatus="vs">            
                    <tr>
                                        <td></td>
                            <c:choose>
                                    <c:when test="${itemAndData.item.itemCat=='1' }">
                                        <td  style="width: 8%;">板材库</td>
                                    </c:when>
                                    <c:when test="${itemAndData.item.itemCat=='2' }">
                                        <td  style="width: 8%;">五金库</td>
                                    </c:when>
                                    <c:when test="${itemAndData.item.itemCat=='3' }">
                                        <td  style="width: 8%;">特供实木库</td>
                                    </c:when>
                                    <c:otherwise>
                                        <td  style="width: 8%;">成品库</td>
                                    </c:otherwise>
                                </c:choose>
                            <td>${itemAndData.itemData.dataId}</td>

                            <td><a class="chakanbut jax"  id="${itemAndData.itemData.itemName}"  >${itemAndData.itemData.itemName}</a></td>


                            <c:choose>
                                    <c:when test="${itemAndData.itemData.itemCount<itemAndData.item.itemCordon}">
                                            <td style="color: red">${itemAndData.itemData.itemCount}</td>
                                    </c:when>
                                    <c:otherwise>
                                            <td>${itemAndData.itemData.itemCount}</td> 
                                    </c:otherwise>
                            </c:choose>
                            <td>${itemAndData.itemData.remark}</td>
                            <td><fmt:formatDate value="${itemAndData.itemData.createtime}" pattern="yyyy-MM-dd" /></td>
                            <td><fmt:formatDate value="${itemAndData.itemData.updatetime}" pattern="yyyy-MM-dd" /></td>
                            <td style="margin-top:5px">
                                    <div class="table-but">
                                            <a title="入库" href="${pageContext.request.contextPath }/admin/toGoodsToIn?dataId=${itemAndData.itemData.dataId}">入库</a>
                                            <a title="出库"  href="${pageContext.request.contextPath }/admin/toGoodsToOut?dataId=${itemAndData.itemData.dataId}" >出库</a>
                                            <a class="colse del" title="删除"  href="${pageContext.request.contextPath }/admin/toGoodsDelete?dataId=${itemAndData.itemData.dataId}">删除</a>
                                    </div>
                            </td>
                    </tr>
            </c:forEach>
        </div>
  </table>


            </div>


<div class="table-bottom">
            <!--分页-->
            <div class="m-but"
                style="width: 90%; min-width: 1200px; margin: 10px auto;">

                <c:choose>
                        <c:when test="${result.pageInfo.pageNum==result.pageInfo.pages}">
                                <span class="m-butnext">下一页</span>
                        </c:when>
                        <c:otherwise>
                                <span class="m-butprev"><a
                                href="${pageContext.request.contextPath }/admin/toGoodsList?itemCat=${itemCat }&&pageNum=${result.pageInfo.nextPage}">下一页</a>
                            </span>
                        </c:otherwise>
                </c:choose>

                <div class="m-ul">
                    <ul>
                        <c:forEach begin="${result.pageInfo.firstPage}"
                            end="${result.pageInfo.lastPage}" var="num">
                            <li><a
                                href="${pageContext.request.contextPath }/admin/toGoodsList?itemCat=${itemCat }&&pageNum=${num}">${num}</a></li>
                        </c:forEach>

                    </ul>

                </div>



                <c:choose>
                        <c:when test="${result.pageInfo.pageNum=='1'}">
                                <span class="m-butprev">上一页</span>
                        </c:when>
                        <c:otherwise>
                                <span class="m-butprev"><a
                                href="${pageContext.request.contextPath }/admin/toGoodsList?itemCat=${itemCat }&&pageNum=${result.pageInfo.prePage}">上一页</a>
                            </span>
                        </c:otherwise>
                </c:choose>


                <div class="m-tleft">
                    <small>当前：</small> <input type="text" name="pageNum" id="pageNum" class="thisPage"
                        placeholder="${result.pageInfo.pageNum}"
                        /> <small>页</small>
                    <a class="tiao" id="ymtz"> 跳转 </a>
                    <span class="m-butprev"><a
                                href="${pageContext.request.contextPath }/admin/toGoodsList?itemCat=${itemCat }&&pageNum=1">首页</a>
                            </span>
                    <script type="text/javascript">
                        $(document).ready(function () {
                            $("#ymtz").click(function(){
                                window.location.href="${pageContext.request.contextPath }/admin/toGoodsList?"+"itemCat="+${itemCat }+"&&pageNum="+$("#pageNum").val();
                            });
                            $("#search").click(function(){
                                window.location.href="${pageContext.request.contextPath }/admin/toGoodsList?itemName="+$("#itemName3").val()+"&&itemCat="+$("#itemCat3").val()+"&&pageNum=1";
                            });
                        });



                        </script>

                </div>
            </div>

pom.xml文件

    <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.lwxf</groupId>
  <artifactId>lwxf</artifactId>
  <packaging>war</packaging>
  <version>0.0.1-SNAPSHOT</version>
  <name>lwxf Maven Webapp</name>
  <url>http://maven.apache.org</url>
  <dependencies>
    <!-- 分页工具 -->
    <dependency>
        <groupId>com.github.pagehelper</groupId>
        <artifactId>pagehelper</artifactId>
        <version>3.4.2-fix</version>
    </dependency>
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>javax.servlet</groupId>
        <artifactId>javax.servlet-api</artifactId>
        <version>3.1.0</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.15</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/commons-codec/commons-codec -->
    <dependency>
        <groupId>commons-codec</groupId>
        <artifactId>commons-codec</artifactId>
        <version>1.10</version>
    </dependency>
    <dependency>
        <groupId>javax.servlet</groupId>
        <artifactId>jstl</artifactId>
        <version>1.2</version>
    </dependency>
    <!-- 配置JSON相关的开发包 -->
    <dependency>
        <groupId>net.sf.json-lib</groupId>
        <artifactId>json-lib</artifactId>
        <version>2.4</version>
        <classifier>jdk15</classifier>
    </dependency>
    <dependency>
        <groupId>net.sf.ezmorph</groupId>
        <artifactId>ezmorph</artifactId>
        <version>1.0.6</version>
    </dependency>
    <dependency>
        <groupId>commons-lang</groupId>
        <artifactId>commons-lang</artifactId>
        <version>2.6</version>
    </dependency>
    <dependency>
        <groupId>commons-beanutils</groupId>
        <artifactId>commons-beanutils</artifactId>
        <version>1.9.2</version>
    </dependency>
    <dependency>
        <groupId>commons-collections</groupId>
        <artifactId>commons-collections</artifactId>
        <version>3.2.1</version>
    </dependency>
    <!-- 配置Spring相关的开发包 -->
    <dependency>
        <groupId>commons-io</groupId>
        <artifactId>commons-io</artifactId>
        <version>2.5</version>
    </dependency>
    <dependency>
        <groupId>commons-fileupload</groupId>
        <artifactId>commons-fileupload</artifactId>
        <version>1.3.2</version>
    </dependency>
    <dependency>
        <groupId>org.quartz-scheduler</groupId>
        <artifactId>quartz</artifactId>
        <version>2.2.3</version>
    </dependency>
    <dependency>
        <groupId>commons-logging</groupId>
        <artifactId>commons-logging</artifactId>
        <version>1.1.3</version>
    </dependency>
    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-api</artifactId>
        <version>1.7.21</version>
    </dependency>
    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-log4j12</artifactId>
        <version>1.7.21</version>
    </dependency>
    <dependency>
        <groupId>com.fasterxml.jackson.core</groupId>
        <artifactId>jackson-databind</artifactId>
        <version>2.7.4</version>
    </dependency>
    <dependency>
        <groupId>com.fasterxml.jackson.core</groupId>
        <artifactId>jackson-core</artifactId>
        <version>2.7.4</version>
    </dependency>
    <dependency>
        <groupId>com.fasterxml.jackson.core</groupId>
        <artifactId>jackson-annotations</artifactId>
        <version>2.7.4</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-core</artifactId>
        <version>4.3.3.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-beans</artifactId>
        <version>4.3.3.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-tx</artifactId>
        <version>4.3.3.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-context</artifactId>
        <version>4.3.3.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-context-support</artifactId>
        <version>4.3.3.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-web</artifactId>
        <version>4.3.3.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-webmvc</artifactId>
        <version>4.3.3.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-aop</artifactId>
        <version>4.3.3.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-aspects</artifactId>
        <version>4.3.3.RELEASE</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>4.3.3.RELEASE</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.1.5</version>
    </dependency>
    <dependency>
        <groupId>com.mchange</groupId>
        <artifactId>mchange-commons-java</artifactId>
        <version>0.2.12</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.0.8</version>
    </dependency>
    <dependency>
        <groupId>quartz</groupId>
        <artifactId>quartz</artifactId>
        <version>1.5.2</version>
    </dependency>

    <!-- Mybatis开发包 -->
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.2.8</version>
    </dependency>
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis-spring</artifactId>
        <version>1.3.0</version>
    </dependency>
    <!-- 配置shiro的相关开发包 -->
    <!-- https://mvnrepository.com/artifact/org.apache.shiro/shiro-all -->
    <dependency>
        <groupId>org.apache.shiro</groupId>
        <artifactId>shiro-all</artifactId>
        <version>1.3.2</version>
    </dependency>
</dependencies>

\

总结

本人刚接触ssm框架不久，这个是使用maven以及反编译插件和pagehelper分页插件的合成项目，因为遇到多个表具有关联关系，但是难以解决，所以暂时想到使用合成类的方法，然后在pagehelper的分页中使用其中一个进行分页，在分页查询的数据中填充合成的实体类，也就是ItemAndData这个类，这个勉强也能使用插件也能使用反编译插件，各位大神，如果本篇文章有何不足之处，还望多多指点小弟。多谢》》》