List of usage examples for org.hibernate SQLQuery setProperties
@Override
NativeQuery<T> setProperties(Map bean);
From source file:com.square.adherent.noyau.dao.implementations.prestation.DecompteDaoImpl.java
License:Open Source License
/** * Crer la requte HQL et ajoute les critres. * @param select clause select de la requte * @param criteresPagines les critres de recherche * @param orderBy true pour ajouter la clause order by la requte. * @return requte HQL/*from w w w.j a v a 2 s .c o m*/ */ private Query creerQuery(String select, RemotePagingCriteriasDto<PrestationCriteresRechercheDto> criteresPagines, boolean orderBy, boolean groupBy) { final SQLQuery query; Map<String, Object> params = null; final StringBuffer requete = new StringBuffer(select); requete.append(" from data_decompte d"); requete.append(" left join dim_decompte_acte acte on (acte.decompte_acte_uid = d.decompte_lig_acte_uid) "); requete.append( " left join dim_decompte_origine origine on (origine.decompte_origine_uid = d.decompte_origine_uid) "); requete.append(" left join data_banque banque on (banque.banque_uid = d.decompte_reglement_banque_uid) "); if (groupBy) { requete.append( " left join data_personne_physique bs on (bs.personne_uid = d.decompte_lig_patient_uid) "); } requete.append(" where 1 = 1"); final PrestationCriteresRechercheDto criteres = criteresPagines.getCriterias(); if (criteres != null) { params = new HashMap<String, Object>(); if (criteres.getIdAssure() != null) { final String param = "uidAssure"; requete.append(" and d.decompte_assure_uid = :" + param); params.put(param, criteres.getIdAssure()); } if (criteres.getIdBeneficiaire() != null) { final String param = "uidLigPatient"; requete.append(" and d.decompte_lig_patient_uid = :" + param); params.put(param, criteres.getIdBeneficiaire()); } if (criteres.getNumero() != null) { final String param = "numero"; requete.append(" and d.decompte_numero like :" + param); params.put(param, criteres.getNumero() + "%"); } if (criteres.getNumeroDecompteExact() != null) { final String param = "numero"; requete.append(" and d.decompte_numero = :" + param); params.put(param, criteres.getNumeroDecompteExact()); } if (criteres.getDateDebutSoins() != null) { final String param = "dateDebut"; requete.append(" and d.decompte_lig_date_debut_soin >= :" + param); params.put(param, criteres.getDateDebutSoins()); } if (criteres.getDateFinSoins() != null) { final String param = "dateFin"; requete.append(" and d.decompte_lig_date_debut_soin <= :" + param); params.put(param, criteres.getDateFinSoins()); } if (criteres.getIdOrigine() != null) { final String param = "uidOrigine"; requete.append(" and origine.decompte_origine_uid = :" + param); params.put(param, criteres.getIdOrigine()); } if (criteres.getIdActes() != null && criteres.getIdActes().size() > 0) { final String param = "listeUidActe"; requete.append(" and acte.decompte_acte_uid in (:" + param + ")"); params.put(param, criteres.getIdActes()); } if (criteres.getIdActesAExclure() != null && criteres.getIdActesAExclure().size() > 0) { final String param = "listeUidActe"; requete.append(" and acte.decompte_acte_uid not in (:" + param + ")"); params.put(param, criteres.getIdActesAExclure()); } } if (groupBy) { requete.append( " group by d.decompte_numero, d.decompte_date_reglement, origine.decompte_origine_uid, origine.decompte_origine_lib, "); requete.append( " banque.banque_compte, banque.banque_domiciliation, d.decompte_destinataire_nom, d.decompte_benef_reglement_nom, "); requete.append( " d.decompte_reglement_professionnel_sante, d.decompte_reglement_numero_cheque, d.decompte_statut_paiement,"); requete.append(" bs.personne_uid, bs.personne_nom, bs.personne_prenom"); } if (orderBy) { query = createSqlQuery(requete.toString(), criteresPagines); } else { query = createSqlQuery(requete.toString()); } if (criteres != null) { query.setProperties(params); } query.addSynchronizedEntityClass(Decompte.class); logger.debug(query.toString()); return query; }
From source file:com.yize.broadcast.core.hibernate.SimpleHibernateDao.java
License:Apache License
/** * ?HQL?Query. find()???.//w w w . j a v a 2 s .c o m * * @param values * ???,??. */ public SQLQuery createSQLQuery(final String queryString, final Map<String, ?> values) { Assert.hasText(queryString, "queryString?"); SQLQuery query = getSession().createSQLQuery(queryString); if (values != null) { query.setProperties(values); } return query; }
From source file:com.yougou.api.service.impl.ApiKeyServiceImpl.java
@Override public PageFinder<ApiKey> queryApiKey(ApiKeyMetadata apiKeyMetadata, Query query) throws Exception { StringBuilder sqlPrefixBuilder = new StringBuilder(); sqlPrefixBuilder.append(" select "); sqlPrefixBuilder.append(// w ww .j a v a 2 s . c o m " t1.id, t1.app_key as appKey, t1.app_secret as appSecret, t1.status, t1.update_user as updateUser, t1.update_time as updateTime "); StringBuilder sqlSuffixBuilder = new StringBuilder(); sqlSuffixBuilder.append(" from "); sqlSuffixBuilder.append(" tbl_merchant_api_key t1 "); sqlSuffixBuilder.append(" left join "); sqlSuffixBuilder.append(" tbl_merchant_api_key_metadata t2 "); sqlSuffixBuilder.append(" on(t1.id = t2.key_id) "); sqlSuffixBuilder.append(" left join "); sqlSuffixBuilder.append(" ( "); sqlSuffixBuilder.append(apiKeyDao.getApiKeyPotentialCustomersSqlStatement()); sqlSuffixBuilder.append(" ) t3 "); sqlSuffixBuilder.append(" on (t2.metadata_val = t3.metadata_val) "); sqlSuffixBuilder.append(" where 1 = 1 "); // ? Map<String, Object> properties = new HashMap<String, Object>(); if (apiKeyMetadata != null) { if (StringUtils.isNotBlank(apiKeyMetadata.getMetadataTag())) { sqlSuffixBuilder.append(" and t3.metadata_tag like :metadata_tag"); properties.put("metadata_tag", '%' + apiKeyMetadata.getMetadataTag() + '%'); } if (apiKeyMetadata.getMetadataKey() != null) { sqlSuffixBuilder.append(" and t2.metadata_key = :metadata_key "); properties.put("metadata_key", apiKeyMetadata.getMetadataKey().name()); } if (StringUtils.isNotBlank(apiKeyMetadata.getMetadataVal())) { sqlSuffixBuilder.append(" and t2.metadata_val = :metadata_val "); properties.put("metadata_val", apiKeyMetadata.getMetadataVal()); } if (apiKeyMetadata.getApiKey() != null) { if (apiKeyMetadata.getApiKey().getStatus() != null) { sqlSuffixBuilder.append(" and t1.status = :status "); properties.put("status", apiKeyMetadata.getApiKey().getStatus().ordinal()); } } } Session session = null; PageFinder<ApiKey> pageFinder = null; try { session = apiKeyDao.getHibernateSession(); SQLQuery sqlQuery = session.createSQLQuery("select count(1) " + sqlSuffixBuilder.toString()); sqlQuery.setProperties(properties); int rowCount = ((Number) sqlQuery.uniqueResult()).intValue(); pageFinder = new PageFinder<ApiKey>(query.getPage(), query.getPageSize(), rowCount); if (rowCount > 0) { // ? sqlSuffixBuilder.append(" group by "); sqlSuffixBuilder .append(" t1.id, t1.app_key, t1.app_secret, t1.status, t1.update_user, t1.update_time "); sqlSuffixBuilder.append(" order by "); sqlSuffixBuilder.append(" t1.update_time desc "); // ?APP sqlQuery = session.createSQLQuery(sqlPrefixBuilder.append(sqlSuffixBuilder).toString()); sqlQuery.setFirstResult(pageFinder.getStartOfPage()); sqlQuery.setMaxResults(pageFinder.getPageSize()); sqlQuery.setProperties(properties); sqlQuery.addScalar("id", Hibernate.STRING); sqlQuery.addScalar("appKey", Hibernate.STRING); sqlQuery.addScalar("appSecret", Hibernate.STRING); sqlQuery.addScalar("status", customApiKeyStatus); sqlQuery.addScalar("updateTime", Hibernate.STRING); sqlQuery.addScalar("updateUser", Hibernate.STRING); sqlQuery.setResultTransformer(Transformers.aliasToBean(ApiKey.class)); List<ApiKey> merchantApiKeys = sqlQuery.list(); // ?APP? sqlPrefixBuilder.setLength(0); sqlPrefixBuilder.append(" select "); sqlPrefixBuilder.append( " t1.metadata_key as metadataKey, t1.metadata_val as metadataVal, t2.metadata_tag as metadataTag "); sqlPrefixBuilder.append(" from "); sqlPrefixBuilder.append(" tbl_merchant_api_key_metadata t1 "); sqlPrefixBuilder.append(" left join "); sqlPrefixBuilder.append(" ( "); sqlPrefixBuilder.append(apiKeyDao.getApiKeyPotentialCustomersSqlStatement()); sqlPrefixBuilder.append(" ) t2 "); sqlPrefixBuilder.append(" on(t1.metadata_val = t2.metadata_val) "); sqlPrefixBuilder.append(" where "); sqlPrefixBuilder.append(" t1.key_id = ? "); sqlQuery = session.createSQLQuery(sqlPrefixBuilder.toString()); sqlQuery.addScalar("metadataKey", customAppType); sqlQuery.addScalar("metadataVal", Hibernate.STRING); sqlQuery.addScalar("metadataTag", Hibernate.STRING); sqlQuery.setResultTransformer(Transformers.aliasToBean(ApiKeyMetadata.class)); for (ApiKey merchantApiKey : merchantApiKeys) { sqlQuery.setParameter(0, merchantApiKey.getId()); merchantApiKey.setApiKeyMetadatas(new HashSet<ApiKeyMetadata>(sqlQuery.list())); } pageFinder.setData(merchantApiKeys); } } finally { apiKeyDao.releaseHibernateSession(session); } return pageFinder; }
From source file:org.ambraproject.annotation.service.AnnotationServiceImpl.java
License:Apache License
/** * Get a list of all annotations satisfying the given criteria. * * @param startDate search for annotation after start date. * @param endDate is the date to search until. If null, search until present date * @param annotTypes List of annotation types * @param maxResults the maximum number of results to return, or 0 for no limit * @param journal journalName//from w ww .ja v a2 s.c om * @return the (possibly empty) list of article annotations. * @throws ParseException if any of the dates or query could not be parsed * @throws URISyntaxException if an element of annotType cannot be parsed as a URI */ @Override @Transactional(readOnly = true) @SuppressWarnings("unchecked") public List<AnnotationView> getAnnotations(final Date startDate, final Date endDate, final Set<String> annotTypes, final int maxResults, final String journal) throws ParseException, URISyntaxException { /*** * There may be a more efficient way to do this other than querying the database twice, at some point in time * we might improve how hibernate does the object mappings * * This execute returns annotationIDs, article DOIs and titles, which are needed to construction the annotionView * object */ Map<Long, String[]> results = (Map<Long, String[]>) hibernateTemplate.execute(new HibernateCallback() { @Override public Object doInHibernate(Session session) throws HibernateException, SQLException { /** * We have to do this with SQL because of how the mappings are currently defined * And hence, there is no way to unit test this */ StringBuilder sqlQuery = new StringBuilder(); Map<String, Object> params = new HashMap<String, Object>(3); sqlQuery.append("select ann.annotationID, art.doi, art.title "); sqlQuery.append("from annotation ann "); sqlQuery.append("join article art on art.articleID = ann.articleID "); sqlQuery.append("join Journal j on art.eIssn = j.eIssn "); sqlQuery.append("where j.journalKey = :journal "); params.put("journal", journal); if (startDate != null) { sqlQuery.append(" and ann.created > :startDate"); params.put("startDate", startDate); } if (endDate != null) { sqlQuery.append(" and ann.created < :endDate"); params.put("endDate", endDate); } if (annotTypes != null) { sqlQuery.append(" and ann.type in (:annotTypes)"); params.put("annotTypes", annotTypes); } sqlQuery.append(" order by ann.created desc"); SQLQuery query = session.createSQLQuery(sqlQuery.toString()); query.setProperties(params); if (maxResults > 0) { query.setMaxResults(maxResults); } List<Object[]> tempResults = query.list(); Map<Long, String[]> results = new HashMap<Long, String[]>(tempResults.size()); for (Object[] obj : tempResults) { //This forces this method to return Long values and not BigInteger results.put((((Number) obj[0]).longValue()), new String[] { (String) obj[1], (String) obj[2] }); } return results; } }); //The previous query puts annotationID and doi into the map. annotationID is key //I do this to avoid extra doi lookups later in the code. if (results.size() > 0) { DetachedCriteria criteria = DetachedCriteria.forClass(Annotation.class) .add(Restrictions.in("ID", results.keySet())).addOrder(Order.desc("created")) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); List<Annotation> annotations = hibernateTemplate.findByCriteria(criteria); List<AnnotationView> views = new ArrayList<AnnotationView>(annotations.size()); for (Annotation ann : annotations) { String articleDoi = results.get(ann.getID())[0]; String articleTitle = results.get(ann.getID())[1]; views.add(buildAnnotationView(ann, articleDoi, articleTitle, false)); } return views; } else { return new ArrayList<AnnotationView>(); } }
From source file:org.ambraproject.service.annotation.AnnotationServiceImpl.java
License:Apache License
/** * Get a list of all annotations satisfying the given criteria. * * @param startDate search for annotation after start date. * @param endDate is the date to search until. If null, search until present date * @param annotTypes List of annotation types * @param maxResults the maximum number of results to return, or 0 for no limit * @param journal journalName//from w w w . j a v a 2s .co m * @return the (possibly empty) list of article annotations. * @throws ParseException if any of the dates or query could not be parsed * @throws URISyntaxException if an element of annotType cannot be parsed as a URI */ @Override @Transactional(readOnly = true) @SuppressWarnings("unchecked") public List<AnnotationView> getAnnotations(final Date startDate, final Date endDate, final Set<String> annotTypes, final int maxResults, final String journal) throws ParseException, URISyntaxException { /*** * There may be a more efficient way to do this other than querying the database twice, at some point in time * we might improve how hibernate does the object mappings * * This execute returns annotationIDs, article DOIs and titles, which are needed to construction the annotionView * object */ Map<Long, String[]> results = (Map<Long, String[]>) hibernateTemplate.execute(new HibernateCallback() { @Override public Object doInHibernate(Session session) throws HibernateException, SQLException { /** URIGen * We have to do this with SQL because of how the mappings are currently defined * And hence, there is no way to unit test this */ StringBuilder sqlQuery = new StringBuilder(); Map<String, Object> params = new HashMap<String, Object>(3); sqlQuery.append("select ann.annotationID, art.doi, art.title "); sqlQuery.append("from annotation ann "); sqlQuery.append("join article art on art.articleID = ann.articleID "); sqlQuery.append("join journal j on art.eIssn = j.eIssn "); sqlQuery.append("where j.journalKey = :journal "); params.put("journal", journal); if (startDate != null) { sqlQuery.append(" and ann.created > :startDate"); params.put("startDate", startDate); } if (endDate != null) { sqlQuery.append(" and ann.created < :endDate"); params.put("endDate", endDate); } if (annotTypes != null) { sqlQuery.append(" and ann.type in (:annotTypes)"); params.put("annotTypes", annotTypes); } sqlQuery.append(" order by ann.created desc"); SQLQuery query = session.createSQLQuery(sqlQuery.toString()); query.setProperties(params); if (maxResults > 0) { query.setMaxResults(maxResults); } List<Object[]> tempResults = query.list(); Map<Long, String[]> results = new HashMap<Long, String[]>(tempResults.size()); for (Object[] obj : tempResults) { //This forces this method to return Long values and not BigInteger results.put((((Number) obj[0]).longValue()), new String[] { (String) obj[1], (String) obj[2] }); } return results; } }); //The previous query puts annotationID and doi into the map. annotationID is key //I do this to avoid extra doi lookups later in the code. if (results.size() > 0) { DetachedCriteria criteria = DetachedCriteria.forClass(Annotation.class) .add(Restrictions.in("ID", results.keySet())).addOrder(Order.desc("created")) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); List<Annotation> annotations = (List<Annotation>) hibernateTemplate.findByCriteria(criteria); List<AnnotationView> views = new ArrayList<AnnotationView>(annotations.size()); for (Annotation ann : annotations) { String articleDoi = results.get(ann.getID())[0]; String articleTitle = results.get(ann.getID())[1]; views.add(buildAnnotationView(ann, articleDoi, articleTitle, false)); } return views; } else { return new ArrayList<AnnotationView>(); } }
From source file:org.ambraproject.service.trackback.TrackbackServiceImpl.java
License:Apache License
@Override @Transactional(readOnly = true)// w w w. j a va 2 s . c om @SuppressWarnings("unchecked") public List<LinkbackView> getTrackbacks(final Date startDate, final Date endDate, final int maxResults, final String journal) { /*** * There may be a more efficient way to do this other than querying the database twice, at some point in time * we might improve how hibernate does the object mappings * * This execute returns trackbackIDs, article DOIs and titles, which are needed to construction the trackbackView * object */ Map<Long, String[]> results = (Map<Long, String[]>) hibernateTemplate.execute(new HibernateCallback() { @Override public Object doInHibernate(Session session) throws HibernateException, SQLException { /** * We have to do this with SQL because of how the mappings are currently defined * And hence, there is no way to unit test this */ StringBuilder sqlQuery = new StringBuilder(); Map<String, Object> params = new HashMap<String, Object>(3); sqlQuery.append("select track.trackbackID, art.doi, art.title "); sqlQuery.append("from trackback track "); sqlQuery.append("join article art on art.articleID = track.articleID "); sqlQuery.append("join journal j on art.eIssn = j.eIssn "); sqlQuery.append("where j.journalKey = :journal "); params.put("journal", journal); if (startDate != null) { sqlQuery.append(" and track.created > :startDate"); params.put("startDate", startDate); } if (endDate != null) { sqlQuery.append(" and track.created < :endDate"); params.put("endDate", endDate); } sqlQuery.append(" order by track.created desc"); SQLQuery query = session.createSQLQuery(sqlQuery.toString()); query.setProperties(params); if (maxResults > 0) { query.setMaxResults(maxResults); } List<Object[]> tempResults = query.list(); Map<Long, String[]> results = new HashMap<Long, String[]>(tempResults.size()); for (Object[] obj : tempResults) { //This forces this method to return Long values and not BigInteger results.put((((Number) obj[0]).longValue()), new String[] { (String) obj[1], (String) obj[2] }); } return results; } }); //The previous query puts annotationID and doi into the map. annotationID is key //I do this to avoid extra doi lookups later in the code. if (results.size() > 0) { DetachedCriteria criteria = DetachedCriteria.forClass(org.ambraproject.models.Trackback.class) .add(Restrictions.in("ID", results.keySet())).addOrder(Order.desc("created")); List<org.ambraproject.models.Trackback> trackbacks = (List<Trackback>) hibernateTemplate .findByCriteria(criteria); List<LinkbackView> views = new ArrayList<LinkbackView>(trackbacks.size()); for (org.ambraproject.models.Trackback track : trackbacks) { String articleDoi = results.get(track.getID())[0]; String articleTitle = results.get(track.getID())[1]; views.add(new LinkbackView(track, articleDoi, articleTitle)); } return views; } else { return new ArrayList<LinkbackView>(); } }
From source file:org.ambraproject.trackback.TrackbackServiceImpl.java
License:Apache License
@Override @Transactional(readOnly = true)/*from w w w . j a va2 s . co m*/ @SuppressWarnings("unchecked") public List<TrackbackView> getTrackbacks(final Date startDate, final Date endDate, final int maxResults, final String journal) { /*** * There may be a more efficient way to do this other than querying the database twice, at some point in time * we might improve how hibernate does the object mappings * * This execute returns trackbackIDs, article DOIs and titles, which are needed to construction the trackbackView * object */ Map<Long, String[]> results = (Map<Long, String[]>) hibernateTemplate.execute(new HibernateCallback() { @Override public Object doInHibernate(Session session) throws HibernateException, SQLException { /** * We have to do this with SQL because of how the mappings are currently defined * And hence, there is no way to unit test this */ StringBuilder sqlQuery = new StringBuilder(); Map<String, Object> params = new HashMap<String, Object>(3); sqlQuery.append("select track.trackbackID, art.doi, art.title "); sqlQuery.append("from trackback track "); sqlQuery.append("join article art on art.articleID = track.articleID "); sqlQuery.append("join Journal j on art.eIssn = j.eIssn "); sqlQuery.append("where j.journalKey = :journal "); params.put("journal", journal); if (startDate != null) { sqlQuery.append(" and track.created > :startDate"); params.put("startDate", startDate); } if (endDate != null) { sqlQuery.append(" and track.created < :endDate"); params.put("endDate", endDate); } sqlQuery.append(" order by track.created desc"); SQLQuery query = session.createSQLQuery(sqlQuery.toString()); query.setProperties(params); if (maxResults > 0) { query.setMaxResults(maxResults); } List<Object[]> tempResults = query.list(); Map<Long, String[]> results = new HashMap<Long, String[]>(tempResults.size()); for (Object[] obj : tempResults) { //This forces this method to return Long values and not BigInteger results.put((((Number) obj[0]).longValue()), new String[] { (String) obj[1], (String) obj[2] }); } return results; } }); //The previous query puts annotationID and doi into the map. annotationID is key //I do this to avoid extra doi lookups later in the code. if (results.size() > 0) { DetachedCriteria criteria = DetachedCriteria.forClass(org.ambraproject.models.Trackback.class) .add(Restrictions.in("ID", results.keySet())).addOrder(Order.desc("created")); List<org.ambraproject.models.Trackback> trackbacks = hibernateTemplate.findByCriteria(criteria); List<TrackbackView> views = new ArrayList<TrackbackView>(trackbacks.size()); for (org.ambraproject.models.Trackback track : trackbacks) { String articleDoi = results.get(track.getID())[0]; String articleTitle = results.get(track.getID())[1]; views.add(new TrackbackView(track, articleDoi, articleTitle)); } return views; } else { return new ArrayList<TrackbackView>(); } }