javax.persistence.Query Java Examples

/**
 * 分页查询评论内容
 * @param firstIndex
 * @param maxResult
 * @param userName 用户名称
 * @param isStaff 是否为员工
 * @return
 */
@Transactional(readOnly=true,propagation=Propagation.NOT_SUPPORTED)
public List<String> findCommentContentByPage(int firstIndex, int maxResult,String userName,boolean isStaff){
	List<String> topicContentList = new ArrayList<String>();//key:话题Id  value:话题内容
	
	String sql = "select o.content from Comment 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++){
			String content = (String)objectList.get(i);
			topicContentList.add(content);
		}
	}
	
	return topicContentList;
}
 

public List<Relation> relationsBySourceAndObject( final RelationLinkImpl source, final RelationLinkImpl object )
{
    EntityManager em = daoManager.getEntityManagerFactory().createEntityManager();
    List<Relation> result = Lists.newArrayList();
    try
    {
        Query qr = em.createQuery( "SELECT ss FROM RelationImpl AS ss" + " WHERE ss.source.uniqueIdentifier=:source"
                + " AND ss.trustedObject.uniqueIdentifier=:trustedObject" + " ORDER BY ss.relationStatus DESC" );
        qr.setParameter( "source", source.getUniqueIdentifier() );
        qr.setParameter( "trustedObject", object.getUniqueIdentifier() );
        result.addAll( qr.getResultList() );
    }
    catch ( Exception ex )
    {
        logger.warn( "Error querying for trust relation.", ex );
    }
    finally
    {
        daoManager.closeEntityManager( em );
    }
    return result;
}
 

@SuppressWarnings("unchecked")
public void retrieveThing(StringBuilder builder) throws SystemException, NamingException {
    // Look up the EntityManager in JNDI
    Context ctx = new InitialContext();
    EntityManager em = (EntityManager) ctx.lookup(JNDI_NAME);
    // Compose a JPQL query
    String query = "SELECT t FROM Thing t";
    Query q = em.createQuery(query);

    // Execute the query
    List<Thing> things = q.getResultList();
    builder.append("Query returned " + things.size() + " things").append(newline);

    // Let's see what we got back!
    for (Thing thing : things) {
        builder.append("Thing in list " + thing).append(newline);
    }
}
 

@Test
public void executeQueryLoadTemplateServices() {
    // given
    Query q = mock(Query.class);
    when(ds.createNamedQuery(anyString())).thenReturn(q);
    doReturn(new ArrayList<Product>()).when(q).getResultList();
    Organization vendor = new Organization();
    vendor.setKey(1L);

    // when
    partnerBean.executeQueryLoadTemplateServices(
            EnumSet.of(ServiceType.TEMPLATE), vendor);

    // then
    verify(q).setParameter("vendorKey", Long.valueOf(vendor.getKey()));
    verify(q)
            .setParameter("productTypes", EnumSet.of(ServiceType.TEMPLATE));
    verify(q).setParameter("filterOutWithStatus",
            EnumSet.of(ServiceStatus.OBSOLETE, ServiceStatus.DELETED));
}
 

public List<Relation> getTrustedRelationsByOwnership( final RelationLink trustedObject, Ownership ownership )
{
    EntityManager em = daoManager.getEntityManagerFactory().createEntityManager();
    List<Relation> result = Lists.newArrayList();
    try
    {
        Query qr = em.createQuery(
                "select ss from RelationImpl AS ss" + " where ss.trustedObject.uniqueIdentifier=:trustedObject "
                        + "and ss.relationInfo.ownershipLevel=:ownershipLevel ORDER BY ss.relationStatus DESC" );
        qr.setParameter( "trustedObject", trustedObject.getUniqueIdentifier() );
        qr.setParameter( "ownershipLevel", ownership.getLevel() );
        result.addAll( qr.getResultList() );
    }
    catch ( Exception ex )
    {
        logger.warn( "Error querying for trust relation.", ex );
    }
    finally
    {
        daoManager.closeEntityManager( em );
    }
    return result;
}
 

@Override
public List findTop(String jpql, int topCount, Map<String, Object> values) {

	Query query = entityManager.createQuery(jpql);
	query.setFirstResult(0);
	query.setMaxResults(topCount);

	if (values != null && values.size() > 0) {
		for (Entry<String, Object> e : values.entrySet()) {
			query.setParameter(e.getKey(), e.getValue());
		}
	}

	List list = query.getResultList();
	return list;
}
 

/**
 * 根据评论Id查询评论在表的第几行
 * @param commentId 评论Id
 * @param topicId 话题Id
 * @param status 状态
 * @param sort 按发表时间排序 1.desc 2.asc 
 * @return
 */
@Transactional(readOnly=true,propagation=Propagation.NOT_SUPPORTED)
public Long findRowByCommentId(Long commentId,Long topicId,Integer status,Integer sort){
	String commentId_sql = "";
	String sort_sql = "";
	if(sort.equals(1)){
		commentId_sql = " o.id >=?1";
		sort_sql = " desc";
	}else{
		commentId_sql = " o.id <=?1";
		sort_sql = " asc";
	}
	Query query = em.createQuery("select count(o.id) from Comment o where "+commentId_sql+" and o.topicId= ?2 and o.status= ?3 order by o.postTime"+sort_sql);
	//给SQL语句设置参数
	query.setParameter(1, commentId);
	query.setParameter(2, topicId);
	query.setParameter(3, status);
	return (Long)query.getSingleResult();		
}
 

@SuppressWarnings("unchecked")
List<Subscription> getSubscriptionsForOrg(PlatformUser user,
        Set<SubscriptionStatus> states,
        org.oscm.paginator.Pagination pagination, String queryString,
        Long... keys) {

    Set<String> statesAsString = getSubscriptionStatesAsString(states);
    Query query = dataManager.createNativeQuery(queryString,
            Subscription.class);
    try {
        query.setParameter("locale", user.getLocale());
        query.setParameter("objecttype",
                LocalizedObjectTypes.PRODUCT_MARKETING_NAME.name());
    } catch (IllegalArgumentException exc) {
        logger.logDebug("Parameters are not found in the query. Not an error, just sorting is not applied.");
    }
    query.setParameter("organization",
            Long.valueOf(user.getOrganization().getKey()));
    query.setParameter("states", statesAsString);

    setPaginationParameters(pagination, query);
    setSubscriptionKeysParameter(query, keys);

    return query.getResultList();
}
 

@Test
@SuppressWarnings("unchecked")
public void testEntityManagerProxyIsProxy() {
	EntityManager em = createContainerManagedEntityManager();
	assertTrue(Proxy.isProxyClass(em.getClass()));
	Query q = em.createQuery("select p from Person as p");
	List<Person> people = q.getResultList();
	assertTrue(people.isEmpty());

	assertTrue("Should be open to start with", em.isOpen());
	try {
		em.close();
		fail("Close should not work on container managed EM");
	}
	catch (IllegalStateException ex) {
		// OK
	}
	assertTrue(em.isOpen());
}
 

@Test
public void adHocNativeQuery() {
	log.info("... adHocNativeQuery ...");

	EntityManager em = emf.createEntityManager();
	em.getTransaction().begin();

	Query q = em.createNativeQuery("SELECT * FROM book b WHERE id = ?", Book.class);
	q.setParameter(1, 1);
	Book b = (Book) q.getSingleResult();
	Assert.assertTrue(b instanceof Book);
	Assert.assertEquals(new Long(1), ((Book)b).getId());
	
	em.getTransaction().commit();
	em.close();
}
 

public Page<T> findBySql(String sql, String countSql, NamedParams params, Pageable pageable) {
    Assert.hasText(sql, "Query must has text!");
    Assert.hasText(countSql, "Count sql must has text!");
    Assert.notNull(params, "QueryParams must not be null!");
    Assert.notNull(pageable, "PageRequest must not be null!");

    Query query = em.createNativeQuery(sql);

    setQueryParams(query, params);
    query.setMaxResults(pageable.getPageSize());
    query.setFirstResult((int) pageable.getOffset());

    List<T> resultList = query.getResultList();

    Query countQuery = em.createNativeQuery(countSql);
    setQueryParams(countQuery, params);
    Long total = Long.valueOf(countQuery.getSingleResult().toString());

    Page<T> page = new PageImpl(resultList, pageable, total);
    return page;
}
 

/**
 * Deactivates and detaches all services of the given supplier on the given
 * marketplace. For each service of the supplier that published on the
 * marketplace the service is first deactivated and then the service is
 * 'detached' from the marketplace. This affects the customer specific
 * copies as well.
 * 
 * @param mp
 *            the marketplace
 * @param supplier
 *            the supplier
 * @param dm
 *            the data service
 */
private void unlinkServices(Marketplace mp, Organization supplier,
        DataService dm) {
    String mId = mp.getMarketplaceId();
    Query query = dm
            .createNamedQuery("Product.getProductsForVendorOnMarketplace");
    query.setParameter("marketplaceId", mId);
    query.setParameter("vendorKey", Long.valueOf(supplier.getKey()));
    List<Product> productList = ParameterizedTypes
            .list(query.getResultList(), Product.class);
    if (productList != null) {
        for (Product product : productList) {
            if (product.getStatus() == ServiceStatus.ACTIVE) {
                product.setStatus(ServiceStatus.INACTIVE);
            }
            deactivateCustomerServices(product);
            for (CatalogEntry ce : product.getCatalogEntries()) {
                Marketplace ceMp = ce.getMarketplace();
                if (ceMp != null && mId.equals(ceMp.getMarketplaceId())) {
                    ce.setMarketplace(null);
                }
            }
        }
    }
}
 

public Query build(GetEntityCountUriInfo uriInfo) throws ODataJPARuntimeException {
  Query query = null;
  try {
    ODataJPAQueryExtensionEntityListener listener = getODataJPAQueryEntityListener((UriInfo) uriInfo);
    if (listener != null) {
      query = listener.getQuery(uriInfo, em);
      JPQLContext jpqlContext = JPQLContext.getJPQLContext();
      query = getParameterizedQueryForListeners(jpqlContext, query);
    }
    if (query == null) {
      query = buildQuery((UriInfo) uriInfo, UriInfoType.GetEntityCount);
    }
  } catch (Exception e) {
    throw ODataJPARuntimeException.throwException(
        ODataJPARuntimeException.ERROR_JPQL_QUERY_CREATE, e);
  } finally {
    JPQLContext.removeJPQLContext();
    ODataExpressionParser.removePositionalParametersThreadLocal();
  }
  return query;
}
 

public Subscription getSubscription(UUID uuid) {
    Query query = dataManager.createNamedQuery("Subscription.getByUUID");
    query.setParameter("uuid", uuid);
    List<Subscription> subscriptions = ParameterizedTypes
            .list(query.getResultList(), Subscription.class);
    if (subscriptions == null || subscriptions.isEmpty()) {
        return null;
    }
    return subscriptions.get(0);
}
 

/**
 * Important: in the current version of this method, we load Owners with all their Pets and Visits while 
 * we do not need Visits at all and we only need one property from the Pet objects (the 'name' property).
 * There are some ways to improve it such as:
 * - creating a Ligtweight class (example here: https://community.jboss.org/wiki/LightweightClass)
 * - Turning on lazy-loading and using {@link OpenSessionInViewFilter}
 */
@SuppressWarnings("unchecked")
public Collection<Owner> findByLastName(String lastName) {
    // using 'join fetch' because a single query should load both owners and pets
    // using 'left join fetch' because it might happen that an owner does not have pets yet
    Query query = this.em.createQuery("SELECT DISTINCT owner FROM Owner owner left join fetch owner.pets WHERE owner.lastName LIKE :lastName");
    query.setParameter("lastName", lastName + "%");
    return query.getResultList();
}
 

@SuppressWarnings("unchecked")
@Override
@Transactional
public SortedSet<Invitation> searchByEmail(String email)	throws DataAccessException {
	Query query = createNamedQuery("Invitation.searchByEmail", -1, -1 , "%" + email +"%" );
	return new TreeSet<Invitation>(query.getResultList());
}
 

public Map findAdminDetail(Long id) {
    String sql = "select a.id,a.role_id roleId,a.department_id departmentId,a.real_name realName,a.avatar,a.email,a.enable,a.mobile_phone mobilePhone,a.qq,a.username, " +
            "d.name as 'departmentName',r.role from admin a LEFT join department d on a.department_id=d.id LEFT JOIN admin_role r on a.role_id=r.id WHERE a.id=:adminId ";
    Query query = em.createNativeQuery(sql);
    //设置结果转成Map类型
    query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
    Object object = query.setParameter("adminId", id).getSingleResult();
    Map map = (HashMap) object;
    return map;
}
 

@Before
public void setup() {
    dataService = new DataServiceBean();
    em = mock(EntityManager.class);
    namedQuery = mock(Query.class);
    sessionContext = mock(SessionContext.class);
    dataService.em = em;
    dataService.sessionCtx = sessionContext;
    domObject_withBusinessKey.setProductId("productId");
    domObject_withBusinessKey.setVendorKey(1L);
}
 

@Test
@SuppressWarnings("unchecked")
public void testQueryNoPersons() {
	EntityManager em = entityManagerFactory.createEntityManager();
	Query q = em.createQuery("select p from Person as p");
	List<Person> people = q.getResultList();
	assertEquals(0, people.size());
	try {
		assertNull(q.getSingleResult());
		fail("Should have thrown NoResultException");
	}
	catch (NoResultException ex) {
		// expected
	}
}
 

/**
 * Apply the current transaction timeout, if any, to the given JPA Query object.
 * <p>This method sets the JPA 2.0 query hint "javax.persistence.query.timeout" accordingly.
 * @param query the JPA Query object
 * @param emf JPA EntityManagerFactory that the Query was created for
 */
public static void applyTransactionTimeout(Query query, EntityManagerFactory emf) {
	EntityManagerHolder emHolder = (EntityManagerHolder) TransactionSynchronizationManager.getResource(emf);
	if (emHolder != null && emHolder.hasTimeout()) {
		int timeoutValue = (int) emHolder.getTimeToLiveInMillis();
		try {
			query.setHint("javax.persistence.query.timeout", timeoutValue);
		}
		catch (IllegalArgumentException ex) {
			// oh well, at least we tried...
		}
	}
}
 

public static void updateSeqNoForMultiReference(EntityManager entityManager, String curGuid, String joinTable,
                                                List<String> refGuids, int i, String refGuid) {
    StringBuilder updateSeqSqlBuilder = new StringBuilder();
    updateSeqSqlBuilder.append("update ").append(joinTable)
            .append(" set seq_no = ").append(i + 1)
            .append(" where from_guid = '").append(curGuid).append("' and ")
            .append(" to_guid = '").append(refGuid).append("'");

    Query query = entityManager.createNativeQuery(updateSeqSqlBuilder.toString());
    int updateCount = query.executeUpdate();
    if (updateCount != 1) {
        throw new WecubeCoreException(String.format("Failed to update seq_no for mult reference [from_guid:%s,to_guid:%s]", curGuid,
                refGuids.get(i)));
    }
}
 

@Override
public void preDelete(EntityManager em) {
	super.preDelete(em);
	Query query = em.createQuery("Delete from Issue p where p.tracker = :tracker");
	query.setParameter("tracker", detach());
	query.executeUpdate();		
}
 

public boolean checkIfProductInstanceIdExists(String productInstanceId,
        TechnicalProduct techProduct) {
    Query query = dataManager
            .createNamedQuery("Subscription.getByInstanceIdOfTechProd");
    query.setParameter("productInstanceId", productInstanceId);
    query.setParameter("technicalProduct", techProduct);
    return !query.getResultList().isEmpty();
}
 

@Override
public void removeAttribute(String name) {
    Collection<RoleAttributeEntity> attributes = role.getAttributes();
    if (attributes == null) {
        return;
    }

    Query query = em.createNamedQuery("deleteRoleAttributesByNameAndUser");
    query.setParameter("name", name);
    query.setParameter("roleId", role.getId());
    query.executeUpdate();

    attributes.removeIf(attribute -> attribute.getName().equals(name));
}
 

@Override
@SuppressWarnings("unchecked")
public List<JpaGroup> findGroups(final String aName) {

    final Query query = entityManager.createQuery("SELECT g FROM JpaGroup g WHERE g.name=:groupName");
    query.setParameter("groupName", aName);

    return query.getResultList();
}
 

/**
 * 删除用户等级
 * @param userGradeId 用户等级Id
 */
@CacheEvict(value="userGradeServiceBean_cache",allEntries=true)
public int deleteUserGrade(Integer userGradeId){
	Query delete = em.createQuery("delete from UserGrade o where o.id=?1")
		.setParameter(1, userGradeId);
	return delete.executeUpdate();
}
 

@Transactional
public Long getCount(Long id) throws DataAccessException {
	try {
		Query query = createNamedQuery("DataSetItem.getCount",-1,-1, id);
		return  (Long) query.getSingleResult();
	} catch (NoResultException nre) {
		return null;
	}
}
 

@Override
public Level findCriterionLevel(Criterion criterion) {
    Query query = entityManager.createQuery("SELECT t FROM "
            + getTestEntityClass().getName() + " t "
            + " JOIN t.criterion cr"
            + " WHERE cr = :criterion");
    query.setParameter("criterion", criterion);
    query.setMaxResults(1);
    Test test = (Test)query.getSingleResult();
    return test.getLevel();
}
 

@Override
public void deleteRelatedContentFromContent(Content content) {
    Query query = entityManager.createNativeQuery(DELETE_RELATED_CONTENT_QUERY);
    query.setParameter("idContent", content.getId());
    try {
        query.executeUpdate();
    } catch (NoResultException nre) {
        // do nothing
    }
}
 

private void setQueryParameters(final Query query, final SearchParameters sp) {
    // add default parameter if specified in the named query
    for (Parameter<?> p : query.getParameters()) {
        if (NAMED_PARAMETER_NOW.equals(p.getName())) {
            query.setParameter(NAMED_PARAMETER_NOW, Calendar.getInstance().getTime());
        }
    }

    // add parameters for the named query
    for (String paramName : sp.getNamedQueryParameters().keySet()) {
        query.setParameter(paramName, sp.getNamedQueryParameter(paramName));
    }
}