[springmvc+mybatis项目实战]杰信商贸22.合同货物附 -电脑资料

    我们上一次完成了合同的总金额的计算，我们这次继续完成一个新的更复杂，但是更有学习意义的业务---购销合同查看

    要求:查看合同的主信息，查看合同下的货物信息，还要查看附件信息，货物和附件的信息要显示出它们的关联关系，

[springmvc+mybatis项目实战]杰信商贸22.合同货物附

    (注:一个合同下有多个货物，每个货物又有每个货物自己的附件)

    我们之前都是使用的外键进行的关联，这里我们将外键改为实实在在的一个对象，到时候通过对象属性来取下一级的数据，假设之前我们表中有一个货物的外键private String contract_product_id，这个时候我们可以改为一个货物对象:

    private ContractProduct cp;

    这样我们取联合数据的时候就比较方便了。

    首先我们先给合同Contract来创建一个VO对象，用于封装从购销合同中取出的货物的信息:

package cn.hpu.jk.vo;import java.util.List;import cn.hpu.jk.domain.ContractProduct;public class ContractVO {	private String id;		private List<contractproduct>contractProducts;//货物的集合		private String offeror;//收购方	private String contractNo;//合同编号		private String cpnum;	private String extnum;		private java.util.Date signingDate;//签单日期	private String inputBy;//制单人	private String checkBy;//审单人	private String inspector;//验货员	private Double totalAmount;//总金额	private Integer importNum;//重要程度	private String crequest;//要求	private String customName;	private java.util.Date deliveryPeriod;//交货期限	private java.util.Date shipTime;//船期	private String tradeTerms;//贸易条款	private String remark;//说明	private String printStyle;//打印版式	private Integer oldState;//归档前状态	private Integer state;//状态	private Integer outState;//走货状态	private String createBy;	private String creatDept;	private java.util.Date creatTime;		//...get和set方法省略	}</contractproduct>

    然后给货物ContractProduct配一个VO对象，将外键全部换成实体类:

package cn.hpu.jk.vo;import java.util.List;import cn.hpu.jk.domain.Contract;import cn.hpu.jk.domain.ExtCproduct;import cn.hpu.jk.domain.Factory;public class ContractProductVO {	private String id;		private Contract contract;//将复杂的关联关系变成单表操作	private List<extcproduct>extCproducts;//和附件一对多	private Factory factory;//和生产厂家多对一		private String factoryName;	private String productNo;	private String productImage;	private String productDesc;//货物描述	private Integer cnumber;//数量	private Integer outNumber;//出货数量	private String loadingRate;//装率	private String boxNum;//箱数	private String packingUnit;//包装单位	private Double price;//单价	private Double amount;//总价	private Integer finished;//是否出货完毕	private String exts;//附件	private Integer orderNo;//排序号	//...get和set方法省略	}</extcproduct>

    然后给附件ExtCproduct配一个VO对象，将外键全部换成实体类:

package cn.hpu.jk.vo;import cn.hpu.jk.domain.ContractProduct;import cn.hpu.jk.domain.Factory;public class ExtCproductVO {	private String id;		private ContractProduct contractProduct;//多对1	private Factory factory;//一对多		private String ctype;	private String productNo;	private String productImage;	private String productDesc;//货物描述	private Integer cnumber;//数量	private String packingUnit;//包装单位	private Double price;//单价	private Double amount;//总价	private String productRequest;//要求	private Integer orderNo;//排序号	//...get和set方法省略	}

    我们的面相对像的方式全配好了，下面就开始陪我们的Mapper

    接下来更改购销合同的映射文件ContractMapper.xml的映射结果集ResultMap，让它能够取出一个货物信息之后能把货物信息(货物中包含附件信息)封装到一个实体里:

<resultmap id="contractRM" type="cn.hpu.jk.domain.Contract"><id column="CONTRACT_ID/" property="id"><result column="OFFEROR/" property="offeror"><result column="CONTRACT_NO/" property="contractNo"><result column="CPNUM/" property="cpnum"><result column="EXTNUM/" property="extnum"><result column="SIGNING_DATE/" property="signingDate"><result column="INPUT_BY/" property="inputBy"><result column="CHECK_BY/" property="checkBy"><result column="INSPECTOR/" property="inspector"><result column="TOTAL_AMOUNT/" property="totalAmount"><result column="IMPORT_NUM/" property="importNum"><result column="CREQUEST/" property="crequest"><result column="CUSTOM_NAME/" property="customName"><result column="DELIVERY_PERIOD/" property="deliveryPeriod"><result column="SHIP_TIME/" property="shipTime"><result column="TRADE_TERMS/" property="tradeTerms"><result column="REMARK/" property="remark"><result column="PRINT_STYLE/" property="printStyle"><result column="OLD_STATE/" property="oldState"><result column="STATE/" property="state"><result column="OUT_STATE/" property="outState"><result column="CREATE_BY/" property="createBy"><result column="CREATE_DEPT/" property="creatDept"><result column="CREATE_TIME/" property="creatTime"></result></result></result></result></result></result></result></result></result></result></result></result></result></result></result></result></result></result></result></result></result></result></result></id></resultmap><resultmap extends="contractRM" id="ContractVORM" type="cn.hpu.jk.vo.ContractVO"><collection ftype="cn.hpu.jk.vo.ContractProductVO" property="contractProducts"><id column="CONTRACT_PRODUCT_ID/" property="id"><result column="PRODUCT_NO/" property="productNo"><result column="PRODUCT_IMAGE/" property="productImage"><result column="PRODUCT_DESC/" property="productDesc"><result column="CNUMBER/" property="cnumber"><result column="OUT_NUMBER/" property="outNumber"><result column="LOADING_RATE/" property="loadingRate"><result column="BOX_NUM/" property="boxNum"><result column="PACKING_UNIT/" property="packingUnit"><result column="PRICE/" property="price"><result column="AMOUNT/" property="amount"><result column="FINISHED/" property="finished"><result column="EXTS/" property="exts"><result column="ORDER_NO/" property="orderNo"><collection ftype="cn.hpu.jk.vo.ExtCproductVO" property="extCproducts"><id column="EXT_CPRODUCT_ID/" property="id"><result column="CTYPE/" property="ctype"><result column="EXT_PRODUCT_NO/" property="productNo"><result column="EXT_PRODUCT_IMAGE/" property="productImage"><result column="EXT_PRODUCT_DESC/" property="productDesc"><result column="EXT_CNUMBER/" property="cnumber"><result column="EXT_PACKING_UNIT/" property="packingUnit"><result column="EXT_PRICE/" property="price"><result column="EXT_AMOUNT/" property="amount"><result column="EXT_PRODUCT_REQUEST/" property="productRequest"><result column="EXT_ORDER_NO/" property="orderNo"><id column="EXT_FACTORY_ID/" property="id"><result column="EXT_FULL_NAME/" property="fullName"><result column="EXT_FACTORY_NAME/" property="factoryName"><result column="EXT_CONTACTS/" property="contacts"><result column="EXT_PHONE/" property="phone"></result></result></result></result></id></result></result></result></result></result></result></result></result></result></result></id></collection></result></result></result></result></result></result></result></result></result></result></result></result></result></id></collection></resultmap>

    我们接下来开始写SQL语句，开实现查看合同的主信息，查看合同下的货物信息，还要查看附件信息，货物和附件的信息要显示出它们的关联关系

    注意:合同、货物、附件、生产厂家构建复杂多级关联的SQL时原则

    1）挑选最小的结果集

    2）滚雪球（左连接），逐步往上加内容

    3）重复字段，需要起别名（mybatis）

    在实际业务中几乎没有内连接的情况，直接就用左连接即可。

    (关于左连接、内连接等介绍请查看http://blog.csdn.net/acmman/article/details/48575049)

    首先我们将货物和生产厂家拼接起来:

selectcp.CONTRACT_PRODUCT_ID,cp.PRODUCT_NO,cp.PRODUCT_IMAGE,cp.PRODUCT_DESC,cp.CNUMBER,cp.OUT_NUMBER,cp.LOADING_RATE,cp.BOX_NUM,cp.PACKING_UNIT,cp.PRICE,cp.AMOUNT,cp.FINISHED,cp.EXTS,cp.ORDER_NO,  f.factory_id,f.full_name,f.factory_name,f.contacts,f.phonefrom(selectCONTRACT_PRODUCT_ID,FACTORY_ID,PRODUCT_NO,PRODUCT_IMAGE,PRODUCT_DESC,CNUMBER,OUT_NUMBER,LOADING_RATE,BOX_NUM,PACKING_UNIT,PRICE,AMOUNT,FINISHED,EXTS,ORDER_NOfrom contract_product_c)cpleft join(select factory_id,full_name,factory_name,contacts,phone from factory_c)fon cp.factory_id=f.factory_id

    然后我们将附件和生产厂家拼接起来:

selectext.EXT_CPRODUCT_ID,ext.CONTRACT_PRODUCT_ID,ext.CTYPE,ext.PRODUCT_NO,ext.PRODUCT_IMAGE,ext.PRODUCT_DESC,ext.CNUMBER,  ext.PACKING_UNIT,ext.PRICE,ext.AMOUNT,ext.PRODUCT_REQUEST,ext.ORDER_NO,  f.factory_id,f.full_name,f.factory_name,f.contacts,f.phonefrom(select EXT_CPRODUCT_ID,CONTRACT_PRODUCT_ID,FACTORY_ID,CTYPE,PRODUCT_NO,PRODUCT_IMAGE,PRODUCT_DESC,CNUMBER,PACKING_UNIT,PRICE,AMOUNT,PRODUCT_REQUEST,ORDER_NOfrom ext_cproduct_c)extleft join(select factory_id,full_name,factory_name,contacts,phone from factory_c)fon ext.factory_id=f.factory_id

    接下来继续滚雪球，我们要把上面两段SQL给拼接起来：

select c.contract_id,c.offeror,c.contract_no,c.signing_date,c.input_by,c.check_by,c.inspector,c.total_amount,c.import_num,c.crequest,c.custom_name,c.delivery_period,c.ship_time,c.trade_terms,c.remark,c.print_style,c.old_state,c.state,c.out_state,c.create_by,c.create_dept,c.create_time,       t.contract_product_id,t.product_no,t.product_image,t.product_desc,t.cnumber,t.out_number,t.loading_rate,t.box_num,t.packing_unit,t.price,t.amount,t.finished,t.exts,t.cp_order_no,t.factory_id,t.full_name,t.factory_name,t.contacts,t.phone,              t.ext_cproduct_id,t.ctype,t.ext_product_no,t.ext_product_image,t.ext_product_desc,t.ext_cnumber,t.ext_packing_unit,t.ext_price,t.ext_amount,t.ext_product_request,t.ext_order_no,t.ext_factory_id,t.ext_full_name,t.ext_factory_name,t.ext_contacts,t.ext_phonefrom(  select     contract_id,offeror,contract_no,signing_date,input_by,check_by,inspector,total_amount,import_num,crequest,custom_name,delivery_period,  ship_time,trade_terms,remark,print_style,old_state,state,out_state,create_by,create_dept,create_time  from contract_c)cleft join(  select    cp.contract_product_id,cp.contract_id,    cp.product_no,cp.product_image,cp.product_desc,cp.cnumber,cp.out_number,cp.loading_rate,cp.box_num,cp.packing_unit,cp.price,    cp.amount,cp.finished,cp.exts,cp.order_no as cp_order_no,    cp.factory_id,cp.full_name,cp.factory_name,cp.contacts,cp.phone,          ext.ext_cproduct_id,ext.ctype,ext.product_no as ext_product_no,ext.product_image as ext_product_image,    ext.product_desc as ext_product_desc,ext.cnumber as ext_cnumber,ext.packing_unit as ext_packing_unit,ext.price as ext_price,    ext.amount as ext_amount,ext.product_request as ext_product_request,ext.order_no as ext_order_no,    ext.factory_id as ext_factory_id,ext.full_name as ext_full_name,ext.factory_name as ext_factory_name,    ext.contacts as ext_contacts,ext.phone as ext_phone  from  (      select        cp.contract_product_id,cp.contract_id,        cp.product_no,cp.product_image,cp.product_desc,cp.cnumber,cp.out_number,cp.loading_rate,cp.box_num,cp.packing_unit,cp.price,cp.amount,cp.finished,cp.exts,cp.order_no,        f.factory_id,f.full_name,f.factory_name,f.contacts,f.phone      from      (select      contract_product_id,contract_id,factory_id,      product_no,product_image,product_desc,cnumber,out_number,loading_rate,box_num,packing_unit,price,amount,finished,exts,order_no      from contract_product_c)cp      left join      (select factory_id,full_name,factory_name,contacts,phone from factory_c)f      on cp.factory_id=f.factory_id  )cp  left join  (      select        ext.ext_cproduct_id,ext.contract_product_id,ext.ctype,ext.product_no,ext.product_image,ext.product_desc,ext.cnumber,        ext.packing_unit,ext.price,ext.amount,ext.product_request,ext.order_no,        f.factory_id,f.full_name,f.factory_name,f.contacts,f.phone      from      (select       ext_cproduct_id,contract_product_id,factory_id,      ctype,product_no,product_image,product_desc,cnumber,packing_unit,price,amount,product_request,order_no      from ext_cproduct_c)ext      left join      (select factory_id,full_name,factory_name,contacts,phone from factory_c)f      on ext.factory_id=f.factory_id  )ext  on cp.contract_product_id=ext.contract_product_id)ton c.contract_id=t.contract_id

    过程中可能会出现这样的错误

    说明你的SQL语句里有重复字段，需要加别名

    接下来我们开始配置这个查询语句，在ContractMapper.xml中加入一下配置语句:

<select id="view" parametertype="string" resultmap="ContractVORM">select 	c.contract_id,c.offeror,c.contract_no,c.signing_date,c.input_by,c.check_by,c.inspector,c.total_amount,c.import_num,	c.crequest,c.custom_name,c.delivery_period,c.ship_time,c.trade_terms,c.remark,c.print_style,c.old_state,c.state,	c.out_state,c.create_by,c.create_dept,c.create_time,	       	t.contract_product_id,	t.product_no,t.product_image,t.product_desc,t.cnumber,t.out_number,t.loading_rate,t.box_num,t.packing_unit,t.price,t.amount,	t.finished,t.exts,t.cp_order_no,t.factory_id,t.full_name,t.factory_name,t.contacts,t.phone,	              	t.ext_cproduct_id,	t.ctype,t.ext_product_no,t.ext_product_image,t.ext_product_desc,t.ext_cnumber,t.ext_packing_unit,t.ext_price,t.ext_amount,	t.ext_product_request,t.ext_order_no,t.ext_factory_id,t.ext_full_name,t.ext_factory_name,t.ext_contacts,t.ext_phone	from	(	  select   	  contract_id,offeror,contract_no,signing_date,input_by,check_by,inspector,total_amount,import_num,crequest,custom_name,delivery_period,	  ship_time,trade_terms,remark,print_style,old_state,state,out_state,create_by,create_dept,create_time	  from contract_c	)c	left join	(	  select	    cp.contract_product_id,cp.contract_id,	    cp.product_no,cp.product_image,cp.product_desc,cp.cnumber,cp.out_number,cp.loading_rate,cp.box_num,cp.packing_unit,cp.price,	    cp.amount,cp.finished,cp.exts,cp.order_no as cp_order_no,	    cp.factory_id,cp.full_name,cp.factory_name,cp.contacts,cp.phone,	      	    ext.ext_cproduct_id,ext.ctype,ext.product_no as ext_product_no,ext.product_image as ext_product_image,	    ext.product_desc as ext_product_desc,ext.cnumber as ext_cnumber,ext.packing_unit as ext_packing_unit,ext.price as ext_price,	    ext.amount as ext_amount,ext.product_request as ext_product_request,ext.order_no as ext_order_no,	    ext.factory_id as ext_factory_id,ext.full_name as ext_full_name,ext.factory_name as ext_factory_name,	    ext.contacts as ext_contacts,ext.phone as ext_phone	  from	  (	      select	        cp.contract_product_id,cp.contract_id,	        cp.product_no,cp.product_image,cp.product_desc,cp.cnumber,cp.out_number,cp.loading_rate,cp.box_num,cp.packing_unit,cp.price,cp.amount,cp.finished,cp.exts,cp.order_no,	        f.factory_id,f.full_name,f.factory_name,f.contacts,f.phone	      from	      (select	      contract_product_id,contract_id,factory_id,	      product_no,product_image,product_desc,cnumber,out_number,loading_rate,box_num,packing_unit,price,amount,finished,exts,order_no	      from contract_product_c)cp	      left join	      (select factory_id,full_name,factory_name,contacts,phone from factory_c)f	      on cp.factory_id=f.factory_id	  )cp	  left join	  (	      select	        ext.ext_cproduct_id,ext.contract_product_id,ext.ctype,ext.product_no,ext.product_image,ext.product_desc,ext.cnumber,	        ext.packing_unit,ext.price,ext.amount,ext.product_request,ext.order_no,	        f.factory_id,f.full_name,f.factory_name,f.contacts,f.phone	      from	      (select 	      ext_cproduct_id,contract_product_id,factory_id,	      ctype,product_no,product_image,product_desc,cnumber,packing_unit,price,amount,product_request,order_no	      from ext_cproduct_c)ext	      left join	      (select factory_id,full_name,factory_name,contacts,phone from factory_c)f	      on ext.factory_id=f.factory_id	  )ext	  on cp.contract_product_id=ext.contract_product_id	)t	on c.contract_id=t.contract_id		where c.contract_id=#{contractId}</select>

    然后我们开始写我们的Dao层

    在ContractDao接口中加入下面方法:

//查询合同加关联关系的详细信息public ContractVO view(String contractId);

    然后在实现类ContractDaoImpl中加入以下方法

@Overridepublic ContractVO view(String contractId) {	return super.getSqlSession().selectOne(super.getNs()+.view, contractId);}

    接下来写Service层

    在ContractService接口中添加以下方法

public ContractVO view(String contractId); //带关联对象的查询

    然后在实现类ContractServiceImpl中加入以下方法

@Overridepublic ContractVO view(String contractId) {	return contractDao.view(contractId);}

    之后使我们的Controller层，我们修改我们之前写的查看方法

//查看方法@RequestMapping(/cargo/contract/toview.action)public String toview(String id,Model model){	ContractVO bj=contractService.view(id);	model.addAttribute(obj, obj);		return /cargo/contract/jContractView.jsp;}

    此时，我们之前的查看页面jContractView.jsp界面就不满足我们的需求了，我们同样要对它进行修改(我们在购销合同下面放置该合同下的货物列表，在每个货物下面放置了每个货物的附件信息):

<%@ page language=java pageEncoding=UTF-8%><%@ include file=../../base.jsp%><%@ include file=../../baselist.jsp%>

${obj.remark}

    下面我们来测试

    先打开购销合同列表，这里有两个购销合同，我们可以发现，第一个购销合同有2个货物和3个附件:

    点击查看之后获取到这个页面:

    我们可以清晰的看到购销合同的详细信息和购销合同下的货物的列表，以及每个货物的附件信息:

    至此，我们的购销合同关联关系信息的查询功能完成！

    注:此篇一定要反复看，SQL语句一定要自己敲一遍，SQL语句一定要自己敲一遍，SQL语句一定要自己敲一遍(重要的事情说三遍)