Example usage for org.hibernate SQLQuery setParameter

List of usage examples for org.hibernate SQLQuery setParameter

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.*
  5. SQLQuery
  6. 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.AddressesTable.java

License:Apache License

/**
 * The purpose of this routine is to interface with the database with hibernate  to 
 * add an  address to a user's record.  The information necessary to add
 * the address is passed in the AddressesTable class.
 *  @param db contains the Database object
 * //from   w  ww .  j a  v a  2  s .  co m
 * @throws CprException 
 */
public void addAddress(final Database db) throws CprException {

    boolean matchFound = false;
    final Session session = db.getSession();
    final Addresses bean = getAddressesBean();
    Long maxGroupId = null;
    String sqlQuery = null;
    Query query = null;
    if (bean.getDocumentTypeKey() == null) {
        sqlQuery = "from Addresses where personId = :person_id AND dataTypeKey = :data_type_key AND endDate IS NULL";
        query = session.createQuery(sqlQuery);
        query.setParameter(PERSON_ID_STRING, bean.getPersonId());
        query.setParameter(DATA_TYPE_KEY_STRING, bean.getDataTypeKey());
    } else {
        sqlQuery = "from Addresses where personId = :person_id AND dataTypeKey = :data_type_key AND  documentTypeKey = :document_type_key AND endDate IS NULL";
        query = session.createQuery(sqlQuery);
        query.setParameter(PERSON_ID_STRING, bean.getPersonId());
        query.setParameter(DATA_TYPE_KEY_STRING, bean.getDataTypeKey());
        query.setParameter(DOCUMENT_TYPE_KEY_STRING, bean.getDocumentTypeKey());
    }

    for (final Iterator<?> it = query.list().iterator(); it.hasNext() && (!matchFound);) {
        Addresses dbBean = (Addresses) it.next();

        // Check to ensure that the fields are not already there
        // ignore campus code and country code

        if (Utility.areStringFieldsEqual(dbBean.getAddress1(), bean.getAddress1())
                && Utility.areStringFieldsEqual(dbBean.getAddress2(), bean.getAddress2())
                && Utility.areStringFieldsEqual(dbBean.getAddress3(), bean.getAddress3())
                && Utility.areStringFieldsEqual(dbBean.getCity(), bean.getCity())
                && Utility.areStringFieldsEqual(dbBean.getState(), bean.getState())
                && Utility.areStringFieldsEqual(dbBean.getProvince(), bean.getProvince())
                && Utility.areStringFieldsEqual(dbBean.getPostalCode(), bean.getPostalCode())
                && Utility.areLongFieldsEqual(dbBean.getCampusCodeKey(), bean.getCampusCodeKey())
                && Utility.areLongFieldsEqual(dbBean.getCountryKey(), bean.getCountryKey())) {
            matchFound = true;
        } else {
            // is there already a address with the same Document type 
            if (bean.getDocumentTypeKey() != null && dbBean.getDocumentTypeKey() != null
                    && bean.getDocumentTypeKey().equals(dbBean.getDocumentTypeKey())) {
                matchFound = true;
            }
        }
    }

    // If no match is found, find the maximum group id for the person and their address type combination
    // the group id is associated with addresstype.  the document type for addresses of type DOCUMENTED_ADDRESS is ignored when
    // setting group id

    if (!matchFound) {
        sqlQuery = " SELECT MAX(group_id) as max_group_id FROM {h-schema}addresses WHERE person_id=:person_id and data_type_key=:data_type_key";
        final SQLQuery query1 = session.createSQLQuery(sqlQuery);
        query1.setParameter(PERSON_ID_STRING, bean.getPersonId());
        query1.setParameter(DATA_TYPE_KEY_STRING, bean.getDataTypeKey());
        query1.addScalar("max_group_id", StandardBasicTypes.LONG);
        final Iterator<?> it = query1.list().iterator();
        // Oracle results in a return value of null if no max_group_id -    that is this address type has no records assigned to it for person
        //  else s included if another database behaves differently
        if (it.hasNext()) {
            maxGroupId = (Long) it.next();
            maxGroupId = (maxGroupId == null) ? 1L : maxGroupId + 1L;
        } else {
            maxGroupId = 1L;

        }

        // save off the new record
        bean.setGroupId(maxGroupId);

        if (MatchingAlgorithmType.valueOf(CprProperties.INSTANCE.getProperties().getProperty(
                CprPropertyName.CPR_MATCHING_ALGORITHM.toString())) == MatchingAlgorithmType.PENN_STATE) {
            getAddressCityMatchCode(bean);
        }

        session.save(bean);
        session.flush();
    }
    if (matchFound) {
        throw new CprException(ReturnType.RECORD_ALREADY_EXISTS, TABLE_NAME);
    }

}

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

License:Apache License

/**
 * This routine will obtain a list of addresses for a person id
 * @param db contains the Database object
 * @param personId   contains the personID
 * //from  ww  w. ja  v  a  2  s  . c  om
 * @return list of addresses
 */
public AddressReturn[] getAddress(final Database db, final long personId) {

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

    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);

    sb.append(
            "SELECT addresses.address_key, addresses.data_type_key, addresses.document_type_key, addresses.group_id,");
    sb.append("addresses.primary_flag,addresses.address1, addresses.address2, addresses.address3, ");
    sb.append("addresses.city, addresses.state, addresses.postal_code, addresses.province, verified_flag, ");
    sb.append("addresses.start_date, ");
    sb.append("addresses.end_date, ");
    sb.append("addresses.last_update_by, ");
    sb.append("addresses.last_update_on, ");
    sb.append("addresses.created_by, ");
    sb.append("addresses.created_on, ");
    sb.append("campus_cs.campus_code, campus_cs.campus, ");
    sb.append("country.country_code_three, country.country ");
    sb.append("FROM {h-schema}addresses ");
    sb.append("LEFT JOIN {h-schema}campus_cs ON addresses.campus_code_key = campus_cs.campus_code_key ");
    sb.append("LEFT JOIN {h-schema}country ON addresses.country_key = country.country_key ");
    sb.append("WHERE addresses.person_id = :person_id_in ");

    if (getAddressType() != null) {
        sb.append("AND addresses.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 addresses.end_date IS NULL ");
    }

    if (getAddressKey() > 0L) {
        sb.append("AND addresses.address_key = :address_key_in ");
    }

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

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

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

    if (getAddressKey() > 0L) {
        query.setParameter("address_key_in", getAddressKey());
    }

    query.addScalar(ADDRESS_KEY_STRING, StandardBasicTypes.LONG);
    query.addScalar(DATA_TYPE_KEY_STRING, StandardBasicTypes.LONG);
    query.addScalar(DOCUMENT_TYPE_KEY_STRING, StandardBasicTypes.LONG);
    query.addScalar(GROUP_ID_STRING, StandardBasicTypes.LONG);
    query.addScalar("primary_flag", StandardBasicTypes.STRING);
    query.addScalar("address1", StandardBasicTypes.STRING);
    query.addScalar("address2", StandardBasicTypes.STRING);
    query.addScalar("address3", StandardBasicTypes.STRING);
    query.addScalar("city", StandardBasicTypes.STRING);
    query.addScalar("state", StandardBasicTypes.STRING);
    query.addScalar("postal_code", StandardBasicTypes.STRING);
    query.addScalar("province", StandardBasicTypes.STRING);
    query.addScalar("verified_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("campus_code", StandardBasicTypes.STRING);
    query.addScalar("campus", StandardBasicTypes.STRING);
    query.addScalar("country_code_three", StandardBasicTypes.STRING);
    query.addScalar("country", StandardBasicTypes.STRING);

    for (final Iterator<?> it = query.list().iterator(); it.hasNext();) {
        Object[] res = (Object[]) it.next();

        AddressReturn anAddress = new AddressReturn();
        anAddress.setAddressKey(((Long) res[ADDRESS_KEY]).toString());
        anAddress.setAddressType(AddressType.get((Long) res[ADDRESS_TYPE]).toString());
        if (res[DOCUMENT_TYPE] != null) {
            anAddress.setDocumentType(DocumentType.get((Long) res[DOCUMENT_TYPE]).toString());
        } else {
            anAddress.setDocumentType(null);
        }
        anAddress.setGroupId((Long) res[GROUP_ID]);
        anAddress.setPrimaryFlag((String) res[PRIMARY_FLAG]);
        anAddress.setAddress1((String) res[ADDRESS1]);
        anAddress.setAddress2((String) res[ADDRESS2]);
        anAddress.setAddress3((String) res[ADDRESS3]);
        anAddress.setCity((String) res[CITY]);
        String tempState = (String) res[STATE];
        anAddress.setPostalCode((String) res[POSTAL_CODE]);
        String tempProvince = (String) res[PROVINCE];
        anAddress.setVerifiedFlag((String) res[VERIFIED_FLAG]);
        anAddress.setStartDate(Utility.formatDateToISO8601((Date) res[START_DATE]));
        anAddress.setEndDate(Utility.formatDateToISO8601((Date) res[END_DATE]));
        anAddress.setLastUpdateBy((String) res[LAST_UPDATE_BY]);
        anAddress.setLastUpdateOn(Utility.formatDateToISO8601((Date) res[LAST_UPDATE_ON]));
        anAddress.setCreatedBy((String) res[CREATED_BY]);
        anAddress.setCreatedOn(Utility.formatDateToISO8601((Date) res[CREATED_ON]));
        anAddress.setCampusCode((String) res[CAMPUS_CODE]);
        anAddress.setCampusName((String) res[CAMPUS_NAME]);
        anAddress.setCountryCode((String) res[COUNTRY_CODE]);
        anAddress.setCountryName((String) res[COUNTRY_NAME]);

        if (tempState != null) {
            anAddress.setStateOrProvince(tempState);
        } else if (tempProvince != null) {
            anAddress.setStateOrProvince(tempProvince);
        } else {
            anAddress.setStateOrProvince(null);
        }

        results.add(anAddress);
    }

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

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

License:Apache License

/**
 * This routine will set a primary address within a address type for a person id
 * @param db contains the Database object
 *
 * /*from  w w  w.j av a  2  s.c om*/
 * @throws CprException 
 * 
 */
public void setPrimaryByType(final Database db) throws CprException {

    boolean notFound = false;
    boolean alreadyPrimary = false;

    final Session session = db.getSession();
    final Addresses bean = getAddressesBean();
    SQLQuery query = null;
    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
    if (bean.getDocumentTypeKey() == null) {
        sb.append("SELECT  primary_flag ");
        sb.append("FROM {h-schema}addresses ");
        sb.append("WHERE person_id = :person_id_in ");
        sb.append("AND data_type_key = :data_type_key ");
        sb.append("AND group_id = :group_id ");
        sb.append("AND end_date IS NULL ");
        query = session.createSQLQuery(sb.toString());
        query.setParameter("person_id_in", bean.getPersonId());
        query.setParameter(DATA_TYPE_KEY_STRING, bean.getDataTypeKey());
        query.setParameter(GROUP_ID_STRING, bean.getGroupId());
        query.addScalar("primary_flag", StandardBasicTypes.STRING);
    } else {

        sb.append("SELECT  primary_flag ");
        sb.append("FROM {h-schema}addresses ");
        sb.append("WHERE person_id = :person_id_in ");
        sb.append("AND data_type_key = :data_type_key ");
        sb.append("AND document_type_key = :document_type_key ");
        sb.append("AND group_id = :group_id ");
        sb.append("AND end_date IS NULL ");
        query = session.createSQLQuery(sb.toString());
        query.setParameter("person_id_in", bean.getPersonId());
        query.setParameter(DATA_TYPE_KEY_STRING, bean.getDataTypeKey());
        query.setParameter(DOCUMENT_TYPE_KEY_STRING, bean.getDocumentTypeKey());
        query.setParameter(GROUP_ID_STRING, bean.getGroupId());
        query.addScalar("primary_flag", StandardBasicTypes.STRING);
    }
    Iterator<?> it = query.list().iterator();
    if (!it.hasNext()) {
        notFound = true;
    } else {

        final String primaryFlag = (String) it.next();
        if (Utility.isOptionYes(primaryFlag)) {
            alreadyPrimary = true;
        } else {
            String sqlQuery = null;
            Query query1 = null;
            if (bean.getDocumentTypeKey() == null) {
                sqlQuery = "from Addresses where personId = :person_id and dataTypeKey = :data_type_key and primaryFlag = 'Y' and endDate is null";
                query1 = session.createQuery(sqlQuery);
                query1.setParameter(PERSON_ID_STRING, bean.getPersonId());
                query1.setParameter(DATA_TYPE_KEY_STRING, bean.getDataTypeKey());
            } else {
                sqlQuery = "from Addresses where personId = :person_id and dataTypeKey = :data_type_key and documentTypeKey = :document_type_key and primaryFlag = 'Y' and endDate is null";
                query1 = session.createQuery(sqlQuery);
                query1.setParameter(PERSON_ID_STRING, bean.getPersonId());
                query1.setParameter(DATA_TYPE_KEY_STRING, bean.getDataTypeKey());
                query1.setParameter(DOCUMENT_TYPE_KEY_STRING, bean.getDocumentTypeKey());
            }
            it = query1.list().iterator();
            while (it.hasNext()) {
                Addresses dbBean = (Addresses) it.next();
                dbBean.setPrimaryFlag("N");
                dbBean.setLastUpdateBy(bean.getLastUpdateBy());
                dbBean.setLastUpdateOn(bean.getLastUpdateOn());
                session.update(dbBean);
                session.flush();
            }
            if (bean.getDocumentTypeKey() == null) {
                sqlQuery = "from Addresses where personId = :person_id and dataTypeKey = :data_type_key and groupId = :group_id and endDate IS NULL";
                query1 = session.createQuery(sqlQuery);
                query1.setParameter(PERSON_ID_STRING, bean.getPersonId());
                query1.setParameter(DATA_TYPE_KEY_STRING, bean.getDataTypeKey());
                query1.setParameter(GROUP_ID_STRING, bean.getGroupId());
            } else {

                sqlQuery = "from Addresses where personId = :person_id and dataTypeKey = :data_type_key and documentTypeKey = :document_type_key and groupId = :group_id and endDate IS NULL";
                query1 = session.createQuery(sqlQuery);
                query1.setParameter(PERSON_ID_STRING, bean.getPersonId());
                query1.setParameter(DATA_TYPE_KEY_STRING, bean.getDataTypeKey());
                query1.setParameter(DOCUMENT_TYPE_KEY_STRING, bean.getDocumentTypeKey());
                query1.setParameter(GROUP_ID_STRING, bean.getGroupId());
            }
            it = query1.list().iterator();
            if (it.hasNext()) {
                Addresses dbBean = (Addresses) it.next();
                dbBean.setPrimaryFlag("Y");
                dbBean.setLastUpdateBy(bean.getLastUpdateBy());
                dbBean.setLastUpdateOn(bean.getLastUpdateOn());
                session.update(dbBean);
                session.flush();
            }
        }
    }

    if (notFound) {
        LOG4J_LOGGER.info("AddressTable:Set primary not found");
        throw new CprException(ReturnType.RECORD_NOT_FOUND_EXCEPTION, "address");
    }

    if (alreadyPrimary) {
        LOG4J_LOGGER.info("AddressTable:Set primary already primary");
        throw new CprException(ReturnType.SET_PRIMARY_FAILED_EXCEPTION, "address");
    }
}

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

License:Apache License

/**
 * Obtain the campus information using a campus code.
 * @param db contains an open database connection.
 * @param campusCode contains the campus code to be used for the search.
 * @param  retrievedBy contains the userid requesting the compus code
 * @throws CprException /*from w ww . j a  v a 2  s .c om*/
 */
public void getCampusInfo(final Database db, final String campusCode, final String retrievedBy)
        throws CprException {

    boolean found = false;
    final Session session = db.getSession();

    final CampusCs bean = new CampusCs();
    bean.setCampusCode(campusCode);
    bean.setCampusCodeKey(null);
    setCampusCsBean(bean);

    if (bean.getCampusCode() == null || bean.getCampusCode().length() == 0) {
        bean.setCampusCodeKey(null);
        return;
    }

    bean.setCampusCode(bean.getCampusCode().toUpperCase().trim());

    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
    sb.append("SELECT campus_code_key, campus ");
    sb.append("FROM {h-schema}campus_cs ");
    sb.append("WHERE campus_code = :campus_code_in ");
    sb.append("AND active_flag = 'Y' ");

    final SQLQuery query = session.createSQLQuery(sb.toString());
    query.setParameter("campus_code_in", bean.getCampusCode());
    query.addScalar("campus_code_key", StandardBasicTypes.LONG);
    query.addScalar("campus", StandardBasicTypes.STRING);
    final Iterator<?> it = query.list().iterator();

    if (it.hasNext()) {
        Object[] res = (Object[]) it.next();
        bean.setCampusCodeKey((Long) res[0]);
        bean.setCampus((String) res[1]);
        found = true;
    }

    if (!found) {
        throw new CprException(ReturnType.RECORD_NOT_FOUND_EXCEPTION, CAMPUS_CODE);
    }
}

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

License:Apache License

/**
 * This routine is used to obtain the confidentiality hold status for a user.
 * @param db contains a reference to a database class that contains a open database connection.
 * @param personId contains the person identifier whose confidentiality is to be obtained
 * @return will return an array of confidentiality hold statuses.
 *///  ww  w. j  av a 2  s .  c  o  m
public ConfidentialityReturn[] getConfidentiality(final Database db, final long personId) {

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

    // Build the select statement as a string.
    sb.append("SELECT ");
    sb.append("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}confidentiality ");
    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 ");

    // Create the hibernate select statement.
    final SQLQuery query = session.createSQLQuery(sb.toString());
    query.setParameter("person_id_in", personId);
    query.addScalar(DATA_TYPE_KEY_STRING, 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();) {

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

        ConfidentialityReturn c = new ConfidentialityReturn(
                ConfidentialityType.get((Long) res[CONFIDENTIALITY_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]));
        results.add(c);
    }

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

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

License:Apache License

/**
 * Obtain the Country information using a country code.
 * @param db contains an open database connection.
 * @param countryCode contains the country code to be retrieved from the database.
 * @param retrieveBy contains the userid of the person doing the retrieve.
 * @throws CprException //from  w  w w  .  ja  v a  2 s.  co  m
 */
public void getCountryInfo(final Database db, final String countryCode, final String retrieveBy)
        throws CprException {

    boolean found = false;
    final Session session = db.getSession();

    final Country bean = new Country();
    bean.setCountryKey(null);
    bean.setCountryCodeThree(countryCode);
    setCountryBean(bean);

    if (bean.getCountryCodeThree() == null || bean.getCountryCodeThree().length() == 0) {
        bean.setCountryKey(null);
        return;
    }

    bean.setCountryCodeThree(bean.getCountryCodeThree().toUpperCase().trim());

    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
    sb.append("SELECT country_key, country ");
    sb.append("FROM {h-schema}country ");
    sb.append("WHERE country_code_three = :country_code_in ");
    sb.append(" AND us_territory_flag='N' ");
    sb.append("AND end_date IS NULL ");

    final SQLQuery query = session.createSQLQuery(sb.toString());
    query.setParameter("country_code_in", bean.getCountryCodeThree());
    query.addScalar("country_key", StandardBasicTypes.LONG);
    query.addScalar("country", StandardBasicTypes.STRING);
    final Iterator<?> it = query.list().iterator();

    if (it.hasNext()) {
        Object[] res = (Object[]) it.next();
        bean.setCountryKey((Long) res[0]);
        bean.setCountry((String) res[1]);
        found = true;
    }

    if (!found) {
        throw new CprException(ReturnType.RECORD_NOT_FOUND_EXCEPTION, COUNTRY_CODE);
    }
}

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

License:Apache License

/**
 * This routine is used to obtain credential information from the database.
 * @param db contains the database connection.
 * @param personId contains the person identifier to retrieve information for.
 * @return will return a CredentialReturn array.
 *//*from   w ww  .  jav a 2  s .  co  m*/
public CredentialReturn[] getCredentialForPersonId(final Database db, final long personId) {
    final List<CredentialReturn> results = new ArrayList<CredentialReturn>();
    final Session session = db.getSession();

    // Build the query string.
    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
    sb.append("SELECT credential_key, data_type_key, credential_data, ");
    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}credential ");
    sb.append("WHERE person_id = :person_id_in ");

    // Check to see if we are doing a query for credential key.
    if (getCredentialKey() > 0L) {
        sb.append("AND credential_key = :credential_key_in ");
    }

    // If we are doing a query for a specific credential type, we need to specify this clause.
    if (getCredentialType() != 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 ");
    }

    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 (getCredentialType() != null) {
        query.setParameter("data_type_key_in", getCredentialType().index());
    }

    if (getCredentialKey() > 0L) {
        query.setParameter("credential_key_in", getCredentialKey());
    }

    query.addScalar("credential_key", StandardBasicTypes.LONG);
    query.addScalar(DATA_TYPE_KEY_STRING, StandardBasicTypes.LONG);
    query.addScalar("credential_data", 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();
        CredentialReturn credentialReturn = new CredentialReturn();

        credentialReturn.setCredentialKey(((Long) res[CREDENTIAL_KEY]).toString());
        credentialReturn.setCredentialType(CredentialType.get((Long) res[CREDENTIAL_TYPE]).toString());
        credentialReturn.setCredentialData((String) res[CREDENTIAL_DATA]);
        credentialReturn.setStartDate(Utility.formatDateToISO8601((Date) res[START_DATE]));
        credentialReturn.setEndDate(Utility.formatDateToISO8601((Date) res[END_DATE]));
        credentialReturn.setLastUpdateBy((String) res[LAST_UPDATE_BY]);
        credentialReturn.setLastUpdateOn(Utility.formatDateToISO8601((Date) res[LAST_UPDATE_ON]));
        credentialReturn.setCreatedBy((String) res[CREATED_BY]);
        credentialReturn.setCreatedOn(Utility.formatDateToISO8601((Date) res[CREATED_ON]));

        results.add(credentialReturn);

    }

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

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

License:Apache License

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

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

    sb.append("SELECT dob_char, ");
    sb.append("start_date, ");
    sb.append("end_date, ");
    sb.append("last_update_by, ");
    sb.append("last_update_on, ");
    sb.append("created_by, ");
    sb.append("created_on ");
    sb.append("FROM {h-schema}date_of_birth ");
    sb.append("WHERE person_id = :person_id_in ");

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

    final SQLQuery query = session.createSQLQuery(sb.toString());
    query.setParameter("person_id_in", personId);
    query.addScalar("dob_char", StandardBasicTypes.STRING);
    query.addScalar("start_date", StandardBasicTypes.TIMESTAMP);
    query.addScalar("end_date", StandardBasicTypes.TIMESTAMP);
    query.addScalar("last_update_by", StandardBasicTypes.STRING);
    query.addScalar("last_update_on", StandardBasicTypes.TIMESTAMP);
    query.addScalar("created_by", StandardBasicTypes.STRING);
    query.addScalar("created_on", StandardBasicTypes.TIMESTAMP);

    for (final Iterator<?> it = query.list().iterator(); it.hasNext();) {
        Object[] res = (Object[]) it.next();
        String dobChar = (String) res[DOB_CHAR];

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

            // Only month and day of DOB was specified.
            if (year.equals(EMPTY_YEAR_STRING)) {
                results.add(new DateOfBirthReturn(month + "/" + day,
                        Utility.formatDateToISO8601((Date) res[START_DATE]),
                        Utility.formatDateToISO8601((Date) res[END_DATE]), (String) res[LAST_UPDATE_BY],
                        Utility.formatDateToISO8601((Date) res[LAST_UPDATE_ON]), (String) res[CREATED_BY],
                        Utility.formatDateToISO8601((Date) res[CREATED_ON])));
            }

            // Full date was specified.
            else {
                results.add(new DateOfBirthReturn(month + "/" + day + "/" + year,
                        Utility.formatDateToISO8601((Date) res[START_DATE]),
                        Utility.formatDateToISO8601((Date) res[END_DATE]), (String) res[LAST_UPDATE_BY],
                        Utility.formatDateToISO8601((Date) res[LAST_UPDATE_ON]), (String) res[CREATED_BY],
                        Utility.formatDateToISO8601((Date) res[CREATED_ON])));

            }
        }
    }

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

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

License:Apache License

/**
 * This routine is used to retrieve the list of email addresses for a person.
 * @param db contains the database connection.
 * @param personId contains the person id.
 * @return will return a list of email addresses.
 * @throws CprException  //from  w w w .  ja v 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.FederationTable.java

License:Apache License

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

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

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

}