2 * Copyright (C) 2008 EDIT
3 * European Distributed Institute of Taxonomy
4 * http://www.e-taxonomy.eu
7 package eu
.etaxonomy
.cdm
.persistence
.dao
.hibernate
.occurrence
;
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
;
15 import java
.util
.UUID
;
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
;
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
;
66 * @author a.babadshanjan
70 public class OccurrenceDaoHibernateImpl
71 extends IdentifiableDaoBase
<SpecimenOrObservationBase
>
72 implements IOccurrenceDao
{
74 private static final Logger logger
= Logger
.getLogger(TaxonDaoHibernateImpl
.class);
77 private IDescriptionDao descriptionDao
;
80 private ITaxonNameDao taxonNameDao
;
83 private IHomotypicalGroupDao homotypicalGroupDao
;
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;
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
);
99 return (Long
)query
.uniqueResult();
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
));
111 if(taxonBase
!= null) {
112 criteria
.add(Restrictions
.eq("taxon",taxonBase
));
115 criteria
.setProjection(Projections
.rowCount());
116 return (Long
)criteria
.uniqueResult();
118 AuditQuery query
= makeAuditQuery(DeterminationEvent
.class,auditEvent
);
120 if(occurrence
!= null) {
121 query
.add(AuditEntity
.relatedId("identifiedUnit").eq(occurrence
.getId()));
124 if(taxonBase
!= null) {
125 query
.add(AuditEntity
.relatedId("taxon").eq(taxonBase
.getId()));
127 query
.addProjection(AuditEntity
.id().count());
129 return (Long
)query
.getSingleResult();
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
);
139 return (Long
)query
.uniqueResult();
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
);
148 addPageSizeAndNumber(query
, pageSize
, pageNumber
);
150 @SuppressWarnings("unchecked")
151 List
<DerivationEvent
> result
= query
.list();
152 defaultBeanInitializer
.initializeAll(result
, propertyPaths
);
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
));
166 if(taxonBase
!= null) {
167 criteria
.add(Restrictions
.eq("taxon",taxonBase
));
170 addPageSizeAndNumber(criteria
, pageSize
, pageNumber
);
172 @SuppressWarnings("unchecked")
173 List
<DeterminationEvent
> result
= criteria
.list();
174 defaultBeanInitializer
.initializeAll(result
, propertyPaths
);
177 AuditQuery query
= getAuditReader().createQuery().forEntitiesAtRevision(DeterminationEvent
.class,auditEvent
.getRevisionNumber());
178 if(occurrence
!= null) {
179 query
.add(AuditEntity
.relatedId("identifiedUnit").eq(occurrence
.getId()));
182 if(taxonBase
!= null) {
183 query
.add(AuditEntity
.relatedId("taxon").eq(taxonBase
.getId()));
185 addPageSizeAndNumber(query
, pageSize
, pageNumber
);
187 @SuppressWarnings("unchecked")
188 List
<DeterminationEvent
> result
= query
.getResultList();
189 defaultBeanInitializer
.initializeAll(result
, propertyPaths
);
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(
200 + " FROM SpecimenOrObservationBase occurence "
201 + " JOIN occurence.media media "
202 + " WHERE occurence = :occurence");
203 query
.setParameter("occurence", occurence
);
205 addPageSizeAndNumber(query
, pageSize
, pageNumber
);
207 @SuppressWarnings("unchecked")
208 List
<Media
> results
= query
.list();
209 defaultBeanInitializer
.initializeAll(results
, propertyPaths
);
214 public void rebuildIndex() {
215 FullTextSession fullTextSession
= Search
.getFullTextSession(getSession());
217 for(SpecimenOrObservationBase
<?
> occurrence
: list(null,null)) { // re-index all taxon base
219 for(DeterminationEvent determination
: occurrence
.getDeterminations()) {
220 Hibernate
.initialize(determination
.getActor());
221 Hibernate
.initialize(determination
.getTaxon());
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());
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());
241 fullTextSession
.index(occurrence
);
243 fullTextSession
.flushToIndexes();
247 public long count(Class
<?
extends SpecimenOrObservationBase
> clazz
, TaxonName determinedAs
) {
249 Criteria criteria
= getCriteria(clazz
);
251 criteria
.createCriteria("determinations").add(Restrictions
.eq("taxonName", determinedAs
));
252 criteria
.setProjection(Projections
.projectionList().add(Projections
.rowCount()));
253 return (Long
)criteria
.uniqueResult();
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
);
261 criteria
.createCriteria("determinations").add(Restrictions
.eq("taxonName", determinedAs
));
263 addPageSizeAndNumber(criteria
, pageSize
, pageNumber
);
264 addOrder(criteria
,orderHints
);
266 @SuppressWarnings({ "unchecked", "rawtypes" })
267 List
<SpecimenOrObservationBase
> results
= criteria
.list();
268 defaultBeanInitializer
.initializeAll(results
, propertyPaths
);
273 public long count(Class
<?
extends SpecimenOrObservationBase
> clazz
, TaxonBase determinedAs
) {
275 Criteria criteria
= getCriteria(clazz
);
277 criteria
.createCriteria("determinations").add(Restrictions
.eq("taxon", determinedAs
));
278 criteria
.setProjection(Projections
.projectionList().add(Projections
.rowCount()));
279 return (Long
)criteria
.uniqueResult();
284 public List
<SpecimenOrObservationBase
> list(Class
<?
extends SpecimenOrObservationBase
> clazz
, TaxonBase determinedAs
,
285 Integer pageSize
, Integer pageNumber
, List
<OrderHint
> orderHints
, List
<String
> propertyPaths
) {
287 Criteria criteria
= getCriteria(clazz
);
289 criteria
.createCriteria("determinations").add(Restrictions
.eq("taxon", determinedAs
));
291 addPageSizeAndNumber(criteria
, pageSize
, pageNumber
);
292 addOrder(criteria
,orderHints
);
294 @SuppressWarnings({ "unchecked", "rawtypes" })
295 List
<SpecimenOrObservationBase
> results
= criteria
.list();
296 defaultBeanInitializer
.initializeAll(results
, propertyPaths
);
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);
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
);
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]));
320 return uuidAndTitleCacheList
;
322 return Collections
.emptyList();
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
) {
331 Criteria criteria
= createFindOccurrenceCriteria(clazz
, queryString
, significantIdentifier
, recordBasis
,
332 associatedTaxon
, associatedTaxonName
, matchmode
, limit
, start
, orderHints
, propertyPaths
);
334 @SuppressWarnings("unchecked")
335 List
<T
> results
= criteria
.list();
336 defaultBeanInitializer
.initializeAll(results
, propertyPaths
);
339 return Collections
.emptyList();
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;
349 criteria
= getSession().createCriteria(type
);
351 criteria
= getSession().createCriteria(clazz
);
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
));
366 criteria
.add(Restrictions
.ilike("titleCache", matchmode
.queryStringFrom(queryString
), org
.hibernate
.criterion
.MatchMode
.ANYWHERE
));
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
)));
376 //recordBasis/SpecimenOrObservationType
377 Set
<SpecimenOrObservationType
> typeAndSubtypes
= new HashSet
<SpecimenOrObservationType
>();
378 if(recordBasis
==null){
380 SpecimenOrObservationType
[] values
= SpecimenOrObservationType
.values();
381 for (SpecimenOrObservationType specimenOrObservationType
: values
) {
382 typeAndSubtypes
.add(specimenOrObservationType
);
386 typeAndSubtypes
= recordBasis
.getGeneralizationOf(true);
387 typeAndSubtypes
.add(recordBasis
);
389 criteria
.add(Restrictions
.in("recordBasis", typeAndSubtypes
));
391 Set
<UUID
> associationUuids
= new HashSet
<UUID
>();
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());
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());
410 if(associatedTaxon
!=null || associatedTaxonName
!=null){
411 if(!associationUuids
.isEmpty()){
412 criteria
.add(Restrictions
.in("uuid", associationUuids
));
420 criteria
.setFirstResult(start
);
422 criteria
.setFirstResult(0);
424 criteria
.setMaxResults(limit
);
427 if(orderHints
!=null){
428 addOrder(criteria
,orderHints
);
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
) {
439 Criteria criteria
= createFindOccurrenceCriteria(clazz
, queryString
, significantIdentifier
, recordBasis
,
440 associatedTaxon
, associatedTaxonName
, matchmode
, limit
, start
, orderHints
, propertyPaths
);
443 criteria
.setProjection(Projections
.rowCount());
444 return (Long
)criteria
.uniqueResult();
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();
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
);
465 query
= session
.createQuery(hql
);
468 query
.setMaxResults(limit
);
471 @SuppressWarnings("unchecked")
472 List
<Object
[]> result
= query
.list();
474 for(Object
[] object
: result
){
475 list
.add(new UuidAndTitleCache
<DerivedUnit
>(DerivedUnit
.class, (UUID
) object
[0], (Integer
)object
[1], (String
) object
[2]));
482 public List
<UuidAndTitleCache
<FieldUnit
>> getFieldUnitUuidAndTitleCache() {
483 List
<UuidAndTitleCache
<FieldUnit
>> list
= new ArrayList
<>();
484 Session session
= getSession();
486 Query query
= session
.createQuery("select uuid, id, titleCache from " + type
.getSimpleName() + " where dtype = " + FieldUnit
.class.getSimpleName());
488 @SuppressWarnings("unchecked")
489 List
<Object
[]> result
= query
.list();
491 for(Object
[] object
: result
){
492 list
.add(new UuidAndTitleCache
<FieldUnit
>(FieldUnit
.class, (UUID
) object
[0], (Integer
)object
[1], (String
) object
[2]));
499 public <T
extends SpecimenOrObservationBase
> List
<T
> listByAssociatedTaxonName(Class
<T
> type
,
500 TaxonName associatedTaxonName
, Integer limit
, Integer start
, List
<OrderHint
> orderHints
, List
<String
> propertyPaths
) {
502 @SuppressWarnings("rawtypes")
503 Set
<SpecimenOrObservationBase
> setOfAll
= new HashSet
<>();
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
);
510 if(setOfAll
.size() == 0){
511 // no need querying the data base
512 return new ArrayList
<T
>();
517 " FROM SpecimenOrObservationBase sob" +
518 " WHERE sob in (:setOfAll)";
520 if(type
!= null && !type
.equals(SpecimenOrObservationBase
.class)){
521 queryString
+= " AND sob.class = :type";
523 queryString
+= orderByClause("sob", orderHints
);
525 Query query
= getSession().createQuery(queryString
);
526 query
.setParameterList("setOfAll", setOfAll
);
528 if(type
!= null && !type
.equals(SpecimenOrObservationBase
.class)){
529 query
.setParameter("type", type
.getSimpleName());
532 addLimitAndStart(query
, limit
, start
);
534 @SuppressWarnings("unchecked")
535 List
<T
> results
= query
.list();
536 defaultBeanInitializer
.initializeAll(results
, propertyPaths
);
540 private List
<SpecimenNodeWrapper
> querySpecimen(Query query
, List
<UUID
> taxonNodeUuids
,
541 Integer limit
, Integer start
){
542 query
.setParameterList("taxonNodeUuids", taxonNodeUuids
);
544 addLimitAndStart(query
, limit
, start
);
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
<>(
555 (SpecimenOrObservationType
)object
[3],
556 new TaxonNodeDto((TaxonNode
)object
[4]));
557 if(object
.length
>5) {
558 wrapper
.setTaxonDescriptionUuid((UUID
)object
[5]);
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, "
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) "
581 Query query
= getSession().createQuery(queryString
);
582 return querySpecimen(query
, taxonNodeUuids
, limit
, start
);
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, "
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) "
599 Query query
= getSession().createQuery(queryString
);
600 return querySpecimen(query
, taxonNodeUuids
, limit
, start
);
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, "
611 + "FROM DeterminationEvent AS det "
612 + "LEFT JOIN det.taxon AS t "
613 + "JOIN t.taxonNodes AS tn "
614 + "WHERE tn.uuid in (:taxonNodeUuids) "
616 Query query
= getSession().createQuery(queryString
);
617 return querySpecimen(query
, taxonNodeUuids
, limit
, start
);
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, "
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) "
634 Query query
= getSession().createQuery(queryString
);
635 return querySpecimen(query
, taxonNodeUuids
, limit
, start
);
639 public Collection
<SpecimenNodeWrapper
> listUuidAndTitleCacheByAssociatedTaxon(List
<UUID
> taxonNodeUuids
,
640 Integer limit
, Integer start
){
642 Set
<SpecimenNodeWrapper
> testSet
= new HashSet();
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
));
649 Collection
<SpecimenNodeWrapper
> wrappers
= new HashSet
<>();
650 wrappers
.addAll(testSet
);
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
);
659 return Collections
.emptyList();
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]));
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
);
675 return Collections
.emptyList();
677 @SuppressWarnings("unchecked")
678 List
<T
> results
= query
.list();
679 defaultBeanInitializer
.initializeAll(results
, propertyPaths
);
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
<>();
688 Criteria criteria
= null;
690 criteria
= getSession().createCriteria(type
, "specimen");
692 criteria
= getSession().createCriteria(clazz
, "specimen");
695 Disjunction determinationOr
= Restrictions
.disjunction();
697 // A Taxon may be referenced by the DeterminationEvent of the SpecimenOrObservationBase
698 Criteria determinationsCriteria
= criteria
.createCriteria("determinations");
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
));
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
));
712 determinationsCriteria
.add(determinationOr
);
716 criteria
.setFirstResult(start
);
718 criteria
.setFirstResult(0);
720 criteria
.setMaxResults(limit
);
722 criteria
.setProjection(Projections
.property("id"));
724 addOrder(criteria
,orderHints
);
726 @SuppressWarnings("unchecked")
727 List
<Integer
> detResults
= criteria
.list();
728 setOfAllIds
.addAll(detResults
);
730 // The IndividualsAssociation elements in a TaxonDescription contain DerivedUnits
731 setOfAllIds
.addAll(descriptionDao
.getIndividualAssociationSpecimenIDs(
732 associatedTaxon
.getUuid(), null, null, 0, null));
735 // SpecimenTypeDesignations may be associated with the TaxonName.
736 setOfAllIds
.addAll(taxonNameDao
.getTypeSpecimenIdsForTaxonName(
737 associatedTaxon
.getName(), null, null, null));
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());
750 if(setOfAllIds
.size() == 0){
751 // no need querying the data base
757 " from SpecimenOrObservationBase sob" +
758 " where sob.id in (:setOfAllIds)";
760 if(clazz
!= null && !clazz
.equals(SpecimenOrObservationBase
.class)){
761 queryString
+= " and sob.class = :type ";
764 if(orderHints
!= null && orderHints
.size() > 0){
765 queryString
+= " order by ";
766 String orderStr
= "";
767 for(OrderHint orderHint
: orderHints
){
768 if(orderStr
.length() > 0){
771 queryString
+= "sob." + orderHint
.getPropertyName() + " " + orderHint
.getSortOrder().toHql();
773 queryString
+= orderStr
;
776 Query query
= getSession().createQuery(queryString
);
777 query
.setParameterList("setOfAllIds", setOfAllIds
);
779 if(clazz
!= null && !clazz
.equals(SpecimenOrObservationBase
.class)){
780 query
.setParameter("type", clazz
.getSimpleName());
783 addLimitAndStart(query
, limit
, start
);
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 ";
793 queryString
+= orderByClause("specimens", orderHints
);
795 Query query
= getSession().createQuery(queryString
);
796 query
.setParameter("type", type
);
798 addLimitAndStart(query
, limit
, start
);
800 @SuppressWarnings({ "unchecked", "rawtypes" })
801 List
<SpecimenOrObservationBase
> results
= query
.list();
802 defaultBeanInitializer
.initializeAll(results
, propertyPaths
);
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";
812 queryString
+= orderByClause("determination", orderHints
);
814 Query query
= getSession().createQuery(queryString
);
815 query
.setParameter("specimen", specimen
);
817 addLimitAndStart(query
, limit
, start
);
819 @SuppressWarnings("unchecked")
820 List
<DeterminationEvent
> results
= query
.list();
821 defaultBeanInitializer
.initializeAll(results
, propertyPaths
);
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";
830 queryString
+= orderByClause("designations", orderHints
);
832 Query query
= getSession().createQuery(queryString
);
833 query
.setParameter("specimen", specimen
);
835 addLimitAndStart(query
, limit
, start
);
837 @SuppressWarnings("unchecked")
838 List
<SpecimenTypeDesignation
> results
= query
.list();
839 defaultBeanInitializer
.initializeAll(results
, propertyPaths
);
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";
849 queryString
+= orderByClause("associations", orderHints
);
851 Query query
= getSession().createQuery(queryString
);
852 query
.setParameter("specimen", specimen
);
854 addLimitAndStart(query
, limit
, start
);
856 @SuppressWarnings("unchecked")
857 List
<IndividualsAssociation
> results
= query
.list();
858 defaultBeanInitializer
.initializeAll(results
, propertyPaths
);
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";
868 queryString
+= orderByClause("descriptions", orderHints
);
870 Query query
= getSession().createQuery(queryString
);
871 query
.setParameter("specimen", specimen
);
873 addLimitAndStart(query
, limit
, start
);
875 @SuppressWarnings("unchecked")
876 List
<DescriptionBase
<?
>> results
= query
.list();
877 defaultBeanInitializer
.initializeAll(results
, propertyPaths
);
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";
889 queryString
+= orderByClause("sob", orderHints
);
891 Query query
= getSession().createQuery(queryString
);
892 query
.setParameter("gatheringEventUuid", gatheringEventUuid
);
894 addLimitAndStart(query
, limit
, start
);
896 @SuppressWarnings("unchecked")
897 List
<FieldUnit
> results
= query
.list();
898 defaultBeanInitializer
.initializeAll(results
, propertyPaths
);
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
);
916 if (dnaSamples
.isEmpty()){
917 logger
.debug("there is no dnaSample for genetic accession number " + accessionNumberString
+ " this should not happen.");
919 }else if (dnaSamples
.size() == 1){
920 return dnaSamples
.get(0);
922 logger
.debug("there are more than one dnaSample for genetic accession number " + accessionNumberString
+ " this should not happen.");
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();
949 private void extractDeterminedOriginals(List
<DerivedUnit
> samples
, List
<DerivedUnit
> results
) {
950 for (DerivedUnit sample
:samples
){
951 if (sample
.getDeterminations() != null && !sample
.getDeterminations().isEmpty()){
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
);
962 if(!originalDerivedUnits
.isEmpty()){
963 extractDeterminedOriginals(originalDerivedUnits
, results
);
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
);
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();