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.Database.java

License:Apache License

/**
 * This routine will verify whether a userid exists in the CPR or not.
 * @param userid input userid to be checked for existence.
 * @throws CprException exception indicates a cpr specific java exception.
 *//*from   w w  w. j a va  2 s . c  o  m*/
public void doesUseridExist(final String userid) throws CprException {

    Long personId = NOT_FOUND_VALUE;

    final String sqlQuery = "SELECT person_id FROM {h-schema}userid WHERE userid = :userid";
    final SQLQuery query = session.createSQLQuery(sqlQuery);
    query.setParameter("userid", userid);
    query.addScalar("person_id", StandardBasicTypes.LONG);
    final Iterator<?> it = query.list().iterator();
    if (it.hasNext()) {
        personId = (Long) it.next();
    }

    if (personId.equals(NOT_FOUND_VALUE)) {
        throw new CprException(ReturnType.RECORD_NOT_FOUND_EXCEPTION, "userid");
    }
}

From source file:edu.psu.iam.cpr.core.database.helpers.PsuIdHelper.java

License:Apache License

/**
 * This routine will generate a new PSU ID for a person and assign it.
 * @param session contains a database session.
 * @param bean contains the PSU ID database bean where the resultant PSU ID will be placed.
 *///w w w  . j a v  a2  s.c  om
public void generatePSUIdNumber(final Session session, final PsuId bean) {

    boolean done = false;

    while (!done) {

        // Obtain a new PSU ID.
        String psuId = Integer.toString(getRandomPSUIdNumber());

        // Determine if the new PSU ID is not already used, in the exception list or tagged for assignment. 
        StringBuilder sb = new StringBuilder(BUFFER_SIZE);
        sb.append("SELECT psu_id FROM {h-schema}psu_id WHERE psu_id = :psu_id_1");
        sb.append(" UNION ");
        sb.append("SELECT psu_id FROM {h-schema}psu_id_exceptions WHERE psu_id = :psu_id_2");
        sb.append(" UNION ");
        sb.append(
                "SELECT generated_identity AS psu_id from {h-schema}generated_identity WHERE generated_identity = :psu_id_3");
        final SQLQuery query = session.createSQLQuery(sb.toString());
        query.addScalar("psu_id", StandardBasicTypes.STRING);
        query.setParameter("psu_id_1", psuId);
        query.setParameter("psu_id_2", psuId);
        query.setParameter("psu_id_3", psuId);

        // Not found, so we can assign it to the user.
        if (query.list().size() == 0) {

            bean.setPsuId(psuId);

            done = true;
        }
    }
}

From source file:edu.psu.iam.cpr.core.database.helpers.UseridHelper.java

License:Apache License

/**
 * This routine is used to generate a userid an assign it.
 * @param session contains a database session.
 * @param bean contains a userid database bean that will contain the assigned userid.
 *///from   w  w w.  j a  va  2s . co m
public void generateUserid(final Session session, final Userid bean) {

    // Obtain the list of active names for the person.
    String sqlQuery = "from Names where personId = :person_id AND endDate IS NULL";
    Query query = session.createQuery(sqlQuery);
    query.setParameter("person_id", bean.getPersonId());
    Names names = null;
    for (Iterator<?> it = query.list().iterator(); it.hasNext();) {
        names = (Names) it.next();

        // If we find a legal name, let's use it.
        if (names.getDataTypeKey() == NameType.LEGAL_NAME.index()) {
            break;
        }
    }

    // Extract the first letter from the selected name.
    String firstNameLetter = null;
    String middleNamesLetter = null;
    String lastNameLetter = null;
    if (names != null) {
        firstNameLetter = getFirstLetter(names.getFirstName());
        middleNamesLetter = getFirstLetter(names.getMiddleNames());
        lastNameLetter = getFirstLetter(names.getLastName());
    }

    boolean done = false;
    int missCount = 0;
    final Random random = new Random(new Date().getTime());

    while (!done) {
        double randomValue = random.nextDouble();

        // first name was not specified.
        if (firstNameLetter == null) {
            if (randomValue < FIRST_LETTER_RANGE1) {
                firstNameLetter = FIRST_LETTERS[INDEX_0];
            } else if (randomValue < FIRST_LETTER_RANGE2) {
                firstNameLetter = FIRST_LETTERS[INDEX_1];
            } else if (randomValue < FIRST_LETTER_RANGE3) {
                firstNameLetter = FIRST_LETTERS[INDEX_2];
            } else if (randomValue < FIRST_LETTER_RANGE4) {
                firstNameLetter = FIRST_LETTERS[INDEX_3];
            } else if (randomValue < FIRST_LETTER_RANGE5) {
                firstNameLetter = FIRST_LETTERS[INDEX_4];
            } else if (randomValue < FIRST_LETTER_RANGE6) {
                firstNameLetter = FIRST_LETTERS[INDEX_5];
            } else {
                firstNameLetter = FIRST_LETTERS[INDEX_6];
            }
        }

        // Middle names was not specified.
        if (middleNamesLetter == null) {
            if (randomValue < MIDDLE_LETTER_RANGE1) {
                middleNamesLetter = MIDDLE_LETTERS[INDEX_0];
            } else if (randomValue < MIDDLE_LETTER_RANGE2) {
                middleNamesLetter = MIDDLE_LETTERS[INDEX_1];
            } else if (randomValue < MIDDLE_LETTER_RANGE3) {
                middleNamesLetter = MIDDLE_LETTERS[INDEX_2];
            } else if (randomValue < MIDDLE_LETTER_RANGE4) {
                middleNamesLetter = MIDDLE_LETTERS[INDEX_3];
            } else if (randomValue < MIDDLE_LETTER_RANGE5) {
                middleNamesLetter = MIDDLE_LETTERS[INDEX_4];
            } else if (randomValue < MIDDLE_LETTER_RANGE6) {
                middleNamesLetter = MIDDLE_LETTERS[INDEX_5];
            } else {
                middleNamesLetter = MIDDLE_LETTERS[INDEX_6];
            }
        }

        // Last name was not specified.
        if (lastNameLetter == null) {
            if (randomValue < LAST_LETTER_RANGE1) {
                lastNameLetter = LAST_LETTERS[INDEX_0];
            } else if (randomValue < LAST_LETTER_RANGE2) {
                lastNameLetter = LAST_LETTERS[INDEX_1];
            } else {
                lastNameLetter = LAST_LETTERS[INDEX_2];
            }
        }

        // Construct the character portion of the userid.
        StringBuilder sb = new StringBuilder(BUFFER_SIZE);
        sb.append(firstNameLetter);
        sb.append(middleNamesLetter);
        sb.append(lastNameLetter);

        String charPart = sb.toString();

        // Verify that the character porition of the userid does not exist in the bad prefixes database table.
        sqlQuery = "from BadPrefixes where charPart = :char_part";
        query = session.createQuery(sqlQuery);
        query.setParameter("char_part", charPart);

        if (query.list().size() == 0) {

            // Find a userid in the pool to be used.
            sqlQuery = "SELECT MIN(num_part) AS min_num_part FROM {h-schema}userid_pool WHERE char_part = :char_part";
            SQLQuery query1 = session.createSQLQuery(sqlQuery);
            query1.setParameter("char_part", charPart);
            query1.addScalar("min_num_part", StandardBasicTypes.LONG);
            Long numPart = null;
            for (Iterator<?> it = query1.list().iterator(); it.hasNext();) {
                numPart = (Long) it.next();
            }

            String userid = charPart + numPart.toString();

            // Delete the userid from the pool.
            query = session
                    .createQuery("delete UseridPool where charPart = :char_part AND numPart = :num_part");
            query.setParameter("char_part", charPart);
            query.setParameter("num_part", numPart);
            query.executeUpdate();
            session.flush();

            bean.setUserid(userid);
            done = true;
        } else {

            // OK, let's pick a different letter.
            missCount = (missCount + 1) % TOTAL_LETTERS;
            switch (missCount) {
            case FIRST_LETTER:
                firstNameLetter = null;
                break;
            case MIDDLE_LETTER:
                middleNamesLetter = null;
                break;
            case LAST_LETTER:
                lastNameLetter = null;
                break;
            }
        }
    }
}

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
 * // www .ja  va  2  s .c o 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   w  w w  .  ja va 2s . 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 ww  w.ja v a  2s  .c o  m
 * @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 /* w w w.j  av  a 2 s .c o m*/
 */
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.
 *///from  w w  w.  j av a2s  .co  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 /* w w w .  j  av  a 2  s. c om*/
 */
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 ww  w  . j  a v a  2s . 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()]);
}