cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/persistence/dao/hibernate/occurrence/OccurrenceDaoHibernateImpl.java

   1 /**
   2 * Copyright (C) 2008 EDIT
   3 * European Distributed Institute of Taxonomy
   4 * http://www.e-taxonomy.eu
   5 */
   6
   7 package eu.etaxonomy.cdm.persistence.dao.hibernate.occurrence;
   8
   9 import java.util.ArrayList;
  10 import java.util.Collection;
  11 import java.util.Collections;
  12 import java.util.HashSet;
  13 import java.util.List;
  14 import java.util.Set;
  15 import java.util.UUID;
  16
  17 import org.apache.log4j.Logger;
  18 import org.hibernate.Criteria;
  19 import org.hibernate.Hibernate;
  20 import org.hibernate.Query;
  21 import org.hibernate.Session;
  22 import org.hibernate.criterion.Disjunction;
  23 import org.hibernate.criterion.ProjectionList;
  24 import org.hibernate.criterion.Projections;
  25 import org.hibernate.criterion.Restrictions;
  26 import org.hibernate.envers.query.AuditEntity;
  27 import org.hibernate.envers.query.AuditQuery;
  28 import org.hibernate.search.FullTextSession;
  29 import org.hibernate.search.Search;
  30 import org.springframework.beans.factory.annotation.Autowired;
  31 import org.springframework.stereotype.Repository;
  32
  33 import eu.etaxonomy.cdm.model.common.CdmBase;
  34 import eu.etaxonomy.cdm.model.description.DescriptionBase;
  35 import eu.etaxonomy.cdm.model.description.IndividualsAssociation;
  36 import eu.etaxonomy.cdm.model.media.Media;
  37 import eu.etaxonomy.cdm.model.molecular.DnaSample;
  38 import eu.etaxonomy.cdm.model.name.HomotypicalGroup;
  39 import eu.etaxonomy.cdm.model.name.SpecimenTypeDesignation;
  40 import eu.etaxonomy.cdm.model.name.TaxonName;
  41 import eu.etaxonomy.cdm.model.occurrence.DerivationEvent;
  42 import eu.etaxonomy.cdm.model.occurrence.DerivedUnit;
  43 import eu.etaxonomy.cdm.model.occurrence.DeterminationEvent;
  44 import eu.etaxonomy.cdm.model.occurrence.FieldUnit;
  45 import eu.etaxonomy.cdm.model.occurrence.SpecimenOrObservationBase;
  46 import eu.etaxonomy.cdm.model.occurrence.SpecimenOrObservationType;
  47 import eu.etaxonomy.cdm.model.taxon.Synonym;
  48 import eu.etaxonomy.cdm.model.taxon.Taxon;
  49 import eu.etaxonomy.cdm.model.taxon.TaxonBase;
  50 import eu.etaxonomy.cdm.model.taxon.TaxonNode;
  51 import eu.etaxonomy.cdm.model.view.AuditEvent;
  52 import eu.etaxonomy.cdm.persistence.dao.description.IDescriptionDao;
  53 import eu.etaxonomy.cdm.persistence.dao.hibernate.common.IdentifiableDaoBase;
  54 import eu.etaxonomy.cdm.persistence.dao.hibernate.taxon.TaxonDaoHibernateImpl;
  55 import eu.etaxonomy.cdm.persistence.dao.name.IHomotypicalGroupDao;
  56 import eu.etaxonomy.cdm.persistence.dao.name.ITaxonNameDao;
  57 import eu.etaxonomy.cdm.persistence.dao.occurrence.IOccurrenceDao;
  58 import eu.etaxonomy.cdm.persistence.dto.SpecimenNodeWrapper;
  59 import eu.etaxonomy.cdm.persistence.dto.UuidAndTitleCache;
  60 import eu.etaxonomy.cdm.persistence.query.MatchMode;
  61 import eu.etaxonomy.cdm.persistence.query.OrderHint;
  62
  63 /**
  64  * @author a.babadshanjan
  65  * @since 01.09.2008
  66  */
  67 @Repository
  68 public class OccurrenceDaoHibernateImpl
  69           extends IdentifiableDaoBase<SpecimenOrObservationBase>
  70           implements IOccurrenceDao {
  71
  72     @SuppressWarnings("unused")
  73     private static final Logger logger = Logger.getLogger(TaxonDaoHibernateImpl.class);
  74
  75     @Autowired
  76     private IDescriptionDao descriptionDao;
  77
  78     @Autowired
  79     private ITaxonNameDao taxonNameDao;
  80
  81     @Autowired
  82     private IHomotypicalGroupDao homotypicalGroupDao;
  83
  84     public OccurrenceDaoHibernateImpl() {
  85         super(SpecimenOrObservationBase.class);
  86         indexedClasses = new Class[7];
  87         indexedClasses[0] = FieldUnit.class;
  88         indexedClasses[1] = DerivedUnit.class;
  89         indexedClasses[5] = DnaSample.class;
  90     }
  91
  92     @Override
  93     public long countDerivationEvents(SpecimenOrObservationBase occurence) {
  94         checkNotInPriorView("OccurrenceDaoHibernateImpl.countDerivationEvents(SpecimenOrObservationBase occurence)");
  95         Query query = getSession().createQuery("select count(distinct derivationEvent) from DerivationEvent derivationEvent join derivationEvent.originals occurence where occurence = :occurence");
  96         query.setParameter("occurence", occurence);
  97
  98         return (Long)query.uniqueResult();
  99     }
 100
 101     @Override
 102     public long countDeterminations(SpecimenOrObservationBase occurrence, TaxonBase taxonBase) {
 103         AuditEvent auditEvent = getAuditEventFromContext();
 104         if(auditEvent.equals(AuditEvent.CURRENT_VIEW)) {
 105             Criteria criteria = getCriteria(DeterminationEvent.class);
 106             if(occurrence != null) {
 107                 criteria.add(Restrictions.eq("identifiedUnit",occurrence));
 108             }
 109
 110             if(taxonBase != null) {
 111                 criteria.add(Restrictions.eq("taxon",taxonBase));
 112             }
 113
 114             criteria.setProjection(Projections.rowCount());
 115             return (Long)criteria.uniqueResult();
 116         } else {
 117             AuditQuery query = makeAuditQuery(DeterminationEvent.class,auditEvent);
 118
 119             if(occurrence != null) {
 120                 query.add(AuditEntity.relatedId("identifiedUnit").eq(occurrence.getId()));
 121             }
 122
 123             if(taxonBase != null) {
 124                 query.add(AuditEntity.relatedId("taxon").eq(taxonBase.getId()));
 125             }
 126             query.addProjection(AuditEntity.id().count());
 127
 128             return (Long)query.getSingleResult();
 129         }
 130     }
 131
 132     @Override
 133     public long countMedia(SpecimenOrObservationBase occurence) {
 134         checkNotInPriorView("OccurrenceDaoHibernateImpl.countMedia(SpecimenOrObservationBase occurence)");
 135         Query query = getSession().createQuery("select count(media) from SpecimenOrObservationBase occurence join occurence.media media where occurence = :occurence");
 136         query.setParameter("occurence", occurence);
 137
 138         return (Long)query.uniqueResult();
 139     }
 140
 141     @Override
 142     public List<DerivationEvent> getDerivationEvents(SpecimenOrObservationBase occurence, Integer pageSize,Integer pageNumber, List<String> propertyPaths) {
 143         checkNotInPriorView("OccurrenceDaoHibernateImpl.getDerivationEvents(SpecimenOrObservationBase occurence, Integer pageSize,Integer pageNumber)");
 144         Query query = getSession().createQuery("select distinct derivationEvent from DerivationEvent derivationEvent join derivationEvent.originals occurence where occurence = :occurence");
 145         query.setParameter("occurence", occurence);
 146
 147         addPageSizeAndNumber(query, pageSize, pageNumber);
 148
 149         @SuppressWarnings("unchecked")
 150         List<DerivationEvent> result = query.list();
 151         defaultBeanInitializer.initializeAll(result, propertyPaths);
 152         return result;
 153     }
 154
 155     @Override
 156     public List<DeterminationEvent> getDeterminations(SpecimenOrObservationBase occurrence, TaxonBase taxonBase, Integer pageSize, Integer pageNumber, List<String> propertyPaths) {
 157         AuditEvent auditEvent = getAuditEventFromContext();
 158         if(auditEvent.equals(AuditEvent.CURRENT_VIEW)) {
 159             Criteria criteria = getSession().createCriteria(DeterminationEvent.class);
 160             if(occurrence != null) {
 161                 criteria.add(Restrictions.eq("identifiedUnit",occurrence));
 162             }
 163
 164             if(taxonBase != null) {
 165                 criteria.add(Restrictions.eq("taxon",taxonBase));
 166             }
 167
 168             addPageSizeAndNumber(criteria, pageSize, pageNumber);
 169
 170             @SuppressWarnings("unchecked")
 171             List<DeterminationEvent> result = criteria.list();
 172             defaultBeanInitializer.initializeAll(result, propertyPaths);
 173             return result;
 174         } else {
 175             AuditQuery query = getAuditReader().createQuery().forEntitiesAtRevision(DeterminationEvent.class,auditEvent.getRevisionNumber());
 176             if(occurrence != null) {
 177                 query.add(AuditEntity.relatedId("identifiedUnit").eq(occurrence.getId()));
 178             }
 179
 180             if(taxonBase != null) {
 181                 query.add(AuditEntity.relatedId("taxon").eq(taxonBase.getId()));
 182             }
 183             addPageSizeAndNumber(query, pageSize, pageNumber);
 184
 185             @SuppressWarnings("unchecked")
 186             List<DeterminationEvent> result = query.getResultList();
 187             defaultBeanInitializer.initializeAll(result, propertyPaths);
 188             return result;
 189         }
 190     }
 191
 192     @Override
 193     public List<Media> getMedia(SpecimenOrObservationBase occurence, Integer pageSize, Integer pageNumber, List<String> propertyPaths) {
 194         checkNotInPriorView("OccurrenceDaoHibernateImpl.getMedia(SpecimenOrObservationBase occurence, Integer pageSize, Integer pageNumber, List<String> propertyPaths)");
 195         Query query = getSession().createQuery(
 196                 "   SELECT media "
 197                 + " FROM SpecimenOrObservationBase occurence "
 198                 + " JOIN occurence.media media "
 199                 + " WHERE occurence = :occurence");
 200         query.setParameter("occurence", occurence);
 201
 202         addPageSizeAndNumber(query, pageSize, pageNumber);
 203
 204         @SuppressWarnings("unchecked")
 205         List<Media> results = query.list();
 206         defaultBeanInitializer.initializeAll(results, propertyPaths);
 207         return results;
 208     }
 209
 210     @Override
 211     public void rebuildIndex() {
 212         FullTextSession fullTextSession = Search.getFullTextSession(getSession());
 213
 214         for(SpecimenOrObservationBase<?> occurrence : list(null,null)) { // re-index all taxon base
 215
 216             for(DeterminationEvent determination : occurrence.getDeterminations()) {
 217                 Hibernate.initialize(determination.getActor());
 218                 Hibernate.initialize(determination.getTaxon());
 219             }
 220             Hibernate.initialize(occurrence.getDefinition());
 221             if(occurrence instanceof DerivedUnit) {
 222                 DerivedUnit derivedUnit = (DerivedUnit) occurrence;
 223                 Hibernate.initialize(derivedUnit.getCollection());
 224                 if(derivedUnit.getCollection() != null) {
 225                     Hibernate.initialize(derivedUnit.getCollection().getSuperCollection());
 226                     Hibernate.initialize(derivedUnit.getCollection().getInstitute());
 227                 }
 228                 Hibernate.initialize(derivedUnit.getStoredUnder());
 229                 SpecimenOrObservationBase<?> original = derivedUnit.getOriginalUnit();
 230                 if(original != null && original.isInstanceOf(FieldUnit.class)) {
 231                     FieldUnit fieldUnit = CdmBase.deproxy(original, FieldUnit.class);
 232                     Hibernate.initialize(fieldUnit.getGatheringEvent());
 233                     if(fieldUnit.getGatheringEvent() != null) {
 234                         Hibernate.initialize(fieldUnit.getGatheringEvent().getActor());
 235                     }
 236                 }
 237             }
 238             fullTextSession.index(occurrence);
 239         }
 240         fullTextSession.flushToIndexes();
 241     }
 242
 243     @Override
 244     public long count(Class<? extends SpecimenOrObservationBase> clazz, TaxonName determinedAs) {
 245
 246         Criteria criteria = getCriteria(clazz);
 247
 248         criteria.createCriteria("determinations").add(Restrictions.eq("taxonName", determinedAs));
 249         criteria.setProjection(Projections.projectionList().add(Projections.rowCount()));
 250         return (Long)criteria.uniqueResult();
 251     }
 252
 253     @Override
 254     public List<SpecimenOrObservationBase> list(Class<? extends SpecimenOrObservationBase> clazz, TaxonName determinedAs,
 255             Integer pageSize, Integer pageNumber, List<OrderHint> orderHints, List<String> propertyPaths) {
 256         Criteria criteria = getCriteria(clazz);
 257
 258         criteria.createCriteria("determinations").add(Restrictions.eq("taxonName", determinedAs));
 259
 260         addPageSizeAndNumber(criteria, pageSize, pageNumber);
 261         addOrder(criteria,orderHints);
 262
 263         @SuppressWarnings({ "unchecked", "rawtypes" })
 264         List<SpecimenOrObservationBase> results = criteria.list();
 265         defaultBeanInitializer.initializeAll(results, propertyPaths);
 266         return results;
 267     }
 268
 269     @Override
 270     public long count(Class<? extends SpecimenOrObservationBase> clazz, TaxonBase determinedAs) {
 271
 272         Criteria criteria = getCriteria(clazz);
 273
 274         criteria.createCriteria("determinations").add(Restrictions.eq("taxon", determinedAs));
 275         criteria.setProjection(Projections.projectionList().add(Projections.rowCount()));
 276         return (Long)criteria.uniqueResult();
 277     }
 278
 279
 280     @Override
 281     public List<SpecimenOrObservationBase> list(Class<? extends SpecimenOrObservationBase> clazz, TaxonBase determinedAs,
 282             Integer pageSize, Integer pageNumber, List<OrderHint> orderHints, List<String> propertyPaths) {
 283
 284         Criteria criteria = getCriteria(clazz);
 285
 286         criteria.createCriteria("determinations").add(Restrictions.eq("taxon", determinedAs));
 287
 288         addPageSizeAndNumber(criteria, pageSize, pageNumber);
 289         addOrder(criteria,orderHints);
 290
 291         @SuppressWarnings({ "unchecked", "rawtypes" })
 292         List<SpecimenOrObservationBase> results = criteria.list();
 293         defaultBeanInitializer.initializeAll(results, propertyPaths);
 294         return results;
 295     }
 296
 297     @Override
 298     public <T extends SpecimenOrObservationBase> List<UuidAndTitleCache<SpecimenOrObservationBase>> findOccurrencesUuidAndTitleCache(
 299             Class<T> clazz, String queryString, String significantIdentifier, SpecimenOrObservationType recordBasis,
 300             Taxon associatedTaxon, TaxonName associatedTaxonName, MatchMode matchmode, Integer limit, Integer start,
 301             List<OrderHint> orderHints) {
 302         Criteria criteria = createFindOccurrenceCriteria(clazz, queryString, significantIdentifier, recordBasis,
 303                 associatedTaxon, associatedTaxonName, matchmode, limit, start, orderHints, null);
 304         if(criteria!=null){
 305             ProjectionList projectionList = Projections.projectionList();
 306             projectionList.add(Projections.property("uuid"));
 307             projectionList.add(Projections.property("id"));
 308             projectionList.add(Projections.property("titleCache"));
 309             criteria.setProjection(projectionList);
 310
 311             @SuppressWarnings("unchecked")
 312             List<Object[]> result = criteria.list();
 313             List<UuidAndTitleCache<SpecimenOrObservationBase>> uuidAndTitleCacheList = new ArrayList<>();
 314             for(Object[] object : result){
 315                 uuidAndTitleCacheList.add(new UuidAndTitleCache<>((UUID) object[0],(Integer) object[1], (String) object[2]));
 316             }
 317             return uuidAndTitleCacheList;
 318         }else{
 319             return Collections.emptyList();
 320         }
 321     }
 322
 323     @Override
 324     public <T extends SpecimenOrObservationBase> List<T> findOccurrences(Class<T> clazz, String queryString,
 325             String significantIdentifier, SpecimenOrObservationType recordBasis, Taxon associatedTaxon, TaxonName associatedTaxonName,
 326             MatchMode matchmode, Integer limit, Integer start, List<OrderHint> orderHints, List<String> propertyPaths) {
 327
 328         Criteria criteria = createFindOccurrenceCriteria(clazz, queryString, significantIdentifier, recordBasis,
 329                 associatedTaxon, associatedTaxonName, matchmode, limit, start, orderHints, propertyPaths);
 330         if(criteria!=null){
 331             @SuppressWarnings("unchecked")
 332             List<T> results = criteria.list();
 333             defaultBeanInitializer.initializeAll(results, propertyPaths);
 334             return results;
 335         }else{
 336             return Collections.emptyList();
 337         }
 338     }
 339
 340     private <T extends SpecimenOrObservationBase> Criteria createFindOccurrenceCriteria(Class<T> clazz, String queryString,
 341             String significantIdentifier, SpecimenOrObservationType recordBasis, Taxon associatedTaxon, TaxonName associatedTaxonName, MatchMode matchmode, Integer limit,
 342             Integer start, List<OrderHint> orderHints, List<String> propertyPaths) {
 343         Criteria criteria = null;
 344
 345         if(clazz == null) {
 346             criteria = getSession().createCriteria(type);
 347         } else {
 348             criteria = getSession().createCriteria(clazz);
 349         }
 350
 351         //queryString
 352         if (queryString != null) {
 353             if(matchmode == null) {
 354                 matchmode = MatchMode.ANYWHERE;
 355                 criteria.add(Restrictions.ilike("titleCache", matchmode.queryStringFrom(queryString)));
 356             } else if(matchmode == MatchMode.BEGINNING) {
 357                 criteria.add(Restrictions.ilike("titleCache", matchmode.queryStringFrom(queryString), org.hibernate.criterion.MatchMode.START));
 358             } else if(matchmode == MatchMode.END) {
 359                 criteria.add(Restrictions.ilike("titleCache", matchmode.queryStringFrom(queryString), org.hibernate.criterion.MatchMode.END));
 360             } else if(matchmode == MatchMode.EXACT) {
 361                 criteria.add(Restrictions.ilike("titleCache", matchmode.queryStringFrom(queryString), org.hibernate.criterion.MatchMode.EXACT));
 362             } else {
 363                 criteria.add(Restrictions.ilike("titleCache", matchmode.queryStringFrom(queryString), org.hibernate.criterion.MatchMode.ANYWHERE));
 364             }
 365         }
 366
 367         //significant identifier
 368         if (significantIdentifier != null) {
 369             criteria.add(Restrictions.or(Restrictions.ilike("accessionNumber", significantIdentifier),
 370                     Restrictions.ilike("catalogNumber", significantIdentifier), Restrictions.ilike("barcode", significantIdentifier)));
 371         }
 372
 373         //recordBasis/SpecimenOrObservationType
 374         Set<SpecimenOrObservationType> typeAndSubtypes = new HashSet<SpecimenOrObservationType>();
 375         if(recordBasis==null){
 376             //add all types
 377             SpecimenOrObservationType[] values = SpecimenOrObservationType.values();
 378             for (SpecimenOrObservationType specimenOrObservationType : values) {
 379                 typeAndSubtypes.add(specimenOrObservationType);
 380             }
 381         }
 382         else{
 383             typeAndSubtypes = recordBasis.getGeneralizationOf(true);
 384             typeAndSubtypes.add(recordBasis);
 385         }
 386         criteria.add(Restrictions.in("recordBasis", typeAndSubtypes));
 387
 388         Set<UUID> associationUuids = new HashSet<UUID>();
 389         //taxon associations
 390         if(associatedTaxon!=null){
 391             List<UuidAndTitleCache<SpecimenOrObservationBase>> associatedTaxaList = listUuidAndTitleCacheByAssociatedTaxon(clazz, associatedTaxon, limit, start, orderHints);
 392             if(associatedTaxaList!=null){
 393                 for (UuidAndTitleCache<SpecimenOrObservationBase> uuidAndTitleCache : associatedTaxaList) {
 394                     associationUuids.add(uuidAndTitleCache.getUuid());
 395                 }
 396             }
 397         }
 398         //taxon name associations
 399         else if(associatedTaxonName!=null){
 400             List<? extends SpecimenOrObservationBase> associatedTaxaList = listByAssociatedTaxonName(clazz, associatedTaxonName, limit, start, orderHints, propertyPaths);
 401             if(associatedTaxaList!=null){
 402                 for (SpecimenOrObservationBase<?> specimenOrObservationBase : associatedTaxaList) {
 403                     associationUuids.add(specimenOrObservationBase.getUuid());
 404                 }
 405             }
 406         }
 407         if(associatedTaxon!=null || associatedTaxonName!=null){
 408             if(!associationUuids.isEmpty()){
 409                 criteria.add(Restrictions.in("uuid", associationUuids));
 410             }
 411             else{
 412                 return null;
 413             }
 414         }
 415         if(limit != null) {
 416             if(start != null) {
 417                 criteria.setFirstResult(start);
 418             } else {
 419                 criteria.setFirstResult(0);
 420             }
 421             criteria.setMaxResults(limit);
 422         }
 423
 424         if(orderHints!=null){
 425             addOrder(criteria,orderHints);
 426         }
 427         return criteria;
 428     }
 429
 430
 431     @Override
 432     public <T extends SpecimenOrObservationBase> long countOccurrences(Class<T> clazz, String queryString,
 433             String significantIdentifier, SpecimenOrObservationType recordBasis, Taxon associatedTaxon, TaxonName associatedTaxonName,
 434             MatchMode matchmode, Integer limit, Integer start, List<OrderHint> orderHints, List<String> propertyPaths) {
 435
 436         Criteria criteria = createFindOccurrenceCriteria(clazz, queryString, significantIdentifier, recordBasis,
 437                 associatedTaxon, associatedTaxonName, matchmode, limit, start, orderHints, propertyPaths);
 438
 439         if(criteria!=null){
 440             criteria.setProjection(Projections.rowCount());
 441             return (Long)criteria.uniqueResult();
 442         }else{
 443             return 0;
 444         }
 445     }
 446
 447     @Override
 448     public List<UuidAndTitleCache<DerivedUnit>> getDerivedUnitUuidAndTitleCache(Integer limit, String pattern) {
 449         List<UuidAndTitleCache<DerivedUnit>> list = new ArrayList<>();
 450         Session session = getSession();
 451         String hql = "SELECT uuid, id, titleCache "
 452                 + " FROM " + type.getSimpleName()
 453                 + " WHERE NOT dtype = " + FieldUnit.class.getSimpleName();
 454         Query query;
 455         if (pattern != null){
 456             pattern = pattern.replace("*", "%");
 457             pattern = pattern.replace("?", "_");
 458             pattern = pattern + "%";
 459             query = session.createQuery(hql +" AND titleCache like :pattern");
 460             query.setParameter("pattern", pattern);
 461         } else {
 462             query = session.createQuery(hql);
 463         }
 464         if (limit != null){
 465            query.setMaxResults(limit);
 466         }
 467
 468         @SuppressWarnings("unchecked")
 469         List<Object[]> result = query.list();
 470
 471         for(Object[] object : result){
 472             list.add(new UuidAndTitleCache<DerivedUnit>(DerivedUnit.class, (UUID) object[0], (Integer)object[1], (String) object[2]));
 473         }
 474
 475         return list;
 476     }
 477
 478     @Override
 479     public List<UuidAndTitleCache<FieldUnit>> getFieldUnitUuidAndTitleCache() {
 480         List<UuidAndTitleCache<FieldUnit>> list = new ArrayList<>();
 481         Session session = getSession();
 482
 483         Query query = session.createQuery("select uuid, id, titleCache from " + type.getSimpleName() + " where dtype = " + FieldUnit.class.getSimpleName());
 484
 485         @SuppressWarnings("unchecked")
 486         List<Object[]> result = query.list();
 487
 488         for(Object[] object : result){
 489             list.add(new UuidAndTitleCache<FieldUnit>(FieldUnit.class, (UUID) object[0], (Integer)object[1], (String) object[2]));
 490         }
 491
 492         return list;
 493     }
 494
 495     @Override
 496     public <T extends SpecimenOrObservationBase> List<T> listByAssociatedTaxonName(Class<T> type,
 497             TaxonName associatedTaxonName, Integer limit, Integer start, List<OrderHint> orderHints, List<String> propertyPaths) {
 498
 499         @SuppressWarnings("rawtypes")
 500         Set<SpecimenOrObservationBase> setOfAll = new HashSet<>();
 501
 502         // A Taxon name may be referenced by the DeterminationEvent of the SpecimenOrObservationBase
 503         @SuppressWarnings("rawtypes")
 504         List<SpecimenOrObservationBase> byDetermination = list(type, associatedTaxonName, null, 0, null, null);
 505         setOfAll.addAll(byDetermination);
 506
 507         if(setOfAll.size() == 0){
 508             // no need querying the data base
 509             return new ArrayList<T>();
 510         }
 511
 512         String queryString =
 513             "SELECT sob " +
 514             " FROM SpecimenOrObservationBase sob" +
 515             " WHERE sob in (:setOfAll)";
 516
 517         if(type != null && !type.equals(SpecimenOrObservationBase.class)){
 518             queryString += " AND sob.class = :type";
 519         }
 520         queryString += orderByClause("sob", orderHints);
 521
 522         Query query = getSession().createQuery(queryString);
 523         query.setParameterList("setOfAll", setOfAll);
 524
 525         if(type != null && !type.equals(SpecimenOrObservationBase.class)){
 526             query.setParameter("type", type.getSimpleName());
 527         }
 528
 529         addLimitAndStart(query, limit, start);
 530
 531         @SuppressWarnings("unchecked")
 532         List<T> results = query.list();
 533         defaultBeanInitializer.initializeAll(results, propertyPaths);
 534         return results;
 535     }
 536
 537     private List<SpecimenNodeWrapper> querySpecimen(
 538             Query query, List<UUID> taxonNodeUuids,
 539             Integer limit, Integer start){
 540         query.setParameterList("taxonNodeUuids", taxonNodeUuids);
 541
 542         addLimitAndStart(query, limit, start);
 543
 544         List<SpecimenNodeWrapper> list = new ArrayList<>();
 545         @SuppressWarnings("unchecked")
 546         List<Object[]> result = query.list();
 547         for(Object[] object : result){
 548             list.add(new SpecimenNodeWrapper(
 549                     new UuidAndTitleCache<>(
 550                             (UUID) object[0],
 551                             (Integer) object[1],
 552                             (String) object[2]),
 553                     (TaxonNode)object[3]));
 554         }
 555         return list;
 556     }
 557
 558     private List<SpecimenNodeWrapper> queryIndividualAssociatedSpecimen(List<UUID> taxonNodeUuids,
 559             Integer limit, Integer start){
 560         String queryString =  "SELECT "
 561                 + "de.associatedSpecimenOrObservation.uuid, "
 562                 + "de.associatedSpecimenOrObservation.id, "
 563                 + "de.associatedSpecimenOrObservation.titleCache, "
 564                 + "tn "
 565                 + "FROM DescriptionElementBase AS de "
 566                 + "LEFT JOIN de.inDescription AS d "
 567                 + "LEFT JOIN d.taxon AS t "
 568                 + "JOIN t.taxonNodes AS tn "
 569                 + "WHERE d.class = 'TaxonDescription' "
 570                 + "AND tn.uuid in (:taxonNodeUuids) "
 571                 ;
 572         Query query = getSession().createQuery(queryString);
 573         return querySpecimen(query, taxonNodeUuids, limit, start);
 574     }
 575
 576     private List<SpecimenNodeWrapper> queryTypeSpecimen(List<UUID> taxonNodeUuids,
 577             Integer limit, Integer start){
 578         String queryString =  "SELECT "
 579                 + "td.typeSpecimen.uuid, "
 580                 + "td.typeSpecimen.id, "
 581                 + "td.typeSpecimen.titleCache, "
 582                 + "tn "
 583                 + "FROM SpecimenTypeDesignation AS td "
 584                 + "LEFT JOIN td.typifiedNames AS tn "
 585                 + "LEFT JOIN tn.taxonBases AS t "
 586                 + "JOIN t.taxonNodes AS tn "
 587                 + "WHERE tn.uuid in (:taxonNodeUuids) "
 588                 ;
 589         Query query = getSession().createQuery(queryString);
 590         return querySpecimen(query, taxonNodeUuids, limit, start);
 591     }
 592
 593     private List<SpecimenNodeWrapper> queryTaxonDeterminations(List<UUID> taxonNodeUuids,
 594             Integer limit, Integer start){
 595         String queryString =  "SELECT "
 596                 + "det.identifiedUnit.uuid, "
 597                 + "det.identifiedUnit.id, "
 598                 + "det.identifiedUnit.titleCache, "
 599                 + "tn "
 600                 + "FROM DeterminationEvent AS det "
 601                 + "LEFT JOIN det.taxon AS t "
 602                 + "JOIN t.taxonNodes AS tn "
 603                 + "WHERE tn.uuid in (:taxonNodeUuids) "
 604                 ;
 605         Query query = getSession().createQuery(queryString);
 606         return querySpecimen(query, taxonNodeUuids, limit, start);
 607     }
 608
 609     private List<SpecimenNodeWrapper> queryTaxonNameDeterminations(List<UUID> taxonNodeUuids,
 610             Integer limit, Integer start){
 611         String queryString =  "SELECT "
 612                 + "det.identifiedUnit.uuid, "
 613                 + "det.identifiedUnit.id, "
 614                 + "det.identifiedUnit.titleCache, "
 615                 + "tn "
 616                 + "FROM DeterminationEvent AS det "
 617                 + "LEFT JOIN det.taxonName AS n "
 618                 + "LEFT JOIN n.taxonBases AS t "
 619                 + "JOIN t.taxonNodes AS tn "
 620                 + "WHERE tn.uuid in (:taxonNodeUuids) "
 621                 ;
 622         Query query = getSession().createQuery(queryString);
 623         return querySpecimen(query, taxonNodeUuids, limit, start);
 624     }
 625
 626     @Override
 627     public Collection<SpecimenNodeWrapper> listUuidAndTitleCacheByAssociatedTaxon(List<UUID> taxonNodeUuids,
 628             Integer limit, Integer start){
 629
 630         Collection<SpecimenNodeWrapper> wrappers = new HashSet<>();
 631         wrappers.addAll(queryIndividualAssociatedSpecimen(taxonNodeUuids, limit, start));
 632         wrappers.addAll(queryTaxonDeterminations(taxonNodeUuids, limit, start));
 633         wrappers.addAll(queryTaxonNameDeterminations(taxonNodeUuids, limit, start));
 634         wrappers.addAll(queryTypeSpecimen(taxonNodeUuids, limit, start));
 635
 636         return wrappers;
 637     }
 638
 639     @Override
 640     public <T extends SpecimenOrObservationBase> List<UuidAndTitleCache<SpecimenOrObservationBase>> listUuidAndTitleCacheByAssociatedTaxon(Class<T> clazz, Taxon associatedTaxon,
 641             Integer limit, Integer start, List<OrderHint> orderHints){
 642         Query query = createSpecimenQuery("sob.uuid, sob.id, sob.titleCache", clazz, associatedTaxon, limit, start, orderHints, null);
 643         if(query==null){
 644             return Collections.emptyList();
 645         }
 646         List<UuidAndTitleCache<SpecimenOrObservationBase>> list = new ArrayList<>();
 647         @SuppressWarnings("unchecked")
 648         List<Object[]> result = query.list();
 649         for(Object[] object : result){
 650             list.add(new UuidAndTitleCache<>((UUID) object[0],(Integer) object[1], (String) object[2]));
 651         }
 652         return list;
 653     }
 654
 655     @Override
 656     public <T extends SpecimenOrObservationBase> List<T> listByAssociatedTaxon(Class<T> clazz,
 657             Taxon associatedTaxon, Integer limit, Integer start, List<OrderHint> orderHints, List<String> propertyPaths) {
 658         Query query = createSpecimenQuery("sob", clazz, associatedTaxon, limit, start, orderHints, propertyPaths);
 659         if(query==null){
 660             return Collections.emptyList();
 661         }
 662         @SuppressWarnings("unchecked")
 663         List<T> results = query.list();
 664         defaultBeanInitializer.initializeAll(results, propertyPaths);
 665         return results;
 666     }
 667
 668     private <T extends SpecimenOrObservationBase> Query createSpecimenQuery(String select, Class<T> clazz,
 669             Taxon associatedTaxon, Integer limit, Integer start, List<OrderHint> orderHints, List<String> propertyPaths){
 670         Set<SpecimenOrObservationBase> setOfAll = new HashSet<>();
 671         Set<Integer> setOfAllIds = new HashSet<>();
 672
 673         Criteria criteria = null;
 674         if(clazz == null) {
 675             criteria = getSession().createCriteria(type, "specimen");
 676         } else {
 677             criteria = getSession().createCriteria(clazz, "specimen");
 678         }
 679
 680         Disjunction determinationOr = Restrictions.disjunction();
 681
 682         // A Taxon may be referenced by the DeterminationEvent of the SpecimenOrObservationBase
 683         Criteria determinationsCriteria = criteria.createCriteria("determinations");
 684
 685         determinationOr.add(Restrictions.eq("taxon", associatedTaxon));
 686         //check also for synonyms
 687         for (Synonym synonym : associatedTaxon.getSynonyms()) {
 688             determinationOr.add(Restrictions.eq("taxon", synonym));
 689         }
 690
 691         //check also for name determinations
 692         determinationOr.add(Restrictions.eq("taxonName", associatedTaxon.getName()));
 693         for (TaxonName synonymName : associatedTaxon.getSynonymNames()) {
 694             determinationOr.add(Restrictions.eq("taxonName", synonymName));
 695         }
 696
 697         determinationsCriteria.add(determinationOr);
 698
 699         if(limit != null) {
 700             if(start != null) {
 701                 criteria.setFirstResult(start);
 702             } else {
 703                 criteria.setFirstResult(0);
 704             }
 705             criteria.setMaxResults(limit);
 706         }
 707         criteria.setProjection(Projections.property("id"));
 708
 709         addOrder(criteria,orderHints);
 710
 711         @SuppressWarnings("unchecked")
 712         List<Integer> detResults = criteria.list();
 713         setOfAllIds.addAll(detResults);
 714
 715         // The IndividualsAssociation elements in a TaxonDescription contain DerivedUnits
 716         setOfAllIds.addAll(descriptionDao.getIndividualAssociationSpecimenIDs(
 717                 associatedTaxon.getUuid(), null, null, 0, null));
 718
 719
 720         // SpecimenTypeDesignations may be associated with the TaxonName.
 721         setOfAllIds.addAll(taxonNameDao.getTypeSpecimenIdsForTaxonName(
 722                 associatedTaxon.getName(), null, null, null));
 723
 724         // SpecimenTypeDesignations may be associated with any HomotypicalGroup related to the specific Taxon.
 725         //TODO adapt to set of ids
 726         for(HomotypicalGroup homotypicalGroup :  associatedTaxon.getHomotypicSynonymyGroups()) {
 727             List<SpecimenTypeDesignation> byHomotypicalGroup = homotypicalGroupDao.getTypeDesignations(homotypicalGroup, SpecimenTypeDesignation.class, null, null, 0, null);
 728             for (SpecimenTypeDesignation specimenTypeDesignation : byHomotypicalGroup) {
 729                 setOfAll.add(specimenTypeDesignation.getTypeSpecimen());
 730             }
 731         }
 732
 733         if(setOfAllIds.size() == 0){
 734             // no need querying the data base
 735             return null;
 736         }
 737
 738         String queryString =
 739             "select "+select+
 740             " from SpecimenOrObservationBase sob" +
 741             " where sob.id in (:setOfAllIds)";
 742
 743         if(clazz != null && !clazz.equals(SpecimenOrObservationBase.class)){
 744             queryString += " and sob.class = :type ";
 745         }
 746
 747         if(orderHints != null && orderHints.size() > 0){
 748             queryString += " order by ";
 749             String orderStr = "";
 750             for(OrderHint orderHint : orderHints){
 751                 if(orderStr.length() > 0){
 752                     orderStr += ", ";
 753                 }
 754                 queryString += "sob." + orderHint.getPropertyName() + " " + orderHint.getSortOrder().toHql();
 755             }
 756             queryString += orderStr;
 757         }
 758
 759         Query query = getSession().createQuery(queryString);
 760         query.setParameterList("setOfAllIds", setOfAllIds);
 761
 762         if(clazz != null && !clazz.equals(SpecimenOrObservationBase.class)){
 763             query.setParameter("type", clazz.getSimpleName());
 764         }
 765
 766         addLimitAndStart(query, limit, start);
 767
 768         return query;
 769     }
 770
 771     @Override
 772     public Collection<SpecimenOrObservationBase> listBySpecimenOrObservationType(SpecimenOrObservationType type, Integer limit, Integer start, List<OrderHint> orderHints, List<String> propertyPaths) {
 773         String queryString = "FROM SpecimenOrObservationBase specimens "
 774                 + " WHERE specimens.recordBasis = :type ";
 775
 776         queryString += orderByClause("specimens", orderHints);
 777
 778         Query query = getSession().createQuery(queryString);
 779         query.setParameter("type", type);
 780
 781         addLimitAndStart(query, limit, start);
 782
 783         @SuppressWarnings({ "unchecked", "rawtypes" })
 784         List<SpecimenOrObservationBase> results = query.list();
 785         defaultBeanInitializer.initializeAll(results, propertyPaths);
 786         return results;
 787     }
 788
 789
 790     @Override
 791     public Collection<DeterminationEvent> listDeterminationEvents(SpecimenOrObservationBase<?> specimen, Integer limit, Integer start, List<OrderHint> orderHints, List<String> propertyPaths) {
 792         String queryString = "FROM DeterminationEvent determination "
 793                 + " WHERE determination.identifiedUnit = :specimen";
 794
 795         queryString += orderByClause("determination", orderHints);
 796
 797         Query query = getSession().createQuery(queryString);
 798         query.setParameter("specimen", specimen);
 799
 800         addLimitAndStart(query, limit, start);
 801
 802         @SuppressWarnings("unchecked")
 803         List<DeterminationEvent> results = query.list();
 804         defaultBeanInitializer.initializeAll(results, propertyPaths);
 805         return results;
 806     }
 807
 808     @Override
 809     public Collection<SpecimenTypeDesignation> listTypeDesignations(SpecimenOrObservationBase<?> specimen, Integer limit, Integer start, List<OrderHint> orderHints, List<String> propertyPaths) {
 810         String queryString = "FROM SpecimenTypeDesignation designations "
 811                 + " WHERE designations.typeSpecimen = :specimen";
 812
 813         queryString += orderByClause("designations", orderHints);
 814
 815         Query query = getSession().createQuery(queryString);
 816         query.setParameter("specimen", specimen);
 817
 818         addLimitAndStart(query, limit, start);
 819
 820         @SuppressWarnings("unchecked")
 821         List<SpecimenTypeDesignation> results = query.list();
 822         defaultBeanInitializer.initializeAll(results, propertyPaths);
 823         return results;
 824     }
 825
 826
 827     @Override
 828     public Collection<IndividualsAssociation> listIndividualsAssociations(SpecimenOrObservationBase<?> specimen, Integer limit, Integer start, List<OrderHint> orderHints, List<String> propertyPaths) {
 829         //DISTINCT is necessary if more than one description exists for a taxon because we create the cross product of all taxon descriptions and description elements
 830         String queryString = "FROM IndividualsAssociation associations WHERE associations.associatedSpecimenOrObservation = :specimen";
 831
 832         queryString += orderByClause("associations", orderHints);
 833
 834         Query query = getSession().createQuery(queryString);
 835         query.setParameter("specimen", specimen);
 836
 837         addLimitAndStart(query, limit, start);
 838
 839         @SuppressWarnings("unchecked")
 840         List<IndividualsAssociation> results = query.list();
 841         defaultBeanInitializer.initializeAll(results, propertyPaths);
 842         return results;
 843     }
 844
 845     @Override
 846     public Collection<DescriptionBase<?>> listDescriptionsWithDescriptionSpecimen(SpecimenOrObservationBase<?> specimen, Integer limit, Integer start, List<OrderHint> orderHints, List<String> propertyPaths) {
 847         //DISTINCT is necessary if more than one description exists for a taxon because we create the cross product of all taxon descriptions and description elements
 848         String queryString = "FROM DescriptionBase descriptions "
 849                 + " WHERE descriptions.describedSpecimenOrObservation = :specimen";
 850
 851         queryString += orderByClause("descriptions", orderHints);
 852
 853         Query query = getSession().createQuery(queryString);
 854         query.setParameter("specimen", specimen);
 855
 856         addLimitAndStart(query, limit, start);
 857
 858         @SuppressWarnings("unchecked")
 859         List<DescriptionBase<?>> results = query.list();
 860         defaultBeanInitializer.initializeAll(results, propertyPaths);
 861         return results;
 862     }
 863
 864     /**
 865      * {@inheritDoc}
 866      */
 867     @Override
 868     public List<FieldUnit> getFieldUnitsForGatheringEvent(UUID gatheringEventUuid, Integer limit, Integer start, List<OrderHint> orderHints, List<String> propertyPaths) {
 869         String queryString = "FROM SpecimenOrObservationBase sob "
 870                 + "WHERE sob.gatheringEvent.uuid = :gatheringEventUuid";
 871
 872         queryString += orderByClause("sob", orderHints);
 873
 874         Query query = getSession().createQuery(queryString);
 875         query.setParameter("gatheringEventUuid", gatheringEventUuid);
 876
 877         addLimitAndStart(query, limit, start);
 878
 879         @SuppressWarnings("unchecked")
 880         List<FieldUnit> results = query.list();
 881         defaultBeanInitializer.initializeAll(results, propertyPaths);
 882         return results;
 883     }
 884
 885     /**
 886      * {@inheritDoc}
 887      */
 888     @Override
 889     public List<DerivedUnit> getByGeneticAccessionNumber(String accessionNumberString, List<String> propertyPaths) {
 890         String queryString = "SELECT dnaSample FROM DnaSample dnaSample join dnaSample.sequences sequence WHERE sequence.geneticAccessionNumber LIKE :accessionNumberString";
 891         Query query = getSession().createQuery(queryString);
 892         query.setParameter("accessionNumberString", accessionNumberString);
 893         @SuppressWarnings("unchecked")
 894         List<DerivedUnit> results = query.list();
 895         defaultBeanInitializer.initializeAll(results, propertyPaths);
 896         return results;
 897     }
 898
 899 }