Example usage for org.hibernate SQLQuery setString

List of usage examples for org.hibernate SQLQuery setString

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

Introduction

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

Prototype

@Deprecated
@SuppressWarnings("unchecked")
default Query<R> setString(int position, String val)

Source Link

Document

Bind a positional String-valued parameter.

Usage

From source file:fr.gael.dhus.database.dao.UserDao.java

License:Open Source License

@Override
public void delete(final User user) {
    if (user == null)
        return;// w ww.j ava  2s  .  com

    // remove user external references
    final String uid = user.getUUID();
    productCartDao.deleteCartOfUser(user);
    getHibernateTemplate().execute(new HibernateCallback<Void>() {
        @Override
        public Void doInHibernate(Session session) throws HibernateException, SQLException {
            String sql = "DELETE FROM COLLECTION_USER_AUTH WHERE USERS_UUID = :uid";
            SQLQuery query = session.createSQLQuery(sql);
            query.setString("uid", uid);
            query.executeUpdate();
            return null;
        }
    });
    getHibernateTemplate().execute(new HibernateCallback<Void>() {
        @Override
        public Void doInHibernate(Session session) throws HibernateException, SQLException {
            String sql = "DELETE FROM PRODUCT_USER_AUTH WHERE USERS_UUID = :uid";
            SQLQuery query = session.createSQLQuery(sql);
            query.setString("uid", uid);
            query.executeUpdate();
            return null;
        }
    });
    getHibernateTemplate().execute(new HibernateCallback<Void>() {
        @Override
        public Void doInHibernate(Session session) throws HibernateException, SQLException {
            String sql = "UPDATE PRODUCTS SET OWNER_UUID = NULL " + "WHERE OWNER_UUID = :uid";
            SQLQuery query = session.createSQLQuery(sql);
            query.setString("uid", uid);
            query.executeUpdate();
            return null;
        }
    });
    getHibernateTemplate().execute(new HibernateCallback<Void>() {
        @Override
        public Void doInHibernate(Session session) throws HibernateException, SQLException {
            String sql = "DELETE FROM NETWORK_USAGE WHERE USER_UUID = :uid";
            SQLQuery query = session.createSQLQuery(sql);
            query.setString("uid", uid);
            query.executeUpdate();
            return null;
        }
    });

    fireDeletedEvent(new DaoEvent<User>(user));
    super.delete(user);
}

From source file:gov.nih.nci.caarray.security.AuthorizationManagerExtensions.java

License:BSD License

private static boolean checkPermissionWithCanonicalTable(String userName, String className,
        String attributeName, String value, String privilegeName, Application application, Session s)
        throws CSException {
    String sql = " select pe.protection_element_id from csm_protection_element pe "
            + "inner join csm_pg_pe pgpe on pe.protection_element_id = pgpe.protection_element_id "
            + "inner join csm_user_group_role_pg ugrpg on pgpe.protection_group_id = ugrpg.protection_group_id "
            + "inner join csm_role r on ugrpg.role_id = r.role_id "
            + "inner join csm_user_group ug on ugrpg.group_id = ug.group_id "
            + "inner join csm_role_privilege rp on r.role_id = rp.role_id "
            + "inner join csm_privilege p on rp.privilege_id = p.privilege_id "
            + "inner join csm_user u on ug.user_id = u.user_id "
            + "where pe.object_id = :class_name and pe.attribute = :attr_name "
            + "and pe.attribute_value = :attr_value and u.login_name = :login_name "
            + "and p.privilege_name= :priv_name and pe.application_id = :app_id";
    SQLQuery query = s.createSQLQuery(sql);
    query.setString("class_name", className);
    query.setString("attr_name", attributeName);
    query.setString("attr_value", value);
    query.setString("login_name", userName);
    query.setString("priv_name", privilegeName);
    query.setLong("app_id", application.getApplicationId());

    List<?> results = query.list();
    return !results.isEmpty();
}

From source file:gov.nih.nci.caarray.security.AuthorizationManagerExtensions.java

License:BSD License

private static boolean checkPermissionWithMappingTable(String groupTableName, String userName, String value,
        String privilegeName, Session s) throws CSException {
    String sql = " select pe.attribute_value from " + groupTableName + " pe "
            + "inner join csm_user_group ug on pe.group_id = ug.group_id "
            + "inner join csm_privilege p on pe.privilege_id = p.privilege_id "
            + "inner join csm_user u on ug.user_id = u.user_id "
            + "where pe.attribute_value = :attr_value and u.login_name = :login_name "
            + "and p.privilege_name= :priv_name";
    SQLQuery query = s.createSQLQuery(sql);
    query.setString("attr_value", value);
    query.setString("login_name", userName);
    query.setString("priv_name", privilegeName);

    List<?> results = query.list();
    return !results.isEmpty();
}

From source file:gov.nih.nci.caarray.security.SecurityUtils.java

License:BSD License

private static Map<Long, Privileges> getPermissionsWithMappingTable(String groupTableName, String userName,
        Collection<Long> protectableIds) {
    final String sql = " select distinct pe.attribute_value, p.privilege_name from " + groupTableName + " pe "
            + "inner join csm_user_group ug on pe.group_id = ug.group_id "
            + "inner join csm_privilege p on pe.privilege_id = p.privilege_id "
            + "inner join csm_user u on ug.user_id = u.user_id "
            + "where pe.attribute_value in (:attr_values) and u.login_name = :login_name "
            + "order by pe.attribute_value, p.privilege_name";
    final SQLQuery query = hibernateHelper.getCurrentSession().createSQLQuery(sql);
    query.setParameterList("attr_values", protectableIds);
    query.setString("login_name", userName);

    @SuppressWarnings("unchecked")
    final List<Object[]> results = query.list();
    return createPrivilegesMapFromResults(results);
}

From source file:gov.nih.nci.caarray.security.SecurityUtils.java

License:BSD License

private static Map<Long, Privileges> getPermissionsWithCanonicalTable(String userName, String className,
        String attributeName, Collection<Long> protectableIds, Application application) {
    final String sql = " select distinct cast(pe.attribute_value as unsigned), "
            + "p.privilege_name from csm_protection_element pe "
            + "inner join csm_pg_pe pgpe on pe.protection_element_id = pgpe.protection_element_id "
            + "inner join csm_user_group_role_pg ugrpg "
            + "on pgpe.protection_group_id = ugrpg.protection_group_id "
            + "inner join csm_role r on ugrpg.role_id = r.role_id "
            + "inner join csm_user_group ug on ugrpg.group_id = ug.group_id "
            + "inner join csm_role_privilege rp on r.role_id = rp.role_id "
            + "inner join csm_privilege p on rp.privilege_id = p.privilege_id "
            + "inner join csm_user u on ug.user_id = u.user_id "
            + "where pe.object_id = :class_name and pe.attribute = :attr_name "
            + "and pe.attribute_value in (:attr_values) and u.login_name = :login_name "
            + "and pe.application_id = :app_id order by pe.attribute_value, p.privilege_name";
    final SQLQuery query = hibernateHelper.getCurrentSession().createSQLQuery(sql);
    query.setParameterList("attr_values", protectableIds);
    query.setString("login_name", userName);
    query.setString("class_name", className);
    query.setString("attr_name", attributeName);
    query.setLong("app_id", application.getApplicationId());

    @SuppressWarnings("unchecked")
    final List<Object[]> results = query.list();
    return createPrivilegesMapFromResults(results);
}

From source file:gov.nih.nci.caintegrator.data.CaIntegrator2DaoImpl.java

License:BSD License

private void setSymbolParameters(SQLQuery query, List<String> symbolsUpper) {
    for (int i = 0; i < symbolsUpper.size(); i++) {
        query.setString(i, symbolsUpper.get(i));
    }//from   w w  w.j  a  v a  2  s  . c o  m
}

From source file:gov.nih.nci.nbia.wadosupport.WADOSupportDAOImpl.java

License:BSD License

@Transactional(propagation = Propagation.REQUIRED)
public List<DICOMSupportDTO> getDICOMSupportDTO(DICOMParameters params, List<String> extraFields) {
    List<DICOMSupportDTO> returnValues = new ArrayList<DICOMSupportDTO>();

    try {/*from www  . j a  va 2 s.c  o  m*/
        String user = NCIAConfig.getGuestUsername();

        String queryString = DICOM_QUERY;
        List<String> parameterList = new ArrayList<String>();
        if (params.getPatientName() != null) {
            queryString = queryString + " and p.patient_name like ?";
            parameterList.add(params.getPatientName());
            log.info("added patient name to query : " + params.getPatientName());
        }
        if (params.getPatientID() != null) {
            queryString = queryString + " and p.patient_id like ?";
            parameterList.add(params.getPatientID());
            log.info("added patient id to query : " + params.getPatientID());
        }
        if (params.getStudyInstanceUID() != null) {
            queryString = queryString + " and gs.study_instance_uid like ?";
            parameterList.add(params.getStudyInstanceUID());
            log.info("added study instance uid to query : " + params.getStudyInstanceUID());
        }
        if (params.getSeriesInstanceUID() != null) {
            queryString = queryString + " and gs.series_instance_uid like ?";
            parameterList.add(params.getSeriesInstanceUID());
            log.info("added series instance uid to query : " + params.getSeriesInstanceUID());
        }
        if (params.getStudyDescription() != null) {
            queryString = queryString + " and s.study_description like ?";
            parameterList.add(params.getStudyDescription());
            log.info("added study description to query : " + params.getStudyDescription());
        }
        SQLQuery query = this.getHibernateTemplate().getSessionFactory().getCurrentSession()
                .createSQLQuery(queryString);
        int p = 0;
        for (String param : parameterList) {
            query.setString(p, param.trim());
            p++;
        }
        List<Object[]> images = query.list();
        if (images.size() == 0) {
            log.error("images not found");
            // returnValue.setErrors("images not found");
            return returnValues;
        }
        List<SiteData> authorizedSites;
        UserObject uo = userTable.get(user);
        if (uo != null) {
            authorizedSites = uo.getAuthorizedSites();
            if (authorizedSites == null) {
                AuthorizationManager manager = new AuthorizationManager(user);
                authorizedSites = manager.getAuthorizedSites();
                uo.setAuthorizedSites(authorizedSites);
            }
        } else {
            System.out.println("the user is " + user);
            AuthorizationManager manager = new AuthorizationManager(user);
            authorizedSites = manager.getAuthorizedSites();
            uo = new UserObject();
            uo.setAuthorizedSites(authorizedSites);
            userTable.put(user, uo);
        }
        for (int i = 0; i < images.size(); i++) {
            String collection = (String) images.get(0)[0];
            String site = (String) images.get(0)[1];
            boolean isAuthorized = false;
            for (SiteData siteData : authorizedSites) {
                if (siteData.getCollection().equals(collection)) {
                    if (siteData.getSiteName().equals(site)) {
                        isAuthorized = true;
                        break;
                    }
                }
            }
            if (!isAuthorized) {
                System.out.println("User: " + user + " not authorized");
                continue; //not authorized
            }
            String filePath = (String) images.get(0)[2];
            String patientName = (String) images.get(0)[3];
            String patientID = (String) images.get(0)[4];
            String modality = (String) images.get(0)[5];
            String studyDate = null;
            try {
                studyDate = ((Date) images.get(0)[6]).toString();
            } catch (Exception e) {
                // its null
            }
            String studyTime = null;
            try {
                studyTime = ((java.math.BigDecimal) images.get(0)[7]).toString();
            } catch (Exception e) {
                // its null
            }
            String accessionNumber = null;
            try {
                accessionNumber = ((java.math.BigDecimal) images.get(0)[8]).toString();
            } catch (Exception e) {
                // its null
            }
            String studyID = (String) images.get(0)[9];
            String studyDescription = (String) images.get(0)[10];
            String seriesNumber = ((Object) images.get(0)[11]).toString();
            String seriesInstanceUID = (String) images.get(0)[12];
            String seriesDescription = (String) images.get(0)[13];
            String referringPhysicianName = "not implemented";
            String patientBirthDate = null;
            try {
                patientBirthDate = ((Date) images.get(0)[14]).toString();
            } catch (Exception e) {
                // its null
            }
            String modalitiesInStudy = (String) images.get(0)[15];
            String studyInstanceUID = (String) images.get(0)[16];
            String sOPInstanceUID = (String) images.get(0)[17];
            File imageFile = new File(filePath);
            if (!imageFile.exists()) {
                log.error("File " + filePath + " does not exist");
                // returnValue.setErrors("File does not exist");
                // return returnValue; 
            } else {
                DICOMSupportDTO returnItem = new DICOMSupportDTO();
                returnItem.setFilePath(imageFile.getPath());
                returnItem.setFileName(imageFile.getName());
                returnItem.setFileSize(new Long(imageFile.length()).toString());
                Hashtable<String, String> extraFieldMap = new Hashtable<String, String>();
                extraFieldMap.put("PatientName", stringForNull(patientName));
                extraFieldMap.put("PatientID", stringForNull(patientID));
                extraFieldMap.put("Modality", stringForNull(modality));
                extraFieldMap.put("StudyDate", stringForNull(studyDate));
                extraFieldMap.put("StudyTime", stringForNull(studyTime));
                extraFieldMap.put("AccessionNumber", stringForNull(accessionNumber));
                extraFieldMap.put("StudyID", stringForNull(studyID));
                extraFieldMap.put("StudyDescription", stringForNull(studyDescription));
                extraFieldMap.put("SeriesNumber", stringForNull(seriesNumber));
                extraFieldMap.put("SeriesInstanceUID", stringForNull(seriesInstanceUID));
                extraFieldMap.put("SeriesDescription", stringForNull(seriesDescription));
                extraFieldMap.put("ReferringPhysicianName", stringForNull(referringPhysicianName));
                extraFieldMap.put("PatientBirthDate", stringForNull(patientBirthDate));
                extraFieldMap.put("ModalitiesInStudy", stringForNull(modalitiesInStudy));
                extraFieldMap.put("StudyInstanceUID", stringForNull(studyInstanceUID));
                extraFieldMap.put("SOPInstanceUID", stringForNull(sOPInstanceUID));
                returnItem.setFieldMap(extraFieldMap);
                returnValues.add(returnItem);
                log.info("added dicom dto " + returnItem.toString());
            }
        }

    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
        //returnValue.setErrors("unable to process request");
        return returnValues;
    }

    return returnValues;
}

From source file:jp.go.nict.langrid.dao.hibernate.HibernateAccessRightDao.java

License:Open Source License

@SuppressWarnings("unchecked")
public AccessRightSearchResult searchAccessRightsAccordingToDefaultAndOwner(int startIndex, int maxCount,
        String userGridId, String userId, String serviceAndOwnerGridId, String[] serviceIds, String ownerUserId,
        Order[] orders) throws DaoException {
    if (orders.length > 0) {
        orders = ArrayUtil.collect(orders, new Transformer<Order, Order>() {
            @Override//from  www  .j  a  v  a  2s  . c  o  m
            public Order transform(Order value) throws TransformationException {
                String mapped = mappedFields.get(value.getFieldName());
                if (mapped != null) {
                    return new Order(mapped, value.getDirection());
                } else {
                    return value;
                }
            }
        });
    }
    String orderby = "order by lower(l.sn), lower(l.on)";
    if (orders.length > 0) {
        orderby = QueryUtil.buildOrderByQuery(capitalIgnoreFields, orders);
    }
    Session session = getSession();
    getContext().beginTransaction();
    try {
        if (getContext().getTransactionNestCount() > 1) {
            session.flush();
        }
        String servicesParameters = null;
        if (serviceAndOwnerGridId.length() > 0) {
            servicesParameters = " and s.gridId=:serviceGridId";
            servicesParameters += serviceIds.length > 0
                    ? " and s.serviceid in (" + StringUtil.repeatedString(new StringGenerator() {
                        public String generate() {
                            return ":service" + i++;
                        }

                        private int i = 0;
                    }, serviceIds.length, ", ") + ")"
                    : "";
        } else {
            servicesParameters = "";
        }
        String usersParameters = null;
        if (userGridId.length() > 0) {
            usersParameters = " and (u.gridId=:userGridId";
            usersParameters += userId.length() > 0 ? " and u.userid=:userId)" : " and u.userid<>'*')";
        } else {
            usersParameters = " and u.userid<>'*'";
        }
        String ownerExcludes = (serviceAndOwnerGridId.length() > 0 && ownerUserId.length() > 0)
                ? " and (u.gridId<>:ownerUserGridId or u.userid<>:ownerUserId)"
                : "";
        Query q = session.createSQLQuery(String.format(selectJoinedEntities, servicesParameters,
                usersParameters, ownerExcludes, orderby));
        q.setFirstResult(startIndex);
        q.setMaxResults(maxCount);
        if (serviceAndOwnerGridId.length() > 0) {
            q.setString("serviceGridId", serviceAndOwnerGridId);
        }
        for (int i = 0; i < serviceIds.length; i++) {
            q.setString("service" + i, serviceIds[i]);
        }
        if (userGridId.length() > 0) {
            q.setString("userGridId", userGridId);
        }
        if (userId.length() > 0) {
            q.setString("userId", userId);
        }
        if (serviceAndOwnerGridId.length() > 0 && ownerUserId.length() > 0) {
            q.setString("ownerUserGridId", serviceAndOwnerGridId);
            q.setString("ownerUserId", ownerUserId);
        }
        List<Object> list = q.list();
        List<AccessRight> elements = new ArrayList<AccessRight>();
        for (Object o : list) {
            Object[] values = (Object[]) o;
            String ugid = values[0].toString();
            String uid = values[1].toString();
            String sgid = values[2].toString();
            String sid = values[3].toString();
            Boolean permitted = (Boolean) values[4];
            Boolean gdPermitted = (Boolean) values[5];
            Boolean sdPermitted = (Boolean) values[6];
            boolean p = false;
            if (permitted != null) {
                p = permitted;
            } else if (gdPermitted != null) {
                p = gdPermitted;
            } else if (sdPermitted != null) {
                p = sdPermitted;
            }
            AccessRight ar = new AccessRight(ugid, uid, sgid, sid, p);
            Timestamp ct = ((Timestamp) values[7]);
            if (ct != null) {
                ar.setCreatedDateTime(CalendarUtil.createFromMillis(ct.getTime()));
            }
            Timestamp ut = ((Timestamp) values[8]);
            if (ut != null) {
                ar.setCreatedDateTime(CalendarUtil.createFromMillis(ut.getTime()));
            }
            elements.add(ar);
        }
        long totalCount = 0;
        if (elements.size() < maxCount) {
            totalCount = elements.size() + startIndex;
        } else {
            SQLQuery cq = session.createSQLQuery(
                    String.format(countJoinedEntities, servicesParameters, usersParameters, ownerExcludes));
            if (serviceAndOwnerGridId.length() > 0) {
                cq.setString("serviceGridId", serviceAndOwnerGridId);
            }
            for (int i = 0; i < serviceIds.length; i++) {
                cq.setString("service" + i, serviceIds[i]);
            }
            if (userGridId.length() > 0) {
                cq.setString("userGridId", userGridId);
            }
            if (userId.length() > 0) {
                cq.setString("userId", userId);
            }
            if (serviceAndOwnerGridId.length() > 0 && ownerUserId.length() > 0) {
                cq.setString("ownerUserGridId", serviceAndOwnerGridId);
                cq.setString("ownerUserId", ownerUserId);
            }
            totalCount = ((Number) cq.uniqueResult()).longValue();
        }

        /*         Criteria c = session.createCriteria(AccessRight.class);
                 addSearchAccessRightCriterionIgnoreDefault(c, userId, serviceIds);
                 List<AccessRight> elements = (List<AccessRight>)CriteriaUtil.getList(
                       c, startIndex, maxCount, orders);
                 int totalCount = 0;
                 if(elements.size() < maxCount){
                    totalCount = elements.size() + startIndex;
                 } else{
                    Criteria cr = session.createCriteria(AccessRight.class);
                    addSearchAccessRightCriterionIgnoreDefault(cr, userId, serviceIds);
                    totalCount = CriteriaUtil.getCount(cr);
                 }
        */ AccessRightSearchResult r = new AccessRightSearchResult(elements.toArray(new AccessRight[] {}),
                (int) totalCount, true);
        getContext().commitTransaction();
        return r;
    } catch (HibernateException e) {
        logAdditionalInfo(e);
        getContext().rollbackTransaction();
        throw new DaoException(e);
    } catch (RuntimeException e) {
        getContext().rollbackTransaction();
        throw new DaoException(e);
    } catch (Error e) {
        getContext().rollbackTransaction();
        throw new DaoException(e);
    }
}

From source file:lt.emasina.resthub.server.handler.Handler.java

License:Open Source License

public void applyParameters(SQLQuery query) throws SQLException {
    for (Map.Entry<QueryParameter, Object> e : parameters.entrySet()) {

        QueryParameter p = e.getKey();//w  w w  .  j a  va 2  s  .  c  o  m
        Object value = e.getValue();
        String name = p.getSqlName();

        if (value != null && p.getArray()) {

            switch (p.getType()) {
            case DATE:
                query.setParameterList(name, (Object[]) value, new DateType());
                break;
            case NUMBER:
                query.setParameterList(name, (Object[]) value, new BigDecimalType());
                break;
            case STRING:
                query.setParameterList(name, (Object[]) value, new StringType());
                break;
            case CLOB:
            case BLOB:
                throw new ClientErrorException(Status.CLIENT_ERROR_BAD_REQUEST,
                        String.format("LOBs are not supported as parameters: %s", name));
            }

        } else {

            switch (p.getType()) {
            case DATE:
                query.setDate(name, (Date) value);
                break;
            case NUMBER:
                query.setBigDecimal(name, (BigDecimal) value);
                break;
            case STRING:
                query.setString(name, (String) value);
                break;
            case CLOB:
            case BLOB:
                throw new ClientErrorException(Status.CLIENT_ERROR_BAD_REQUEST,
                        String.format("LOBs are not supported as parameters: %s", name));
            }
        }
    }
}

From source file:mpimp.assemblxweb.db.util.AssemblXWebDBUtil.java

License:Open Source License

@SuppressWarnings("unchecked")
public static Boolean readUserData(AssemblXWebModel model) throws AssemblXException {
    Session hibernateSession = null;// ww w. ja va  2  s .c o  m
    try {
        hibernateSession = HibernateSessionFactory.getSession();
        hibernateSession.beginTransaction();

        SQLQuery query = hibernateSession
                .createSQLQuery(
                        "select us.id as operatorId, " + "us.login_name as login, us.password as password,"
                                + " us.first_name as firstName, us.last_name as lastName "
                                + "from user us where us.login_name = :loginName")
                .addScalar("operatorId", IntegerType.INSTANCE).addScalar("login", StringType.INSTANCE)
                .addScalar("password", StringType.INSTANCE).addScalar("firstName", StringType.INSTANCE)
                .addScalar("lastName", StringType.INSTANCE);

        query.setString("loginName", model.getOperator().getLogin());
        query.setResultTransformer(Transformers.aliasToBean(OperatorRecord.class));
        ArrayList<OperatorRecord> operatorRecords = new ArrayList<OperatorRecord>();
        operatorRecords = (ArrayList<OperatorRecord>) query.list();
        hibernateSession.getTransaction().commit();
        if (operatorRecords.size() == 1) {
            model.setOperator(operatorRecords.get(0));
            return true;
        } else {
            return false;
        }
    } catch (Exception e) {
        //         if (hibernateSession.getTransaction() != null && hibernateSession.getTransaction().isActive()) {
        //            hibernateSession.getTransaction().rollback();
        //         }
        String message = "Error during reading user data from database. " + e.getMessage();
        logger_.error(message);
        throw new AssemblXException(message, AssemblXWebDBUtil.class);
    } finally {
        HibernateSessionFactory.closeSession();
    }
}