Our last article completed the Cascade deletion of the purchase and sale contract, the goods and the attachment, this time we need to do the business is----require direct display of the contract and the number of goods and accessories
Last time we left a question:
We have to go to the next level or database every time we delete. No, we can show the list of contracts directly under each contract and the number of items attached to the goods, so as to avoid repeated to the next level to see the list of cumbersome operations.
So below we use SQL to write the number of goods under the contract:
Select COUNT (*) from Contract_product_cwhere contract_id= ' 928eb2ae-23ba-46e0-9ad0-054354f66af8 '
Get the number of attachments under the contract for the goods:
Select COUNT (*) from Ext_cproduct_cwhere contract_product_id in (select contract_product_id from Contract_product_c where contract_id= ' 928eb2ae-23ba-46e0-9ad0-054354f66af8 ')
We add a column to the contract information for each line under the list of purchase and sale contracts, specifically to show "number of Goods/attachments" (omitted before and after HTML code):
<span style= "White-space:pre" ></span><tr><td class= "TableHeader" > Number of goods/accessories </td></ Tr><tr><td>${o.cpnum}/${o.extnum}</td></tr>
Effect
So this requires that when we take out the list of sales and purchase contract should be taken out of the number of goods/accessories, then we have to write a SQL synthesis of the two scattered SQL we have just written, so that we can take out a purchase and sale of a contract of goods/accessories number:
Select (SELECT COUNT (*) from Contract_product_c where contract_id=c.contract_id) as Cpnum, (SELECT count ( *) from Ext_cproduct_c where contract_product_id in (select contract_product_id from contract_product_c where contract_id=c.contract_id) as Extnum, C.*from contract_c C
You can see that we have added two new fields-the number of goods cpnum and the number of attachments extnum, but there are no two fields in resultmapping of our contractmapper mapping file:
<mapper namespace= "Cn.hpu.jk.mapper.ContractMapper" ><resultmap type= "Cn.hpu.jk.domain.Contract" id= " CONTRACTRM "><id property=" id "column=" contract_id "/><result property=" offeror "column=" Offeror "/> <result property= "Contractno" column= "Contract_no"/><result property= "signingdate" column= "SIGNING_DATE"/ ><result property= "Inputby" column= "input_by"/><result property= "Checkby" column= "CHECK_BY"/>< Result property= "Inspector" column= "Inspector"/><result property= "TotalAmount" column= "Total_amount"/> <result property= "Importnum" column= "Import_num"/><result property= "Crequest" column= "Crequest"/>< Result property= "Customname" column= "Custom_name"/><result property= "Deliveryperiod" column= "DELIVERY_PERIOD "/><result property=" Shiptime "column=" Ship_time "/><result property=" tradeterms "column=" TRADE_TERMS "/ ><result property= "Remark" column= "remark"/><result property= "Printstyle" column= "print_sTyle "/><result property=" oldstate "column=" old_state "/><result property=" state "column=" state "/> <result property= "outstate" column= "out_state"/><result property= "CreateBy" column= "CREATE_BY"/>< Result property= "creatdept" column= "create_dept"/><result property= "Creattime" column= "CREATE_TIME"/>< /resultmap><!--querying multiple--><select id= "find" parametertype= "map" resultmap= "CONTRACTRM" >select * from Contract_cwhere 1=1</select>
So we're going to add the fields, and our entities are adding both fields (here our entities change because our entities are just VO objects, not PO objects)
Referring to Vo and Po, we have a few more words here:
What is the difference between "interview" PO, VO, and Bo?
PO persistence objects, typically direct object database tables
VO View object, usually corresponding to page JSP
BO business objects, generally corresponding to complex business
OK, here we begin to modify our Find configuration statement, and then increase the number of goods cpnum and the number of attachments Extnum result:
</pre><pre name= "code" class= "HTML" ><mapper namespace= "Cn.hpu.jk.mapper.ContractMapper" >< Resultmap type= "Cn.hpu.jk.domain.Contract" id= "CONTRACTRM" ><id property= "id" column= "contract_id"/>< Result property= "Offeror" column= "offeror"/><!--virtual field--><result property= "Contractno" column= "CONTRACT_ NO "/><result property=" Cpnum "column=" Cpnum "/><result property=" Extnum "column=" Extnum "/><result Property= "Signingdate" column= "signing_date"/><result property= "Inputby" column= "INPUT_BY"/><result Property= "Checkby" column= "check_by"/><result property= "Inspector" column= "Inspector"/><result Property= "TotalAmount" column= "Total_amount"/><result property= "Importnum" column= "IMPORT_NUM"/>< Result property= "Crequest" column= "crequest"/><result property= "Customname" column= "Custom_name"/>< Result property= "Deliveryperiod" column= "Delivery_period"/><result property= "Shiptime" column= "SHIP_TIME "/><result property=" tradeterms "column=" trade_terms "/><result property=" remark "column=" remark "/ ><result property= "Printstyle" column= "Print_style"/><result property= "oldState" column= "OLD_STATE"/ ><result property= "state" column= "state"/><result property= "outstate" column= "Out_state"/>< Result property= "CreateBy" column= "create_by"/><result property= "creatdept" column= "Create_dept"/>< Result property= "Creattime" column= "Create_time"/></resultmap><!--querying multiple--><select id= "Find" Parametertype= "Map" resultmap= "CONTRACTRM" >select (SELECT COUNT (*) from Contract_product_c where contract_i d=c.contract_id) as Cpnum, (select COUNT (*) from Ext_cproduct_c where contract_product_id in (select Contract_pro duct_id from Contract_product_c where contract_id=c.contract_id) as Extnum, c.* from Contract_c c</s Elect><mapper>
We're done here, and we're going to modify our entities:
As you can see, our number of goods cpnum and the number of attachments Extnum is a string type, but in fact the number of our database statistics and should be an int or even a long, it is important to note that mybatis it does not take the database out of the data type as the final type, Instead, it is the most final type of the type in the entity class, that is, hard to convert the data to the type specified by the entity class. Now we're only looking at it because we're not counting, so for the sake of simplicity, we just need a string type of data:
Package Cn.hpu.jk.domain;import Java.util.date;public class Contract {private string Id;private string offeror;// Acquirer private string contractno;//contract number private string Cpnum;private string Extnum;private java.util.Date signingdate;// Signing Date private string inputby;//single private string checkby;//trial single private string inspector;//Inspector Private Double totalamount;/ /Total amount Private Integer importnum;//critical degree private string crequest;//requires private string customname;private java.util.Date deliveryperiod;//Delivery term private java.util.Date shiptime;//sailing Schedule private string tradeterms;//trade terms private string remark;// Description private String printstyle;//Print Layout Private integer oldstate;//pre-archive State private integer state;//State private integer outstate;/ /Shipment Status private string Createby;private string Creatdept;private java.util.Date Creattime;//...get and set method omitted}
We are reviewing the JSP page:
<%@ page language= "java" pageencoding= "UTF-8"%><%@ include file= ". /.. /baselist.jsp "%><%@ taglib uri=" http://java.sun.com/jsp/jstl/fmt "prefix=" FMT "%>
Let's restart the server to see:
Our goods and accessories display function is successful!
Reprint Please specify source: http://blog.csdn.net/acmman/article/details/48627483
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
"Springmvc+mybatis project" Jie Xin Business-20. Number of contracts and number of annexes