org.hibernate.criterion.Disjunction Java Examples

public int countTargetInstances(final String aUsername, final ArrayList<String> aStates) {
	return (Integer) getHibernateTemplate().execute(
		new HibernateCallback() {
			public Object doInHibernate(Session session) {					
				Criteria query = session.createCriteria(TargetInstance.class);
				query.setProjection(Projections.rowCount());
									
				if (aStates != null && !aStates.isEmpty()) {
					Disjunction stateDisjunction = Restrictions.disjunction();
					for(String s: aStates) {
						stateDisjunction.add(Restrictions.eq("state", s));
					}
					query.add(stateDisjunction);
				}
				
				query.createCriteria("owner").add(Restrictions.eq("username", aUsername));
				
									
				Integer count = (Integer) query.uniqueResult();
                
                return count;
			}
		}
	);	
}
 

@Override
public List<SbiCrossNavigation> listNavigationsByDocumentAndParameters(Integer documentId, List<Integer> inputParameters, List<Integer> outputParameters,
		Session session) {
	// load cross navigation item
	Disjunction disj = Restrictions.disjunction();
	if (!inputParameters.isEmpty()) {
		disj.add(Restrictions.conjunction().add(Restrictions.eq("_par.fromType", 1)).add(Restrictions.in("_par.fromKeyId", inputParameters)));
		disj.add(Restrictions.in("_par.toKeyId", inputParameters));
	}
	if (!outputParameters.isEmpty()) {
		disj.add(Restrictions.conjunction().add(Restrictions.eq("_par.fromType", 0)).add(Restrictions.in("_par.fromKeyId", outputParameters)));
	}
	disj.add(Restrictions.conjunction().add(Restrictions.eq("_par.fromType", 2)).add(Restrictions.eq("_par.fromKeyId", documentId)));

	List ret = session.createCriteria(SbiCrossNavigation.class).createAlias("sbiCrossNavigationPars", "_par").add(disj).list();
	return ret;
}
 

public int countTasks(final User user, final List<RolePrivilege> privs) {
 	return (Integer) getHibernateTemplate().execute(new HibernateCallback() {
public Object doInHibernate(Session session) throws HibernateException, SQLException {
	
	Criteria query = session.createCriteria(Task.class);
	query.setProjection(Projections.rowCount());
             Disjunction dis = Restrictions.disjunction();
             
             for(RolePrivilege userPriv: privs) {
               dis.add(Restrictions.eq("privilege", userPriv.getPrivilege()));
             }
             dis.add(Restrictions.eq("assigneeOid",user.getOid()));

             query.add(dis);
             query.createCriteria("agency").add(Restrictions.eq("oid", user.getAgency().getOid()));
                             
             Integer count = (Integer) query.uniqueResult();
             
             return count;
}
 	});
 }
 

public Pagination getTasks(final User user, final List<RolePrivilege> privs, final int pageNum, final int pageSize) {
    return (Pagination) getHibernateTemplate().execute(new HibernateCallback() {
  
        public Object doInHibernate(Session aSession) throws HibernateException, SQLException {
            Criteria query = aSession.createCriteria(Task.class);
            
            Disjunction dis = Restrictions.disjunction();
            
            for(RolePrivilege userPriv: privs) {
              dis.add(Restrictions.eq("privilege", userPriv.getPrivilege()));
            }
            dis.add(Restrictions.eq("assigneeOid",user.getOid()));

            query.add(dis);
            query.createCriteria("agency").add(Restrictions.eq("oid", user.getAgency().getOid()));
            query.addOrder(Order.desc("sentDate"));
            
            Criteria cntQuery = aSession.createCriteria(Task.class);
            cntQuery.add(dis);
            cntQuery.createCriteria("agency").add(Restrictions.eq("oid", user.getAgency().getOid()));
            cntQuery.setProjection(Projections.rowCount());
            
            return new Pagination(cntQuery, query, pageNum, pageSize);
        }
    });
}
 

@Override
@Transactional
public void delShoppingCart(int[] cart) {
    DetachedCriteria criteria=DetachedCriteria.forClass(ShoppingCart.class);
    Disjunction dis = Restrictions.disjunction();
    for(int item : cart) {
        System.out.println(item);
        dis.add(Restrictions.eq("id", item));
    }
    criteria.add(dis);
    List<ShoppingCart> list = (List<ShoppingCart>) template.findByCriteria(criteria);
    template.deleteAll(list);
}
 

public Criterion build(PropertyFilter filter) {
	Object value = convertMatchValue(filter.getMatchValue(), filter.getFieldType());
	Criterion criterion = null;
	if (filter.hasMultiplePropertyNames()) {
		Disjunction disjunction = Restrictions.disjunction();
		for (String propertyName:filter.getPropertyNames()) {
			disjunction.add(build(propertyName,value));
		}
		criterion = disjunction;
	} else {
		criterion = build(filter.getSinglePropertyName(),value);
	}
	return criterion;
}
 

public int countActiveTIsForTarget(final Long targetOid) {
	return (Integer) getHibernateTemplate().execute(
		new HibernateCallback() {
			public Object doInHibernate(Session session) {					
				Criteria query = session.createCriteria(TargetInstance.class);
				query.setProjection(Projections.rowCount());
									
				Disjunction stateDisjunction = Restrictions.disjunction();
				
				stateDisjunction.add(Restrictions.eq("state", TargetInstance.STATE_SCHEDULED));
				stateDisjunction.add(Restrictions.eq("state", TargetInstance.STATE_QUEUED));
				stateDisjunction.add(Restrictions.eq("state", TargetInstance.STATE_RUNNING));
				stateDisjunction.add(Restrictions.eq("state", TargetInstance.STATE_PAUSED));
				stateDisjunction.add(Restrictions.eq("state", TargetInstance.STATE_STOPPING));

				query.add(stateDisjunction);
				
				//query.createAlias("target", "t");
				query.createCriteria("target").add(Restrictions.eq("oid", targetOid));

				Integer count = (Integer) query.uniqueResult();
                
                return count;
			}
		}
	);	
}
 

public Map<Long, List<ItemGradingData>> getItemScores(final Long itemId, List<AssessmentGradingData> scores, boolean loadItemGradingAttachment) {
    try {
        HashMap<Long, List<ItemGradingData>> map = new HashMap<>();

        HibernateCallback<List<ItemGradingData>> hcb = session -> {
            Criteria criteria = session.createCriteria(ItemGradingData.class);
            Disjunction disjunction = Expression.disjunction();

            /** make list from AssessmentGradingData ids */
            List<Long> gradingIdList = scores.stream()
                    .map(AssessmentGradingData::getAssessmentGradingId)
                    .collect(Collectors.toList());

            /** create or disjunctive expression for (in clauses) */
            List tempList;
            for (int i = 0; i < gradingIdList.size(); i += 50) {
                if (i + 50 > gradingIdList.size()) {
                    tempList = gradingIdList.subList(i, gradingIdList.size());
                    disjunction.add(Expression.in("assessmentGradingId", tempList));
                } else {
                    tempList = gradingIdList.subList(i, i + 50);
                    disjunction.add(Expression.in("assessmentGradingId", tempList));
                }
            }

            if (itemId.equals(Long.valueOf(0))) {
                criteria.add(disjunction);
                //criteria.add(Expression.isNotNull("submittedDate"));
            } else {

                /** create logical and between the pubCriterion and the disjunction criterion */
                //Criterion pubCriterion = Expression.eq("publishedItem.itemId", itemId);
                Criterion pubCriterion = Expression.eq("publishedItemId", itemId);
                criteria.add(Expression.and(pubCriterion, disjunction));
                //criteria.add(Expression.isNotNull("submittedDate"));
            }
            criteria.addOrder(Order.asc("agentId"));
            criteria.addOrder(Order.desc("submittedDate"));
            return criteria.list();
            //large list cause out of memory error (java heap space)
            //return criteria.setMaxResults(10000).list();
        };
        List<ItemGradingData> temp = getHibernateTemplate().execute(hcb);

        Map<Long, Set<ItemGradingAttachment>> attachmentMap = new HashMap<>();
        if (loadItemGradingAttachment) {
            attachmentMap = getItemGradingAttachmentMap(itemId);
        }
        for (ItemGradingData data : temp) {
            if (loadItemGradingAttachment) {
                if (attachmentMap.get(data.getItemGradingId()) != null) {
                    data.setItemGradingAttachmentSet(attachmentMap.get(data.getItemGradingId()));
                } else {
                    data.setItemGradingAttachmentSet(new HashSet<>());
                }
            }
            List<ItemGradingData> thisone = map.get(data.getPublishedItemId());
            if (thisone == null) {
                thisone = new ArrayList<>();
            }
            thisone.add(data);
            map.put(data.getPublishedItemId(), thisone);
        }
        return map;
    } catch (Exception e) {
        log.warn(e.getMessage(), e);
        return new HashMap<>();
    }
}
 

public List<MediaData> getMediaArray(Long publishedId, final Long publishedItemId, String which) {
    try {
        Map<Long, List<ItemGradingData>> itemScores = getItemScores(publishedId, publishedItemId, which);
        final List<ItemGradingData> list = itemScores.get(publishedItemId);
        log.debug("list size list.size() = " + list.size());

        HibernateCallback<List<MediaData>> hcb = session -> {
            Criteria criteria = session.createCriteria(MediaData.class);
            Disjunction disjunction = Expression.disjunction();

            /** make list from AssessmentGradingData ids */
            List<Long> itemGradingIdList = list.stream()
                    .map(ItemGradingData::getItemGradingId)
                    .collect(Collectors.toList());

            /** create or disjunctive expression for (in clauses) */
            List<Long> tempList;
            for (int i = 0; i < itemGradingIdList.size(); i += 50) {
                if (i + 50 > itemGradingIdList.size()) {
                    tempList = itemGradingIdList.subList(i, itemGradingIdList.size());
                    disjunction.add(Expression.in("itemGradingData.itemGradingId", tempList));
                } else {
                    tempList = itemGradingIdList.subList(i, i + 50);
                    disjunction.add(Expression.in("itemGradingData.itemGradingId", tempList));
                }
            }
            criteria.add(disjunction);
            return criteria.list();
            //large list cause out of memory error (java heap space)
            //return criteria.setMaxResults(10000).list();
        };

        List<MediaData> a = new ArrayList<>();
        List<MediaData> hbmList = getHibernateTemplate().execute(hcb);
        for (MediaData mediaData : hbmList) {
            mediaData.setContentResource(getMediaContentResource(mediaData));
            a.add(mediaData);
        }
        return a;

    } catch (Exception e) {
        log.warn(e.getMessage(), e);
        return new ArrayList<>();
    }
}
 

public Set<PublishedItemData> getItemSet(final Long publishedAssessmentId, final Long sectionId) {

        final HibernateCallback<List<Long>> hcb = session -> {
            Query q = session.createQuery(
                    "select distinct p.itemId " +
                            "from PublishedItemData p, AssessmentGradingData a, ItemGradingData i " +
                            "where a.publishedAssessmentId = :id and a.forGrade = :forgrade and p.section.id = :sectionid " +
                            "and i.assessmentGradingId = a.assessmentGradingId " +
                            "and p.itemId = i.publishedItemId and a.status > :status ");
            q.setLong("id", publishedAssessmentId);
            q.setBoolean("forgrade", true);
            q.setLong("sectionid", sectionId);
            q.setInteger("status", AssessmentGradingData.REMOVED);
            return q.list();
        };
        List<Long> itemIds = getHibernateTemplate().execute(hcb);

        if (itemIds.isEmpty()) {
            return new HashSet<>();
        }

        final HibernateCallback<List<PublishedItemData>> hcb2 = session -> {

            final Criteria criteria = session.createCriteria(PublishedItemData.class);
            if (itemIds.size() > 1000) {
                final Set<Long> ids = new HashSet<>();
                Disjunction disjunction = Restrictions.disjunction();

                for (Long id : itemIds) {
                    if (ids.size() < 1000) {
                        ids.add(id);
                    } else {
                        criteria.add(disjunction.add(Restrictions.in("itemId", ids)));
                        ids.clear();
                    }
                }
            } else {
                criteria.add(Restrictions.in("itemId", itemIds));
            }
            return criteria.list();
        };

        List<PublishedItemData> publishedItems = getHibernateTemplate().execute(hcb2);

        return new HashSet<>(publishedItems);
    }
 

public Pagination search(final SiteCriteria aCriteria, final int page, final int pageSize) {
	return (Pagination) getHibernateTemplate().execute(
			new HibernateCallback() {
				public Object doInHibernate(Session session) {
					
					Criteria query = session.createCriteria(Site.class);
					Criteria cntQuery = session.createCriteria(Site.class);
					
					if(aCriteria != null && aCriteria.getTitle() != null && !"".equals(aCriteria.getTitle().trim())) {
						query.add(Restrictions.ilike("title", aCriteria.getTitle().trim(), MatchMode.START));
						cntQuery.add(Restrictions.ilike("title", aCriteria.getTitle().trim(), MatchMode.START));
					}
					
					if(aCriteria != null && aCriteria.getOrderNo() != null && !"".equals(aCriteria.getOrderNo().trim())) {
						query.add(Restrictions.ilike("libraryOrderNo", aCriteria.getOrderNo().trim(), MatchMode.START));
						cntQuery.add(Restrictions.ilike("libraryOrderNo", aCriteria.getOrderNo().trim(), MatchMode.START));
					}
					
					if(aCriteria != null && aCriteria.getAgentName() != null && !"".equals(aCriteria.getAgentName().trim())) {
						query.createCriteria("authorisingAgents").add(Restrictions.ilike("name", aCriteria.getAgentName().trim(), MatchMode.START));
						cntQuery.createCriteria("authorisingAgents").add(Restrictions.ilike("name", aCriteria.getAgentName().trim(), MatchMode.START));
					}
					
					if(aCriteria != null) {
						if (!aCriteria.isShowDisabled()) {
							query.add(Restrictions.eq("active", true));
							cntQuery.add(Restrictions.eq("active", true));
						}							
					}

					// Owning Agency criteria.
					if(aCriteria != null && aCriteria.getAgency() != null && !"".equals(aCriteria.getAgency().trim())) {
						query.createCriteria("owningAgency").add(Restrictions.ilike("name", aCriteria.getAgency().trim(), MatchMode.START));
						cntQuery.createCriteria("owningAgency").add(Restrictions.ilike("name", aCriteria.getAgency().trim(), MatchMode.START));
					}
					
					if(aCriteria != null && aCriteria.getSearchOid() != null) {
						query.add(Restrictions.eq("oid", aCriteria.getSearchOid()));
						cntQuery.add(Restrictions.eq("oid", aCriteria.getSearchOid()));
					}

					// URL Pattern's URL pattern criteria.
					if(aCriteria != null && aCriteria.getUrlPattern() != null && !"".equals(aCriteria.getUrlPattern().trim())) {
						query.createCriteria("urlPatterns").add(Restrictions.ilike("pattern", aCriteria.getUrlPattern().trim(), MatchMode.START));
						cntQuery.createCriteria("urlPatterns").add(Restrictions.ilike("pattern", aCriteria.getUrlPattern().trim(), MatchMode.START));
					}
					
					Criteria permissionsCriteria = null;
					Criteria cntPermissionsCriteria = null;

					// Permission's File Reference criteria.
					if(aCriteria != null && aCriteria.getPermsFileRef() != null && !"".equals(aCriteria.getPermsFileRef().trim())) {
						if(permissionsCriteria == null) {
							permissionsCriteria = query.createCriteria("permissions");	
							cntPermissionsCriteria = cntQuery.createCriteria("permissions");	
						}
						permissionsCriteria.add(Restrictions.ilike("fileReference", aCriteria.getPermsFileRef().trim(), MatchMode.START));
						cntPermissionsCriteria.add(Restrictions.ilike("fileReference", aCriteria.getPermsFileRef().trim(), MatchMode.START));
					}

					// Permission's status flags criteria.
					Set<Integer> states = null;
					if(aCriteria != null) { states = aCriteria.getStates(); }
					if(aCriteria != null && states != null && states.size() > 0) {
						Disjunction stateDisjunction = Restrictions.disjunction();
						for(Integer i: states) {
							stateDisjunction.add(Restrictions.eq("status", i));
						}
						if(permissionsCriteria == null) {
							permissionsCriteria = query.createCriteria("permissions");	
							cntPermissionsCriteria = cntQuery.createCriteria("permissions");	
						}
						permissionsCriteria.add(stateDisjunction);
						cntPermissionsCriteria.add(stateDisjunction);
					}

					
					if( aCriteria.getSortorder() == null || 
						aCriteria.getSortorder().equals(SiteSearchCommand.SORT_NAME_ASC)) {
						query.addOrder(Order.asc("title"));
					} else if (aCriteria.getSortorder().equals(SiteSearchCommand.SORT_NAME_DESC)) {
						query.addOrder(Order.desc("title"));
					} else if (aCriteria.getSortorder().equals(SiteSearchCommand.SORT_DATE_ASC)) {
						query.addOrder(Order.asc("creationDate"));
					} else if (aCriteria.getSortorder().equals(SiteSearchCommand.SORT_DATE_DESC)) {
						query.addOrder(Order.desc("creationDate"));
					}
					
					query.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

					cntQuery.setProjection(Projections.rowCount());
					cntQuery.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
					
					return new Pagination(cntQuery, query, page, pageSize);
				}
			}
		);	
}
 

/**
 * Creates a detached criteria with sharing restrictions relative to the given
 * user and access string.
 *
 * @param user   the user.
 * @param access the access string.
 * @return a DetachedCriteria.
 */
private DetachedCriteria getSharingDetachedCriteria( UserInfo user, String access )
{
    DetachedCriteria criteria = DetachedCriteria.forClass( getClazz(), "c" );

    preProcessDetachedCriteria( criteria );

    if ( !sharingEnabled( user ) || user == null )
    {
        return criteria;
    }

    Assert.notNull( user, "User argument can't be null." );

    Disjunction disjunction = Restrictions.disjunction();

    disjunction.add( Restrictions.like( "c.publicAccess", access ) );
    disjunction.add( Restrictions.isNull( "c.publicAccess" ) );
    disjunction.add( Restrictions.isNull( "c.user.id" ) );
    disjunction.add( Restrictions.eq( "c.user.id", user.getId() ) );

    DetachedCriteria userGroupDetachedCriteria = DetachedCriteria.forClass( getClazz(), "ugdc" );
    userGroupDetachedCriteria.createCriteria( "ugdc.userGroupAccesses", "uga" );
    userGroupDetachedCriteria.createCriteria( "uga.userGroup", "ug" );
    userGroupDetachedCriteria.createCriteria( "ug.members", "ugm" );

    userGroupDetachedCriteria.add( Restrictions.eqProperty( "ugdc.id", "c.id" ) );
    userGroupDetachedCriteria.add( Restrictions.eq( "ugm.id", user.getId() ) );
    userGroupDetachedCriteria.add( Restrictions.like( "uga.access", access ) );

    userGroupDetachedCriteria.setProjection( Property.forName( "uga.id" ) );

    disjunction.add( Subqueries.exists( userGroupDetachedCriteria ) );

    DetachedCriteria userDetachedCriteria = DetachedCriteria.forClass( getClazz(), "udc" );
    userDetachedCriteria.createCriteria( "udc.userAccesses", "ua" );
    userDetachedCriteria.createCriteria( "ua.user", "u" );

    userDetachedCriteria.add( Restrictions.eqProperty( "udc.id", "c.id" ) );
    userDetachedCriteria.add( Restrictions.eq( "u.id", user.getId() ) );
    userDetachedCriteria.add( Restrictions.like( "ua.access", access ) );

    userDetachedCriteria.setProjection( Property.forName( "ua.id" ) );

    disjunction.add( Subqueries.exists( userDetachedCriteria ) );

    criteria.add( disjunction );

    return criteria;
}
 

/**
 * Creates a detached criteria with data sharing restrictions relative to the
 * given user and access string.
 *
 * @param user the user.
 * @param access the access string.
 * @return a DetachedCriteria.
 */
private DetachedCriteria getDataSharingDetachedCriteria( UserInfo user, String access )
{
    DetachedCriteria criteria = DetachedCriteria.forClass( getClazz(), "c" );

    if ( user == null || !dataSharingEnabled( user ) )
    {
        return criteria;
    }

    Assert.notNull( user, "User argument can't be null." );

    Disjunction disjunction = Restrictions.disjunction();

    disjunction.add( Restrictions.like( "c.publicAccess", access ) );
    disjunction.add( Restrictions.isNull( "c.publicAccess" ) );

    DetachedCriteria userGroupDetachedCriteria = DetachedCriteria.forClass( getClazz(), "ugdc" );
    userGroupDetachedCriteria.createCriteria( "ugdc.userGroupAccesses", "uga" );
    userGroupDetachedCriteria.createCriteria( "uga.userGroup", "ug" );
    userGroupDetachedCriteria.createCriteria( "ug.members", "ugm" );

    userGroupDetachedCriteria.add( Restrictions.eqProperty( "ugdc.id", "c.id" ) );
    userGroupDetachedCriteria.add( Restrictions.eq( "ugm.id", user.getId() ) );
    userGroupDetachedCriteria.add( Restrictions.like( "uga.access", access ) );

    userGroupDetachedCriteria.setProjection( Property.forName( "uga.id" ) );

    disjunction.add( Subqueries.exists( userGroupDetachedCriteria ) );

    DetachedCriteria userDetachedCriteria = DetachedCriteria.forClass( getClazz(), "udc" );
    userDetachedCriteria.createCriteria( "udc.userAccesses", "ua" );
    userDetachedCriteria.createCriteria( "ua.user", "u" );

    userDetachedCriteria.add( Restrictions.eqProperty( "udc.id", "c.id" ) );
    userDetachedCriteria.add( Restrictions.eq( "u.id", user.getId() ) );
    userDetachedCriteria.add( Restrictions.like( "ua.access", access ) );

    userDetachedCriteria.setProjection( Property.forName( "ua.id" ) );

    disjunction.add( Subqueries.exists( userDetachedCriteria ) );

    criteria.add( disjunction );

    return criteria;
}
 

public Pagination search(final int pageNumber, final int pageSize, final Long searchOid, final String targetName, final Set<Integer> states, final String seed, final String username, final String agencyName, final String memberOf, final boolean nondisplayonly, final String sortorder, final String description) {
	return (Pagination) getHibernateTemplate().execute(
			new HibernateCallback() {
				public Object doInHibernate(Session session) {
					
					Criteria query = session.createCriteria(Target.class);
					Criteria cntQuery = session.createCriteria(Target.class);
					Criteria ownerCriteria = null;
					Criteria cntOwnerCriteria = null;
					
					if(targetName != null && !"".equals(targetName.trim())) {
						query.add(Restrictions.ilike("name", targetName, MatchMode.START));
						cntQuery.add(Restrictions.ilike("name", targetName, MatchMode.START));
					}
					
					if(description != null && !"".equals(description.trim())) {
						query.add(Restrictions.ilike("description", description, MatchMode.ANYWHERE));
						cntQuery.add(Restrictions.ilike("description", description, MatchMode.ANYWHERE));
					}
					
					if(states != null && states.size() > 0) {
						Disjunction stateDisjunction = Restrictions.disjunction();
						for(Integer i: states) {
							stateDisjunction.add(Restrictions.eq("state", i));
						}
						query.add(stateDisjunction);
						cntQuery.add(stateDisjunction);
					}
					
					if(seed != null && !"".equals(seed.trim())) {
						query.createCriteria("seeds").add(Restrictions.like("seed", seed, MatchMode.START));
						cntQuery.createCriteria("seeds").add(Restrictions.like("seed", seed, MatchMode.START));
					}
					
					if(!Utils.isEmpty(username)) {
						if(ownerCriteria == null) { 
							ownerCriteria = query.createCriteria("owner"); 
							cntOwnerCriteria = cntQuery.createCriteria("owner");
						}
						ownerCriteria.add(Restrictions.eq("username", username));
						cntOwnerCriteria.add(Restrictions.eq("username", username));
					}
					
					// Parents criteria.
					if(!Utils.isEmpty(memberOf)) {
						query.createCriteria("parents").createCriteria("parent").add(Restrictions.ilike("name", memberOf, MatchMode.START));
						cntQuery.createCriteria("parents").createCriteria("parent").add(Restrictions.ilike("name", memberOf, MatchMode.START));
					}
					
					if(!Utils.isEmpty(agencyName)) {
						if(ownerCriteria == null) { 
							ownerCriteria = query.createCriteria("owner"); 
							cntOwnerCriteria = cntQuery.createCriteria("owner"); 
						}
						ownerCriteria.createCriteria("agency").add(Restrictions.eq("name", agencyName));
						cntOwnerCriteria.createCriteria("agency").add(Restrictions.eq("name", agencyName));
					}
					
					if(searchOid != null) {
						query.add(Restrictions.eq("oid", searchOid));
						cntQuery.add(Restrictions.eq("oid", searchOid));
					}
					
					if (nondisplayonly) {
						query.add(Restrictions.eq("displayTarget", false));
						cntQuery.add(Restrictions.eq("displayTarget", false));
					}
					
					if(sortorder == null || sortorder.equals(TargetSearchCommand.SORT_NAME_ASC)) {
						query.addOrder(Order.asc("name"));
					} else if (sortorder.equals(TargetSearchCommand.SORT_NAME_DESC)) {
						query.addOrder(Order.desc("name"));
					} else if (sortorder.equals(TargetSearchCommand.SORT_DATE_ASC)) {
						query.addOrder(Order.asc("creationDate"));
					} else if (sortorder.equals(TargetSearchCommand.SORT_DATE_DESC)) {
						query.addOrder(Order.desc("creationDate"));
					}
					cntQuery.setProjection(Projections.rowCount());
					
					return new Pagination(cntQuery, query, pageNumber, pageSize);
				}
			}
		);	
}
 

@Override
public List<SbiDataSet> loadPaginatedSearchSbiDataSet(String search, Integer page, Integer item_per_page, IEngUserProfile finalUserProfile,
		Boolean seeTechnical, Integer[] ids, boolean spatialOnly) {
	Session session = null;
	List<SbiDataSet> list = null;

	try {
		session = getSession();
		Criteria c = session.createCriteria(SbiDataSet.class);
		c.addOrder(Order.asc("label"));

		if (page != null && item_per_page != null) {
			c.setFirstResult((page - 1) * item_per_page);
			c.setMaxResults(item_per_page);
		}

		c.add(Restrictions.like("label", search == null ? "" : search, MatchMode.ANYWHERE).ignoreCase());
		c.add(Restrictions.eq("active", true));

		if (ids != null && ids.length > 0) {
			c.add(Restrictions.in("id.dsId", ids));
		}

		if (spatialOnly) {
			c.add(Restrictions.like("dsMetadata", IFieldMetaData.FieldType.SPATIAL_ATTRIBUTE.toString(), MatchMode.ANYWHERE));
		}

		if (finalUserProfile != null) {

			logger.debug("For final user take only owned, enterprise and shared");

			SbiDomains scopeUserDomain = DAOFactory.getDomainDAO().loadSbiDomainByCodeAndValue("DS_SCOPE", SpagoBIConstants.DS_SCOPE_USER);
			SbiDomains scopeEnterpriseDomain = DAOFactory.getDomainDAO().loadSbiDomainByCodeAndValue("DS_SCOPE", SpagoBIConstants.DS_SCOPE_ENTERPRISE);
			SbiDomains scopeTechnicalDomain = DAOFactory.getDomainDAO().loadSbiDomainByCodeAndValue("DS_SCOPE", SpagoBIConstants.DS_SCOPE_TECHNICAL);

			Disjunction or = Restrictions.disjunction();

			// OWNER OR

			// take owned datasets
			or.add(Restrictions.eq("owner", ((UserProfile) finalUserProfile).getUserId().toString()));

			// get categories
			Set<Domain> categoryList = UserUtilities.getDataSetCategoriesByUser(finalUserProfile);

			if (categoryList != null) {
				if (categoryList.size() > 0) {
					SbiDomains[] categoryArray = new SbiDomains[categoryList.size()];
					int i = 0;
					for (Iterator iterator = categoryList.iterator(); iterator.hasNext();) {
						Domain domain = (Domain) iterator.next();
						String domainCd = domain.getDomainCode();
						String valueCd = domain.getValueCd();
						SbiDomains sbiDomain = DAOFactory.getDomainDAO().loadSbiDomainByCodeAndValue(domainCd, valueCd);
						categoryArray[i] = sbiDomain;
						i++;
					}
					// (IN CATEGORY AND (SCOPE=USER OR SCOPE=ENTERPRISE)) OR SCOPE=TECHNICAL
					Conjunction andCategories = Restrictions.conjunction();
					andCategories.add(Restrictions.in("category", categoryArray));

					Disjunction orScope = Restrictions.disjunction();
					orScope.add(Restrictions.eq("scope", scopeUserDomain));
					orScope.add(Restrictions.eq("scope", scopeEnterpriseDomain));

					andCategories.add(orScope);

					if (seeTechnical != null && seeTechnical) {
						Disjunction orTechnical = Restrictions.disjunction();
						orTechnical.add(andCategories);
						orTechnical.add(Restrictions.eq("scope", scopeTechnicalDomain));
						or.add(orTechnical);
					} else {
						or.add(andCategories);
					}

				}
			} else {
				// if no categoryList take also all USER and ENTERPRISE dataset
				// SCOPE=USER OR SCOPE=ENTERPRISE)
				or.add(Restrictions.eq("scope", scopeUserDomain));
				or.add(Restrictions.eq("scope", scopeEnterpriseDomain));
			}

			c.add(or);
		}

		list = c.list();
		initialize(list);

	} catch (Exception e) {
		throw new SpagoBIDAOException("An unexpected error occured while loading datasets", e);
	} finally {
		if (session != null && session.isOpen()) {
			session.close();
		}
		logger.debug("OUT");
	}
	return list;
}
 

@Override
public List<BIObject> loadAllBIObjectsBySearchKey(String searchKey, String attributes) throws EMFUserError {
	logger.debug("IN");
	Session aSession = null;
	Transaction tx = null;
	List result = new ArrayList();
	try {
		aSession = getSession();
		tx = aSession.beginTransaction();
		Criteria hibQuery = aSession.createCriteria(SbiObjects.class);

		Criterion labelCriterion = EscapedLikeRestrictions.ilikeEscaped("label", searchKey, MatchMode.ANYWHERE);
		Criterion nameCriterion = EscapedLikeRestrictions.ilikeEscaped("name", searchKey, MatchMode.ANYWHERE);
		Criterion descrCriterion = EscapedLikeRestrictions.ilikeEscaped("descr", searchKey, MatchMode.ANYWHERE);

		if ("LABEL".equalsIgnoreCase(attributes)) {
			hibQuery.add(labelCriterion);
		} else if ("NAME".equalsIgnoreCase(attributes)) {
			hibQuery.add(nameCriterion);
		} else if ("DESCRIPTION".equalsIgnoreCase(attributes)) {
			hibQuery.add(descrCriterion);
		} else {
			Disjunction disjunction = Restrictions.disjunction();
			disjunction.add(labelCriterion);
			disjunction.add(nameCriterion);
			disjunction.add(descrCriterion);
			hibQuery.add(disjunction);
		}

		List hibList = hibQuery.list();
		Iterator it = hibList.iterator();
		Set<Integer> resultIds = new HashSet<>();
		while (it.hasNext()) {
			SbiObjects next = (SbiObjects) it.next();
			Integer id = next.getBiobjId();
			if (!resultIds.contains(id)) {
				resultIds.add(id);
				result.add(toBIObject(next, aSession));
			}
		}

		tx.commit();
	} catch (HibernateException he) {
		logger.error("HibernateException", he);
		if (tx != null) {
			tx.rollback();
		}
		throw new EMFUserError(EMFErrorSeverity.ERROR, 100);
	} finally {
		if (aSession != null && aSession.isOpen()) {
			aSession.close();
		}
		logger.debug("OUT");
	}
	return result;
}
 

public Map<Long, List<ItemGradingData>> getItemScores(final Long itemId, List<AssessmentGradingData> scores, boolean loadItemGradingAttachment) {
    try {
        HashMap<Long, List<ItemGradingData>> map = new HashMap<>();

        HibernateCallback<List<ItemGradingData>> hcb = session -> {
            Criteria criteria = session.createCriteria(ItemGradingData.class);
            Disjunction disjunction = Expression.disjunction();

            /** make list from AssessmentGradingData ids */
            List<Long> gradingIdList = scores.stream()
                    .map(AssessmentGradingData::getAssessmentGradingId)
                    .collect(Collectors.toList());

            /** create or disjunctive expression for (in clauses) */
            List tempList;
            for (int i = 0; i < gradingIdList.size(); i += 50) {
                if (i + 50 > gradingIdList.size()) {
                    tempList = gradingIdList.subList(i, gradingIdList.size());
                    disjunction.add(Expression.in("assessmentGradingId", tempList));
                } else {
                    tempList = gradingIdList.subList(i, i + 50);
                    disjunction.add(Expression.in("assessmentGradingId", tempList));
                }
            }

            if (itemId.equals(Long.valueOf(0))) {
                criteria.add(disjunction);
                //criteria.add(Expression.isNotNull("submittedDate"));
            } else {

                /** create logical and between the pubCriterion and the disjunction criterion */
                //Criterion pubCriterion = Expression.eq("publishedItem.itemId", itemId);
                Criterion pubCriterion = Expression.eq("publishedItemId", itemId);
                criteria.add(Expression.and(pubCriterion, disjunction));
                //criteria.add(Expression.isNotNull("submittedDate"));
            }
            criteria.addOrder(Order.asc("agentId"));
            criteria.addOrder(Order.desc("submittedDate"));
            return criteria.list();
            //large list cause out of memory error (java heap space)
            //return criteria.setMaxResults(10000).list();
        };
        List<ItemGradingData> temp = getHibernateTemplate().execute(hcb);

        Map<Long, Set<ItemGradingAttachment>> attachmentMap = new HashMap<>();
        if (loadItemGradingAttachment) {
            attachmentMap = getItemGradingAttachmentMap(itemId);
        }
        for (ItemGradingData data : temp) {
            if (loadItemGradingAttachment) {
                if (attachmentMap.get(data.getItemGradingId()) != null) {
                    data.setItemGradingAttachmentSet(attachmentMap.get(data.getItemGradingId()));
                } else {
                    data.setItemGradingAttachmentSet(new HashSet<>());
                }
            }
            List<ItemGradingData> thisone = map.get(data.getPublishedItemId());
            if (thisone == null) {
                thisone = new ArrayList<>();
            }
            thisone.add(data);
            map.put(data.getPublishedItemId(), thisone);
        }
        return map;
    } catch (Exception e) {
        log.warn(e.getMessage(), e);
        return new HashMap<>();
    }
}
 

public List<MediaData> getMediaArray(Long publishedId, final Long publishedItemId, String which) {
    try {
        Map<Long, List<ItemGradingData>> itemScores = getItemScores(publishedId, publishedItemId, which);
        final List<ItemGradingData> list = itemScores.get(publishedItemId);
        log.debug("list size list.size() = " + list.size());

        HibernateCallback<List<MediaData>> hcb = session -> {
            Criteria criteria = session.createCriteria(MediaData.class);
            Disjunction disjunction = Expression.disjunction();

            /** make list from AssessmentGradingData ids */
            List<Long> itemGradingIdList = list.stream()
                    .map(ItemGradingData::getItemGradingId)
                    .collect(Collectors.toList());

            /** create or disjunctive expression for (in clauses) */
            List<Long> tempList;
            for (int i = 0; i < itemGradingIdList.size(); i += 50) {
                if (i + 50 > itemGradingIdList.size()) {
                    tempList = itemGradingIdList.subList(i, itemGradingIdList.size());
                    disjunction.add(Expression.in("itemGradingData.itemGradingId", tempList));
                } else {
                    tempList = itemGradingIdList.subList(i, i + 50);
                    disjunction.add(Expression.in("itemGradingData.itemGradingId", tempList));
                }
            }
            criteria.add(disjunction);
            return criteria.list();
            //large list cause out of memory error (java heap space)
            //return criteria.setMaxResults(10000).list();
        };

        List<MediaData> a = new ArrayList<>();
        List<MediaData> hbmList = getHibernateTemplate().execute(hcb);
        for (MediaData mediaData : hbmList) {
            mediaData.setContentResource(getMediaContentResource(mediaData));
            a.add(mediaData);
        }
        return a;

    } catch (Exception e) {
        log.warn(e.getMessage(), e);
        return new ArrayList<>();
    }
}
 

public Set<PublishedItemData> getItemSet(final Long publishedAssessmentId, final Long sectionId) {

        final HibernateCallback<List<Long>> hcb = session -> {
            Query q = session.createQuery(
                    "select distinct p.itemId " +
                            "from PublishedItemData p, AssessmentGradingData a, ItemGradingData i " +
                            "where a.publishedAssessmentId = :id and a.forGrade = :forgrade and p.section.id = :sectionid " +
                            "and i.assessmentGradingId = a.assessmentGradingId " +
                            "and p.itemId = i.publishedItemId and a.status > :status ");
            q.setLong("id", publishedAssessmentId);
            q.setBoolean("forgrade", true);
            q.setLong("sectionid", sectionId);
            q.setInteger("status", AssessmentGradingData.REMOVED);
            return q.list();
        };
        List<Long> itemIds = getHibernateTemplate().execute(hcb);

        if (itemIds.isEmpty()) {
            return new HashSet<>();
        }

        final HibernateCallback<List<PublishedItemData>> hcb2 = session -> {

            final Criteria criteria = session.createCriteria(PublishedItemData.class);
            if (itemIds.size() > 1000) {
                final Set<Long> ids = new HashSet<>();
                Disjunction disjunction = Restrictions.disjunction();

                for (Long id : itemIds) {
                    if (ids.size() < 1000) {
                        ids.add(id);
                    } else {
                        criteria.add(disjunction.add(Restrictions.in("itemId", ids)));
                        ids.clear();
                    }
                }
            } else {
                criteria.add(Restrictions.in("itemId", itemIds));
            }
            return criteria.list();
        };

        List<PublishedItemData> publishedItems = getHibernateTemplate().execute(hcb2);

        return new HashSet<>(publishedItems);
    }
 

/**
 * Find a {@link SUSEProduct} given by name, version, release and arch.
 * @param name name
 * @param version version or null
 * @param release release or null
 * @param arch arch or null
 * @param imprecise if true, allow returning products with NULL name, version or
 * release even if the corresponding parameters are not null
 * @return product or null if it is not found
 */
@SuppressWarnings("unchecked")
public static SUSEProduct findSUSEProduct(String name, String version, String release,
        String arch, boolean imprecise) {

    Criteria c = getSession().createCriteria(SUSEProduct.class);
    c.add(Restrictions.eq("name", name.toLowerCase()));

    Disjunction versionCriterion = Restrictions.disjunction();
    if (imprecise || version == null) {
        versionCriterion.add(Restrictions.isNull("version"));
    }
    if (version != null) {
        versionCriterion.add(Restrictions.eq("version", version.toLowerCase()));
    }
    c.add(versionCriterion);

    Disjunction releaseCriterion = Restrictions.disjunction();
    if (imprecise || release == null) {
        releaseCriterion.add(Restrictions.isNull("release"));
    }
    if (release != null) {
        releaseCriterion.add(Restrictions.eq("release", release.toLowerCase()));
    }
    c.add(releaseCriterion);

    Disjunction archCriterion = Restrictions.disjunction();
    if (imprecise || arch == null) {
        archCriterion.add(Restrictions.isNull("arch"));
    }
    if (arch != null) {
        archCriterion.add(Restrictions.eq("arch",
                PackageFactory.lookupPackageArchByLabel(arch)));
    }
    c.add(archCriterion);

    c.addOrder(Order.asc("name")).addOrder(Order.asc("version"))
            .addOrder(Order.asc("release")).addOrder(Order.asc("arch"));

    List<SUSEProduct> result = c.list();
    return result.isEmpty() ? null : result.get(0);
}
 

/**
 * Queries accounts by the properties of themselves in the given range.
 * 
 * @param banned null for not query
 * @param twitchToken null for not query, empty for no token
 * @param range the account range
 * @return a set of id
 */
private Collection<String> queryAccountsByPropertiesInRange(Boolean banned, String twitchToken, Set<String> range) {
	Conjunction propertiesConjunction = buildAccountsPropertiesConjunction(banned, twitchToken);
	Disjunction accountsDisjunction = disjunction();
	range.forEach(id -> accountsDisjunction.add(eq("id", id)));
	return queryAccountsByCriterion(conjunction(propertiesConjunction, accountsDisjunction));
}