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, TaxonName associatedTaxonName, MatchMode matchmode, Integer limit,
 345             Integer start, List<OrderHint> orderHints, List<String> propertyPaths) {
 346         Criteria criteria = null;
 347
 348         if(clazz == null) {
 349             criteria = getSession().createCriteria(type);
 350         } else {
 351             criteria = getSession().createCriteria(clazz);
 352         }
 353
 354         //queryString
 355         if (queryString != null) {
 356             if(matchmode == null) {
 357                 matchmode = MatchMode.ANYWHERE;
 358                 criteria.add(Restrictions.ilike("titleCache", matchmode.queryStringFrom(queryString)));
 359             } else if(matchmode == MatchMode.BEGINNING) {
 360                 criteria.add(Restrictions.ilike("titleCache", matchmode.queryStringFrom(queryString), org.hibernate.criterion.MatchMode.START));
 361             } else if(matchmode == MatchMode.END) {
 362                 criteria.add(Restrictions.ilike("titleCache", matchmode.queryStringFrom(queryString), org.hibernate.criterion.MatchMode.END));
 363             } else if(matchmode == MatchMode.EXACT) {
 364                 criteria.add(Restrictions.ilike("titleCache", matchmode.queryStringFrom(queryString), org.hibernate.criterion.MatchMode.EXACT));
 365             } else {
 366                 criteria.add(Restrictions.ilike("titleCache", matchmode.queryStringFrom(queryString), org.hibernate.criterion.MatchMode.ANYWHERE));
 367             }
 368         }
 369
 370         //significant identifier
 371         if (significantIdentifier != null) {
 372             criteria.add(Restrictions.or(Restrictions.ilike("accessionNumber", significantIdentifier),
 373                     Restrictions.ilike("catalogNumber", significantIdentifier), Restrictions.ilike("barcode", significantIdentifier)));
 374         }
 375
 376         //recordBasis/SpecimenOrObservationType
 377         Set<SpecimenOrObservationType> typeAndSubtypes = new HashSet<SpecimenOrObservationType>();
 378         if(recordBasis==null){
 379             //add all types
 380             SpecimenOrObservationType[] values = SpecimenOrObservationType.values();
 381             for (SpecimenOrObservationType specimenOrObservationType : values) {
 382                 typeAndSubtypes.add(specimenOrObservationType);
 383             }
 384         }
 385         else{
 386             typeAndSubtypes = recordBasis.getGeneralizationOf(true);
 387             typeAndSubtypes.add(recordBasis);
 388         }
 389         criteria.add(Restrictions.in("recordBasis", typeAndSubtypes));
 390
 391         Set<UUID> associationUuids = new HashSet<UUID>();
 392         //taxon associations
 393         if(associatedTaxon!=null){
 394             List<UuidAndTitleCache<SpecimenOrObservationBase>> associatedTaxaList = listUuidAndTitleCacheByAssociatedTaxon(clazz, associatedTaxon, limit, start, orderHints);
 395             if(associatedTaxaList!=null){
 396                 for (UuidAndTitleCache<SpecimenOrObservationBase> uuidAndTitleCache : associatedTaxaList) {
 397                     associationUuids.add(uuidAndTitleCache.getUuid());
 398                 }
 399             }
 400         }
 401         //taxon name associations
 402         else if(associatedTaxonName!=null){
 403             List<? extends SpecimenOrObservationBase> associatedTaxaList = listByAssociatedTaxonName(clazz, associatedTaxonName, limit, start, orderHints, propertyPaths);
 404             if(associatedTaxaList!=null){
 405                 for (SpecimenOrObservationBase<?> specimenOrObservationBase : associatedTaxaList) {
 406                     associationUuids.add(specimenOrObservationBase.getUuid());
 407                 }
 408             }
 409         }
 410         if(associatedTaxon!=null || associatedTaxonName!=null){
 411             if(!associationUuids.isEmpty()){
 412                 criteria.add(Restrictions.in("uuid", associationUuids));
 413             }
 414             else{
 415                 return null;
 416             }
 417         }
 418         if(limit != null) {
 419             if(start != null) {
 420                 criteria.setFirstResult(start);
 421             } else {
 422                 criteria.setFirstResult(0);
 423             }
 424             criteria.setMaxResults(limit);
 425         }
 426
 427         if(orderHints!=null){
 428             addOrder(criteria,orderHints);
 429         }
 430         return criteria;
 431     }
 432
 433
 434     @Override
 435     public <T extends SpecimenOrObservationBase> long countOccurrences(Class<T> clazz, String queryString,
 436             String significantIdentifier, SpecimenOrObservationType recordBasis, Taxon associatedTaxon, TaxonName associatedTaxonName,
 437             MatchMode matchmode, Integer limit, Integer start, List<OrderHint> orderHints, List<String> propertyPaths) {
 438
 439         Criteria criteria = createFindOccurrenceCriteria(clazz, queryString, significantIdentifier, recordBasis,
 440                 associatedTaxon, associatedTaxonName, matchmode, limit, start, orderHints, propertyPaths);
 441
 442         if(criteria!=null){
 443             criteria.setProjection(Projections.rowCount());
 444             return (Long)criteria.uniqueResult();
 445         }else{
 446             return 0;
 447         }
 448     }
 449
 450     @Override
 451     public List<UuidAndTitleCache<DerivedUnit>> getDerivedUnitUuidAndTitleCache(Integer limit, String pattern) {
 452         List<UuidAndTitleCache<DerivedUnit>> list = new ArrayList<>();
 453         Session session = getSession();
 454         String hql = "SELECT uuid, id, titleCache "
 455                 + " FROM " + type.getSimpleName()
 456                 + " WHERE NOT dtype = " + FieldUnit.class.getSimpleName();
 457         Query query;
 458         if (pattern != null){
 459             pattern = pattern.replace("*", "%");
 460             pattern = pattern.replace("?", "_");
 461             pattern = pattern + "%";
 462             query = session.createQuery(hql +" AND titleCache like :pattern");
 463             query.setParameter("pattern", pattern);
 464         } else {
 465             query = session.createQuery(hql);
 466         }
 467         if (limit != null){
 468            query.setMaxResults(limit);
 469         }
 470
 471         @SuppressWarnings("unchecked")
 472         List<Object[]> result = query.list();
 473
 474         for(Object[] object : result){
 475             list.add(new UuidAndTitleCache<DerivedUnit>(DerivedUnit.class, (UUID) object[0], (Integer)object[1], (String) object[2]));
 476         }
 477
 478         return list;
 479     }
 480
 481     @Override
 482     public List<UuidAndTitleCache<FieldUnit>> getFieldUnitUuidAndTitleCache() {
 483         List<UuidAndTitleCache<FieldUnit>> list = new ArrayList<>();
 484         Session session = getSession();
 485
 486         Query query = session.createQuery("select uuid, id, titleCache from " + type.getSimpleName() + " where dtype = " + FieldUnit.class.getSimpleName());
 487
 488         @SuppressWarnings("unchecked")
 489         List<Object[]> result = query.list();
 490
 491         for(Object[] object : result){
 492             list.add(new UuidAndTitleCache<FieldUnit>(FieldUnit.class, (UUID) object[0], (Integer)object[1], (String) object[2]));
 493         }
 494
 495         return list;
 496     }
 497
 498     @Override
 499     public <T extends SpecimenOrObservationBase> List<T> listByAssociatedTaxonName(Class<T> type,
 500             TaxonName associatedTaxonName, Integer limit, Integer start, List<OrderHint> orderHints, List<String> propertyPaths) {
 501
 502         @SuppressWarnings("rawtypes")
 503         Set<SpecimenOrObservationBase> setOfAll = new HashSet<>();
 504
 505         // A Taxon name may be referenced by the DeterminationEvent of the SpecimenOrObservationBase
 506         @SuppressWarnings("rawtypes")
 507         List<SpecimenOrObservationBase> byDetermination = list(type, associatedTaxonName, null, 0, null, null);
 508         setOfAll.addAll(byDetermination);
 509
 510         if(setOfAll.size() == 0){
 511             // no need querying the data base
 512             return new ArrayList<T>();
 513         }
 514
 515         String queryString =
 516             "SELECT sob " +
 517             " FROM SpecimenOrObservationBase sob" +
 518             " WHERE sob in (:setOfAll)";
 519
 520         if(type != null && !type.equals(SpecimenOrObservationBase.class)){
 521             queryString += " AND sob.class = :type";
 522         }
 523         queryString += orderByClause("sob", orderHints);
 524
 525         Query query = getSession().createQuery(queryString);
 526         query.setParameterList("setOfAll", setOfAll);
 527
 528         if(type != null && !type.equals(SpecimenOrObservationBase.class)){
 529             query.setParameter("type", type.getSimpleName());
 530         }
 531
 532         addLimitAndStart(query, limit, start);
 533
 534         @SuppressWarnings("unchecked")
 535         List<T> results = query.list();
 536         defaultBeanInitializer.initializeAll(results, propertyPaths);
 537         return results;
 538     }
 539
 540     private List<SpecimenNodeWrapper> querySpecimen(Query query, List<UUID> taxonNodeUuids,
 541             Integer limit, Integer start){
 542         query.setParameterList("taxonNodeUuids", taxonNodeUuids);
 543
 544         addLimitAndStart(query, limit, start);
 545
 546         List<SpecimenNodeWrapper> list = new ArrayList<>();
 547         @SuppressWarnings("unchecked")
 548         List<Object[]> result = query.list();
 549         for(Object[] object : result){
 550             SpecimenNodeWrapper wrapper = new SpecimenNodeWrapper(
 551                     new UuidAndTitleCache<>(
 552                             (UUID) object[0],
 553                             (Integer) object[1],
 554                             (String) object[2]),
 555                     (SpecimenOrObservationType)object[3],
 556                     new TaxonNodeDto((TaxonNode)object[4]));
 557             if(object.length>5) {
 558                 wrapper.setTaxonDescriptionUuid((UUID)object[5]);
 559             }
 560             list.add(wrapper);
 561         }
 562         return list;
 563     }
 564
 565     private List<SpecimenNodeWrapper> queryIndividualAssociatedSpecimen(List<UUID> taxonNodeUuids,
 566             Integer limit, Integer start){
 567         String queryString =  "SELECT "
 568                 + "de.associatedSpecimenOrObservation.uuid, "
 569                 + "de.associatedSpecimenOrObservation.id, "
 570                 + "de.associatedSpecimenOrObservation.titleCache, "
 571                 + "de.associatedSpecimenOrObservation.recordBasis, "
 572                 + "tn, "
 573                 + "d.uuid "
 574                 + "FROM DescriptionElementBase AS de "
 575                 + "LEFT JOIN de.inDescription AS d "
 576                 + "LEFT JOIN d.taxon AS t "
 577                 + "JOIN t.taxonNodes AS tn "
 578                 + "WHERE d.class = 'TaxonDescription' "
 579                 + "AND tn.uuid in (:taxonNodeUuids) "
 580                 ;
 581         Query query = getSession().createQuery(queryString);
 582         return querySpecimen(query, taxonNodeUuids, limit, start);
 583     }
 584
 585     private List<SpecimenNodeWrapper> queryTypeSpecimen(List<UUID> taxonNodeUuids,
 586             Integer limit, Integer start){
 587         String queryString =  "SELECT "
 588                 + "td.typeSpecimen.uuid, "
 589                 + "td.typeSpecimen.id, "
 590                 + "td.typeSpecimen.titleCache, "
 591                 + "td.typeSpecimen.recordBasis, "
 592                 + "tn "
 593                 + "FROM SpecimenTypeDesignation AS td "
 594                 + "LEFT JOIN td.typifiedNames AS tn "
 595                 + "LEFT JOIN tn.taxonBases AS t "
 596                 + "JOIN t.taxonNodes AS tn "
 597                 + "WHERE tn.uuid in (:taxonNodeUuids) "
 598                 ;
 599         Query query = getSession().createQuery(queryString);
 600         return querySpecimen(query, taxonNodeUuids, limit, start);
 601     }
 602
 603     private List<SpecimenNodeWrapper> queryTaxonDeterminations(List<UUID> taxonNodeUuids,
 604             Integer limit, Integer start){
 605         String queryString =  "SELECT "
 606                 + "det.identifiedUnit.uuid, "
 607                 + "det.identifiedUnit.id, "
 608                 + "det.identifiedUnit.titleCache, "
 609                 + "det.identifiedUnit.recordBasis, "
 610                 + "tn "
 611                 + "FROM DeterminationEvent AS det "
 612                 + "LEFT JOIN det.taxon AS t "
 613                 + "JOIN t.taxonNodes AS tn "
 614                 + "WHERE tn.uuid in (:taxonNodeUuids) "
 615                 ;
 616         Query query = getSession().createQuery(queryString);
 617         return querySpecimen(query, taxonNodeUuids, limit, start);
 618     }
 619
 620     private List<SpecimenNodeWrapper> queryTaxonNameDeterminations(List<UUID> taxonNodeUuids,
 621             Integer limit, Integer start){
 622         String queryString =  "SELECT "
 623                 + "det.identifiedUnit.uuid, "
 624                 + "det.identifiedUnit.id, "
 625                 + "det.identifiedUnit.titleCache, "
 626                 + "det.identifiedUnit.recordBasis, "
 627                 + "tn "
 628                 + "FROM DeterminationEvent AS det "
 629                 + "LEFT JOIN det.taxonName AS n "
 630                 + "LEFT JOIN n.taxonBases AS t "
 631                 + "JOIN t.taxonNodes AS tn "
 632                 + "WHERE tn.uuid in (:taxonNodeUuids) "
 633                 ;
 634         Query query = getSession().createQuery(queryString);
 635         return querySpecimen(query, taxonNodeUuids, limit, start);
 636     }
 637
 638     @Override
 639     public Collection<SpecimenNodeWrapper> listUuidAndTitleCacheByAssociatedTaxon(List<UUID> taxonNodeUuids,
 640             Integer limit, Integer start){
 641
 642         Set<SpecimenNodeWrapper> testSet = new HashSet();
 643
 644         testSet.addAll(queryIndividualAssociatedSpecimen(taxonNodeUuids, limit, start));
 645         testSet.addAll(queryTaxonDeterminations(taxonNodeUuids, limit, start));
 646         testSet.addAll(queryTaxonNameDeterminations(taxonNodeUuids, limit, start));
 647         testSet.addAll(queryTypeSpecimen(taxonNodeUuids, limit, start));
 648
 649         Collection<SpecimenNodeWrapper> wrappers = new HashSet<>();
 650         wrappers.addAll(testSet);
 651         return wrappers;
 652     }
 653
 654     @Override
 655     public <T extends SpecimenOrObservationBase> List<UuidAndTitleCache<SpecimenOrObservationBase>> listUuidAndTitleCacheByAssociatedTaxon(Class<T> clazz, Taxon associatedTaxon,
 656             Integer limit, Integer start, List<OrderHint> orderHints){
 657         Query query = createSpecimenQuery("sob.uuid, sob.id, sob.titleCache", clazz, associatedTaxon, limit, start, orderHints);
 658         if(query==null){
 659             return Collections.emptyList();
 660         }
 661         List<UuidAndTitleCache<SpecimenOrObservationBase>> list = new ArrayList<>();
 662         @SuppressWarnings("unchecked")
 663         List<Object[]> result = query.list();
 664         for(Object[] object : result){
 665             list.add(new UuidAndTitleCache<>((UUID) object[0],(Integer) object[1], (String) object[2]));
 666         }
 667         return list;
 668     }
 669
 670     @Override
 671     public <T extends SpecimenOrObservationBase> List<T> listByAssociatedTaxon(Class<T> clazz,
 672             Taxon associatedTaxon, Integer limit, Integer start, List<OrderHint> orderHints, List<String> propertyPaths) {
 673         Query query = createSpecimenQuery("sob", clazz, associatedTaxon, limit, start, orderHints);
 674         if(query==null){
 675             return Collections.emptyList();
 676         }
 677         @SuppressWarnings("unchecked")
 678         List<T> results = query.list();
 679         defaultBeanInitializer.initializeAll(results, propertyPaths);
 680         return results;
 681     }
 682
 683     private <T extends SpecimenOrObservationBase> Query createSpecimenQuery(String select, Class<T> clazz,
 684             Taxon associatedTaxon, Integer limit, Integer start, List<OrderHint> orderHints){
 685 //        Set<SpecimenOrObservationBase> setOfAll = new HashSet<>();
 686         Set<Integer> setOfAllIds = new HashSet<>();
 687
 688         Criteria criteria = null;
 689         if(clazz == null) {
 690             criteria = getSession().createCriteria(type, "specimen");
 691         } else {
 692             criteria = getSession().createCriteria(clazz, "specimen");
 693         }
 694
 695         Disjunction determinationOr = Restrictions.disjunction();
 696
 697         // A Taxon may be referenced by the DeterminationEvent of the SpecimenOrObservationBase
 698         Criteria determinationsCriteria = criteria.createCriteria("determinations");
 699
 700         determinationOr.add(Restrictions.eq("taxon", associatedTaxon));
 701         //check also for synonyms
 702         for (Synonym synonym : associatedTaxon.getSynonyms()) {
 703             determinationOr.add(Restrictions.eq("taxon", synonym));
 704         }
 705
 706         //check also for name determinations
 707         determinationOr.add(Restrictions.eq("taxonName", associatedTaxon.getName()));
 708         for (TaxonName synonymName : associatedTaxon.getSynonymNames()) {
 709             determinationOr.add(Restrictions.eq("taxonName", synonymName));
 710         }
 711
 712         determinationsCriteria.add(determinationOr);
 713
 714         if(limit != null) {
 715             if(start != null) {
 716                 criteria.setFirstResult(start);
 717             } else {
 718                 criteria.setFirstResult(0);
 719             }
 720             criteria.setMaxResults(limit);
 721         }
 722         criteria.setProjection(Projections.property("id"));
 723
 724         addOrder(criteria,orderHints);
 725
 726         @SuppressWarnings("unchecked")
 727         List<Integer> detResults = criteria.list();
 728         setOfAllIds.addAll(detResults);
 729
 730         // The IndividualsAssociation elements in a TaxonDescription contain DerivedUnits
 731         setOfAllIds.addAll(descriptionDao.getIndividualAssociationSpecimenIDs(
 732                 associatedTaxon.getUuid(), null, null, 0, null));
 733
 734
 735         // SpecimenTypeDesignations may be associated with the TaxonName.
 736         setOfAllIds.addAll(taxonNameDao.getTypeSpecimenIdsForTaxonName(
 737                 associatedTaxon.getName(), null, null, null));
 738
 739         // SpecimenTypeDesignations may be associated with any HomotypicalGroup related to the specific Taxon.
 740         //TODO adapt to set of ids
 741         for(HomotypicalGroup homotypicalGroup :  associatedTaxon.getHomotypicSynonymyGroups()) {
 742             List<SpecimenTypeDesignation> byHomotypicalGroup = homotypicalGroupDao.getTypeDesignations(homotypicalGroup, SpecimenTypeDesignation.class, null, null, 0, null);
 743             for (SpecimenTypeDesignation specimenTypeDesignation : byHomotypicalGroup) {
 744                 if (specimenTypeDesignation.getTypeSpecimen() != null){
 745                     setOfAllIds.add(specimenTypeDesignation.getTypeSpecimen().getId());
 746                 }
 747             }
 748         }
 749
 750         if(setOfAllIds.size() == 0){
 751             // no need querying the data base
 752             return null;
 753         }
 754
 755         String queryString =
 756             "select "+select+
 757             " from SpecimenOrObservationBase sob" +
 758             " where sob.id in (:setOfAllIds)";
 759
 760         if(clazz != null && !clazz.equals(SpecimenOrObservationBase.class)){
 761             queryString += " and sob.class = :type ";
 762         }
 763
 764         if(orderHints != null && orderHints.size() > 0){
 765             queryString += " order by ";
 766             String orderStr = "";
 767             for(OrderHint orderHint : orderHints){
 768                 if(orderStr.length() > 0){
 769                     orderStr += ", ";
 770                 }
 771                 queryString += "sob." + orderHint.getPropertyName() + " " + orderHint.getSortOrder().toHql();
 772             }
 773             queryString += orderStr;
 774         }
 775
 776         Query query = getSession().createQuery(queryString);
 777         query.setParameterList("setOfAllIds", setOfAllIds);
 778
 779         if(clazz != null && !clazz.equals(SpecimenOrObservationBase.class)){
 780             query.setParameter("type", clazz.getSimpleName());
 781         }
 782
 783         addLimitAndStart(query, limit, start);
 784
 785         return query;
 786     }
 787
 788     @Override
 789     public Collection<SpecimenOrObservationBase> listBySpecimenOrObservationType(SpecimenOrObservationType type, Integer limit, Integer start, List<OrderHint> orderHints, List<String> propertyPaths) {
 790         String queryString = "FROM SpecimenOrObservationBase specimens "
 791                 + " WHERE specimens.recordBasis = :type ";
 792
 793         queryString += orderByClause("specimens", orderHints);
 794
 795         Query query = getSession().createQuery(queryString);
 796         query.setParameter("type", type);
 797
 798         addLimitAndStart(query, limit, start);
 799
 800         @SuppressWarnings({ "unchecked", "rawtypes" })
 801         List<SpecimenOrObservationBase> results = query.list();
 802         defaultBeanInitializer.initializeAll(results, propertyPaths);
 803         return results;
 804     }
 805
 806
 807     @Override
 808     public Collection<DeterminationEvent> listDeterminationEvents(SpecimenOrObservationBase<?> specimen, Integer limit, Integer start, List<OrderHint> orderHints, List<String> propertyPaths) {
 809         String queryString = "FROM DeterminationEvent determination "
 810                 + " WHERE determination.identifiedUnit = :specimen";
 811
 812         queryString += orderByClause("determination", orderHints);
 813
 814         Query query = getSession().createQuery(queryString);
 815         query.setParameter("specimen", specimen);
 816
 817         addLimitAndStart(query, limit, start);
 818
 819         @SuppressWarnings("unchecked")
 820         List<DeterminationEvent> results = query.list();
 821         defaultBeanInitializer.initializeAll(results, propertyPaths);
 822         return results;
 823     }
 824
 825     @Override
 826     public Collection<SpecimenTypeDesignation> listTypeDesignations(SpecimenOrObservationBase<?> specimen, Integer limit, Integer start, List<OrderHint> orderHints, List<String> propertyPaths) {
 827         String queryString = "FROM SpecimenTypeDesignation designations "
 828                 + " WHERE designations.typeSpecimen = :specimen";
 829
 830         queryString += orderByClause("designations", orderHints);
 831
 832         Query query = getSession().createQuery(queryString);
 833         query.setParameter("specimen", specimen);
 834
 835         addLimitAndStart(query, limit, start);
 836
 837         @SuppressWarnings("unchecked")
 838         List<SpecimenTypeDesignation> results = query.list();
 839         defaultBeanInitializer.initializeAll(results, propertyPaths);
 840         return results;
 841     }
 842
 843
 844     @Override
 845     public Collection<IndividualsAssociation> listIndividualsAssociations(SpecimenOrObservationBase<?> specimen, Integer limit, Integer start, List<OrderHint> orderHints, List<String> propertyPaths) {
 846         //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
 847         String queryString = "FROM IndividualsAssociation associations WHERE associations.associatedSpecimenOrObservation = :specimen";
 848
 849         queryString += orderByClause("associations", orderHints);
 850
 851         Query query = getSession().createQuery(queryString);
 852         query.setParameter("specimen", specimen);
 853
 854         addLimitAndStart(query, limit, start);
 855
 856         @SuppressWarnings("unchecked")
 857         List<IndividualsAssociation> results = query.list();
 858         defaultBeanInitializer.initializeAll(results, propertyPaths);
 859         return results;
 860     }
 861
 862     @Override
 863     public Collection<DescriptionBase<?>> listDescriptionsWithDescriptionSpecimen(SpecimenOrObservationBase<?> specimen, Integer limit, Integer start, List<OrderHint> orderHints, List<String> propertyPaths) {
 864         //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
 865         String queryString = "FROM DescriptionBase descriptions "
 866                 + " WHERE descriptions.describedSpecimenOrObservation = :specimen";
 867
 868         queryString += orderByClause("descriptions", orderHints);
 869
 870         Query query = getSession().createQuery(queryString);
 871         query.setParameter("specimen", specimen);
 872
 873         addLimitAndStart(query, limit, start);
 874
 875         @SuppressWarnings("unchecked")
 876         List<DescriptionBase<?>> results = query.list();
 877         defaultBeanInitializer.initializeAll(results, propertyPaths);
 878         return results;
 879     }
 880
 881     /**
 882      * {@inheritDoc}
 883      */
 884     @Override
 885     public List<FieldUnit> findFieldUnitsForGatheringEvent(UUID gatheringEventUuid, Integer limit, Integer start, List<OrderHint> orderHints, List<String> propertyPaths) {
 886         String queryString = "FROM SpecimenOrObservationBase sob "
 887                 + "WHERE sob.gatheringEvent.uuid = :gatheringEventUuid";
 888
 889         queryString += orderByClause("sob", orderHints);
 890
 891         Query query = getSession().createQuery(queryString);
 892         query.setParameter("gatheringEventUuid", gatheringEventUuid);
 893
 894         addLimitAndStart(query, limit, start);
 895
 896         @SuppressWarnings("unchecked")
 897         List<FieldUnit> results = query.list();
 898         defaultBeanInitializer.initializeAll(results, propertyPaths);
 899         return results;
 900     }
 901
 902     /**
 903      *
 904      * {@inheritDoc}
 905      */
 906     @Override
 907     public DnaSample findByGeneticAccessionNumber(String accessionNumberString, List<String> propertyPaths) {
 908         String queryString = "SELECT dnaSample FROM DnaSample as dnaSample join dnaSample.sequences as sequences WITH sequences.geneticAccessionNumber LIKE :accessionNumberString";
 909         Query query = getSession().createQuery(queryString);
 910         query.setParameter("accessionNumberString", accessionNumberString);
 911         @SuppressWarnings("unchecked")
 912         List<DnaSample> dnaSamples = query.list();
 913         defaultBeanInitializer.initializeAll(dnaSamples, propertyPaths);
 914
 915
 916         if (dnaSamples.isEmpty()){
 917             logger.debug("there is no dnaSample for genetic accession number " + accessionNumberString + " this should not happen.");
 918             return null;
 919         }else if (dnaSamples.size() == 1){
 920             return dnaSamples.get(0);
 921         } else{
 922             logger.debug("there are more than one dnaSample for genetic accession number " + accessionNumberString + " this should not happen.");
 923             return null;
 924         }
 925
 926
 927     }
 928
 929     /**
 930     *
 931     * {@inheritDoc}
 932     */
 933    @Override
 934    public long countByGeneticAccessionNumber(String accessionNumberString) {
 935        String queryString = "SELECT count(dnaSample) FROM DnaSample dnaSample JOIN dnaSample.sequences sequence WHERE sequence.geneticAccessionNumber LIKE :accessionNumberString";
 936        Query query = getSession().createQuery(queryString);
 937        query.setParameter("accessionNumberString", accessionNumberString);
 938        @SuppressWarnings("unchecked")
 939        List<DerivedUnit> dnaSamples = query.list();
 940        long result = (long)query.uniqueResult();
 941
 942        return result;
 943    }
 944
 945     /**
 946      * @param dnaSamples
 947      * @param results
 948      */
 949     private void extractDeterminedOriginals(List<DerivedUnit> samples, List<DerivedUnit> results) {
 950         for (DerivedUnit sample:samples){
 951             if (sample.getDeterminations() != null && !sample.getDeterminations().isEmpty()){
 952                 results.add(sample);
 953             }else{
 954                 if (sample instanceof DerivedUnit){
 955                     Set<SpecimenOrObservationBase> originals = sample.getDerivedFrom().getOriginals();
 956                     List<DerivedUnit> originalDerivedUnits = new ArrayList();
 957                     for (SpecimenOrObservationBase original:originals){
 958                         if (original instanceof DerivedUnit){
 959                             originalDerivedUnits.add((DerivedUnit)original);
 960                         }
 961                     }
 962                     if(!originalDerivedUnits.isEmpty()){
 963                         extractDeterminedOriginals(originalDerivedUnits, results);
 964                     }
 965                 }
 966             }
 967         }
 968     }
 969
 970     /**
 971      * {@inheritDoc}
 972      */
 973     @Override
 974     public List<SpecimenOrObservationBase> findOriginalsForDerivedUnit(UUID derivedUnitUuid, List<String> propertyPaths) {
 975         String queryString = "SELECT DISTINCT o FROM DerivedUnit du"
 976                 + " JOIN du.derivedFrom.originals o WHERE du.uuid LIKE :derivedUnitUuid";
 977         Query query = getSession().createQuery(queryString);
 978         query.setParameter("derivedUnitUuid", derivedUnitUuid);
 979         @SuppressWarnings("unchecked")
 980         List<SpecimenOrObservationBase> results = query.list();
 981         defaultBeanInitializer.initializeAll(results, propertyPaths);
 982         return results;
 983     }
 984
 985     /**
 986      * {@inheritDoc}
 987      */
 988     @Override
 989     public List<Point> findPointsForFieldUnitList(List<UUID> fieldUnitUuids) {
 990         String queryString = "SELECT DISTINCT fu.gatheringEvent.exactLocation FROM FieldUnit fu"
 991                 + "  WHERE fu.uuid IN (:fieldUnitUuids)";
 992         Query query = getSession().createQuery(queryString);
 993         query.setParameterList("fieldUnitUuids", fieldUnitUuids);
 994         @SuppressWarnings("unchecked")
 995         List<Point> results = query.list();
 996
 997         return results;
 998     }
 999
1000 }