Example usage for org.hibernate SQLQuery setParameter

Introduction

In this page you can find the example usage for org.hibernate SQLQuery setParameter.

Prototype

@Override
    NativeQuery<T> setParameter(int position, Object val);

Source Link

Usage

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 /*from  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 . ja 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.
 * //ww w .  j a va  2s .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.
 *//*from  w w  w .ja  v a  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 .  ja  v a  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.
 *//*from  w  w w.  j a va 2  s . 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   w  ww  .java  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.
 *//* w  w  w. j  av a 2 s  . c om*/
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);
    }
}

---

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.
 *//*w  ww .  jav a 2s.  co m*/
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()]);
}

---

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

License:Apache License

/**
 * Retrieve all active PSU IAPS for a userid.
 *
 * @param db contains the Database Object
 * @param personId contains the personId
 * @param userid The userid to query//from  ww  w .  j  ava  2  s. c om
 * @param federation contains the name of the federation
 * @return A list of IAPs
 * @throws CprException
 */
public IAPReturn[] getExternalIAP(final Database db, final long personId, final String userid,
        final String federation) throws CprException {

    boolean useridValid = false;
    final Session session = db.getSession();
    useridValid = db.isValidUserid(personId, userid);

    if (!useridValid) {
        throw new CprException(ReturnType.INVALID_PARAMETERS_EXCEPTION, USERID_ARG);
    }

    final String upperFed = federation.toUpperCase();
    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);

    final List<IAPReturn> results = new ArrayList<IAPReturn>();
    sb.append("SELECT external_iap ");
    sb.append("FROM {h-schema}v_external_iap_federation ");
    sb.append("WHERE userid = :userid_in ");
    sb.append("AND person_id = :person_id ");
    sb.append("AND UPPER(federation)=:federation_in");
    final SQLQuery query = session.createSQLQuery(sb.toString());
    query.setParameter("userid_in", userid);
    query.setParameter("person_id", personId);
    query.setParameter("federation_in", upperFed);
    final Iterator<?> it = query.list().iterator();

    while (it.hasNext()) {
        String iapFed = (String) it.next();
        IAPReturn anIAP = new IAPReturn();
        anIAP.setIap(iapFed);
        results.add(anIAP);
    }

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

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »