Example usage for org.hibernate SQLQuery addScalar

List of usage examples for org.hibernate SQLQuery addScalar

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.*
  5. SQLQuery
  6. 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.linagora.linshare.core.repository.hibernate.ContainerQuotaRepositoryImpl.java

License:Open Source License

public List<Long> getQuotaIdforMaxFileSizeInSubDomains(AbstractDomain domain, QuotaType type,
        ContainerQuotaType containerType) {
    HibernateCallback<List<Long>> action = new HibernateCallback<List<Long>>() {
        public List<Long> doInHibernate(final Session session) throws HibernateException, SQLException {
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT DISTINCT child.id AS child_id FROM quota AS father");
            sb.append(" JOIN quota AS child");
            sb.append(" ON child.domain_parent_id = father.domain_id");
            sb.append(" AND child.quota_type = :domainType ");
            sb.append(" AND father.domain_parent_id = :domainId ");
            sb.append(" AND father.max_file_size_override = false");
            if (containerType != null) {
                sb.append(" AND father.container_type = :containerType");
            }//  ww  w  . j  av a 2 s. c  o m
            sb.append(" WHERE father.quota_type = :domainType");
            if (containerType != null) {
                sb.append(" AND child.container_type = :containerType");
            }
            sb.append(" AND child.max_file_size_override = false");
            sb.append(";");
            final SQLQuery query = session.createSQLQuery(sb.toString());
            query.setLong("domainId", domain.getPersistenceId());
            query.addScalar("child_id", LongType.INSTANCE);
            query.setString("domainType", type.name());
            if (containerType != null) {
                query.setString("containerType", containerType.name());
            }
            @SuppressWarnings("unchecked")
            List<Long> res = query.list();
            logger.debug("child_ids :" + res);
            return res;
        }
    };
    return getHibernateTemplate().execute(action);
}

From source file:org.linagora.linshare.core.repository.hibernate.ContainerQuotaRepositoryImpl.java

License:Open Source License

public List<Long> getQuotaIdforDefaultAccountQuotaInTopDomains(AbstractDomain domain, QuotaType type,
        ContainerQuotaType containerType) {
    HibernateCallback<List<Long>> action = new HibernateCallback<List<Long>>() {
        public List<Long> doInHibernate(final Session session) throws HibernateException, SQLException {
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT DISTINCT id FROM quota ");
            sb.append(" WHERE quota_type = :domainType");
            sb.append(" AND container_type = :containerType");
            sb.append(" AND account_quota_override = false");
            sb.append(" AND domain_parent_id = :domainId ");
            sb.append(";");
            final SQLQuery query = session.createSQLQuery(sb.toString());
            query.setLong("domainId", domain.getPersistenceId());
            query.addScalar("id", LongType.INSTANCE);
            query.setString("domainType", type.name());
            query.setString("containerType", containerType.name());
            @SuppressWarnings("unchecked")
            List<Long> res = query.list();
            logger.debug("ids :" + res);
            return res;
        }//from w w w  .  j a  va  2s  .  co  m
    };
    return getHibernateTemplate().execute(action);
}

From source file:org.linagora.linshare.core.repository.hibernate.ContainerQuotaRepositoryImpl.java

License:Open Source License

public List<Long> getQuotaIdforDefaultAccountQuotaInSubDomains(AbstractDomain domain, QuotaType type,
        ContainerQuotaType containerType) {
    HibernateCallback<List<Long>> action = new HibernateCallback<List<Long>>() {
        public List<Long> doInHibernate(final Session session) throws HibernateException, SQLException {
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT DISTINCT child.id AS child_id FROM quota AS father");
            sb.append(" JOIN quota AS child");
            sb.append(" ON child.domain_parent_id = father.domain_id");
            sb.append(" AND child.quota_type = :domainType ");
            sb.append(" AND father.domain_parent_id = :domainId ");
            sb.append(" AND father.default_account_quota_override = false");
            sb.append(" WHERE father.quota_type = :domainType");
            if (containerType != null) {
                sb.append(" AND child.container_type = :containerType");
            }/*from   w  ww . j  av a 2 s . co m*/
            sb.append(" AND child.account_quota_override = false");
            sb.append(";");
            final SQLQuery query = session.createSQLQuery(sb.toString());
            query.setLong("domainId", domain.getPersistenceId());
            query.addScalar("child_id", LongType.INSTANCE);
            query.setString("domainType", type.name());
            if (containerType != null) {
                query.setString("containerType", containerType.name());
            }
            @SuppressWarnings("unchecked")
            List<Long> res = query.list();
            logger.debug("child_ids :" + res);
            return res;
        }
    };
    return getHibernateTemplate().execute(action);
}

From source file:org.linagora.linshare.core.repository.hibernate.ContainerQuotaRepositoryImpl.java

License:Open Source License

public List<Long> getQuotaIdforAccountQuotaInSubDomains(AbstractDomain domain, QuotaType type,
        ContainerQuotaType containerType) {
    HibernateCallback<List<Long>> action = new HibernateCallback<List<Long>>() {
        public List<Long> doInHibernate(final Session session) throws HibernateException, SQLException {
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT DISTINCT child.id AS child_id FROM quota AS father");
            sb.append(" JOIN quota AS child");
            sb.append(" ON child.domain_parent_id = father.domain_id");
            sb.append(" AND child.quota_type = :domainType ");
            sb.append(" AND father.domain_parent_id = :domainId ");
            sb.append(" AND father.default_account_quota_override = false");
            if (containerType != null) {
                sb.append(" AND father.container_type = :containerType");
            }// w  w  w  .  j  av  a 2  s .  c o m
            sb.append(" WHERE father.quota_type = :domainType");
            if (containerType != null) {
                sb.append(" AND child.container_type = :containerType");
            }
            sb.append(" AND child.account_quota_override = false");
            sb.append(";");
            final SQLQuery query = session.createSQLQuery(sb.toString());
            query.setLong("domainId", domain.getPersistenceId());
            query.addScalar("child_id", LongType.INSTANCE);
            query.setString("domainType", type.name());
            if (containerType != null) {
                query.setString("containerType", containerType.name());
            }
            @SuppressWarnings("unchecked")
            List<Long> res = query.list();
            logger.debug("child_ids :" + res);
            return res;
        }
    };
    return getHibernateTemplate().execute(action);
}

From source file:org.linagora.linshare.core.repository.hibernate.GenericQuotaRepositoryImpl.java

License:Open Source License

public List<Long> getQuotaIdforDefaultQuotaInSubDomains(AbstractDomain domain, Long quota, QuotaType type,
        ContainerQuotaType containerType) {
    HibernateCallback<List<Long>> action = new HibernateCallback<List<Long>>() {
        public List<Long> doInHibernate(final Session session) throws HibernateException, SQLException {
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT DISTINCT child.id AS child_id FROM quota AS father");
            sb.append(" JOIN quota AS child");
            sb.append(" ON child.domain_parent_id = father.domain_id");
            sb.append(" AND child.quota_type = :domainType ");
            sb.append(" AND father.domain_parent_id = :domainId ");
            sb.append(" AND father.default_quota_override = false");
            sb.append(" WHERE father.quota_type = :domainType");
            if (containerType != null) {
                sb.append(" AND child.container_type = :containerType");
            }//from  ww w .ja  v a  2s.  co m
            sb.append(" AND child.default_quota_override = false");
            sb.append(";");
            final SQLQuery query = session.createSQLQuery(sb.toString());
            query.setLong("domainId", domain.getPersistenceId());
            query.addScalar("child_id", LongType.INSTANCE);
            query.setString("domainType", type.name());
            if (containerType != null) {
                query.setString("containerType", containerType.name());
            }
            @SuppressWarnings("unchecked")
            List<Long> res = query.list();
            logger.debug("child_ids :" + res);
            return res;
        }
    };
    return getHibernateTemplate().execute(action);
}

From source file:org.linagora.linshare.core.repository.hibernate.GenericQuotaRepositoryImpl.java

License:Open Source License

public List<Long> getQuotaIdforQuotaInSubDomains(AbstractDomain domain, Long quota, QuotaType type,
        ContainerQuotaType containerType) {
    HibernateCallback<List<Long>> action = new HibernateCallback<List<Long>>() {
        public List<Long> doInHibernate(final Session session) throws HibernateException, SQLException {
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT DISTINCT child.id AS child_id FROM quota AS father");
            sb.append(" JOIN quota AS child");
            sb.append(" ON child.domain_parent_id = father.domain_id");
            sb.append(" AND child.quota_type = :domainType ");
            sb.append(" AND father.domain_parent_id = :domainId ");
            sb.append(" AND father.quota_override = false");
            if (containerType != null) {
                sb.append(" AND father.container_type = :containerType");
            }//from w w w.  j  av a2s .com
            sb.append(" WHERE father.quota_type = :domainType");
            if (containerType != null) {
                sb.append(" AND child.container_type = :containerType");
            }
            sb.append(" AND child.quota_override = false");
            sb.append(";");
            final SQLQuery query = session.createSQLQuery(sb.toString());
            query.setLong("domainId", domain.getPersistenceId());
            query.addScalar("child_id", LongType.INSTANCE);
            query.setString("domainType", type.name());
            if (containerType != null) {
                query.setString("containerType", containerType.name());
            }
            @SuppressWarnings("unchecked")
            List<Long> res = query.list();
            logger.debug("child_ids :" + res);
            return res;
        }
    };
    return getHibernateTemplate().execute(action);
}

From source file:org.mousephenotype.dcc.utils.persistence.HibernateManager.java

License:Apache License

public List nativeQuery(String query, Map<String, org.hibernate.type.Type> scalars) {

    this.refreshSession();
    Transaction transaction = null;/*from  w w  w  .j  av a  2s  .com*/
    List result = null;
    try {
        transaction = session.beginTransaction();
        SQLQuery sqlQuery = session.createSQLQuery(query);
        for (Map.Entry<String, org.hibernate.type.Type> entry : scalars.entrySet()) {
            sqlQuery.addScalar(entry.getKey(), entry.getValue());
        }
        result = sqlQuery.list();
        transaction.commit();
    } catch (RuntimeException ex) {
        if (transaction != null) {
            logger.error("rolling back transaction {}", ex);
            transaction.rollback();
        }
    }
    return result;
}

From source file:org.mousephenotype.dcc.utils.persistence.HibernateManager.java

License:Apache License

public List nativeQuery(String query, Map<String, org.hibernate.type.Type> scalars,
        com.google.common.collect.Table<String, Class, Object> parameters) {

    this.refreshSession();
    Transaction transaction = null;//from w  w  w.ja  va 2s.co  m
    List result = null;
    try {
        transaction = session.beginTransaction();
        SQLQuery sqlQuery = session.createSQLQuery(query);
        if (scalars != null) {
            for (Map.Entry<String, org.hibernate.type.Type> entry : scalars.entrySet()) {
                sqlQuery.addScalar(entry.getKey(), entry.getValue());
            }
        }
        if (parameters != null) {
            for (com.google.common.collect.Table.Cell<String, Class, Object> entry : parameters.cellSet()) {
                if (entry.getColumnKey().equals(String.class)) {
                    sqlQuery.setString(entry.getRowKey(), (String) entry.getValue());
                    continue;
                }
                if (entry.getColumnKey().equals(Long.class)) {
                    sqlQuery.setLong(entry.getRowKey(), (Long) entry.getValue());
                    continue;
                }
                if (entry.getColumnKey().equals(BigInteger.class)) {
                    sqlQuery.setBigInteger(entry.getRowKey(), (BigInteger) entry.getValue());
                    continue;
                }
                logger.error("Type {} not registered", entry.getValue().getClass());
            }
        }

        result = sqlQuery.list();
        transaction.commit();
    } catch (RuntimeException ex) {
        if (transaction != null) {
            logger.error("rolling back transaction {}", ex);
            transaction.rollback();
        }
    }
    return result;
}

From source file:org.openbravo.costing.CostingMigrationProcess.java

License:Open Source License

private void doChecks() {
    // Check all transactions have a legacy cost available.
    AlertRule legacyCostAvailableAlert = getLegacyCostAvailableAlert();
    if (legacyCostAvailableAlert == null) {
        Organization org0 = OBDal.getInstance().get(Organization.class, "0");
        Client client0 = OBDal.getInstance().get(Client.class, "0");

        legacyCostAvailableAlert = OBProvider.getInstance().get(AlertRule.class);
        legacyCostAvailableAlert.setClient(client0);
        legacyCostAvailableAlert.setOrganization(org0);
        legacyCostAvailableAlert.setName(alertRuleName);
        // Header tab of Product window
        legacyCostAvailableAlert.setTab(OBDal.getInstance().get(org.openbravo.model.ad.ui.Tab.class, "180"));
        StringBuffer sql = new StringBuffer();
        sql.append("select t.m_product_id as referencekey_id, '0' as ad_role_id, null as ad_user_id,");
        sql.append("\n    'Product ' || p.name || ' has transactions on dates without available");
        sql.append(" costs. Min date ' || min(t.movementdate) || '. Max date ' || max(t.movementdate)");
        sql.append(" as description,");
        sql.append("\n    'Y' as isactive, p.ad_org_id, p.ad_client_id,");
        sql.append("\n    now() as created, '0' as createdby, now() as updated, '0' as updatedby,");
        sql.append("\n    p.name as record_id");
        sql.append("\nfrom m_transaction t join m_product p on t.m_product_id = p.m_product_id");
        sql.append("\nwhere not exists (select 1 from m_costing c ");
        sql.append("\n                  where t.isactive = 'Y'");
        sql.append("\n                    and t.m_product_id = c.m_product_id");
        sql.append("\n                    and t.movementdate >= c.datefrom");
        sql.append("\n                    and t.movementdate < c.dateto");
        sql.append("\n                    and c.cost is not null)");
        sql.append("\ngroup by t.m_product_id, p.ad_org_id, p.ad_client_id, p.name");
        legacyCostAvailableAlert.setSql(sql.toString());

        OBDal.getInstance().save(legacyCostAvailableAlert);
        OBDal.getInstance().flush();/*from   w w w  .  ja  v a  2s  . c o m*/

        insertAlertRecipients(legacyCostAvailableAlert);
    }

    // Delete previous alerts
    StringBuffer delete = new StringBuffer();
    delete.append("delete from " + Alert.ENTITY_NAME);
    delete.append(" where " + Alert.PROPERTY_ALERTRULE + " = :alertRule ");
    Query queryDelete = OBDal.getInstance().getSession().createQuery(delete.toString());
    queryDelete.setEntity("alertRule", legacyCostAvailableAlert);
    queryDelete.executeUpdate();

    if (legacyCostAvailableAlert.isActive()) {

        SQLQuery alertQry = OBDal.getInstance().getSession().createSQLQuery(legacyCostAvailableAlert.getSql());
        alertQry.addScalar("REFERENCEKEY_ID", StringType.INSTANCE);
        alertQry.addScalar("AD_ROLE_ID", StringType.INSTANCE);
        alertQry.addScalar("AD_USER_ID", StringType.INSTANCE);
        alertQry.addScalar("DESCRIPTION", StringType.INSTANCE);
        alertQry.addScalar("ISACTIVE", StringType.INSTANCE);
        alertQry.addScalar("AD_ORG_ID", StringType.INSTANCE);
        alertQry.addScalar("AD_CLIENT_ID", StringType.INSTANCE);
        alertQry.addScalar("CREATED", DateType.INSTANCE);
        alertQry.addScalar("CREATEDBY", StringType.INSTANCE);
        alertQry.addScalar("UPDATED", DateType.INSTANCE);
        alertQry.addScalar("UPDATEDBY", StringType.INSTANCE);
        alertQry.addScalar("RECORD_ID", StringType.INSTANCE);
        List<?> rows = alertQry.list();
        for (final Object row : rows) {
            final Object[] values = (Object[]) row;
            Alert alert = OBProvider.getInstance().get(Alert.class);
            alert.setCreatedBy(OBDal.getInstance().get(User.class, "0"));
            alert.setUpdatedBy(OBDal.getInstance().get(User.class, "0"));
            alert.setClient(OBDal.getInstance().get(Client.class, values[6]));
            alert.setOrganization(OBDal.getInstance().get(Organization.class, values[5]));
            alert.setAlertRule(legacyCostAvailableAlert);
            alert.setRecordID((String) values[11]);
            alert.setReferenceSearchKey((String) values[0]);
            alert.setDescription((String) values[3]);
            alert.setUserContact(null);
            alert.setRole(OBDal.getInstance().get(org.openbravo.model.ad.access.Role.class, "0"));
            OBDal.getInstance().save(alert);
        }
        if (SessionHandler.isSessionHandlerPresent()) {
            SessionHandler.getInstance().commitAndStart();
        }
        if (rows.size() > 0) {
            throw new OBException("@TrxWithNoCost@");
        }
    }
}

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

License:Open Source License

private Page<Facility> searchWithExtraData(FacilityRepositoryCustom.SearchParams searchParams,
        Set<UUID> geographicZoneIds, String extraData, Pageable pageable) {

    Map<String, Object> params = Maps.newHashMap();
    String query = prepareQuery(NATIVE_SELECT_BY_PARAMS, searchParams, geographicZoneIds, extraData, params);

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

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

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

    if (isEmpty(ids)) {
        return Pagination.getPage(Collections.emptyList(), pageable, 0);
    }//from   ww  w.ja  va2 s. c om

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

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

    return Pagination.getPage(facilities, pageable, ids.size());
}