javax.persistence.TypedQuery Java Examples

@GET
@Produces("application/json")
public List<EventDTO> listAll(@QueryParam("start") Integer startPosition, @QueryParam("max") Integer maxResult)
{
   TypedQuery<Event> findAllQuery = em.createQuery("SELECT DISTINCT e FROM Event e LEFT JOIN FETCH e.mediaItem LEFT JOIN FETCH e.category ORDER BY e.id", Event.class);
   if (startPosition != null)
   {
      findAllQuery.setFirstResult(startPosition);
   }
   if (maxResult != null)
   {
      findAllQuery.setMaxResults(maxResult);
   }
   final List<Event> searchResults = findAllQuery.getResultList();
   final List<EventDTO> results = new ArrayList<EventDTO>();
   for (Event searchResult : searchResults)
   {
      EventDTO dto = new EventDTO(searchResult);
      results.add(dto);
   }
   return results;
}
 

/**
 * This method is used to the parse the contents of the
 * "com/sap/espm/model/data/Products.xml" file and populate a list of
 * {@link Product} that will eventually be persisted in the Data Source.
 * 
 * @param suppliers
 *            - The List of {@link Supplier}.
 * @return - The list of {@link Product} that will be persisted in the
 *         Database.
 */
public List<Product> loadProducts(List<Supplier> suppliers) {
	EntityManager em = emf.createEntityManager();
	TypedQuery<Product> queryProd;
	List<Product> resProd = null;
	try {
		em.getTransaction().begin();
		queryProd = em.createNamedQuery("Product.getAllProducts", Product.class);
		resProd = queryProd.getResultList();
		if (resProd.size() > 5) {
			logger.info(resProd.size() + " Products already available in the db");
		} else {
			new XMLParser().readProduct(em, "com/sap/espm/model/data/Products.xml", suppliers);
			em.getTransaction().commit();
			queryProd = em.createNamedQuery("Product.getAllProducts", Product.class);
			resProd = queryProd.getResultList();
			logger.info(resProd.size() + " Products loaded into the db");
		}
	} catch (Exception e) {
		logger.error("Exception occured", e);
	} finally {
		em.close();
	}
	return resProd;
}
 

/**
 * 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;
}
 

@GET
@Produces("application/json")
public List<TicketPriceDTO> listAll(@QueryParam("start") Integer startPosition, @QueryParam("max") Integer maxResult)
{
   TypedQuery<TicketPrice> findAllQuery = em.createQuery("SELECT DISTINCT t FROM TicketPrice t LEFT JOIN FETCH t.show LEFT JOIN FETCH t.section LEFT JOIN FETCH t.ticketCategory ORDER BY t.id", TicketPrice.class);
   if (startPosition != null)
   {
      findAllQuery.setFirstResult(startPosition);
   }
   if (maxResult != null)
   {
      findAllQuery.setMaxResults(maxResult);
   }
   final List<TicketPrice> searchResults = findAllQuery.getResultList();
   final List<TicketPriceDTO> results = new ArrayList<TicketPriceDTO>();
   for (TicketPrice searchResult : searchResults)
   {
      TicketPriceDTO dto = new TicketPriceDTO(searchResult);
      results.add(dto);
   }
   return results;
}
 

@Override
public List<GroupModel> searchForGroupByName(RealmModel realm, String search, Integer first, Integer max) {
    TypedQuery<String> query = em.createNamedQuery("getGroupIdsByNameContaining", String.class)
            .setParameter("realm", realm.getId())
            .setParameter("search", search);
    if(Objects.nonNull(first) && Objects.nonNull(max)) {
        query= query.setFirstResult(first).setMaxResults(max);
    }
    List<String> groups =  query.getResultList();
    if (Objects.isNull(groups)) return Collections.EMPTY_LIST;
    List<GroupModel> list = new ArrayList<>();
    for (String id : groups) {
        GroupModel groupById = session.realms().getGroupById(id, realm);
        while(Objects.nonNull(groupById.getParentId())) {
            groupById = session.realms().getGroupById(groupById.getParentId(), realm);
        }
        if(!list.contains(groupById)) {
            list.add(groupById);
        }
    }
    list.sort(Comparator.comparing(GroupModel::getName));

    return Collections.unmodifiableList(list);
}
 

@Transactional(readOnly = true)
private VirtualRouterVmInventory findRunningVirtualRouterForRule(String ruleUuid) {
    List<String> vrUuids = proxy.getVrUuidsByNetworkService(PortForwardingRuleVO.class.getSimpleName(), ruleUuid);
    if (vrUuids == null || vrUuids.isEmpty()) {
        return null;
    }

    String sql = "select vr from VirtualRouterVmVO vr where vr.uuid in (:vrUuids) and vr.state = :vrState";
    TypedQuery<VirtualRouterVmVO> q = dbf.getEntityManager().createQuery(sql, VirtualRouterVmVO.class);
    q.setParameter("vrUuids", vrUuids);
    q.setParameter("vrState", VmInstanceState.Running);
    q.setMaxResults(1);
    List<VirtualRouterVmVO> vrs = q.getResultList();
    if (vrs.isEmpty()) {
        return null;
    } else {
        return VirtualRouterVmInventory.valueOf(vrs.get(0));
    }
}
 

@Override
public WSDLEndpoint getWSDLEndpointForIa(final CsarId csarId, final QName nodeTypeImpl, final String iaName) {
    WSDLEndpoint endpoint = null;
    final TypedQuery<WSDLEndpoint> queryWSDLEndpoint = em.createQuery(
        "SELECT e FROM WSDLEndpoint e where e.csarId= :csarId and e.IaName = :IaName and e.TypeImplementation = :nodeTypeImpl", WSDLEndpoint.class);
    queryWSDLEndpoint.setParameter("csarId", csarId);
    queryWSDLEndpoint.setParameter("IaName", iaName);
    queryWSDLEndpoint.setParameter("nodeTypeImpl", nodeTypeImpl);

    try {
        endpoint = (WSDLEndpoint) queryWSDLEndpoint.getSingleResult();
    } catch (final NoResultException e) {
        LOG.info("No endpoint stored for requested IA.");
    }
    return endpoint;
}
 

@Transactional(readOnly = true)
private void checkIfAnotherVip(String vipUuid, String vmNicUuid) {
    String sql = "select nic.uuid from VmNicVO nic where nic.vmInstanceUuid = (select n.vmInstanceUuid from VmNicVO n where" +
            " n.uuid = :nicUuid)";
    TypedQuery<String> q = dbf.getEntityManager().createQuery(sql, String.class);
    q.setParameter("nicUuid", vmNicUuid);
    List<String> nicUuids = q.getResultList();

    sql = "select count(*) from VmNicVO nic, PortForwardingRuleVO pf where nic.uuid = pf.vmNicUuid and pf.vipUuid != :vipUuid and nic.uuid in (:nicUuids)";
    TypedQuery<Long> lq = dbf.getEntityManager().createQuery(sql, Long.class);
    lq.setParameter("vipUuid", vipUuid);
    lq.setParameter("nicUuids", nicUuids);
    long count = lq.getSingleResult();

    if (count > 0) {
        sql = "select vm from VmInstanceVO vm, VmNicVO nic where vm.uuid = nic.vmInstanceUuid and nic.uuid = :nicUuid";
        TypedQuery<VmInstanceVO> vq = dbf.getEntityManager().createQuery(sql, VmInstanceVO.class);
        vq.setParameter("nicUuid", vmNicUuid);
        VmInstanceVO vm = vq.getSingleResult();

        throw new ApiMessageInterceptionException(operr("the VM[name:%s uuid:%s] already has port forwarding rules that have different VIPs than the one[uuid:%s]",
                        vm.getName(), vm.getUuid(), vipUuid));
    }
}
 

@GET
@Path("/{id:[0-9][0-9]*}")
@Produces("application/json")
public Response findById(@PathParam("id") Long id)
{
   TypedQuery<SectionAllocation> findByIdQuery = em.createQuery("SELECT DISTINCT s FROM SectionAllocation s LEFT JOIN FETCH s.performance LEFT JOIN FETCH s.section WHERE s.id = :entityId ORDER BY s.id", SectionAllocation.class);
   findByIdQuery.setParameter("entityId", id);
   SectionAllocation entity;
   try
   {
      entity = findByIdQuery.getSingleResult();
   }
   catch (NoResultException nre)
   {
      entity = null;
   }
   if (entity == null)
   {
      return Response.status(Status.NOT_FOUND).build();
   }
   SectionAllocationDTO dto = new SectionAllocationDTO(entity);
   return Response.ok(dto).build();
}
 

@GET
@Produces("application/json")
public List<ShowDTO> listAll(@QueryParam("start") Integer startPosition, @QueryParam("max") Integer maxResult)
{
   TypedQuery<Show> findAllQuery = em.createQuery("SELECT DISTINCT s FROM Show s LEFT JOIN FETCH s.event LEFT JOIN FETCH s.venue LEFT JOIN FETCH s.performances LEFT JOIN FETCH s.ticketPrices ORDER BY s.id", Show.class);
   if (startPosition != null)
   {
      findAllQuery.setFirstResult(startPosition);
   }
   if (maxResult != null)
   {
      findAllQuery.setMaxResults(maxResult);
   }
   final List<Show> searchResults = findAllQuery.getResultList();
   final List<ShowDTO> results = new ArrayList<ShowDTO>();
   for (Show searchResult : searchResults)
   {
      ShowDTO dto = new ShowDTO(searchResult);
      results.add(dto);
   }
   return results;
}
 

private static void verifyJPANamedQuery(final EntityManagerFactory emf) {
    EntityManager em = emf.createEntityManager();
    EntityTransaction transaction = em.getTransaction();
    transaction.begin();
    TypedQuery<Person> typedQuery = em.createNamedQuery(
            "get_person_by_name", Person.class);
    typedQuery.setParameter("name", "Quarkus");
    final Person singleResult = typedQuery.getSingleResult();

    if (!singleResult.getName().equals("Quarkus")) {
        throw new RuntimeException("Wrong result from named JPA query");
    }

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

@Transactional(readOnly = true)
private List<ImageDeletionStruct> getImageOnBackupStorage(List<String> bsUuids) {
    String sql = "select ref.backupStorageUuid, img from ImageVO img, ImageBackupStorageRefVO ref where img.uuid = ref.imageUuid and ref.backupStorageUuid in (:bsUuids) group by img.uuid";
    TypedQuery<Tuple> q = dbf.getEntityManager().createQuery(sql, Tuple.class);
    q.setParameter("bsUuids", bsUuids);
    List<Tuple> ts = q.getResultList();

    Map<String, ImageDeletionStruct> tmp = new HashMap<String, ImageDeletionStruct>();
    for (Tuple t : ts) {
        String bsUuid = t.get(0, String.class);
        ImageVO img = t.get(1, ImageVO.class);
        ImageDeletionStruct struct = tmp.get(img.getUuid());
        if (struct == null) {
            struct = new ImageDeletionStruct();
            struct.setImage(ImageInventory.valueOf(img));
            struct.setBackupStorageUuids(new ArrayList<String>());
            tmp.put(img.getUuid(), struct);
        }
        struct.getBackupStorageUuids().add(bsUuid);
    }

    List<ImageDeletionStruct> structs = new ArrayList<ImageDeletionStruct>();
    structs.addAll(tmp.values());
    return structs;
}
 

@Override
public List<PermissionTicket> findByScope(String scopeId, String resourceServerId) {
    if (scopeId==null) {
        return Collections.emptyList();
    }

    // Use separate subquery to handle DB2 and MSSSQL
    TypedQuery<String> query = entityManager.createNamedQuery("findPermissionIdByScope", String.class);

    query.setFlushMode(FlushModeType.COMMIT);
    query.setParameter("scopeId", scopeId);
    query.setParameter("serverId", resourceServerId);

    List<String> result = query.getResultList();
    List<PermissionTicket> list = new LinkedList<>();
    PermissionTicketStore ticketStore = provider.getStoreFactory().getPermissionTicketStore();

    for (String id : result) {
        PermissionTicket ticket = ticketStore.findById(id, resourceServerId);
        if (Objects.nonNull(ticket)) {
            list.add(ticket);
        }
    }

    return list;
}
 

public PlanInstance findByCorrelationId(final String correlationId) {
    try (AutoCloseableEntityManager em = EntityManagerProvider.createEntityManager()) {
        final CriteriaBuilder cb = em.getCriteriaBuilder();
        // Parameters
        final ParameterExpression<String> correlationIdParameter = cb.parameter(String.class);
        // Build the Criteria Query
        final CriteriaQuery<PlanInstance> cq = cb.createQuery(PlanInstance.class);
        final Root<PlanInstance> sti = cq.from(PlanInstance.class);
        cq.select(sti).where(cb.equal(sti.get("correlationId"), correlationIdParameter));
        // Create a TypedQuery
        final TypedQuery<PlanInstance> q = em.createQuery(cq);
        q.setParameter(correlationIdParameter, correlationId);
        // Execute
        PlanInstance result = q.getSingleResult();
        initializeInstance(result);
        return result;
    }
}
 

@Transactional
private List<SecurityGroupFailureHostVO> takeFailureHosts() {
    String sql = "select sgf from SecurityGroupFailureHostVO sgf, HostVO host where host.uuid = sgf.hostUuid and host.status = :hostConnectionState and sgf.managementNodeId is NULL group by sgf.hostUuid order by sgf.lastOpDate ASC";
    TypedQuery<SecurityGroupFailureHostVO> q = dbf.getEntityManager().createQuery(sql, SecurityGroupFailureHostVO.class);
    q.setLockMode(LockModeType.PESSIMISTIC_READ);
    q.setParameter("hostConnectionState", HostStatus.Connected);
    q.setMaxResults(failureHostEachTimeTake);
    List<SecurityGroupFailureHostVO> lst = q.getResultList();
    if (lst.isEmpty()) {
        return lst;
    }

    List<Long> ids = CollectionUtils.transformToList(lst, new Function<Long, SecurityGroupFailureHostVO>() {
        @Override
        public Long call(SecurityGroupFailureHostVO arg) {
            return arg.getId();
        }
    });

    sql = "update SecurityGroupFailureHostVO f set f.managementNodeId = :mgmtId where f.id in (:ids)";
    Query uq = dbf.getEntityManager().createQuery(sql);
    uq.setParameter("mgmtId", Platform.getManagementServerId());
    uq.setParameter("ids", ids);
    uq.executeUpdate();
    return lst;
}
 

@Override
public EnforcementJob getByAgreementId(String agreementId) {

    TypedQuery<EnforcementJob> query = entityManager.createNamedQuery(EnforcementJob.QUERY_FIND_BY_AGREEMENT_ID,EnforcementJob.class);
    query.setParameter("agreementId", agreementId);

    EnforcementJob result;
    try {
        result = query.getSingleResult();

    } catch (NoResultException e) {
        logger.debug("Null will returned due to no Result found: " + e);
        return null;
    }

    return result;
}
 

@Override
public List<Uom> getUnitMeasure() {
	EntityManager em = entityManagerInventory.createEntityManager();
	String sql = "SELECT u FROM Uom u";
	TypedQuery<Uom> query = em.createQuery(sql, Uom.class);
		
	return  query.getResultList();
}
 

@Test
public void test6() {
    // the default behavior is to erase the whole DB after the test has been executed. This way
    // the query should return an empty result set

    final TypedQuery<Depositor> query = manager.createQuery("SELECT d FROM Depositor d WHERE d.name='Max'", Depositor.class);

    assertTrue(query.getResultList().isEmpty());
}
 

public List<Category> getAllCategories(boolean withSub){

        TypedQuery<Category> query = em.createQuery("select c from Category c", Category.class);
        List<Category> categories = query.getResultList();

        if(withSub){
            //force loading
            categories.forEach(c -> c.getSubCategories().size());
        }

        return categories;
    }
 

@PUT
@Path("/{id:[0-9][0-9]*}")
@Consumes("application/json")
public Response update(@PathParam("id") Long id, PerformanceDTO dto)
{
   TypedQuery<Performance> findByIdQuery = em.createQuery("SELECT DISTINCT p FROM Performance p LEFT JOIN FETCH p.show WHERE p.id = :entityId ORDER BY p.id", Performance.class);
   findByIdQuery.setParameter("entityId", id);
   Performance entity;
   try
   {
      entity = findByIdQuery.getSingleResult();
   }
   catch (NoResultException nre)
   {
      entity = null;
   }
   entity = dto.fromDTO(entity, em);
   try
   {
      entity = em.merge(entity);
   }
   catch (OptimisticLockException e)
   {
      return Response.status(Response.Status.CONFLICT).entity(e.getEntity()).build();
   }
   return Response.noContent().build();
}
 

@Override
@Transactional(readOnly = true)
public List<AuthProfile> findAll() {
    TypedQuery<AuthProfile> query = entityManager().createQuery(
        "SELECT e FROM " + JPAAuthProfile.class.getSimpleName() + " e ",
        AuthProfile.class);
    return query.getResultList();
}
 

@Override
public <T> TypedQuery<T> createNamedQuery(String name, Class<T> resultClass) {
    checkBlocking();
    try (EntityManagerResult emr = getEntityManager()) {
        return emr.em.createNamedQuery(name, resultClass);
    }
}
 

@Test
public void test4() {
    // Just to verify the surname update has been persisted
    final TypedQuery<Depositor> query = manager.createQuery("SELECT d FROM Depositor d WHERE d.surname='Doe'", Depositor.class);
    final Depositor entity = query.getSingleResult();

    assertNotNull(entity);
    assertThat(entity.getName(), equalTo("Foo"));
    assertThat(entity.getSurname(), equalTo("Doe"));
}
 

@Override
public List<User> findAllByLastName(String username) {
    CriteriaBuilder builder= entityManager.getCriteriaBuilder();
    CriteriaQuery<User> query = builder.createQuery(User.class);
    Root<User> userRoot = query.from(User.class);
    ParameterExpression<String> value = builder.parameter(String.class);
    query.select(userRoot).where(builder.equal(userRoot.get("lastName"), value));

    TypedQuery<User> tquery = entityManager.createQuery(query);
    tquery.setParameter(value,username);
    return tquery.getResultList();
}
 

@PUT
@Path("/{id:[0-9][0-9]*}")
@Consumes("application/json")
public Response update(@PathParam("id") Long id, SectionAllocationDTO dto)
{
   TypedQuery<SectionAllocation> findByIdQuery = em.createQuery("SELECT DISTINCT s FROM SectionAllocation s LEFT JOIN FETCH s.performance LEFT JOIN FETCH s.section WHERE s.id = :entityId ORDER BY s.id", SectionAllocation.class);
   findByIdQuery.setParameter("entityId", id);
   SectionAllocation entity;
   try
   {
      entity = findByIdQuery.getSingleResult();
   }
   catch (NoResultException nre)
   {
      entity = null;
   }
   entity = dto.fromDTO(entity, em);
   try
   {
      entity = em.merge(entity);
   }
   catch (OptimisticLockException e)
   {
      return Response.status(Response.Status.CONFLICT).entity(e.getEntity()).build();
   }
   return Response.noContent().build();
}
 

public Equipment findEquipment(String cimId) {
    Principal cPrincipal = getCallerPrincipal();
    TypedQuery<Equipment> query = em.createQuery(
            "SELECT m FROM Equipment m WHERE m.cimId = :arg1",
            Equipment.class);
    query.setParameter("arg1", cimId);
    List<Equipment> res = query.getResultList();
    return res.size() > 0 ? res.get(0) : null;
}
 

@Transactional(readOnly = true)
private void handle(VolumeSnapshotReportPrimaryStorageCapacityUsageMsg msg) {
    String sql = "select sum(sp.size)" +
            " from VolumeSnapshotVO sp" +
            " where sp.type = :sptype" +
            " and sp.primaryStorageUuid = :prUuid";
    TypedQuery<Long> q = dbf.getEntityManager().createQuery(sql, Long.class);
    q.setParameter("sptype", VolumeSnapshotConstant.HYPERVISOR_SNAPSHOT_TYPE.toString());
    q.setParameter("prUuid", msg.getPrimaryStorageUuid());
    Long size = q.getSingleResult();

    VolumeSnapshotReportPrimaryStorageCapacityUsageReply reply = new VolumeSnapshotReportPrimaryStorageCapacityUsageReply();
    reply.setUsedSize(size == null ? 0 : size);
    bus.reply(msg, reply);
}
 

@SuppressWarnings("unchecked")
public List<Tblregistration> getPendingApp() {
	String qlString = "SELECT b FROM Tblregistration b";
	TypedQuery<Tblregistration> query = entityManagerFactory.createQuery(qlString, Tblregistration.class);
	return query.getResultList();
	
	
}
 

@Transactional
protected  String getHostNameFromImageInventory(ImageInventory img) {
    String sql="select mon.hostname from CephBackupStorageMonVO mon, ImageBackupStorageRefVO ref where " +
            "ref.imageUuid= :uuid and ref.backupStorageUuid = mon.backupStorageUuid";
    TypedQuery<String> q = dbf.getEntityManager().createQuery(sql, String.class);
    q.setParameter("uuid", img.getUuid());
    return q.getSingleResult();
}
 

@Test
@Cleanup
public void test2() {
    final TypedQuery<Person> query = manager.createQuery("SELECT p FROM Person p WHERE p.name='Max'", Person.class);
    final Person entity = query.getSingleResult();

    assertNotNull(entity);
    assertThat(entity.getName(), equalTo("Max"));
    assertThat(entity.getSurname(), equalTo("Payne"));

    assertThat(entity.getExpertiseIn(), hasItems(new Technology("All kinds of weapons"), new Technology("Detective work")));
    assertThat(entity.getFriends(), hasItems(new Person("Alex", "Balder")));
}