cdmlib-persistence/src/main/java/eu/etaxonomy/cdm/persistence/dao/hibernate/taxon/TaxonDaoHibernateImpl.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 package eu.etaxonomy.cdm.persistence.dao.hibernate.taxon;
  10
  11 import java.util.ArrayList;
  12 import java.util.Collection;
  13 import java.util.Collections;
  14 import java.util.Comparator;
  15 import java.util.HashSet;
  16 import java.util.Iterator;
  17 import java.util.List;
  18 import java.util.Set;
  19 import java.util.SortedSet;
  20 import java.util.TreeSet;
  21 import java.util.UUID;
  22
  23 import org.apache.log4j.Logger;
  24 import org.hibernate.Criteria;
  25 import org.hibernate.FetchMode;
  26 import org.hibernate.Hibernate;
  27 import org.hibernate.Query;
  28 import org.hibernate.Session;
  29 import org.hibernate.criterion.Criterion;
  30 import org.hibernate.criterion.Order;
  31 import org.hibernate.criterion.Projections;
  32 import org.hibernate.criterion.Restrictions;
  33 import org.hibernate.envers.query.AuditEntity;
  34 import org.hibernate.envers.query.AuditQuery;
  35 import org.hibernate.search.FullTextSession;
  36 import org.hibernate.search.Search;
  37 import org.springframework.beans.factory.annotation.Autowired;
  38 import org.springframework.beans.factory.annotation.Qualifier;
  39 import org.springframework.dao.DataAccessException;
  40 import org.springframework.stereotype.Repository;
  41
  42 import eu.etaxonomy.cdm.model.common.CdmBase;
  43 import eu.etaxonomy.cdm.model.common.DefinedTerm;
  44 import eu.etaxonomy.cdm.model.common.IdentifiableEntity;
  45 import eu.etaxonomy.cdm.model.common.LSID;
  46 import eu.etaxonomy.cdm.model.common.OriginalSourceBase;
  47 import eu.etaxonomy.cdm.model.common.RelationshipBase;
  48 import eu.etaxonomy.cdm.model.common.RelationshipBase.Direction;
  49 import eu.etaxonomy.cdm.model.location.NamedArea;
  50 import eu.etaxonomy.cdm.model.name.NonViralName;
  51 import eu.etaxonomy.cdm.model.name.Rank;
  52 import eu.etaxonomy.cdm.model.name.TaxonNameBase;
  53 import eu.etaxonomy.cdm.model.name.TaxonNameComparator;
  54 import eu.etaxonomy.cdm.model.reference.Reference;
  55 import eu.etaxonomy.cdm.model.taxon.Classification;
  56 import eu.etaxonomy.cdm.model.taxon.Synonym;
  57 import eu.etaxonomy.cdm.model.taxon.SynonymRelationship;
  58 import eu.etaxonomy.cdm.model.taxon.SynonymRelationshipType;
  59 import eu.etaxonomy.cdm.model.taxon.Taxon;
  60 import eu.etaxonomy.cdm.model.taxon.TaxonBase;
  61 import eu.etaxonomy.cdm.model.taxon.TaxonNode;
  62 import eu.etaxonomy.cdm.model.taxon.TaxonRelationship;
  63 import eu.etaxonomy.cdm.model.taxon.TaxonRelationshipType;
  64 import eu.etaxonomy.cdm.model.view.AuditEvent;
  65 import eu.etaxonomy.cdm.persistence.dao.hibernate.common.IdentifiableDaoBase;
  66 import eu.etaxonomy.cdm.persistence.dao.name.ITaxonNameDao;
  67 import eu.etaxonomy.cdm.persistence.dao.taxon.ITaxonDao;
  68 import eu.etaxonomy.cdm.persistence.dto.UuidAndTitleCache;
  69 import eu.etaxonomy.cdm.persistence.fetch.CdmFetch;
  70 import eu.etaxonomy.cdm.persistence.query.MatchMode;
  71 import eu.etaxonomy.cdm.persistence.query.OrderHint;
  72
  73
  74 /**
  75  * @author a.mueller
  76  * @created 24.11.2008
  77  */
  78 @Repository
  79 @Qualifier("taxonDaoHibernateImpl")
  80 public class TaxonDaoHibernateImpl extends IdentifiableDaoBase<TaxonBase> implements ITaxonDao {
  81 //    private AlternativeSpellingSuggestionParser<TaxonBase> alternativeSpellingSuggestionParser;
  82     private static final Logger logger = Logger.getLogger(TaxonDaoHibernateImpl.class);
  83
  84     public TaxonDaoHibernateImpl() {
  85         super(TaxonBase.class);
  86         indexedClasses = new Class[2];
  87         indexedClasses[0] = Taxon.class;
  88         indexedClasses[1] = Synonym.class;
  89         super.defaultField = "name.titleCache_tokenized";
  90     }
  91
  92     @Autowired
  93     private ITaxonNameDao taxonNameDao;
  94
  95 ////    spelling support currently disabled in appcontext, see spelling.xml ... "
  96 ////    @Autowired(required = false)   //TODO switched of because it caused problems when starting CdmApplicationController
  97 //    public void setAlternativeSpellingSuggestionParser(AlternativeSpellingSuggestionParser<TaxonBase> alternativeSpellingSuggestionParser) {
  98 //        this.alternativeSpellingSuggestionParser = alternativeSpellingSuggestionParser;
  99 //    }
 100
 101     @Override
 102     public List<Taxon> getRootTaxa(Reference sec) {
 103         return getRootTaxa(sec, CdmFetch.FETCH_CHILDTAXA(), true, false);
 104     }
 105
 106     @Override
 107     public List<Taxon> getRootTaxa(Rank rank, Reference sec, CdmFetch cdmFetch, Boolean onlyWithChildren, Boolean withMisapplications, List<String> propertyPaths) {
 108         checkNotInPriorView("TaxonDaoHibernateImpl.getRootTaxa(Rank rank, Reference sec, CdmFetch cdmFetch, Boolean onlyWithChildren, Boolean withMisapplications)");
 109         if (onlyWithChildren == null){
 110             onlyWithChildren = true;
 111         }
 112         if (withMisapplications == null){
 113             withMisapplications = true;
 114         }
 115         if (cdmFetch == null){
 116             cdmFetch = CdmFetch.NO_FETCH();
 117         }
 118
 119         Criteria crit = getSession().createCriteria(Taxon.class);
 120
 121         crit.setFetchMode("name", FetchMode.JOIN);
 122         crit.createAlias("name", "name");
 123
 124         if (rank != null) {
 125             crit.add(Restrictions.eq("name.rank", rank));
 126         }else{
 127             crit.add(Restrictions.isNull("taxonomicParentCache"));
 128         }
 129
 130         if (sec != null){
 131             crit.add(Restrictions.eq("sec", sec) );
 132         }
 133
 134         if (! cdmFetch.includes(CdmFetch.FETCH_CHILDTAXA())){
 135             logger.info("Not fetching child taxa");
 136             //TODO overwrite LAZY (SELECT) does not work (bug in hibernate?)
 137             crit.setFetchMode("relationsToThisTaxon.fromTaxon", FetchMode.LAZY);
 138         }
 139
 140         List<Taxon> results = new ArrayList<Taxon>();
 141         @SuppressWarnings("unchecked")
 142         List<Taxon> taxa = crit.list();
 143         for(Taxon taxon : taxa){
 144
 145
 146             //childTaxa
 147             //TODO create restriction instead
 148             // (a) not using cache fields
 149             /*Hibernate.initialize(taxon.getRelationsFromThisTaxon());
 150             if (onlyWithChildren == false || taxon.getRelationsFromThisTaxon().size() > 0){
 151                 if (withMisapplications == true || ! taxon.isMisappliedName()){
 152                     defaultBeanInitializer.initialize(taxon, propertyPaths);
 153                     results.add(taxon);
 154                 }
 155             }*/
 156             // (b) using cache fields
 157             if (onlyWithChildren == false || taxon.hasTaxonomicChildren()){
 158                 if (withMisapplications == true || ! taxon.isMisapplication()){
 159                     defaultBeanInitializer.initialize(taxon, propertyPaths);
 160                     results.add(taxon);
 161                 }
 162             }
 163         }
 164         return results;
 165     }
 166
 167     @Override
 168     public List<Taxon> getRootTaxa(Reference sec, CdmFetch cdmFetch, Boolean onlyWithChildren, Boolean withMisapplications) {
 169         return getRootTaxa(null, sec, cdmFetch, onlyWithChildren, withMisapplications, null);
 170     }
 171
 172     @Override
 173     public List<TaxonBase> getTaxaByName(String queryString, Reference sec) {
 174
 175         return getTaxaByName(queryString, true, sec);
 176     }
 177
 178     @Override
 179     public List<TaxonBase> getTaxaByName(String queryString, Boolean accepted, Reference sec) {
 180         checkNotInPriorView("TaxonDaoHibernateImpl.getTaxaByName(String name, Reference sec)");
 181
 182         Criteria criteria = null;
 183         if (accepted == true) {
 184             criteria = getSession().createCriteria(Taxon.class);
 185         } else {
 186             criteria = getSession().createCriteria(Synonym.class);
 187         }
 188
 189         criteria.setFetchMode( "name", FetchMode.JOIN );
 190         criteria.createAlias("name", "name");
 191
 192         if (sec != null && sec.getId() != 0) {
 193             criteria.add(Restrictions.eq("sec", sec ) );
 194         }
 195
 196         if (queryString != null) {
 197             criteria.add(Restrictions.ilike("name.nameCache", queryString));
 198         }
 199
 200         return criteria.list();
 201     }
 202
 203     public List<TaxonBase> getTaxaByName(boolean doTaxa, boolean doSynonyms, String queryString, MatchMode matchMode,
 204             Integer pageSize, Integer pageNumber) {
 205
 206         return getTaxaByName(doTaxa, doSynonyms, false, queryString, null, matchMode, null, pageSize, pageNumber, null);
 207     }
 208
 209     @Override
 210     public List<TaxonBase> getTaxaByName(String queryString, MatchMode matchMode,
 211             Boolean accepted, Integer pageSize, Integer pageNumber) {
 212
 213         boolean doTaxa = true;
 214         boolean doSynonyms = true;
 215
 216         if (accepted == true) {
 217             doSynonyms = false;
 218         } else {
 219            doTaxa = false;
 220         }
 221         return getTaxaByName(doTaxa, doSynonyms, queryString, matchMode, pageSize, pageNumber);
 222     }
 223
 224     @Override
 225     public List<TaxonBase> getTaxaByName(boolean doTaxa, boolean doSynonyms, boolean doMisappliedNames,String queryString, Classification classification,
 226             MatchMode matchMode, Set<NamedArea> namedAreas, Integer pageSize,
 227             Integer pageNumber, List<String> propertyPaths) {
 228
 229         boolean doCount = false;
 230
 231         Query query = prepareTaxaByName(doTaxa, doSynonyms, doMisappliedNames, "nameCache", queryString, classification, matchMode, namedAreas, pageSize, pageNumber, doCount);
 232
 233         if (query != null){
 234             @SuppressWarnings("unchecked")
 235             List<TaxonBase> results = query.list();
 236
 237             defaultBeanInitializer.initializeAll(results, propertyPaths);
 238             //TaxonComparatorSearch comp = new TaxonComparatorSearch();
 239             //Collections.sort(results, comp);
 240             return results;
 241         }
 242
 243         return new ArrayList<TaxonBase>();
 244
 245     }
 246
 247
 248     //new search for the editor, for performance issues the return values are only uuid and titleCache, to avoid the initialisation of all objects
 249     @Override
 250     @SuppressWarnings("unchecked")
 251     public List<UuidAndTitleCache<IdentifiableEntity>> getTaxaByNameForEditor(boolean doTaxa, boolean doSynonyms, boolean doNamesWithoutTaxa, boolean doMisappliedNames, String queryString, Classification classification,
 252             MatchMode matchMode, Set<NamedArea> namedAreas) {
 253 //        long zstVorher;
 254 //        long zstNachher;
 255
 256         boolean doCount = false;
 257         List<UuidAndTitleCache<IdentifiableEntity>> resultObjects = new ArrayList<UuidAndTitleCache<IdentifiableEntity>>();
 258         if (doNamesWithoutTaxa){
 259                 List<? extends TaxonNameBase<?,?>> nameResult = taxonNameDao.findByName(queryString,matchMode, null, null, null, null);
 260
 261                 for (TaxonNameBase name: nameResult){
 262                         if (name.getTaxonBases().size() == 0){
 263                                 resultObjects.add(new UuidAndTitleCache(TaxonNameBase.class, name.getUuid(), name.getId(), name.getTitleCache()));
 264                         }
 265                 }
 266                 if (!doSynonyms && !doTaxa){
 267                         return resultObjects;
 268                 }
 269         }
 270         Query query = prepareTaxaByNameForEditor(doTaxa, doSynonyms, doMisappliedNames, "nameCache", queryString, classification, matchMode, namedAreas, doCount);
 271
 272
 273         if (query != null){
 274             List<Object[]> results = query.list();
 275
 276             Object[] result;
 277             for(int i = 0; i<results.size();i++){
 278                 result = results.get(i);
 279
 280                 //differentiate taxa and synonyms
 281                 // new Boolean(result[3].toString()) is due to the fact that result[3] could be a Boolean ora String
 282                 // see FIXME in 'prepareQuery' for more details
 283                 if (doTaxa && doSynonyms){
 284                     if (result[3].equals("synonym")) {
 285                         resultObjects.add( new UuidAndTitleCache(Synonym.class, (UUID) result[0], (Integer) result[1], (String)result[2], new Boolean(result[4].toString())));
 286                     }
 287                     else {
 288                         resultObjects.add( new UuidAndTitleCache(Taxon.class, (UUID) result[0], (Integer) result[1], (String)result[2], new Boolean(result[4].toString())));
 289                     }
 290                 }else if (doTaxa){
 291                         resultObjects.add( new UuidAndTitleCache(Taxon.class, (UUID) result[0], (Integer) result[1], (String)result[2], new Boolean(result[4].toString())));
 292                 }else if (doSynonyms){
 293                     resultObjects.add( new UuidAndTitleCache(Synonym.class, (UUID) result[0], (Integer) result[1], (String)result[2], new Boolean(result[4].toString())));
 294                 }
 295             }
 296
 297
 298
 299         }
 300
 301         return resultObjects;
 302
 303     }
 304
 305     @Override
 306     public List<Taxon> getTaxaByCommonName(String queryString, Classification classification,
 307                MatchMode matchMode, Set<NamedArea> namedAreas, Integer pageSize,
 308                Integer pageNumber, List<String> propertyPaths) {
 309         boolean doCount = false;
 310         Query query = prepareTaxaByCommonName(queryString, classification, matchMode, namedAreas, pageSize, pageNumber, doCount, false);
 311         if (query != null){
 312             List<Taxon> results = query.list();
 313             defaultBeanInitializer.initializeAll(results, propertyPaths);
 314             return results;
 315         }
 316         return new ArrayList<Taxon>();
 317
 318     }
 319
 320     /**
 321      * @param clazz
 322      * @param searchField the field in TaxonNameBase to be searched through usually either <code>nameCache</code> or <code>titleCache</code>
 323      * @param queryString
 324      * @param classification TODO
 325      * @param matchMode
 326      * @param namedAreas
 327      * @param pageSize
 328      * @param pageNumber
 329      * @param doCount
 330      * @return
 331      *
 332      *
 333      */
 334     private Query prepareTaxaByNameForEditor(boolean doTaxa, boolean doSynonyms, boolean doMisappliedNames, String searchField, String queryString, Classification classification,
 335             MatchMode matchMode, Set<NamedArea> namedAreas, boolean doCount) {
 336         return prepareQuery(doTaxa, doSynonyms, doMisappliedNames, searchField, queryString,
 337                 classification, matchMode, namedAreas, doCount, true);
 338     }
 339
 340     /**
 341      * @param searchField
 342      * @param queryString
 343      * @param classification
 344      * @param matchMode
 345      * @param namedAreas
 346      * @param doCount
 347      * @param doNotReturnFullEntities
 348      *            if set true the seach method will not return synonym and taxon
 349      *            entities but an array containing the uuid, titleCache, and the
 350      *            DTYPE in lowercase letters.
 351      * @param clazz
 352      * @return
 353      */
 354     private Query prepareQuery(boolean doTaxa, boolean doSynonyms, boolean doIncludeMisappliedNames, String searchField, String queryString,
 355                 Classification classification, MatchMode matchMode, Set<NamedArea> namedAreas, boolean doCount, boolean doNotReturnFullEntities){
 356
 357             String hqlQueryString = matchMode.queryStringFrom(queryString);
 358             String selectWhat;
 359             if (doNotReturnFullEntities){
 360                 selectWhat = "t.uuid, t.id, t.titleCache ";
 361             }else {
 362                 selectWhat = (doCount ? "count(t)": "t");
 363             }
 364
 365             String hql = "";
 366             Set<NamedArea> areasExpanded = new HashSet<NamedArea>();
 367             if(namedAreas != null && namedAreas.size() > 0){
 368                 // expand areas and restrict by distribution area
 369                 Query areaQuery = getSession().createQuery("select childArea from NamedArea as childArea left join childArea.partOf as parentArea where parentArea = :area");
 370                 expandNamedAreas(namedAreas, areasExpanded, areaQuery);
 371             }
 372             boolean doAreaRestriction = areasExpanded.size() > 0;
 373
 374             Set<UUID> namedAreasUuids = new HashSet<UUID>();
 375             for (NamedArea area:areasExpanded){
 376                 namedAreasUuids.add(area.getUuid());
 377             }
 378
 379
 380             String [] subSelects = createHQLString(doTaxa, doSynonyms, doIncludeMisappliedNames, classification, areasExpanded, matchMode, searchField);
 381             String taxonSubselect = subSelects[1];
 382             String synonymSubselect = subSelects[2];
 383             String misappliedSelect = subSelects[0];
 384
 385
 386             /*if(classification != null ){
 387                 if (!doIncludeMisappliedNames){
 388                     if(doAreaRestriction){
 389
 390                         taxonSubselect = "select t.id from" +
 391                             " Distribution e" +
 392                             " join e.inDescription d" +
 393                             " join d.taxon t" +
 394                             " join t.name n " +
 395                             " join t.taxonNodes as tn "+
 396                             " where" +
 397                             " e.area.uuid in (:namedAreasUuids) AND" +
 398                             " tn.classification = :classification" +
 399                             " AND n." + searchField +  " " + matchMode.getMatchOperator() + " :queryString";
 400
 401
 402
 403                         synonymSubselect = "select s.id from" +
 404                             " Distribution e" +
 405                             " join e.inDescription d" +
 406                             " join d.taxon t" + // the taxa
 407                             " join t.taxonNodes as tn "+
 408                             " join t.synonymRelations sr" +
 409                             " join sr.relatedFrom s" + // the synonyms
 410                             " join s.name sn"+
 411                             " where" +
 412                             " e.area.uuid in (:namedAreasUuids) AND" +
 413                             " tn.classification = :classification" +
 414                             " AND sn." + searchField +  " " + matchMode.getMatchOperator() + " :queryString";
 415
 416                     } else {
 417
 418                         taxonSubselect = "select t.id from" +
 419                             " Taxon t" +
 420                             " join t.name n " +
 421                             " join t.taxonNodes as tn "+
 422                             " where" +
 423                             " tn.classification = :classification" +
 424                             " AND n." + searchField +  " " + matchMode.getMatchOperator() + " :queryString";
 425
 426                         synonymSubselect = "select s.id from" +
 427                             " Taxon t" + // the taxa
 428                             " join t.taxonNodes as tn "+
 429                             " join t.synonymRelations sr" +
 430                             " join sr.relatedFrom s" + // the synonyms
 431                             " join s.name sn"+
 432                             " where" +
 433                             " tn.classification = :classification" +
 434                             " AND sn." + searchField +  " " + matchMode.getMatchOperator() + " :queryString";
 435                     }
 436                 }else{
 437                     if(doAreaRestriction){
 438                         if (!doTaxa && !doSynonyms ){
 439                             misappliedSelect = "select t.id from" +
 440                             " Distribution e" +
 441                             " join e.inDescription d" +
 442                             " join d.taxon t" +
 443                             " join t.name n " +
 444                             " join t.taxonNodes as tn "+
 445                             " left join t.relationsFromThisTaxon as rft" +
 446                             " left join rft.relatedTo as rt" +
 447                             " left join rt.taxonNodes as tn2" +
 448                             " left join rt.name as n2" +
 449                             " left join rft.type as rtype"+
 450                             " where" +
 451                             " e.area.uuid in (:namedAreasUuids) AND" +
 452                             " (tn.classification != :classification" +
 453                             " AND n." + searchField + " " + matchMode.getMatchOperator() + " :queryString" +
 454                             " AND tn2.classification = :classification" +
 455                             " AND rtype = :rType )";
 456
 457                         }else{
 458                             taxonSubselect = "select t.id from" +
 459                                 " Distribution e" +
 460                                 " join e.inDescription d" +
 461                                 " join d.taxon t" +
 462                                 " join t.name n " +
 463                                 " join t.taxonNodes as tn "+
 464                                 " left join t.relationsFromThisTaxon as rft" +
 465                                 " left join rft.relatedTo as rt" +
 466                                 " left join rt.taxonNodes as tn2" +
 467                                 " left join rt.name as n2" +
 468                                 " left join rft.type as rtype"+
 469                                 " where" +
 470                                 " e.area.uuid in (:namedAreasUuids) AND" +
 471                                 " (tn.classification = :classification" +
 472                                 " AND n." + searchField + " " + matchMode.getMatchOperator() + " :queryString )" +
 473                                 " OR"+
 474                                 " (tn.classification != :classification" +
 475                                 " AND n." + searchField + " " + matchMode.getMatchOperator() + " :queryString" +
 476                                 " AND tn2.classification = :classification" +
 477                                 " AND rtype = :rType )";
 478
 479
 480                             synonymSubselect = "select s.id from" +
 481                                 " Distribution e" +
 482                                 " join e.inDescription d" +
 483                                 " join d.taxon t" + // the taxa
 484                                 " join t.taxonNodes as tn "+
 485                                 " join t.synonymRelations sr" +
 486                                 " join sr.relatedFrom s" + // the synonyms
 487                                 " join s.name sn"+
 488                                 " where" +
 489                                 " e.area.uuid in (:namedAreasUuids) AND" +
 490                                 " tn.classification != :classification" +
 491                                 " AND sn." + searchField +  " " + matchMode.getMatchOperator() + " :queryString";
 492                         }
 493                     } else {
 494                         if (!doTaxa && !doSynonyms ){
 495                             misappliedSelect = "select t.id from" +
 496                             " Distribution e" +
 497                             " join e.inDescription d" +
 498                             " join d.taxon t" +
 499                             " join t.name n " +
 500                             " join t.taxonNodes as tn "+
 501                             " left join t.relationsFromThisTaxon as rft" +
 502                             " left join rft.relatedTo as rt" +
 503                             " left join rt.taxonNodes as tn2" +
 504                             " left join rt.name as n2" +
 505                             " left join rft.type as rtype"+
 506                             " where" +
 507                             " (tn.classification != :classification" +
 508                             " AND n." + searchField + " " + matchMode.getMatchOperator() + " :queryString" +
 509                             " AND tn2.classification = :classification" +
 510                             " AND rtype = :rType )";
 511
 512                         }else{
 513                             taxonSubselect = "select t.id from" +
 514                                 " Taxon t" +
 515                                 " join t.name n " +
 516                                 " join t.taxonNodes as tn "+
 517                                 " left join t.relationsFromThisTaxon as rft" +
 518                                 " left join rft.relatedTo as rt" +
 519                                 " left join rt.taxonNodes as tn2" +
 520                                 " left join rt.name as n2" +
 521                                 " left join rft.type as rtype"+
 522                                 " where " +
 523                                 " (tn.classification = :classification" +
 524                                 " AND n." + searchField + " " + matchMode.getMatchOperator() + " :queryString )" +
 525                                 " OR"+
 526                                 " (tn.classification != :classification" +
 527                                 " AND n." + searchField + " " + matchMode.getMatchOperator() + " :queryString" +
 528                                 " AND tn2.classification = :classification" +
 529                                 " AND rtype = :rType )";
 530
 531                             synonymSubselect = "select s.id from" +
 532                                 " Taxon t" + // the taxa
 533                                 " join t.taxonNodes as tn "+
 534                                 " join t.synonymRelations sr" +
 535                                 " join sr.relatedFrom s" + // the synonyms
 536                                 " join s.name sn"+
 537                                 " where" +
 538                                 " tn.classification != :classification" +
 539                                 " AND sn." + searchField +  " " + matchMode.getMatchOperator() + " :queryString";
 540                         }
 541                     }
 542                 }
 543             } else {
 544
 545                 if (!doIncludeMisappliedNames){
 546                     if(doAreaRestriction){
 547                         taxonSubselect = "select t.id from " +
 548                             " Distribution e" +
 549                             " join e.inDescription d" +
 550                             " join d.taxon t" +
 551                             " join t.name n "+
 552                             " where" +
 553                             (doAreaRestriction ? " e.area.uuid in (:namedAreasUuids) AND" : "") +
 554                             " n." + searchField +  " " + matchMode.getMatchOperator() + " :queryString";
 555
 556                         synonymSubselect = "select s.id from" +
 557                             " Distribution e" +
 558                             " join e.inDescription d" +
 559                             " join d.taxon t" + // the taxa
 560                             " join t.synonymRelations sr" +
 561                             " join sr.relatedFrom s" + // the synonyms
 562                             " join s.name sn"+
 563                             " where" +
 564                             (doAreaRestriction ? " e.area.uuid in (:namedAreasUuids) AND" : "") +
 565                             " sn." + searchField +  " " + matchMode.getMatchOperator() + " :queryString";
 566
 567                     } else {
 568
 569                         taxonSubselect = "select t.id from " +
 570                             " Taxon t" +
 571                             " join t.name n "+
 572                             " where" +
 573                             " n." + searchField +  " " + matchMode.getMatchOperator() + " :queryString";
 574
 575                         synonymSubselect = "select s.id from" +
 576                             " Taxon t" + // the taxa
 577                             " join t.synonymRelations sr" +
 578                             " join sr.relatedFrom s" + // the synonyms
 579                             " join s.name sn"+
 580                             " where" +
 581                             " sn." + searchField +  " " + matchMode.getMatchOperator() + " :queryString";
 582                     }
 583                 }else{
 584
 585                 }
 586
 587             }*/
 588
 589             logger.debug("taxonSubselect: " + taxonSubselect != null ? taxonSubselect: "NULL");
 590             logger.debug("synonymSubselect: " + synonymSubselect != null ? synonymSubselect: "NULL");
 591
 592             Query subTaxon = null;
 593             Query subSynonym = null;
 594             Query subMisappliedNames = null;
 595             if(doTaxa){
 596                 // find Taxa
 597                 subTaxon = getSession().createQuery(taxonSubselect).setParameter("queryString", hqlQueryString);
 598
 599                 if(doAreaRestriction){
 600                     subTaxon.setParameterList("namedAreasUuids", namedAreasUuids);
 601                 }
 602                 if(classification != null){
 603                     subTaxon.setParameter("classification", classification);
 604
 605                 }
 606
 607
 608             }
 609
 610             if(doSynonyms){
 611                 // find synonyms
 612                 subSynonym = getSession().createQuery(synonymSubselect).setParameter("queryString", hqlQueryString);
 613
 614                 if(doAreaRestriction){
 615                     subSynonym.setParameterList("namedAreasUuids", namedAreasUuids);
 616                 }
 617                 if(classification != null){
 618                     subSynonym.setParameter("classification", classification);
 619                 }
 620             }
 621             if (doIncludeMisappliedNames ){
 622                 subMisappliedNames = getSession().createQuery(misappliedSelect).setParameter("queryString", hqlQueryString);
 623                 subMisappliedNames.setParameter("rType", TaxonRelationshipType.MISAPPLIED_NAME_FOR());
 624                 if(doAreaRestriction){
 625                     subMisappliedNames.setParameterList("namedAreasUuids", namedAreasUuids);
 626                 }
 627                 if(classification != null){
 628                     subMisappliedNames.setParameter("classification", classification);
 629                 }
 630             }
 631
 632             List<Integer> taxa = new ArrayList<Integer>();
 633             List<Integer> synonyms = new ArrayList<Integer>();
 634             if (doSynonyms){
 635                 synonyms = subSynonym.list();
 636             }
 637             if(doTaxa){
 638                 taxa = subTaxon.list();
 639             }
 640             if (doIncludeMisappliedNames){
 641                 taxa.addAll(subMisappliedNames.list());
 642             }
 643
 644             //FIXME : the fourth element of the result should be a boolean, but in the case of a synonym
 645             // (which does require a check) a constant boolean (false) value needs to set. It seems that
 646             // hql cannot parse a constant boolean value in the select list clause. This implies that the
 647             // resulting object could be a Boolean or a String. The workaround for this is to convert the
 648             // resutling object into a String (using toString) and then create a new Boolean object from
 649             // String.
 650             if (doTaxa && doSynonyms){
 651                 if(synonyms.size()>0 && taxa.size()>0){
 652                     hql = "select " + selectWhat;
 653                     // in doNotReturnFullEntities mode it is nesscary to also return the type of the matching entities:
 654                     // also return the computed isOrphaned flag
 655                     if (doNotReturnFullEntities &&  !doCount ){
 656                         hql += ", case when t.id in (:taxa) then 'taxon' else 'synonym' end, " +
 657                                 " case when t.id in (:taxa) and t.taxonNodes is empty and t.relationsFromThisTaxon is empty and t.relationsToThisTaxon is empty then true else false end ";
 658                     }
 659                     hql +=  " from %s t " +
 660                             " where (t.id in (:taxa) OR t.id in (:synonyms)) ";
 661                 }else if (synonyms.size()>0 ){
 662                     hql = "select " + selectWhat;
 663                     // in doNotReturnFullEntities mode it is nesscary to also return the type of the matching entities:
 664                     // also return the computed isOrphaned flag
 665                     if (doNotReturnFullEntities &&  !doCount ){
 666                         hql += ", 'synonym', 'false' ";
 667
 668                     }
 669                     hql +=  " from %s t " +
 670                             " where t.id in (:synonyms) ";
 671
 672                 } else if (taxa.size()>0 ){
 673                     hql = "select " + selectWhat;
 674                     // in doNotReturnFullEntities mode it is nesscary to also return the type of the matching entities:
 675                     // also return the computed isOrphaned flag
 676                     if (doNotReturnFullEntities &&  !doCount ){
 677                         hql += ", 'taxon', " +
 678                                 " case when t.taxonNodes is empty and t.relationsFromThisTaxon is empty and t.relationsToThisTaxon is empty then true else false end ";
 679                     }
 680                     hql +=  " from %s t " +
 681                             " where t.id in (:taxa) ";
 682
 683                 } else{
 684                     hql = "select " + selectWhat + " from %s t";
 685                 }
 686             } else if(doTaxa){
 687                 if  (taxa.size()>0){
 688                     hql = "select " + selectWhat;
 689                     // in doNotReturnFullEntities mode it is nesscary to also return the type of the matching entities:
 690                     // also return the computed isOrphaned flag
 691                     if (doNotReturnFullEntities){
 692                         hql += ", 'taxon', " +
 693                                 " case when t.taxonNodes is empty and t.relationsFromThisTaxon is empty and t.relationsToThisTaxon is empty then true else false end ";
 694                     }
 695                     hql +=  " from %s t " +
 696                             " where t.id in (:taxa) ";
 697
 698                 }else{
 699                     hql = "select " + selectWhat + " from %s t";
 700                 }
 701             } else if(doSynonyms){
 702                 if (synonyms.size()>0){
 703
 704                     hql = "select " + selectWhat;
 705                     // in doNotReturnFullEntities mode it is nesscary to also return the type of the matching entities:
 706                     // also return the computed isOrphaned flag
 707                     if (doNotReturnFullEntities){
 708                         hql += ", 'synonym', 'false' ";
 709                     }
 710                     hql +=  " from %s t " +
 711                             " where t.id in (:synonyms) ";
 712                 }else{
 713                     hql = "select " + selectWhat + " from %s t";
 714                 }
 715             } else if (doIncludeMisappliedNames){
 716                 hql = "select " + selectWhat;
 717                 // in doNotReturnFullEntities mode it is nesscary to also return the type of the matching entities:
 718                 // also return the computed isOrphaned flag
 719                 if (doNotReturnFullEntities){
 720                     hql += ", 'taxon', " +
 721                             " case when t.taxonNodes is empty and t.relationsFromThisTaxon is empty and t.relationsToThisTaxon is empty then true else false end ";
 722                 }
 723                 hql +=  " from %s t " +
 724                         " where t.id in (:taxa) ";
 725
 726             }
 727
 728             String classString;
 729             if (doTaxa && doSynonyms){
 730                 classString = "TaxonBase";
 731             } else if (doTaxa){
 732                 classString = "Taxon";
 733             } else if (doSynonyms){
 734                 classString = "Synonym";
 735             } else{//only misappliedNames
 736                 classString = "Taxon";
 737             }
 738
 739             hql = String.format(hql, classString);
 740
 741
 742             if (hql == "") {
 743                 return null;
 744             }
 745             if(!doCount){
 746                 hql += " order by t.name.genusOrUninomial, case when t.name.specificEpithet like '\"%\"' then 1 else 0 end, t.name.specificEpithet, t.name.rank desc, t.name.nameCache";
 747             }
 748
 749             logger.debug("hql: " + hql);
 750             Query query = getSession().createQuery(hql);
 751
 752
 753             if (doTaxa && doSynonyms){
 754                 // find taxa and synonyms
 755                 if (taxa.size()>0){
 756                     query.setParameterList("taxa", taxa);
 757                 }
 758                 if (synonyms.size()>0){
 759                     query.setParameterList("synonyms",synonyms);
 760                 }
 761                 if (taxa.size()== 0 && synonyms.size() == 0){
 762                     return null;
 763                 }
 764             }else if(doTaxa){
 765                 //find taxa
 766                 if (taxa.size()>0){
 767                     query.setParameterList("taxa", taxa );
 768                 }else{
 769                     logger.warn("there are no taxa for the query: " + queryString);
 770                     return null;
 771                 }
 772             } else if(doSynonyms){
 773                 // find synonyms
 774                 if (synonyms.size()>0){
 775                     query.setParameterList("synonyms", synonyms);
 776                 }else{
 777                     return null;
 778                 }
 779             }   else{
 780                 //only misappliedNames
 781                 if (taxa.size()>0){
 782                     query.setParameterList("taxa", taxa );
 783                 }else{
 784                     return null;
 785                 }
 786             }
 787
 788             return query;
 789     }
 790
 791
 792     /**
 793      * @param searchField the field in TaxonNameBase to be searched through usually either <code>nameCache</code> or <code>titleCache</code>
 794      * @param queryString
 795      * @param classification TODO
 796      * @param matchMode
 797      * @param namedAreas
 798      * @param pageSize
 799      * @param pageNumber
 800      * @param doCount
 801      * @param clazz
 802      * @return
 803      *
 804      * FIXME implement classification restriction & implement test: see {@link TaxonDaoHibernateImplTest#testCountTaxaByName()}
 805      */
 806     private Query prepareTaxaByName(boolean doTaxa, boolean doSynonyms, boolean doMisappliedNames, String searchField, String queryString,
 807             Classification classification, MatchMode matchMode, Set<NamedArea> namedAreas, Integer pageSize, Integer pageNumber, boolean doCount) {
 808
 809         Query query = prepareQuery(doTaxa, doSynonyms, doMisappliedNames, searchField, queryString, classification, matchMode, namedAreas, doCount, false);
 810
 811         if(pageSize != null &&  !doCount && query != null) {
 812             query.setMaxResults(pageSize);
 813             if(pageNumber != null) {
 814                 query.setFirstResult(pageNumber * pageSize);
 815             }
 816         }
 817
 818         return query;
 819     }
 820
 821     private Query prepareTaxaByCommonName(String queryString, Classification classification,
 822             MatchMode matchMode, Set<NamedArea> namedAreas, Integer pageSize, Integer pageNumber, boolean doCount, boolean doNotReturnFullEntities){
 823
 824         String what = "select";
 825         if (doNotReturnFullEntities){
 826                 what += " t.uuid, t.id, t.titleCache, \'taxon\', case when t.taxonNodes is empty and t.relationsFromThisTaxon is empty and t.relationsToThisTaxon is empty then true else false end ";
 827         }else {
 828                 what += (doCount ? " count(t)": " t");
 829         }
 830         String hql= what + " from Taxon t " +
 831         "join t.descriptions d "+
 832         "join d.descriptionElements e " +
 833         "join e.feature f " +
 834         "where f.supportsCommonTaxonName = true and e.name "+matchMode.getMatchOperator()+" :queryString";//and ls.text like 'common%'";
 835
 836         Query query = getSession().createQuery(hql);
 837
 838         query.setParameter("queryString", matchMode.queryStringFrom(queryString));
 839
 840         if(pageSize != null &&  !doCount) {
 841             query.setMaxResults(pageSize);
 842             if(pageNumber != null) {
 843                 query.setFirstResult(pageNumber * pageSize);
 844             }
 845         }
 846         return query;
 847     }
 848
 849     @Override
 850     public long countTaxaByName(boolean doTaxa, boolean doSynonyms, boolean doMisappliedNames, String queryString, Classification classification,
 851         MatchMode matchMode, Set<NamedArea> namedAreas) {
 852
 853         boolean doCount = true;
 854         /*
 855         boolean doTaxa = true;
 856         boolean doSynonyms = true;
 857         if (clazz.equals(Taxon.class)){
 858             doSynonyms = false;
 859         } else if (clazz.equals(Synonym.class)){
 860             doTaxa = false;
 861         }
 862         */
 863
 864
 865         Query query = prepareTaxaByName(doTaxa, doSynonyms, doMisappliedNames, "nameCache", queryString, classification, matchMode, namedAreas, null, null, doCount);
 866         if (query != null) {
 867             return (Long)query.uniqueResult();
 868         }else{
 869             return 0;
 870         }
 871     }
 872
 873     /**
 874      * @param namedAreas
 875      * @param areasExpanded
 876      * @param areaQuery
 877      */
 878     private void expandNamedAreas(Collection<NamedArea> namedAreas, Set<NamedArea> areasExpanded, Query areaQuery) {
 879         List<NamedArea> childAreas;
 880         for(NamedArea a : namedAreas){
 881             areasExpanded.add(a);
 882             areaQuery.setParameter("area", a);
 883             childAreas = areaQuery.list();
 884             if(childAreas.size() > 0){
 885                 areasExpanded.addAll(childAreas);
 886                 expandNamedAreas(childAreas, areasExpanded, areaQuery);
 887             }
 888         }
 889     }
 890
 891
 892     @Override
 893     public List<TaxonBase> getAllTaxonBases(Integer pagesize, Integer page) {
 894         return super.list(pagesize, page);
 895     }
 896
 897     @Override
 898     public List<Synonym> getAllSynonyms(Integer limit, Integer start) {
 899         Criteria criteria = getSession().createCriteria(Synonym.class);
 900
 901         if(limit != null) {
 902             criteria.setFirstResult(start);
 903             criteria.setMaxResults(limit);
 904         }
 905
 906         return criteria.list();
 907     }
 908
 909     @Override
 910     public List<Taxon> getAllTaxa(Integer limit, Integer start) {
 911         Criteria criteria = getSession().createCriteria(Taxon.class);
 912
 913         if(limit != null) {
 914             criteria.setFirstResult(start);
 915             criteria.setMaxResults(limit);
 916         }
 917
 918         return criteria.list();
 919     }
 920
 921     @Override
 922     public List<RelationshipBase> getAllRelationships(/*Class<? extends RelationshipBase> clazz,*/ Integer limit, Integer start) {
 923         Class<? extends RelationshipBase> clazz = RelationshipBase.class;  //preliminary, see #2653
 924         AuditEvent auditEvent = getAuditEventFromContext();
 925         if(auditEvent.equals(AuditEvent.CURRENT_VIEW)) {
 926             // for some reason the HQL .class discriminator didn't work here so I created this preliminary
 927             // implementation for now. Should be cleaned in future.
 928
 929             List<RelationshipBase> result = new ArrayList<RelationshipBase>();
 930
 931             int taxRelSize = countAllRelationships(TaxonRelationship.class);
 932
 933             if (taxRelSize > start){
 934
 935                 String hql = " FROM TaxonRelationship as rb ORDER BY rb.id ";
 936                 Query query = getSession().createQuery(hql);
 937                 query.setFirstResult(start);
 938                 if (limit != null){
 939                     query.setMaxResults(limit);
 940                 }
 941                 result = query.list();
 942             }
 943             limit = limit - result.size();
 944             if (limit > 0){
 945                 String hql = " FROM SynonymRelationship as rb ORDER BY rb.id ";
 946                 Query query = getSession().createQuery(hql);
 947                 start = (taxRelSize > start) ? 0 : (start - taxRelSize);
 948                 query.setFirstResult(start);
 949                 if (limit != null){
 950                     query.setMaxResults(limit);
 951                 }
 952                 result.addAll(query.list());
 953             }
 954             return result;
 955
 956         } else {
 957             AuditQuery query = getAuditReader().createQuery().forEntitiesAtRevision(clazz,auditEvent.getRevisionNumber());
 958             return query.getResultList();
 959         }
 960     }
 961
 962     @Override
 963     public UUID delete(TaxonBase taxonBase) throws DataAccessException{
 964         if (taxonBase == null){
 965             logger.warn("TaxonBase was 'null'");
 966             return null;
 967         }
 968
 969         // Merge the object in if it is detached
 970         //
 971         // I think this is preferable to catching lazy initialization errors
 972         // as that solution only swallows and hides the exception, but doesn't
 973         // actually solve it.
 974         getSession().merge(taxonBase);
 975
 976         taxonBase.removeSources();
 977
 978         if (taxonBase instanceof Taxon){ //     is Taxon
 979             for (Iterator<TaxonRelationship> iterator = ((Taxon)taxonBase).getRelationsFromThisTaxon().iterator(); iterator.hasNext();){
 980                 TaxonRelationship relationFromThisTaxon = iterator.next();
 981
 982                 // decrease children count of taxonomic parent by one
 983                 if (relationFromThisTaxon.getType().equals(TaxonRelationshipType.TAXONOMICALLY_INCLUDED_IN())) {
 984                     Taxon toTaxon = relationFromThisTaxon.getToTaxon(); // parent
 985                     if (toTaxon != null) {
 986                         toTaxon.setTaxonomicChildrenCount(toTaxon.getTaxonomicChildrenCount() - 1);
 987                     }
 988                 }
 989             }
 990         }
 991
 992        return super.delete(taxonBase);
 993
 994     }
 995
 996     @Override
 997     public List<TaxonBase> findByNameTitleCache(boolean doTaxa, boolean doSynonyms, String queryString, Classification classification, MatchMode matchMode, Set<NamedArea> namedAreas, Integer pageNumber, Integer pageSize, List<String> propertyPaths) {
 998
 999         boolean doCount = false;
1000         Query query = prepareTaxaByName(doTaxa, doSynonyms, false, "titleCache", queryString, classification, matchMode, namedAreas, pageSize, pageNumber, doCount);
1001         if (query != null){
1002             List<TaxonBase> results = query.list();
1003             defaultBeanInitializer.initializeAll(results, propertyPaths);
1004             return results;
1005         }
1006         return new ArrayList<TaxonBase>();
1007
1008     }
1009
1010     @Override
1011     public TaxonBase findByUuid(UUID uuid, List<Criterion> criteria, List<String> propertyPaths) {
1012
1013         Criteria crit = getSession().createCriteria(type);
1014
1015         if (uuid != null) {
1016             crit.add(Restrictions.eq("uuid", uuid));
1017         } else {
1018             logger.warn("UUID is NULL");
1019             return null;
1020         }
1021         if(criteria != null){
1022             for (Criterion criterion : criteria) {
1023                 crit.add(criterion);
1024             }
1025         }
1026         crit.addOrder(Order.asc("uuid"));
1027
1028         List<? extends TaxonBase> results = crit.list();
1029         if (results.size() == 1) {
1030             defaultBeanInitializer.initializeAll(results, propertyPaths);
1031             TaxonBase taxon = results.iterator().next();
1032             return taxon;
1033         } else if (results.size() > 1) {
1034             logger.error("Multiple results for UUID: " + uuid);
1035         } else if (results.size() == 0) {
1036             logger.info("No results for UUID: " + uuid);
1037         }
1038
1039         return null;
1040     }
1041
1042     @Override
1043     public List<? extends TaxonBase> findByUuids(List<UUID> uuids, List<Criterion> criteria, List<String> propertyPaths) {
1044
1045         Criteria crit = getSession().createCriteria(type);
1046
1047         if (uuids != null) {
1048             crit.add(Restrictions.in("uuid", uuids));
1049         } else {
1050             logger.warn("List<UUID> uuids is NULL");
1051             return null;
1052         }
1053         if(criteria != null){
1054             for (Criterion criterion : criteria) {
1055                 crit.add(criterion);
1056             }
1057         }
1058         crit.addOrder(Order.asc("uuid"));
1059
1060         List<? extends TaxonBase> results = crit.list();
1061
1062         defaultBeanInitializer.initializeAll(results, propertyPaths);
1063         return results;
1064     }
1065
1066     @Override
1067     public int countMatchesByName(String queryString, MatchMode matchMode, boolean onlyAcccepted) {
1068         checkNotInPriorView("TaxonDaoHibernateImpl.countMatchesByName(String queryString, ITitledDao.MATCH_MODE matchMode, boolean onlyAcccepted)");
1069         Criteria crit = getSession().createCriteria(type);
1070         crit.add(Restrictions.ilike("titleCache", matchMode.queryStringFrom(queryString)));
1071         crit.setProjection(Projections.rowCount());
1072         int result = ((Number)crit.list().get(0)).intValue();
1073         return result;
1074     }
1075
1076
1077     @Override
1078     public int countMatchesByName(String queryString, MatchMode matchMode, boolean onlyAcccepted, List<Criterion> criteria) {
1079         checkNotInPriorView("TaxonDaoHibernateImpl.countMatchesByName(String queryString, ITitledDao.MATCH_MODE matchMode, boolean onlyAcccepted, List<Criterion> criteria)");
1080         Criteria crit = getSession().createCriteria(type);
1081         crit.add(Restrictions.ilike("titleCache", matchMode.queryStringFrom(queryString)));
1082         if(criteria != null){
1083             for (Criterion criterion : criteria) {
1084                 crit.add(criterion);
1085             }
1086         }
1087         crit.setProjection(Projections.rowCount());
1088         int result = ((Number)crit.list().get(0)).intValue();
1089         return result;
1090     }
1091
1092     @Override
1093     public int countTaxonRelationships(Taxon taxon, TaxonRelationshipType type, Direction direction) {
1094         AuditEvent auditEvent = getAuditEventFromContext();
1095         if(auditEvent.equals(AuditEvent.CURRENT_VIEW)) {
1096             Query query = null;
1097
1098             if(type == null) {
1099                 query = getSession().createQuery("select count(taxonRelationship) from TaxonRelationship taxonRelationship where taxonRelationship."+direction+" = :relatedTaxon");
1100             } else {
1101                 query = getSession().createQuery("select count(taxonRelationship) from TaxonRelationship taxonRelationship where taxonRelationship."+direction+" = :relatedTaxon and taxonRelationship.type = :type");
1102                 query.setParameter("type",type);
1103             }
1104             query.setParameter("relatedTaxon", taxon);
1105
1106             return ((Long)query.uniqueResult()).intValue();
1107         } else {
1108             AuditQuery query = getAuditReader().createQuery().forEntitiesAtRevision(TaxonRelationship.class,auditEvent.getRevisionNumber());
1109             query.add(AuditEntity.relatedId(direction.toString()).eq(taxon.getId()));
1110             query.addProjection(AuditEntity.id().count());
1111
1112             if(type != null) {
1113                 query.add(AuditEntity.relatedId("type").eq(type.getId()));
1114             }
1115
1116             return ((Long)query.getSingleResult()).intValue();
1117         }
1118     }
1119
1120     @Override
1121     public int countSynonyms(Taxon taxon, SynonymRelationshipType type) {
1122         AuditEvent auditEvent = getAuditEventFromContext();
1123         if(auditEvent.equals(AuditEvent.CURRENT_VIEW)) {
1124             Criteria criteria = getSession().createCriteria(SynonymRelationship.class);
1125
1126             criteria.add(Restrictions.eq("relatedTo", taxon));
1127             if(type != null) {
1128                 criteria.add(Restrictions.eq("type", type));
1129             }
1130             criteria.setProjection(Projections.rowCount());
1131             return ((Number)criteria.uniqueResult()).intValue();
1132         } else {
1133             AuditQuery query = getAuditReader().createQuery().forEntitiesAtRevision(SynonymRelationship.class,auditEvent.getRevisionNumber());
1134             query.add(AuditEntity.relatedId("relatedTo").eq(taxon.getId()));
1135             query.addProjection(AuditEntity.id().count());
1136
1137             if(type != null) {
1138                 query.add(AuditEntity.relatedId("type").eq(type.getId()));
1139             }
1140
1141             return ((Long)query.getSingleResult()).intValue();
1142         }
1143     }
1144
1145     @Override
1146     public int countSynonyms(Synonym synonym, SynonymRelationshipType type) {
1147         AuditEvent auditEvent = getAuditEventFromContext();
1148         if(auditEvent.equals(AuditEvent.CURRENT_VIEW)) {
1149             Criteria criteria = getSession().createCriteria(SynonymRelationship.class);
1150
1151             criteria.add(Restrictions.eq("relatedFrom", synonym));
1152             if(type != null) {
1153                 criteria.add(Restrictions.eq("type", type));
1154             }
1155
1156             criteria.setProjection(Projections.rowCount());
1157             return ((Number)criteria.uniqueResult()).intValue();
1158         } else {
1159             AuditQuery query = getAuditReader().createQuery().forEntitiesAtRevision(SynonymRelationship.class,auditEvent.getRevisionNumber());
1160             query.add(AuditEntity.relatedId("relatedFrom").eq(synonym.getId()));
1161             query.addProjection(AuditEntity.id().count());
1162
1163             if(type != null) {
1164                 query.add(AuditEntity.relatedId("type").eq(type.getId()));
1165             }
1166
1167             return ((Long)query.getSingleResult()).intValue();
1168         }
1169     }
1170
1171     @Override
1172     public int countTaxaByName(Class<? extends TaxonBase> clazz, String genusOrUninomial, String infraGenericEpithet, String specificEpithet,   String infraSpecificEpithet, Rank rank) {
1173         checkNotInPriorView("TaxonDaoHibernateImpl.countTaxaByName(Boolean accepted, String genusOrUninomial,   String infraGenericEpithet, String specificEpithet,     String infraSpecificEpithet, Rank rank)");
1174         Criteria criteria = null;
1175
1176         criteria = getSession().createCriteria(clazz);
1177
1178         criteria.setFetchMode( "name", FetchMode.JOIN );
1179         criteria.createAlias("name", "name");
1180
1181         if(genusOrUninomial == null) {
1182             criteria.add(Restrictions.isNull("name.genusOrUninomial"));
1183         } else if(!genusOrUninomial.equals("*")) {
1184             criteria.add(Restrictions.eq("name.genusOrUninomial", genusOrUninomial));
1185         }
1186
1187         if(infraGenericEpithet == null) {
1188             criteria.add(Restrictions.isNull("name.infraGenericEpithet"));
1189         } else if(!infraGenericEpithet.equals("*")) {
1190             criteria.add(Restrictions.eq("name.infraGenericEpithet", infraGenericEpithet));
1191         }
1192
1193         if(specificEpithet == null) {
1194             criteria.add(Restrictions.isNull("name.specificEpithet"));
1195         } else if(!specificEpithet.equals("*")) {
1196             criteria.add(Restrictions.eq("name.specificEpithet", specificEpithet));
1197
1198         }
1199
1200         if(infraSpecificEpithet == null) {
1201             criteria.add(Restrictions.isNull("name.infraSpecificEpithet"));
1202         } else if(!infraSpecificEpithet.equals("*")) {
1203             criteria.add(Restrictions.eq("name.infraSpecificEpithet", infraSpecificEpithet));
1204         }
1205
1206         if(rank != null) {
1207             criteria.add(Restrictions.eq("name.rank", rank));
1208         }
1209
1210         criteria.setProjection(Projections.projectionList().add(Projections.rowCount()));
1211
1212         return ((Number)criteria.uniqueResult()).intValue();
1213     }
1214
1215     @Override
1216     public List<TaxonBase> findTaxaByName(Class<? extends TaxonBase> clazz, String genusOrUninomial, String infraGenericEpithet, String specificEpithet, String infraSpecificEpithet, String authorship, Rank rank, Integer pageSize,   Integer pageNumber) {
1217         checkNotInPriorView("TaxonDaoHibernateImpl.findTaxaByName(Boolean accepted, String genusOrUninomial, String infraGenericEpithet, String specificEpithet, String infraSpecificEpithet, String authorship, Rank rank, Integer pageSize,   Integer pageNumber)");
1218         Criteria criteria = null;
1219         if (clazz == null){
1220             criteria = getSession().createCriteria(TaxonBase.class);
1221         } else{
1222             criteria = getSession().createCriteria(clazz);
1223         }
1224         criteria.setFetchMode( "name", FetchMode.JOIN );
1225         criteria.createAlias("name", "name");
1226
1227         if(genusOrUninomial == null) {
1228             criteria.add(Restrictions.isNull("name.genusOrUninomial"));
1229         } else if(!genusOrUninomial.equals("*")) {
1230             criteria.add(Restrictions.eq("name.genusOrUninomial", genusOrUninomial));
1231         }
1232
1233         if(infraGenericEpithet == null) {
1234             criteria.add(Restrictions.isNull("name.infraGenericEpithet"));
1235         } else if(!infraGenericEpithet.equals("*")) {
1236             criteria.add(Restrictions.eq("name.infraGenericEpithet", infraGenericEpithet));
1237         }
1238
1239         if(specificEpithet == null) {
1240             criteria.add(Restrictions.isNull("name.specificEpithet"));
1241         } else if(!specificEpithet.equals("*")) {
1242             criteria.add(Restrictions.eq("name.specificEpithet", specificEpithet));
1243
1244         }
1245
1246         if(infraSpecificEpithet == null) {
1247             criteria.add(Restrictions.isNull("name.infraSpecificEpithet"));
1248         } else if(!infraSpecificEpithet.equals("*")) {
1249             criteria.add(Restrictions.eq("name.infraSpecificEpithet", infraSpecificEpithet));
1250         }
1251
1252         if(authorship == null) {
1253             criteria.add(Restrictions.eq("name.authorshipCache", ""));
1254         } else if(!authorship.equals("*")) {
1255             criteria.add(Restrictions.eq("name.authorshipCache", authorship));
1256         }
1257
1258         if(rank != null) {
1259             criteria.add(Restrictions.eq("name.rank", rank));
1260         }
1261
1262         if(pageSize != null) {
1263             criteria.setMaxResults(pageSize);
1264             if(pageNumber != null) {
1265                 criteria.setFirstResult(pageNumber * pageSize);
1266             } else {
1267                 criteria.setFirstResult(0);
1268             }
1269         }
1270
1271         return criteria.list();
1272     }
1273
1274     @Override
1275     public List<TaxonRelationship> getTaxonRelationships(Taxon taxon, TaxonRelationshipType type,
1276             Integer pageSize, Integer pageNumber, List<OrderHint> orderHints, List<String> propertyPaths, Direction direction) {
1277
1278         AuditEvent auditEvent = getAuditEventFromContext();
1279         if(auditEvent.equals(AuditEvent.CURRENT_VIEW)) {
1280
1281             Criteria criteria = getSession().createCriteria(TaxonRelationship.class);
1282
1283             if(direction != null) {
1284                 criteria.add(Restrictions.eq(direction.name(), taxon));
1285             } else {
1286                 criteria.add(Restrictions.or(
1287                         Restrictions.eq(Direction.relatedFrom.name(), taxon),
1288                         Restrictions.eq(Direction.relatedTo.name(), taxon))
1289                     );
1290             }
1291
1292             if(type != null) {
1293                 criteria.add(Restrictions.eq("type", type));
1294             }
1295
1296             addOrder(criteria,orderHints);
1297
1298             if(pageSize != null) {
1299                 criteria.setMaxResults(pageSize);
1300                 if(pageNumber != null) {
1301                     criteria.setFirstResult(pageNumber * pageSize);
1302                 } else {
1303                     criteria.setFirstResult(0);
1304                 }
1305             }
1306
1307             List<TaxonRelationship> result = criteria.list();
1308             defaultBeanInitializer.initializeAll(result, propertyPaths);
1309
1310             return result;
1311         } else {
1312             AuditQuery query = getAuditReader().createQuery().forEntitiesAtRevision(TaxonRelationship.class,auditEvent.getRevisionNumber());
1313             query.add(AuditEntity.relatedId("relatedTo").eq(taxon.getId()));
1314
1315             if(type != null) {
1316                 query.add(AuditEntity.relatedId("type").eq(type.getId()));
1317             }
1318
1319             if(pageSize != null) {
1320                 query.setMaxResults(pageSize);
1321                 if(pageNumber != null) {
1322                     query.setFirstResult(pageNumber * pageSize);
1323                 } else {
1324                     query.setFirstResult(0);
1325                 }
1326             }
1327
1328             List<TaxonRelationship> result = query.getResultList();
1329             defaultBeanInitializer.initializeAll(result, propertyPaths);
1330
1331             // Ugly, but for now, there is no way to sort on a related entity property in Envers,
1332             // and we can't live without this functionality in CATE as it screws up the whole
1333             // taxon tree thing
1334             if(orderHints != null && !orderHints.isEmpty()) {
1335                 SortedSet<TaxonRelationship> sortedList = new TreeSet<TaxonRelationship>(new TaxonRelationshipFromTaxonComparator());
1336                 sortedList.addAll(result);
1337                 return new ArrayList<TaxonRelationship>(sortedList);
1338             }
1339
1340             return result;
1341         }
1342     }
1343
1344     class TaxonRelationshipFromTaxonComparator implements Comparator<TaxonRelationship> {
1345
1346         @Override
1347         public int compare(TaxonRelationship o1, TaxonRelationship o2) {
1348             return o1.getFromTaxon().getTitleCache().compareTo(o2.getFromTaxon().getTitleCache());
1349         }
1350
1351     }
1352
1353     class SynonymRelationshipFromTaxonComparator implements Comparator<SynonymRelationship> {
1354
1355         @Override
1356         public int compare(SynonymRelationship o1, SynonymRelationship o2) {
1357             return o1.getSynonym().getTitleCache().compareTo(o2.getSynonym().getTitleCache());
1358         }
1359
1360     }
1361
1362     @Override
1363     public List<SynonymRelationship> getSynonyms(Taxon taxon, SynonymRelationshipType type, Integer pageSize, Integer pageNumber, List<OrderHint> orderHints, List<String> propertyPaths) {
1364         AuditEvent auditEvent = getAuditEventFromContext();
1365         if(auditEvent.equals(AuditEvent.CURRENT_VIEW)) {
1366             Criteria criteria = getSession().createCriteria(SynonymRelationship.class);
1367
1368             criteria.add(Restrictions.eq("relatedTo", taxon));
1369             if(type != null) {
1370                 criteria.add(Restrictions.eq("type", type));
1371             }
1372
1373             addOrder(criteria,orderHints);
1374
1375             if(pageSize != null) {
1376                 criteria.setMaxResults(pageSize);
1377                 if(pageNumber != null) {
1378                     criteria.setFirstResult(pageNumber * pageSize);
1379                 } else {
1380                     criteria.setFirstResult(0);
1381                 }
1382             }
1383
1384             List<SynonymRelationship> result = criteria.list();
1385             defaultBeanInitializer.initializeAll(result, propertyPaths);
1386
1387             return result;
1388         } else {
1389             AuditQuery query = getAuditReader().createQuery().forEntitiesAtRevision(SynonymRelationship.class,auditEvent.getRevisionNumber());
1390             query.add(AuditEntity.relatedId("relatedTo").eq(taxon.getId()));
1391
1392             if(type != null) {
1393                 query.add(AuditEntity.relatedId("type").eq(type.getId()));
1394             }
1395
1396             if(pageSize != null) {
1397                 query.setMaxResults(pageSize);
1398                 if(pageNumber != null) {
1399                     query.setFirstResult(pageNumber * pageSize);
1400                 } else {
1401                     query.setFirstResult(0);
1402                 }
1403             }
1404
1405             List<SynonymRelationship> result = query.getResultList();
1406             defaultBeanInitializer.initializeAll(result, propertyPaths);
1407
1408             return result;
1409         }
1410     }
1411
1412     @Override
1413     public List<SynonymRelationship> getSynonyms(Synonym synonym, SynonymRelationshipType type, Integer pageSize, Integer pageNumber, List<OrderHint> orderHints, List<String> propertyPaths) {
1414         AuditEvent auditEvent = getAuditEventFromContext();
1415         if(auditEvent.equals(AuditEvent.CURRENT_VIEW)) {
1416             Criteria criteria = getSession().createCriteria(SynonymRelationship.class);
1417
1418             criteria.add(Restrictions.eq("relatedFrom", synonym));
1419             if(type != null) {
1420                 criteria.add(Restrictions.eq("type", type));
1421             }
1422
1423             addOrder(criteria,orderHints);
1424
1425             if(pageSize != null) {
1426                 criteria.setMaxResults(pageSize);
1427                 if(pageNumber != null) {
1428                     criteria.setFirstResult(pageNumber * pageSize);
1429                 } else {
1430                     criteria.setFirstResult(0);
1431                 }
1432             }
1433
1434             List<SynonymRelationship> result = criteria.list();
1435             defaultBeanInitializer.initializeAll(result, propertyPaths);
1436
1437             return result;
1438         } else {
1439             AuditQuery query = getAuditReader().createQuery().forEntitiesAtRevision(SynonymRelationship.class,auditEvent.getRevisionNumber());
1440             query.add(AuditEntity.relatedId("relatedFrom").eq(synonym.getId()));
1441
1442             if(type != null) {
1443                 query.add(AuditEntity.relatedId("type").eq(type.getId()));
1444             }
1445
1446             if(pageSize != null) {
1447                 query.setMaxResults(pageSize);
1448                 if(pageNumber != null) {
1449                     query.setFirstResult(pageNumber * pageSize);
1450                 } else {
1451                     query.setFirstResult(0);
1452                 }
1453             }
1454
1455             List<SynonymRelationship> result = query.getResultList();
1456             defaultBeanInitializer.initializeAll(result, propertyPaths);
1457
1458             return result;
1459         }
1460     }
1461
1462     @Override
1463     public void rebuildIndex() {
1464         FullTextSession fullTextSession = Search.getFullTextSession(getSession());
1465
1466         for(TaxonBase taxonBase : list(null,null)) { // re-index all taxon base
1467             Hibernate.initialize(taxonBase.getName());
1468             fullTextSession.index(taxonBase);
1469         }
1470         fullTextSession.flushToIndexes();
1471     }
1472
1473     @Override
1474     public String suggestQuery(String queryString) {
1475         throw new RuntimeException("Query suggestion currently not implemented in TaxonDaoHibernateImpl");
1476 //        checkNotInPriorView("TaxonDaoHibernateImpl.suggestQuery(String queryString)");
1477 //        String alternativeQueryString = null;
1478 //        if (alternativeSpellingSuggestionParser != null) {
1479 //            try {
1480 //
1481 //                alternativeSpellingSuggestionParser.parse(queryString);
1482 //                org.apache.lucene.search.Query alternativeQuery = alternativeSpellingSuggestionParser.suggest(queryString);
1483 //                if (alternativeQuery != null) {
1484 //                    alternativeQueryString = alternativeQuery
1485 //                            .toString("name.titleCache");
1486 //                }
1487 //
1488 //            } catch (ParseException e) {
1489 //                throw new QueryParseException(e, queryString);
1490 //            }
1491 //        }
1492 //        return alternativeQueryString;
1493     }
1494
1495     @Override
1496     public List<Taxon> listAcceptedTaxaFor(Synonym synonym, Classification classificationFilter, Integer pageSize, Integer pageNumber,
1497             List<OrderHint> orderHints, List<String> propertyPaths){
1498
1499         String hql = prepareListAcceptedTaxaFor(classificationFilter, orderHints, false);
1500
1501         Query query = getSession().createQuery(hql);
1502
1503         query.setParameter("synonym", synonym);
1504
1505         if(classificationFilter != null){
1506             query.setParameter("classificationFilter", classificationFilter);
1507         }
1508
1509
1510         if(pageSize != null) {
1511             query.setMaxResults(pageSize);
1512             if(pageNumber != null) {
1513                 query.setFirstResult(pageNumber * pageSize);
1514             }
1515         }
1516
1517         @SuppressWarnings("unchecked")
1518         List<Taxon> result = query.list();
1519
1520         defaultBeanInitializer.initializeAll(result, propertyPaths);
1521
1522         return result;
1523
1524     }
1525
1526     @Override
1527     public long countAcceptedTaxaFor(Synonym synonym, Classification classificationFilter){
1528
1529         String hql = prepareListAcceptedTaxaFor(classificationFilter, null, true);
1530
1531         Query query = getSession().createQuery(hql);
1532
1533         query.setParameter("synonym", synonym);
1534
1535         if(classificationFilter != null){
1536             query.setParameter("classificationFilter", classificationFilter);
1537         }
1538
1539         Long count = Long.parseLong(query.uniqueResult().toString());
1540
1541         return count;
1542
1543     }
1544
1545
1546     /**
1547      * @param classificationFilter
1548      * @param orderHints
1549      * @return
1550      */
1551     private String prepareListAcceptedTaxaFor(Classification classificationFilter, List<OrderHint> orderHints, boolean doCount) {
1552
1553         String hql;
1554         String hqlSelect = "select " + (doCount? "count(taxon)" : "taxon") + " from Taxon as taxon left join taxon.synonymRelations as synRel ";
1555         String hqlWhere = " where synRel.relatedFrom = :synonym";
1556
1557         if(classificationFilter != null){
1558             hqlSelect += " left join taxon.taxonNodes AS taxonNode";
1559             hqlWhere += " and taxonNode.classification = :classificationFilter";
1560         }
1561         hql = hqlSelect + hqlWhere + orderByClause(orderHints, "taxon");
1562         return hql;
1563     }
1564     @Override
1565     public List<UuidAndTitleCache<TaxonNode>> getTaxonNodeUuidAndTitleCacheOfAcceptedTaxaByClassification(Classification classification, Integer limit, String pattern) {
1566         int classificationId = classification.getId();
1567         // StringBuffer excludeUuids = new StringBuffer();
1568
1569          String queryString = "SELECT nodes.uuid, nodes.id, taxon.titleCache FROM TaxonNode AS nodes JOIN nodes.taxon as taxon WHERE nodes.classification.id = " + classificationId ;
1570
1571          if (pattern != null){
1572              pattern = pattern.replace("*", "%");
1573              queryString = queryString + " AND taxon.titleCache like (:pattern)" ;
1574          }
1575
1576          Query query = getSession().createQuery(queryString);
1577
1578
1579          if (limit != null){
1580              query.setMaxResults(limit);
1581          }
1582
1583          if (pattern != null){
1584              query.setParameter("pattern", pattern);
1585          }
1586          @SuppressWarnings("unchecked")
1587          List<Object[]> result = query.list();
1588
1589          if(result.size() == 0){
1590              return null;
1591          }else{
1592              List<UuidAndTitleCache<TaxonNode>> list = new ArrayList<UuidAndTitleCache<TaxonNode>>(result.size());
1593
1594              for (Object object : result){
1595
1596                  Object[] objectArray = (Object[]) object;
1597
1598                  UUID uuid = (UUID)objectArray[0];
1599                  Integer id = (Integer) objectArray[1];
1600                  String titleCache = (String) objectArray[2];
1601
1602                  list.add(new UuidAndTitleCache<TaxonNode>(TaxonNode.class, uuid, id, titleCache));
1603              }
1604
1605              return list;
1606          }
1607     }
1608
1609
1610     @Override
1611     public TaxonBase find(LSID lsid) {
1612         TaxonBase<?> taxonBase = super.find(lsid);
1613         if(taxonBase != null) {
1614             List<String> propertyPaths = new ArrayList<String>();
1615             propertyPaths.add("createdBy");
1616             propertyPaths.add("updatedBy");
1617             propertyPaths.add("name");
1618             propertyPaths.add("sec");
1619             propertyPaths.add("relationsToThisTaxon");
1620             propertyPaths.add("relationsToThisTaxon.fromTaxon");
1621             propertyPaths.add("relationsToThisTaxon.toTaxon");
1622             propertyPaths.add("relationsFromThisTaxon");
1623             propertyPaths.add("relationsFromThisTaxon.toTaxon");
1624             propertyPaths.add("relationsToThisTaxon.type");
1625             propertyPaths.add("synonymRelations");
1626             propertyPaths.add("synonymRelations.synonym");
1627             propertyPaths.add("synonymRelations.type");
1628             propertyPaths.add("descriptions");
1629
1630             defaultBeanInitializer.initialize(taxonBase, propertyPaths);
1631         }
1632         return taxonBase;
1633     }
1634
1635     public List<TaxonBase> getTaxaByCommonName(String queryString,
1636             Classification classification, MatchMode matchMode,
1637             Set<NamedArea> namedAreas, Integer pageSize, Integer pageNumber) {
1638         logger.warn("getTaxaByCommonName not yet implemented.");
1639         return null;
1640     }
1641
1642
1643
1644
1645 /*      private void xxx(List<SynonymRelationship> synonymRelationships, HashMap <UUID, ZoologicalName> zooHashMap, SynonymRelationshipType type, String addString){
1646
1647         for (SynonymRelationship synonymRelation:synonymRelationships){
1648             TaxonNameBase synName;
1649             NonViralName inferredSynName;
1650             Synonym syn = synonymRelation.getSynonym();
1651             HibernateProxyHelper.deproxy(syn);
1652
1653             synName = syn.getName();
1654             ZoologicalName zooName = zooHashMap.get(synName.getUuid());
1655             String synGenusName = zooName.getGenusOrUninomial();
1656
1657             switch(type.getId()){
1658             case SynonymRelationshipType.INFERRED_EPITHET_OF().getId():
1659                 inferredSynName.setSpecificEpithet(addString);
1660                 break;
1661             case SynonymRelationshipType.INFERRED_GENUS_OF().getId():
1662                 break;
1663             case SynonymRelationshipType.POTENTIAL_COMBINATION_OF().getId():
1664                 break;
1665             default:
1666             }
1667             if (!synonymsGenus.contains(synGenusName)){
1668                 synonymsGenus.add(synGenusName);
1669             }
1670             inferredSynName = NonViralName.NewInstance(Rank.SPECIES());
1671             inferredSynName.setSpecificEpithet(epithetOfTaxon);
1672             inferredSynName.setGenusOrUninomial(synGenusName);
1673             inferredEpithet = Synonym.NewInstance(inferredSynName, null);
1674             taxon.addSynonym(inferredEpithet, SynonymRelationshipType.INFERRED_GENUS_OF());
1675             inferredSynonyms.add(inferredEpithet);
1676             inferredSynName.generateTitle();
1677             taxonNames.add(inferredSynName.getNameCache());
1678         }
1679
1680
1681         if (!taxonNames.isEmpty()){
1682         List<String> synNotInCDM = this.taxaByNameNotInDB(taxonNames);
1683         ZoologicalName name;
1684         if (!synNotInCDM.isEmpty()){
1685             for (Synonym syn :inferredSynonyms){
1686                 name =zooHashMap.get(syn.getName().getUuid());
1687                 if (!synNotInCDM.contains(name.getNameCache())){
1688                     inferredSynonyms.remove(syn);
1689                 }
1690             }
1691         }
1692         }
1693     }*/
1694
1695     @Override
1696     public int countAllRelationships() {
1697         return countAllRelationships(null);
1698     }
1699
1700
1701     //FIXME add to interface or make private
1702     public int countAllRelationships(Class<? extends RelationshipBase> clazz) {
1703         if (clazz != null && ! TaxonRelationship.class.isAssignableFrom(clazz) && ! SynonymRelationship.class.isAssignableFrom(clazz) ){
1704             throw new RuntimeException("Class must be assignable by a taxon or snonym relation");
1705         }
1706         int size = 0;
1707
1708         if (clazz == null || TaxonRelationship.class.isAssignableFrom(clazz)){
1709             String hql = " SELECT count(rel) FROM TaxonRelationship rel";
1710             size += (Long)getSession().createQuery(hql).list().get(0);
1711         }
1712         if (clazz == null || SynonymRelationship.class.isAssignableFrom(clazz)){
1713             String hql = " SELECT count(rel) FROM SynonymRelationship rel";
1714             size += (Long)getSession().createQuery(hql).list().get(0);
1715         }
1716         return size;
1717     }
1718
1719     @Override
1720     public List<String> taxaByNameNotInDB(List<String> taxonNames){
1721         List<TaxonBase> notInDB = new ArrayList<TaxonBase>();
1722         //get all taxa, already in db
1723         Query query = getSession().createQuery("from TaxonNameBase t where t.nameCache IN (:taxonList)");
1724         query.setParameterList("taxonList", taxonNames);
1725         List<TaxonNameBase> taxaInDB = query.list();
1726         //compare the original list with the result of the query
1727         for (TaxonNameBase taxonName: taxaInDB){
1728             if (taxonName.isInstanceOf(NonViralName.class)) {
1729                 NonViralName nonViralName = CdmBase.deproxy(taxonName, NonViralName.class);
1730                 String nameCache = nonViralName.getNameCache();
1731                 if (taxonNames.contains(nameCache)){
1732                     taxonNames.remove(nameCache);
1733                 }
1734             }
1735         }
1736
1737         return taxonNames;
1738     }
1739
1740     //TODO: mal nur mit UUID probieren (ohne fetch all properties), vielleicht geht das schneller?
1741     @Override
1742     public List<UUID> findIdenticalTaxonNameIds(List<String> propertyPaths){
1743         Query query=getSession().createQuery("select tmb2 from ZoologicalName tmb, ZoologicalName tmb2 fetch all properties where tmb.id != tmb2.id and tmb.nameCache = tmb2.nameCache");
1744         List<UUID> zooNames = query.list();
1745
1746         return zooNames;
1747
1748     }
1749
1750     @Override
1751     public List<TaxonNameBase> findIdenticalTaxonNames(List<String> propertyPaths) {
1752
1753         Query query=getSession().createQuery("select tmb2 from ZoologicalName tmb, ZoologicalName tmb2 fetch all properties where tmb.id != tmb2.id and tmb.nameCache = tmb2.nameCache");
1754
1755         List<TaxonNameBase> zooNames = query.list();
1756
1757         TaxonNameComparator taxComp = new TaxonNameComparator();
1758         Collections.sort(zooNames, taxComp);
1759
1760         for (TaxonNameBase taxonNameBase: zooNames){
1761             defaultBeanInitializer.initialize(taxonNameBase, propertyPaths);
1762         }
1763
1764         return zooNames;
1765     }
1766
1767     @Override
1768     public List<TaxonNameBase> findIdenticalNamesNew(List<String> propertyPaths){
1769
1770         //Hole die beiden Source_ids von "Fauna Europaea" und "Erms" und in sources der names darf jeweils nur das entgegengesetzte auftreten (i member of tmb.taxonBases)
1771         Query query = getSession().createQuery("Select id from Reference where titleCache like 'Fauna Europaea database'");
1772         List<String> secRefFauna = query.list();
1773         query = getSession().createQuery("Select id from Reference where titleCache like 'ERMS'");
1774         List<String> secRefErms = query.list();
1775         //Query query = getSession().createQuery("select tmb2.nameCache from ZoologicalName tmb, TaxonBase tb1, ZoologicalName tmb2, TaxonBase tb2 where tmb.id != tmb2.id and tb1.name = tmb and tb2.name = tmb2 and tmb.nameCache = tmb2.nameCache and tb1.sec != tb2.sec");
1776         //Get all names of fauna europaea
1777         query = getSession().createQuery("select zn.nameCache from ZoologicalName zn, TaxonBase tb where tb.name = zn and tb.sec.id = :secRefFauna");
1778         query.setParameter("secRefFauna", secRefFauna.get(0));
1779         List<String> namesFauna= query.list();
1780
1781         //Get all names of erms
1782
1783         query = getSession().createQuery("select zn.nameCache from ZoologicalName zn, TaxonBase tb where tb.name = zn and tb.sec.id = :secRefErms");
1784         query.setParameter("secRefErms", secRefErms.get(0));
1785
1786         List<String> namesErms = query.list();
1787         /*TaxonNameComparator comp = new TaxonNameComparator();
1788         Collections.sort(namesFauna);
1789         Collections.sort(namesErms);
1790         */
1791         List <String> identicalNames = new ArrayList<String>();
1792         String predecessor = "";
1793
1794         for (String nameFauna: namesFauna){
1795             if (namesErms.contains(nameFauna)){
1796                 identicalNames.add(nameFauna);
1797             }
1798         }
1799
1800
1801         query = getSession().createQuery("from ZoologicalName zn where zn.nameCache IN (:identicalNames)");
1802         query.setParameterList("identicalNames", identicalNames);
1803         List<TaxonNameBase> result = query.list();
1804         TaxonNameBase temp = result.get(0);
1805
1806         Iterator<OriginalSourceBase> sources = temp.getSources().iterator();
1807
1808         TaxonNameComparator taxComp = new TaxonNameComparator();
1809         Collections.sort(result, taxComp);
1810         defaultBeanInitializer.initializeAll(result, propertyPaths);
1811         return result;
1812
1813     }
1814
1815
1816
1817     @Override
1818     public String getPhylumName(TaxonNameBase name){
1819         List results = new ArrayList();
1820         try{
1821         Query query = getSession().createSQLQuery("select getPhylum("+ name.getId()+");");
1822         results = query.list();
1823         }catch(Exception e){
1824             System.err.println(name.getUuid());
1825             return null;
1826         }
1827         System.err.println("phylum of "+ name.getTitleCache() );
1828         return (String)results.get(0);
1829     }
1830
1831
1832     @Override
1833     public long countTaxaByCommonName(String searchString,
1834             Classification classification, MatchMode matchMode,
1835             Set<NamedArea> namedAreas) {
1836         boolean doCount = true;
1837         Query query = prepareTaxaByCommonName(searchString, classification, matchMode, namedAreas, null, null, doCount, false);
1838         if (query != null && !query.list().isEmpty()) {
1839             Object o = query.uniqueResult();
1840             if(o != null) {
1841                 return (Long)o;
1842             }
1843         }
1844         return 0;
1845     }
1846
1847     @Override
1848     public long deleteSynonymRelationships(Synonym synonym, Taxon taxon) {
1849
1850         String hql = "delete SynonymRelationship sr where sr.relatedFrom = :syn ";
1851         if (taxon != null){
1852             hql += " and sr.relatedTo = :taxon";
1853         }
1854         Session session = this.getSession();
1855         Query q = session.createQuery(hql);
1856
1857         q.setParameter("syn", synonym);
1858         if (taxon != null){
1859             q.setParameter("taxon", taxon);
1860         }
1861         long result = q.executeUpdate();
1862
1863         return result;
1864     }
1865
1866
1867     @Override
1868     public Integer countSynonymRelationships(TaxonBase taxonBase,
1869             SynonymRelationshipType type, Direction relatedfrom) {
1870         AuditEvent auditEvent = getAuditEventFromContext();
1871         if(auditEvent.equals(AuditEvent.CURRENT_VIEW)) {
1872             Query query = null;
1873
1874             if(type == null) {
1875                 query = getSession().createQuery("select count(synonymRelationship) from SynonymRelationship synonymRelationship where synonymRelationship."+relatedfrom+" = :relatedSynonym");
1876             } else {
1877                 query = getSession().createQuery("select count(synonymRelationship) from SynonymRelationship synonymRelationship where synonymRelationship."+relatedfrom+" = :relatedSynonym and synonymRelationship.type = :type");
1878                 query.setParameter("type",type);
1879             }
1880             query.setParameter("relatedTaxon", taxonBase);
1881
1882             return ((Long)query.uniqueResult()).intValue();
1883         } else {
1884             AuditQuery query = getAuditReader().createQuery().forEntitiesAtRevision(TaxonRelationship.class,auditEvent.getRevisionNumber());
1885             query.add(AuditEntity.relatedId(relatedfrom.toString()).eq(taxonBase.getId()));
1886             query.addProjection(AuditEntity.id().count());
1887
1888             if(type != null) {
1889                 query.add(AuditEntity.relatedId("type").eq(type.getId()));
1890             }
1891
1892             return ((Long)query.getSingleResult()).intValue();
1893         }
1894     }
1895
1896
1897     @Override
1898     public List<SynonymRelationship> getSynonymRelationships(TaxonBase taxonBase,
1899             SynonymRelationshipType type, Integer pageSize, Integer pageNumber,
1900             List<OrderHint> orderHints, List<String> propertyPaths,
1901             Direction direction) {
1902
1903         AuditEvent auditEvent = getAuditEventFromContext();
1904         if(auditEvent.equals(AuditEvent.CURRENT_VIEW)) {
1905             Criteria criteria = getSession().createCriteria(SynonymRelationship.class);
1906
1907             if (direction.equals(Direction.relatedTo)){
1908                 criteria.add(Restrictions.eq("relatedTo", taxonBase));
1909             }else{
1910                 criteria.add(Restrictions.eq("relatedFrom", taxonBase));
1911             }
1912             if(type != null) {
1913                 criteria.add(Restrictions.eq("type", type));
1914             }
1915
1916             addOrder(criteria,orderHints);
1917
1918             if(pageSize != null) {
1919                 criteria.setMaxResults(pageSize);
1920                 if(pageNumber != null) {
1921                     criteria.setFirstResult(pageNumber * pageSize);
1922                 } else {
1923                     criteria.setFirstResult(0);
1924                 }
1925             }
1926
1927             List<SynonymRelationship> result = criteria.list();
1928             defaultBeanInitializer.initializeAll(result, propertyPaths);
1929
1930             return result;
1931         } else {
1932             AuditQuery query = getAuditReader().createQuery().forEntitiesAtRevision(TaxonRelationship.class,auditEvent.getRevisionNumber());
1933
1934             if (direction.equals(Direction.relatedTo)){
1935                 query.add(AuditEntity.relatedId("relatedTo").eq(taxonBase.getId()));
1936             }else{
1937                 query.add(AuditEntity.relatedId("relatedFrom").eq(taxonBase.getId()));
1938             }
1939
1940             if(type != null) {
1941                 query.add(AuditEntity.relatedId("type").eq(type.getId()));
1942             }
1943
1944             if(pageSize != null) {
1945                 query.setMaxResults(pageSize);
1946                 if(pageNumber != null) {
1947                     query.setFirstResult(pageNumber * pageSize);
1948                 } else {
1949                     query.setFirstResult(0);
1950                 }
1951             }
1952
1953             List<SynonymRelationship> result = query.getResultList();
1954             defaultBeanInitializer.initializeAll(result, propertyPaths);
1955
1956             // Ugly, but for now, there is no way to sort on a related entity property in Envers,
1957             // and we can't live without this functionality in CATE as it screws up the whole
1958             // taxon tree thing
1959             if(orderHints != null && !orderHints.isEmpty()) {
1960                 SortedSet<SynonymRelationship> sortedList = new TreeSet<SynonymRelationship>(new SynonymRelationshipFromTaxonComparator());
1961                 sortedList.addAll(result);
1962                 return new ArrayList<SynonymRelationship>(sortedList);
1963             }
1964
1965             return result;
1966         }
1967     }
1968
1969
1970     public List<UuidAndTitleCache<TaxonBase>> getUuidAndTitleCache(Integer limit, String pattern, boolean isTaxon) {
1971         String className;
1972         if (isTaxon){
1973             className = Taxon.class.getSimpleName();
1974         } else{
1975             className = Synonym.class.getSimpleName();
1976         }
1977         String queryString;
1978
1979         if(pattern == null){
1980             queryString = String.format("select uuid, id, titleCache from %s where DTYPE = '%s' ", type.getSimpleName(), className );
1981        } else{
1982            queryString = String.format("select uuid, id, titleCache from %s where DTYPE = '%s' and titleCache like :pattern", type.getSimpleName(), className);
1983        }
1984         Query query = getSession().createQuery(queryString);
1985         if (pattern != null){
1986             pattern = pattern.replace("*", "%");
1987             pattern = pattern.replace("?", "_");
1988             pattern = pattern + "%";
1989             pattern = pattern.replace("?", "_");
1990             query.setParameter("pattern", pattern);
1991         }
1992         if (limit  != null){
1993             query.setMaxResults(limit);
1994         }
1995
1996         List<UuidAndTitleCache<TaxonBase>> result = getUuidAndTitleCache(query);
1997
1998         return result;
1999     }
2000     @Override
2001     public List<UuidAndTitleCache<TaxonBase>> getUuidAndTitleCacheSynonym(Integer limit, String pattern){
2002
2003         return getUuidAndTitleCache(limit, pattern, false);
2004     }
2005
2006     @Override
2007     public List<UuidAndTitleCache<TaxonBase>> getUuidAndTitleCacheTaxon(Integer limit, String pattern){
2008
2009         return getUuidAndTitleCache(limit, pattern, true);
2010     }
2011
2012     private String[] createHQLString(boolean doTaxa, boolean doSynonyms, boolean doIncludeMisappliedNames, Classification classification,  Set<NamedArea> areasExpanded, MatchMode matchMode, String searchField){
2013
2014            boolean doAreaRestriction = areasExpanded.size() > 0;
2015            String       doAreaRestrictionSubSelect = "select %s.id from" +
2016                 " Distribution e" +
2017                 " join e.inDescription d" +
2018                 " join d.taxon t" +
2019                 (classification != null ? " join t.taxonNodes as tn " : " ");
2020
2021            String       doAreaRestrictionMisappliedNameSubSelect = "select %s.id from" +
2022             " Distribution e" +
2023             " join e.inDescription d" +
2024             " join d.taxon t";
2025
2026            String doTaxonSubSelect = "select %s.id from Taxon t " + (classification != null ? " join t.taxonNodes as tn " : " ");
2027            String doTaxonMisappliedNameSubSelect = "select %s.id from Taxon t ";
2028
2029            String doTaxonNameJoin =   " join t.name n ";
2030
2031            String doSynonymNameJoin =   " join t.synonymRelations sr join sr.relatedFrom s join s.name sn";
2032
2033            String doMisappliedNamesJoin = " left join t.relationsFromThisTaxon as rft" +
2034                 " left join rft.relatedTo as rt" +
2035                 (classification != null ? " left join rt.taxonNodes as tn2" : " ") +
2036                 " left join rt.name as n2" +
2037                 " left join rft.type as rtype";
2038
2039            String doClassificationWhere = " tn.classification = :classification";
2040            String doClassificationForMisappliedNamesWhere = " tn2 .classification = :classification";
2041
2042            String doAreaRestrictionWhere =  " e.area.uuid in (:namedAreasUuids)";
2043
2044            String doSearchFieldWhere = "%s." + searchField +  " " + matchMode.getMatchOperator() + " :queryString";
2045
2046            String doRelationshipTypeComparison = " rtype = :rType ";
2047
2048         String taxonSubselect = null;
2049         String synonymSubselect = null;
2050         String misappliedSelect = null;
2051
2052         if(classification != null ){
2053             if (!doIncludeMisappliedNames){
2054                 if(doAreaRestriction){
2055                     taxonSubselect = String.format(doAreaRestrictionSubSelect, "t") + doTaxonNameJoin +
2056                     " WHERE " + doAreaRestrictionWhere +
2057                     " AND " + doClassificationWhere +
2058                     " AND " + String.format(doSearchFieldWhere, "n");
2059                     synonymSubselect = String.format(doAreaRestrictionSubSelect, "s") + doSynonymNameJoin +
2060                     " WHERE " + doAreaRestrictionWhere +
2061                     " AND " + doClassificationWhere +
2062                     " AND " + String.format(doSearchFieldWhere, "sn");
2063                 } else {
2064                     taxonSubselect = String.format(doTaxonSubSelect, "t" )+ doTaxonNameJoin +
2065                     " WHERE " + doClassificationWhere +
2066                     " AND " + String.format(doSearchFieldWhere, "n");
2067                     synonymSubselect = String.format(doTaxonSubSelect, "s" ) + doSynonymNameJoin +
2068                     " WHERE " + doClassificationWhere +
2069                     " AND " + String.format(doSearchFieldWhere, "sn");
2070                 }
2071             }else{ //misappliedNames included
2072                 if(doAreaRestriction){
2073                     misappliedSelect = String.format(doAreaRestrictionMisappliedNameSubSelect, "t") + doTaxonNameJoin + doMisappliedNamesJoin +
2074                     " WHERE " + doAreaRestrictionWhere +
2075                     " AND " + String.format(doSearchFieldWhere, "n") +
2076                     " AND " + doClassificationForMisappliedNamesWhere +
2077                     " AND " + doRelationshipTypeComparison;
2078
2079                     taxonSubselect = String.format(doAreaRestrictionSubSelect, "t") + doTaxonNameJoin +
2080                     " WHERE " + doAreaRestrictionWhere +
2081                     " AND "+ String.format(doSearchFieldWhere, "n") + " AND "+ doClassificationWhere;
2082
2083                     synonymSubselect = String.format(doAreaRestrictionSubSelect, "s") + doSynonymNameJoin +
2084                     " WHERE " + doAreaRestrictionWhere +
2085                     " AND " + doClassificationWhere + " AND " +  String.format(doSearchFieldWhere, "sn");;
2086
2087                 } else {
2088                     misappliedSelect = String.format(doTaxonMisappliedNameSubSelect, "t" ) + doTaxonNameJoin + doMisappliedNamesJoin +
2089                     " WHERE " + String.format(doSearchFieldWhere, "n") +
2090                     " AND " + doClassificationForMisappliedNamesWhere +
2091                     " AND " + doRelationshipTypeComparison;
2092
2093                     taxonSubselect = String.format(doTaxonSubSelect, "t" ) + doTaxonNameJoin +
2094                     " WHERE " +  String.format(doSearchFieldWhere, "n") +
2095                     " AND "+ doClassificationWhere;
2096
2097                     synonymSubselect = String.format(doTaxonSubSelect, "s" ) + doSynonymNameJoin +
2098                     " WHERE " + doClassificationWhere +
2099                     " AND " +  String.format(doSearchFieldWhere, "sn");
2100
2101                 }
2102             }
2103         } else {
2104             if(doAreaRestriction){
2105                 misappliedSelect = String.format(doAreaRestrictionMisappliedNameSubSelect, "t") + doTaxonNameJoin + doMisappliedNamesJoin +
2106                 " WHERE " + doAreaRestrictionWhere +
2107                 " AND " + String.format(doSearchFieldWhere, "n")+
2108                 " AND " + doRelationshipTypeComparison;
2109
2110                 taxonSubselect = String.format(doAreaRestrictionSubSelect, "t") + doTaxonNameJoin +
2111                 " WHERE " + doAreaRestrictionWhere +
2112                 " AND " + String.format(doSearchFieldWhere, "n");
2113
2114                 synonymSubselect = String.format(doAreaRestrictionSubSelect, "s") + doSynonymNameJoin +
2115                 " WHERE " +   doAreaRestrictionWhere +
2116                 " AND " +  String.format(doSearchFieldWhere, "sn");
2117
2118
2119             } else {
2120                 misappliedSelect = String.format(doTaxonMisappliedNameSubSelect, "t" ) + doTaxonNameJoin + doMisappliedNamesJoin + " WHERE " +  String.format(doSearchFieldWhere, "n") + " AND " + doRelationshipTypeComparison;
2121                 taxonSubselect = String.format(doTaxonSubSelect, "t" ) + doTaxonNameJoin + " WHERE " +  String.format(doSearchFieldWhere, "n");
2122                 synonymSubselect = String.format(doTaxonSubSelect, "s" ) + doSynonymNameJoin + " WHERE " +  String.format(doSearchFieldWhere, "sn");
2123
2124             }
2125         }
2126         String[] result = {misappliedSelect, taxonSubselect, synonymSubselect};
2127
2128         return result;
2129     }
2130
2131         @Override
2132         public List<UuidAndTitleCache<IdentifiableEntity>> getTaxaByCommonNameForEditor(
2133                         String titleSearchStringSqlized, Classification classification,
2134                         MatchMode matchMode, Set namedAreas) {
2135             List<Object> resultArray = new ArrayList<Object>();
2136                 Query query = prepareTaxaByCommonName(titleSearchStringSqlized, classification, matchMode, namedAreas, null, null, false, true);
2137         if (query != null){
2138             resultArray = query.list();
2139             List<UuidAndTitleCache<IdentifiableEntity>> returnResult = new ArrayList<UuidAndTitleCache<IdentifiableEntity>>() ;
2140             Object[] result;
2141             for(int i = 0; i<resultArray.size();i++){
2142                 result = (Object[]) resultArray.get(i);
2143                 returnResult.add(new UuidAndTitleCache(Taxon.class, (UUID) result[0],(Integer)result[1], (String)result[2], new Boolean(result[4].toString())));
2144             }
2145             return returnResult;
2146         }
2147
2148                 return null;
2149         }
2150
2151
2152         /**
2153          * @param
2154          * @see eu.etaxonomy.cdm.persistence.dao.taxon.ITaxonDao#countByIdentifier(java.lang.Class, java.lang.String, eu.etaxonomy.cdm.model.common.DefinedTerm, eu.etaxonomy.cdm.model.taxon.TaxonNode, eu.etaxonomy.cdm.persistence.query.MatchMode)
2155          */
2156         @Override
2157         public <S extends TaxonBase> int countByIdentifier(Class<S> clazz,
2158                         String identifier, DefinedTerm identifierType, TaxonNode subtreeFilter, MatchMode matchmode) {
2159                 if (subtreeFilter == null){
2160                         return countByIdentifier(clazz, identifier, identifierType, matchmode);
2161                 }
2162
2163                 Class<?> clazzParam = clazz == null ? type : clazz;
2164                 checkNotInPriorView("IdentifiableDaoBase.countByIdentifier(T clazz, String identifier, DefinedTerm identifierType, TaxonNode subMatchMode matchmode)");
2165
2166                 boolean isTaxon = clazzParam == Taxon.class || clazzParam == TaxonBase.class;
2167                 boolean isSynonym = clazzParam == Synonym.class || clazzParam == TaxonBase.class;
2168
2169                 getSession().update(subtreeFilter);  //to avoid LIE when retrieving treeindex
2170                 String filterStr = "'" + subtreeFilter.treeIndex() + "%%'";
2171                 String accTreeJoin = isTaxon? " LEFT JOIN c.taxonNodes tn  " : "";
2172                 String synTreeJoin = isSynonym ? " LEFT JOIN c.synonymRelations sr LEFT  JOIN sr.relatedTo as acc LEFT JOIN acc.taxonNodes synTn  " : "";
2173                 String accWhere = isTaxon ?  "tn.treeIndex like " + filterStr : "(1=0)";
2174                 String synWhere = isSynonym  ?  "synTn.treeIndex like " + filterStr : "(1=0)";
2175
2176                 String queryString = "SELECT count(*)  FROM %s as c " +
2177                 " INNER JOIN c.identifiers as ids " +
2178                 accTreeJoin +
2179                 synTreeJoin +
2180                 " WHERE (1=1) " +
2181                         "  AND ( " + accWhere + " OR " + synWhere + ")";
2182                 queryString = String.format(queryString, clazzParam.getSimpleName());
2183
2184                 if (identifier != null){
2185                         if (matchmode == null || matchmode == MatchMode.EXACT){
2186                                 queryString += " AND ids.identifier = '"  + identifier + "'";
2187                         }else {
2188                                 queryString += " AND ids.identifier LIKE '" + matchmode.queryStringFrom(identifier)  + "'";
2189                         }
2190                 }
2191                 if (identifierType != null){
2192                 queryString += " AND ids.type = :type";
2193         }
2194
2195                 Query query = getSession().createQuery(queryString);
2196         if (identifierType != null){
2197                 query.setEntity("type", identifierType);
2198         }
2199
2200                 Long c = (Long)query.uniqueResult();
2201         return c.intValue();
2202         }
2203
2204         @Override
2205         public <S extends TaxonBase> List<Object[]> findByIdentifier(
2206                         Class<S> clazz, String identifier, DefinedTerm identifierType, TaxonNode subtreeFilter,
2207                         MatchMode matchmode, boolean includeEntity,
2208                         Integer pageSize, Integer pageNumber, List<String> propertyPaths) {
2209
2210                 checkNotInPriorView("IdentifiableDaoBase.findByIdentifier(T clazz, String identifier, DefinedTerm identifierType, MatchMode matchmode, Integer pageSize, Integer pageNumber, List<OrderHint> orderHints, List<String> propertyPaths)");
2211                 Class<?> clazzParam = clazz == null ? type : clazz;
2212
2213                 boolean isTaxon = clazzParam == Taxon.class || clazzParam == TaxonBase.class;
2214                 boolean isSynonym = clazzParam == Synonym.class || clazzParam == TaxonBase.class;
2215                 getSession().update(subtreeFilter);  //to avoid LIE when retrieving treeindex
2216                 String filterStr = "'" + subtreeFilter.treeIndex() + "%%'";
2217                 String accTreeJoin = isTaxon? " LEFT JOIN c.taxonNodes tn  " : "";
2218                 String synTreeJoin = isSynonym ? " LEFT JOIN c.synonymRelations sr LEFT  JOIN sr.relatedTo as acc LEFT JOIN acc.taxonNodes synTn  " : "";
2219                 String accWhere = isTaxon ?  "tn.treeIndex like " + filterStr : "(1=0)";
2220                 String synWhere = isSynonym  ?  "synTn.treeIndex like " + filterStr : "(1=0)";
2221
2222                 String queryString = "SELECT ids.type, ids.identifier, %s " +
2223                                 " FROM %s as c " +
2224                 " INNER JOIN c.identifiers as ids " +
2225                 accTreeJoin +
2226                                 synTreeJoin +
2227                 " WHERE (1=1) " +
2228                         " AND ( " + accWhere + " OR " + synWhere + ")";
2229                 queryString = String.format(queryString, (includeEntity ? "c":"c.uuid, c.titleCache") , clazzParam.getSimpleName());
2230
2231                 //Matchmode and identifier
2232                 if (identifier != null){
2233                         if (matchmode == null || matchmode == MatchMode.EXACT){
2234                                 queryString += " AND ids.identifier = '"  + identifier + "'";
2235                         }else {
2236                                 queryString += " AND ids.identifier LIKE '" + matchmode.queryStringFrom(identifier)  + "'";
2237                         }
2238                 }
2239         if (identifierType != null){
2240                 queryString += " AND ids.type = :type";
2241         }
2242         //order
2243         queryString +=" ORDER BY ids.type.uuid, ids.identifier, c.uuid ";
2244
2245                 Query query = getSession().createQuery(queryString);
2246
2247                 //parameters
2248                 if (identifierType != null){
2249                 query.setEntity("type", identifierType);
2250         }
2251
2252         //paging
2253         setPagingParameter(query, pageSize, pageNumber);
2254
2255         List<Object[]> results = query.list();
2256         //initialize
2257         if (includeEntity){
2258                 List<S> entities = new ArrayList<S>();
2259                 for (Object[] result : results){
2260                         entities.add((S)result[2]);
2261                 }
2262                 defaultBeanInitializer.initializeAll(entities, propertyPaths);
2263         }
2264         return results;
2265         }
2266
2267 }