Example usage for org.hibernate SQLQuery addScalar

Introduction

In this page you can find the example usage for org.hibernate SQLQuery addScalar.

Prototype

SQLQuery<T> addScalar(String columnAlias, Type type);

Source Link

Document

Declare a scalar query result.

Usage

From source file:org.openlmis.referencedata.repository.custom.impl.FacilityTypeApprovedProductRepositoryImpl.java

License:Open Source License

private UUID getFacilityTypeId(UUID facilityId, Profiler profiler) {
    String queryString = String.format(NATIVE_SELECT_FACILITY_TYPE_ID, facilityId);
    Query query = entityManager.createNativeQuery(queryString);

    SQLQuery sql = query.unwrap(SQLQuery.class);
    sql.addScalar("type_id", PostgresUUIDType.INSTANCE);

    try {/*from  w w w  .  j  a v a 2 s  . c o  m*/
        return (UUID) query.getSingleResult();
    } catch (Exception ex) {
        profiler.stop().log();
        throw new ValidationMessageException(ex, FacilityMessageKeys.ERROR_NOT_FOUND);
    }
}

From source file:org.openlmis.referencedata.repository.custom.impl.FacilityTypeApprovedProductRepositoryImpl.java

License:Open Source License

private Query createNativeQuery(StringBuilder builder, Map<String, Object> params, boolean count) {
    Query nativeQuery = entityManager.createNativeQuery(builder.toString());
    params.forEach(nativeQuery::setParameter);

    if (!count) {
        SQLQuery sqlQuery = nativeQuery.unwrap(SQLQuery.class);
        sqlQuery.addScalar("id", PostgresUUIDType.INSTANCE);
        sqlQuery.addScalar("versionNumber", LongType.INSTANCE);
    }/*w  w w  .  j av  a2 s .c  o m*/

    return nativeQuery;
}

From source file:org.openlmis.referencedata.repository.custom.impl.IdealStockAmountRepositoryImpl.java

License:Open Source License

private void prepareIdQuery(Query query) {
    SQLQuery sql = query.unwrap(SQLQuery.class);
    sql.addScalar("isa_id", PostgresUUIDType.INSTANCE);
}

From source file:org.openlmis.referencedata.repository.custom.impl.IdealStockAmountRepositoryImpl.java

License:Open Source License

private SQLQuery prepareMinimalQuery(Query query) {
    SQLQuery sql = query.unwrap(SQLQuery.class);
    sql.addScalar("isa_id", PostgresUUIDType.INSTANCE);
    sql.addScalar("isa_amount", IntegerType.INSTANCE);
    sql.addScalar("facility_id", PostgresUUIDType.INSTANCE);
    sql.addScalar("commodity_id", PostgresUUIDType.INSTANCE);
    sql.addScalar("period_id", PostgresUUIDType.INSTANCE);
    return sql;// w w w  . jav a2 s .  com
}

From source file:org.openlmis.referencedata.repository.custom.impl.IdealStockAmountRepositoryImpl.java

License:Open Source License

private void prepareCountQuery(Query query) {
    SQLQuery sql = query.unwrap(SQLQuery.class);
    sql.addScalar("count", LongType.INSTANCE);
}

From source file:org.openlmis.referencedata.repository.custom.impl.OrderableRepositoryImpl.java

License:Open Source License

private Query prepareNativeQuery(SearchParams searchParams, boolean count, Pageable pageable) {
    String startNativeQuery = count ? NATIVE_COUNT_ORDERABLES : NATIVE_SELECT_ORDERABLES_IDENTITIES;
    StringBuilder builder = new StringBuilder(startNativeQuery);
    Map<String, Object> params = Maps.newHashMap();
    List<String> where = Lists.newArrayList();

    if (null != searchParams) {
        if (null != searchParams.getProgramCode()) {
            builder.append(NATIVE_PROGRAM_ORDERABLE_INNER_JOIN).append(NATIVE_PROGRAM_INNER_JOIN).append(AND)
                    .append(NATIVE_PROGRAM_CODE);
            params.put("programCode", searchParams.getProgramCode().toLowerCase());
        }//from   w  w w  . ja v a  2 s. c o m

        if (isEmpty(searchParams.getIdentityPairs())) {
            builder.append(NATIVE_LATEST_ORDERABLE_INNER_JOIN);
        } else {
            where.add(searchParams.getIdentityPairs().stream()
                    .map(pair -> String.format(NATIVE_IDENTITY, pair.getLeft(), pair.getRight()))
                    .collect(Collectors.joining(OR)));
        }

        if (isNotBlank(searchParams.getCode())) {
            where.add(NATIVE_PRODUCT_CODE);
            params.put("orderableCode", "%" + searchParams.getCode().toLowerCase() + "%");
        }

        if (isNotBlank(searchParams.getName())) {
            where.add(NATIVE_PRODUCT_NAME);
            params.put("orderableName", "%" + searchParams.getName().toLowerCase() + "%");
        }
    } else {
        builder.append(NATIVE_LATEST_ORDERABLE_INNER_JOIN);
    }

    if (!where.isEmpty()) {
        builder.append(WHERE).append(String.join(AND, where));
    }

    if (!count) {
        Optional.ofNullable(pageable)
                .ifPresent(p -> builder.append(GROUP_BY_ID_VERSION_NUMBER_AND_FULL_PRODUCT_NAME)
                        .append(ORDER_BY).append(PageableUtil.getOrderPredicate(p, "o.", ASC_SORT)));
        setPagination(builder, params, pageable);
    }

    Query nativeQuery = entityManager.createNativeQuery(builder.toString());
    params.forEach(nativeQuery::setParameter);

    if (!count) {
        SQLQuery sqlQuery = nativeQuery.unwrap(SQLQuery.class);
        sqlQuery.addScalar("id", PostgresUUIDType.INSTANCE);
        sqlQuery.addScalar("versionNumber", LongType.INSTANCE);
    }

    return nativeQuery;
}

From source file:org.openlmis.referencedata.repository.custom.impl.ProcessingPeriodRepositoryImpl.java

License:Open Source License

/**
 * This method is supposed to retrieve all Processing Periods with matched parameters.
 * Method is searching/* w w  w .  j  a v  a 2 s . com*/
 *
 * @param scheduleId  UUID of processing schedule
 * @param programId  UUID of program
 * @param facilityId  UUID of facility
 * @param startDate Processing Period Start Date
 * @param endDate   Processing Period End Date
 * @param pageable  pagination and sorting parameters
 * @return Page of Processing Periods matching the parameters.
 */
public Page<ProcessingPeriod> search(UUID scheduleId, UUID programId, UUID facilityId, LocalDate startDate,
        LocalDate endDate, Collection<UUID> ids, Pageable pageable) {

    Map<String, Object> params = Maps.newHashMap();
    Query nativeQuery = entityManager.createNativeQuery(
            prepareQuery(scheduleId, programId, facilityId, startDate, endDate, ids, params));
    params.forEach(nativeQuery::setParameter);

    SQLQuery countQuery = nativeQuery.unwrap(SQLQuery.class);
    countQuery.addScalar("ID", PostgresUUIDType.INSTANCE);

    // appropriate scalar is added to native query
    @SuppressWarnings("unchecked")
    List<UUID> periodIds = nativeQuery.getResultList();

    if (isEmpty(periodIds)) {
        return Pagination.getPage(Collections.emptyList(), pageable, 0);
    }

    String hqlWithSort = Joiner.on(' ').join(Lists.newArrayList(SELECT_PERIODS, WHERE, WITH_IDS, ORDER_BY,
            PageableUtil.getOrderPredicate(pageable, "pp.", DEFAULT_SORT)));

    List<ProcessingPeriod> periods = entityManager.createQuery(hqlWithSort, ProcessingPeriod.class)
            .setParameter("ids", periodIds).setMaxResults(pageable.getPageSize())
            .setFirstResult(pageable.getOffset()).getResultList();

    return Pagination.getPage(periods, pageable, periodIds.size());
}

From source file:org.openmrs.module.ugandaemrsync.api.dao.UgandaEMRSyncDao.java

License:Mozilla Public License

public List getFinalResults(List<String> columns, String finalQuery) {
    SQLQuery sqlQuery = getSession().createSQLQuery(finalQuery);
    for (String column : columns) {
        sqlQuery.addScalar(column, StringType.INSTANCE);
    }//from   w  w  w  .j  a  v  a  2s .  c  o  m
    return sqlQuery.list();
}

From source file:org.openxdata.server.dao.hibernate.HibernateDataExportDAO.java

License:Apache License

@Override
@SuppressWarnings("unchecked")
public List<Object[]> getFormDataWithAuditing(Integer formDefVersionId, Date fromDate, Date toDate,
        Integer userId) {//from w w  w  .  j  a v a2s .  co m
    Session session = getSessionFactory().getCurrentSession();
    String sql = "select d.form_data_id, u.user_name, d.date_created, d.data from form_data d, users u";
    // FIXME: see HibernateEditableDAO.getFormData
    String filter = null;
    if (!(formDefVersionId == null && fromDate == null && toDate == null && userId == null)) {
        filter = addIntegerFilter(filter, "d.form_definition_version_id", formDefVersionId);
        filter = addIntegerFilter(filter, "d.creator", userId);
        filter = addDateFilter(filter, "d.date_created", fromDate, toDate);
        if (!filter.equals("")) {
            sql += filter += " and u.user_id = d.creator;";
        } else {
            sql += " where u.user_id = d.creator;";
        }
    }

    SQLQuery query = session.createSQLQuery(sql);

    query.addScalar("form_data_id", Hibernate.INTEGER);
    query.addScalar("user_name", Hibernate.STRING);
    query.addScalar("date_created", Hibernate.DATE);
    query.addScalar("data", Hibernate.STRING);

    List<Object[]> items = query.list();
    return items;
}

From source file:org.openxdata.server.dao.hibernate.HibernateFormDownloadDAO.java

License:Apache License

@Override
@SuppressWarnings("unchecked")
public List<Object[]> getStudyList() {

    Session session = getSessionFactory().getCurrentSession();

    SQLQuery query = session.createSQLQuery("select study_id, name from study order by name");
    query.addScalar("study_id", Hibernate.INTEGER);
    query.addScalar("name", Hibernate.STRING);
    return query.list();
}