Java Query類代碼示例

import javax.persistence.Query; //導入依賴的package包/類
/**
 * 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;
  }
}
 

import javax.persistence.Query; //導入依賴的package包/類
/**
 * 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;
}
 

import javax.persistence.Query; //導入依賴的package包/類
/**
 * 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;
}
 

import javax.persistence.Query; //導入依賴的package包/類
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ı");
         }
    }
    
}
 

import javax.persistence.Query; //導入依賴的package包/類
/**
 * @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();
    }
}
 

import javax.persistence.Query; //導入依賴的package包/類
/**
 * 根據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;
}
 

import javax.persistence.Query; //導入依賴的package包/類
/**
 * 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;
}
 

import javax.persistence.Query; //導入依賴的package包/類
@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();
}
 

import javax.persistence.Query; //導入依賴的package包/類
/**
 * 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");
    }
}
 

import javax.persistence.Query; //導入依賴的package包/類
@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;
}
 

import javax.persistence.Query; //導入依賴的package包/類
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;
        }
    });
}
 

import javax.persistence.Query; //導入依賴的package包/類
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;
        }
    });
}
 

import javax.persistence.Query; //導入依賴的package包/類
@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;
        }
    }

}
 

import javax.persistence.Query; //導入依賴的package包/類
/**
 * 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;
}
 

import javax.persistence.Query; //導入依賴的package包/類
@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);
    }
}