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:de.codesourcery.eve.skills.db.dao.InventoryTypeDAO.java

License:Apache License

@Override
public List<InventoryType> getInventoryTypes(final MarketGroup group) {
    if (group == null) {
        throw new IllegalArgumentException("group must not be NULL");
    }//  ww w  .ja  v a2  s. com

    final List<InventoryType> cached = typeByMarketGroupID.get(group.getId());
    if (cached != null) {
        return new ArrayList<>(cached);
    }
    return execute(new HibernateCallback<List<InventoryType>>() {

        @Override
        public List<InventoryType> doInSession(Session session) {
            final SQLQuery query = session
                    .createSQLQuery("SELECT typeID FROM invTypes WHERE marketGroupID = :marketGroupId");
            query.setParameter("marketGroupId", group.getId());
            query.addScalar("typeID", new LongType());

            final List<InventoryType> result = getInventoryTypesByIDs(query.list());
            typeByMarketGroupID.putIfAbsent(group.getId(), new ArrayList<>(result));
            return result;
        }
    });
}

---

From source file:de.codesourcery.eve.skills.db.dao.InventoryTypeDAO.java

License:Apache License

@Override
public List<InventoryType> getInventoryTypesWithBlueprints(final MarketGroup group) {
    return execute(new HibernateCallback<List<InventoryType>>() {

        @Override//from  w  w  w.  j  a  v  a  2  s  .co m
        public List<InventoryType> doInSession(Session session) {
            final SQLQuery query = session
                    .createSQLQuery("SELECT i.typeID FROM invTypes i , invBlueprintTypes bp WHERE "
                            + "i.marketGroupID = :marketGroupId AND bp.productTypeID = i.typeID");
            query.setParameter("marketGroupId", group.getId());
            query.addScalar("typeID", new LongType());

            return getInventoryTypesByIDs(query.list());
        }
    });
}

---

From source file:domain.friends.java

/**
 * Processes requests for both HTTP <code>GET</code> and <code>POST</code>
 * methods.// ww  w.j a  va  2  s .  c o  m
 *
 * @param request servlet request
 * @param response servlet response
 * @throws ServletException if a servlet-specific error occurs
 * @throws IOException if an I/O error occurs
 */
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    response.setContentType("text/html;charset=UTF-8");

    Session hbm = HibernateUtil.getSessionFactory().openSession();
    int id = request.getParameter("id") != null ? Integer.parseInt(request.getParameter("id")) : 0;

    User mainUser = (User) hbm.get(User.class, id);
    request.setAttribute("mainUser", mainUser);

    SQLQuery query = hbm.createSQLQuery("select user2 from friends where user1 = :id");
    query.setParameter("id", id);
    List<Integer> idovi = query.list();
    List<User> friends = new ArrayList<User>();
    for (int user_id : idovi) {
        friends.add((User) hbm.get(User.class, user_id));
    }

    request.setAttribute("friends", friends);

    request.getRequestDispatcher("friends.jsp").forward(request, response);
}

---

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

License:Apache License

/**
 * This method is used to find a registration authority based on a server principal.
 * @param principalId contains the ra server principal.
 * @param serviceName contains the name of the calling service.
 * @return will return a list of longs contains the registration authority key and the ra server principal key.
 * @throws CprException will be thrown if there are any CPR Related problems.
 *//*from w ww.ja va2s .  com*/
private List<Long> findRegistrationAuthority(final String principalId, final String serviceName)
        throws CprException {

    Long localRegistrationAuthoritykey = NOT_FOUND_VALUE;
    Long raServerPrincipalKey = NOT_FOUND_VALUE;

    String suspendFlag = "Y";

    // Build the query.
    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
    sb.append("SELECT ra.registration_authority_key, ra.suspend_flag, rasrvrprinc.ra_server_principal_key ");
    sb.append("FROM {h-schema}registration_authority ra JOIN {h-schema}ra_server_principals rasrvrprinc ");
    sb.append("ON ra.registration_authority_key = rasrvrprinc.registration_authority_key ");
    sb.append("WHERE rasrvrprinc.ra_server_principal = :ra_server_principal_in ");
    sb.append("AND ra.end_date IS NULL ");
    sb.append("AND rasrvrprinc.end_date IS NULL");

    // Create the query, bind the input parameters and determine the output parameters.
    SQLQuery query = session.createSQLQuery(sb.toString());
    query.setParameter("ra_server_principal_in", principalId);
    query.addScalar("registration_authority_key", StandardBasicTypes.LONG);
    query.addScalar("suspend_flag", StandardBasicTypes.STRING);
    query.addScalar("ra_server_principal_key", StandardBasicTypes.LONG);

    // See if a record is found, if so get its data.
    Iterator<?> it = query.list().iterator();
    if (it.hasNext()) {
        Object[] res = (Object[]) it.next();
        localRegistrationAuthoritykey = (Long) res[RA_KEY_INDEX];
        suspendFlag = (String) res[RA_SUSPEND_FLAG];
        raServerPrincipalKey = (Long) res[RA_SERVER_PRINCIPAL_KEY_INDEX];
    }

    // Is the RA suspended?
    if (localRegistrationAuthoritykey.equals(NOT_FOUND_VALUE) || raServerPrincipalKey.equals(NOT_FOUND_VALUE)
            || Utility.isOptionYes(suspendFlag)) {
        throw new CprException(ReturnType.NOT_AUTHORIZED_EXCEPTION, serviceName);
    }

    List<Long> methodReturn = new ArrayList<Long>();
    methodReturn.add(localRegistrationAuthoritykey);
    methodReturn.add(raServerPrincipalKey);
    return methodReturn;

}

---

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

License:Apache License

/**
 * This method is used to verify that the client's IP address is authorized to call the service for the particular RA.
 * @param raServerPrincipalKey contains the ra server principal key associated with the RA.
 * @param serviceName contains the name of the service that is being called.
 * @param clientIpAddress contains the ip address of the caller.
 * @throws CprException will be thrown if there are any CPR related problems.
 *///ww w.j  ava 2s  .c om
private void verifyClientIpAddress(final Long raServerPrincipalKey, final String serviceName,
        final String clientIpAddress) throws CprException {

    Long localRaServerPrincipalKey = NOT_FOUND_VALUE;

    final StringBuilder sb = new StringBuilder();
    sb.append("select ra_server_principal_key from {h-schema}server_principal_ip ");
    sb.append("where ra_server_principal_key = :ra_server_principal_key AND ");
    sb.append("(ip_address = :wildcard or ip_address = :client_ip_address)");

    SQLQuery query = session.createSQLQuery(sb.toString());
    query.addScalar("ra_server_principal_key", StandardBasicTypes.LONG);
    query.setParameter("ra_server_principal_key", raServerPrincipalKey);
    query.setParameter("wildcard", WILD_CARD_IP);
    query.setParameter("client_ip_address", clientIpAddress);

    for (Iterator<?> it = query.list().iterator(); it.hasNext();) {
        localRaServerPrincipalKey = (Long) it.next();
    }

    if (localRaServerPrincipalKey.equals(NOT_FOUND_VALUE)) {
        throw new CprException(ReturnType.NOT_AUTHORIZED_EXCEPTION, serviceName);
    }
}

---

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

License:Apache License

/**
 * This routine will determine if a particular server principal is authorized to call a service.
 * @param principalId contains the requestor's principal identifier.
 * @param requestor contains the userid of the person requesting access.
 * @param serviceName contains the name of the service.
 * @param clientIpAddress contains the client ip address.
 * @throws CprException //from  w  ww .j av a  2s  .  c o m
 */
public void requestorAuthorized(final String principalId, final String requestor, final String serviceName,
        final String clientIpAddress) throws CprException {

    String grpMbrsSuspendFlag = "Y";
    String cprAccGrpsSuspendFlag = "Y";
    String webSrvAccSuspendFlag = "Y";
    Long localCprAccessGroupsKey = NOT_FOUND_VALUE;

    // Get the RA information.
    List<Long> methodReturn = findRegistrationAuthority(principalId, serviceName);
    Long localRegistrationAuthorityKey = methodReturn.get(0);
    Long raServerPrincipalKey = methodReturn.get(1);

    // Determine if the client ip address is valid for the particular RA.
    verifyClientIpAddress(raServerPrincipalKey, serviceName, clientIpAddress);

    // Determine the user's status and group for the particular RA.

    // Build the query.
    final StringBuilder sb = new StringBuilder();
    sb.append(
            "SELECT cpr_access_groups_key, grpmbrs_suspend_flag, cpraccgprs_suspend_flag, websrvacc_suspend_flag ");
    sb.append("FROM {h-schema}v_ra_group_web_service ");
    sb.append("WHERE registration_authority_key = :l_ra_key ");
    sb.append("AND ra_server_principal_key = :ra_sp_key ");
    sb.append("AND web_service = :web_service_in ");
    sb.append("AND userid = :requested_by_in");

    // Create the query, bind the parameters and determine the returns.
    SQLQuery query = session.createSQLQuery(sb.toString());
    query.setParameter("l_ra_key", localRegistrationAuthorityKey);
    query.setParameter("ra_sp_key", raServerPrincipalKey);
    query.setParameter("web_service_in", serviceName);
    query.setParameter("requested_by_in", requestor);
    query.addScalar("cpr_access_groups_key", StandardBasicTypes.LONG);
    query.addScalar("grpmbrs_suspend_flag", StandardBasicTypes.STRING);
    query.addScalar("cpraccgprs_suspend_flag", StandardBasicTypes.STRING);
    query.addScalar("websrvacc_suspend_flag", StandardBasicTypes.STRING);

    // Perform the query.
    for (Iterator<?> it = query.list().iterator(); it.hasNext();) {
        Object[] res = (Object[]) it.next();
        localCprAccessGroupsKey = (Long) res[CPR_ACCESS_GROUPS_KEY];
        grpMbrsSuspendFlag = (String) res[GRP_MBRS_SUSPEND_FLAG];
        cprAccGrpsSuspendFlag = (String) res[CPR_GRPS_SUSPEND_FLAG];
        webSrvAccSuspendFlag = (String) res[WEB_SRV_SUSPEND_FLAG];
    }

    // If any of the suspend flags is set to Yes, we need to throw an exception.
    if (localCprAccessGroupsKey.equals(NOT_FOUND_VALUE) || Utility.isOptionYes(grpMbrsSuspendFlag)
            || Utility.isOptionYes(cprAccGrpsSuspendFlag) || Utility.isOptionYes(webSrvAccSuspendFlag)) {
        throw new CprException(ReturnType.NOT_AUTHORIZED_EXCEPTION, serviceName);
    }

    setCprAccessGroupsKey(localCprAccessGroupsKey);
    setRegistrationAuthorityKey(localRegistrationAuthorityKey);

}

---

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

License:Apache License

/**
 * This routine is used to verify that the requester is allowed to perform an operation on a particular data type.
 * This routine will return true if the operation is allowed, otherwise it will throw an exception.
 * @param dataResource contains the data source that is being checked.
 * @param action contains the action that is being checked.
 * @param requestedBy contains the access id of the perform who requested this operation.
 * @return will return true if successful.
 * @throws CprException will be thrown if the access is denied.
 *//* ww w. j a  va 2 s . c  o  m*/
public boolean isDataActionAuthorized(final String dataResource, final String action, final String requestedBy)
        throws CprException {

    // Verify that the operation being checked is against a valid data key.
    final Long dataTypeKey = AccessType.valueOf(dataResource.toUpperCase().trim()).index();
    final Long accessOperationKey = AccessType.valueOf(action.toUpperCase().trim()).index();
    boolean dataKeyValid = false;
    try {

        // Build the query.
        final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
        sb.append("SELECT data_types.data_type_key ");
        sb.append("FROM {h-schema}data_types ");
        sb.append("WHERE data_types.data_type_key = :data_type_key_in ");
        sb.append("AND data_types.active_flag = 'Y' ");

        // Create the query, bind the parameters and set the return type.
        final SQLQuery query = session.createSQLQuery(sb.toString());
        query.setParameter("data_type_key_in", dataTypeKey);
        query.addScalar("data_type_key", StandardBasicTypes.LONG);

        for (final Iterator<?> it = query.list().iterator(); it.hasNext();) {
            it.next();
            dataKeyValid = true;
        }
    } finally {
        if (!dataKeyValid) {
            throw new CprException(ReturnType.DATA_CHANGE_EXCEPTION, dataResource);
        }
    }

    // Do the query to determine if they have access.
    String readFlag = "N";
    String writeFlag = "N";
    String archiveFlag = "N";
    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
    sb.append("SELECT v_group_data_type_access.read_flag, v_group_data_type_access.write_flag, ");
    sb.append("v_group_data_type_access.archive_flag ");
    sb.append("FROM {h-schema}v_group_data_type_access ");
    sb.append("WHERE v_group_data_type_access.cpr_access_groups_key = :cpr_access_groups_key_in ");
    sb.append("AND v_group_data_type_access.data_type_key = :data_type_key_in");

    // Create the query, bind the parameters and set the return type.
    final SQLQuery query = session.createSQLQuery(sb.toString());
    query.setParameter("cpr_access_groups_key_in", getCprAccessGroupsKey());
    query.setParameter("data_type_key_in", dataTypeKey);
    query.addScalar("read_flag", StandardBasicTypes.STRING);
    query.addScalar("write_flag", StandardBasicTypes.STRING);
    query.addScalar("archive_flag", StandardBasicTypes.STRING);

    for (final Iterator<?> it = query.list().iterator(); it.hasNext();) {
        Object[] res = (Object[]) it.next();
        readFlag = (String) res[0];
        writeFlag = (String) res[1];
        archiveFlag = (String) res[2];
    }

    boolean hasAccess = false;
    if (accessOperationKey == AccessType.ACCESS_OPERATION_ARCHIVE.index()) {
        hasAccess = Utility.isOptionYes(archiveFlag);
    } else if (accessOperationKey == AccessType.ACCESS_OPERATION_READ.index()) {
        hasAccess = Utility.isOptionYes(readFlag);
    } else if (accessOperationKey == AccessType.ACCESS_OPERATION_WRITE.index()) {
        hasAccess = Utility.isOptionYes(writeFlag);
    }

    if (!hasAccess) {
        throw new CprException(ReturnType.DATA_CHANGE_EXCEPTION, AccessType.get(dataTypeKey).toString());
    }

    return hasAccess;
}

---

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

License:Apache License

/**
 * This routine is used to verify that the requester is allowed to perform an operation on a particular data type.
 * This routine will return true if the operation is allowed, otherwise it will throw an exception.
 * @param iamGroupKey contains the iam group key which indicates which group the user is a member of. 
 * @param dataTypeKey contains the data type key associated with the data element.
 * @param accessOperationKey contains the access operation key which indicates the type of operation.
 * @param requestedBy contains the access id of the perform who requested this operation.
 * @return will return true if successful.
 * @throws CprException will be thrown if the access is denied.
 *///from ww  w .  j av a 2 s .c  o  m
public boolean isDataActionAuthorizedOldCode(final long iamGroupKey, final long dataTypeKey,
        final long accessOperationKey, final String requestedBy) throws CprException {

    // Verify that the operation being checked is against a valid data key.
    boolean dataKeyValid = false;
    try {

        // Build the query.
        final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
        sb.append("SELECT data_types.data_type_key ");
        sb.append("FROM {h-schema}data_types ");
        sb.append("WHERE data_types.data_type_key = :data_type_key_in ");
        sb.append("AND data_types.active_flag = 'Y' ");

        // Create the query, bind the parameters and set the return type.
        final SQLQuery query = session.createSQLQuery(sb.toString());
        query.setParameter("data_type_key_in", dataTypeKey);
        query.addScalar("data_type_key", StandardBasicTypes.LONG);

        final Iterator<?> it = query.list().iterator();
        if (it.hasNext()) {
            dataKeyValid = true;
        }
    } finally {
        if (!dataKeyValid) {
            throw new CprException(ReturnType.DATA_CHANGE_EXCEPTION, AccessType.get(dataTypeKey).toString());
        }
    }

    // Do the query to determine if they have access.
    String readFlag = "N";
    String writeFlag = "N";
    String archiveFlag = "N";
    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);
    sb.append("SELECT v_group_data_type_access.read_flag, v_group_data_type_access.write_flag, ");
    sb.append("v_group_data_type_access.archive_flag ");
    sb.append("FROM {h-schema}v_group_data_type_access ");
    sb.append("WHERE v_group_data_type_access.iam_group_key = :iam_group_key_in ");
    sb.append("AND v_group_data_type_access.data_type_key = :data_type_key_in");

    // Create the query, bind the parameters and set the return type.
    final SQLQuery query = session.createSQLQuery(sb.toString());
    query.setParameter("iam_group_key_in", iamGroupKey);
    query.setParameter("data_type_key_in", dataTypeKey);
    query.addScalar("read_flag", StandardBasicTypes.STRING);
    query.addScalar("write_flag", StandardBasicTypes.STRING);
    query.addScalar("archive_flag", StandardBasicTypes.STRING);

    final Iterator<?> it = query.list().iterator();
    if (it.hasNext()) {
        Object[] res = (Object[]) it.next();
        readFlag = (String) res[0];
        writeFlag = (String) res[1];
        archiveFlag = (String) res[2];
    }

    boolean hasAccess = false;
    if (accessOperationKey == AccessType.ACCESS_OPERATION_ARCHIVE.index()) {
        hasAccess = Utility.isOptionYes(archiveFlag);
    } else if (accessOperationKey == AccessType.ACCESS_OPERATION_READ.index()) {
        hasAccess = Utility.isOptionYes(readFlag);
    } else if (accessOperationKey == AccessType.ACCESS_OPERATION_WRITE.index()) {
        hasAccess = Utility.isOptionYes(writeFlag);
    }

    if (!hasAccess) {
        throw new CprException(ReturnType.DATA_CHANGE_EXCEPTION, AccessType.get(dataTypeKey).toString());
    }

    return hasAccess;
}

---

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

License:Apache License

/**
 * This routine is used to determine if an RA is authorize to assign an affiliation.
 * @param affiliationType - contains the affiliation
 * @param requestedBy - userid of the requestor
 * //from  w  ww. j  av a 2  s.co  m
 * @return true if ra is authorized for affiliation
 * 
 * @throws CprException
 */
public boolean isAffiliationAccessAuthorized(final String affiliationType, final String requestedBy)
        throws CprException {

    final Long affiliationKey = AffiliationsType.valueOf(affiliationType.toUpperCase().trim()).index();
    boolean affiliationKeyValid = false;
    final StringBuilder sb = new StringBuilder(BUFFER_SIZE);

    // Build the query.

    sb.append("SELECT affiliations.affiliation_key ");
    sb.append("FROM {h-schema}affiliations ");
    sb.append("WHERE affiliations.affiliation_key = :affiliation_key_in ");
    sb.append("AND affiliations.active_flag = 'Y' ");

    SQLQuery query = session.createSQLQuery(sb.toString());
    query.setParameter("affiliation_key_in", affiliationKey);
    query.addScalar("affiliation_key", StandardBasicTypes.LONG);

    Iterator<?> it = query.list().iterator();
    if (it.hasNext()) {
        affiliationKeyValid = true;
    }
    if (!affiliationKeyValid) {
        throw new CprException(ReturnType.DATA_CHANGE_EXCEPTION,
                AffiliationsType.get(affiliationKey).toString());
    }
    sb.setLength(0);
    sb.append("select * FROM {h-schema}ra_affiliation ");
    sb.append("WHERE affiliation_key = :affiliation_key_in ");
    sb.append("AND registration_authority_key= :ra_type_key_in ");
    sb.append("AND end_date is null ");
    // Create the query, bind the parameters and set the return type.
    query = session.createSQLQuery(sb.toString());
    query.setParameter("affiliation_key_in", affiliationKey);
    query.setParameter("ra_type_key_in", getRegistrationAuthorityKey());

    it = query.list().iterator();
    if (!it.hasNext()) {
        affiliationKeyValid = false;
    }
    if (!affiliationKeyValid) {
        throw new CprException(ReturnType.DATA_CHANGE_EXCEPTION,
                AffiliationsType.get(affiliationKey).toString());
    }
    return affiliationKeyValid;
}

---

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 psu id number.
 * @param psuId contains the psu id number.
 * @return person id if the psu id can be found, otherwise it will return a -1 to indicate an error.
 * @throws CprException /*from  www . jav  a 2 s.c o  m*/
 */
public long getPersonIdUsingPsuId(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.PERSON_NOT_FOUND_EXCEPTION);
    }

    return personId;

}

---

• «
• 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
• »