cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/persistence/dao/hibernate/taxon/TaxonNodeDaoHibernateImpl.java

   1 /**
   2 * Copyright (C) 2007 EDIT
   3 * European Distributed Institute of Taxonomy
   4 * http://www.e-taxonomy.eu
   5 *
   6 * The contents of this file are subject to the Mozilla Public License Version 1.1
   7 * See LICENSE.TXT at the top of this package for the full license terms.
   8 */
   9
  10 package eu.etaxonomy.cdm.persistence.dao.hibernate.taxon;
  11
  12 import java.math.BigInteger;
  13 import java.util.ArrayList;
  14 import java.util.Collection;
  15 import java.util.Collections;
  16 import java.util.Comparator;
  17 import java.util.HashMap;
  18 import java.util.HashSet;
  19 import java.util.Iterator;
  20 import java.util.List;
  21 import java.util.Map;
  22 import java.util.Set;
  23 import java.util.UUID;
  24
  25 import org.apache.log4j.Logger;
  26 import org.hibernate.Criteria;
  27 import org.hibernate.Hibernate;
  28 import org.hibernate.Query;
  29 import org.hibernate.criterion.Projections;
  30 import org.hibernate.criterion.Restrictions;
  31 import org.springframework.beans.factory.annotation.Autowired;
  32 import org.springframework.beans.factory.annotation.Qualifier;
  33 import org.springframework.stereotype.Repository;
  34
  35 import eu.etaxonomy.cdm.common.CdmUtils;
  36 import eu.etaxonomy.cdm.common.monitor.IProgressMonitor;
  37 import eu.etaxonomy.cdm.hibernate.HibernateProxyHelper;
  38 import eu.etaxonomy.cdm.model.common.CdmBase;
  39 import eu.etaxonomy.cdm.model.common.TreeIndex;
  40 import eu.etaxonomy.cdm.model.name.Rank;
  41 import eu.etaxonomy.cdm.model.name.TaxonName;
  42 import eu.etaxonomy.cdm.model.reference.Reference;
  43 import eu.etaxonomy.cdm.model.taxon.Classification;
  44 import eu.etaxonomy.cdm.model.taxon.Synonym;
  45 import eu.etaxonomy.cdm.model.taxon.Taxon;
  46 import eu.etaxonomy.cdm.model.taxon.TaxonBase;
  47 import eu.etaxonomy.cdm.model.taxon.TaxonNode;
  48 import eu.etaxonomy.cdm.model.taxon.TaxonNodeAgentRelation;
  49 import eu.etaxonomy.cdm.model.taxon.TaxonRelationship;
  50 import eu.etaxonomy.cdm.persistence.dao.common.Restriction;
  51 import eu.etaxonomy.cdm.persistence.dao.hibernate.common.AnnotatableDaoImpl;
  52 import eu.etaxonomy.cdm.persistence.dao.taxon.IClassificationDao;
  53 import eu.etaxonomy.cdm.persistence.dao.taxon.ITaxonDao;
  54 import eu.etaxonomy.cdm.persistence.dao.taxon.ITaxonNodeDao;
  55 import eu.etaxonomy.cdm.persistence.dao.taxon.ITaxonRelationshipDao;
  56 import eu.etaxonomy.cdm.persistence.dto.SortableTaxonNodeQueryResult;
  57 import eu.etaxonomy.cdm.persistence.dto.SortableTaxonNodeQueryResultComparator;
  58 import eu.etaxonomy.cdm.persistence.dto.TaxonNodeDto;
  59 import eu.etaxonomy.cdm.persistence.dto.UuidAndTitleCache;
  60 import eu.etaxonomy.cdm.persistence.query.OrderHint;
  61
  62 /**
  63  * @author a.mueller
  64  * @since 16.06.2009
  65  */
  66 @Repository
  67 @Qualifier("taxonNodeDaoHibernateImpl")
  68 public class TaxonNodeDaoHibernateImpl extends AnnotatableDaoImpl<TaxonNode>
  69                 implements ITaxonNodeDao {
  70
  71         private static final Logger logger = Logger.getLogger(TaxonNodeDaoHibernateImpl.class);
  72
  73         @Autowired
  74         private ITaxonDao taxonDao;
  75         @Autowired
  76         private IClassificationDao classificationDao;
  77     @Autowired
  78     private ITaxonRelationshipDao taxonRelDao;
  79
  80         public TaxonNodeDaoHibernateImpl() {
  81                 super(TaxonNode.class);
  82         }
  83
  84         @Override
  85         public UUID delete(TaxonNode persistentObject, boolean deleteChildren){
  86                 Taxon taxon = persistentObject.getTaxon();
  87                 taxon = HibernateProxyHelper.deproxy(taxon);
  88
  89                 /*Session session = this.getSession();
  90                 Query query = session.createQuery("from TaxonNode t where t.taxon = :taxon");
  91                 query.setParameter("taxon", taxon);
  92                 List result = query.list();*/
  93                 if (taxon != null){
  94                     Hibernate.initialize(taxon);
  95                     Hibernate.initialize(taxon.getTaxonNodes());
  96                         Set<TaxonNode> nodes = taxon.getTaxonNodes();
  97                         //Hibernate.initialize(taxon.getTaxonNodes());
  98                         for (TaxonNode node:nodes) {
  99                             node = HibernateProxyHelper.deproxy(node);
 100
 101                             if (node.equals(persistentObject)){
 102                                 if (node.hasChildNodes()){
 103                                     Iterator<TaxonNode> childNodes = node.getChildNodes().iterator();
 104                                     TaxonNode childNode;
 105                                     List<TaxonNode> listForDeletion = new ArrayList<>();
 106                             while (childNodes.hasNext()){
 107                                 childNode = childNodes.next();
 108                                 listForDeletion.add(childNode);
 109                                 childNodes.remove();
 110
 111                             }
 112                             for (TaxonNode deleteNode:listForDeletion){
 113                                 delete(deleteNode, deleteChildren);
 114                             }
 115                         }
 116
 117                                 taxon.removeTaxonNode(node, deleteChildren);
 118                                 taxonDao.saveOrUpdate(taxon);
 119                                 taxon = HibernateProxyHelper.deproxy(taxonDao.findByUuid(taxon.getUuid()), Taxon.class);
 120                                 taxonDao.delete(taxon);
 121
 122                             }
 123                         }
 124                 }
 125
 126                 UUID result = super.delete(persistentObject);
 127                 return result;
 128         }
 129
 130         @Override
 131         public List<TaxonNode> getTaxonOfAcceptedTaxaByClassification(Classification classification, Integer start, Integer end) {
 132                 int classificationId = classification.getId();
 133                 String limit = "";
 134                 if(start !=null && end != null){
 135                     limit = "LIMIT "+start+"," +end;
 136                 }
 137                 //FIXME write test
 138         String queryString = "SELECT DISTINCT nodes.*,taxa.titleCache "
 139                 + " FROM TaxonNode AS nodes "
 140                 + "    LEFT JOIN TaxonBase AS taxa ON nodes.taxon_id = taxa.id "
 141                 + " WHERE taxa.DTYPE = 'Taxon' "
 142                 + "    AND nodes.classification_id = " + classificationId +
 143                   " ORDER BY taxa.titleCache " + limit;
 144         @SuppressWarnings("unchecked")
 145         List<TaxonNode> result  = getSession().createSQLQuery(queryString).addEntity(TaxonNode.class).list();
 146
 147         return result;
 148         }
 149
 150     @Override
 151     public int countTaxonOfAcceptedTaxaByClassification(Classification classification){
 152         int classificationId = classification.getId();
 153         //FIXME write test
 154         String queryString = ""
 155                 + " SELECT DISTINCT COUNT('nodes.*') "
 156                 + " FROM TaxonNode AS nodes "
 157                 + "   LEFT JOIN TaxonBase AS taxa ON nodes.taxon_id = taxa.id "
 158                 + " WHERE taxa.DTYPE = 'Taxon' AND nodes.classification_id = " + classificationId;
 159          @SuppressWarnings("unchecked")
 160         List<BigInteger> result = getSession().createSQLQuery(queryString).list();
 161          return result.get(0).intValue ();
 162     }
 163
 164     @Override
 165     public List<TaxonNodeDto> listChildNodesAsUuidAndTitleCache(TaxonNodeDto parent) {
 166         String queryString =
 167                   " SELECT tn.uuid, tn.id, t.titleCache "
 168                 + " FROM TaxonNode tn "
 169                 + "    INNER JOIN tn.taxon AS t "
 170                 + " WHERE tn.parent.uuid = :parentId";
 171
 172         Query query =  getSession().createQuery(queryString);
 173         query.setParameter("parentId", parent.getUuid());
 174
 175         @SuppressWarnings("unchecked")
 176         List<Object[]> result = query.list();
 177
 178         List<TaxonNodeDto> list = new ArrayList<>();
 179         for(Object[] object : result){
 180             list.add(new TaxonNodeDto((UUID) object[0],(Integer) object[1], (String) object[2]));
 181         }
 182         return list;
 183     }
 184
 185     @Override
 186     public List<TaxonNodeDto> listChildNodesAsTaxonNodeDto(TaxonNodeDto parent) {
 187         String queryString =
 188                  " SELECT tn "
 189                + " FROM TaxonNode tn "
 190                + "    INNER JOIN tn.taxon AS t "
 191                + " WHERE tn.parent.uuid = :parentId";
 192         Query query =  getSession().createQuery(queryString);
 193         query.setParameter("parentId", parent.getUuid());
 194
 195         @SuppressWarnings("unchecked")
 196         List<TaxonNode> result = query.list();
 197
 198         List<TaxonNodeDto> list = new ArrayList<>();
 199         for(TaxonNode object : result){
 200             list.add(new TaxonNodeDto(object));
 201         }
 202         return list;
 203     }
 204
 205     @Override
 206     public List<TaxonNodeDto> getUuidAndTitleCache(Integer limit, String pattern, UUID classificationUuid, boolean includeDoubtful) {
 207
 208         Query query = createQueryForUuidAndTitleCache(limit, classificationUuid, pattern, includeDoubtful);
 209         @SuppressWarnings("unchecked")
 210         List<SortableTaxonNodeQueryResult> result = query.list();
 211         Collections.sort(result, new SortableTaxonNodeQueryResultComparator());
 212         if(logger.isTraceEnabled()){
 213             logger.trace("number of matches:" + result.size());
 214             result.stream().forEach(o -> logger.trace("uuid: " + o.getTaxonNodeUuid() + " titleCache:" + o.getTaxonTitleCache() + " rank: " + o.getNameRank()));
 215         }
 216         List<TaxonNodeDto> list = new ArrayList<>();
 217 //        int index = limit;
 218         for(SortableTaxonNodeQueryResult stnqr : result){
 219 //            if (index > 0){
 220                 list.add(new TaxonNodeDto(stnqr.getTaxonNodeUuid(),stnqr.getTaxonNodeId(), stnqr.getTaxonTitleCache()));
 221 //                index --;
 222 //            }
 223
 224         }
 225
 226         return list;
 227     }
 228
 229     private Query createQueryForUuidAndTitleCache(Integer limit, UUID classificationUuid, String pattern, boolean includeDoubtful){
 230         String doubtfulPattern = "";
 231         String queryString = "SELECT new " + SortableTaxonNodeQueryResult.class.getName() + "("
 232                 + " node.uuid, node.id, t.titleCache, rank"
 233                 + ") "
 234                 + " FROM TaxonNode AS node "
 235                 + "   JOIN node.taxon as t " // FIXME why not inner join here?
 236                 + "   INNER JOIN t.name AS name "
 237                 + "   LEFT OUTER JOIN name.rank AS rank "
 238                 + " WHERE ";
 239
 240       if (classificationUuid != null){
 241           queryString = queryString + " node.classification.uuid like :classificationUuid " ;
 242       }
 243       if (pattern != null){
 244           if (pattern.equals("?")){
 245               limit = null;
 246           } else{
 247               if (!pattern.endsWith("*")){
 248                   pattern += "%";
 249               }
 250               pattern = pattern.replace("*", "%");
 251               pattern = pattern.replace("?", "%");
 252               if (classificationUuid != null){
 253                   queryString = queryString + " AND ";
 254               }
 255               queryString = queryString + " (t.titleCache LIKE (:pattern) " ;
 256               doubtfulPattern = "?" + pattern;
 257               if (includeDoubtful){
 258                   queryString = queryString + " OR t.titleCache LIKE (:doubtfulPattern))";
 259               }else{
 260                   queryString = queryString + ")";
 261               }
 262           }
 263
 264       }
 265
 266       Query query =  getSession().createQuery(queryString);
 267       if (pattern != null){
 268           query.setParameter("pattern", pattern);
 269       }
 270       if (includeDoubtful){
 271           query.setParameter("doubtfulPattern", doubtfulPattern);
 272       }
 273
 274       if(classificationUuid != null){
 275           query.setParameter("classificationUuid", classificationUuid);
 276       }
 277       if (limit != null){
 278           query.setMaxResults(limit);
 279       }
 280       return query;
 281     }
 282
 283
 284
 285     @Override
 286     public TaxonNodeDto getParentUuidAndTitleCache(TaxonNodeDto child) {
 287         String queryString = ""
 288                 + " SELECT tn.parent.uuid, tn.parent.id, tn.parent.taxon.titleCache, "
 289                 + "                  tn.parent.classification.titleCache "
 290                 + " FROM TaxonNode tn"
 291                 + "    LEFT OUTER JOIN tn.parent.taxon"
 292                 + " WHERE tn.id = :childId";
 293         Query query =  getSession().createQuery(queryString);
 294         query.setParameter("childId", child.getId());
 295         List<TaxonNodeDto> list = new ArrayList<>();
 296
 297         @SuppressWarnings("unchecked")
 298         List<Object[]> result = query.list();
 299
 300         for(Object[] object : result){
 301             UUID uuid = (UUID) object[0];
 302             Integer id = (Integer) object[1];
 303             String taxonTitleCache = (String) object[2];
 304             String classificationTitleCache = (String) object[3];
 305             if(taxonTitleCache!=null){
 306                 list.add(new TaxonNodeDto(uuid,id, taxonTitleCache));
 307             }
 308             else{
 309                 list.add(new TaxonNodeDto(uuid,id, classificationTitleCache));
 310             }
 311         }
 312         if(list.size()==1){
 313             return list.iterator().next();
 314         }
 315         return null;
 316     }
 317     @Override
 318     public List<TaxonNode> listChildrenOf(TaxonNode node, Integer pageSize, Integer pageIndex,
 319             boolean recursive, boolean includeUnpublished, List<String> propertyPaths, Comparator<TaxonNode> comparator){
 320         return listChildrenOfRecursive(node,new ArrayList<>(), pageSize, pageIndex, recursive, includeUnpublished, propertyPaths, comparator);
 321     }
 322
 323     private List<TaxonNode> listChildrenOfRecursive(TaxonNode node, List<TaxonNode> previousResult, Integer pageSize, Integer pageIndex,
 324             boolean recursive, boolean includeUnpublished, List<String> propertyPaths, Comparator<TaxonNode> comparator){
 325
 326         if (recursive == true && comparator == null ){
 327                 Criteria crit = childrenOfCriteria(node, includeUnpublished);
 328
 329                 this.addPageSizeAndNumber(crit, pageSize, pageIndex);
 330                 @SuppressWarnings("unchecked")
 331             List<TaxonNode> results = crit.list();
 332                 results.remove(node);
 333                 defaultBeanInitializer.initializeAll(results, propertyPaths);
 334                 return results;
 335
 336         } else if (recursive == true){
 337             List<TaxonNode> children = node.getChildNodes();
 338             Collections.sort(children, comparator);
 339             for (TaxonNode child: children){
 340                 if (!previousResult.contains(child)){
 341                     previousResult.add(child);
 342                 }
 343                 if (child.hasChildNodes()){
 344                     previousResult = listChildrenOfRecursive(child, previousResult, pageSize, pageIndex,
 345                             recursive, includeUnpublished, propertyPaths, comparator);
 346                 }
 347             }
 348             return previousResult;
 349
 350         } else{
 351                 return classificationDao.listChildrenOf(node.getTaxon(), node.getClassification(), null,
 352                        includeUnpublished, pageSize, pageIndex, propertyPaths);
 353         }
 354     }
 355
 356     @Override
 357         public Long countChildrenOf(TaxonNode node, Classification classification,
 358                         boolean recursive, boolean includeUnpublished) {
 359
 360                 if (recursive == true){
 361                         Criteria crit = childrenOfCriteria(node, includeUnpublished);
 362                 crit.setProjection(Projections.rowCount());
 363                 return ((Integer)crit.uniqueResult().hashCode()).longValue();
 364                 }else{
 365                         return classificationDao.countChildrenOf(
 366                                 node.getTaxon(), classification, null, includeUnpublished);
 367                 }
 368         }
 369
 370     private Criteria childrenOfCriteria(TaxonNode node, boolean includeUnpublished) {
 371         Criteria crit = getSession().createCriteria(TaxonNode.class);
 372         crit.add( Restrictions.like("treeIndex", node.treeIndex()+ "%") );
 373         if (!includeUnpublished){
 374             crit.createCriteria("taxon").add( Restrictions.eq("publish", Boolean.TRUE));
 375         }
 376         return crit;
 377     }
 378
 379     @Override
 380     public List<TaxonNodeAgentRelation> listTaxonNodeAgentRelations(UUID taxonUuid, UUID classificationUuid,
 381             UUID agentUuid, UUID rankUuid, UUID relTypeUuid, Integer start, Integer limit,
 382             List<String> propertyPaths) {
 383
 384         StringBuilder hql = prepareListTaxonNodeAgentRelations(taxonUuid, classificationUuid,
 385                 agentUuid, rankUuid, relTypeUuid, false);
 386
 387         Query query =  getSession().createQuery(hql.toString());
 388
 389         if(limit != null) {
 390             query.setMaxResults(limit);
 391             if(start != null) {
 392                 query.setFirstResult(start);
 393             }
 394         }
 395
 396         setParamsForListTaxonNodeAgentRelations(taxonUuid, classificationUuid, agentUuid, rankUuid, relTypeUuid, query);
 397
 398         @SuppressWarnings("unchecked")
 399         List<TaxonNodeAgentRelation> records = query.list();
 400
 401         if(propertyPaths != null) {
 402             defaultBeanInitializer.initializeAll(records, propertyPaths);
 403         }
 404         return records;
 405     }
 406
 407     @Override
 408     public <S extends TaxonNode> List<S> list(Class<S> type, List<Restriction<?>> restrictions, Integer limit,
 409             Integer start, List<OrderHint> orderHints, List<String> propertyPaths) {
 410         // TODO Auto-generated method stub
 411         return list(type, restrictions, limit, start, orderHints, propertyPaths, INCLUDE_UNPUBLISHED);
 412     }
 413
 414     @Override
 415     public <S extends TaxonNode> List<S> list(Class<S> type, List<Restriction<?>> restrictions, Integer limit,
 416             Integer start, List<OrderHint> orderHints, List<String> propertyPaths, boolean includePublished) {
 417
 418         Criteria criteria = createCriteria(type, restrictions, false);
 419
 420         if(!includePublished){
 421             criteria.add(Restrictions.eq("taxon.publish", true));
 422         }
 423
 424         addLimitAndStart(criteria, limit, start);
 425         addOrder(criteria, orderHints);
 426
 427         @SuppressWarnings("unchecked")
 428         List<S> result = criteria.list();
 429         defaultBeanInitializer.initializeAll(result, propertyPaths);
 430         return result;
 431     }
 432
 433     @Override
 434     public long count(Class<? extends TaxonNode> type, List<Restriction<?>> restrictions) {
 435         return count(type, restrictions, INCLUDE_UNPUBLISHED);
 436     }
 437
 438
 439     @Override
 440     public long count(Class<? extends TaxonNode> type, List<Restriction<?>> restrictions, boolean includePublished) {
 441
 442         Criteria criteria = createCriteria(type, restrictions, false);
 443         if(!includePublished){
 444             criteria.add(Restrictions.eq("taxon.publish", true));
 445         }
 446         criteria.setProjection(Projections.projectionList().add(Projections.rowCount()));
 447         return (Long) criteria.uniqueResult();
 448     }
 449
 450     @Override
 451     public long countTaxonNodeAgentRelations(UUID taxonUuid, UUID classificationUuid, UUID agentUuid, UUID rankUuid, UUID relTypeUuid) {
 452
 453         StringBuilder hql = prepareListTaxonNodeAgentRelations(taxonUuid, classificationUuid, agentUuid, rankUuid, relTypeUuid, true);
 454         Query query =  getSession().createQuery(hql.toString());
 455
 456         setParamsForListTaxonNodeAgentRelations(taxonUuid, classificationUuid, agentUuid, rankUuid, relTypeUuid, query);
 457
 458         Long count = Long.parseLong(query.uniqueResult().toString());
 459
 460         return count;
 461     }
 462
 463     /**
 464      * @param taxonUuid
 465      * @param classificationUuid
 466      * @param agentUuid
 467      * @param relTypeUuid TODO
 468      * @param doCount TODO
 469      * @param rankId
 470      *     limit to taxa having this rank, only applies if <code>taxonUuid = null</code>
 471      * @return
 472      */
 473     private StringBuilder prepareListTaxonNodeAgentRelations(UUID taxonUuid, UUID classificationUuid, UUID agentUuid, UUID rankUuid, UUID relTypeUuid, boolean doCount) {
 474
 475         StringBuilder hql = new StringBuilder();
 476
 477         String join_fetch_mode = doCount ? "JOIN" : "JOIN FETCH";
 478
 479         if(doCount) {
 480             hql.append("SELECT COUNT(tnar)");
 481         } else {
 482             hql.append("SELECT tnar");
 483         }
 484
 485         hql.append(" FROM TaxonNodeAgentRelation AS tnar ");
 486         if(taxonUuid != null) {
 487             // taxonUuid is search filter, do not fetch it
 488             hql.append(" JOIN tnar.taxonNode AS tn "
 489                     + "  JOIN tn.taxon AS t ");
 490         } else {
 491             hql.append(join_fetch_mode)
 492                 .append(" tnar.taxonNode AS tn ")
 493                 .append(join_fetch_mode).append(" tn.taxon AS t ");
 494             if(rankUuid != null) {
 495                 hql.append(" join t.name as n ");
 496             }
 497         }
 498         hql.append(" JOIN tn.classification AS c ");
 499         if(agentUuid != null) {
 500             // agentUuid is search filter, do not fetch it
 501 //            hql.append(" join tnar.agent as a ");
 502             hql.append(join_fetch_mode).append(" tnar.agent AS a ");
 503         } else {
 504             hql.append(join_fetch_mode).append(" tnar.agent AS a ");
 505         }
 506
 507         hql.append(" WHERE (1 = 1) ");
 508
 509         if(relTypeUuid != null) {
 510             hql.append(" AND tnar.type.uuid = :relTypeUuid ");
 511         }
 512
 513         if(taxonUuid != null) {
 514             hql.append(" AND t.uuid = :taxonUuid ");
 515         } else {
 516             if(rankUuid != null) {
 517                 hql.append(" AND n.rank.uuid = :rankUuid ");
 518             }
 519         }
 520         if(classificationUuid != null) {
 521             hql.append(" AND c.uuid = :classificationUuid ");
 522         }
 523         if(agentUuid != null) {
 524             hql.append(" AND a.uuid = :agentUuid ");
 525         }
 526
 527         hql.append(" ORDER BY a.titleCache");
 528         return hql;
 529     }
 530
 531     /**
 532      * @param taxonUuid
 533      * @param classificationUuid
 534      * @param agentUuid
 535      * @param relTypeUuid TODO
 536      * @param query
 537      * @param rankId TODO
 538      */
 539     private void setParamsForListTaxonNodeAgentRelations(UUID taxonUuid, UUID classificationUuid, UUID agentUuid,
 540             UUID rankUuid, UUID relTypeUuid, Query query) {
 541
 542         if(taxonUuid != null) {
 543             query.setParameter("taxonUuid", taxonUuid);
 544         } else {
 545             if(rankUuid != null) {
 546                 query.setParameter("rankUuid", rankUuid);
 547             }
 548         }
 549         if(classificationUuid != null) {
 550             query.setParameter("classificationUuid", classificationUuid);
 551         }
 552         if(agentUuid != null) {
 553             query.setParameter("agentUuid", agentUuid);
 554         }
 555         if(relTypeUuid != null) {
 556             query.setParameter("relTypeUuid", relTypeUuid);
 557         }
 558     }
 559
 560     @Override
 561     public Map<TreeIndex, Integer> rankOrderIndexForTreeIndex(List<TreeIndex> treeIndexes,
 562             Integer minRankOrderIndex,
 563             Integer maxRankOrderIndex) {
 564
 565         Map<TreeIndex, Integer> result = new HashMap<>();
 566         if (treeIndexes == null || treeIndexes.isEmpty()){
 567             return result;
 568         }
 569
 570         String hql = " SELECT tn.treeIndex, r.orderIndex "
 571                 + " FROM TaxonNode tn "
 572                 + "     JOIN tn.taxon t "
 573                 + "     JOIN t.name n "
 574                 + "      JOIN n.rank r "
 575                 + " WHERE tn.treeIndex IN (:treeIndexes) ";
 576         if (minRankOrderIndex != null){
 577             hql += " AND r.orderIndex <= :minOrderIndex";
 578         }
 579         if (maxRankOrderIndex != null){
 580             hql += " AND r.orderIndex >= :maxOrderIndex";
 581         }
 582
 583         Query query =  getSession().createQuery(hql);
 584         query.setParameterList("treeIndexes", TreeIndex.toString(treeIndexes));
 585         if (minRankOrderIndex != null){
 586             query.setParameter("minOrderIndex", minRankOrderIndex);
 587         }
 588         if (maxRankOrderIndex != null){
 589             query.setParameter("maxOrderIndex", maxRankOrderIndex);
 590         }
 591
 592         @SuppressWarnings("unchecked")
 593         List<Object[]> list = query.list();
 594         for (Object[] o : list){
 595             result.put(TreeIndex.NewInstance((String)o[0]), (Integer)o[1]);
 596         }
 597         return result;
 598     }
 599
 600     @Override
 601     public Map<TreeIndex, UuidAndTitleCache<?>> taxonUuidsForTreeIndexes(Collection<TreeIndex> treeIndexes) {
 602         Map<TreeIndex, UuidAndTitleCache<?>> result = new HashMap<>();
 603         if (treeIndexes == null || treeIndexes.isEmpty()){
 604             return result;
 605         }
 606
 607         String hql =
 608                   " SELECT tn.treeIndex, t.uuid, tnb.titleCache "
 609                 + " FROM TaxonNode tn JOIN tn.taxon t Join t.name tnb "
 610                 + " WHERE tn.treeIndex IN (:treeIndexes) ";
 611         Query query =  getSession().createQuery(hql);
 612         query.setParameterList("treeIndexes", TreeIndex.toString(treeIndexes));
 613
 614         @SuppressWarnings("unchecked")
 615         List<Object[]> list = query.list();
 616         for (Object[] o : list){
 617             result.put(TreeIndex.NewInstance((String)o[0]), new UuidAndTitleCache<>((UUID)o[1], null, (String)o[2]));
 618         }
 619         return result;
 620     }
 621
 622     @Override
 623     public List<TaxonNodeDto> getParentTaxonNodeDtoForRank(
 624             Classification classification, Rank rank, TaxonBase<?> taxonBase) {
 625
 626         Taxon taxon = null;
 627         if (taxonBase instanceof Taxon) {
 628             taxon = CdmBase.deproxy(taxonBase, Taxon.class);
 629         }else {
 630             taxon = CdmBase.deproxy(((Synonym)taxonBase).getAcceptedTaxon());
 631         }
 632         TaxonNode node = null;
 633         if (taxon != null) {
 634             node = taxon.getTaxonNode(classification);
 635         }
 636         List<TaxonNodeDto> result = new ArrayList<>();
 637         if (node != null) {
 638             String treeIndex = node.treeIndex();
 639             List<Integer> ancestorNodeIds = TreeIndex.NewInstance(treeIndex).parentNodeIds(false);
 640
 641             Criteria nodeCrit = getSession().createCriteria(TaxonNode.class);
 642             Criteria taxonCrit = nodeCrit.createCriteria("taxon");
 643             Criteria nameCrit = taxonCrit.createCriteria("name");
 644             nodeCrit.add(Restrictions.in("id", ancestorNodeIds));
 645             nodeCrit.add(Restrictions.eq("classification", classification));
 646             nameCrit.add(Restrictions.eq("rank", rank));
 647
 648             @SuppressWarnings("unchecked")
 649             List<TaxonNode> list = nodeCrit.list();
 650             for (TaxonNode rankNode : list){
 651                 TaxonNodeDto dto = new TaxonNodeDto(rankNode);
 652                 result.add(dto);
 653             }
 654         }
 655         return result;
 656     }
 657
 658
 659     @Override
 660     public List<TaxonNodeDto> getParentTaxonNodeDtoForRank(
 661             Classification classification, Rank rank, TaxonName name) {
 662
 663         Set<TaxonBase> taxa = name.getTaxonBases();
 664         List<TaxonNodeDto> result = new ArrayList<>();
 665         for (TaxonBase<?> taxonBase:taxa) {
 666             List<TaxonNodeDto> tmpList = getParentTaxonNodeDtoForRank(classification, rank, taxonBase);
 667             for (TaxonNodeDto tmpDto : tmpList){
 668                 boolean exists = false; //an equal method does not yet exist for TaxonNodeDto therefore this workaround
 669                 for (TaxonNodeDto dto: result){
 670                     if (dto.getTreeIndex().equals(tmpDto.getTreeIndex())){
 671                         exists = true;
 672                     }
 673                 }
 674                 if (!exists){
 675                     result.add(tmpDto);
 676                 }
 677             }
 678         }
 679         return result;
 680     }
 681
 682     @Override
 683     public int countSecundumForSubtreeAcceptedTaxa(TreeIndex subTreeIndex, Reference newSec,
 684             boolean overwriteExisting, boolean includeSharedTaxa, boolean emptySecundumDetail) {
 685         String queryStr = forSubtreeAcceptedQueryStr(includeSharedTaxa, subTreeIndex, false, SelectMode.COUNT);
 686         if (!overwriteExisting){
 687             queryStr += " AND t.secSource.citation IS NULL ";
 688         }
 689         return countResult(queryStr);
 690     }
 691
 692     private int countResult(String queryStr) {
 693         Query query = getSession().createQuery(queryStr);
 694         return ((Long)query.uniqueResult()).intValue();
 695     }
 696
 697     @Override
 698     public int countSecundumForSubtreeSynonyms(TreeIndex subTreeIndex, Reference newSec,
 699             boolean overwriteExisting, boolean includeSharedTaxa, boolean emptySecundumDetail) {
 700         String queryStr = forSubtreeSynonymQueryStr(includeSharedTaxa, subTreeIndex, false, SelectMode.COUNT);
 701         if (!overwriteExisting){
 702             queryStr += " AND syn.secSource.citation IS NULL ";
 703         }
 704         return countResult(queryStr);
 705     }
 706
 707     @Override
 708     public int countSecundumForSubtreeRelations(TreeIndex subTreeIndex, Reference newSec,
 709             boolean overwriteExisting, boolean includeSharedTaxa, boolean emptySecundumDetail) {
 710         String queryStr = forSubtreeRelationQueryStr(includeSharedTaxa, overwriteExisting, subTreeIndex, SelectMode.COUNT);
 711         return countResult(queryStr);
 712     }
 713
 714     //#3465
 715     @Override
 716     public Set<TaxonBase> setSecundumForSubtreeAcceptedTaxa(TreeIndex subTreeIndex, Reference newSec,
 717             boolean overwriteExisting, boolean includeSharedTaxa, boolean emptyDetail, IProgressMonitor monitor) {
 718         //for some reason this does not work, maybe because the listeners are not activated,
 719         //but also the first taxon for some reason does not get updated in terms of secundum, but only by the update listener
 720 //        String where = "SELECT t.id FROM TaxonNode tn JOIN tn.taxon t " +
 721 //                " WHERE tn.treeIndex like '%s%%' ORDER BY t.id";
 722 //        where = String.format(where, subTreeIndex.toString());
 723 //        Query query1 = getSession().createQuery(where);
 724 //        List l = query1.list();
 725 //
 726 //        String hql = "UPDATE Taxon SET sec = :newSec, publish=false WHERE id IN (" + where + ")";
 727 //        Query query = getSession().createQuery(hql);
 728 //        query.setParameter("newSec", newSec);
 729 //        int n = query.executeUpdate();
 730
 731         String queryStr = forSubtreeAcceptedQueryStr(includeSharedTaxa, subTreeIndex, false, SelectMode.ID);
 732         if (!overwriteExisting){
 733             queryStr += " AND t.secSource.citation IS NULL ";
 734         }
 735         return setSecundum(newSec, emptyDetail, queryStr, monitor);
 736
 737     }
 738
 739     @Override
 740     public Set<TaxonBase> setSecundumForSubtreeSynonyms(TreeIndex subTreeIndex, Reference newSec,
 741             boolean overwriteExisting, boolean includeSharedTaxa, boolean emptyDetail, IProgressMonitor monitor) {
 742
 743         String queryStr = forSubtreeSynonymQueryStr(includeSharedTaxa, subTreeIndex, false, SelectMode.ID);
 744         if (!overwriteExisting){
 745             queryStr += " AND syn.secSource.citation IS NULL ";
 746         }
 747         return setSecundum(newSec, emptyDetail, queryStr, monitor);
 748     }
 749
 750     @SuppressWarnings("unchecked")
 751     private <T extends TaxonBase<?>> Set<T> setSecundum(Reference newSec, boolean emptyDetail, String queryStr, IProgressMonitor monitor) {
 752         Set<T> result = new HashSet<>();
 753         Query query = getSession().createQuery(queryStr);
 754         List<List<Integer>> partitionList = splitIdList(query.list(), DEFAULT_PARTITION_SIZE);
 755         for (List<Integer> taxonIdList : partitionList){
 756             List<TaxonBase> taxonList = taxonDao.loadList(taxonIdList, null, null);
 757             for (TaxonBase<?> taxonBase : taxonList){
 758                 if (taxonBase != null){
 759                     taxonBase = CdmBase.deproxy(taxonBase);
 760                     if (newSec == null && taxonBase.getSec() !=null
 761                             || newSec != null && (taxonBase.getSec() == null || !newSec.equals(taxonBase.getSec()) )){
 762                         taxonBase.setSec(newSec);
 763                         result.add((T)taxonBase);
 764                     }
 765                     if (emptyDetail){
 766                         if (taxonBase.getSecMicroReference() != null){
 767                             taxonBase.setSecMicroReference(null);
 768                             result.add((T)taxonBase);
 769                         }
 770                     }
 771
 772                     monitor.worked(1);
 773                     if (monitor.isCanceled()){
 774                         return result;
 775                     }
 776                 }
 777             }
 778         }
 779         return result;
 780     }
 781
 782     @Override
 783     public Set<TaxonRelationship> setSecundumForSubtreeRelations(TreeIndex subTreeIndex, Reference newRef,
 784             Set<UUID> relationTypes,  boolean overwriteExisting, boolean includeSharedTaxa, boolean emptyDetail, IProgressMonitor monitor) {
 785
 786         String queryStr = forSubtreeRelationQueryStr(includeSharedTaxa, overwriteExisting, subTreeIndex, SelectMode.ID);
 787
 788         Set<TaxonRelationship> result = new HashSet<>();
 789         Query query = getSession().createQuery(queryStr);
 790         @SuppressWarnings("unchecked")
 791         List<List<Integer>> partitionList = splitIdList(query.list(), DEFAULT_PARTITION_SIZE);
 792         for (List<Integer> relIdList : partitionList){
 793             List<TaxonRelationship> relList = taxonRelDao.loadList(relIdList, null, null);
 794             for (TaxonRelationship rel : relList){
 795                 if (rel != null){
 796                     rel = CdmBase.deproxy(rel);
 797                     if (newRef == null && rel.getCitation() !=null
 798                             || newRef != null && (rel.getCitation() == null || !newRef.equals(rel.getCitation()) )){
 799                         rel.setCitation(newRef);
 800                         result.add(rel);
 801                     }
 802                     if (emptyDetail){
 803                         if (rel.getCitationMicroReference() != null){
 804                             rel.setCitationMicroReference(null);
 805                             result.add(rel);
 806                         }
 807                     }
 808                     //TODO do we also need to add NamedSource to result?
 809                     monitor.worked(1);
 810                     if (monitor.isCanceled()){
 811                         return result;
 812                     }
 813                 }
 814             }
 815         }
 816
 817         return result;
 818     }
 819
 820     private List<List<Integer>> splitIdList(List<Integer> idList, Integer size){
 821         List<List<Integer>> result = new ArrayList<>();
 822         for (int i = 0; (i*size)<idList.size(); i++) {
 823             int upper = Math.min((i+1)*size, idList.size());
 824             result.add(idList.subList(i*size, upper));
 825         }
 826         return result;
 827     }
 828
 829     @Override
 830     public int countPublishForSubtreeAcceptedTaxa(TreeIndex subTreeIndex, boolean publish, boolean includeSharedTaxa, boolean includeHybrids) {
 831         String queryStr = forSubtreeAcceptedQueryStr(includeSharedTaxa, subTreeIndex, !includeHybrids, SelectMode.COUNT);
 832         queryStr += " AND t.publish != :publish ";
 833         System.out.println(queryStr);
 834         Query query = getSession().createQuery(queryStr);
 835         query.setBoolean("publish", publish);
 836         return ((Long)query.uniqueResult()).intValue();
 837     }
 838
 839     @Override
 840     public int countPublishForSubtreeSynonyms(TreeIndex subTreeIndex, boolean publish, boolean includeSharedTaxa, boolean includeHybrids) {
 841         String queryStr = forSubtreeSynonymQueryStr(includeSharedTaxa, subTreeIndex, !includeHybrids, SelectMode.COUNT);
 842         queryStr += " AND syn.publish != :publish ";
 843         Query query = getSession().createQuery(queryStr);
 844         query.setBoolean("publish", publish);
 845         return ((Long)query.uniqueResult()).intValue();
 846     }
 847
 848     @Override
 849     public Set<TaxonBase> setPublishForSubtreeAcceptedTaxa(TreeIndex subTreeIndex, boolean publish,
 850             boolean includeSharedTaxa, boolean includeHybrids, IProgressMonitor monitor) {
 851         String queryStr = forSubtreeAcceptedQueryStr(includeSharedTaxa, subTreeIndex, !includeHybrids, SelectMode.ID);
 852         queryStr += " AND t.publish != :publish ";
 853         return setPublish(publish, queryStr, null, monitor);
 854     }
 855
 856     @Override
 857     public Set<TaxonBase> setPublishForSubtreeSynonyms(TreeIndex subTreeIndex, boolean publish,
 858             boolean includeSharedTaxa, boolean includeHybrids, IProgressMonitor monitor) {
 859         String queryStr = forSubtreeSynonymQueryStr(includeSharedTaxa, subTreeIndex, !includeHybrids, SelectMode.ID);
 860         queryStr += " AND syn.publish != :publish ";
 861         return setPublish(publish, queryStr, null, monitor);
 862     }
 863
 864     @Override
 865     public int countPublishForSubtreeRelatedTaxa(TreeIndex subTreeIndex, boolean publish, boolean includeSharedTaxa, boolean includeHybrids) {
 866         String queryStr = forSubtreeRelatedTaxaQueryStr(includeSharedTaxa, subTreeIndex, !includeHybrids, SelectMode.COUNT);
 867         queryStr += " AND relTax.publish != :publish ";
 868         Query query = getSession().createQuery(queryStr);
 869         query.setBoolean("publish", publish);
 870         return ((Long)query.uniqueResult()).intValue();
 871     }
 872
 873     @Override
 874     public Set<TaxonBase> setPublishForSubtreeRelatedTaxa(TreeIndex subTreeIndex, boolean publish,
 875             Set<UUID> relationTypes, boolean includeSharedTaxa, boolean includeHybrids,
 876             IProgressMonitor monitor) {
 877         String queryStr = forSubtreeRelatedTaxaQueryStr(includeSharedTaxa, subTreeIndex, !includeHybrids, SelectMode.ID);
 878         queryStr += " AND relTax.publish != :publish ";
 879         queryStr += " AND rel.type.uuid IN (:relTypeUuid)";
 880         return setPublish(publish, queryStr, relationTypes, monitor);
 881     }
 882
 883     private static final int DEFAULT_PARTITION_SIZE = 100;
 884
 885     private <T extends TaxonBase<?>> Set<T> setPublish(boolean publish, String queryStr, Set<UUID> relTypeUuids, IProgressMonitor monitor) {
 886         Set<T> result = new HashSet<>();
 887         Query query = getSession().createQuery(queryStr);
 888         query.setBoolean("publish", publish);
 889         if (relTypeUuids != null && !relTypeUuids.isEmpty()){
 890             query.setParameterList("relTypeUuid", relTypeUuids);
 891         }
 892         @SuppressWarnings("unchecked")
 893         List<List<Integer>> partitionList = splitIdList(query.list(), DEFAULT_PARTITION_SIZE);
 894         for (List<Integer> taxonIdList : partitionList){
 895             List<TaxonBase> taxonList = taxonDao.loadList(taxonIdList, null, null);
 896             for (TaxonBase<?> taxonBase : taxonList){
 897                 if (taxonBase != null){
 898                     if (taxonBase.isPublish() != publish){  //to be on the save side
 899                         taxonBase.setPublish(publish);
 900                         result.add((T)CdmBase.deproxy(taxonBase));
 901                     }
 902                     monitor.worked(1);
 903                     if (monitor.isCanceled()){
 904                         return result;
 905                     }
 906                 }
 907             }
 908         }
 909         return result;
 910     }
 911
 912     private String forSubtreeSynonymQueryStr(boolean includeSharedTaxa, TreeIndex subTreeIndex, boolean excludeHybrids, SelectMode mode) {
 913         String queryStr = "SELECT " + mode.hql("syn")
 914                 + " FROM TaxonNode tn "
 915                 + "   JOIN tn.taxon t "
 916                 + "   JOIN t.synonyms syn  "
 917                 + "   LEFT JOIN syn.name n "
 918                 + "   LEFT JOIN syn.secSource ss ";
 919         String whereStr = " tn.treeIndex LIKE '%1$s%%' ";
 920         if (!includeSharedTaxa){
 921             whereStr += " AND NOT EXISTS ("
 922                     + "FROM TaxonNode tn2 WHERE tn2.taxon = t AND tn2.treeIndex not like '%1$s%%')  ";
 923         }
 924         whereStr = handleExcludeHybrids(whereStr, excludeHybrids, "syn");
 925         queryStr += " WHERE " + String.format(whereStr, subTreeIndex.toString());
 926
 927         return queryStr;
 928     }
 929
 930     private String handleExcludeHybrids(String whereStr, boolean excludeHybrids, String t) {
 931         if(excludeHybrids){
 932
 933             String hybridWhere =  " AND (n is NULL OR "
 934                     + " (n.monomHybrid=0 AND n.binomHybrid=0 "
 935                     + "   AND n.trinomHybrid=0 AND n.hybridFormula=0 )) ";
 936
 937             whereStr += hybridWhere; //String.format(hybridWhere, t);
 938         }
 939         return whereStr;
 940     }
 941
 942     private String forSubtreeRelatedTaxaQueryStr(boolean includeSharedTaxa, TreeIndex subTreeIndex,
 943             boolean excludeHybrids, SelectMode mode) {
 944         String queryStr = "SELECT " + mode.hql("relTax")
 945                 + " FROM TaxonNode tn "
 946                 + "   JOIN tn.taxon t "
 947                 + "   JOIN t.relationsToThisTaxon rel"
 948                 + "   JOIN rel.relatedFrom relTax "
 949                 + "   LEFT JOIN relTax.name n ";
 950         String whereStr =" tn.treeIndex LIKE '%1$s%%' ";
 951         if (!includeSharedTaxa){
 952             //toTaxon should only be used in the given subtree
 953             whereStr += " AND NOT EXISTS ("
 954                     + "FROM TaxonNode tn2 WHERE tn2.taxon = t AND tn2.treeIndex not like '%1$s%%')  ";
 955             //from taxon should not be used in another classification
 956             whereStr += " AND NOT EXISTS ("
 957                     + "FROM TaxonNode tn3 WHERE tn3.taxon = relTax AND tn3.treeIndex not like '%1$s%%')  ";
 958             //fromTaxon should not be related as e.g. pro parte synonym or misapplication to
 959             //another taxon which is not part of the subtree
 960             //TODO and has not the publish state
 961             whereStr += " AND NOT EXISTS ("
 962                     + "FROM TaxonNode tn4 JOIN tn4.taxon t2 JOIN t2.relationsToThisTaxon rel2  "
 963                     + "   WHERE rel2.relatedFrom = relTax AND tn4.treeIndex not like '%1$s%%' "
 964                     + "         AND tn4.taxon.publish != :publish ) ";
 965         }
 966         whereStr = handleExcludeHybrids(whereStr, excludeHybrids, "relTax");
 967         queryStr += " WHERE " + String.format(whereStr, subTreeIndex.toString());
 968
 969         return queryStr;
 970     }
 971
 972     /**
 973      * query for
 974      */
 975     private String forSubtreeRelationQueryStr(boolean includeSharedTaxa, boolean overwriteExisting,
 976             TreeIndex subTreeIndex, SelectMode mode) {
 977
 978         String queryStr = "SELECT " + mode.hql("rel")
 979                 + " FROM TaxonNode tn "
 980                 + "   JOIN tn.taxon t "
 981                 + "   JOIN t.relationsToThisTaxon rel "
 982                 + "   LEFT JOIN rel.source src ";
 983         String whereStr =" tn.treeIndex LIKE '%1$s%%' ";
 984         if (!includeSharedTaxa){
 985             //toTaxon should only be used in the given subtree
 986             whereStr += " AND NOT EXISTS ("
 987                     + "FROM TaxonNode tn2 WHERE tn2.taxon = t AND tn2.treeIndex not like '%1$s%%')  ";
 988         }
 989         queryStr += " WHERE " + String.format(whereStr, subTreeIndex.toString());
 990         if (!overwriteExisting){
 991             queryStr += " AND (rel.source IS NULL OR src.citation IS NULL) ";
 992         }
 993
 994         return queryStr;
 995     }
 996
 997     private enum SelectMode{
 998         COUNT(" count(*) "),
 999         ID ("id "),
1000         UUID("uuid "),
1001         FULL("");
1002         private String hql;
1003         SelectMode(String hql){
1004             this.hql = hql;
1005         }
1006         public String hql(String prefix){
1007             switch (this){
1008             case ID:
1009             case UUID:
1010                 return CdmUtils.Nz(prefix)+"." + hql;
1011             case FULL:
1012                 return CdmUtils.Nz(prefix) + hql;
1013             case COUNT:
1014             default: return hql;
1015             }
1016
1017         }
1018     }
1019
1020     private String forSubtreeAcceptedQueryStr(boolean includeSharedTaxa, TreeIndex subTreeIndex, boolean excludeHybrids, SelectMode mode) {
1021         String queryStr = "SELECT " + mode.hql("t")
1022                 + " FROM TaxonNode tn "
1023                 + "   JOIN tn.taxon t "
1024                 + "   LEFT JOIN t.name n "
1025                 + "   LEFT JOIN t.secSource ss ";
1026         String whereStr = " tn.treeIndex like '%1$s%%' ";
1027         if (!includeSharedTaxa){
1028             whereStr += " AND NOT EXISTS ("
1029                     + "FROM TaxonNode tn2 WHERE tn2.taxon = t AND tn2.treeIndex not like '%1$s%%')  ";
1030         }
1031         whereStr = handleExcludeHybrids(whereStr, excludeHybrids, "t");
1032         queryStr += " WHERE " + String.format(whereStr, subTreeIndex.toString());
1033
1034         return queryStr;
1035     }
1036
1037     @Override
1038     public List<UuidAndTitleCache<TaxonNode>> getTaxonNodeUuidAndTitleCacheOfAcceptedTaxaByClassification(Classification classification, Integer limit, String pattern, boolean searchForClassifications, boolean includeDoubtful) {
1039
1040          Query query = createQueryForUuidAndTitleCache(limit, classification.getUuid(), pattern, includeDoubtful);
1041          @SuppressWarnings("unchecked")
1042          List<SortableTaxonNodeQueryResult> result = query.list();
1043
1044
1045          if (searchForClassifications){
1046              String queryString = "SELECT new " + SortableTaxonNodeQueryResult.class.getName() + "("
1047                      + " node.uuid, node.id, node.classification.titleCache"
1048                      + ") "
1049                      + " FROM TaxonNode AS node "
1050                      + " WHERE node.classification.id = " + classification.getId() +
1051                           " AND node.taxon IS NULL";
1052              if (pattern != null){
1053                  if (pattern.equals("?")){
1054                      limit = null;
1055                  } else{
1056                      if (!pattern.endsWith("*")){
1057                          pattern += "%";
1058                      }
1059                      pattern = pattern.replace("*", "%");
1060                      pattern = pattern.replace("?", "%");
1061                      queryString = queryString + " AND node.classification.titleCache LIKE (:pattern) " ;
1062                  }
1063              }
1064              query = getSession().createQuery(queryString);
1065
1066              if (limit != null){
1067                  query.setMaxResults(limit);
1068              }
1069
1070              if (pattern != null && !pattern.equals("?")){
1071                  query.setParameter("pattern", pattern);
1072              }
1073              @SuppressWarnings("unchecked")
1074              List<SortableTaxonNodeQueryResult> resultClassifications = query.list();
1075
1076              result.addAll(resultClassifications);
1077          }
1078
1079          if(result.size() == 0){
1080              return null;
1081          }else{
1082              List<UuidAndTitleCache<TaxonNode>> list = new ArrayList<>(result.size());
1083              Collections.sort(result, new SortableTaxonNodeQueryResultComparator());
1084              for (SortableTaxonNodeQueryResult resultDTO : result){
1085                  list.add(new UuidAndTitleCache<>(TaxonNode.class, resultDTO.getTaxonNodeUuid(), resultDTO.getTaxonNodeId(), resultDTO.getTaxonTitleCache()));
1086              }
1087
1088              return list;
1089          }
1090     }
1091
1092     @Override
1093     public List<TaxonNodeDto> getTaxonNodeDto(Integer limit, String pattern, UUID classificationUuid) {
1094         String queryString = "SELECT new " + SortableTaxonNodeQueryResult.class.getName() + "("
1095                 + "tn.uuid, tn.id, t.titleCache, rank "
1096                 + ") "
1097                 + " FROM TaxonNode tn "
1098                 + "   INNER JOIN tn.taxon AS t "
1099                 + "   INNER JOIN tn.classification AS cls "
1100                 + "   INNER JOIN t.name AS name "
1101                 + "   LEFT OUTER JOIN name.rank AS rank "
1102                 + " WHERE t.titleCache LIKE :pattern ";
1103         if(classificationUuid != null){
1104             queryString += "AND cls.uuid = :classificationUuid";
1105         }
1106
1107         Query query =  getSession().createQuery(queryString);
1108
1109         query.setParameter("pattern", pattern.toLowerCase()+"%");
1110         if(classificationUuid != null){
1111             query.setParameter("classificationUuid", classificationUuid);
1112         }
1113
1114         @SuppressWarnings("unchecked")
1115         List<SortableTaxonNodeQueryResult> result = query.list();
1116         Collections.sort(result, new SortableTaxonNodeQueryResultComparator());
1117
1118         List<TaxonNodeDto> list = new ArrayList<>();
1119         for(SortableTaxonNodeQueryResult queryDTO : result){
1120             list.add(new TaxonNodeDto(queryDTO.getTaxonNodeUuid(), queryDTO.getTaxonNodeId(), queryDTO.getTaxonTitleCache()));
1121         }
1122         return list;
1123     }
1124
1125     @Override
1126     public List<TaxonNodeDto> getUuidAndTitleCache(Integer limit, String pattern, UUID classificationUuid) {
1127         return getUuidAndTitleCache(limit, pattern, classificationUuid, false);
1128     }
1129
1130     @Override
1131     public List<UuidAndTitleCache<TaxonNode>> getTaxonNodeUuidAndTitleCacheOfAcceptedTaxaByClassification(
1132             Classification classification, Integer limit, String pattern, boolean searchForClassifications) {
1133         return getTaxonNodeUuidAndTitleCacheOfAcceptedTaxaByClassification(classification, limit, pattern, searchForClassifications, false);
1134     }
1135
1136 }