Example usage for org.hibernate SQLQuery addScalar

List of usage examples for org.hibernate SQLQuery addScalar

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.*
  5. SQLQuery
  6. addScalar

Introduction

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

Prototype

SQLQuery<T> addScalar(String columnAlias, Type type);

Source Link

Document

Declare a scalar query result.

Usage

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

License:Apache License

/**
 * This routine is use to obtain person linkage information for a person in the registry.
 * @param db contains an open database connection.
 * @param personId contains the person identifier to perform the query for.
 * @return will return a PersonLinkageReturn array if successful, otherwise it will return null.
 *//*from w w  w  .  ja v  a  2s . c om*/
public PersonLinkageReturn[] getPersonLinkage(final Database db, final long personId) {

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

    // Build the query string.
    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
    sb.append("SELECT data_type_key, person_id, linked_person_id, ");
    sb.append("start_date, ");
    sb.append("end_date,  ");
    sb.append("last_update_by, ");
    sb.append("last_update_on, ");
    sb.append("created_by, ");
    sb.append("created_on ");
    sb.append("FROM {h-schema}person_linkage ");
    sb.append("WHERE person_id = :person_id_in ");

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

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

    // Set up hibernate for the query, bind parameters and determine return types.
    final SQLQuery query = session.createSQLQuery(sb.toString());
    query.setParameter("person_id_in", personId);
    query.addScalar(DATA_TYPE_KEY_STRING, StandardBasicTypes.LONG);
    query.addScalar(PERSON_ID_STRING, StandardBasicTypes.LONG);
    query.addScalar("linked_person_id", StandardBasicTypes.LONG);
    query.addScalar("start_date", StandardBasicTypes.TIMESTAMP);
    query.addScalar("end_date", StandardBasicTypes.TIMESTAMP);
    query.addScalar("last_update_by", StandardBasicTypes.STRING);
    query.addScalar("last_update_on", StandardBasicTypes.TIMESTAMP);
    query.addScalar("created_by", StandardBasicTypes.STRING);
    query.addScalar("created_on", StandardBasicTypes.TIMESTAMP);

    // Perform the query.
    for (final Iterator<?> it = query.list().iterator(); it.hasNext();) {
        Object[] res = (Object[]) it.next();
        PersonLinkageReturn personLinkageReturn = new PersonLinkageReturn(
                LinkageType.get((Long) res[LINKAGE_TYPE]).toString(), (Long) res[PERSON_ID],
                (Long) res[LINKED_PERSON_ID], Utility.formatDateToISO8601((Date) res[START_DATE]),
                Utility.formatDateToISO8601((Date) res[END_DATE]), (String) res[LAST_UPDATE_BY],
                Utility.formatDateToISO8601((Date) res[LAST_UPDATE_ON]), (String) res[CREATED_BY],
                Utility.formatDateToISO8601((Date) res[CREATED_ON]));
        results.add(personLinkageReturn);

    }

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

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 An active database handle/*from  www.j  a  v  a  2s. c  om*/
 * @param personId contains the person_id
 * @param userid The userid to query
 * 
 * @return A list of IAPs
 * @throws CprException 
 */
public IAPReturn[] getPSUIAP(final Database db, final long personId, final String userid) 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 List<IAPReturn> results = new ArrayList<IAPReturn>();
    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
    sb.append("SELECT iap.iap, ");
    sb.append("person_userid_iap.start_date, ");
    sb.append("person_userid_iap.end_date, ");
    sb.append("person_userid_iap.last_update_by, ");
    sb.append("person_userid_iap.last_update_on, ");
    sb.append("person_userid_iap.created_by, ");
    sb.append("person_userid_iap.created_on ");
    sb.append("FROM {h-schema}iap ");
    sb.append("LEFT JOIN {h-schema}person_userid_iap ON iap.iap_key = person_userid_iap.iap_key ");
    sb.append("WHERE person_userid_iap.person_id = :person_id_in ");
    sb.append("AND person_userid_iap.userid=:userid_in ");

    // If we are not returning all records, we need to just return the active ones.
    if (!isReturnHistoryFlag()) {
        sb.append("AND person_userid_iap.end_date IS NULL ");
    }
    sb.append("ORDER BY iap.iap_key ASC, person_userid_iap.start_date ASC ");
    final SQLQuery query = session.createSQLQuery(sb.toString());
    query.setParameter("person_id_in", personId);

    query.setParameter("userid_in", userid);
    query.addScalar("iap", 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();
        IAPReturn anIAP = new IAPReturn();
        anIAP.setIap((String) res[IAP]);
        anIAP.setStartDate(Utility.formatDateToISO8601((Date) res[START_DATE]));
        anIAP.setEndDate(Utility.formatDateToISO8601((Date) res[END_DATE]));
        anIAP.setLastUpdateBy((String) res[LAST_UPDATE_BY]);
        anIAP.setLastUpdateOn(Utility.formatDateToISO8601((Date) res[LAST_UPDATE_ON]));
        anIAP.setCreatedBy((String) res[CREATED_BY]);
        anIAP.setCreatedOn(Utility.formatDateToISO8601((Date) res[CREATED_ON]));
        results.add(anIAP);
    }

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

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

License:Apache License

/**
 *  The purpose of this routine is to interface with the database JDBC calls to
 *  call a store function to add a phone of the specified type to user's record.  
 *  The information necessary to add the phone is passed in the PhonesTable class.
 *  @param db//from   w w w. j  ava2 s  .com
 * @throws CprException 
 */
public void addPhone(final Database db) throws CprException {

    boolean matchFound = false;
    final Session session = db.getSession();
    final Phones bean = getPhonesBean();
    Long maxGroupId = null;
    // verify that this is not a duplicate record within type
    String sqlQuery = "from Phones where personId = :person_id AND dataTypeKey = :data_type_key AND endDate IS NULL";
    final Query query = session.createQuery(sqlQuery);
    query.setParameter(PERSON_ID_STRING, bean.getPersonId());
    query.setParameter(DATA_TYPE_KEY_STRING, bean.getDataTypeKey());
    for (final Iterator<?> it = query.list().iterator(); it.hasNext() && (!matchFound);) {
        Phones dbBean = (Phones) it.next();
        if (Utility.areStringFieldsEqual(bean.getPhoneNumber(), dbBean.getPhoneNumber())
                && Utility.areStringFieldsEqual(bean.getExtension(), dbBean.getExtension())
                && Utility.areStringFieldsEqual(bean.getInternationalNumberFlag(),
                        dbBean.getInternationalNumberFlag())) {
            matchFound = true;
        }
    }

    if (!matchFound) {
        // Find the maximum group id for the person and their phone type combination.
        sqlQuery = "SELECT MAX(group_id) as max_group_id FROM {h-schema}phones 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();
        if (it.hasNext()) {
            maxGroupId = (Long) it.next();
            maxGroupId = (maxGroupId == null) ? 1L : maxGroupId + 1L;

        } else {
            maxGroupId = 1L;
        }

        // Save off the new record.
        bean.setGroupId(maxGroupId);
        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.PhonesTable.java

License:Apache License

/**
 * This routine will obtain a list of phone numbers for a person id
 * @param db //from   w  w w.j  a v a2  s.c o  m
 * @param personId contains the personID
 * @return list of phone numbers
 */
public PhoneReturn[] getPhones(final Database db, final long personId) {

    final List<PhoneReturn> results = new ArrayList<PhoneReturn>();

    final Session session = db.getSession();
    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
    sb.append(
            "SELECT phone_key, data_type_key, group_id, primary_flag, phone_number, extension, international_number_flag,  ");
    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}phones ");
    sb.append("WHERE person_id = :person_id_in ");

    if (getPhoneType() != null) {
        sb.append("AND data_type_key = :data_type_key_in ");
    }

    if (!isReturnHistoryFlag()) {
        sb.append("AND end_date IS NULL");
    }

    if (getPhoneKey() > 0L) {
        sb.append("AND phone_key = :phone_key ");
    }

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

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

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

    if (getPhoneKey() > 0L) {
        query.setParameter(PHONE_KEY_STRING, getPhoneKey());
    }

    query.addScalar(PHONE_KEY_STRING, StandardBasicTypes.LONG);
    query.addScalar(DATA_TYPE_KEY_STRING, StandardBasicTypes.LONG);
    query.addScalar(GROUP_ID_STRING, StandardBasicTypes.LONG);
    query.addScalar("primary_flag", StandardBasicTypes.STRING);
    query.addScalar("phone_number", StandardBasicTypes.STRING);
    query.addScalar("extension", StandardBasicTypes.STRING);
    query.addScalar("international_number_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);

    for (final Iterator<?> it = query.list().iterator(); it.hasNext();) {
        Object[] res = (Object[]) it.next();
        PhoneReturn aPhone = new PhoneReturn();
        aPhone.setPhoneKey(((Long) res[PHONE_KEY]).toString());
        aPhone.setPhoneType(PhoneType.get((Long) res[PHONE_TYPE]).toString());
        aPhone.setGroupId((Long) res[GROUP_ID]);
        aPhone.setPrimaryFlag((String) res[PRIMARY_FLAG]);
        aPhone.setPhoneNumber((String) res[PHONE_NUMBER]);
        aPhone.setExtension((String) res[EXTENSION]);
        aPhone.setInternationalNumber((String) res[INTERNATIONAL_NUMBER]);
        aPhone.setStartDate(Utility.formatDateToISO8601((Date) res[START_DATE]));
        aPhone.setEndDate(Utility.formatDateToISO8601((Date) res[END_DATE]));
        aPhone.setLastUpdateBy((String) res[LAST_UPDATE_BY]);
        aPhone.setLastUpdateOn(Utility.formatDateToISO8601((Date) res[LAST_UPDATE_ON]));
        aPhone.setCreatedBy((String) res[CREATED_BY]);
        aPhone.setCreatedOn(Utility.formatDateToISO8601((Date) res[CREATED_ON]));
        results.add(aPhone);
    }

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

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

License:Apache License

/**
 * This routine will set a primary phone number within a phonetype for a person id
 * @param db /*from www  .j  a v  a2  s  . 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 Phones bean = getPhonesBean();

    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
    sb.append("SELECT  primary_flag ");
    sb.append("FROM {h-schema}phones ");
    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 ");
    final SQLQuery 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);
    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 = "from Phones where personId = :person_id and dataTypeKey = :data_type_key and primaryFlag = 'Y' and endDate is null";
            Query query1 = session.createQuery(sqlQuery);
            query1.setParameter(PERSON_ID_STRING, bean.getPersonId());
            query1.setParameter(DATA_TYPE_KEY_STRING, bean.getDataTypeKey());
            for (it = query1.list().iterator(); it.hasNext();) {
                Phones dbBean = (Phones) it.next();
                dbBean.setPrimaryFlag("N");
                dbBean.setLastUpdateBy(bean.getLastUpdateBy());
                dbBean.setLastUpdateOn(bean.getLastUpdateOn());
                session.update(dbBean);
                session.flush();
            }

            sqlQuery = "from Phones 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());
            it = query1.list().iterator();
            if (it.hasNext()) {
                Phones dbBean = (Phones) 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.PsuDirectoryTable.java

License:Apache License

/**
 * This routine is used to populate the directory table class with information from the PSU_DIRECTORY table for a specific person identifier.
 * @param db contains the database connection
 * @param personId contains the person identifier.
 * @throws CprException /*w w w .j a va 2 s  .  c  o m*/
 */
public void getPsuDirectoryTable(final Database db, final Long personId) throws CprException {

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

    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
    sb.append("SELECT userid, psu_directory_key ");
    sb.append("FROM {h-schema}psu_directory ");
    sb.append("WHERE person_id = :person_id_in ");
    sb.append("AND end_date IS NULL ");

    final SQLQuery query = session.createSQLQuery(sb.toString());
    query.setParameter("person_id_in", personId);
    query.addScalar("userid", StandardBasicTypes.STRING);
    query.addScalar("psu_directory_key", StandardBasicTypes.LONG);

    final Iterator<?> it = query.list().iterator();

    if (it.hasNext()) {
        Object[] res = (Object[]) it.next();

        final PsuDirectory bean = new PsuDirectory();
        bean.setPersonId(personId);
        bean.setUserid((String) res[USERID]);
        bean.setPsuDirectoryKey((Long) res[PSU_DIRECTORY_KEY]);
        setPsuDirectoryBean(bean);
        found = true;
    }

    if (!found) {
        throw new CprException(ReturnType.PERSON_NOT_FOUND_EXCEPTION);
    }
}

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

License:Apache License

/**
 * This routine is used to obtain the PSU ID for a specific person.
 * @param db contains the open database connection.
 * @param personId contains the person identifier used to retrieve the PSU ID for.
 * @return will return a PsuIdReturn object if success.
 *//*from  w  w  w.  j  a  va  2 s. com*/
public PsuIdReturn[] getPsuIdForPersonId(final Database db, final long personId) {

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

    sb.append("SELECT psu_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}psu_id 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 ");
    }
    sb.append("ORDER BY start_date ASC ");

    final SQLQuery query = session.createSQLQuery(sb.toString());
    query.setParameter("person_id", personId);
    query.addScalar("psu_id", 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();
        results.add(new PsuIdReturn((String) res[PSU_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])));
    }
    return results.toArray(new PsuIdReturn[results.size()]);
}

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

License:Apache License

/**
 * @param db a database object that contains an open database connection.
 * @param serviceName the service name to obtain a web service identifier for.
 * @return will contain the web service key
 * @throws CprException //from  ww w.j av  a 2  s .  c om
 */
public Long getWebServiceKey(Database db, String serviceName) throws CprException {

    Long webServiceKey = NOT_FOUND_VALUE;
    final Session session = db.getSession();
    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
    sb.append("SELECT web_service_key FROM web_service WHERE web_service = :web_service_in");

    final SQLQuery query = session.createSQLQuery(sb.toString());
    query.setParameter("web_service_in", serviceName);
    query.addScalar("web_service_key", StandardBasicTypes.LONG);
    final Iterator<?> it = query.list().iterator();
    if (it.hasNext()) {
        webServiceKey = (Long) it.next();
    }

    if (webServiceKey == NOT_FOUND_VALUE) {
        throw new CprException(ReturnType.WEB_SERVICE_NOT_FOUND_EXCEPTION, serviceName);
    }

    return webServiceKey;
}

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

License:Apache License

/**
 * Obtain user comments./*  w  w  w .  ja  v  a2 s.  c  o m*/
 * @param db
 * @param userId contains the user id of person.
 * @return an array of user comments.
 */
public UserCommentReturn[] getUserComments(final Database db, final String userId) {

    final List<UserCommentReturn> results = new ArrayList<UserCommentReturn>();

    final Session session = db.getSession();

    // Build the query string.
    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
    sb.append("SELECT user_comment_key, data_type_key, comments, ");
    sb.append("start_date, ");
    sb.append("end_date, ");
    sb.append("last_update_by, ");
    sb.append("last_update_on, ");
    sb.append("created_by ");
    sb.append("FROM {h-schema}user_comments ");
    sb.append("WHERE userid = :userid_in ");

    if (getUserCommentType() != 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 ");
    }

    if (getCommentKey() > 0L) {
        sb.append("AND user_comment_key = :user_comment_key ");
    }

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

    // Init the hibernate query.
    final SQLQuery query = session.createSQLQuery(sb.toString());
    query.setParameter("userid_in", userId);

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

    if (getCommentKey() > 0L) {
        query.setParameter("user_comment_key", getCommentKey());
    }

    query.addScalar("user_comment_key", StandardBasicTypes.LONG);
    query.addScalar("data_type_key", StandardBasicTypes.LONG);
    query.addScalar("comments", 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);

    // Perform the query.
    final Iterator<?> it = query.list().iterator();

    // Process the results.
    while (it.hasNext()) {
        Object[] res = (Object[]) it.next();
        UserCommentReturn ucr = new UserCommentReturn();
        ucr.setCommentKey(((Long) res[COMMENT_KEY]).toString());
        ucr.setUserCommentType(UserCommentType.get((Long) res[USER_COMMENT_TYPE]).toString());
        ucr.setComment((String) res[COMMENT]);
        ucr.setCommentDateString(Utility.formatDateToISO8601((Date) res[COMMENT_DATE_STRING]));
        ucr.setEndDate(Utility.formatDateToISO8601((Date) res[END_DATE]));
        ucr.setLastUpdatedBy((String) res[LAST_UPDATE_BY]);
        ucr.setLastUpdateOn(Utility.formatDateToISO8601((Date) res[LAST_UPDATE_ON]));
        ucr.setCommenter((String) res[COMMENTER]);
        results.add(ucr);
    }

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

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

License:Apache License

/**
 * This routine is to call a stored procedure to add a new userid for a user.
 * @param db contains a database connection.
 * @throws CprException /* ww w  .  j  a  v a 2s .  c om*/
 * 
 */
public void addUserid(final Database db) throws CprException {

    final Session session = db.getSession();
    GeneratedIdentityTable generatedIdentityTable = null;
    try {
        final Userid bean = getUseridBean();

        // Obtain a userid from the pool, check to see if there was a failure.
        getUseridHelper().generateUserid(session, bean);

        // Obtain the character part and number part.
        final String charPart = getCharacterPart(bean.getUserid());
        bean.setCharPart(charPart);
        bean.setNumPart(getNumberPart(bean.getUserid(), charPart));

        generatedIdentityTable = new GeneratedIdentityTable(bean.getPersonId(), bean.getUserid(),
                bean.getCharPart(), bean.getNumPart(), bean.getLastUpdateBy());
        generatedIdentityTable.addGeneratedIdentity(session);

        // Do a select to determine what primary needs to be set to.
        final String sqlQuery = "SELECT person_id FROM {h-schema}userid WHERE person_id = :person_id_in AND end_date IS NULL";
        final SQLQuery query = session.createSQLQuery(sqlQuery);
        query.setParameter("person_id_in", bean.getPersonId());
        query.addScalar("person_id", StandardBasicTypes.LONG);
        if (query.list().size() == 0) {
            bean.setPrimaryFlag("Y");
        } else {
            bean.setDisplayNameFlag("N");
            bean.setPrimaryFlag("N");
        }

        // Save off the new userid record.
        session.save(bean);
        session.flush();

        // Add a record to the psu directory table.
        final PsuDirectoryTable psuDirectoryTable = new PsuDirectoryTable(bean.getPersonId(), bean.getUserid(),
                bean.getLastUpdateBy());
        psuDirectoryTable.addDirectoryTable(db);

    } finally {
        try {
            generatedIdentityTable.removeGeneratedIdentity(session);
        } catch (Exception e) {
        }
    }
}