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