Java 类javax.persistence.Query 实例源码

/**
 * Finds the most recently started job history record matching the given jobName/env1/env2 which is no older
 * than maxAge.  Null if none found.
 */
public JobHistory findLastRelevantJobHistory(String jobName, String env1, String env2, long maxAge)
{
  String queryString = "SELECT jh FROM " + JobHistory.class.getSimpleName() + " jh WHERE "
      + "jh.jobName = '" + jobName + "' "
      + "AND jh.env1 = '" + env1 + "' "
      + "AND " + makeNullableCondition("jh.env2", env2) + " "
      + "AND jh.startTime > :oldestAllowedStartTime "
      + "ORDER BY jh.startTime DESC ";
  Query query = entityManager.createQuery(queryString);
  query.setParameter("oldestAllowedStartTime", makeTimestampBeforeNow(maxAge));
  query.setMaxResults(1);
  List<JobHistory> results = query.getResultList();
  if (results != null && results.size() > 0)
  {
    return results.get(0);
  }
  else
  {
    return null;
  }
}

/**
 * Verifies if there is a pending user registration process with the
 * specified user identifier. Throws an
 * <code>IllegalArgumentException</code> if the subscription identifier is
 * <code>null</code>.
 * 
 * @param userId
 *            the user identifier to be registered.
 * @return <code>true</code> if the there is such pending process, otherwise
 *         <code>false</code>.
 */
public boolean isRegisterOwnUserPending(String userId) {
    ArgumentValidator.notNull("userId", userId);
    ArgumentValidator.notEmptyString("userId", userId);

    Query query = ds
            .createNamedQuery("TriggerProcessIdentifier.isRegisterOwnUserPending");
    query.setParameter("pendingStates",
            TriggerProcess.getUnfinishedStatus());
    query.setParameter("triggerType", TriggerType.REGISTER_OWN_USER);
    query.setParameter("orgKeyName",
            TriggerProcessIdentifierName.ORGANIZATION_KEY);
    query.setParameter("orgKey",
            String.valueOf(ds.getCurrentUser().getOrganization().getKey()));
    query.setParameter("userIdName", TriggerProcessIdentifierName.USER_ID);
    query.setParameter("userId", userId);

    return ((Long) query.getSingleResult()).longValue() > 0;
}

/**
 * New old customer statistic method
 *
 * @param startHour start hour
 * @param threshold {@link QueryThreshold} of query
 *                  sum value of threshold hours
 * @param statRange range <em>THRESHOLD</em> number of statistic(NOT hour number)
 * @param probeId   id of probe device
 * @return list of {@link NewOldVo} with size equals to statRange
 */
@Override
public List<NewOldVo> getNewOldStat(int startHour, QueryThreshold threshold, int statRange, String probeId) {
    String isProbeSelected = probeId==null || probeId.isEmpty()? "": "AND wifiProb = :probeId ";
    String sqlQuery = "SELECT wifiProb,DATE_FORMAT(hour,:dateFormat),sum(newCustomer),sum(oldCustomer)" +
            "FROM new_old " +
            "WHERE UNIX_TIMESTAMP(hour) >= (:startHour*3600) " + isProbeSelected+
            " GROUP BY wifiProb,DATE_FORMAT(hour,:dateFormat) " +
            "LIMIT 0,:statRange";
    Query query = entityManager.createNativeQuery(sqlQuery);
    query.setParameter("dateFormat", ThresholdUtil.convertToString(threshold));
    query.setParameter("startHour",startHour);
    if (!isProbeSelected.isEmpty()) query.setParameter("probeId",probeId);
    query.setParameter("statRange",statRange>=1? statRange: 10);
    List resultList = query.getResultList();
    List<NewOldVo> newOldVos = new LinkedList<>();
    for (Object object: resultList) {
        newOldVos.add((NewOldVo) ObjectMapper.arrayToObject(NewOldVo.class,object));
    }
    return newOldVos;
}

private void btn_kontrolActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btn_kontrolActionPerformed
    // TODO add your handling code here:
     EntityManagerFactory emf = Persistence.createEntityManagerFactory("BP2_LAB2PU");
        EntityManager em = emf.createEntityManager();
        Query q = em.createQuery("SELECT m FROM Musteri m");
        List<Musteri> musteriler = q.getResultList();

    for (Musteri musteri : musteriler) {
         Path p= Paths.get("musteriler\\"+musteri.getId()+".txt");
         try {
             p.toRealPath();
         } catch (IOException ex) {
             System.out.println(musteri.getId()+" numaralı müsteri dosyası bulunamadı");
         }
    }

}

/**
 * @return the number of entities updated or deleted
 */
public int executeJpqlQuery(@Nonnull final String queryString, @Nullable final Map<String, Object> parameters)
        throws DatabaseException {
    final EntityManager em = this.databaseConnection.getEntityManager();
    try {
        final Query query = em.createQuery(queryString);
        if (parameters != null) {
            parameters.forEach(query::setParameter);
        }
        em.getTransaction().begin();
        final int updatedOrDeleted = query.executeUpdate();
        em.getTransaction().commit();
        return updatedOrDeleted;
    } catch (final PersistenceException e) {
        final String message = String.format("Failed to execute JPQL query %s with %s parameters on DB %s",
                queryString, parameters != null ? parameters.size() : "null", this.databaseConnection.getName());
        throw new DatabaseException(message, e);
    } finally {
        em.close();
    }
}

/**
 * 根据hql语句查询数据
 * @param hql
 * @return
 */
@SuppressWarnings("rawtypes")
public List queryForList(String hql, List<Object> params){
    Query query = em.createQuery(hql);
    List list = null;
    try {
        if(params != null && !params.isEmpty()){
            for(int i=0,size=params.size();i<size;i++){
                query.setParameter(i+1, params.get(i));
            }
        }
        list = query.getResultList();
    } catch (Exception e) {
        e.printStackTrace();
    }finally{
        em.close();
    }
    return list;
}

/**
 * Getting list of organization to sending info mail about ending discount
 * in one week (seven days).
 * 
 * @param currentTimeMillis
 *            Current millisecond.
 * @return Organization list for sending notification.
 */
@Override
@TransactionAttribute(TransactionAttributeType.MANDATORY)
public List<OrganizationReference> getOrganizationForDiscountEndNotificiation(
        long currentTimeMillis) {

    // define the first and the last millisecond of needed day:
    // define date + 7 days
    long firstMillis = getMillisecondInFuture(currentTimeMillis, 7);
    long lastMillis = getMillisecondInFuture(currentTimeMillis, 8) - 1;

    // getting list of organization to sending info mail about ending
    // discount
    Query query = dm.createNamedQuery(
            "OrganizationReference.findOrganizationForDiscountEndNotification");
    query.setParameter("firstMillis", Long.valueOf(firstMillis));
    query.setParameter("lastMillis", Long.valueOf(lastMillis));
    List<OrganizationReference> list = ParameterizedTypes
            .list(query.getResultList(), OrganizationReference.class);

    return list;
}

@GET
@Produces({"application/xml", "application/json"})
@Path("/recent/region/producttype/{regionName}/{productTypeId}/{orderLineId}")
public List<LiveSalesList> findRecentRegionProductTypeFrom(@PathParam("regionName") String regionName, @PathParam("productTypeId") Integer productTypeId, @PathParam("orderLineId") Integer orderLineId) {
    CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
    javax.persistence.criteria.CriteriaQuery cq = cb.createQuery();
    Root<LiveSalesList> liveSalesList = cq.from(LiveSalesList.class);
    cq.select(liveSalesList);
    cq.where(cb.and(
        cb.equal(liveSalesList.get(LiveSalesList_.productTypeId), productTypeId),
        cb.equal(liveSalesList.get(LiveSalesList_.region), regionName),
        cb.gt(liveSalesList.get(LiveSalesList_.orderLineId), orderLineId)
    ));
    Query q = getEntityManager().createQuery(cq);
    q.setMaxResults(500);
    return q.getResultList();
}

/**
 * Selection of arbitrary sharing stations at specific location via lat/lon coordinates
 *
 * @param lon Longitude of the target location
 * @param lat Latitude of the target location
 * @return A descendant of SharingStation class if exists at target location
 * @throws DatabaseException if no station could be retrieved
 */
@Override
public SharingStation findByCoordinate(Double lon, Double lat, Class<? extends SharingStation> clazz) throws DatabaseException {
    String sql = "SELECT * FROM bikestation WHERE " +
                 "ST_PointFromText('POINT(' || ? || ' ' || ? || ')', 4326) = geopos " +
                 "UNION " +
                 "SELECT * FROM carstation " +
                 "WHERE ST_PointFromText('POINT(' || ? || ' ' || ? || ')', 4326) = geopos;";

    Query q = entityManager.createNativeQuery(sql, clazz);

    q.setParameter(1, lon);
    q.setParameter(2, lat);
    q.setParameter(3, lon);
    q.setParameter(4, lat);
    try {
        return (SharingStation) q.getSingleResult();
    } catch (PersistenceException e) {
        throw new DatabaseException("Unable to find Sharing Station in Database");
    }
}

@Override
@TransactionAttribute(TransactionAttributeType.MANDATORY)
public Set<UserRoleType> getAvailableUserRolesForUser(PlatformUser pu) {

    Query query = dm.createNamedQuery("UserRole.getAllUserRoles");
    List<UserRole> userRoleList = ParameterizedTypes
            .list(query.getResultList(), UserRole.class);
    Organization org = pu.getOrganization();
    Set<UserRoleType> roleList = new HashSet<>();
    for (UserRole userRole : userRoleList) {
        if (isAllowedUserRole(org, userRole.getRoleName())) {
            roleList.add(userRole.getRoleName());
        }
    }

    return roleList;
}

private void updateDiscounts(final BigDecimal discountValue,
        final Long discountStart, final Long discountEnd) throws Exception {
    runTX(new Callable<Void>() {
        @Override
        public Void call() {
            Query query = dm.createQuery("SELECT d FROM Discount d");
            List<Discount> list = ParameterizedTypes
                    .list(query.getResultList(), Discount.class);
            for (Discount discount : list) {
                discount.setEndTime(discountEnd);
                discount.setStartTime(discountStart);
                discount.setValue(discountValue);
            }
            return null;
        }
    });
}

private static Document getBillingResult(final long subscriptionKey,
        final long billingPeriodStart, final long billingPeriodEnd)
        throws Exception {
    return runTX(new Callable<Document>() {
        @Override
        public Document call() throws Exception {

            DataService dataService = container.get(DataService.class);
            Query query = dataService
                    .createNamedQuery("BillingResult.findBillingResult");
            query.setParameter("subscriptionKey",
                    Long.valueOf(subscriptionKey));
            query.setParameter("startPeriod",
                    Long.valueOf(billingPeriodStart));
            query.setParameter("endPeriod", Long.valueOf(billingPeriodEnd));
            BillingResult billingResult = (BillingResult) query
                    .getSingleResult();
            Document doc = XMLConverter.convertToDocument(
                    billingResult.getResultXML(), true);
            return doc;
        }
    });
}

@Override
@TransactionAttribute(TransactionAttributeType.MANDATORY)
public void checkDistinguishedName(Organization organization)
        throws DistinguishedNameException {

    String dn = organization.getDistinguishedName();
    if (dn != null && dn.length() > 0) {
        Query query = dm
                .createNamedQuery("Organization.countOrgsWithSameDN");
        query.setParameter("distinguishedName", dn);
        query.setParameter("organization", organization);
        Long result = (Long) query.getSingleResult();
        if (result.longValue() > 0) {
            DistinguishedNameException e = new DistinguishedNameException();
            logger.logWarn(Log4jLogger.SYSTEM_LOG, e,
                    LogMessageIdentifier.WARN_DUPLICATE_ORG_WITH_DISTINGUISHED_NAME,
                    dn);
            throw e;
        }
    }

}

/**
 * Returns the platform events defined in the system. No platform events are
 * available if the specified technical service is defined for the
 * <code>DIRECT</code> or <code>USER</code> access type.
 * 
 * @param tp
 *            The technical product to retrieve the events for.
 * 
 * @return The platform events.
 */
List<Event> getPlatformEvents(TechnicalProduct tp) {

    List<Event> result = new ArrayList<>();
    Query query = dm.createNamedQuery("Event.getAllPlatformEvents");
    query.setParameter("eventType", EventType.PLATFORM_EVENT);
    result = ParameterizedTypes.list(query.getResultList(), Event.class);
    if (tp.getAccessType() == ServiceAccessType.DIRECT
            || tp.getAccessType() == ServiceAccessType.USER) {
        List<Event> copy = new ArrayList<>(result);
        for (Event ed : copy) {
            if (EventType.PLATFORM_EVENT == ed.getEventType()) {
                result.remove(ed);
            }
        }
    }

    return result;
}

@TransactionAttribute(TransactionAttributeType.REQUIRES_NEW)
public void storeAppConfigurationSettings(HashMap<String, String> settings) throws ConfigurationException, GeneralSecurityException {

    LOGGER.debug("Storing configuration settings for APP platform");
    if (settings == null) {
        throw new IllegalArgumentException("All parameters must be set");
    }
    Query query = em
            .createNamedQuery("ConfigurationSetting.getForController");
    query.setParameter("controllerId", PROXY_ID);
    List<?> resultList = query.getResultList();
    for (Object entry : resultList) {
        ConfigurationSetting setting = (ConfigurationSetting) entry;
        String key = setting.getSettingKey();
        if (settings.containsKey(key)) {
            if (settings.get(key) == null) {
                em.remove(setting);
            } else {
                setting.setDecryptedValue(settings.get(key));
                em.persist(setting);
            }
        }
        settings.remove(key);
    }
}

@Override
public void updateResourceTemplate(final String wsName, final String resourceTemplateName, final String template) {
    final Query q = entityManager.createNamedQuery(JpaWebServerConfigTemplate.UPDATE_WEBSERVER_TEMPLATE_CONTENT);
    q.setParameter("webServerName", wsName);
    q.setParameter("templateName", resourceTemplateName);
    q.setParameter("templateContent", template);

    int numEntities;
    try {
        numEntities = q.executeUpdate();
    } catch (RuntimeException re) {
        LOGGER.error("Error updating resource template {} for web server {}", resourceTemplateName, wsName, re);
        throw new ResourceTemplateUpdateException(wsName, resourceTemplateName, re);
    }

    if (numEntities == 0) {
        LOGGER.error("Error updating resource template numEntities=0 {} for web server {}", resourceTemplateName, wsName);
        throw new ResourceTemplateUpdateException(wsName, resourceTemplateName);
    }
}

/**
 * Returns the first service instance entry found in the database.
 * 
 * @return A service instance.
 */
private ServiceInstance getServiceInstance() throws Exception {
    return runTX(new Callable<ServiceInstance>() {
        @Override
        public ServiceInstance call() throws Exception {
            Query query = em
                    .createQuery("SELECT si FROM ServiceInstance si");
            List<?> resultList = query.getResultList();
            if (resultList.isEmpty()) {
                return null;
            } else {
                return (ServiceInstance) resultList.get(0);
            }
        }
    });
}

public ServiceInstance getInstanceBySubscriptionAndOrganization(
        String subscriptionId, String organizationId)
        throws ServiceInstanceNotFoundException {
    if (Strings.isEmpty(subscriptionId) || Strings.isEmpty(organizationId)) {
        throw new ServiceInstanceNotFoundException(
                "Subscription or organization ID not set or empty.");
    }
    Query query = em
            .createNamedQuery("ServiceInstance.getForSubscriptionAndOrg");
    query.setParameter("subscriptionId", subscriptionId);
    query.setParameter("organizationId", organizationId);
    try {
        final ServiceInstance instance = (ServiceInstance) query
                .getSingleResult();
        return instance;
    } catch (NoResultException e) {
        throw new ServiceInstanceNotFoundException(
                "Service instance for subscription '%s' and organization '%s' not found.",
                subscriptionId, organizationId);
    }
}

/**
 * Find an Employee who is a user by is login.
 * @param login Login of the Employee.
 * @return The Employee with the matching login.
 */
public Employee findByLogin(String login) {
    EntityManager em = HibernateUtil.getEntityManagerFactory().createEntityManager();

    String hql = "from Employee where login = :login";
    Query query = em.createQuery(hql);
    query.setParameter("login", login);

    Employee employee = null;
    try {
         employee = (Employee) query.getSingleResult();
    } catch (NoResultException e) {
        e.printStackTrace();
    }

    em.close();

    return employee;
}

@SuppressWarnings("rawtypes")
public List queryByMapParams(String hql, Map<String, Object> params, Integer currentPage,Integer pageSize){
    //EntityManager em = this.emf.createEntityManager();
    Query query = em.createQuery(hql);
    List list = null;
    try {
        if(params != null && !params.isEmpty()){
            for(Map.Entry<String,Object> entry: params.entrySet()){
                query.setParameter(entry.getKey(), entry.getValue());
            }
        }

        if(currentPage != null && pageSize != null){
            query.setFirstResult((currentPage-1)*pageSize);
            query.setMaxResults(pageSize);
        }
        list = query.getResultList();
    } catch (Exception e) {
        e.printStackTrace();
    }finally{
        em.close();
    }

    return list;
}

@Override
public int update(T entity) {
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaUpdate<T> criteriaUpdate = builder.createCriteriaUpdate(entityClass);
    Root<T> root = criteriaUpdate.from(entityClass);
    Field[] fields = entityClass.getDeclaredFields();
    Object primaryV = null;
    String primaryKey = this.getPrimaryKey();
    for (Field field : fields) {
        ReflectionUtils.makeAccessible(field);
        Object fieldV = ReflectionUtils.getField(field, entity);
        if (fieldV == null)
            continue;
        if (primaryKey.equals(field.getName())) {// 主键不参与修改
            primaryV = fieldV;
        } else {
            criteriaUpdate.set(root.get(field.getName()), fieldV);
        }
    }
    criteriaUpdate.where(builder.equal(root.get(primaryKey), primaryV));
    Query query = entityManager.createQuery(criteriaUpdate);
    return query.executeUpdate();

}

@Override
public SurveyEntity findByCode(String code) {
    logger.log(Level.INFO, "Attempting to find survey '" + code + "'");

    Query query = entityManager.createQuery("select s from survey s where s.code = :code");
    query.setParameter("code", code);
    try {
        return (SurveyEntity) query.getSingleResult();
    } catch (RuntimeException e) {
        logger.log(Level.WARNING, "Error getting survey from database", e);
        return null;
    }
}

/**
 * Retrieves one arbitrary global marketplace for testing purposes.
 * 
 * @param ds
 *            the data service
 * @return a <code>Marketplace</code>
 */
public static Marketplace findOneGlobalMarketplace(DataService ds) {

    Query query = ds.createNamedQuery("Marketplace.getAll");
    List<Marketplace> result = ParameterizedTypes.list(
            query.getResultList(), Marketplace.class);
    Assert.assertNotNull("No global marketplace defined", result);
    Assert.assertTrue("No global marketplace defined", result.size() > 0);
    return result.get(0);
}

public void delete(Long id) {
  Query query = manager
       .createQuery("UPDATE Cargo pro "
                    + "SET pro.deleted = true, "
                    + "pro.deleted_at = :Deleted_at "
                                + "WHERE pro.id_cargo = :id");
query.setParameter("Deleted_at", Calendar.getInstance());
query.setParameter("id", id);
query.executeUpdate();
 }

@Override
public UserEntity findByEmail(String email) {
    Query query = em.createQuery("select u from user u where u.email = :email");
    query.setParameter("email", email);

    try {
        Object queryResult = query.getSingleResult();
        return (UserEntity) queryResult;
    } catch (RuntimeException e) {
        logger.log(Level.WARNING, "Unable to find user " + email, e);
        return null;
    }
}

/**
 * Execute la requete et ramene l'ensemble des elements d'une table
 * 
 * @param className
 *            la class concernée
 * @return la liste d'objet
 * @throws SiScolException
 */
private <T> List<T> executeQueryListEntity(Class<T> className) throws SiScolException {
    try {
        EntityManagerFactory emf = Persistence.createEntityManagerFactory("pun-jpa-siscol");
        EntityManager em = emf.createEntityManager();
        Query query = em.createQuery("Select a from " + className.getName() + " a", className);
        List<T> listeSiScol = query.getResultList();
        em.close();
        return listeSiScol;
    } catch (Exception e) {
        throw new SiScolException("SiScol database error on execute query list entity", e.getCause());
    }
}

private void btn_tutarActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_btn_tutarActionPerformed
        // TODO add your handling code here:


            // TODO add your handling code here:
            EntityManagerFactory emf = Persistence.createEntityManagerFactory("BP2_LAB2PU");
            EntityManager em = emf.createEntityManager();
            Query q = em.createQuery("SELECT m FROM Musteri m");
            List<Musteri> musteriler = q.getResultList();
            for (Musteri musteri : musteriler) {
                q = em.createQuery("SELECT s FROM Satis s WHERE s.idMusteri=:id");
                q.setParameter("id", musteri.getId());
                List<Satis> satislar = q.getResultList();
                int toplam = 0;

                for (Satis satis : satislar) {
                    toplam += satis.getTutar();
                }

                System.out.println(musteri.getId()+" "+toplam);

            }
//            
//            Query q = em.createQuery("SELECT sum(s.tutar) FROM Satis s Group By s.idMusteri ");
//            List<Object> satislar = q.getResultList();
//            for (Object satis : satislar) {
//                System.out.println(""+(Long)satis);
//        }



    }

@Override
public User getByName(String name) {
    Query query = this.entityManager.createQuery("from User u where u.name=:name",User.class);
    query.setParameter("name",name);
    User user = (User)query.getSingleResult();
    return user;
}

@Override
public AclObjectIdentity getObjectIdentity(String type, Serializable identifier) {
    Query query = entityManager.createQuery("select aoi from AclObjectIdentity aoi, AclClass aclClass where  aoi.objIdIdentity = :objIdIdentity and aoi.objIdClass = aclClass and aclClass.clazz = :clazz)");
    query.setParameter("objIdIdentity", identifier);
    query.setParameter("clazz", type);

    return (AclObjectIdentity) query.getSingleResult();
}

/**
 * 根据hql语句和分页条件查找分页数据
 * @param hql
 * @param currentPage
 * @param pageSize
 * @return
 */
@SuppressWarnings({ "rawtypes", "unchecked" })
public PageModel queryForPage(String hql,int currentPage,int pageSize){
    PageModel page = new PageModel();
    List list = null;
    Integer totalCount = 0;
    Integer totalPage = 0; //总页数
    try {
        int firstResult = (currentPage-1)*pageSize;
        Query query = em.createQuery(hql);
        query.setMaxResults(pageSize);
        query.setFirstResult(firstResult);
        list = query.getResultList();

        Query query2 = em.createQuery(hql);
        List list2 = query2.getResultList();
        totalCount = (list2 == null) ? 0 : list2.size();
        if(totalCount % pageSize == 0){
            totalPage = totalCount/pageSize;
        }else{
            totalPage = totalCount/pageSize + 1;
        }

        page.setCurrentPage(currentPage);
        page.setList(list);
        page.setPageSize(pageSize);
        page.setTotalCount(totalCount);
        page.setTotalPage(totalPage);
    } catch (Exception e) {
        e.printStackTrace();
    }finally{
        em.close();
    }       
    return page;
}

public static List<Object> countNbCardsByRejets(String userType, Date date) {
    EntityManager em = Card.entityManager();
    String sql = "SELECT nb_rejets, count(*) FROM card WHERE request_date>=:date GROUP BY nb_rejets";
    if (!userType.isEmpty()) {
        sql = "SELECT nb_rejets, count(*) FROM card, user_account WHERE card.user_account= user_account.id " + "AND request_date>=:date  AND user_type = :userType GROUP BY nb_rejets";
    }
    Query q = em.createNativeQuery(sql);
    q.setParameter("date", date);
    if (!userType.isEmpty()) {
        q.setParameter("userType", userType);
    }
    return q.getResultList();
}

@Before
public void setup() throws Exception {
    query = mock(Query.class);
    service = spy(new PartnerServiceBean());
    service.localizer = mock(LocalizerServiceLocal.class);
    sps = mock(ServiceProvisioningServiceBean.class);
    service.sps = sps;
    service.spsLocal = mock(ServiceProvisioningServiceLocal.class);
    service.ds = mock(DataService.class);
    doReturn(query).when(service.ds).createNamedQuery(
            "Product.getSpecificCustomerProduct");
    service.spsLocalizer = mock(ServiceProvisioningServiceLocalizationLocal.class);
    service.sessionCtx = mock(SessionContext.class);
    service.slService = mock(SubscriptionListServiceLocal.class);
    when(service.ds.getCurrentUser()).thenReturn(new PlatformUser());
    product = new Product();
    product.setStatus(ServiceStatus.INACTIVE);
    doReturn(product).when(service.ds).getReference(eq(Product.class),
            anyLong());

    PlatformUser u = new PlatformUser();
    u.setLocale("locale");
    doReturn(u).when(service.ds).getCurrentUser();
    doReturn(Boolean.FALSE).when(service.slService)
            .isUsableSubscriptionExistForTemplate(any(PlatformUser.class),
                    eq(Subscription.ASSIGNABLE_SUBSCRIPTION_STATUS),
                    any(Product.class));
}

/**
 * retrieves the list of user emails registered.
 * 
 * @return users the list of user emails
 * 
 */
public static List<String> listUsers() {
    EntityManagerFactory emf = Persistence.createEntityManagerFactory("userData");
    EntityManager em = emf.createEntityManager();
    Query query = em.createQuery("SELECT username FROM User");
    @SuppressWarnings("unchecked")
    List<String> users = query.getResultList();
    em.close();
    return users;
}

@Override
public JpaWebServerConfigTemplate getWebServerResource(final String resourceName, final String webServerName) {
    final Query q = em.createNamedQuery(JpaWebServerConfigTemplate.QUERY_GET_WEBSERVER_RESOURCE);
    q.setParameter(JpaWebServerConfigTemplate.QUERY_PARAM_TEMPLATE_NAME, resourceName);
    q.setParameter(JpaWebServerConfigTemplate.QUERY_PARAM_WEBSERVER_NAME, webServerName);
    return (JpaWebServerConfigTemplate) q.getSingleResult();
}

/**
 * Verifies if the specified service was already scheduled and is still
 * pending to be activated or de-activated. Throws an
 * <code>IllegalArgumentException</code> if the specified service is
 * <code>null</code> or its key is smaller than or equals <code>0</code>.
 * 
 * @param service
 *            The service to be checked.
 * @return <code>true</code> if the service was already scheduled and
 *         pending, otherwise <code>false</code>.
 */
public boolean isActivateOrDeactivateServicePending(VOService service) {
    ArgumentValidator.notNull("service", service);
    TriggerProcessIdentifiers.validateObjectKey(service);
    Query query = ds
            .createNamedQuery("TriggerProcessIdentifier.isActivateDeactivateServicePending");
    query.setParameter("pendingStates",
            TriggerProcess.getUnfinishedStatus());
    query.setParameter("triggerTypes", Arrays.asList(
            TriggerType.ACTIVATE_SERVICE, TriggerType.DEACTIVATE_SERVICE));
    query.setParameter("serviceKeyName",
            TriggerProcessIdentifierName.SERVICE_KEY);
    query.setParameter("serviceKey", String.valueOf(service.getKey()));
    return ((Long) query.getSingleResult()).longValue() > 0;
}

private void setMarketplaceReferencesOfSubscriptionsToNull(Marketplace mp) {
    Query query = dm.createNamedQuery("Subscription.getForMarketplace");
    query.setParameter("marketplace", mp);
    List<Subscription> subscriptions = ParameterizedTypes
            .list(query.getResultList(), Subscription.class);
    if (subscriptions != null && !subscriptions.isEmpty()) {
        for (Subscription subscription : subscriptions) {
            subscription.setMarketplace(null);
        }
    }
}

@Override
@TransactionAttribute(TransactionAttributeType.MANDATORY)
public List<Marketplace> getAllAccessibleMarketplacesForOrganization(
        long organizationKey) {

    Query query = ds.createNamedQuery("Marketplace.getAllAccessible");
    query.setParameter("organization_tkey", organizationKey);
    List<Marketplace> marketplaceList = ParameterizedTypes
            .list(query.getResultList(), Marketplace.class);

    return marketplaceList;
}

/**
 * Helper method for event reading.
 * 
 * @param actor
 *            User saving event.
 * @param occurrenceTime
 *            Occurance time.
 * @param subKey
 *            Key of subscription.
 * @param eventType
 *            The type of the event to read
 * @return Event.
 * @throws Exception
 */
private GatheredEvent readEvent(final String actor,
        final long occurrenceTime, final long subKey,
        final EventType eventType) throws Exception {
    GatheredEvent eventLocal = runTX(new Callable<GatheredEvent>() {
        @Override
        public GatheredEvent call() throws Exception {
            long key = -1;
            Query query = mgr.createQuery(
                    "select c from GatheredEvent c where c.dataContainer.actor=:actor and "
                            + "c.dataContainer.occurrenceTime=:occurrencetime and "
                            + "c.dataContainer.subscriptionTKey=:subscriptionTKey and "
                            + "c.dataContainer.type=:type");
            query.setParameter("actor", actor);
            query.setParameter("occurrencetime",
                    Long.valueOf(occurrenceTime));
            query.setParameter("subscriptionTKey", Long.valueOf(subKey));
            query.setParameter("type", eventType);
            Iterator<GatheredEvent> gatheredEventIterator = ParameterizedTypes
                    .iterator(query.getResultList(), GatheredEvent.class);
            if (gatheredEventIterator.hasNext()) {
                key = gatheredEventIterator.next().getKey();
            }
            return mgr.find(GatheredEvent.class, key);
        }
    });
    return eventLocal;
}

public void addAccessibleServices(String unitId,
        List<String> accessibleServices) {
    List<Long> existingInvisibleProductKeys = getExistingInvisibleProductKeys(unitId);

    for (String product : accessibleServices) {
        if (existingInvisibleProductKeys.contains(Long.valueOf(product))) {
            String queryString = "DELETE FROM UserGroupToInvisibleProduct as ug2ip "
                    + "WHERE ug2ip.usergroup_tkey=:unitId AND ug2ip.product_tkey=:productId";
            Query query = dm.createNativeQuery(queryString);
            query.setParameter("productId", Long.valueOf(product));
            query.setParameter("unitId", Long.valueOf(unitId));
            query.executeUpdate();
        }
    }
}

/**
 * @return Map the subscriptionId with the latest valid one
 * */
public Map<String, String> retrieveLastValidSubscriptionIdMap() {
    HashMap<String, String> result = new HashMap<String, String>();
    Query query = ds.createNativeQuery(QUERY_LAST_VALID_SUBID_MAP);
    query.setParameter("status", SubscriptionStatus.DEACTIVATED.name());
    @SuppressWarnings("unchecked")
    List<Object[]> querymap = query.getResultList();
    for (Object[] objs : querymap) {
        result.put((String) objs[0], (String) objs[1]);
    }
    return result;
}