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

License:Apache License

/**
 * This routine is used to obtain a person identifier using a userid.
 * @param userid contains the userid to be used in the search.
 * @return person id if the userid can be found, otherwise it will return a -1 to indicate an error.
 * @throws CprException exception indicates a cpr specific java exception.
 *//*from  w w w  .  j a  v a2 s .  co  m*/
public long getPersonIdUsingUserid(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.PERSON_NOT_FOUND_EXCEPTION);
    }

    return personId;
}

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

License:Apache License

/**
 * This routine is used to find a person using their id card number.
 * @param idCard contains the id card that is used to be search for.
 * @return will return the person identifier if a user was found with the correct id.
 * @throws CprException will be thrown if there are any CPR specific problems.
 *//*www.j av  a  2 s  .com*/
public long getPersonIdUsingIdCard(final String idCard) throws CprException {

    Long personId = NOT_FOUND_VALUE;

    final String sqlQuery = "SELECT person_id FROM {h-schema}person_id_card WHERE id_card_number = :idcard AND end_date IS NULL";
    final SQLQuery query = session.createSQLQuery(sqlQuery);
    query.setParameter("idcard", idCard);
    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.PERSON_NOT_FOUND_EXCEPTION);
    }

    return personId;
}

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

License:Apache License

/**
 * This routine is used to determine whether a userid/person id is valid and whether the userid is still active.
 * @param personId contains the person identifier to do the query for.
 * @param userid contains the userid to do the query for.
 * @return will return true if valid, otherwise it will return false.
 *///from  w  w  w .java 2  s.  c  om
public boolean isValidUserid(final Long personId, final String userid) {

    boolean found = false;

    final String sqlQuery = "SELECT person_id FROM {h-schema}userid WHERE userid = :userid AND person_id = :person_id AND end_date IS NULL";
    final SQLQuery query = session.createSQLQuery(sqlQuery);
    query.setParameter("person_id", personId);
    query.setParameter("userid", userid);
    query.addScalar("person_id", StandardBasicTypes.LONG);
    found = (query.list().size() == 0) ? false : true;

    return found;
}

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

License:Apache License

/**
 * This routine is used to determine if a person with a person identifier exists in the CPR or not.  
 * @param personId contains the person identifier to do a search for.
 * @return returns the person identifier found.
 * @throws CprException exception indicates a cpr specific java exception.
 *///from  w  w  w.j a va  2s  . co m
public long getPersonIdUsingPersonId(final long personId) throws CprException {

    Long personIdOut = NOT_FOUND_VALUE;

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

    if (personIdOut.equals(NOT_FOUND_VALUE)) {
        throw new CprException(ReturnType.PERSON_NOT_FOUND_EXCEPTION);
    }

    return personId;
}

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

License:Apache License

/**
 * This routine is used to obtain a person identifier using a SOR identifier.
 * @param identifierType contains the identifier type.
 * @param identifier contains the identifier value.
 * @return will return the person identifier or throw an exception.
 * @throws CprException will be thrown if there is a CPR specific problem.
 *//* w ww.j  ava  2s  . c o m*/
public long getPersonIdUsingPersonIdentifier(final IdentifierType identifierType, final String identifier)
        throws CprException {

    Long personId = NOT_FOUND_VALUE;

    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
    sb.append("SELECT person_id FROM {h-schema}person_identifier WHERE type_key = :type_key ");
    sb.append("AND identifier_value = :identifier_value ");
    sb.append("AND end_date IS NULL");
    final SQLQuery query = session.createSQLQuery(sb.toString());
    query.setParameter("type_key", identifierType.getTypeKey());
    query.setParameter("identifier_value", identifier);
    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.PERSON_NOT_FOUND_EXCEPTION);
    }

    return personId;
}

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

License:Apache License

/**
 * This routine will called a stored function to determine if a person is active in the CPR or not.
 * @param personId person identifier from the Central Person Registry.
 * @return true if the person is active.
 * @throws CprException exception indicates a cpr specific java exception.
 *//*from  w  w  w.j  a v a  2  s  . c  o  m*/
public boolean isPersonActive(final long personId) throws CprException {

    long personIdOut = NOT_FOUND_VALUE;

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

    if (personId != personIdOut) {
        throw new CprException(ReturnType.PERSON_NOT_ACTIVE_EXCEPTION);
    }

    return true;
}

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

License:Apache License

/**
 * doesPsuIdExist accepts a single parameter the psuId and checks to see if it exists in the CPR.
 * @param psuId contains the psu id to check for existence.
 * @throws CprException exception indicates a cpr specific java exception.
 */// w ww. j  av a  2s.  co  m
public void doesPsuIdExist(final String psuId) throws CprException {

    Long personId = NOT_FOUND_VALUE;

    final String sqlQuery = "SELECT person_id FROM {h-schema}psu_id WHERE psu_id = :psuid AND end_date IS NULL";
    final SQLQuery query = session.createSQLQuery(sqlQuery);
    query.setParameter("psuid", psuId);
    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.PSUID_NOT_FOUND_EXCEPTION);
    }
}

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.
 *///w  ww  .  j a v a  2s  .  c om
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.
 *///from w  ww .  j a v  a  2  s.c  o m
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 ww.  j  a v  a2s. c om*/
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;
            }
        }
    }
}