Java Code Examples for javax.persistence.EntityManager#createQuery()

/**
 * Gets all order items for the given productId.
 * @param productId The id of the product ordered.
 * @param start The index of the first orderItem to return. Negative value to start at the beginning.
 * @param limit The maximum number of orderItem to return. Negative value to return all.
 * @return List of order items with the specified product.
 */
public List<PersistenceOrderItem> getAllEntitiesWithProduct(long productId, int start, int limit) {
	List<PersistenceOrderItem> entities = null;
	EntityManager em = getEM();
    try {
        em.getTransaction().begin();
        PersistenceProduct prod = em.find(PersistenceProduct.class, productId);
        if (prod != null) {
        	TypedQuery<PersistenceOrderItem> allMatchesQuery =
        			em.createQuery("SELECT u FROM " + getEntityClass().getName()
        					+ " u WHERE u.product = :prod", getEntityClass());
        	allMatchesQuery.setParameter("prod", prod);
    		entities = resultsWithStartAndLimit(em, allMatchesQuery, start, limit);
        }
        em.getTransaction().commit();
    } finally {
        em.close();
    }
	if (entities == null) {
		return new ArrayList<PersistenceOrderItem>();
	}
	return entities;
}
 

/**
 * @return Either the newest product, or null it if does not exist
 */
public static Product selectNewestProduct() {
   EntityManager em = DBUtil.getEmFactory().createEntityManager();
   String queryString = "SELECT p FROM Product p "
                      + "ORDER BY p.productId";
   Query query = em.createQuery(queryString);
   
   Product product = null;
           
   try {
      List<Product> products = query.getResultList();
      product = products.get(products.size() - 1);
   } catch (Exception e) {
      System.err.println(e);
   } finally {
      em.close();
   }
   
   return product;
}
 

@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);
    }
}
 

/**
 * Method to remove the audit trail of the cluster.
 * 
 * @param clusterId
 */
public void removeAuditTrail(Long clusterId) {
	try {
		// Get entity manager.
		EntityManager em = HibernateUtils.getEntityManager();
		// Get the transaction
		EntityTransaction tc = em.getTransaction();
		// begin the transaction
		tc.begin();
		// build the query.
		Query query = em
				.createQuery("delete from com.impetus.ankush.common.domain.Configuration_AUD where clusterId=:clusterId");
		// setting the cluster id parameter in query.
		query.setParameter(
				com.impetus.ankush2.constant.Constant.Keys.CLUSTERID,
				clusterId);
		// execute the query
		query.executeUpdate();
		// commit the transaction
		tc.commit();
	} catch (Exception e) {
		LOG.error(e.getMessage(), e);
	}
}
 

/**
 * @see io.apiman.manager.api.core.IStorage#getAllContracts(java.lang.String, java.lang.String, java.lang.String)
 */
@Override
public Iterator<ContractBean> getAllContracts(String organizationId, String clientId, String version)
        throws StorageException {
    EntityManager entityManager = getActiveEntityManager();
        String jpql =
            "SELECT c from ContractBean c " +
            "  JOIN c.client clientv " +
            "  JOIN clientv.client client " +
            "  JOIN client.organization aorg" +
            " WHERE client.id = :clientId " +
            "   AND aorg.id = :orgId " +
            "   AND clientv.version = :version " +
            " ORDER BY c.id ASC";
    Query query = entityManager.createQuery(jpql);
    query.setParameter("orgId", organizationId);
    query.setParameter("clientId", clientId);
    query.setParameter("version", version);

    return getAll(ContractBean.class, query);
}
 

public RelationLink getRelationLinkByUniqueId( final String uniqueIdentifier )
{
    EntityManager em = daoManager.getEntityManagerFactory().createEntityManager();
    RelationLink result = null;
    try
    {
        Query qr = em.createQuery(
                "SELECT ss FROM RelationLinkImpl AS ss" + " WHERE ss.uniqueIdentifier=:uniqueIdentifier" );
        qr.setParameter( "uniqueIdentifier", uniqueIdentifier );
        List<RelationLink> list = qr.getResultList();

        if ( !list.isEmpty() )
        {
            result = list.get( 0 );
        }
    }
    catch ( Exception ex )
    {
        logger.warn( "Error querying for trust item.", ex );
    }
    finally
    {
        daoManager.closeEntityManager( em );
    }
    return result;
}
 

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

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

	TypedQuery<Book> q = em.createQuery("SELECT b FROM Book b WHERE b.id = :id", Book.class);
	q.setParameter("id", 1L);
	Book b = q.getSingleResult();
	Assert.assertTrue(b instanceof Book);
	Assert.assertEquals(new Long(1), ((Book)b).getId());
	
	em.getTransaction().commit();
	em.close();
}
 

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

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

	Query q = em.createQuery("SELECT a, size(a.books) FROM Author a GROUP BY a.id");
	@SuppressWarnings("unchecked")
	List<Object[]> results = q.getResultList();
	
	for (Object[] r :  results) {
		log.info(r[0] + " wrote " +  r[1] + " books.");
	}

	em.getTransaction().commit();
	em.close();
}
 

public Relation findBySourceTargetObject( final RelationLink source, final RelationLink target,
                                          final RelationLink object )
{
    EntityManager em = daoManager.getEntityManagerFactory().createEntityManager();
    Relation result = null;
    try
    {
        Query qr = em.createQuery( "select ss from RelationImpl AS ss" + " WHERE ss.source.uniqueIdentifier=:source"
                + " AND ss.target.uniqueIdentifier=:target"
                + " AND ss.trustedObject.uniqueIdentifier=:trustedObject" );
        qr.setParameter( "source", source.getUniqueIdentifier() );
        qr.setParameter( "target", target.getUniqueIdentifier() );
        qr.setParameter( "trustedObject", object.getUniqueIdentifier() );
        List<Relation> list = qr.getResultList();

        if ( !list.isEmpty() )
        {
            result = list.get( 0 );
        }
    }
    catch ( Exception ex )
    {
        logger.warn( "Error querying for trust relation.", ex );
    }
    finally
    {
        daoManager.closeEntityManager( em );
    }
    return result;
}
 

public TicketCategory fromDTO(TicketCategory entity, EntityManager em)
{
   if (entity == null)
   {
      entity = new TicketCategory();
   }
   if (this.id != null)
   {
      TypedQuery<TicketCategory> findByIdQuery = em
            .createQuery(
                  "SELECT DISTINCT t FROM TicketCategory t WHERE t.id = :entityId",
                  TicketCategory.class);
      findByIdQuery.setParameter("entityId", this.id);
      try
      {
         entity = findByIdQuery.getSingleResult();
      }
      catch (javax.persistence.NoResultException nre)
      {
         entity = null;
      }
      return entity;
   }
   entity.setDescription(this.description);
   entity = em.merge(entity);
   return entity;
}
 

@ExpectSelect(5)
@Test
public void execute_one_select_but_five_select_expected() {
    EntityManager em = emf.createEntityManager();
    Query query = em.createQuery("FROM " + Book.class.getCanonicalName());
    query.getResultList();
}
 

@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
	}
}
 

public <T extends JpaObject> List<String> idsGreaterThan(Class<T> cls, String attribute, Object value)
		throws Exception {
	EntityManager em = this.get(cls);
	String str = "SELECT o.id FROM " + cls.getCanonicalName() + " o where o." + attribute + " > ?1";
	TypedQuery<String> query = em.createQuery(str, String.class);
	query.setParameter(1, value);
	List<String> os = query.getResultList();
	return new ArrayList<>(os);
}
 

public void removeAllRelationsWithLink( RelationLink link )
{
    EntityManager em = daoManager.getEntityManagerFactory().createEntityManager();

    try
    {
        daoManager.startTransaction( em );

        Query qr = em.createQuery( "DELETE FROM RelationImpl AS rln" + " WHERE rln.source.uniqueIdentifier=:id"
                + " OR rln.target.uniqueIdentifier=:id" + " OR rln.trustedObject.uniqueIdentifier=:id" );
        qr.setParameter( "id", link.getUniqueIdentifier() );
        qr.executeUpdate();

        qr = em.createQuery( "DELETE FROM RelationLinkImpl AS link" + " WHERE link.uniqueIdentifier=:id" );
        qr.setParameter( "id", link.getUniqueIdentifier() );
        qr.executeUpdate();

        daoManager.commitTransaction( em );
    }
    catch ( Exception ex )
    {
        daoManager.rollBackTransaction( em );
    }
    finally
    {
        daoManager.closeEntityManager( em );
    }
}
 

@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();		
}
 

/**
 * 根据条件搜索中心工作ID
 * @param id
 * @param count
 * @param sequence
 * @param com.x.okr.assemble.control.jaxrs.okrcenterworkinfo.WrapInFilter wrapIn
 * @return
 * @throws Exception 
 */
@SuppressWarnings("unchecked")
public List<OkrWorkPerson> listCenterWorkPersonNextWithFilter(String id, Integer count, Object sequence,
		com.x.okr.assemble.control.jaxrs.WorkCommonQueryFilter wrapIn) throws Exception {
	// 先获取上一页最后一条的sequence值，如果有值的话，以此sequence值作为依据取后续的count条数据
	EntityManager em = this.entityManagerContainer().get( OkrWorkPerson.class );
	String order = wrapIn.getOrder();// 排序方式
	List<Object> vs = new ArrayList<>();
	StringBuffer sql_stringBuffer = new StringBuffer();

	if (order == null || order.isEmpty()) {
		order = "DESC";
	}

	Integer index = 1;
	sql_stringBuffer.append("SELECT o FROM " + OkrWorkPerson.class.getCanonicalName() + " o where o.workId is null and o.processIdentity = '观察者' ");

	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.centerTitle like ?" + (index));
		vs.add("%" + wrapIn.getTitle() + "%");
		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++;
	}
	
	//根据默认的工作类别查询
	if (null != wrapIn.getDefaultWorkTypes() && wrapIn.getDefaultWorkTypes().size() > 0) {
		sql_stringBuffer.append(" and o.workType in ( ?" + (index) + " )");
		vs.add(wrapIn.getDefaultWorkTypes());
		index++;
	}
	
	//根据用户身份查询查询
	if (null != wrapIn.getIdentity() && !wrapIn.getIdentity().isEmpty() ) {
		sql_stringBuffer.append(" and o.employeeIdentity = ?" + (index) );
		vs.add(wrapIn.getIdentity());
		index++;
	}
	
	sql_stringBuffer.append(" order by o." + wrapIn.getSequenceField() + " " + (StringUtils.equalsIgnoreCase(order, "DESC") ? "DESC" : "ASC"));

	Query query = em.createQuery( sql_stringBuffer.toString(), OkrWorkPerson.class );

	for (int i = 0; i < vs.size(); i++) {
		query.setParameter(i + 1, vs.get(i));
	}
	return query.setMaxResults(count).getResultList();
}
 

/**
 * @see io.apiman.manager.api.core.IStorageQuery#getApiVersions(java.lang.String, java.lang.String)
 */
@Override
public List<ApiVersionSummaryBean> getApiVersions(String orgId, String apiId)
        throws StorageException {
    beginTx();
    try {
        EntityManager entityManager = getActiveEntityManager();
                String jpql =
                  "SELECT v "
                + "  FROM ApiVersionBean v"
                + "  JOIN v.api s"
                + "  JOIN s.organization o"
                + " WHERE o.id = :orgId"
                + "  AND s.id = :apiId"
                + " ORDER BY v.createdOn DESC";
        Query query = entityManager.createQuery(jpql);
        query.setMaxResults(500);
        query.setParameter("orgId", orgId);
        query.setParameter("apiId", apiId);

        List<ApiVersionBean> apiVersions = query.getResultList();
        List<ApiVersionSummaryBean> rval = new ArrayList<>(apiVersions.size());
        for (ApiVersionBean apiVersion : apiVersions) {
            ApiVersionSummaryBean svsb = new ApiVersionSummaryBean();
            svsb.setOrganizationId(apiVersion.getApi().getOrganization().getId());
            svsb.setOrganizationName(apiVersion.getApi().getOrganization().getName());
            svsb.setId(apiVersion.getApi().getId());
            svsb.setName(apiVersion.getApi().getName());
            svsb.setDescription(apiVersion.getApi().getDescription());
            svsb.setVersion(apiVersion.getVersion());
            svsb.setStatus(apiVersion.getStatus());
            svsb.setPublicAPI(apiVersion.isPublicAPI());
            rval.add(svsb);
        }
        return rval;
    } catch (Throwable t) {
        logger.error(t.getMessage(), t);
        throw new StorageException(t);
    } finally {
        rollbackTx();
    }
}
 

public boolean deleteUser() {
	try {
		checkAdmin();
		EntityManager em = AdminDatabase.instance().getFactory().createEntityManager();
		try {
			Query query = em.createQuery("Select count(p) from BotInstance p where p.creator = :user");
			query.setParameter("user", this.viewUser);
			if (((Number)query.getSingleResult()).intValue() > 0) {
				throw new BotException("You must first delete your bots");
			}
			query = em.createQuery("Select count(p) from ChatChannel p where p.creator = :user");
			query.setParameter("user", this.viewUser);
			if (((Number)query.getSingleResult()).intValue() > 0) {
				throw new BotException("You must first delete your channels");
			}
			query = em.createQuery("Select count(p) from Forum p where p.creator = :user");
			query.setParameter("user", this.viewUser);
			if (((Number)query.getSingleResult()).intValue() > 0) {
				throw new BotException("You must first delete your forums");
			}
			query = em.createQuery("Select count(p) from Analytic p where p.creator = :user");
			query.setParameter("user", this.viewUser);
			if (((Number)query.getSingleResult()).intValue() > 0) {
				throw new BotException("You must first delete your analytics");
			}
			query = em.createQuery("Select count(p) from Graphic p where p.creator = :user");
			query.setParameter("user", this.viewUser);
			if (((Number)query.getSingleResult()).intValue() > 0) {
				throw new BotException("You must first delete your graphics");
			}
			query = em.createQuery("Select count(p) from Avatar p where p.creator = :user");
			query.setParameter("user", this.viewUser);
			if (((Number)query.getSingleResult()).intValue() > 0) {
				throw new BotException("You must first delete your avatars");
			}
			query = em.createQuery("Select count(p) from Script p where p.creator = :user");
			query.setParameter("user", this.viewUser);
			if (((Number)query.getSingleResult()).intValue() > 0) {
				throw new BotException("You must first delete your scripts");
			}
			query = em.createQuery("Select count(p) from IssueTracker p where p.creator = :user");
			query.setParameter("user", this.viewUser);
			if (((Number)query.getSingleResult()).intValue() > 0) {
				throw new BotException("You must first delete your issue trackers");
			}
		} finally {
			em.close();
		}
		AdminDatabase.instance().deleteUser(this.viewUser.getUserId());
		setViewUser(null);
		if (!isSuper()) {
			logout();
		}
	} catch (Exception failed) {
		error(failed);
		return false;
	}
	return true;
}
 

/**
 * 查询符合的文档信息总数
 * @param id
 * @param count
 * @param sequence
 * @param wrapIn
 * @return
 * @throws Exception
 */
public long getCountWithFilter( WrapInFilterStatisticPersonForMonth wrapIn ) throws Exception {
	//先获取上一页最后一条的sequence值，如果有值的话，以此sequence值作为依据取后续的count条数据
	EntityManager em = this.entityManagerContainer().get( StatisticPersonForMonth.class );
	List<Object> vs = new ArrayList<>();
	StringBuffer sql_stringBuffer = new StringBuffer();
	Integer index = 1;
	
	sql_stringBuffer.append( "SELECT count(o.id) FROM "+StatisticPersonForMonth.class.getCanonicalName()+" o where 1=1" );
	
	if ((null != wrapIn.getEmployeeName()) && wrapIn.getEmployeeName().size() > 0) {
		sql_stringBuffer.append(" and o.employeeName in ?" + (index));
		vs.add( wrapIn.getEmployeeName() );
		index++;
	}
	if ((null != wrapIn.getUnitName()) && wrapIn.getUnitName().size() > 0 ) {
		sql_stringBuffer.append(" and o.unitName in ?" + (index));
		vs.add( wrapIn.getUnitName() );
		index++;
	}
	if ((null != wrapIn.getTopUnitName()) && wrapIn.getTopUnitName().size() > 0 ) {
		sql_stringBuffer.append(" and o.topUnitName in ?" + (index));
		vs.add( wrapIn.getTopUnitName() );
		index++;
	}
	if ((null != wrapIn.getStatisticYear() ) && (!wrapIn.getStatisticYear().isEmpty())) {
		sql_stringBuffer.append(" and o.statisticYear = ?" + (index));
		vs.add( wrapIn.getStatisticYear() );
		index++;
	}
	if ((null != wrapIn.getStatisticMonth()) && (!wrapIn.getStatisticMonth().isEmpty())) {
		sql_stringBuffer.append(" and o.statisticMonth = ?" + (index));
		vs.add( wrapIn.getStatisticMonth() );
		index++;
	}
	Query query = em.createQuery( sql_stringBuffer.toString(), StatisticPersonForMonth.class );
	//为查询设置所有的参数值
	for (int i = 0; i < vs.size(); i++) {
		query.setParameter(i + 1, vs.get(i));
	}		
	return (Long) query.getSingleResult();
}
 

/**
 * 查询符合的文档信息总数
 * @param wrapIn
 * @return
 * @throws Exception
 */
public long getCountWithFilter( WrapInFilterAppeal wrapIn ) throws Exception {
	//先获取上一页最后一条的sequence值，如果有值的话，以此sequence值作为依据取后续的count条数据
	EntityManager em = this.entityManagerContainer().get( AttendanceAppealInfo.class );
	List<Object> vs = new ArrayList<>();
	StringBuffer sql_stringBuffer = new StringBuffer();
	Integer index = 1;
	
	sql_stringBuffer.append( "SELECT count(o.id) FROM "+AttendanceAppealInfo.class.getCanonicalName()+" o where 1=1" );
	
	if ((null != wrapIn.getDetailId()) && (!wrapIn.getDetailId().isEmpty())) {
		sql_stringBuffer.append(" and o.detailId = ?" + (index));
		vs.add( wrapIn.getDetailId() );
		index++;
	}
	if ((null != wrapIn.getEmpName()) && (!wrapIn.getEmpName().isEmpty())) {
		sql_stringBuffer.append(" and o.empName = ?" + (index));
		vs.add( wrapIn.getEmpName() );
		index++;
	}
	if ((null != wrapIn.getUnitName()) && (!wrapIn.getUnitName().isEmpty())) {
		sql_stringBuffer.append(" and o.unitName = ?" + (index));
		vs.add( wrapIn.getUnitName() );
		index++;
	}
	if ((null != wrapIn.getTopUnitName()) && (!wrapIn.getTopUnitName().isEmpty())) {
		sql_stringBuffer.append(" and o.topUnitName = ?" + (index));
		vs.add( wrapIn.getTopUnitName() );
		index++;
	}
	if ((null != wrapIn.getYearString() ) && (!wrapIn.getYearString().isEmpty())) {
		sql_stringBuffer.append(" and o.yearString = ?" + (index));
		vs.add( wrapIn.getYearString() );
		index++;
	}
	if ((null != wrapIn.getMonthString()) && (!wrapIn.getMonthString().isEmpty())) {
		sql_stringBuffer.append(" and o.monthString = ?" + (index));
		vs.add( wrapIn.getMonthString() );
		index++;
	}
	if (wrapIn.getStatus()!=999) {
		sql_stringBuffer.append(" and o.status = ?" + (index));
		vs.add( wrapIn.getStatus() );
		index++;
	}
	if ((null != wrapIn.getAppealReason()) && (!wrapIn.getAppealReason().isEmpty())) {
		sql_stringBuffer.append(" and o.appealReason = ?" + (index));
		vs.add( wrapIn.getAppealReason() );
		index++;
	}
	if ((null != wrapIn.getProcessPerson1()) && (!wrapIn.getProcessPerson1().isEmpty())) {
		sql_stringBuffer.append(" and o.processPerson1 = ?" + (index));
		vs.add( wrapIn.getProcessPerson1() );
		index++;
	}
	if ((null != wrapIn.getProcessPerson2()) && (!wrapIn.getProcessPerson2().isEmpty())) {
		sql_stringBuffer.append(" and o.processPerson2 = ?" + (index));
		vs.add( wrapIn.getProcessPerson2() );
		index++;
	}
	//添加OR
	if (wrapIn.getOrAtrribute() != null && wrapIn.getOrAtrribute().size() > 0) {
		sql_stringBuffer.append(" and (");
		NameValueCountPair nameValueCountPair = null;
		for (int p = 0; p < wrapIn.getOrAtrribute().size(); p++) {
			nameValueCountPair = wrapIn.getOrAtrribute().get(p);
			if (p == 0) {
				sql_stringBuffer.append(" o." + nameValueCountPair.getName() + " = ?" + (index));

			} else {
				sql_stringBuffer.append(" or o." + nameValueCountPair.getName() + " = ?" + (index));
			}
			vs.add(nameValueCountPair.getValue());
			index++;
		}
		sql_stringBuffer.append(" )");
	}

	Query query = em.createQuery( sql_stringBuffer.toString(), AttendanceAppealInfo.class );
	//为查询设置所有的参数值
	for (int i = 0; i < vs.size(); i++) {
		query.setParameter(i + 1, vs.get(i));
	}		
	return (Long) query.getSingleResult();
}