第四天 购销合同查看,SQL语句的Power
1.附件业务实现
随时携带主表的ID
2.购销合同查看
合同下货物,货物下面附件,在购销合同中全部浏览,体现货物和附件的关系。
构建对象关系
3.【面试】PO、VO、BO有什么区别?
PO 持久化对象,跟数据库表对应
VO 视图对象,跟页面对应
BO 业务对象,跟业务逻辑加工对应对象(比较少)
PO、VO、BO人为分类,它们本质没有什么区别,它们都是POJO。
构建复杂SQL时原则
1)从最小的结果集开始查询
2)将它们进行连接,用左连接
4.合同对象关联SQL
select
cp.contract_id,
cp.offeror,cp.contract_no,cp.signing_date,cp.input_by,cp.check_by,cp.inspector,cp.total_amount,cp.import_num,cp.crequest,cp.custom_name,
cp.delivery_period,cp.ship_time,cp.trade_terms,cp.remark,cp.print_style,cp.old_state,cp.state,cp.out_state,cp.create_by,cp.create_dept,cp.create_time,
cp.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,
cp.factory_id,cp.full_name,cp.factory_name,cp.contacts,cp.phone,
ep.ext_cproduct_id,ep.contract_product_id,
ep.ctype,
ep.product_no as ext_product_no,ep.product_image as ext_product_image,ep.product_desc as ext_product_desc,
ep.cnumber as ext_cnumber,ep.packing_unit as ext_packing_unit,ep.price as ext_price,
ep.amount as ext_amount,ep.product_request as ext_product_request,ep.order_no as ext_order_no,
ep.factory_id as ext_factory_id,ep.full_name as ext_full_name,ep.factory_name as ext_factory_name,ep.contacts as ext_contacts,ep.phone as ext_phone
from
(
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,
cp.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,
cp.factory_id,cp.full_name,cp.factory_name,cp.contacts,cp.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,
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
on c.contract_id=cp.contract_id
) cp
left join
(
select
ep.ext_cproduct_id,ep.contract_product_id,
ep.ctype,ep.product_no,ep.product_image,ep.product_desc,ep.cnumber,ep.packing_unit,ep.price,ep.amount,ep.product_request,ep.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
) ep
left join
( select factory_id,full_name,factory_name,contacts,phone from factory_c ) f
on ep.factory_id=f.factory_id
) ep
on cp.contract_product_id=ep.contract_product_id
注意MyBatis查询出的结果集中不能有同名字段,必须另起别名。
5.关联映射,一对多,多对一
<resultMap type="cn.itcast.jk.domain.Contract" id="contractRM">
<id property="id" column="CONTRACT_ID"/>
<result property="cpnum" column="CPNUM"/>
<result property="extnum" column="EXTNUM"/>
<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="createDept" column="CREATE_DEPT"/>
<result property="createTime" column="CREATE_TIME"/>
</resultMap>
<!-- 对象的关联关系,合同、货物、附件、生产厂家 -->
<resultMap type="cn.itcast.jk.vo.ContractVO" id="contractVORM" extends="contractRM">
<!-- 货物信息(一对多) -->
<collection property="contractProducts" ofType="cn.itcast.jk.vo.ContractProductVO">
<id column="CONTRACT_PRODUCT_ID" property="id" jdbcType="VARCHAR" />
<result column="FACTORY_ID" property="factoryId" jdbcType="VARCHAR" />
<result column="FACTORY_NAME" property="factoryName" jdbcType="VARCHAR" />
<result column="PRODUCT_NO" property="productNo" jdbcType="VARCHAR" />
<result column="PRODUCT_IMAGE" property="productImage" jdbcType="VARCHAR" />
<result column="PRODUCT_DESC" property="productDesc" jdbcType="VARCHAR" />
<result column="CNUMBER" property="cnumber" jdbcType="INTEGER" />
<result column="OUT_NUMBER" property="outNumber" jdbcType="INTEGER" />
<result column="LOADING_RATE" property="loadingRate" jdbcType="VARCHAR" />
<result column="BOX_NUM" property="boxNum" jdbcType="INTEGER" />
<result column="PACKING_UNIT" property="packingUnit" jdbcType="VARCHAR" />
<result column="PRICE" property="price" jdbcType="DOUBLE" />
<result column="AMOUNT" property="amount" jdbcType="DOUBLE" />
<result column="FINISHED" property="finished" jdbcType="INTEGER" />
<result column="EXTS" property="exts" jdbcType="VARCHAR" />
<result column="ORDER_NO" property="orderNo" jdbcType="INTEGER" />
<!-- 生产厂家(多对一)mybatis优化,按需要所取 -->
<association property="factory" javaType="cn.itcast.jk.domain.Factory">
<id property="id" column="FACTORY_ID"/>
<result property="fullName" column="FULL_NAME"/>
<result property="factoryName" column="FACTORY_NAME"/>
<result property="contacts" column="CONTACTS"/>
<result property="phone" column="PHONE"/>
</association>
<!-- 附件信息(一对多) -->
<collection property="extCproducts" ofType="cn.itcast.jk.vo.ExtCproductVO">
<id column="EXT_CPRODUCT_ID" property="id" jdbcType="VARCHAR" />
<result column="EXT_FACTORY_ID" property="factoryId" jdbcType="VARCHAR" />
<result column="EXT_FACTORY_NAME" property="factoryName" jdbcType="VARCHAR" />
<result column="EXT_CTYPE" property="ctype" jdbcType="INTEGER" />
<result column="EXT_PRODUCT_NO" property="productNo" jdbcType="VARCHAR" />
<result column="EXT_PRODUCT_IMAGE" property="productImage" jdbcType="VARCHAR" />
<result column="EXT_PRODUCT_DESC" property="productDesc" jdbcType="VARCHAR" />
<result column="EXT_CNUMBER" property="cnumber" jdbcType="INTEGER" />
<result column="EXT_PACKING_UNIT" property="packingUnit" jdbcType="VARCHAR" />
<result column="EXT_PRICE" property="price" jdbcType="DOUBLE" />
<result column="EXT_AMOUNT" property="amount" jdbcType="DOUBLE" />
<result column="EXT_PRODUCT_REQUEST" property="productRequest" jdbcType="VARCHAR" />
<result column="EXT_ORDER_NO" property="orderNo" jdbcType="INTEGER" />
<!-- 生产厂家(多对一)mybatis优化,按需要所取 -->
<association property="factory" javaType="cn.itcast.jk.domain.Factory">
<id property="id" column="EXT_FACTORY_ID"/>
<result property="fullName" column="EXT_FULL_NAME"/>
<result property="factoryName" column="EXT_FACTORY_NAME"/>
<result property="contacts" column="EXT_CONTACTS"/>
<result property="phone" column="EXT_PHONE"/>
</association>
</collection>
</collection>
</resultMap>
6.业务:货物数和附件数
select
(select count(*) from contract_product_c where contract_id=t.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=t.contract_id)) as extnum,
t.* from CONTRACT_C t
where 1=1
7.在使用hibernate和mybatis一些区别?
应用hibernate,对象映射,对象的关联关系。
应用mybatis,SQL,利用SQL直接查询所需要的内容,提高了获取数据的效率。SQL可以按照用户的业务来挑取所要的字段。
Hibernate当中SQL能否优化,一般情况无法优化SQL,因为hibernat使用HQL,它自动生成SQL语句。Mybatis可以实现手工SQL优化。
Hibernate开发重点,面向对象,来思考,来开发;全自动ORM
Mybatis开发重点,面向SQL,面向过程思考,面向对象的开发;半自动ORM
8.级联删除
在hibernate中只要配置映射文件中的对象关系,级联删除。当删除主对象时,根本不管是否有子对象,直接删除主对象就实现。
Hibernate底层自动删除相关的子对象。
合同、货物、附件两级主从。Hibernate中只关系上下级对象。
在mybatis中没有级联删除的概念,只能手工去删除。引发骨牌效应。
9.业务,购销合同查询
利用状态,可以控制登陆人看到不同的信息。数据权限控制
10.*细粒度权限控制
主菜单,左侧菜单,按钮,URL,方法,业界都称为粗粒度的权限控制。
日常的小系统,基于用户,角色,权限架构足够用。
在实际大型项目中,光有上面的用户角色,URL这样的控制粒度不够细致,还有数据访问权限。
1)对数据的访问列是可以自己定制的。
例如:访问一个人员工资信息表,这个表中含有人员的工资。
2)对数据的访问行可以受权限控制。
例如:工资表中有销售部人员的工资信息,还有船运部人员的工资信息。当销售部领导登录时,他只能看到销售人员的工资信息,他不能看到船运部人员的工资信息。
3)直接浏览数据库表,工资列不能直接看到其值。对这个数据库列进行加密。
上面统称为数据访问权限,这个权限加上角色用户这样这个系统才比较安全。也称为细粒度的权限控制。
a)列访问控制:
单独建立表,权限分配某个角色能访问哪些列;
b)行访问控制:
1)只能看自己创建的记录,不能看到别人创建的记录
Where create_by = 当前登陆人ID
2)部门领导看到,他能看到本部门人员创建的记录,不能看到其他部门人员创建的记录
Where create_dept = 当前登陆人部门ID and user_level <4
3)跨部门访问权限,分管领导跨部门,跨人员
再加一个配置表,分管领导可以管理的人和部门;
11.练习:
1.合同、货物、附件、生产厂家对象关联SQL
2.购销合同列表实现货物数和附件数,合同总金额 SQL语句
合同总金额=所有当前合同下的货物的数量单价总和+所有附件的数量单价总和
3.对象关联的映射
12.重点知识回顾
a)购销合同查看,采用类似hibernate方式,都已对象关联方式。
1.PO为了利用mybatis性能,在创建时,没有采用对象关联,而是将对象关联字段,也就是外键,利用这个普通属性,来记录值,表数据间关联关系存在,但对象关联关系不存在。代码也就变得简单。在货物新增时,只要从主对象中携带过来,主表ID即可。
2.VO为了方便对象关联时取数据。在列表循环货物信息时,要去查询当前货物下的附件时,如果采用上面的方式,只能再次查询。但是我们已对象关联方式,可以直接获取到当前货物下的附件集合。一次SQL所有的附件都查询出来。
PO/VO都是POJO对象。根据业务的当前环境。
b)货物数、附件数
方法1:代码可以通过合同号,分别去查询货物数、附件数;它需要发出多条的SQL再次查询。
方法2:利用SQL语句直接支持,它可以把一个结果集作为一个字段值,这样的SQL返回结果集必须一个列。同时在这个子查询中它的条件可以拼接动态的条件。contract_id=t.contract_id
t是总表的别称。这样引入一个动态的条件。以此类推实现了附件数。
方法1和方法2效能够不高。方法1,代码加工,要重新发出SQL子查询。效率低。方法2,SQL嵌套很多子查询,虽然它是数据库级别,也就是允许在强大的服务器上。
方法3:冗余+分次计算【亮点】
在表中建立货物数和附件数字段,在货物新增时,当前合同,货物数+1。删除货物,货物数-1;在附件新增时,当前合同,附件数+1,删除附件时,附件数-1,删除货物时,要将其下面的附件总数从合同附件数中减去。代码考虑的比较复杂些,但它因为分散各处,计算效率很高,查询时,无需动态计算,效率奇高。
c)权限:【亮点】
角色用户权限,大多数系统都使用此种方式。
细粒度权限,它控制到数据访问权限。权限架构才能解决实际项目的应用。(财务)