java后端 生成表格一对多数据

最近做一个管理系统需要用到合并表格

直接上代码

    /**
     *分页查询
     */
    @Override
    public PageJson listData(Integer page, Integer pageNum,String start, String end,DriverAppointmentBean bean,String PLATE_NUMBER, String CROSS_NAME,String APPOINT__DELIVERY_TIME, String ITEM_NAME) {
        String whereSql = "";

        try {
        //这里是查询方法
            if(StringUtils.isNotEmpty(PLATE_NUMBER)){
                whereSql +=" and vsa.PLATE_NUMBER like '"+PLATE_NUMBER+"'";
            }

            if (StringUtils.isNotEmpty(CROSS_NAME)) {
                whereSql += " and  vsa.CROSS_NAME LIKE '" + CROSS_NAME + "'";
            }
            if (StringUtils.isNotEmpty(ITEM_NAME)) {
                whereSql += " and   vda.ITEM_NAME LIKE '" +ITEM_NAME + "'";
            }
            if(!"0".equals(start)&&!"0".equals(end)){
                SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
                long startlong = Long.parseLong(start);
                long endlong=Long.parseLong(end);
                String starttime = sdf.format(new Date(startlong));
                String endtime=sdf.format(new Date(endlong));
                whereSql+=" and  vsa.APPOINTMENT_TIME BETWEEN "+"'"+starttime+"'"+" and "+"'" +endtime+"'";
            }

            //物料信息                   供应商代码
            String itemSql="select  vda.SUPPLIER_CODE, vda.SUPPLIER_NAME, vda.ITEM_NAME,vda.ITEM_CODE  from VAS_DRIVER_APPOINTMENT vsa\n" +
                    "left join\n" +
                    "VAS_DRIVER_INVOICES_M vdi on vdi.PLATE_NUMBER = vsa.PLATE_NUMBER \n" +
                    "left join\n" +
                    "VAS_SUPPLIER_APPOINTMENT vda on vda.INVOICES_NO = vdi.INVOICES_NO   group by vda.SUPPLIER_CODE, vda.SUPPLIER_NAME, vda.ITEM_CODE, vda.ITEM_NAME\n";
            //司机sql
            String driverSql="select vsa.APPOINTMENT_TIME,vsa.PLATE_NUMBER,vsa.DRIVER_NAME,vsa.PHONE_NUMBER,vsa.CROSS_NAME,vda.APPOINT__DELIVERY_TIME\n" +
                    "from VAS_DRIVER_APPOINTMENT vsa left join `vas_driver_invoices_m` vdi on vdi.PLATE_NUMBER = vsa.PLATE_NUMBER \n" +
                    "left join VAS_SUPPLIER_APPOINTMENT vda on vda.INVOICES_NO = vdi.INVOICES_NO where 1=1  "+whereSql+" group by vsa.PLATE_NUMBER,vsa.DRIVER_NAME,vsa.PHONE_NUMBER \n";
            //供应商sql
            String supplierSql="select  vsa.APPOINTMENT_TIME,vsa.PLATE_NUMBER,vsa.DRIVER_NAME,vsa.PHONE_NUMBER,vsa.CROSS_NAME,vda.APPOINT__DELIVERY_TIME, vda.SUPPLIER_CODE,vda.SUPPLIER_NAME  from VAS_DRIVER_APPOINTMENT vsa\n" +
                    "left join\n" +
                    "VAS_DRIVER_INVOICES_M vdi on vdi.PLATE_NUMBER = vsa.PLATE_NUMBER \n" +
                    "left join\n" +
                    "VAS_SUPPLIER_APPOINTMENT vda on vda.INVOICES_NO = vdi.INVOICES_NO   group by vsa.APPOINTMENT_TIME,vsa.PLATE_NUMBER,vsa.DRIVER_NAME,vsa.PHONE_NUMBER,vsa.CROSS_NAME,vda.APPOINT__DELIVERY_TIME, vda.SUPPLIER_NAME,vda.SUPPLIER_CODE ";
            //道口sql
            String crossSql="select vsa.PLATE_NUMBER,vsa.DRIVER_NAME,vsa.PHONE_NUMBER,vsa.CROSS_NAME\n" +
                    "from VAS_DRIVER_APPOINTMENT vsa group by vsa.CROSS_NAME\n";

            Integer count=iBaseSQLService.getCount("select count(1) from (" + driverSql + ")c");
            //获取司机信息
            List<Object[]> driverLists =iBaseSQLService.getPageData(driverSql + "", (page-1)*pageNum, pageNum);
            //获取道口信息
            List<Object[]> crossLists= jpa.createNativeQuery(crossSql).getResultList();
            //获取供应商信息
            List<Object[]> supplierList = iBaseSQLService.getPageData(supplierSql + "", (page - 1) * pageNum, pageNum);
            //获取物料信息
            List<Object[]> itemList = jpa.createNativeQuery(itemSql).getResultList();

            //如果司机信息不为空就循环
            if (driverLists != null && driverLists.size() > 0) {
                List<DriverAppointmentBean> obList = new ArrayList<>();
                for (Object[] driverList : driverLists) {
                    //db存放的是司机信息
                    DriverAppointmentBean db = new DriverAppointmentBean();
                    String appointmentTime = Tools.getResultByObject(driverList[0]);
                    String plateNumber = Tools.getResultByObject(driverList[1]);
                    String driverName = Tools.getResultByObject(driverList[2]);
                    String phoneNumber = Tools.getResultByObject(driverList[3]);
                    String appointDeliveryTime = Tools.getResultByObject(driverList[5]);
                    db.setAppointmentTime(appointmentTime);
                    db.setPlateNumber(plateNumber);
                    db.setDriverName(driverName);
                    db.setPhoneNumber(phoneNumber);
                    db.setAppointDeliveryTime(appointDeliveryTime);
                    //道口信息
                    if(crossLists !=null && crossLists.size()>0){
                        List<CrossBean> cross = new ArrayList<>();
                        CrossBean cb= new CrossBean();
                        String crossName = "";
                        for (Object[] crossList : crossLists) {
                            if( Tools.getResultByObject(crossList[0]).equals(plateNumber)
                                    &&Tools.getResultByObject(crossList[1]).equals(driverName)
                                    &&Tools.getResultByObject(crossList[2]).equals(phoneNumber)
                                    ) {
                                crossName += Tools.getResultByObject(crossList[3]+",");
                            }
                        }
                        if (StringUtils.isNotEmpty(crossName)){
                            crossName = crossName.substring(0,crossName.length() - 1);
                        }
                        cb.setCrossName(crossName);
                        cross.add(cb);
                        db.setCeossesBean(cross);
                    }
                    //供应信息不为空就循环
                    if (supplierList != null && supplierList.size() > 0) {
                        List<SupplierAppointmentBean> sbeList = new ArrayList<>();
                        for (Object[] suppliers : supplierList) {
                            if ( Tools.getResultByObject(suppliers[1]).equals(plateNumber)
                                    &&Tools.getResultByObject(suppliers[2]).equals(driverName)
                                    &&Tools.getResultByObject(suppliers[3]).equals(phoneNumber)
                                ) {
                                SupplierAppointmentBean sb = new SupplierAppointmentBean();
                                List<ItemAppointmentBean> lbList = new ArrayList<>();
                                //物料信息
                                for (Object[] items : itemList) {
                                    //供应商名称，供应商代码
                                    if ((Tools.getResultByObject(items[0]).equals(suppliers[6])
                                            && Tools.getResultByObject(items[1]).equals(suppliers[7]))) {
                                        //供应商存储
                                        ItemAppointmentBean sab = new ItemAppointmentBean();
                                        sab.setItemCode(Tools.getResultByObject(items[2]));
                                        sab.setItemName(Tools.getResultByObject(items[3]));

                                        lbList.add(sab);
                                    }
                                }
                                sb.setSupplierName(Tools.getResultByObject(suppliers[6]));
                                sb.setSupplierCode(Tools.getResultByObject(suppliers[7]));
                                sb.setAppointmentItems(lbList);
                                sbeList.add(sb);
                            }
                        }
                        db.setSupplierAppointmentBean(sbeList);

                    }
                    obList.add(db);

                }
                PageJson pj = new PageJson(page, count, obList);

                return pj;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

参数里面的bean 类型 其实就是一个中间表 DriverAppointmentBean（保存司机预约查看列表信息）