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.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  ava 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  ww  w  . j  ava 2  s.c om*/
 */
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 add an affiliation record to the CPR
 *
 * @param db contains the Database object
 * @throws CprException //  ww  w  . j a v  a  2 s .c  o  m
 */
public void addAffiliation(final Database db) throws CprException {

    RulesReturn rulesReturn = null;
    boolean fatalError = false;
    final List<String> existingAffiliations = new ArrayList<String>();
    Long anAffiliationKey = null;
    Long newAffiliationKey = null;
    Long oldAffiliationKey = null;
    final Session session = db.getSession();
    final PersonAffiliation bean = getPersonAffiliationBean();

    // Check to see if the person has any affiliations.  If none, add one.
    // This assumes that all affiliations as a lone affiliations
    String sqlQuery = "from PersonAffiliation where personId = :person_id ";
    Query query = session.createQuery(sqlQuery);
    query.setParameter("person_id", bean.getPersonId());
    Iterator<?> it = query.list().iterator();
    if (!it.hasNext()) {
        session.save(bean);
        session.flush();
    } else {
        newAffiliationKey = bean.getAffiliationKey();
        String newAffiliationString = AffiliationsType.get(newAffiliationKey).toString();
        // Create an arraylist of all existing affiliations to send to the Rules engine.
        // Select the affiliation_ke for all active affiliations. Store the enum string in the arraylist
        final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
        sb.append(
                "SELECT affiliation_key from {h-schema}person_affiliation where person_id = :person_id_in  AND end_date IS NULL");
        final SQLQuery query1 = session.createSQLQuery(sb.toString());
        query1.setParameter("person_id_in", bean.getPersonId());
        query1.addScalar("affiliation_key", StandardBasicTypes.LONG);
        it = query1.list().iterator();

        while (it.hasNext()) {
            anAffiliationKey = (Long) it.next();
            if (anAffiliationKey.equals(newAffiliationKey)) {
                //the specified affiliation is already assigned to the person
                // do nothing
                return;
            }

            existingAffiliations.add(AffiliationsType.get(anAffiliationKey).toString());
        }

        // pass the existingAffliliations to the rules engine 
        String[] existingArray = new String[existingAffiliations.size()];
        existingArray = existingAffiliations.toArray(existingArray);
        rulesReturn = new RulesEngineHelper().processRules("rules.drl", existingArray, newAffiliationString);

        if (rulesReturn.getStatusCode() != 0) {
            fatalError = true;
        } else {
            if ((rulesReturn.getNumberOfFacts() < existingAffiliations.size())
                    || (rulesReturn.getNumberOfFacts() > existingAffiliations.size() + 1)) {
                fatalError = true;
            } else {
                // process the returned array
                final List<String> newFacts = new ArrayList<String>(Arrays.asList(rulesReturn.getFacts()));
                final Iterator<String> facts = newFacts.iterator();
                while (facts.hasNext()) {
                    String newAffiliation = facts.next();
                    Iterator<String> existing = existingAffiliations.iterator();
                    while (existing.hasNext()) {
                        String oldAffiliation = existing.next();
                        if (newAffiliation.equals(oldAffiliation)) {
                            facts.remove();
                            existing.remove();
                        }
                    }
                }

                // if both arrays are now empty nothing needs to change
                if (!(existingAffiliations.isEmpty())) {
                    // make the change
                    if (newFacts.size() != 1) {
                        fatalError = true;
                    } else {
                        // archive the old affiliation
                        // store the new affiliations

                        if (newFacts.get(0).equals(newAffiliationString)) {
                            oldAffiliationKey = AffiliationsType
                                    .valueOf((existingAffiliations.get(0)).toUpperCase().trim()).index();
                            // archive the affiliation
                            sqlQuery = "from PersonAffiliation where personId = :person_id_in AND affiliationKey = :l_aff_key AND endDate IS NULL";
                            query = session.createQuery(sqlQuery);
                            query.setParameter("person_id_in", bean.getPersonId());
                            query.setParameter("l_aff_key", oldAffiliationKey);
                            it = query.list().iterator();
                            if (it.hasNext()) {
                                PersonAffiliation dbBean = (PersonAffiliation) it.next();
                                dbBean.setEndDate(bean.getLastUpdateOn());
                                dbBean.setPrimaryFlag("N");
                                dbBean.setLastUpdateBy(bean.getLastUpdateBy());
                                dbBean.setLastUpdateOn(bean.getLastUpdateOn());
                                session.update(dbBean);
                                session.flush();
                            }

                            session.save(bean);
                            session.flush();
                        } else {
                            fatalError = true;
                        }
                    }
                } else {
                    // add the new affiliation
                    session.save(bean);
                    session.flush();
                }
            }
        }
    }

    if (fatalError) {
        throw new CprException(ReturnType.ADD_FAILED_EXCEPTION, "affiliation");
    }
}

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  w  w  w.j a v  a  2 s . co  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.
 * /*  w  ww. j  a  v a 2 s . c o  m*/
 * @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  a  v  a2s  . com*/
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 a  v  a 2 s .c  om*/
 */
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.
 *//*ww  w . j  av  a2 s . c om*/
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   w w w .  j  a  v a 2  s.co 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 used to archive a person's linkage.
 * @param db contains an open database connection.
 * @throws CprException will be thrown if there are any problems.
 *//*from  w ww. j a v a 2  s . co  m*/
public void archivePersonLinkage(final Database db) throws CprException {

    boolean recordNotFound = false;
    boolean alreadyArchived = false;

    final Session session = db.getSession();
    final PersonLinkage bean = getPersonLinkageBean();

    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
    sb.append("SELECT person_id FROM {h-schema}person_linkage WHERE person_id = :person_id AND ");
    sb.append("linked_person_id = :linked_person_id AND ");
    sb.append("data_type_key = :data_type_key ");
    final SQLQuery query = session.createSQLQuery(sb.toString());
    query.setParameter(PERSON_ID_STRING, bean.getPersonId());
    query.setParameter("linked_person_id", bean.getLinkedPersonId());
    query.setParameter(DATA_TYPE_KEY_STRING, bean.getDataTypeKey());
    query.addScalar(PERSON_ID_STRING, StandardBasicTypes.LONG);
    if (query.list().size() == 0) {
        recordNotFound = true;
    } else {
        sb.delete(0, sb.length());
        sb.append("from PersonLinkage WHERE personId = :person_id AND linkedPersonId = :linked_person_id AND ");
        sb.append("dataTypeKey = :data_type_key AND endDate IS NULL");
        final Query query1 = session.createQuery(sb.toString());
        query1.setParameter(PERSON_ID_STRING, bean.getPersonId());
        query1.setParameter("linked_person_id", bean.getLinkedPersonId());
        query1.setParameter(DATA_TYPE_KEY_STRING, bean.getDataTypeKey());
        final Iterator<?> it = query1.list().iterator();
        if (it.hasNext()) {
            PersonLinkage dbBean = (PersonLinkage) it.next();
            dbBean.setEndDate(bean.getLastUpdateOn());
            dbBean.setLastUpdateBy(bean.getLastUpdateBy());
            dbBean.setLastUpdateOn(bean.getLastUpdateOn());
            session.update(dbBean);
            session.flush();
        } else {
            alreadyArchived = true;
        }
    }

    if (recordNotFound) {
        throw new CprException(ReturnType.RECORD_NOT_FOUND_EXCEPTION, TABLE_NAME);
    }
    if (alreadyArchived) {
        throw new CprException(ReturnType.ALREADY_DELETED_EXCEPTION, TABLE_NAME);
    }
}