Java Code Examples for javax.persistence.Query#setParameter()

/**
 * 根据模板目录名称和引用代码查询版块
 * @param dirName 模板目录
 * @param referenceCode 模块引用代码
 * @return
 */
@Transactional(readOnly=true,propagation=Propagation.NOT_SUPPORTED)
public Forum findForum(String dirName,String referenceCode){
	Query query = em.createQuery("select o from Forum o where o.dirName=?1 and o.referenceCode=?2");
	
	//给SQL语句设置参数
	query.setParameter(1, dirName);
	query.setParameter(2, referenceCode);
	List<Forum> forumList = query.getResultList();
	if(forumList != null && forumList.size() >0){
		for(Forum forum : forumList){
			return forum;
		}
	}
	return null;
}
 

@Override
public Set<String> removeDynMemberships(final User user) {
    List<Group> dynGroups = userDAO.findDynGroups(user.getKey());

    Query delete = entityManager().createNativeQuery("DELETE FROM " + UDYNMEMB_TABLE + " WHERE any_id=?");
    delete.setParameter(1, user.getKey());
    delete.executeUpdate();

    Set<String> before = new HashSet<>();
    dynGroups.forEach(group -> {
        before.add(group.getKey());

        publisher.publishEvent(new AnyCreatedUpdatedEvent<>(this, group, AuthContextUtils.getDomain()));
    });

    return before;
}
 

/**
 * 根据模板目录名称和布局类型和引用代码查询布局
 * @param dirName 模板目录
 * @param type 布局类型
 * @param referenceCode 模块引用代码
 * @return
 */
@Transactional(readOnly=true,propagation=Propagation.NOT_SUPPORTED)
public Layout findLayoutByReferenceCode(String dirName,Integer type,String referenceCode){
	Query query = em.createQuery("select o from Layout o where o.dirName=?1 and o.type=?2 and o.referenceCode=?3");
	
	//给SQL语句设置参数
	query.setParameter(1, dirName);
	query.setParameter(2, type);
	query.setParameter(3, referenceCode);
	List<Layout> layoutList = query.getResultList();
	if(layoutList != null && layoutList.size() >0){
		for(Layout layout : layoutList){
			return layout;
		}
	}
	return null;
}
 

@Override
public Collection<ProcessResult> retrieveProcessResultListByWebResourceAndTest(WebResource webResource, Test test) {
    Query query = entityManager.createQuery(
            "SELECT distinct(pr) FROM "
            + getEntityClass().getName() + " pr"
            + " LEFT JOIN FETCH pr.remarkSet pk"
            + " LEFT JOIN FETCH pk.elementSet el"
            + " JOIN pr.subject r"
            + " JOIN pr.test t"
            + " WHERE r=:webResource"
            + " AND t=:test");
    query.setParameter("webResource", webResource);
    query.setParameter("test", test);
    try {
        return query.getResultList();
    } catch (NoResultException e) {
        return null;
    }
}
 

/**
 * 根据规格组查询缩略图
 * @param specificationGroup 规格组
 * @return
 */
@Transactional(readOnly=true,propagation=Propagation.NOT_SUPPORTED)
public Thumbnail findThumbnailBySpecificationGroup(String specificationGroup){
	Query query =  em.createQuery("select o from Thumbnail o where o.specificationGroup=?1");
	query.setParameter(1, specificationGroup);
	List<Thumbnail> thumbnailList = query.getResultList();
	if(thumbnailList != null && thumbnailList.size() >0){
		for(Thumbnail thumbnail : thumbnailList){
			return thumbnail;
		}
	}
	return null;
}
 

@Override
public Parameter findParameter(ParameterElement parameterElement, String parameterValue) {
    Query query = entityManager.createQuery("SELECT p FROM "
            + getEntityClass().getName() + " p"
            + " WHERE p.parameterElement = :parameterElement"
            + " AND p.parameterValue = :parameterValue");
    query.setParameter("parameterElement", parameterElement);
    query.setParameter("parameterValue", parameterValue);
    try {
        return (Parameter)(query.getSingleResult());
    } catch (NoResultException nre) {
        return null;
    }
}
 

@Transactional
private List<ImageCacheVO> getImageCacheToDelete() {
    String sql = "select i from ImageCacheVO i where i.imageUuid = NULL and i.state = :state";
    TypedQuery<ImageCacheVO> q = dbf.getEntityManager().createQuery(sql, ImageCacheVO.class);
    q.setLockMode(LockModeType.PESSIMISTIC_WRITE);
    q.setParameter("state", ImageCacheState.ready);
    List<ImageCacheVO> ret = q.getResultList();
    
    // if ImageCacheVO in deleting state and it has been stayed for 1 day
    // that means zstack that issued garbage collection exited before removing this entry from database
    // we garbage this entry again here
    sql = "select i from ImageCacheVO i where i.imageUuid = NULL and i.state = :state and CURRENT_TIMESTAMP > DATE_ADD(i.lastOpDate, INTERVAL 1 DAY)";
    Query q1 = dbf.getEntityManager().createNativeQuery(sql, ImageCacheVO.class);
    q1.setLockMode(LockModeType.PESSIMISTIC_WRITE);
    q1.setParameter("state", ImageCacheState.deleting);
    ret.addAll(q1.getResultList());
    if (ret.isEmpty()) {
        return ret;
    }
    
    List<Long> ids = new ArrayList<Long>(ret.size());
    for (ImageCacheVO i : ret) {
        ids.add(i.getId());
    }
    sql = "update ImageCacheVO i set i.state = :state where i.id in (:ids)";
    TypedQuery<ImageCacheVO> q2 = dbf.getEntityManager().createQuery(sql, ImageCacheVO.class);
    q2.setParameter("state", ImageCacheState.deleting);
    q2.setParameter("ids", ids);
    q2.executeUpdate();
    return ret;
}
 

public OwnerRegistrationControlEntry[] findByUserIdAndThatIsEditable(String userId) {
    Query query = entityManager.createQuery("select orce from OwnerRegistrationControlEntryEntity orce, "
            + "DomainRoleEntryEntity dre, in(dre.domains) dds where orce.userId = :userId "
            + "and dre.userId = :userId and dre.role = :role and orce.domain = dds",
                                            OwnerRegistrationControlEntryEntity.class);
    query.setParameter("userId", userId);
    query.setParameter("role", Role.OWNER);
    return executeAndCovert(query);
}
 

@Override
public boolean checkGroupAppResourceFileName(String groupName, String fileName) {
    final Query q = entityManager.createNamedQuery(JpaGroupAppConfigTemplate.GET_GROUP_APP_TEMPLATE_RESOURCE_NAME);
    q.setParameter(JpaGroupAppConfigTemplate.QUERY_PARAM_GRP_NAME, groupName);
    q.setParameter(JpaGroupAppConfigTemplate.QUERY_PARAM_TEMPLATE_NAME, fileName);
    final List<String> result = q.getResultList();
    if (result != null && result.size() == 1) {
        return true;
    }
    return false;
}
 

@Override
public ParameterElement findParameterElementFromCode(String parameterElementCode) {
    Query query = entityManager.createQuery("SELECT pe FROM "
            + getEntityClass().getName() + " pe"
            + " WHERE pe.parameterElementCode = :parameterElementCode");
    query.setParameter("parameterElementCode", parameterElementCode);
    try {
        return (ParameterElement) query.getSingleResult();
    } catch (NoResultException nre) {
        return null;
    } catch (NonUniqueResultException nure) {
        return null;
    }
}
 

public List<CloneFile> getFileVersions(Long id) {
    String queryStr = "select f from CloneFile f where "
            + "      f.id = :id ";

    Query query = config.getDatabase().getEntityManager().createQuery(queryStr, CloneFile.class);
    query.setHint("javax.persistence.cache.storeMode", "REFRESH");
    query.setHint("eclipselink.cache-usage", "DoNotCheckCache");        
    
    query.setParameter("id", id);

    return query.getResultList();
}
 

@Override
public String getGroupAppResourceTemplateMetaData(String groupName, String templateName, String appName) {
    final Query q = entityManager.createNamedQuery(JpaGroupAppConfigTemplate.GET_GROUP_APP_TEMPLATE_META_DATA);
    q.setParameter("grpName", groupName);
    q.setParameter("templateName", templateName);
    q.setParameter("appName", appName);
    try {
        return (String) q.getSingleResult();
    } catch (RuntimeException re) {
        LOGGER.error("Error getting group app resource meta data for resource {} in group {}", templateName, groupName, re);
        throw new NonRetrievableResourceTemplateContentException(groupName, templateName, re);
    }
}
 

/**
 * 分页查询问题内容
 * @param firstIndex
 * @param maxResult
 * @param userName 用户名称
 * @param isStaff 是否为员工
 * @return
 */
@Transactional(readOnly=true,propagation=Propagation.NOT_SUPPORTED)
public List<Question> findQuestionContentByPage(int firstIndex, int maxResult,String userName,boolean isStaff){
	List<Question> questionContentList = new ArrayList<Question>();
	
	String sql = "select o.id,o.content,o.appendContent from Question o where o.userName=?1 and o.isStaff=?2";
	Query query = em.createQuery(sql);	
	query.setParameter(1, userName);
	query.setParameter(2, isStaff);
	//索引开始,即从哪条记录开始
	query.setFirstResult(firstIndex);
	//获取多少条数据
	query.setMaxResults(maxResult);
	
	List<Object[]> objectList = query.getResultList();
	if(objectList != null && objectList.size() >0){
		for(int i = 0; i<objectList.size(); i++){
			Object[] obj = (Object[])objectList.get(i);
			Long id = (Long)obj[0];
			String content = (String)obj[1];
			String appendContent = (String)obj[2];
			
			Question question = new Question();
			question.setId(id);
			question.setContent(content);
			question.setAppendContent(appendContent);
			questionContentList.add(question);
		}
	}
	
	return questionContentList;
}
 

@TransactionAttribute(TransactionAttributeType.REQUIRES_NEW)
public void storeControllerOrganizations(
        HashMap<String, String> controllerOrganizations) {

    LOGGER.debug("Storing configured controllers");
    Query query = em
            .createNamedQuery("ConfigurationSetting.getControllersForKey");
    query.setParameter("key",
            ControllerConfigurationKey.BSS_ORGANIZATION_ID.name());
    List<?> resultList = query.getResultList();
    for (Object entry : resultList) {
        ConfigurationSetting currentCs = (ConfigurationSetting) entry;
        String cId = currentCs.getControllerId();
        if (controllerOrganizations.containsKey(cId)) {
            String value = controllerOrganizations.get(cId);
            if (value == null || value.trim().length() == 0) {
                em.remove(currentCs);
            } else {
                currentCs.setSettingValue(value);
                em.persist(currentCs);
            }
            controllerOrganizations.remove(cId);
        }
    }
    for (String key : controllerOrganizations.keySet()) {
        if (controllerOrganizations.get(key) != null
                && controllerOrganizations.get(key).trim().length() > 0) {
            ConfigurationSetting newSetting = new ConfigurationSetting();
            newSetting.setControllerId(key);
            newSetting.setSettingKey(
                    ControllerConfigurationKey.BSS_ORGANIZATION_ID.name());
            newSetting.setSettingValue(controllerOrganizations.get(key));
            em.persist(newSetting);
        }
    }
}
 

@Override
@SuppressWarnings("unchecked")
public void preDelete(EntityManager em) {
	super.preDelete(em);
	Query query = em.createQuery("Select p from ChatChannel p where p.bot = :bot");
	query.setParameter("bot", detach());
	List<ChatChannel> channels = query.getResultList();
	for (ChatChannel channel : channels) {
		channel.setBot(null);
	}
	query = em.createQuery("Delete from BotAttachment p where p.bot = :bot");
	query.setParameter("bot", detach());
	query.executeUpdate();
}
 

public Optional<GetResult> findGetResultForMessageId(String messageId) {
    Query query = entityManager.createQuery("select gr from GetResult gr where gr.messageId = :messageId");
    query.setParameter("messageId", messageId);
    List<GetResult> resultList = query.getResultList();
    logger.trace("Get result for {}:\n{}", messageId, resultList);
    if (resultList.isEmpty()) {
        return Optional.empty();
    }
    if (resultList.size() > 1) {
        throw new NonUniqueResultException("Several entities found with messageId: " + messageId);
    }
    return Optional.of(resultList.get(0));
}
 

@Override
public boolean checkWebServerResourceFileName(String groupName, String webServerName, String fileName) {
    final JpaWebServer jpaWebServer = findWebServer(groupName, webServerName);
    if (jpaWebServer != null) {
        final Query q = entityManager.createNamedQuery(JpaWebServerConfigTemplate.GET_WEBSERVER_TEMPLATE_RESOURCE_NAME);
        q.setParameter(JpaWebServerConfigTemplate.QUERY_PARAM_WEBSERVER_NAME, webServerName);
        q.setParameter(JpaWebServerConfigTemplate.QUERY_PARAM_TEMPLATE_NAME, fileName);
        final List<String> result = q.getResultList();
        if (result != null && result.size() == 1) {
            return true;
        }
    }
    return false;
}
 

@Override
public void setValues(Query query, Searchable search) {
    if (search.containsSearchKey("realname")) {
        query.setParameter("realname", "%" + search.getValue("realname") + "%");
    }
}
 

@SuppressWarnings("unchecked")
public List<OkrWorkReportPersonLink> listNextWithFilter(String id, Integer count, Object sequence, WorkPersonSearchFilter wrapIn) throws Exception {
	//先获取上一页最后一条的sequence值，如果有值的话，以此sequence值作为依据取后续的count条数据
	EntityManager em = this.entityManagerContainer().get( OkrWorkReportPersonLink.class);
	String order = wrapIn.getOrder(); // 排序方式
	List<Object> vs = new ArrayList<>();
	StringBuffer sql_stringBuffer = new StringBuffer();
	Integer index = 1;
	
	if ( order == null || order.isEmpty() ) {
		order = "DESC";
	}
	sql_stringBuffer.append( "SELECT o FROM " + OkrWorkReportPersonLink.class.getCanonicalName() + " o where 1=1" );
	if ((null != sequence)) {
		sql_stringBuffer.append( " and o." + wrapIn.getSequenceField() + " " + (StringUtils.equalsIgnoreCase(order, "DESC" ) ? "<" : ">" ) + ( " ?" + (index)));
		vs.add(sequence);
		index++;
	}
	if ( null != wrapIn.getTitle() && !wrapIn.getTitle().isEmpty() ) {
		sql_stringBuffer.append( " and o.title like  ?" + (index) );
		vs.add( "%" + wrapIn.getTitle() + "%" );
		index++;
	}
	//当前处理人身份
	if ( null != wrapIn.getProcessIdentity() && !wrapIn.getProcessIdentity().isEmpty() ) {
		sql_stringBuffer.append( " and o.processorIdentity =  ?" + (index) );
		vs.add( wrapIn.getProcessIdentity() );
		index++;
	}
	//工作处理状态
	if (null != wrapIn.getProcessStatusList() && wrapIn.getProcessStatusList().size() > 0) {
		sql_stringBuffer.append( " and o.processStatus in ( ?" + (index) + " )" );
		vs.add( wrapIn.getProcessStatusList() );
		index++;
	}
	if (null != wrapIn.getQ_statuses() && wrapIn.getQ_statuses().size() > 0) {
		sql_stringBuffer.append( " and o.status in ( ?" + (index) + " )" );
		vs.add( wrapIn.getQ_statuses() );
		index++;
	}
	sql_stringBuffer.append( " order by o." + wrapIn.getSequenceField() + " " + (StringUtils.equalsIgnoreCase(order, "DESC" ) ? "DESC" : "ASC" ));
	
	//logger.debug( sql_stringBuffer.toString() );
	//logger.debug( vs );
	
	Query query = em.createQuery( sql_stringBuffer.toString(), OkrWorkReportPersonLink.class);
	// 为查询设置所有的参数值
	for (int i = 0; i < vs.size(); i++) {
		query.setParameter(i + 1, vs.get(i));
	}
	return query.setMaxResults( count ).getResultList();
}
 

/**
 * Loads all non-deleted products of a supplier. In ST-Test some
 * organizations create lots of products that are deleted afterwards. In BES
 * deleted products are not removed from the database. They are only
 * flagged. This will slow down the test execution when the list of deleted
 * products gets longer. A long list of deleted products is not very
 * realistic in production. However, I added this optimization to simplify
 * ST-Testing.
 * 
 * @param supplier
 *            Supplier that created the products
 * @return List of products
 */
private List<Product> loadNonDeletedProducts(Organization supplier) {

    Query query = null;
    query = ds.createNamedQuery("Product.getProductsForVendor");
    query.setParameter("vendorKey", Long.valueOf(supplier.getKey()));
    query.setParameter("filterOutWithStatus",
            EnumSet.of(ServiceStatus.DELETED));
    @SuppressWarnings("unchecked")
    List<Product> result = query.getResultList();

    return result;
}