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:edu.psu.iam.cpr.core.database.tables.DateOfBirthTable.java

License:Apache License

/**
 * This routine is used to obtain the date of birth for a user.
 * @param db contains the database connection.
 * @param personId contains the person identifier from the central person registry.
 * @return will return an array of DateOfBirthReturn objects if successful.
 *///from w  w  w  . ja  v a2s  . com
public DateOfBirthReturn[] getDateOfBirthForPersonId(final Database db, long personId) {

    final List<DateOfBirthReturn> results = new ArrayList<DateOfBirthReturn>();
    final Session session = db.getSession();
    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);

    sb.append("SELECT dob_char, ");
    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}date_of_birth ");
    sb.append("WHERE person_id = :person_id_in ");

    // If we are not returning all records, we need to just return the active ones.
    if (!isReturnHistoryFlag()) {
        sb.append("AND end_date IS NULL ");
    }
    sb.append("ORDER BY start_date ASC ");

    final SQLQuery query = session.createSQLQuery(sb.toString());
    query.setParameter("person_id_in", personId);
    query.addScalar("dob_char", 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();
        String dobChar = (String) res[DOB_CHAR];

        if (dobChar != null && dobChar.length() == MMDDYYYY_SIZE) {
            final String month = dobChar.substring(MONTH_START_POSITION, MONTH_END_POSITION);
            final String day = dobChar.substring(DAY_START_POSITION, DAY_END_POSITION);
            final String year = dobChar.substring(YEAR_START_POSITION);

            // Only month and day of DOB was specified.
            if (year.equals(EMPTY_YEAR_STRING)) {
                results.add(new DateOfBirthReturn(month + "/" + day,
                        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])));
            }

            // Full date was specified.
            else {
                results.add(new DateOfBirthReturn(month + "/" + day + "/" + year,
                        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 DateOfBirthReturn[results.size()]);
}

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

License:Apache License

/**
 * This routine is used to retrieve the list of email addresses for a person.
 * @param db contains the database connection.
 * @param personId contains the person id.
 * @return will return a list of email addresses.
 * @throws CprException  /*  ww  w .  j ava 2 s.co m*/
 */
public EmailAddressReturn[] getEmailAddressForPersonId(final Database db, final long personId)
        throws CprException {

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

    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
    sb.append("SELECT email_address_key, data_type_key, email_address, ");
    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}email_address ");
    sb.append("WHERE person_id=:person_id ");

    // If we are not returning all records, we need to just return the active ones.
    if (!isReturnHistoryFlag()) {
        sb.append("AND end_date IS NULL ");
    }

    if (getEmailKey() > 0L) {
        sb.append("AND email_address_key = :email_address_key ");
    }

    sb.append("ORDER BY data_type_key ASC, start_date ASC ");

    final SQLQuery query = session.createSQLQuery(sb.toString());
    query.setParameter(PERSON_ID_STRING, personId);

    if (getEmailKey() > 0L) {
        query.setParameter(EMAIL_ADDRESS_KEY_STRING, getEmailKey());
    }

    query.addScalar(EMAIL_ADDRESS_KEY_STRING, StandardBasicTypes.LONG);
    query.addScalar(DATA_TYPE_KEY_STRING, StandardBasicTypes.LONG);
    query.addScalar("email_address", 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();
        EmailAddressReturn emailAddressReturn = new EmailAddressReturn();
        emailAddressReturn.setEmailKey(((Long) res[EMAIL_KEY]).toString());
        emailAddressReturn.setEmailAddressType(EmailAddressType.get((Long) res[EMAIL_ADDRESS_TYPE]).toString());
        emailAddressReturn.setEmailAddress((String) res[EMAIL_ADDRESS]);
        emailAddressReturn.setStartDate(Utility.formatDateToISO8601((Date) res[START_DATE]));
        emailAddressReturn.setEndDate(Utility.formatDateToISO8601((Date) res[END_DATE]));
        emailAddressReturn.setLastUpdateBy((String) res[LAST_UPDATE_BY]);
        emailAddressReturn.setLastUpdateOn(Utility.formatDateToISO8601((Date) res[LAST_UPDATE_ON]));
        emailAddressReturn.setCreatedBy((String) res[CREATED_BY]);
        emailAddressReturn.setCreatedOn(Utility.formatDateToISO8601((Date) res[CREATED_ON]));
        results.add(emailAddressReturn);
    }

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

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

License:Apache License

/**
 * /*from  ww  w .java2 s.  c o  m*/
 * @param db  contains Database object
 * @param federationName  contains the federationName to validate      
 * @return true if valid federation, false otherwse
 * @throws CprException 
 */
public boolean isFederationValid(final Database db, final String federationName) throws CprException {

    boolean fedValid = false;
    final Session session = db.getSession();
    String localFederationName = (federationName != null) ? federationName.trim() : null;

    String activeFlag = "";
    final String upperFed = localFederationName.toUpperCase();
    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
    sb.append("SELECT active_flag ");
    sb.append("FROM {h-schema}federation ");
    sb.append("WHERE UPPER(federation)  = :fed_name_in ");
    final SQLQuery query = session.createSQLQuery(sb.toString());
    query.setParameter("fed_name_in", upperFed);
    query.addScalar("active_flag", StandardBasicTypes.STRING);
    final Iterator<?> it = query.list().iterator();
    if (it.hasNext()) {
        activeFlag = (String) it.next();
        if (Utility.isOptionYes(activeFlag)) {
            fedValid = true;
        }
    } else {
        fedValid = false;
    }
    if (!fedValid) {
        throw new CprException(ReturnType.INVALID_PARAMETERS_EXCEPTION, "Federation name");
    }
    return fedValid;

}

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

License:Apache License

/**
 * Add an Id Card Print Log event/*from   w  w w.ja v  a  2s.  c  o m*/
 * 
 * @param db
 * @throws CprException
 */
public void addIdCardPrintLog(final Database db) throws CprException {

    boolean noPersonIdCard = false;
    final Session session = db.getSession();
    final IdCardPrintLog bean = getIdCardPrintLogBean();
    final String sqlQuery = "SELECT person_id_card_key FROM {h-schema}person_id_card WHERE id_card_number = :idCard AND end_date IS NULL";
    final SQLQuery query = session.createSQLQuery(sqlQuery);
    query.setParameter("idCard", eventIdCardNumber);
    query.addScalar("person_id_card_key", StandardBasicTypes.LONG);
    final Iterator<?> it = query.list().iterator();
    if (it.hasNext()) {
        bean.setPersonIdCardKey((Long) it.next());
        session.save(bean);
        session.flush();
    } else {
        noPersonIdCard = true;
    }
    if (noPersonIdCard) {
        throw new CprException(ReturnType.ADD_FAILED_EXCEPTION, "Id Card Print Log");
    }
}

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

License:Apache License

/**
 * Get an IdCard Print Log event/*w  w w. j  a v a  2  s .  c o  m*/
 * @param db contains a database connection.
 * @return IdCardPrintLogReturn array of results.
 */
public IdCardPrintLogReturn[] getIdCardPrintLog(final Database db) {

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

    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
    sb.append("SELECT person_id, id_card_number, work_station_ip_address, ");
    sb.append("work_station_name, printed_by , printed_on ");
    sb.append("FROM {h-schema}v_person_id_card_print_log WHERE id_card_number = :id_card_number_in ");
    sb.append("order by printed_on ASC");

    final SQLQuery query = session.createSQLQuery(sb.toString());
    query.setParameter("id_card_number_in", getEventIdCardNumber());
    query.addScalar("person_id", StandardBasicTypes.LONG);
    query.addScalar("id_card_number", StandardBasicTypes.STRING);
    query.addScalar("work_station_ip_address", StandardBasicTypes.STRING);
    query.addScalar("work_station_name", StandardBasicTypes.STRING);
    query.addScalar("printed_by", StandardBasicTypes.STRING);
    query.addScalar("printed_on", StandardBasicTypes.TIMESTAMP);
    for (final Iterator<?> it = query.list().iterator(); it.hasNext();) {
        Object[] res = (Object[]) it.next();
        IdCardPrintLogReturn anIdLog = new IdCardPrintLogReturn();
        anIdLog.setPersonId((Long) res[PERSON_ID]);
        anIdLog.setIdCardNumber((String) res[ID_CARD_NUMBER]);
        anIdLog.setIpAddress((String) res[IP_ADDRESS]);
        anIdLog.setWorkStationName((String) res[WORKSTATION_NAME]);
        anIdLog.setPrintedBy((String) res[PRINTED_BY]);
        anIdLog.setPrintDate(Utility.convertTimestampToString((Date) res[PRINT_DATE]));
        results.add(anIdLog);
    }
    return results.toArray(new IdCardPrintLogReturn[results.size()]);

}

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

License:Apache License

/**
 * This routine is used to obtain id card information for a person.
 * @param db contains the database connection object.
 * @param personId contains the person identifier to do the query for.
 * @return PersonIdCardReturn array./*from  w w  w.  jav a 2s  . c o  m*/
 */
public PersonIdCardReturn[] getIdCardForPersonId(final Database db, final long personId) {

    final List<PersonIdCardReturn> results = new ArrayList<PersonIdCardReturn>();
    final Session session = db.getSession();
    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
    sb.append("SELECT data_type_key, id_card_number, id_serial_number, ");
    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}person_id_card ");
    sb.append("WHERE person_id=:person_id ");

    if (getIdCardType() != null) {
        sb.append("AND data_type_key = :data_type_key_in ");
    }

    if (!isReturnHistoryFlag()) {
        sb.append("AND end_date IS NULL ");
    }

    sb.append("ORDER BY data_type_key ASC, start_date ASC ");
    final SQLQuery query = session.createSQLQuery(sb.toString());
    query.setParameter(PERSON_ID_STRING, personId);

    if (getIdCardType() != null) {
        query.setParameter("data_type_key_in", getIdCardType().index());
    }

    query.addScalar(DATA_TYPE_KEY_STRING, StandardBasicTypes.LONG);
    query.addScalar("id_card_number", StandardBasicTypes.STRING);
    query.addScalar("id_serial_number", 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();
        PersonIdCardReturn personIdCardReturn = new PersonIdCardReturn();
        personIdCardReturn.setIdCardType(IdCardType.get((Long) res[ID_CARD_TYPE]).toString());
        personIdCardReturn.setIdCardNumber((String) res[ID_CARD_NUMBER]);
        personIdCardReturn.setIdSerialNumber((String) res[ID_SERIAL_NUMBER]);
        personIdCardReturn.setStartDate(Utility.convertTimestampToString((Date) res[START_DATE]));
        personIdCardReturn.setEndDate(Utility.convertTimestampToString((Date) res[END_DATE]));
        personIdCardReturn.setLastUpdateBy((String) res[LAST_UPDATE_BY]);
        personIdCardReturn.setLastUpdateOn(Utility.convertTimestampToString((Date) res[LAST_UPDATE_ON]));
        personIdCardReturn.setCreatedBy((String) res[CREATED_BY]);
        personIdCardReturn.setCreatedOn(Utility.convertTimestampToString((Date) res[CREATED_ON]));
        results.add(personIdCardReturn);
    }
    return results.toArray(new PersonIdCardReturn[results.size()]);
}

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

License:Apache License

/**
 * This routine is used to obtain id card number for a person.
 * @param db contains the database connection object.
 * @param personId contains the person identifier to do the query for.
 * @return PersonIdCardNumberReturn array.
 *//*w ww . j av a 2s  .  c  o m*/
public PersonIdCardNumberReturn[] getIdCardNumberForPersonId(final Database db, final long personId) {

    final List<PersonIdCardNumberReturn> results = new ArrayList<PersonIdCardNumberReturn>();
    final Session session = db.getSession();
    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
    sb.append("SELECT data_type_key, id_card_number ");
    sb.append("from {h-schema}person_id_card ");
    sb.append("WHERE person_id=:person_id ");
    sb.append("AND end_date IS NULL ");

    sb.append("ORDER BY data_type_key ASC, start_date ASC ");
    final SQLQuery query = session.createSQLQuery(sb.toString());
    query.setParameter(PERSON_ID_STRING, personId);

    query.addScalar(DATA_TYPE_KEY_STRING, StandardBasicTypes.LONG);
    query.addScalar("id_card_number", StandardBasicTypes.STRING);

    for (final Iterator<?> it = query.list().iterator(); it.hasNext();) {
        Object[] res = (Object[]) it.next();
        PersonIdCardNumberReturn personIdCardNumberReturn = new PersonIdCardNumberReturn();
        personIdCardNumberReturn.setIdCardType(IdCardType.get((Long) res[0]).toString());
        personIdCardNumberReturn.setIdCardNumber((String) res[1]);

        results.add(personIdCardNumberReturn);
    }
    return results.toArray(new PersonIdCardNumberReturn[results.size()]);
}

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

License:Apache License

/**
 * This routine is used to obtain a match set from the database.
 * @param db contains the database object which hold an open database connection.
 * @return will return a List of match results items.
 *//*from   ww w.  j  a  v  a 2 s.  c  o  m*/
public List<MatchResultsTable> getMatchSet(final Database db) {

    final List<MatchResultsTable> matchResultsTable = new ArrayList<MatchResultsTable>();
    final Session session = db.getSession();
    final SQLQuery query = session.createSQLQuery(GET_MATCH_SET_SQL);
    query.setParameter(MATCH_SET_KEY_STRING, getMatchSetKey());
    query.addScalar("match_set_key", StandardBasicTypes.LONG);
    query.addScalar("person_id", StandardBasicTypes.LONG);
    query.addScalar("score", StandardBasicTypes.LONG);
    final Iterator<?> it = query.list().iterator();
    while (it.hasNext()) {
        Object[] res = (Object[]) it.next();
        matchResultsTable.add(new MatchResultsTable((Long) res[RES_MATCH_SET_KEY], (Long) res[RES_PERSON_ID],
                (Long) res[RES_SCORE]));
    }
    return matchResultsTable;
}

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

License:Apache License

/**
 * This routine is used to obtain a match set from the database.
 * This method only retrieved the first n items.
 * //w  w  w .  j a  v a  2  s.  c  o  m
 * @param db contains the database object which hold an open database connection.
 * @param maxResults The maximum number of results to retrieve.
 * 
 * @return will return a List of match results items containing up to maxResults items.
 * 
 */
public List<MatchResultsTable> getMatchSetWithLimit(final Database db, int maxResults) {

    List<MatchResultsTable> matchResultsTable = new ArrayList<MatchResultsTable>(maxResults);
    final Session session = db.getSession();

    final SQLQuery query = session.createSQLQuery(GET_MATCH_SET_SQL_LIMIT);
    query.setParameter(MATCH_SET_KEY_STRING, getMatchSetKey());
    query.setParameter("rownum_in", maxResults);
    query.addScalar("match_set_key", StandardBasicTypes.LONG);
    query.addScalar("person_id", StandardBasicTypes.LONG);
    query.addScalar("score", StandardBasicTypes.LONG);
    final Iterator<?> it = query.list().iterator();

    while (it.hasNext()) {
        Object[] res = (Object[]) it.next();
        matchResultsTable.add(new MatchResultsTable((Long) res[RES_MATCH_SET_KEY], (Long) res[RES_PERSON_ID],
                (Long) res[RES_SCORE]));
    }
    // XXX: error checking on maxResults (>0)

    ((ArrayList<MatchResultsTable>) matchResultsTable).trimToSize();
    return matchResultsTable;
}

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

License:Apache License

/**
 * This routine is used to obtain a match set from the database.
 * This method only retrieved the first n items with a minimum match score.
 * //  ww  w  .j a  v  a 2  s . c  o m
 * @param db contains the database object which hold an open database connection.
 * @param maxResults The maximum number of results to retrieve.
 * @param minMatchScore The minimum match score for all hits.
 * 
 * @return will return an ArrayList of match results items containing up to maxResults items.
 * 
 */
public List<MatchResultsTable> getMatchSetWithLimitAndCutoff(final Database db, int maxResults,
        Long minMatchScore) {

    // XXX: error checking on maxResults (non-negative)
    // XXX: error checking on minMatchScore (non-negative)

    final List<MatchResultsTable> matchResultsTable = new ArrayList<MatchResultsTable>(maxResults);

    final Session session = db.getSession();
    final SQLQuery query = session.createSQLQuery(GET_MATCH_SET_MIN_SCORE_SQL_LIMIT);
    query.setParameter(MATCH_SET_KEY_STRING, getMatchSetKey());
    query.setParameter("score_in", minMatchScore);
    query.setParameter("rownum_in", maxResults);
    query.addScalar("match_set_key", StandardBasicTypes.LONG);
    query.addScalar("person_id", StandardBasicTypes.LONG);
    query.addScalar("score", StandardBasicTypes.LONG);
    final Iterator<?> it = query.list().iterator();

    while (it.hasNext()) {
        Object[] res = (Object[]) it.next();
        matchResultsTable.add(new MatchResultsTable((Long) res[RES_MATCH_SET_KEY], (Long) res[RES_PERSON_ID],
                (Long) res[RES_SCORE]));
    }

    ((ArrayList<MatchResultsTable>) matchResultsTable).trimToSize();
    return matchResultsTable;
}