List of usage examples for org.hibernate SQLQuery addScalar
SQLQuery<T> addScalar(String columnAlias, Type type);
From source file:gov.nih.nci.cananolab.system.dao.orm.CaNanoLabORMDAOImpl.java
License:BSD License
public List directSQL(String directSQL, String[] columns, Object[] columnTypes) throws DAOException { Session session = getSession();/*w w w.ja v a2s .c o m*/ try { SQLQuery query = session.createSQLQuery(directSQL); for (int i = 0; i < columns.length; i++) { query.addScalar(columns[i], (NullableType) columnTypes[i]); } List results = query.list(); return results; } catch (JDBCException ex) { log.error("JDBC Exception in CustomORMDAOImpl ", ex); throw new DAOException("JDBC Exception in CustomORMDAOImpl ", ex); } catch (org.hibernate.HibernateException hbmEx) { log.error(hbmEx.getMessage()); throw new DAOException("Hibernate problem ", hbmEx); } catch (Exception e) { log.error("Exception ", e); throw new DAOException("Exception in CustomORMDAOImpl ", e); } }
From source file:gov.utah.dts.det.ccl.dao.impl.FacilityDaoImpl.java
License:Open Source License
@Override public List<UserCaseloadCount> getUserCaseloadCounts() { Session session = (Session) em.getDelegate(); SQLQuery query = session.createSQLQuery(CASELOAD_COUNT_QUERY); query.addScalar("id", StandardBasicTypes.LONG); query.addScalar("name", StandardBasicTypes.STRING); query.addScalar("roleType", StandardBasicTypes.STRING); query.addScalar("active", StandardBasicTypes.YES_NO); query.addScalar("count", StandardBasicTypes.LONG); query.setResultTransformer(Transformers.aliasToBean(UserCaseloadCount.class)); return (List<UserCaseloadCount>) query.list(); }
From source file:lt.emasina.resthub.server.factory.DataFactory.java
License:Open Source License
public CcData getData(final Session session, final DataHandler handler) throws Exception { final Query q = handler.getQuery(); final SQLQuery query = getPagedSQLQuery(session, handler); for (MdColumn c : q.getColumns()) { switch (c.getType()) { case BLOB: query.addScalar(c.getName(), new BlobType()); break; case CLOB: query.addScalar(c.getName(), new ClobType()); break; case DATE: query.addScalar(c.getName(), new CalendarType()); break; case NUMBER: query.addScalar(c.getName(), new BigDecimalType()); break; case STRING: query.addScalar(c.getName(), new StringType()); break; }/*from ww w .j a v a2 s . c o m*/ } if (log.isDebugEnabled()) { log.debug(query.getQueryString()); } ExecutorService executor = Executors.newSingleThreadExecutor(); Future<CcData> loopRows = executor.submit(new Callable<CcData>() { @Override @SuppressWarnings("unchecked") public CcData call() throws Exception { CcData cc = new CcData(); for (Object o : query.list()) { cc.addRow(q, o); } return cc; }; }); try { return loopRows.get(q.getTimeOut(), TimeUnit.SECONDS); } catch (ExecutionException | InterruptedException ex) { throw ex; } catch (TimeoutException ex) { throw new ServerErrorException(Status.SERVER_ERROR_GATEWAY_TIMEOUT, ex); } }
From source file:lt.emasina.resthub.server.factory.DataFactory.java
License:Open Source License
public CcLob getLob(final Session session, final LobHandler handler) throws Exception { final Query q = handler.getQuery(); final SQLQuery query = getPagedSQLQuery(session, handler); final MdColumn c = handler.getMdColumn(); switch (c.getType()) { case BLOB://from www.ja va2 s . c o m query.addScalar(c.getName(), new WrapperBinaryType()); break; case CLOB: query.addScalar(c.getName(), new TextType()); break; default: throw new ClientErrorException(Status.CLIENT_ERROR_BAD_REQUEST, "Column %d (%s) expected to be LOB found %s", handler.getColumn(), c.getName(), c.getType().name()); } if (log.isDebugEnabled()) { log.debug(query.getQueryString()); } ExecutorService executor = Executors.newSingleThreadExecutor(); Future<CcLob> fetchData = executor.submit(new Callable<CcLob>() { @Override @SuppressWarnings("unchecked") public CcLob call() throws Exception { CcLob cc = new CcLob(); Object o = query.uniqueResult(); if (o != null) { switch (c.getType()) { case CLOB: cc.setValue((String) o); break; case BLOB: cc.setValue((Byte[]) o); break; } } return cc; }; }); try { return fetchData.get(q.getTimeOut(), TimeUnit.SECONDS); } catch (ExecutionException | InterruptedException ex) { throw ex; } catch (TimeoutException ex) { throw new ServerErrorException(Status.SERVER_ERROR_GATEWAY_TIMEOUT, ex); } }
From source file:nl.strohalm.cyclos.dao.accounts.AccountDAOImpl.java
License:Open Source License
public IteratorList<AccountDailyDifference> iterateDailyDifferences(final MemberAccount account, final Period period) { Map<String, Object> params = new HashMap<String, Object>(); params.put("accountId", account.getId()); QueryParameter beginParameter = HibernateHelper.getBeginParameter(period); QueryParameter endParameter = HibernateHelper.getEndParameter(period); if (beginParameter != null) { params.put("begin", beginParameter.getValue()); }//from w w w .j a v a 2 s .c o m if (endParameter != null) { params.put("end", endParameter.getValue()); } StringBuilder sql = new StringBuilder(); sql.append(" select type, date(d.date) as date, sum(amount) as amount "); sql.append(" from ( "); sql.append(" select 'B' as type, t.process_date as date, "); sql.append(" case when t.chargeback_of_id is null then "); sql.append(" case when t.from_account_id = :accountId then -t.amount else t.amount end "); sql.append(" else "); sql.append(" case when t.to_account_id = :accountId then t.amount else -t.amount end "); sql.append(" end as amount "); sql.append(" from transfers t "); sql.append(" where (t.from_account_id = :accountId or t.to_account_id = :accountId) "); sql.append(" and t.process_date is not null "); if (beginParameter != null) { sql.append(" and t.process_date " + beginParameter.getOperator() + " :begin"); } if (endParameter != null) { sql.append(" and t.process_date " + endParameter.getOperator() + " :end"); } sql.append(" union "); sql.append(" select 'R', r.date, r.amount "); sql.append(" from amount_reservations r "); sql.append(" where r.account_id = :accountId "); if (beginParameter != null) { sql.append(" and r.date " + beginParameter.getOperator() + " :begin"); } if (endParameter != null) { sql.append(" and r.date " + endParameter.getOperator() + " :end"); } sql.append(" ) d "); sql.append(" group by type, date(d.date) "); sql.append(" order by date(d.date) "); SQLQuery query = getSession().createSQLQuery(sql.toString()); query.addScalar("type", StandardBasicTypes.STRING); query.addScalar("date", StandardBasicTypes.CALENDAR_DATE); query.addScalar("amount", StandardBasicTypes.BIG_DECIMAL); getHibernateQueryHandler().setQueryParameters(query, params); ScrollableResults results = query.scroll(ScrollMode.SCROLL_INSENSITIVE); return new IteratorListImpl<AccountDailyDifference>(new DiffsIterator(results)); }
From source file:nl.strohalm.cyclos.dao.accounts.AccountDAOImpl.java
License:Open Source License
public Iterator<MemberTransactionDetailsReportData> membersTransactionsDetailsReport( final MembersTransactionsReportParameters params) { final StringBuilder sql = new StringBuilder(); final Map<String, Object> parameters = new HashMap<String, Object>(); // Find the transfer types ids Set<Long> ttIds = null; if (CollectionUtils.isNotEmpty(params.getPaymentFilters())) { ttIds = new HashSet<Long>(); for (PaymentFilter pf : params.getPaymentFilters()) { pf = getFetchDao().fetch(pf, PaymentFilter.Relationships.TRANSFER_TYPES); final Long[] ids = EntityHelper.toIds(pf.getTransferTypes()); CollectionUtils.addAll(ttIds, ids); }/* ww w .j ava 2s . c om*/ } // Get the member group ids Set<Long> groupIds = null; if (CollectionUtils.isNotEmpty(params.getMemberGroups())) { groupIds = new HashSet<Long>(); CollectionUtils.addAll(groupIds, EntityHelper.toIds(params.getMemberGroups())); } // Get the period final Period period = params.getPeriod(); final QueryParameter beginParameter = HibernateHelper.getBeginParameter(period); final QueryParameter endParameter = HibernateHelper.getEndParameter(period); // Set the parameters final boolean useTT = CollectionUtils.isNotEmpty(ttIds); if (useTT) { parameters.put("ttIds", ttIds); } if (beginParameter != null) { parameters.put("beginDate", beginParameter.getValue()); } if (endParameter != null) { parameters.put("endDate", endParameter.getValue()); } parameters.put("processed", Payment.Status.PROCESSED.getValue()); // Build the sql string sql.append( " select u.username, m.name, bu.username broker_username, b.name broker_name, h.account_type_name, h.date, h.amount, h.description, h.related_username, h.related_name, h.transfer_type_name, h.transaction_number"); sql.append( " from members m inner join users u on m.id = u.id left join members b on m.member_broker_id = b.id left join users bu on b.id = bu.id,"); sql.append(" ("); if (params.isCredits()) { appendMembersTransactionsDetailsReportSqlPart(sql, useTT, beginParameter, endParameter, true, true); sql.append(" union"); appendMembersTransactionsDetailsReportSqlPart(sql, useTT, beginParameter, endParameter, true, false); if (params.isDebits()) { sql.append(" union"); } } if (params.isDebits()) { appendMembersTransactionsDetailsReportSqlPart(sql, useTT, beginParameter, endParameter, false, true); sql.append(" union"); appendMembersTransactionsDetailsReportSqlPart(sql, useTT, beginParameter, endParameter, false, false); } sql.append(" ) h"); sql.append(" where m.id = h.member_id"); if (groupIds != null) { parameters.put("groupIds", groupIds); sql.append(" and m.group_id in (:groupIds)"); } sql.append(" order by m.name, u.username, h.account_type_name, h.date desc, h.transfer_id desc"); // Prepare the query final SQLQuery query = getSession().createSQLQuery(sql.toString()); final Map<String, Type> columns = new LinkedHashMap<String, Type>(); columns.put("username", StandardBasicTypes.STRING); columns.put("name", StandardBasicTypes.STRING); columns.put("broker_username", StandardBasicTypes.STRING); columns.put("broker_name", StandardBasicTypes.STRING); columns.put("account_type_name", StandardBasicTypes.STRING); columns.put("date", StandardBasicTypes.CALENDAR); columns.put("amount", StandardBasicTypes.BIG_DECIMAL); columns.put("description", StandardBasicTypes.STRING); columns.put("related_username", StandardBasicTypes.STRING); columns.put("related_name", StandardBasicTypes.STRING); columns.put("transfer_type_name", StandardBasicTypes.STRING); columns.put("transaction_number", StandardBasicTypes.STRING); for (final Map.Entry<String, Type> entry : columns.entrySet()) { query.addScalar(entry.getKey(), entry.getValue()); } getHibernateQueryHandler().setQueryParameters(query, parameters); // Create a transformer, which will read rows as Object[] and transform // them to MemberTransactionDetailsReportData final Transformer<Object[], MemberTransactionDetailsReportData> transformer = new Transformer<Object[], MemberTransactionDetailsReportData>() { public MemberTransactionDetailsReportData transform(final Object[] input) { final MemberTransactionDetailsReportData data = new MemberTransactionDetailsReportData(); int i = 0; for (final Map.Entry<String, Type> entry : columns.entrySet()) { final String columnName = entry.getKey(); // Column names are transfer_type_name, property is // transferTypeName String propertyName = WordUtils.capitalize(columnName, COLUMN_DELIMITERS); propertyName = Character.toLowerCase(propertyName.charAt(0)) + propertyName.substring(1); propertyName = StringUtils.replace(propertyName, "_", ""); PropertyHelper.set(data, propertyName, input[i]); i++; } return data; } }; return new ScrollableResultsIterator<MemberTransactionDetailsReportData>(query, transformer); }
From source file:nl.strohalm.cyclos.dao.accounts.AccountDAOImpl.java
License:Open Source License
public Iterator<MemberTransactionSummaryVO> membersTransactionSummaryReport( final Collection<MemberGroup> memberGroups, final PaymentFilter paymentFilter, final Period period, final boolean credits, final MemberResultDisplay order) { final Map<String, Object> parameters = new HashMap<String, Object>(); final StringBuilder sql = new StringBuilder(); // Get the transfer types ids final List<Long> ttIds = paymentFilter == null ? null : Arrays.asList(EntityHelper.toIds(paymentFilter.getTransferTypes())); // Get the member group ids List<Long> groupIds = null; if (CollectionUtils.isNotEmpty(memberGroups)) { groupIds = Arrays.asList(EntityHelper.toIds(memberGroups)); }/* w w w .j a va2 s. c om*/ // Get the period final QueryParameter beginParameter = HibernateHelper.getBeginParameter(period); final QueryParameter endParameter = HibernateHelper.getEndParameter(period); // Set the parameters final boolean useGroups = CollectionUtils.isNotEmpty(groupIds); final boolean useTT = CollectionUtils.isNotEmpty(ttIds); if (useGroups) { parameters.put("groupIds", groupIds); } if (useTT) { parameters.put("ttIds", ttIds); } if (beginParameter != null) { parameters.put("beginDate", beginParameter.getValue()); } if (endParameter != null) { parameters.put("endDate", endParameter.getValue()); } parameters.put("processed", Payment.Status.PROCESSED.getValue()); // Create the SQL query sql.append(" select member_id, sum(count) as count, sum(amount) as amount"); sql.append(" from ("); appendMembersTransactionsSummaryReportSqlPart(sql, useGroups, useTT, beginParameter, endParameter, credits, true); sql.append(" union"); appendMembersTransactionsSummaryReportSqlPart(sql, useGroups, useTT, beginParameter, endParameter, credits, false); sql.append(" ) ts"); sql.append(" group by member_id"); sql.append(" order by ").append(order == MemberResultDisplay.NAME ? "member_name, member_id" : "username"); final SQLQuery query = getSession().createSQLQuery(sql.toString()); query.addScalar("member_id", StandardBasicTypes.LONG); query.addScalar("count", StandardBasicTypes.INTEGER); query.addScalar("amount", StandardBasicTypes.BIG_DECIMAL); getHibernateQueryHandler().setQueryParameters(query, parameters); final Transformer<Object[], MemberTransactionSummaryVO> transformer = new Transformer<Object[], MemberTransactionSummaryVO>() { public MemberTransactionSummaryVO transform(final Object[] input) { final MemberTransactionSummaryVO vo = new MemberTransactionSummaryVO(); vo.setMemberId((Long) input[0]); vo.setCount((Integer) input[1]); vo.setAmount((BigDecimal) input[2]); return vo; } }; return new ScrollableResultsIterator<MemberTransactionSummaryVO>(query, transformer); }
From source file:nl.strohalm.cyclos.dao.members.ReferenceDAOImpl.java
License:Open Source License
public List<PaymentAwaitingFeedbackDTO> searchPaymentsAwaitingFeedback( final PaymentsAwaitingFeedbackQuery query) { final ResultType resultType = query.getResultType(); final PageParameters pageParameters = query.getPageParameters(); final boolean countOnly = resultType == ResultType.PAGE && pageParameters != null && pageParameters.getMaxResults() == 0; // There are 2 tables which contains payments that can have feedback: transfers and scheduled payments // As we need an union, we need a native SQL final Member member = query.getMember(); Boolean expired = query.getExpired(); final StringBuilder sql = new StringBuilder(); sql.append(" select "); if (countOnly) { sql.append(" count(*) as row_count"); } else {//w w w.j av a 2 s. co m sql.append(" * "); } sql.append(" from ( "); { sql.append( " select t.id, t.type_id as transferTypeId, false as scheduled, t.date, t.amount, tm.id as memberId, tm.name as memberName, ta.owner_name as memberUsername"); sql.append( " from transfers t inner join transfer_types tt on t.type_id = tt.id inner join accounts ta on t.to_account_id = ta.id inner join members tm on ta.member_id = tm.id"); if (member != null) { sql.append(" inner join accounts a on t.from_account_id = a.id"); } sql.append(" left join refs tf on tf.transfer_id = t.id"); sql.append(" where tt.requires_feedback = true"); sql.append(" and t.date >= tt.feedback_enabled_since"); sql.append(" and t.parent_id is null"); sql.append(" and t.chargeback_of_id is null"); sql.append(" and t.scheduled_payment_id is null"); sql.append(" and t.process_date is not null"); if (expired != null) { sql.append(" and t.feedback_deadline " + (expired ? "<" : ">=") + " now()"); } sql.append(" and tf.id is null"); if (member != null) { sql.append(" and a.member_id = :memberId"); } sql.append(" union "); sql.append(" select sp.id, sp.type_id, true, sp.date, sp.amount, tm.id, tm.name, ta.owner_name"); sql.append( " from scheduled_payments sp inner join transfer_types tt on sp.type_id = tt.id inner join accounts ta on sp.to_account_id = ta.id inner join members tm on ta.member_id = tm.id"); if (member != null) { sql.append(" inner join accounts a on sp.from_account_id = a.id"); } sql.append(" left join refs tf on tf.scheduled_payment_id = sp.id"); sql.append(" where tt.requires_feedback = true"); if (expired != null) { sql.append(" and sp.feedback_deadline " + (expired ? "<" : ">=") + " now()"); } sql.append(" and sp.date >= tt.feedback_enabled_since"); sql.append(" and tf.id is null"); if (member != null) { sql.append(" and a.member_id = :memberId"); } } sql.append(") as awaiting "); if (!countOnly) { sql.append("order by date"); } SQLQuery sqlQuery = getSession().createSQLQuery(sql.toString()); if (member != null) { sqlQuery.setLong("memberId", member.getId()); } if (countOnly) { // Handle the special case for count only sqlQuery.addScalar("row_count", StandardBasicTypes.INTEGER); int count = ((Number) sqlQuery.uniqueResult()).intValue(); return new PageImpl<PaymentAwaitingFeedbackDTO>(pageParameters, count, Collections.<PaymentAwaitingFeedbackDTO>emptyList()); } else { // Execute the search sqlQuery.addScalar("id", StandardBasicTypes.LONG); sqlQuery.addScalar("transferTypeId", StandardBasicTypes.LONG); sqlQuery.addScalar("scheduled", StandardBasicTypes.BOOLEAN); sqlQuery.addScalar("date", StandardBasicTypes.CALENDAR); sqlQuery.addScalar("amount", StandardBasicTypes.BIG_DECIMAL); sqlQuery.addScalar("memberId", StandardBasicTypes.LONG); sqlQuery.addScalar("memberName", StandardBasicTypes.STRING); sqlQuery.addScalar("memberUsername", StandardBasicTypes.STRING); getHibernateQueryHandler().applyPageParameters(pageParameters, sqlQuery); // We'll always use an iterator, even if it is for later adding it to a list Iterator<PaymentAwaitingFeedbackDTO> iterator = new ScrollableResultsIterator<PaymentAwaitingFeedbackDTO>( sqlQuery, new Transformer<Object[], PaymentAwaitingFeedbackDTO>() { public PaymentAwaitingFeedbackDTO transform(final Object[] input) { PaymentAwaitingFeedbackDTO dto = new PaymentAwaitingFeedbackDTO(); dto.setId((Long) input[0]); dto.setTransferTypeId((Long) input[1]); dto.setScheduled(Boolean.TRUE.equals(input[2])); dto.setDate((Calendar) input[3]); dto.setAmount((BigDecimal) input[4]); dto.setMemberId((Long) input[5]); dto.setMemberName((String) input[6]); dto.setMemberUsername((String) input[7]); TransferType transferType = (TransferType) getSession().load(TransferType.class, dto.getTransferTypeId()); dto.setCurrency(getFetchDao().fetch(transferType.getCurrency())); return dto; } }); if (resultType == ResultType.ITERATOR) { return new IteratorListImpl<PaymentAwaitingFeedbackDTO>(iterator); } else { List<PaymentAwaitingFeedbackDTO> list = new ArrayList<PaymentAwaitingFeedbackDTO>(); CollectionUtils.addAll(list, iterator); DataIteratorHelper.close(iterator); if (resultType == ResultType.PAGE) { // For page, we need another search for the total count query.setPageForCount(); int totalCount = PageHelper.getTotalCount(searchPaymentsAwaitingFeedback(query)); return new PageImpl<PaymentAwaitingFeedbackDTO>(pageParameters, totalCount, list); } else { return list; } } } }
From source file:org.anyframe.hibernate.impl.DynamicHibernateServiceImpl.java
License:Apache License
private void addScalar(SQLQuery query, Map<String, String> returnScalarMap) { BasicTypeRegistry typeRegistry = new BasicTypeRegistry(); if (!returnScalarMap.isEmpty()) { Set<String> keySet = returnScalarMap.keySet(); Iterator<String> keyItr = keySet.iterator(); while (keyItr.hasNext()) { String column = keyItr.next(); String typeName = returnScalarMap.get(column); if (typeName != null) query.addScalar(column, typeRegistry.getRegisteredType(typeName)); else/*from w ww .j av a 2 s . com*/ query.addScalar(column); } } }
From source file:org.bonitasoft.engine.persistence.SQLQueryBuilder.java
License:Open Source License
private void setReturnType(String builtQuery, SQLQuery sqlQuery) { if (isCountQuery(builtQuery)) { sqlQuery.addScalar("count", LongType.INSTANCE); } else {//from w w w .j a v a 2 s . c o m String hqlAlias = classAliasMappings.get(entityType.getName()); String sqlAlias = hqlToSqlAlias.containsKey(hqlAlias) ? hqlAlias.replace("user", "user_") : hqlAlias; Class<? extends PersistentObject> entityClass = interfaceToClassMapping.get(entityType.getName()); sqlQuery.addEntity(sqlAlias, entityClass.getName()); } }