Author: fdesbois Date: 2010-06-08 10:26:34 +0000 (Tue, 08 Jun 2010) New Revision: 496 Log: Finish refactor for Topia 2.4 in ServiceBoat Modified: trunk/wao-business/src/main/java/fr/ifremer/wao/WaoQueryBuilder.java trunk/wao-business/src/main/java/fr/ifremer/wao/service/ServiceBoatImpl.java Modified: trunk/wao-business/src/main/java/fr/ifremer/wao/WaoQueryBuilder.java =================================================================== --- trunk/wao-business/src/main/java/fr/ifremer/wao/WaoQueryBuilder.java 2010-06-08 10:01:43 UTC (rev 495) +++ trunk/wao-business/src/main/java/fr/ifremer/wao/WaoQueryBuilder.java 2010-06-08 10:26:34 UTC (rev 496) @@ -28,6 +28,8 @@ public static String ALIAS_BOAT = "B"; + public static String ALIAS_ACTIVITY_CALENDAR = "AC"; + String sampleRowProperty; String fishingZoneProperty; @@ -381,26 +383,9 @@ addLeftJoin(sampleRowFishingZoneProperty, fishingZoneProperty, false); -// addFrom(FishingZone.class, fishingZoneProperty). -// // FIXME : for ToPIA 2.4 : replace by addInElements -// addWhere(new StringBuilder(fishingZoneProperty). -// append(" IN elements("). -// append(sampleRowfishingZoneProperty). -// append(")"). -// toString() -// ); - // addInElements(fishingZoneProperty, sampleRowfishingZoneProperty); - // Apply filter for fishingZone on subQuery applySimpleFishingZoneFilter(filter, subquery); - // FIXME : for ToPIA 2.4 : replace by addSubQuery -// query.addWhere(new StringBuilder(sampleRowProperty). -// append(" IN ("). -// append(subquery.fullQuery()). -// append(")"). -// toString() -// ).addParams(subquery.getParams()); query.addSubQuery(sampleRowProperty + " IN (?)", subquery); } } @@ -483,18 +468,13 @@ // Sampling filter if (filter.isSamplingFiltered()) { -// query.addFrom(ElligibleBoat.class, ALIAS_ELLIGIBLE_BOAT). -// addWhere(new StringBuilder(getElligibleBoatProperty()). -// append(" = "). -// append(boatProperty). -// toString() -// ); // Add join for ElligibleBoat elligibleBoatProperty = ALIAS_ELLIGIBLE_BOAT; query.addLeftJoin(getBoatElligibleProperty(), elligibleBoatProperty, false); - // To have no doubloons + + // To be sure to have no doubloons query.setSelect(boatProperty).addDistinct(); // Apply filter for sampling Modified: trunk/wao-business/src/main/java/fr/ifremer/wao/service/ServiceBoatImpl.java =================================================================== --- trunk/wao-business/src/main/java/fr/ifremer/wao/service/ServiceBoatImpl.java 2010-06-08 10:01:43 UTC (rev 495) +++ trunk/wao-business/src/main/java/fr/ifremer/wao/service/ServiceBoatImpl.java 2010-06-08 10:26:34 UTC (rev 496) @@ -99,9 +99,6 @@ BoatFilter filter) throws TopiaException { BoatDAO dao = WaoDAOHelper.getBoatDAO(transaction); -// TopiaQuery query = dao.createQuery("B"); -// -// query = createQueryForBoatFilter(query, filter); TopiaQuery query = new WaoQueryBuilder(Boat.class). applyBoatFilter(filter). @@ -124,54 +121,16 @@ BoatFilter filter) throws TopiaException { BoatDAO dao = WaoDAOHelper.getBoatDAO(transaction); -// TopiaQuery query = dao.createQuery("B"); -// -// query = createQueryForBoatFilter(query, filter); TopiaQuery query = new WaoQueryBuilder(Boat.class). applyBoatFilter(filter). toQuery(); -// int count = query.executeToInteger("COUNT(DISTINCT B)"); int count = dao.countByQuery(query); return count; } - @Deprecated - protected TopiaQuery createQueryForBoatFilter(TopiaQuery query, - BoatFilter filter) throws TopiaException { - String main = query.getMainAlias(); - query = filter.prepareQueryForBoat(query, main).addDistinct(); - - // Sampling filter - if (filter.isSamplingFiltered()) { - // Add join for ElligibleBoat - //String elligibleBoatClassName = ElligibleBoat.class.getName(); - query.addFrom(ElligibleBoat.class, "E"). - add("E." + ElligibleBoat.BOAT + " = " + main); - query = filter.prepareQueryForSampling(query, - "E." + ElligibleBoat.SAMPLE_ROW); - - // Company - if (filter.getCompany() != null) { - // Test only valid ElligibleBoat for that company - query.add("E." + ElligibleBoat.COMPANY_ACTIVE + - " = :booleanTrue " + - "OR (E." + ElligibleBoat.COMPANY_ACTIVE + - " IS NULL AND E." + ElligibleBoat.GLOBAL_ACTIVE + - " = :booleanTrue)"); - query.addParam("booleanTrue", Boolean.TRUE); - } - } - - if (StringUtils.isNotEmpty(filter.getOrderBy())) { - query.addOrder(main + "." + filter.getOrderBy()); - } - - return query; - } - /** * Get boat infos and elligible boats for a company and a boat. This method * is useful to get sampleRows elligibles for the boat. Only sampleRows not @@ -358,7 +317,7 @@ * QUARTIER_IMMA,PER_COD, PER_NOM,PER_PRENOM. * Existing boats (with same immatriculation) are updated. * - * @param transaction + * @param transaction current transaction * @param input Stream corresponding to Csv file * @return an array with two int : the first corresponding to total number * of boats imported and the second corresponding only to new boats added. @@ -502,9 +461,11 @@ // Get boats from database filterd by filter in argument BoatDAO dao = WaoDAOHelper.getBoatDAO(transaction); - TopiaQuery query = dao.createQuery("B"); - query = createQueryForBoatFilter(query, filter); + TopiaQuery query = new WaoQueryBuilder(Boat.class). + applyBoatFilter(filter). + toQuery(); + // Reset limit : no pagination needed for results query.resetLimit(); @@ -582,8 +543,6 @@ TopiaContext transaction, List<Object> errorArgs, WaoUser user, Boat boat) throws TopiaException, IOException { - // TODO-fdesbois-2010-05-31 : long execution more than 3sec, need to check which operation cost - errorArgs.add(boat.getName()); errorArgs.add(boat.getImmatriculation()); @@ -591,20 +550,54 @@ ActivityCalendarDAO dao = WaoDAOHelper.getActivityCalendarDAO(transaction); - TopiaQuery query = dao.createQuery("A"). - add("A." + ActivityCalendar.BOAT, boat); + // Properties needed for query and subQuery + String calendarAlias = WaoQueryBuilder.ALIAS_ACTIVITY_CALENDAR; + String calendarBoatProperty = + TopiaQuery.getProperty(calendarAlias, ActivityCalendar.BOAT); + String calendarYearProperty = + TopiaQuery.getProperty(calendarAlias, ActivityCalendar.YEAR); - TopiaQuery subquery = dao.createQuery("B"). - setSelect("MAX(B." + ActivityCalendar.YEAR + ")"). - add("B." + ActivityCalendar.BOAT + " = A." + ActivityCalendar.BOAT). - addGroup("B." + ActivityCalendar.BOAT); + String maxCalendarAlias = "max" + calendarAlias; + String maxCalendarBoatProperty = + TopiaQuery.getProperty(maxCalendarAlias, ActivityCalendar.BOAT); + String maxCalendarYearProperty = + TopiaQuery.getProperty(maxCalendarAlias, ActivityCalendar.YEAR); - query.add("A." + ActivityCalendar.YEAR + " = (" + subquery.fullQuery() + ")"); + // Prepare subQuery + TopiaQuery subquery = dao.createQuery(maxCalendarAlias). + setSelect(new StringBuilder("MAX("). + append(maxCalendarYearProperty). + append(')'). + toString() + ). + addWhere(new StringBuilder(maxCalendarBoatProperty). + append(" = "). + append(calendarBoatProperty). + toString() + ). + // addWhere("$1 = $2", maxCalendarBoatProperty, calendarBoatProperty); + addGroup(maxCalendarBoatProperty); - query.addLoad(ActivityCalendar.BOAT); + // Create main query + TopiaQuery query = dao.createQuery(calendarAlias). + addEquals(calendarBoatProperty, boat). + addSubQuery(calendarYearProperty + " = (?)", subquery). + addLoad(ActivityCalendar.BOAT); + +// TopiaQuery query = dao.createQuery("A"). +// add("A." + ActivityCalendar.BOAT, boat); +// +// TopiaQuery subquery = dao.createQuery("B"). +// setSelect("MAX(B." + ActivityCalendar.YEAR + ")"). +// add("B." + ActivityCalendar.BOAT + " = A." + ActivityCalendar.BOAT). +// addGroup("B." + ActivityCalendar.BOAT); +// +// query.add("A." + ActivityCalendar.YEAR + " = (" + subquery.fullQuery() + ")"); + result = dao.findByQuery(query); - // load other complex data from collections + // load other complex data from collections + // TODO-fdesbois-2010-05-31 : long execution more than 3sec, need to check which operation cost --> loading List<ActivityMonth> months = result.getActivityMonth(); for (ActivityMonth month : months) { List<ActivityProfession> professions = month.getActivityProfession();