7个表的复杂查询

public List findDetailSumById(Long id) throws Exception {  
       List sumList=new ArrayList();  
       StringBuffer sb = new StringBuffer(" ");  
       sb.append("select sup.materialcode as materialcode,sup.materialname as materialname,sup.is_auto_send_erp,");  
       sb.append("sup.providerproductname  as providerproductname , sup.measurename as measurename,");  
       sb.append("sum(sup.amount) as amount,sum(sup.money) as money,");  
       sb.append("sup.providername as providername, sup.locationname as locationname,");  
       sb.append("sup.projectcode as  projectcode,sup.materialproperty as materialproperty,");  
       sb.append("sup.providerproductcode as providerproductcode, sup.submeasurename as submeasurename,");  
       sb.append("sup.providershortname as providershortname ,");  
       sb.append("sup.ismainproduct as ismainproduct,sup.inlocationname as inlocationname");  
       sb.append(" from");  
       sb.append("(");  
       sb.append("select spm.entity_code as  materialcode,spm.entity_name as  materialname,sibd.is_auto_send_erp ,");  
       sb.append("sibd.provider_product_name as providerproductname,sibd.is_main_product as ismainproduct,");  
       sb.append(" nvl( sibd.measure_name,' ')  as  measurename,nvl( sibd.sub_measure_name,' ') as submeasurename,");  
       sb.append("nvl(sibd.amount,0) as amount,nvl(sibd.money,0) as money,");  
       sb.append(" spr.entity_name as providername,spr.short_name as providershortname ,nvl(swl.entity_name,' ') as locationname ,");  
       sb.append(" nvl( sibd.entity_desc,' ') as entitydesc,nvl( sp.entity_code,' ') as projectcode,");  
       sb.append(" nvl( sibd.provider_product_code,' ') as providerproductcode,nvl( smp.entity_name,' ') as materialproperty,");  
       sb.append(" sibd.output_bill_id as outputbillid,nvl(swlin.entity_name,' ') as inlocationname");  
       sb.append(" from scm_output_bill_detail sibd");  
       sb.append(" left outer join scm_product_material spm on spm.id =sibd.product_material_id");  
       sb.append(" left outer join scm_provider spr on spr.id = sibd.provider_id");  
       sb.append(" left outer join scm_warehouse_location swl on swl.id =sibd.warehouse_location_id");  
       sb.append(" left outer join scm_warehouse_location swlin on swlin.id =sibd.in_warehouse_location_id");  
       sb.append(" left outer join scm_project sp on sp.id = sibd.project_id");  
       sb.append(" left outer join scm_material_property smp on smp.id=sibd.material_property_id");  
       sb.append(") sup");   
       sb.append(" where sup.outputbillid=?");  
       sb.append(" group by");  
       sb.append(" sup.materialcode,sup.materialname,sup.materialproperty, sup.measurename,sup.projectcode,");  
       sb.append("sup.providername,sup.providerproductname,sup.providerproductcode,sup.locationname,sup.is_auto_send_erp ,");  
       sb.append("sup.providershortname,sup.submeasurename,sup.ismainproduct,sup.inlocationname");  
       sb.append(" order by");  
       sb.append(" sup.materialcode,sup.materialname,sup.materialproperty,sup.measurename,sup.projectcode,");  
       sb.append("sup.providername,sup.providerproductname,sup.providerproductcode,sup.locationname,");  
       sb.append("sup.submeasurename,sup.providershortname");  
         
       sumList = getJdbcTemplate().query(sb.toString(), new Object[]{id}, new RowMapper(){  
           public Object mapRow(ResultSet rt, int arg1) throws SQLException {  
               OutputDetailSumBean outDetailSumBean=new OutputDetailSumBean();  
               outDetailSumBean.setMaterialCode(rt.getString("materialcode"));  
               outDetailSumBean.setMaterialName(rt.getString("materialname"));  
               outDetailSumBean.setProjectCode(rt.getString("projectcode"));   
               outDetailSumBean.setProviderProductName(rt.getString("providerproductname"));  
               outDetailSumBean.setMaterialProperty(rt.getString("materialproperty"));  
               outDetailSumBean.setSubmeasureName(rt.getString("submeasurename"));  
               outDetailSumBean.setProviderProductCode(rt.getString("providerproductcode"));  
               outDetailSumBean.setProviderShortName(rt.getString("providershortname"));  
               outDetailSumBean.setProviderName(rt.getString("providername"));  
               outDetailSumBean.setLocationName(rt.getString("locationname"));  
               outDetailSumBean.setInlocationName(rt.getString("inlocationname"));  
               outDetailSumBean.setMeasureName(rt.getString("measurename"));  
               outDetailSumBean.setIsmainProduct(rt.getLong("ismainproduct"));  
               outDetailSumBean.setAmount(rt.getDouble("amount"));  
             //  outDetailSumBean.setPrice(rt.getDouble("price"));  
               outDetailSumBean.setMoney(rt.getDouble("money"));   
               outDetailSumBean.setIsAutoSendERP(rt.getLong("is_auto_send_erp"));  
                 
               return outDetailSumBean;  
           }  
       });  
        return sumList;  
   }  

补充：综合编程 , 其他综合 ,

上一个：rcp（插件开发）org.eclipse.ui.decorators 使用
下一个：texture与sprite的区别