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