Example usage for org.hibernate SQLQuery addScalar

List of usage examples 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:edu.psu.iam.cpr.core.database.tables.UseridTable.java

License:Apache License

/**
 * This routine is to call a stored procedure to set the primary userid for a user.
 * @param db contains a reference to a open database connection.
 * @throws CprException  will be thrown if there are any CPR related errors.
 * /* www.j  a  v a  2  s  .  co m*/
 */
public void setPrimaryUserid(final Database db) throws CprException {

    boolean recordExpired = false;
    boolean alreadyPrimary = false;
    boolean recordNotFound = false;

    final Session session = db.getSession();
    final Userid bean = getUseridBean();

    // For the selected userid, obtain the end date and their primary flag.
    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
    sb.append("SELECT end_date, primary_flag ");
    sb.append("FROM {h-schema}userid ");
    sb.append("WHERE person_id = :person_id_in ");
    sb.append("AND userid = :userid_in ");
    final SQLQuery query = session.createSQLQuery(sb.toString());
    query.setParameter("person_id_in", bean.getPersonId());
    query.setParameter("userid_in", bean.getUserid());
    query.addScalar("end_date", StandardBasicTypes.DATE);
    query.addScalar("primary_flag", StandardBasicTypes.STRING);
    Iterator<?> it = query.list().iterator();

    if (it.hasNext()) {
        Object[] res = (Object[]) it.next();
        bean.setEndDate((Date) res[0]);
        bean.setPrimaryFlag((String) res[1]);

        // Expired, we have an error.
        if (bean.getEndDate() != null) {
            recordExpired = true;
        }

        // Already primary, we have an error.
        else if (Utility.isOptionYes(bean.getPrimaryFlag())) {
            alreadyPrimary = true;
        } else {

            // Switch the current primary record.
            String sqlQuery = "from Userid where personId = :person_id_in AND primaryFlag = 'Y' AND endDate IS NULL";
            Query query1 = session.createQuery(sqlQuery);
            query1.setParameter("person_id_in", bean.getPersonId());
            for (it = query1.list().iterator(); it.hasNext();) {
                Userid dbBean = (Userid) it.next();

                dbBean.setPrimaryFlag("N");
                dbBean.setLastUpdateBy(bean.getLastUpdateBy());
                dbBean.setLastUpdateOn(bean.getLastUpdateOn());
                session.update(dbBean);
                session.flush();
            }

            // Make the new record primary.
            sqlQuery = "from Userid where personId = :person_id_in AND userid = :userid_in AND endDate IS NULL";
            query1 = session.createQuery(sqlQuery);
            query1.setParameter("person_id_in", bean.getPersonId());
            query1.setParameter("userid_in", bean.getUserid());
            for (it = query1.list().iterator(); it.hasNext();) {
                Userid dbBean = (Userid) it.next();
                dbBean.setPrimaryFlag("Y");
                dbBean.setLastUpdateBy(bean.getLastUpdateBy());
                dbBean.setLastUpdateOn(bean.getLastUpdateOn());
                session.update(dbBean);
                session.flush();
            }
        }
    } else {
        recordNotFound = true;
    }

    // Handle other errors.
    if (recordExpired) {
        throw new CprException(ReturnType.ALREADY_DELETED_EXCEPTION, TABLE_NAME);
    }
    if (alreadyPrimary) {
        throw new CprException(ReturnType.SET_PRIMARY_FAILED_EXCEPTION, TABLE_NAME);
    }
    if (recordNotFound) {
        throw new CprException(ReturnType.RECORD_NOT_FOUND_EXCEPTION, TABLE_NAME);
    }
}

From source file:edu.psu.iam.cpr.core.database.tables.UseridTable.java

License:Apache License

/**
 * This routine is used to archive a userid.  It is called by the ArchiveUserid service.
 * @param db contains a reference to an open database connection.
 * @throws CprException will be thrown for any CPR specific problems.
 *//* w  ww.  j a v a 2 s.c  o m*/
public void archiveUserid(final Database db) throws CprException {

    boolean noneActive = false;
    boolean notFound = false;
    boolean alreadyArchived = false;
    boolean cannotArchive = false;

    final Session session = db.getSession();
    final Userid bean = getUseridBean();

    // Determine how many userids are active for the current user.
    String sqlQuery = "SELECT person_id FROM {h-schema}userid WHERE person_id = :person_id_in AND end_date IS NULL";
    SQLQuery query = session.createSQLQuery(sqlQuery);
    query.setParameter("person_id_in", bean.getPersonId());
    query.addScalar("person_id", StandardBasicTypes.LONG);
    final int activeCount = query.list().size();
    if (activeCount == 0) {
        noneActive = true;
    } else {

        // For the selected userid, obtain the end date and their primary flag.
        final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
        sb.append("SELECT end_date, primary_flag ");
        sb.append("FROM {h-schema}userid ");
        sb.append("WHERE person_id = :person_id_in ");
        sb.append("AND userid = :userid_in ");
        query = session.createSQLQuery(sb.toString());
        query.setParameter("person_id_in", bean.getPersonId());
        query.setParameter("userid_in", bean.getUserid());
        query.addScalar("end_date", StandardBasicTypes.DATE);
        query.addScalar("primary_flag", StandardBasicTypes.STRING);
        Iterator<?> it = query.list().iterator();

        if (it.hasNext()) {
            Object[] res = (Object[]) it.next();
            bean.setEndDate((Date) res[0]);
            bean.setPrimaryFlag((String) res[1]);

            // Error if the record already has an end date.
            if (bean.getEndDate() != null) {
                alreadyArchived = true;
            }

            // If there are more than one record and this one is primary, do not all the archival.
            else if (activeCount > 1 && Utility.isOptionYes(bean.getPrimaryFlag())) {
                cannotArchive = true;
            }

            // Otherwise we can do the archive.
            else {
                sqlQuery = "from Userid where personId = :person_id_in AND userid = :userid_in AND endDate IS NULL";
                final Query query1 = session.createQuery(sqlQuery);
                query1.setParameter("person_id_in", bean.getPersonId());
                query1.setParameter("userid_in", bean.getUserid());
                for (it = query1.list().iterator(); it.hasNext();) {
                    Userid dbBean = (Userid) it.next();
                    dbBean.setPrimaryFlag("N");
                    dbBean.setEndDate(bean.getLastUpdateOn());
                    dbBean.setLastUpdateBy(bean.getLastUpdateBy());
                    dbBean.setLastUpdateOn(bean.getLastUpdateOn());
                    session.update(dbBean);
                    session.flush();
                }
            }
        } else {
            notFound = true;
        }

    }

    if (notFound) {
        throw new CprException(ReturnType.RECORD_NOT_FOUND_EXCEPTION, TABLE_NAME);
    }
    if (noneActive) {
        throw new CprException(ReturnType.GENERAL_EXCEPTION,
                "Cannot archive userid, because there are no active userids.");
    }
    if (alreadyArchived) {
        throw new CprException(ReturnType.ALREADY_DELETED_EXCEPTION, TABLE_NAME);
    }
    if (cannotArchive) {
        throw new CprException(ReturnType.GENERAL_EXCEPTION,
                "Cannot archive userid, because its the primary userid.");
    }

}

From source file:edu.psu.iam.cpr.core.database.tables.UseridTable.java

License:Apache License

/**
 * This routine is used to unarchive a userid.  It is called by the UnarchiveUserid service.
 * @param db contains a reference to an open database connection.
 * @throws CprException will be thrown for any CPR specific problems.
 *///from ww  w .  java 2 s .  c  o m
public void unarchiveUserid(final Database db) throws CprException {

    boolean alreadyUnarchived = false;
    boolean noArchivedRecords = false;
    boolean recordNotFound = false;

    final Session session = db.getSession();
    final Userid bean = getUseridBean();

    // See how any userids are archived for the user, if there are none that are archived, we have an error.
    String sqlQuery = "SELECT person_id FROM {h-schema}userid WHERE person_id = :person_id_in AND end_date IS NOT NULL";
    SQLQuery query = session.createSQLQuery(sqlQuery);
    query.setParameter("person_id_in", bean.getPersonId());
    query.addScalar("person_id", StandardBasicTypes.LONG);
    final int archivedCount = query.list().size();

    if (archivedCount == 0) {
        noArchivedRecords = true;
    } else {

        // For the selected userid, obtain the end date and their primary flag.
        final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
        sb.append("SELECT end_date, primary_flag ");
        sb.append("FROM {h-schema}userid ");
        sb.append("WHERE person_id = :person_id_in ");
        sb.append("AND userid = :userid_in ");
        query = session.createSQLQuery(sb.toString());
        query.setParameter("person_id_in", bean.getPersonId());
        query.setParameter("userid_in", bean.getUserid());
        query.addScalar("end_date", StandardBasicTypes.DATE);
        query.addScalar("primary_flag", StandardBasicTypes.STRING);
        Iterator<?> it = query.list().iterator();

        if (it.hasNext()) {
            Object[] res = (Object[]) it.next();
            bean.setEndDate((Date) res[0]);
            bean.setPrimaryFlag((String) res[1]);

            if (bean.getEndDate() == null) {
                alreadyUnarchived = true;
            } else {
                // Determine how many userids are active for the current user.
                sqlQuery = "SELECT person_id FROM {h-schema}userid WHERE person_id = :person_id_in AND end_date IS NULL";
                query = session.createSQLQuery(sqlQuery);
                query.setParameter("person_id_in", bean.getPersonId());
                query.addScalar("person_id", StandardBasicTypes.LONG);
                final int activeCount = query.list().size();

                if (activeCount == 0) {
                    bean.setPrimaryFlag("Y");
                } else {
                    bean.setPrimaryFlag("N");
                }

                // Do the unarchive.
                sqlQuery = "from Userid where personId = :person_id AND userid = :userid_in AND endDate IS NOT NULL";
                final Query query1 = session.createQuery(sqlQuery);
                query1.setParameter("person_id", bean.getPersonId());
                query1.setParameter("userid_in", bean.getUserid());
                for (it = query1.list().iterator(); it.hasNext();) {
                    Userid dbBean = (Userid) it.next();
                    dbBean.setPrimaryFlag(bean.getPrimaryFlag());
                    dbBean.setEndDate(null);
                    dbBean.setLastUpdateBy(bean.getLastUpdateBy());
                    dbBean.setLastUpdateOn(bean.getLastUpdateOn());
                    session.update(dbBean);
                    session.flush();
                }
            }

        } else {
            recordNotFound = true;
        }
    }

    if (alreadyUnarchived) {
        throw new CprException(ReturnType.UNARCHIVE_FAILED_EXCEPTION, "userid");
    }

    if (noArchivedRecords) {
        throw new CprException(ReturnType.GENERAL_EXCEPTION, "There are no records that can be unarchived.");
    }

    if (recordNotFound) {
        throw new CprException(ReturnType.RECORD_NOT_FOUND_EXCEPTION, "userid");
    }
}

From source file:edu.psu.iam.cpr.core.database.tables.UseridTable.java

License:Apache License

/**
 * This routine is used to add a special userid.  It is called by the AddSpecialUserid service.
 * @param db contains a reference to an open database connection.
 * @throws CprException will be thrown for any CPR specific problems.
 *///  ww  w  .  j a v  a 2  s . c  o m
public void addSpecialUserid(final Database db) throws CprException {

    // Verify that the new userid contains valid characters.
    if (!isUseridValid(db, getUseridBean().getUserid())) {
        throw new CprException(ReturnType.INVALID_PARAMETERS_EXCEPTION, TABLE_NAME);
    }

    final Session session = db.getSession();
    final Userid bean = getUseridBean();

    // Fill in the char and number parts of the userid.
    final String charPart = getCharacterPart(bean.getUserid());
    bean.setCharPart(charPart);
    bean.setNumPart(getNumberPart(bean.getUserid(), charPart));

    // Do a select to determine what primary needs to be set to.
    final String sqlQuery = "SELECT person_id FROM {h-schema}userid WHERE person_id = :person_id_in AND end_date IS NULL";
    final SQLQuery query = session.createSQLQuery(sqlQuery);
    query.setParameter("person_id_in", bean.getPersonId());
    query.addScalar("person_id", StandardBasicTypes.LONG);
    if (query.list().size() == 0) {
        bean.setPrimaryFlag("Y");
    } else {
        bean.setDisplayNameFlag("N");
        bean.setPrimaryFlag("N");
    }

    // Save off the new userid record.
    session.save(bean);
    session.flush();

    // Add a record to the psu directory table.
    final PsuDirectoryTable psuDirectoryTable = new PsuDirectoryTable(bean.getPersonId(), bean.getUserid(),
            bean.getLastUpdateBy());
    psuDirectoryTable.addDirectoryTable(db);
}

From source file:edu.psu.iam.cpr.core.database.tables.UseridTable.java

License:Apache License

/**
 * This routine will obtain a list of userids for a person id.
 * @param db contains an open database connection.
 * @param personId contains the person id.
 * @return an array of userids.// w w  w  .j  a  v  a2s.  c  om
 */
public UseridReturn[] getUseridsForPersonId(final Database db, final long personId) {

    final Session session = db.getSession();
    final List<UseridReturn> results = new ArrayList<UseridReturn>();

    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
    sb.append("SELECT userid, primary_flag, ");
    sb.append("start_date, ");
    sb.append("end_date, ");
    sb.append("last_update_by, ");
    sb.append("last_update_on, ");
    sb.append("created_by, ");
    sb.append("created_on ");
    sb.append("FROM {h-schema}userid ");
    sb.append("WHERE person_id = :person_id_in ");
    if (!isReturnHistoryFlag()) {
        sb.append("AND end_date IS NULL ");
    }
    if (getUserid() != null) {
        sb.append("AND userid = :userid ");
    }
    sb.append("ORDER BY start_date");

    final SQLQuery query = session.createSQLQuery(sb.toString());

    query.setParameter("person_id_in", personId);

    if (getUserid() != null) {
        query.setParameter("userid", getUserid());
    }

    query.addScalar("userid", StandardBasicTypes.STRING);
    query.addScalar("primary_flag", StandardBasicTypes.STRING);
    query.addScalar("start_date", StandardBasicTypes.TIMESTAMP);
    query.addScalar("end_date", StandardBasicTypes.TIMESTAMP);
    query.addScalar("last_update_by", StandardBasicTypes.STRING);
    query.addScalar("last_update_on", StandardBasicTypes.TIMESTAMP);
    query.addScalar("created_by", StandardBasicTypes.STRING);
    query.addScalar("created_on", StandardBasicTypes.TIMESTAMP);

    for (final Iterator<?> it = query.list().iterator(); it.hasNext();) {
        Object[] res = (Object[]) it.next();
        results.add(new UseridReturn((String) res[USERID], (String) res[PRIMARY_FLAG],
                Utility.formatDateToISO8601((Date) res[START_DATE]),
                Utility.formatDateToISO8601((Date) res[END_DATE]), (String) res[LAST_UPDATE_BY],
                Utility.formatDateToISO8601((Date) res[LAST_UPDATE_ON]), (String) res[CREATED_BY],
                Utility.formatDateToISO8601((Date) res[CREATED_ON])));
    }

    return results.toArray(new UseridReturn[results.size()]);
}

From source file:edu.psu.iam.cpr.core.database.tables.UseridTable.java

License:Apache License

/**
 * This routine is used to determine if the passed in userid is valid.
 * @param db contains a reference to the database handle.
 * @param userid contains the userid to valid.
 * @return will return true if the userid is valid, otherwise it will return false.
 *//*from   w w w . j  av a2 s.  c  o  m*/
public boolean isUseridValid(final Database db, final String userid) {

    final Session session = db.getSession();
    // Verify that the userid does not contain spaces.
    if (userid.contains(" ")) {
        return false;
    }
    // Verify that the userid only contains letters, numbers, $ and underscore.
    if (!userid.matches("^[a-zA-Z0-9$_]+$")) {
        return false;
    }

    // Obtain the character portion of the userid.
    final String charPart = getCharacterPart(userid);

    // Verify that the userid does not exist in the bad prefixes table.
    String sqlQuery = "SELECT char_part FROM {h-schema}bad_prefixes WHERE char_part = :char_part_in";
    SQLQuery query = session.createSQLQuery(sqlQuery);
    query.setParameter("char_part_in", charPart);
    query.addScalar("char_part", StandardBasicTypes.STRING);
    if (query.list().size() > 0) {
        return false;
    }

    // Verify that the userid does not already exist.
    sqlQuery = "SELECT person_id FROM {h-schema}userid WHERE userid = :userid_in";
    query = session.createSQLQuery(sqlQuery);
    query.setParameter("userid_in", userid);
    query.addScalar("person_id", StandardBasicTypes.LONG);
    if (query.list().size() > 0) {
        return false;
    }

    return true;
}

From source file:edu.psu.iam.cpr.core.messaging.ServiceProvisionerQueue.java

License:Apache License

/**
 * This routine is used to obtain a list of service providers and their respective queues for a particular web service.
 * @param db contains a database connection.
 * @param webService contains the web server to do the query for.
 * @return will return an ArrayList of service provider information.
 */// w ww.java 2 s.  com
public static ArrayList<ServiceProvisionerQueue> getServiceProvisionerQueues(Database db, String webService) {

    final ArrayList<ServiceProvisionerQueue> results = new ArrayList<ServiceProvisionerQueue>();
    final Session session = db.getSession();

    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
    sb.append("SELECT service_provisioner_key, service_provisioner, web_service_key, web_service, ");
    sb.append("service_provisioner_queue FROM v_sp_notification WHERE web_service=:web_service ");

    final SQLQuery query = session.createSQLQuery(sb.toString());
    query.setParameter("web_service", webService);
    query.addScalar("service_provisioner_key", StandardBasicTypes.LONG);
    query.addScalar("service_provisioner", StandardBasicTypes.STRING);
    query.addScalar("web_service_key", StandardBasicTypes.LONG);
    query.addScalar("web_service", StandardBasicTypes.STRING);
    query.addScalar("service_provisioner_queue", StandardBasicTypes.STRING);
    final Iterator<?> it = query.list().iterator();

    while (it.hasNext()) {
        Object res[] = (Object[]) it.next();
        results.add(new ServiceProvisionerQueue((Long) res[SP_KEY], (String) res[SP_NAME],
                (Long) res[WEB_SERVICE_KEY], (String) res[WEB_SERVICE], (String) res[SP_QUEUE]));
    }

    return results;
}

From source file:edu.vt.vbi.patric.dao.DBSummary.java

License:Apache License

/**
 * Finds taxonomy rank "Genus" for a given taxon node.
 * //from   w  w w. ja  va2  s . co  m
 * @param refseq_locus_tag RefSeq Locus Tag
 * @return comments
 */
public List<Map<String, Object>> getTBAnnotation(String refseq_locus_tag) {
    String sql = "select distinct locus_tag, property, value, evidence_code, comments, source"
            + "   from app.tbcap_annotation "
            + "   where locus_tag = :refseq_locus_tag and property != 'Interaction'"
            + "   order by property asc, evidence_code asc ";

    Session session = factory.getCurrentSession();
    session.beginTransaction();
    SQLQuery q = session.createSQLQuery(sql);
    q.setString("refseq_locus_tag", refseq_locus_tag);

    q.addScalar("locus_tag", Hibernate.STRING).addScalar("property", Hibernate.STRING).addScalar("value",
            Hibernate.STRING);
    q.addScalar("evidence_code", Hibernate.STRING).addScalar("comments", Hibernate.STRING).addScalar("source",
            Hibernate.STRING);

    List<Object[]> rset = q.list();

    List<Map<String, Object>> results = new ArrayList<>();
    for (Object[] obj : rset) {

        Map<String, Object> row = new HashMap<>();
        row.put("locus", obj[0]);
        row.put("property", obj[1]);
        row.put("value", obj[2]);
        row.put("evidencecode", obj[3]);
        row.put("comment", obj[4]);
        row.put("source", obj[5]);

        results.add(row);
    }

    // get Interactions
    sql = "select distinct locus_tag, property, value, evidence_code, comments, source"
            + "   from app.tbcap_annotation "
            + "   where locus_tag = :refseq_locus_tag and property = 'Interaction' "
            + "   order by value asc, evidence_code asc ";

    q = session.createSQLQuery(sql);
    q.setString("refseq_locus_tag", refseq_locus_tag);

    q.addScalar("locus_tag", Hibernate.STRING).addScalar("property", Hibernate.STRING).addScalar("value",
            Hibernate.STRING);
    q.addScalar("evidence_code", Hibernate.STRING).addScalar("comments", Hibernate.STRING).addScalar("source",
            Hibernate.STRING);

    rset = q.list();

    for (Object[] obj : rset) {

        Map<String, Object> row = new HashMap<>();
        row.put("locus", obj[0]);
        row.put("property", obj[1]);
        row.put("value", obj[2]);
        row.put("evidencecode", obj[3]);
        row.put("comment", obj[4]);
        row.put("source", obj[5]);

        results.add(row);
    }

    return results;
}

From source file:es.emergya.bbdd.dao.HistoricoGPSHome.java

License:Open Source License

@SuppressWarnings("unchecked")
@Transactional(propagation = Propagation.REQUIRES_NEW, readOnly = true, rollbackFor = Throwable.class)
private List<String> calculateRecursos(Date inicio, Date fin, String flotas,
        List<String> recursosYaEncontrados) {
    int i;/*from   w ww  .j  av a 2 s  .  c  om*/
    StringBuffer sb = new StringBuffer();
    sb.append("select distinct(rec.recurso) as nombreRecurso from ");

    sb.append(" (select st_collect(geom) as geom, recurso from historico_gps where ");

    sb.append(flotas);

    if (recursosYaEncontrados.size() > 0) {
        if (flotas.length() > 0) {
            sb.append(" and ");
        }
        sb.append("recurso not in ('");
        sb.append(recursosYaEncontrados.get(0));
        for (i = 1; i < recursosYaEncontrados.size(); i++) {
            sb.append("', '").append(recursosYaEncontrados.get(i));
        }
        sb.append("') ");
    }

    if (inicio != null) {
        sb.append(" and marca_temporal >= :FECHA_INICIO ");

    }

    if (fin != null) {
        sb.append(" and marca_temporal <= :FECHA_FIN ");
    }

    sb.append(" group by recurso) as rec");

    SQLQuery q = getSession().createSQLQuery(sb.toString());
    if (inicio != null) {
        q.setParameter("FECHA_INICIO", inicio, Hibernate.TIMESTAMP);

    }

    if (fin != null) {
        q.setParameter("FECHA_FIN", fin, Hibernate.TIMESTAMP);
    }

    q.addScalar("nombreRecurso", Hibernate.STRING);

    log.debug(sb.toString() + " => " + inicio + " " + fin);

    List<String> result = q.list();

    return result;
}

From source file:es.emergya.bbdd.dao.HistoricoGPSHome.java

License:Open Source License

/**
 * Dado una lista de zonas y un usuario, devuelve la lista de recursos que
 * el que usuario puede ver por su rol y cuya ltima posicin en la base de
 * datos est en alguna de las zonas pasadas.
 * //from  ww w .  j  a v  a2 s  .  c  o m
 * @param zonas
 * @param u
 * @return
 */
@Transactional(propagation = Propagation.REQUIRES_NEW, readOnly = true, rollbackFor = Throwable.class)
private List<String> calculateRecursosUltimasPosiciones(Usuario u) {
    int i;

    StringBuffer sb = new StringBuffer();
    sb.append("select distinct r.nombre as nombreRecurso ")
            .append("from recursos r inner join flotas f on r.flota_x_flota=f.x_flota ")
            .append("inner join roles_x_flotas rxf on rxf.x_flota = f.x_flota ")
            .append("inner join roles rol on rxf.x_rol=rol.x_rol ")
            .append("inner join usuarios u on u.fk_roles = rol.x_rol ")
            .append("inner join historico_gps h on r.fk_historico_gps = h.x_historico ");

    sb.append("and u.nombre_usuario=:USUARIO ");

    sb.append("order by nombreRecurso");

    SQLQuery q = getSession().createSQLQuery(sb.toString());

    q.addScalar("nombreRecurso", Hibernate.STRING);
    q.setString("USUARIO", u.getNombreUsuario());
    if (log.isDebugEnabled()) {
        log.debug(sb.toString());
    }

    List<String> result = q.list();
    return result;
}