Example usage for org.hibernate SQLQuery setParameter

Introduction

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

Prototype

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

Source Link

Usage

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

License:Apache License

/**
 * Add an Id Card Print Log event//  w  w  w .ja v a2 s . co m
 * 
 * @param db
 * @throws CprException
 */
public void addIdCardPrintLog(final Database db) throws CprException {

    boolean noPersonIdCard = false;
    final Session session = db.getSession();
    final IdCardPrintLog bean = getIdCardPrintLogBean();
    final String sqlQuery = "SELECT person_id_card_key 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", eventIdCardNumber);
    query.addScalar("person_id_card_key", StandardBasicTypes.LONG);
    final Iterator<?> it = query.list().iterator();
    if (it.hasNext()) {
        bean.setPersonIdCardKey((Long) it.next());
        session.save(bean);
        session.flush();
    } else {
        noPersonIdCard = true;
    }
    if (noPersonIdCard) {
        throw new CprException(ReturnType.ADD_FAILED_EXCEPTION, "Id Card Print Log");
    }
}

---

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

License:Apache License

/**
 * Get an IdCard Print Log event//from  ww w.  j a  v a 2 s.co  m
 * @param db contains a database connection.
 * @return IdCardPrintLogReturn array of results.
 */
public IdCardPrintLogReturn[] getIdCardPrintLog(final Database db) {

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

    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
    sb.append("SELECT person_id, id_card_number, work_station_ip_address, ");
    sb.append("work_station_name, printed_by , printed_on ");
    sb.append("FROM {h-schema}v_person_id_card_print_log WHERE id_card_number = :id_card_number_in ");
    sb.append("order by printed_on ASC");

    final SQLQuery query = session.createSQLQuery(sb.toString());
    query.setParameter("id_card_number_in", getEventIdCardNumber());
    query.addScalar("person_id", StandardBasicTypes.LONG);
    query.addScalar("id_card_number", StandardBasicTypes.STRING);
    query.addScalar("work_station_ip_address", StandardBasicTypes.STRING);
    query.addScalar("work_station_name", StandardBasicTypes.STRING);
    query.addScalar("printed_by", StandardBasicTypes.STRING);
    query.addScalar("printed_on", StandardBasicTypes.TIMESTAMP);
    for (final Iterator<?> it = query.list().iterator(); it.hasNext();) {
        Object[] res = (Object[]) it.next();
        IdCardPrintLogReturn anIdLog = new IdCardPrintLogReturn();
        anIdLog.setPersonId((Long) res[PERSON_ID]);
        anIdLog.setIdCardNumber((String) res[ID_CARD_NUMBER]);
        anIdLog.setIpAddress((String) res[IP_ADDRESS]);
        anIdLog.setWorkStationName((String) res[WORKSTATION_NAME]);
        anIdLog.setPrintedBy((String) res[PRINTED_BY]);
        anIdLog.setPrintDate(Utility.convertTimestampToString((Date) res[PRINT_DATE]));
        results.add(anIdLog);
    }
    return results.toArray(new IdCardPrintLogReturn[results.size()]);

}

---

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

License:Apache License

/**
 * This routine is used to obtain id card information for a person.
 * @param db contains the database connection object.
 * @param personId contains the person identifier to do the query for.
 * @return PersonIdCardReturn array./*  w w w.ja va 2  s.  co m*/
 */
public PersonIdCardReturn[] getIdCardForPersonId(final Database db, final long personId) {

    final List<PersonIdCardReturn> results = new ArrayList<PersonIdCardReturn>();
    final Session session = db.getSession();
    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
    sb.append("SELECT data_type_key, id_card_number, id_serial_number, ");
    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_id_card ");
    sb.append("WHERE person_id=:person_id ");

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

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

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

    query.addScalar(DATA_TYPE_KEY_STRING, StandardBasicTypes.LONG);
    query.addScalar("id_card_number", StandardBasicTypes.STRING);
    query.addScalar("id_serial_number", 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();
        PersonIdCardReturn personIdCardReturn = new PersonIdCardReturn();
        personIdCardReturn.setIdCardType(IdCardType.get((Long) res[ID_CARD_TYPE]).toString());
        personIdCardReturn.setIdCardNumber((String) res[ID_CARD_NUMBER]);
        personIdCardReturn.setIdSerialNumber((String) res[ID_SERIAL_NUMBER]);
        personIdCardReturn.setStartDate(Utility.convertTimestampToString((Date) res[START_DATE]));
        personIdCardReturn.setEndDate(Utility.convertTimestampToString((Date) res[END_DATE]));
        personIdCardReturn.setLastUpdateBy((String) res[LAST_UPDATE_BY]);
        personIdCardReturn.setLastUpdateOn(Utility.convertTimestampToString((Date) res[LAST_UPDATE_ON]));
        personIdCardReturn.setCreatedBy((String) res[CREATED_BY]);
        personIdCardReturn.setCreatedOn(Utility.convertTimestampToString((Date) res[CREATED_ON]));
        results.add(personIdCardReturn);
    }
    return results.toArray(new PersonIdCardReturn[results.size()]);
}

---

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

License:Apache License

/**
 * This routine is used to obtain id card number for a person.
 * @param db contains the database connection object.
 * @param personId contains the person identifier to do the query for.
 * @return PersonIdCardNumberReturn array.
 *///from w  ww  .j a  v a2 s .c  o m
public PersonIdCardNumberReturn[] getIdCardNumberForPersonId(final Database db, final long personId) {

    final List<PersonIdCardNumberReturn> results = new ArrayList<PersonIdCardNumberReturn>();
    final Session session = db.getSession();
    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
    sb.append("SELECT data_type_key, id_card_number ");
    sb.append("from {h-schema}person_id_card ");
    sb.append("WHERE person_id=:person_id ");
    sb.append("AND end_date IS NULL ");

    sb.append("ORDER BY data_type_key ASC, start_date ASC ");
    final SQLQuery query = session.createSQLQuery(sb.toString());
    query.setParameter(PERSON_ID_STRING, personId);

    query.addScalar(DATA_TYPE_KEY_STRING, StandardBasicTypes.LONG);
    query.addScalar("id_card_number", StandardBasicTypes.STRING);

    for (final Iterator<?> it = query.list().iterator(); it.hasNext();) {
        Object[] res = (Object[]) it.next();
        PersonIdCardNumberReturn personIdCardNumberReturn = new PersonIdCardNumberReturn();
        personIdCardNumberReturn.setIdCardType(IdCardType.get((Long) res[0]).toString());
        personIdCardNumberReturn.setIdCardNumber((String) res[1]);

        results.add(personIdCardNumberReturn);
    }
    return results.toArray(new PersonIdCardNumberReturn[results.size()]);
}

---

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

License:Apache License

/**
 * This routine is used to remove a match set from the database.
 * @param db contains a database object which points to an open database connection.
 *//*from w w w  .  ja v  a2  s.c o  m*/
public void removeMatchSet(final Database db) {

    final Session session = db.getSession();
    final SQLQuery query = session.createSQLQuery(REMOVE_MATCH_SET_SQL);
    query.setParameter(MATCH_SET_KEY_STRING, getMatchSetKey());
    query.executeUpdate();
}

---

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

License:Apache License

/**
 * This routine is used to obtain a match set from the database.
 * @param db contains the database object which hold an open database connection.
 * @return will return a List of match results items.
 *//*  www.j a  v a 2s .  c o m*/
public List<MatchResultsTable> getMatchSet(final Database db) {

    final List<MatchResultsTable> matchResultsTable = new ArrayList<MatchResultsTable>();
    final Session session = db.getSession();
    final SQLQuery query = session.createSQLQuery(GET_MATCH_SET_SQL);
    query.setParameter(MATCH_SET_KEY_STRING, getMatchSetKey());
    query.addScalar("match_set_key", StandardBasicTypes.LONG);
    query.addScalar("person_id", StandardBasicTypes.LONG);
    query.addScalar("score", StandardBasicTypes.LONG);
    final Iterator<?> it = query.list().iterator();
    while (it.hasNext()) {
        Object[] res = (Object[]) it.next();
        matchResultsTable.add(new MatchResultsTable((Long) res[RES_MATCH_SET_KEY], (Long) res[RES_PERSON_ID],
                (Long) res[RES_SCORE]));
    }
    return matchResultsTable;
}

---

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

License:Apache License

/**
 * This routine is used to obtain a match set from the database.
 * This method only retrieved the first n items.
 * //from w ww. j  a  v  a 2 s  .  c o m
 * @param db contains the database object which hold an open database connection.
 * @param maxResults The maximum number of results to retrieve.
 * 
 * @return will return a List of match results items containing up to maxResults items.
 * 
 */
public List<MatchResultsTable> getMatchSetWithLimit(final Database db, int maxResults) {

    List<MatchResultsTable> matchResultsTable = new ArrayList<MatchResultsTable>(maxResults);
    final Session session = db.getSession();

    final SQLQuery query = session.createSQLQuery(GET_MATCH_SET_SQL_LIMIT);
    query.setParameter(MATCH_SET_KEY_STRING, getMatchSetKey());
    query.setParameter("rownum_in", maxResults);
    query.addScalar("match_set_key", StandardBasicTypes.LONG);
    query.addScalar("person_id", StandardBasicTypes.LONG);
    query.addScalar("score", StandardBasicTypes.LONG);
    final Iterator<?> it = query.list().iterator();

    while (it.hasNext()) {
        Object[] res = (Object[]) it.next();
        matchResultsTable.add(new MatchResultsTable((Long) res[RES_MATCH_SET_KEY], (Long) res[RES_PERSON_ID],
                (Long) res[RES_SCORE]));
    }
    // XXX: error checking on maxResults (>0)

    ((ArrayList<MatchResultsTable>) matchResultsTable).trimToSize();
    return matchResultsTable;
}

---

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

License:Apache License

/**
 * This routine is used to obtain a match set from the database.
 * This method only retrieved the first n items with a minimum match score.
 * /*from  w w w  .  jav  a2 s  . c  o  m*/
 * @param db contains the database object which hold an open database connection.
 * @param maxResults The maximum number of results to retrieve.
 * @param minMatchScore The minimum match score for all hits.
 * 
 * @return will return an ArrayList of match results items containing up to maxResults items.
 * 
 */
public List<MatchResultsTable> getMatchSetWithLimitAndCutoff(final Database db, int maxResults,
        Long minMatchScore) {

    // XXX: error checking on maxResults (non-negative)
    // XXX: error checking on minMatchScore (non-negative)

    final List<MatchResultsTable> matchResultsTable = new ArrayList<MatchResultsTable>(maxResults);

    final Session session = db.getSession();
    final SQLQuery query = session.createSQLQuery(GET_MATCH_SET_MIN_SCORE_SQL_LIMIT);
    query.setParameter(MATCH_SET_KEY_STRING, getMatchSetKey());
    query.setParameter("score_in", minMatchScore);
    query.setParameter("rownum_in", maxResults);
    query.addScalar("match_set_key", StandardBasicTypes.LONG);
    query.addScalar("person_id", StandardBasicTypes.LONG);
    query.addScalar("score", StandardBasicTypes.LONG);
    final Iterator<?> it = query.list().iterator();

    while (it.hasNext()) {
        Object[] res = (Object[]) it.next();
        matchResultsTable.add(new MatchResultsTable((Long) res[RES_MATCH_SET_KEY], (Long) res[RES_PERSON_ID],
                (Long) res[RES_SCORE]));
    }

    ((ArrayList<MatchResultsTable>) matchResultsTable).trimToSize();
    return matchResultsTable;
}

---

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

License:Apache License

/**
 * This routine is used to get a message history log.
 * @param db contains an open database connection.
 * @param messageId contains the message identifier.
 * @return will return an array of message log history information.
 *///from   w w w.  ja v a2  s  .  c  o m
public MessageLogHistoryReturn[] getMessageLogHistory(final Database db, final String messageId) {

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

    // Build the select statement as a string.
    sb.append("SELECT ");
    sb.append("message_log_key, ");
    sb.append("message_sent_timestamp, ");
    sb.append("message_received_timestamp, ");
    sb.append("message_received, ");
    sb.append("error_code, ");
    sb.append("error_message, ");
    sb.append("try_number ");
    sb.append("FROM {h-schema}message_log_history ");
    sb.append("WHERE message_id = :message_id_in ");

    // Create the hibernate select statement.
    final SQLQuery query = session.createSQLQuery(sb.toString());
    query.setParameter("message_id_in", messageId);
    query.addScalar("message_log_key", StandardBasicTypes.LONG);
    query.addScalar("message_sent_timestamp", StandardBasicTypes.TIMESTAMP);
    query.addScalar("message_received_timestamp", StandardBasicTypes.TIMESTAMP);
    query.addScalar("message_received", StandardBasicTypes.STRING);
    query.addScalar("error_code", StandardBasicTypes.STRING);
    query.addScalar("error_message", StandardBasicTypes.STRING);
    query.addScalar("try_number", StandardBasicTypes.LONG);

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

    // Loop for the results.
    while (it.hasNext()) {

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

        MessageLogHistoryReturn msgLogHistory = new MessageLogHistoryReturn();
        msgLogHistory.setMessageId(messageId);
        msgLogHistory.setMessageLogKey((Long) res[MESSAGE_LOG_KEY]);
        msgLogHistory.setMessageSentTimestamp((Timestamp) res[MESSAGE_SENT_TIMESTAMP]);
        msgLogHistory.setMessageReceivedTimestamp((Timestamp) res[MESSAGE_RECEIVED_TIMESTAMP]);
        msgLogHistory.setMessageReceived((String) res[MESSAGE_RECEIVED]);
        msgLogHistory.setErrorCode((String) res[ERROR_CODE]);
        msgLogHistory.setErrorMessage((String) res[ERROR_MESSAGE]);
        msgLogHistory.setTryNumber((Long) res[TRY_NUMBER]);
        results.add(msgLogHistory);
    }

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

---

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

License:Apache License

/**
 * This routine will obtain a list of names for a person id.
 * @param db contains the database connection.
 * @param personId contains the person id.
 * @return list of names./*from  w  w  w. j  av  a2 s  .  c  om*/
 */
public NameReturn[] getNamesForPersonId(final Database db, final long personId) {

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

    // Build the query string.
    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
    sb.append(
            "SELECT name_key, data_type_key, document_type_key, first_name, middle_names, last_name, suffix, nickname, ");
    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}names ");
    sb.append("WHERE person_id = :person_id_in ");

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

    // Check to see if we are returning a specific name record 
    if (getNameKey() > 0L) {
        sb.append("AND name_key = :name_key_in ");
    }

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

    if (getNameKey() > 0L) {
        query.setParameter("name_key_in", getNameKey());
    }

    query.addScalar(NAME_KEY_STRING, StandardBasicTypes.LONG);
    query.addScalar(DATA_TYPE_KEY_STRING, StandardBasicTypes.LONG);
    query.addScalar(DOCUMENT_TYPE_KEY_STRING, StandardBasicTypes.LONG);
    query.addScalar("first_name", StandardBasicTypes.STRING);
    query.addScalar("middle_names", StandardBasicTypes.STRING);
    query.addScalar("last_name", StandardBasicTypes.STRING);
    query.addScalar("suffix", StandardBasicTypes.STRING);
    query.addScalar("nickname", 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();
        NameReturn nameReturn = new NameReturn();

        nameReturn.setNameKey(((Long) res[NAME_KEY]).toString());
        nameReturn.setNameType(NameType.get((Long) res[NAME_TYPE]).toString());

        if ((Long) res[DOCUMENT_TYPE] != null) {
            nameReturn.setDocumentType(DocumentType.get((Long) res[DOCUMENT_TYPE]).toString());
        } else {
            nameReturn.setDocumentType(null);
        }

        nameReturn.setFirstName((String) res[FIRST_NAME]);
        nameReturn.setMiddleNames((String) res[MIDDLE_NAMES]);
        nameReturn.setLastName((String) res[LAST_NAME]);
        nameReturn.setSuffix((String) res[SUFFIX]);
        nameReturn.setNickname((String) res[NICKNAME]);
        nameReturn.setStartDate(Utility.formatDateToISO8601((Date) res[START_DATE]));
        nameReturn.setEndDate(Utility.formatDateToISO8601((Date) res[END_DATE]));
        nameReturn.setLastUpdateBy((String) res[LAST_UPDATE_BY]);
        nameReturn.setLastUpdateOn(Utility.formatDateToISO8601((Date) res[LAST_UPDATE_ON]));
        nameReturn.setCreatedBy((String) res[CREATED_BY]);
        nameReturn.setCreatedOn(Utility.formatDateToISO8601((Date) res[CREATED_ON]));

        results.add(nameReturn);

    }

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

---

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