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