Example usage for org.hibernate.type StandardBasicTypes TIMESTAMP

Introduction

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

Prototype

TimestampType TIMESTAMP

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

Click Source Link

Document

The standard Hibernate type for mapping java.util.Date ( java.sql.Timestamp ) to JDBC java.sql.Types#TIMESTAMP TIMESTAMP .

Usage

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  /*from  w ww  .  j av a  2s.  c om*/
 */
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.IdCardPrintLogTable.java

License:Apache License

/**
 * Get an IdCard Print Log event//from ww w  . j ava  2s. 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./*  w w w . j a va 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.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.
 */// w  w w. java 2s.  co  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.// ww w . j  ava  2  s  .co  m
 */
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.
 * //from   www  .j  av  a2  s  .c  o  m
 * @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.
 * @return an array of PSU Affiliations.
 *//* w  w w  .ja  v  a 2s . 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.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.
 */// www.j a  v a  2s .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.
 *//*from ww w  .  j a v a2 s  . c  o  m*/
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 w  w w. j a 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()]);
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• »