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