Example usage for org.hibernate.type StandardBasicTypes STRING

List of usage examples for org.hibernate.type StandardBasicTypes STRING

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.type.*
  5. StandardBasicTypes
  6. STRING

Introduction

In this page you can find the example usage for org.hibernate.type StandardBasicTypes STRING.

Prototype

StringType STRING

To view the source code for org.hibernate.type StandardBasicTypes STRING.

Click Source Link

Document

The standard Hibernate type for mapping String to JDBC java.sql.Types#VARCHAR VARCHAR .

Usage

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.
 *//*from w w  w  .  jav a2s  .co  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.MessageLogHistoryTable.java

License:Apache License

/**
 * This routine is used to get a message history log.
 * @param db contains an open database connection.
 * @param messageId contains the message identifier.
 * @return will return an array of message log history information.
 *///from  w  ww.j av  a  2s.  c  o  m
public MessageLogHistoryReturn[] getMessageLogHistory(final Database db, final String messageId) {

    // Init some variables.
    final List<MessageLogHistoryReturn> results = new ArrayList<MessageLogHistoryReturn>();
    final Session session = db.getSession();
    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);

    // Build the select statement as a string.
    sb.append("SELECT ");
    sb.append("message_log_key, ");
    sb.append("message_sent_timestamp, ");
    sb.append("message_received_timestamp, ");
    sb.append("message_received, ");
    sb.append("error_code, ");
    sb.append("error_message, ");
    sb.append("try_number ");
    sb.append("FROM {h-schema}message_log_history ");
    sb.append("WHERE message_id = :message_id_in ");

    // Create the hibernate select statement.
    final SQLQuery query = session.createSQLQuery(sb.toString());
    query.setParameter("message_id_in", messageId);
    query.addScalar("message_log_key", StandardBasicTypes.LONG);
    query.addScalar("message_sent_timestamp", StandardBasicTypes.TIMESTAMP);
    query.addScalar("message_received_timestamp", StandardBasicTypes.TIMESTAMP);
    query.addScalar("message_received", StandardBasicTypes.STRING);
    query.addScalar("error_code", StandardBasicTypes.STRING);
    query.addScalar("error_message", StandardBasicTypes.STRING);
    query.addScalar("try_number", StandardBasicTypes.LONG);

    final Iterator<?> it = query.list().iterator();

    // Loop for the results.
    while (it.hasNext()) {

        // For each result, store its value in the return class.
        Object[] res = (Object[]) it.next();

        MessageLogHistoryReturn msgLogHistory = new MessageLogHistoryReturn();
        msgLogHistory.setMessageId(messageId);
        msgLogHistory.setMessageLogKey((Long) res[MESSAGE_LOG_KEY]);
        msgLogHistory.setMessageSentTimestamp((Timestamp) res[MESSAGE_SENT_TIMESTAMP]);
        msgLogHistory.setMessageReceivedTimestamp((Timestamp) res[MESSAGE_RECEIVED_TIMESTAMP]);
        msgLogHistory.setMessageReceived((String) res[MESSAGE_RECEIVED]);
        msgLogHistory.setErrorCode((String) res[ERROR_CODE]);
        msgLogHistory.setErrorMessage((String) res[ERROR_MESSAGE]);
        msgLogHistory.setTryNumber((Long) res[TRY_NUMBER]);
        results.add(msgLogHistory);
    }

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

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

License:Apache License

/**
 * This routine will obtain a list of names for a person id.
 * @param db contains the database connection.
 * @param personId contains the person id.
 * @return list of names.//from   www. j  a v a  2s .com
 */
public NameReturn[] getNamesForPersonId(final Database db, final long personId) {

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

    // Build the query string.
    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
    sb.append(
            "SELECT name_key, data_type_key, document_type_key, first_name, middle_names, last_name, suffix, nickname, ");
    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}names ");
    sb.append("WHERE person_id = :person_id_in ");

    // If we are doing a query for a specific name type, we need to specify this clause.
    if (getNameType() != null) {
        sb.append("AND data_type_key = :data_type_key_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 ");
    }

    // Check to see if we are returning a specific name record 
    if (getNameKey() > 0L) {
        sb.append("AND name_key = :name_key_in ");
    }

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

    // Set up hibernate for the query, bind parameters and determine return types.
    final SQLQuery query = session.createSQLQuery(sb.toString());
    query.setParameter("person_id_in", personId);

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

    if (getNameKey() > 0L) {
        query.setParameter("name_key_in", getNameKey());
    }

    query.addScalar(NAME_KEY_STRING, StandardBasicTypes.LONG);
    query.addScalar(DATA_TYPE_KEY_STRING, StandardBasicTypes.LONG);
    query.addScalar(DOCUMENT_TYPE_KEY_STRING, StandardBasicTypes.LONG);
    query.addScalar("first_name", StandardBasicTypes.STRING);
    query.addScalar("middle_names", StandardBasicTypes.STRING);
    query.addScalar("last_name", StandardBasicTypes.STRING);
    query.addScalar("suffix", StandardBasicTypes.STRING);
    query.addScalar("nickname", 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);

    // Perform the query.
    for (final Iterator<?> it = query.list().iterator(); it.hasNext();) {
        Object[] res = (Object[]) it.next();
        NameReturn nameReturn = new NameReturn();

        nameReturn.setNameKey(((Long) res[NAME_KEY]).toString());
        nameReturn.setNameType(NameType.get((Long) res[NAME_TYPE]).toString());

        if ((Long) res[DOCUMENT_TYPE] != null) {
            nameReturn.setDocumentType(DocumentType.get((Long) res[DOCUMENT_TYPE]).toString());
        } else {
            nameReturn.setDocumentType(null);
        }

        nameReturn.setFirstName((String) res[FIRST_NAME]);
        nameReturn.setMiddleNames((String) res[MIDDLE_NAMES]);
        nameReturn.setLastName((String) res[LAST_NAME]);
        nameReturn.setSuffix((String) res[SUFFIX]);
        nameReturn.setNickname((String) res[NICKNAME]);
        nameReturn.setStartDate(Utility.formatDateToISO8601((Date) res[START_DATE]));
        nameReturn.setEndDate(Utility.formatDateToISO8601((Date) res[END_DATE]));
        nameReturn.setLastUpdateBy((String) res[LAST_UPDATE_BY]);
        nameReturn.setLastUpdateOn(Utility.formatDateToISO8601((Date) res[LAST_UPDATE_ON]));
        nameReturn.setCreatedBy((String) res[CREATED_BY]);
        nameReturn.setCreatedOn(Utility.formatDateToISO8601((Date) res[CREATED_ON]));

        results.add(nameReturn);

    }

    // Check on the results.
    return results.toArray(new NameReturn[results.size()]);
}

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

License:Apache License

/**
 * This method will obtain a list of all PSU affiliations for a person id
 * @param db contains the Database object
 * @param personId contains the person id.
 * /* www.ja  v a2s .c  om*/
 * @return an array of PSU Affiliations.
 */
public AffiliationReturn[] getAllAffiliationsForPersonId(final Database db, final long personId) {

    final List<AffiliationReturn> results = new ArrayList<AffiliationReturn>();
    final Session session = db.getSession();
    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
    sb.append("SELECT affiliations.enum_string, affiliations.affiliation, person_affiliation.primary_flag, ");
    sb.append("person_affiliation.start_date, ");
    sb.append("person_affiliation.end_date, ");
    sb.append("person_affiliation.last_update_by, ");
    sb.append("person_affiliation.last_update_on, ");
    sb.append("person_affiliation.created_by, ");
    sb.append("person_affiliation.created_on, ");
    sb.append("person_affiliation.affiliation_key ");
    sb.append("FROM {h-schema}person_affiliation ");
    sb.append("LEFT JOIN {h-schema}affiliations ");
    sb.append("ON person_affiliation.affiliation_key = affiliations.affiliation_key ");
    sb.append("WHERE person_affiliation.person_id = :person_id_in ");
    if (!isReturnHistoryFlag()) {
        sb.append("AND person_affiliation.end_date IS NULL ");
    }

    sb.append("ORDER BY person_affiliation.affiliation_key ASC, person_affiliation.start_date ASC ");
    SQLQuery query = session.createSQLQuery(sb.toString());
    query.setParameter("person_id_in", personId);
    query.addScalar("enum_string", StandardBasicTypes.STRING);
    query.addScalar("affiliation", 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);

    Iterator<?> it = query.list().iterator();
    while (it.hasNext()) {
        Object[] res = (Object[]) it.next();
        AffiliationReturn newAffRet = new AffiliationReturn();
        newAffRet.setAffiliationType((String) res[ALL_AFFILIATION_TYPE]);
        newAffRet.setAffiliation((String) res[ALL_AFFILIATION]);
        newAffRet.setPrimary((String) res[ALL_PRIMARY]);
        newAffRet.setStartDate(Utility.formatDateToISO8601((Date) res[ALL_START_DATE]));
        newAffRet.setEndDate(Utility.formatDateToISO8601((Date) res[ALL_END_DATE]));
        newAffRet.setLastUpdateBy((String) res[ALL_LAST_UPDATE_BY]);
        newAffRet.setLastUpdateOn(Utility.formatDateToISO8601((Date) res[ALL_LAST_UPDATE_ON]));
        newAffRet.setCreatedBy((String) res[ALL_CREATED_BY]);
        newAffRet.setCreatedOn(Utility.formatDateToISO8601((Date) res[ALL_CREATED_ON]));
        results.add(newAffRet);
    }

    sb.setLength(0);
    sb.append("SELECT DISTINCT ");
    sb.append("person_affiliation.primary_flag, ");
    sb.append("v_ext_affiliation_mapping.ext_affiliation_type, ");
    sb.append("v_ext_affiliation_mapping.ext_affiliation ");
    sb.append("FROM {h-schema}person_affiliation JOIN {h-schema}v_ext_affiliation_mapping ");
    sb.append("ON person_affiliation.affiliation_key = v_ext_affiliation_mapping.affiliation_key ");
    sb.append("AND v_ext_affiliation_mapping.extaff_active_flag = 'Y' ");
    sb.append("AND v_ext_affiliation_mapping.aff_active_flag = 'Y' ");
    sb.append("WHERE person_affiliation.person_id = :person_id_in ");
    sb.append("AND person_affiliation.end_date IS NULL ");
    query = session.createSQLQuery(sb.toString());
    query.setParameter("person_id_in", personId);
    query.addScalar("primary_flag", StandardBasicTypes.STRING);
    query.addScalar("ext_affiliation_type", StandardBasicTypes.STRING);
    query.addScalar("ext_affiliation", StandardBasicTypes.STRING);
    it = query.list().iterator();

    while (it.hasNext()) {
        Object[] res = (Object[]) it.next();

        AffiliationReturn newAffRet = new AffiliationReturn();
        newAffRet.setPrimary((String) res[EXT_AFF_PRIMARY_FLAG]);
        newAffRet.setAffiliationType((String) res[EXT_AFF_AFFILIATION_TYPE]);
        newAffRet.setAffiliation((String) res[EXT_AFF_AFFILIATION]);
        results.add(newAffRet);
    }

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

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

License:Apache License

/**
 * This method will obtain a list of PSU Affiliation for a person id.
 * @param db contains the Database object
 * @param personId contains the person id.
 * //  www.j  a  v  a  2 s .  c  om
 * @return An array of PSU Affiliations.
 * @throws CprException 
 *     
 */
public AffiliationReturn[] getExternalAffiliationsForPersonId(final Database db, final long personId)
        throws CprException {
    final List<AffiliationReturn> results = new ArrayList<AffiliationReturn>();

    final Session session = db.getSession();

    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
    sb.append("SELECT affiliations.enum_string, person_affiliation.primary_flag ");
    sb.append("FROM {h-schema}person_affiliation LEFT JOIN {h-schema}affiliations ");
    sb.append("ON person_affiliation.affiliation_key = affiliations.affiliation_key ");
    sb.append("WHERE person_affiliation.person_id = :person_id_in ");
    sb.append("AND person_affiliation.end_date IS NULL ");
    final SQLQuery query = session.createSQLQuery(sb.toString());
    query.setParameter("person_id_in", personId);
    query.addScalar("enum_string", StandardBasicTypes.STRING);
    query.addScalar("primary_flag", StandardBasicTypes.STRING);
    final Iterator<?> it = query.list().iterator();

    while (it.hasNext()) {
        Object[] res = (Object[]) it.next();

        AffiliationReturn newAffRet = new AffiliationReturn();
        // fix the call  
        // assume eduPerson 
        // need to addess mulitple federation
        newAffRet.setAffiliation(getEduPersonAffiliation((String) res[EDUPERSON_AFFILIATION]));
        newAffRet.setPrimary((String) res[EDUPERSON_PRIMARY_FLAG]);
        newAffRet.setAffiliationType("eduPerson");
        results.add(newAffRet);
    }

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

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

License:Apache License

/**
 * This method will obtain a list of PSU Affiliation for a person id.
 * @param db contains the Database object
 * @param personId contains the person id.
 * @return an array of PSU Affiliations.
 *//*w w  w . j ava  2  s  .  c  o m*/
public AffiliationReturn[] getInternalAffiliationsForPersonId(final Database db, final long personId) {

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

    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
    sb.append("SELECT affiliations.enum_string, affiliations.affiliation,person_affiliation.primary_flag, ");
    sb.append("person_affiliation.start_date, ");
    sb.append("person_affiliation.end_date, ");
    sb.append("person_affiliation.last_update_by, ");
    sb.append("person_affiliation.last_update_on, ");
    sb.append("person_affiliation.created_by, ");
    sb.append("person_affiliation.created_on, ");
    sb.append("person_affiliation.affiliation_key ");
    sb.append("FROM {h-schema}person_affiliation ");
    sb.append("LEFT JOIN {h-schema}affiliations ");
    sb.append("ON person_affiliation.affiliation_key = affiliations.affiliation_key ");
    sb.append("WHERE person_affiliation.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 person_affiliation.end_date IS NULL ");
    }
    sb.append("ORDER BY person_affiliation.affiliation_key ASC, person_affiliation.start_date ASC ");
    final SQLQuery query = session.createSQLQuery(sb.toString());
    query.setParameter("person_id_in", personId);
    query.addScalar("enum_string", StandardBasicTypes.STRING);
    query.addScalar("affiliation", 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);
    query.addScalar("affiliation_key", StandardBasicTypes.INTEGER);

    Iterator<?> it = query.list().iterator();
    while (it.hasNext()) {
        Object[] res = (Object[]) it.next();
        AffiliationReturn newAffRet = new AffiliationReturn();
        newAffRet.setAffiliationType((String) res[INT_AFFILIATION_TYPE]);
        newAffRet.setAffiliation((String) res[INT_AFFILIATION]);
        newAffRet.setPrimary((String) res[INT_PRIMARY_FLAG]);
        newAffRet.setStartDate(Utility.convertTimestampToString((Date) res[INT_START_DATE]));
        newAffRet.setEndDate(Utility.convertTimestampToString((Date) res[INT_END_DATE]));
        newAffRet.setLastUpdateBy((String) res[INT_LAST_UPDATE_BY]);
        newAffRet.setLastUpdateOn(Utility.convertTimestampToString((Date) res[INT_LAST_UPDATE_ON]));
        newAffRet.setCreatedBy((String) res[INT_CREATED_BY]);
        newAffRet.setCreatedOn(Utility.convertTimestampToString((Date) res[INT_CREATED_ON]));
        results.add(newAffRet);
    }

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

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

License:Apache License

/**
 * This method is used to set a user's primary name.
 * @param db contains the Database object
 * @throws CprException //from   w  w w .j  ava  2s .c o  m
 */
public void setPrimaryAffiliation(final Database db) throws CprException {

    boolean notFound = false;
    boolean alreadyPrimary = false;

    final Session session = db.getSession();
    final PersonAffiliation bean = getPersonAffiliationBean();

    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
    sb.append("SELECT  primary_flag ");
    sb.append("FROM {h-schema}person_affiliation ");
    sb.append("WHERE person_id = :person_id_in ");
    sb.append("AND affiliation_key = :affiliation_key_in ");
    sb.append("AND end_date IS NULL ");

    final SQLQuery query = session.createSQLQuery(sb.toString());
    query.setParameter("person_id_in", bean.getPersonId());
    query.setParameter("affiliation_key_in", bean.getAffiliationKey());
    query.addScalar("primary_flag", StandardBasicTypes.STRING);

    Iterator<?> it = query.list().iterator();
    if (!it.hasNext()) {
        notFound = true;
    } else {
        String primaryFlag = (String) it.next();
        if (Utility.isOptionYes(primaryFlag)) {
            alreadyPrimary = true;
        } else {

            String sqlQuery = "from PersonAffiliation where personId = :person_id and primaryFlag = 'Y' and endDate is null";
            Query query1 = session.createQuery(sqlQuery);
            query1.setParameter("person_id", bean.getPersonId());
            it = query1.list().iterator();
            while (it.hasNext()) {
                PersonAffiliation dbBean = (PersonAffiliation) it.next();
                dbBean.setPrimaryFlag("N");
                dbBean.setLastUpdateBy(bean.getLastUpdateBy());
                dbBean.setLastUpdateOn(bean.getLastUpdateOn());
                session.update(dbBean);
                session.flush();
            }

            sqlQuery = "from PersonAffiliation where personId = :person_id and affiliationKey = :affiliation_key and endDate IS NULL";
            query1 = session.createQuery(sqlQuery);
            query1.setParameter("person_id", bean.getPersonId());
            query1.setParameter("affiliation_key", bean.getAffiliationKey());
            it = query1.list().iterator();
            if (it.hasNext()) {
                PersonAffiliation dbBean = (PersonAffiliation) it.next();
                dbBean.setPrimaryFlag("Y");
                dbBean.setLastUpdateBy(bean.getLastUpdateBy());
                dbBean.setLastUpdateOn(bean.getLastUpdateOn());
                session.update(dbBean);
                session.flush();
            }
        }
    }

    if (notFound) {
        throw new CprException(ReturnType.RECORD_NOT_FOUND_EXCEPTION, TABLE_NAME);
    }

    if (alreadyPrimary) {
        throw new CprException(ReturnType.SET_PRIMARY_FAILED_EXCEPTION, TABLE_NAME);
    }
}

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

License:Apache License

/**
 * This routine is used to obtain the gender for a user.
 * @param db contain the database connection.
 * @param personId contains the person identifier from the central person registry.
 * @return will return an array of GenderReturn objects.
 */// w  w  w . ja v  a2s  .  c  o m
public GenderReturn[] getGenderForPersonId(final Database db, long personId) {

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

    sb.append("SELECT data_type_key, ");
    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_gender ");
    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("data_type_key", StandardBasicTypes.LONG);
    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 GenderReturn(GenderType.get((Long) res[GENDER_TYPE]).toString(),
                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 GenderReturn[results.size()]);
}

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

License:Apache License

/**
 * This routine is used to obtain all of the system identifiers for a person.
 * @param db contains the database connection.
 * @param personId contains the person identifier to retrieve information for.
 * @return will return a person identifier return array.
 *//* w  ww .ja  v a2s  .  com*/
public PersonIdentifierReturn[] getPersonIdentifiersForPersonId(final Database db, final long personId) {
    final List<PersonIdentifierReturn> results = new ArrayList<PersonIdentifierReturn>();
    final Session session = db.getSession();

    // Build the query string.
    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
    sb.append("SELECT type_key, identifier_value, ");
    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_identifier ");
    sb.append("WHERE person_id = :person_id_in ");

    // If we are doing a query for a specific identifier type, we need to specify this clause.
    if (getIdentifierType() != null) {
        sb.append("AND type_key = :type_key_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 type_key ASC, start_date ASC ");

    // Set up hibernate for the query, bind parameters and determine return types.
    final SQLQuery query = session.createSQLQuery(sb.toString());
    query.setParameter("person_id_in", personId);

    if (getIdentifierType() != null) {
        query.setParameter("type_key_in", getIdentifierType().getTypeKey());
    }

    query.addScalar(TYPE_KEY_STRING, StandardBasicTypes.LONG);
    query.addScalar("identifier_value", 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);

    Map<String, Object> map = DBTypes.INSTANCE.getTypeMaps(DBTypes.IDENTIFIER_TYPE);

    // Perform the query.
    for (final Iterator<?> it = query.list().iterator(); it.hasNext();) {
        Object[] res = (Object[]) it.next();
        PersonIdentifierReturn personIdentifierReturn = new PersonIdentifierReturn();

        // Need to find the matching identifier type object.
        Long typeKey = (Long) res[TYPE_KEY];
        for (Map.Entry<String, Object> entry : map.entrySet()) {
            IdentifierType idType = (IdentifierType) entry.getValue();
            if (idType.getTypeKey().equals(typeKey)) {
                personIdentifierReturn.setIdentifierTypeString(idType.getTypeName());
                break;
            }
        }

        personIdentifierReturn.setIdentifierValue((String) res[IDENTIFER_VALUE]);
        personIdentifierReturn.setStartDate(Utility.formatDateToISO8601((Date) res[START_DATE]));
        personIdentifierReturn.setEndDate(Utility.formatDateToISO8601((Date) res[END_DATE]));
        personIdentifierReturn.setLastUpdateBy((String) res[LAST_UPDATE_BY]);
        personIdentifierReturn.setLastUpdateOn(Utility.formatDateToISO8601((Date) res[LAST_UPDATE_ON]));
        personIdentifierReturn.setCreatedBy((String) res[CREATED_BY]);
        personIdentifierReturn.setCreatedOn(Utility.formatDateToISO8601((Date) res[CREATED_ON]));

        results.add(personIdentifierReturn);
    }

    // Check on the results.
    return results.toArray(new PersonIdentifierReturn[results.size()]);
}

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

License:Apache License

/**
 * This routine is use to obtain person linkage information for a person in the registry.
 * @param db contains an open database connection.
 * @param personId contains the person identifier to perform the query for.
 * @return will return a PersonLinkageReturn array if successful, otherwise it will return null.
 *//*from   ww  w  .ja va 2  s  .  c  om*/
public PersonLinkageReturn[] getPersonLinkage(final Database db, final long personId) {

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

    // Build the query string.
    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
    sb.append("SELECT data_type_key, person_id, linked_person_id, ");
    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_linkage ");
    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 data_type_key ASC, start_date ASC ");

    // Set up hibernate for the query, bind parameters and determine return types.
    final SQLQuery query = session.createSQLQuery(sb.toString());
    query.setParameter("person_id_in", personId);
    query.addScalar(DATA_TYPE_KEY_STRING, StandardBasicTypes.LONG);
    query.addScalar(PERSON_ID_STRING, StandardBasicTypes.LONG);
    query.addScalar("linked_person_id", StandardBasicTypes.LONG);
    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);

    // Perform the query.
    for (final Iterator<?> it = query.list().iterator(); it.hasNext();) {
        Object[] res = (Object[]) it.next();
        PersonLinkageReturn personLinkageReturn = new PersonLinkageReturn(
                LinkageType.get((Long) res[LINKAGE_TYPE]).toString(), (Long) res[PERSON_ID],
                (Long) res[LINKED_PERSON_ID], 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]));
        results.add(personLinkageReturn);

    }

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