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:com.yougou.api.service.impl.ApiKeyServiceImpl.java

@Override
public ApiKey randomSeekTestApiKey() throws Exception {
    Session session = null;//from w w w . j  av  a2  s. com
    try {
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append(" select ");
        sqlBuilder.append(" t1.app_key as appKey, t1.app_secret as appSecret ");
        sqlBuilder.append(" from ");
        sqlBuilder.append(" tbl_merchant_api_key t1 ");
        sqlBuilder.append(" inner join ");
        sqlBuilder.append(" tbl_merchant_api_key_metadata t2 ");
        sqlBuilder.append(" on(t1.id = t2.key_id) ");
        sqlBuilder.append(" left join ");
        sqlBuilder.append(" ( ");
        sqlBuilder.append(apiKeyDao.getApiKeyPotentialCustomersSqlStatement());
        sqlBuilder.append(" ) t3 ");
        sqlBuilder.append(" on(t2.metadata_val = t3.metadata_val) ");
        sqlBuilder.append(" where ");
        sqlBuilder.append(" t3.metadata_tag like '%%' ");
        session = apiKeyDao.getHibernateSession();
        SQLQuery sqlQuery = session.createSQLQuery(sqlBuilder.toString());
        sqlQuery.addScalar("appKey", Hibernate.STRING);
        sqlQuery.addScalar("appSecret", Hibernate.STRING);
        sqlQuery.setResultTransformer(Transformers.aliasToBean(ApiKey.class));
        List<ApiKey> list = sqlQuery.list();
        return list.size() == 0 ? null : list.get(new Random().nextInt(list.size()));
    } finally {
        apiKeyDao.releaseHibernateSession(session);
    }
}

From source file:com.yougou.api.service.impl.ApiKeyServiceImpl.java

/**
 * ?metadataValApiKeyMetadata?//from   w w  w. j  a v a2  s .co m
 * @param metadataVal
 * @return
 * @throws Exception
 */
@Override
public ApiKeyMetadata queryApiKeyByMetadataVal(String metadataVal) throws Exception {
    StringBuilder sqlBuilder = new StringBuilder();
    sqlBuilder.append(" SELECT km.metadata_key as metadataKey,km.metadata_val as metadataVal,km.key_id as id ");
    sqlBuilder.append(" FROM tbl_merchant_api_key_metadata km ");
    sqlBuilder.append(" WHERE km.metadata_key = 'MERCHANTS' ");
    sqlBuilder.append(" AND km.metadata_val = ? ");

    Session session = null;
    try {
        session = apiKeyDao.getHibernateSession();
        SQLQuery sqlQuery = session.createSQLQuery(sqlBuilder.toString());
        sqlQuery.setParameter(0, metadataVal);
        sqlQuery.addScalar("metadataKey", customAppType);
        sqlQuery.addScalar("metadataVal", Hibernate.STRING);
        sqlQuery.addScalar("id", Hibernate.STRING);
        sqlQuery.setResultTransformer(Transformers.aliasToBean(ApiKeyMetadata.class));
        List<ApiKeyMetadata> list = sqlQuery.list();
        return list.size() == 0 ? null : list.get(0);
    } finally {
        apiKeyDao.releaseHibernateSession(session);
    }
}

From source file:com.zdtx.ifms.specific.service.monitor.IpCamManager.java

/**
 * Get IP Camera's users//from   www  .j ava2  s  .c o m
 * @return   CamUserVO
 */
@SuppressWarnings("unchecked")
public CamUserVO getCamUsers() {
    String sql = "SELECT A.USERNAME AS ADMINNAME, A.USERPASS AS ADMINPASS, "
            + "B.USERNAME AS OPERATORNAME, B.USERPASS AS OPERATORPASS, "
            + "C.USERNAME AS VIEWERNAME, C.USERPASS AS VIEWERPASS "
            + "FROM T_CORE_CAM_USER A, T_CORE_CAM_USER B, T_CORE_CAM_USER C "
            + "WHERE A.AUTHLEVEL = 0 AND B.AUTHLEVEL = 1 AND C.AUTHLEVEL = 2";
    SQLQuery query = dao.getSession().createSQLQuery(sql);
    query.addScalar("adminName", StringType.INSTANCE);
    query.addScalar("adminPass", StringType.INSTANCE);
    query.addScalar("operatorName", StringType.INSTANCE);
    query.addScalar("operatorPass", StringType.INSTANCE);
    query.addScalar("viewerName", StringType.INSTANCE);
    query.addScalar("viewerPass", StringType.INSTANCE);
    query.setResultTransformer(Transformers.aliasToBean(CamUserVO.class));
    query.setCacheable(true);
    CamUserVO camUserVO = ((List<CamUserVO>) query.list()).get(0);
    return camUserVO;
}

From source file:dao.ParametrDao.java

public List<Object[]> getParamsAndNeedsFromCat(Long catId) {
    String sql = "select p.*,l.req_type from param_category_link l left join parametr p on l.parametr_id=p.parametr_id where l.category_id=:catId order by p.name asc";
    SQLQuery query = getCurrentSession().createSQLQuery(sql);
    query.addEntity("p", Parametr.class);
    query.addScalar("req_type", StandardBasicTypes.INTEGER);
    query.setParameter("catId", catId);
    return query.list();
}

From source file:daoImpl.GroupsDAOImpl.java

@Override
public List<SearchResult> findNearestGroups(float lati, float longi, float maxDistance) {
    Session session = sFac.openSession();
    SQLQuery getDistances = session.createSQLQuery("Select groups.group_id, "
            + "(6371 * acos( cos( radians(:lati )) * cos( radians( groups.latitude ) ) * cos( radians( groups.longitude ) - radians(:longi) ) + sin( radians(:lati) ) * sin( radians( groups.latitude ) ) ) ) as distance "
            + "from groups having distance < :maxDistance order by distance;");
    getDistances.setParameter("lati", lati);
    getDistances.setParameter("longi", longi);
    getDistances.setParameter("maxDistance", maxDistance);
    getDistances.addScalar("group_id", StandardBasicTypes.INTEGER);
    getDistances.addScalar("distance", StandardBasicTypes.FLOAT);

    List<Object[]> closestGroups = getDistances.list();

    if (closestGroups.isEmpty())
        return new ArrayList<>();

    ArrayList groupsToGet = new ArrayList<>();

    for (Object[] r : closestGroups) {
        int i = (int) r[0];
        groupsToGet.add(i);/*from w  w w  .  j  av  a2  s.  com*/
    }

    Query getGroups = session.createQuery(
            "from Groups G left join fetch G.tagses left join fetch G.usersGroupses where G.groupId in (:ids)");
    getGroups.setParameterList("ids", groupsToGet);
    getGroups.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
    List<Groups> groupsFound = getGroups.list();

    ArrayList<SearchResult> results = new ArrayList<>();

    for (int i = 0; i < groupsFound.size(); i++) {
        SearchResult s = new SearchResult(groupsFound.get(i), (float) (closestGroups.get(i)[1]));
        results.add(s);
    }
    session.close();
    return results;
}

From source file:daoImpl.GroupsDAOImpl.java

@Override
public List<SearchResult> findNearestGroupsByName(float lati, float longi, float maxDistance,
        String searchTerm) {/* w w  w .j av  a  2s.  c om*/
    Session session = sFac.openSession();
    SQLQuery getDistances = session.createSQLQuery("Select groups.group_id, "
            + "(6371 * acos( cos( radians(:lati )) * cos( radians( groups.latitude ) ) * cos( radians( groups.longitude ) - radians(:longi) ) + sin( radians(:lati) ) * sin( radians( groups.latitude ) ) ) ) as distance "
            + "from groups where groups.groupname like concat('%', :name , '%') having distance < :maxDistance order by distance;");
    getDistances.setParameter("lati", lati);
    getDistances.setParameter("longi", longi);
    getDistances.setParameter("maxDistance", maxDistance);
    getDistances.setParameter("name", searchTerm);
    getDistances.addScalar("group_id", StandardBasicTypes.INTEGER);
    getDistances.addScalar("distance", StandardBasicTypes.FLOAT);
    List<Object[]> closestGroups = getDistances.list();

    if (closestGroups.isEmpty())
        return new ArrayList<>();

    ArrayList groupsToGet = new ArrayList<>();

    for (Object[] r : closestGroups) {
        int i = (int) r[0];
        groupsToGet.add(i);
    }

    Query getGroups = session.createQuery(
            "from Groups G left join fetch G.tagses left join fetch G.usersGroupses where G.groupId in (:ids)");
    getGroups.setParameterList("ids", groupsToGet);
    getGroups.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
    List<Groups> groupsFound = getGroups.list();

    ArrayList<SearchResult> results = new ArrayList<>();

    for (int i = 0; i < groupsFound.size(); i++) {
        SearchResult s = new SearchResult(groupsFound.get(i), (float) (closestGroups.get(i)[1]));
        results.add(s);
    }
    session.close();
    return results;
}

From source file:daoImpl.GroupsDAOImpl.java

@Override
public List<SearchResult> findNearestGroupsByDesc(float lati, float longi, float maxDistance,
        String description) {/*  w ww. ja v a  2s  . c  o m*/
    Session session = sFac.openSession();
    SQLQuery getDistances = session.createSQLQuery("Select groups.group_id, "
            + "(6371 * acos( cos( radians(:lati )) * cos( radians( groups.latitude ) ) * cos( radians( groups.longitude ) - radians(:longi) ) + sin( radians(:lati) ) * sin( radians( groups.latitude ) ) ) ) as distance "
            + "from groups where groups.description like concat('%', :name , '%') having distance < :maxDistance order by distance;");
    getDistances.setParameter("lati", lati);
    getDistances.setParameter("longi", longi);
    getDistances.setParameter("maxDistance", maxDistance);
    getDistances.setParameter("name", description);
    getDistances.addScalar("group_id", StandardBasicTypes.INTEGER);
    getDistances.addScalar("distance", StandardBasicTypes.FLOAT);
    List<Object[]> closestGroups = getDistances.list();

    if (closestGroups.isEmpty())
        return new ArrayList<>();

    ArrayList groupsToGet = new ArrayList<>();

    for (Object[] r : closestGroups) {
        int i = (int) r[0];
        groupsToGet.add(i);
    }

    Query getGroups = session.createQuery(
            "from Groups G left join fetch G.tagses left join fetch G.usersGroupses where G.groupId in (:ids)");
    getGroups.setParameterList("ids", groupsToGet);
    getGroups.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
    List<Groups> groupsFound = getGroups.list();

    ArrayList<SearchResult> results = new ArrayList<>();

    for (int i = 0; i < groupsFound.size(); i++) {
        SearchResult s = new SearchResult(groupsFound.get(i), (float) (closestGroups.get(i)[1]));
        results.add(s);
    }
    session.close();
    return results;
}

From source file:daoImpl.GroupsDAOImpl.java

@Override
public List<SearchResult> findNearestGroupsByTag(float latitude, float longitude, float maxDistance,
        String tag) {//from   ww w  .  j ava2  s . c  o m
    Session session = sFac.openSession();
    SQLQuery query = session.createSQLQuery(
            "Select groups.group_id, (6371 * acos( cos( radians(:lati )) * cos( radians( groups.latitude ) ) * cos( radians( groups.longitude ) - radians(:longi) ) + sin( radians(:lati) ) * sin( radians( groups.latitude ) ) ) ) as distance from groups join groups_tags on (groups.group_id=groups_tags.group_id_fk) join tags on (groups_tags.tag_id_fk=tags.tag_id) where tags.tag_name = :tagname having distance < :dist order by distance;");
    query.setParameter("lati", latitude);
    query.setParameter("longi", longitude);
    query.setParameter("tagname", tag);
    query.setParameter("dist", maxDistance);
    query.addScalar("group_id", StandardBasicTypes.INTEGER);
    query.addScalar("group_id", StandardBasicTypes.FLOAT);

    List<Object[]> closestGroups = query.list();

    if (closestGroups.isEmpty())
        return new ArrayList<>();

    ArrayList groupsToGet = new ArrayList<>();

    for (Object[] r : closestGroups) {
        int i = (int) r[0];
        groupsToGet.add(i);
    }

    Query getGroups = session.createQuery(
            "from Groups G left join fetch G.tagses left join fetch G.usersGroupses where G.groupId in (:ids)");
    getGroups.setParameterList("ids", groupsToGet);
    getGroups.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
    List<Groups> groupsFound = getGroups.list();

    ArrayList<SearchResult> results = new ArrayList<>();

    for (int i = 0; i < groupsFound.size(); i++) {
        SearchResult s = new SearchResult(groupsFound.get(i), (float) (closestGroups.get(i)[1]));
        results.add(s);
    }
    session.close();
    return results;
}

From source file:de.codesourcery.eve.skills.db.dao.InventoryTypeDAO.java

License:Apache License

@Override
public List<InventoryType> getInventoryTypes(final MarketGroup group) {
    if (group == null) {
        throw new IllegalArgumentException("group must not be NULL");
    }/*from  ww w  .  j  a  va2s. c o m*/

    final List<InventoryType> cached = typeByMarketGroupID.get(group.getId());
    if (cached != null) {
        return new ArrayList<>(cached);
    }
    return execute(new HibernateCallback<List<InventoryType>>() {

        @Override
        public List<InventoryType> doInSession(Session session) {
            final SQLQuery query = session
                    .createSQLQuery("SELECT typeID FROM invTypes WHERE marketGroupID = :marketGroupId");
            query.setParameter("marketGroupId", group.getId());
            query.addScalar("typeID", new LongType());

            final List<InventoryType> result = getInventoryTypesByIDs(query.list());
            typeByMarketGroupID.putIfAbsent(group.getId(), new ArrayList<>(result));
            return result;
        }
    });
}

From source file:de.codesourcery.eve.skills.db.dao.InventoryTypeDAO.java

License:Apache License

@Override
public List<InventoryType> getInventoryTypesWithBlueprints(final MarketGroup group) {
    return execute(new HibernateCallback<List<InventoryType>>() {

        @Override/*from  w ww.  j a va 2s.co  m*/
        public List<InventoryType> doInSession(Session session) {
            final SQLQuery query = session
                    .createSQLQuery("SELECT i.typeID FROM invTypes i , invBlueprintTypes bp WHERE "
                            + "i.marketGroupID = :marketGroupId AND bp.productTypeID = i.typeID");
            query.setParameter("marketGroupId", group.getId());
            query.addScalar("typeID", new LongType());

            return getInventoryTypesByIDs(query.list());
        }
    });
}