Example usage for org.hibernate SQLQuery setParameter

List of usage examples for org.hibernate SQLQuery setParameter

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

Introduction

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

Prototype

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

Source Link

Usage

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

License:Apache License

/**
 * This routine is used to archive a userid.  It is called by the ArchiveUserid service.
 * @param db contains a reference to an open database connection.
 * @throws CprException will be thrown for any CPR specific problems.
 *///from   w  w w . j  ava  2s .  c om
public void archiveUserid(final Database db) throws CprException {

    boolean noneActive = false;
    boolean notFound = false;
    boolean alreadyArchived = false;
    boolean cannotArchive = false;

    final Session session = db.getSession();
    final Userid bean = getUseridBean();

    // Determine how many userids are active for the current user.
    String sqlQuery = "SELECT person_id FROM {h-schema}userid WHERE person_id = :person_id_in AND end_date IS NULL";
    SQLQuery query = session.createSQLQuery(sqlQuery);
    query.setParameter("person_id_in", bean.getPersonId());
    query.addScalar("person_id", StandardBasicTypes.LONG);
    final int activeCount = query.list().size();
    if (activeCount == 0) {
        noneActive = true;
    } else {

        // For the selected userid, obtain the end date and their primary flag.
        final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
        sb.append("SELECT end_date, primary_flag ");
        sb.append("FROM {h-schema}userid ");
        sb.append("WHERE person_id = :person_id_in ");
        sb.append("AND userid = :userid_in ");
        query = session.createSQLQuery(sb.toString());
        query.setParameter("person_id_in", bean.getPersonId());
        query.setParameter("userid_in", bean.getUserid());
        query.addScalar("end_date", StandardBasicTypes.DATE);
        query.addScalar("primary_flag", StandardBasicTypes.STRING);
        Iterator<?> it = query.list().iterator();

        if (it.hasNext()) {
            Object[] res = (Object[]) it.next();
            bean.setEndDate((Date) res[0]);
            bean.setPrimaryFlag((String) res[1]);

            // Error if the record already has an end date.
            if (bean.getEndDate() != null) {
                alreadyArchived = true;
            }

            // If there are more than one record and this one is primary, do not all the archival.
            else if (activeCount > 1 && Utility.isOptionYes(bean.getPrimaryFlag())) {
                cannotArchive = true;
            }

            // Otherwise we can do the archive.
            else {
                sqlQuery = "from Userid where personId = :person_id_in AND userid = :userid_in AND endDate IS NULL";
                final Query query1 = session.createQuery(sqlQuery);
                query1.setParameter("person_id_in", bean.getPersonId());
                query1.setParameter("userid_in", bean.getUserid());
                for (it = query1.list().iterator(); it.hasNext();) {
                    Userid dbBean = (Userid) it.next();
                    dbBean.setPrimaryFlag("N");
                    dbBean.setEndDate(bean.getLastUpdateOn());
                    dbBean.setLastUpdateBy(bean.getLastUpdateBy());
                    dbBean.setLastUpdateOn(bean.getLastUpdateOn());
                    session.update(dbBean);
                    session.flush();
                }
            }
        } else {
            notFound = true;
        }

    }

    if (notFound) {
        throw new CprException(ReturnType.RECORD_NOT_FOUND_EXCEPTION, TABLE_NAME);
    }
    if (noneActive) {
        throw new CprException(ReturnType.GENERAL_EXCEPTION,
                "Cannot archive userid, because there are no active userids.");
    }
    if (alreadyArchived) {
        throw new CprException(ReturnType.ALREADY_DELETED_EXCEPTION, TABLE_NAME);
    }
    if (cannotArchive) {
        throw new CprException(ReturnType.GENERAL_EXCEPTION,
                "Cannot archive userid, because its the primary userid.");
    }

}

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

License:Apache License

/**
 * This routine is used to unarchive a userid.  It is called by the UnarchiveUserid service.
 * @param db contains a reference to an open database connection.
 * @throws CprException will be thrown for any CPR specific problems.
 *//*w  w  w .  j  ava2  s .c  o m*/
public void unarchiveUserid(final Database db) throws CprException {

    boolean alreadyUnarchived = false;
    boolean noArchivedRecords = false;
    boolean recordNotFound = false;

    final Session session = db.getSession();
    final Userid bean = getUseridBean();

    // See how any userids are archived for the user, if there are none that are archived, we have an error.
    String sqlQuery = "SELECT person_id FROM {h-schema}userid WHERE person_id = :person_id_in AND end_date IS NOT NULL";
    SQLQuery query = session.createSQLQuery(sqlQuery);
    query.setParameter("person_id_in", bean.getPersonId());
    query.addScalar("person_id", StandardBasicTypes.LONG);
    final int archivedCount = query.list().size();

    if (archivedCount == 0) {
        noArchivedRecords = true;
    } else {

        // For the selected userid, obtain the end date and their primary flag.
        final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
        sb.append("SELECT end_date, primary_flag ");
        sb.append("FROM {h-schema}userid ");
        sb.append("WHERE person_id = :person_id_in ");
        sb.append("AND userid = :userid_in ");
        query = session.createSQLQuery(sb.toString());
        query.setParameter("person_id_in", bean.getPersonId());
        query.setParameter("userid_in", bean.getUserid());
        query.addScalar("end_date", StandardBasicTypes.DATE);
        query.addScalar("primary_flag", StandardBasicTypes.STRING);
        Iterator<?> it = query.list().iterator();

        if (it.hasNext()) {
            Object[] res = (Object[]) it.next();
            bean.setEndDate((Date) res[0]);
            bean.setPrimaryFlag((String) res[1]);

            if (bean.getEndDate() == null) {
                alreadyUnarchived = true;
            } else {
                // Determine how many userids are active for the current user.
                sqlQuery = "SELECT person_id FROM {h-schema}userid WHERE person_id = :person_id_in AND end_date IS NULL";
                query = session.createSQLQuery(sqlQuery);
                query.setParameter("person_id_in", bean.getPersonId());
                query.addScalar("person_id", StandardBasicTypes.LONG);
                final int activeCount = query.list().size();

                if (activeCount == 0) {
                    bean.setPrimaryFlag("Y");
                } else {
                    bean.setPrimaryFlag("N");
                }

                // Do the unarchive.
                sqlQuery = "from Userid where personId = :person_id AND userid = :userid_in AND endDate IS NOT NULL";
                final Query query1 = session.createQuery(sqlQuery);
                query1.setParameter("person_id", bean.getPersonId());
                query1.setParameter("userid_in", bean.getUserid());
                for (it = query1.list().iterator(); it.hasNext();) {
                    Userid dbBean = (Userid) it.next();
                    dbBean.setPrimaryFlag(bean.getPrimaryFlag());
                    dbBean.setEndDate(null);
                    dbBean.setLastUpdateBy(bean.getLastUpdateBy());
                    dbBean.setLastUpdateOn(bean.getLastUpdateOn());
                    session.update(dbBean);
                    session.flush();
                }
            }

        } else {
            recordNotFound = true;
        }
    }

    if (alreadyUnarchived) {
        throw new CprException(ReturnType.UNARCHIVE_FAILED_EXCEPTION, "userid");
    }

    if (noArchivedRecords) {
        throw new CprException(ReturnType.GENERAL_EXCEPTION, "There are no records that can be unarchived.");
    }

    if (recordNotFound) {
        throw new CprException(ReturnType.RECORD_NOT_FOUND_EXCEPTION, "userid");
    }
}

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

License:Apache License

/**
 * This routine is used to add a special userid.  It is called by the AddSpecialUserid service.
 * @param db contains a reference to an open database connection.
 * @throws CprException will be thrown for any CPR specific problems.
 *//*w  w  w  .j  a  v  a  2 s  .c  o  m*/
public void addSpecialUserid(final Database db) throws CprException {

    // Verify that the new userid contains valid characters.
    if (!isUseridValid(db, getUseridBean().getUserid())) {
        throw new CprException(ReturnType.INVALID_PARAMETERS_EXCEPTION, TABLE_NAME);
    }

    final Session session = db.getSession();
    final Userid bean = getUseridBean();

    // Fill in the char and number parts of the userid.
    final String charPart = getCharacterPart(bean.getUserid());
    bean.setCharPart(charPart);
    bean.setNumPart(getNumberPart(bean.getUserid(), charPart));

    // 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);
}

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

License:Apache License

/**
 * This routine will obtain a list of userids for a person id.
 * @param db contains an open database connection.
 * @param personId contains the person id.
 * @return an array of userids./*from w w w .  ja v a2  s.c o m*/
 */
public UseridReturn[] getUseridsForPersonId(final Database db, final long personId) {

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

    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
    sb.append("SELECT userid, primary_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}userid ");
    sb.append("WHERE person_id = :person_id_in ");
    if (!isReturnHistoryFlag()) {
        sb.append("AND end_date IS NULL ");
    }
    if (getUserid() != null) {
        sb.append("AND userid = :userid ");
    }
    sb.append("ORDER BY start_date");

    final SQLQuery query = session.createSQLQuery(sb.toString());

    query.setParameter("person_id_in", personId);

    if (getUserid() != null) {
        query.setParameter("userid", getUserid());
    }

    query.addScalar("userid", StandardBasicTypes.STRING);
    query.addScalar("primary_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();
        results.add(new UseridReturn((String) res[USERID], (String) res[PRIMARY_FLAG],
                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 UseridReturn[results.size()]);
}

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

License:Apache License

/**
 * This routine is used to determine if the passed in userid is valid.
 * @param db contains a reference to the database handle.
 * @param userid contains the userid to valid.
 * @return will return true if the userid is valid, otherwise it will return false.
 *///  w  w  w  .  j a  v  a 2  s .  co  m
public boolean isUseridValid(final Database db, final String userid) {

    final Session session = db.getSession();
    // Verify that the userid does not contain spaces.
    if (userid.contains(" ")) {
        return false;
    }
    // Verify that the userid only contains letters, numbers, $ and underscore.
    if (!userid.matches("^[a-zA-Z0-9$_]+$")) {
        return false;
    }

    // Obtain the character portion of the userid.
    final String charPart = getCharacterPart(userid);

    // Verify that the userid does not exist in the bad prefixes table.
    String sqlQuery = "SELECT char_part FROM {h-schema}bad_prefixes WHERE char_part = :char_part_in";
    SQLQuery query = session.createSQLQuery(sqlQuery);
    query.setParameter("char_part_in", charPart);
    query.addScalar("char_part", StandardBasicTypes.STRING);
    if (query.list().size() > 0) {
        return false;
    }

    // Verify that the userid does not already exist.
    sqlQuery = "SELECT person_id FROM {h-schema}userid WHERE userid = :userid_in";
    query = session.createSQLQuery(sqlQuery);
    query.setParameter("userid_in", userid);
    query.addScalar("person_id", StandardBasicTypes.LONG);
    if (query.list().size() > 0) {
        return false;
    }

    return true;
}

From source file:edu.psu.iam.cpr.core.messaging.ServiceProvisionerQueue.java

License:Apache License

/**
 * This routine is used to obtain a list of service providers and their respective queues for a particular web service.
 * @param db contains a database connection.
 * @param webService contains the web server to do the query for.
 * @return will return an ArrayList of service provider information.
 */// www.  j  ava 2s. c om
public static ArrayList<ServiceProvisionerQueue> getServiceProvisionerQueues(Database db, String webService) {

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

    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
    sb.append("SELECT service_provisioner_key, service_provisioner, web_service_key, web_service, ");
    sb.append("service_provisioner_queue FROM v_sp_notification WHERE web_service=:web_service ");

    final SQLQuery query = session.createSQLQuery(sb.toString());
    query.setParameter("web_service", webService);
    query.addScalar("service_provisioner_key", StandardBasicTypes.LONG);
    query.addScalar("service_provisioner", StandardBasicTypes.STRING);
    query.addScalar("web_service_key", StandardBasicTypes.LONG);
    query.addScalar("web_service", StandardBasicTypes.STRING);
    query.addScalar("service_provisioner_queue", StandardBasicTypes.STRING);
    final Iterator<?> it = query.list().iterator();

    while (it.hasNext()) {
        Object res[] = (Object[]) it.next();
        results.add(new ServiceProvisionerQueue((Long) res[SP_KEY], (String) res[SP_NAME],
                (Long) res[WEB_SERVICE_KEY], (String) res[WEB_SERVICE], (String) res[SP_QUEUE]));
    }

    return results;
}

From source file:edu.uoc.pelp.model.dao.DeliverDAO.java

License:Open Source License

@Override
public List<Deliver> findLastClassroom(IClassroomID classroom, ActivityID activity) {
    // Get the activity ID and classroom ID
    ClassroomPK classPK = ObjectFactory.getClassroomPK(classroom);
    if (classPK == null) {
        return null;
    }//from w w w. j av  a 2s .  c o m
    ActivityPK activityPK = ObjectFactory.getActivityPK(activity);
    if (activityPK == null) {
        return null;
    }

    // Get the activity register
    /*Query query=getSession().getNamedQuery("Deliver.findAtivityLastDeliverByClass");
    query.setParameter("semester", activityPK.getSemester());
    query.setParameter("subject", activityPK.getSubject());
    query.setParameter("activityIndex", activityPK.getActivityIndex());
    query.setParameter("classroom", classPK.toString());
    List<edu.uoc.pelp.model.vo.Deliver> list=query.list();*/
    getSession().beginTransaction();
    String SQL = "SELECT d1.* " + "FROM pelp.deliver d1 "
            + "LEFT JOIN pelp.deliver d2 ON d1.semester=d2.semester and d1.subject=d2.subject and d1.activityIndex=d2.activityIndex and d1.userID=d2.userID and d1.deliverIndex<d2.deliverIndex "
            + "WHERE d2.deliverIndex is null " + "and d1.semester=:semester " + "and d1.subject=:subject "
            + "and d1.activityIndex=:activityIndex "
            + "and (d1.classroom=:classroom or d1.laboratory=:classroom) "
            + "ORDER BY d1.semester,d1.subject,d1.activityIndex,d1.userID;";
    SQLQuery query = getSession().createSQLQuery(SQL);
    query.addEntity(edu.uoc.pelp.model.vo.Deliver.class);
    //query.setEntity("d1", edu.uoc.pelp.model.vo.Deliver.class);
    query.setParameter("semester", activityPK.getSemester());
    query.setParameter("subject", activityPK.getSubject());
    query.setParameter("activityIndex", activityPK.getActivityIndex());
    query.setParameter("classroom", classPK.toString());
    List<edu.uoc.pelp.model.vo.Deliver> list = query.list();

    List<Deliver> listDelivers = getDeliverList(list);

    getSession().close();
    // Return the results
    return listDelivers;
}

From source file:es.logongas.ix3.dao.impl.NativeDAOImplHibernate.java

License:Apache License

@Override
public List<Object> createNativeQuery(DataSession dataSession, String query, List<Object> params) {
    Session session = (Session) dataSession.getDataBaseSessionImpl();

    SQLQuery sqlQuery = session.createSQLQuery(query);

    if (params != null) {
        for (int i = 0; i < params.size(); i++) {
            sqlQuery.setParameter(i, params.get(i));
        }/*from w  w w .j  ava2 s.  c o m*/
    }

    return sqlQuery.list();
}

From source file:es.logongas.ix3.dao.impl.NativeDAOImplHibernate.java

License:Apache License

@Override
public List<Object> createNativeQuery(DataSession dataSession, String query, Map<String, Object> params) {
    Session session = (Session) dataSession.getDataBaseSessionImpl();

    SQLQuery sqlQuery = session.createSQLQuery(query);

    if (params != null) {
        for (String paramName : params.keySet()) {
            sqlQuery.setParameter(paramName, params.get(paramName));
        }/*from   ww w .j a  va 2  s.  co m*/
    }
    return sqlQuery.list();
}

From source file:financeiro.dao.hibernate.LancamentoDAOHibernate.java

License:Creative Commons License

public float saldo(Conta conta, Date data) {

    if (data == null) {
        throw new IllegalArgumentException("[Financeiro] data cannot be null");
    }/* ww w.j  a va  2  s  .co  m*/

    StringBuffer sql = new StringBuffer();
    sql.append("select sum(l.valor * c.fator)");
    sql.append("  from LANCAMENTO l,");
    sql.append("        CATEGORIA c");
    sql.append(" where l.categoria = c.codigo");
    sql.append("   and l.conta = :conta");
    sql.append("   and l.data <= :data");

    SQLQuery query = this.session.createSQLQuery(sql.toString());

    query.setParameter("conta", conta.getConta());
    query.setParameter("data", data);

    BigDecimal saldo = (BigDecimal) query.uniqueResult();

    if (saldo != null) {
        return saldo.floatValue();
    }
    return 0f;
}