Example usage for org.hibernate SQLQuery addScalar

List of usage examples for org.hibernate SQLQuery addScalar

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

Introduction

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

Prototype

SQLQuery<T> addScalar(String columnAlias, Type type);

Source Link

Document

Declare a scalar query result.

Usage

From source file:de.csw.expertfinder.expertise.ExpertiseModel.java

License:Open Source License

/**
 * Returns the TF/IDF weighting for the given word in the given document.
 * Does no word normalization in terms of lemmatization or stemming! If
 * normalization is needed, it has to be done on the word before calling
 * this method.//from  ww  w.  j a v  a  2s .  c  o  m
 * 
 * @param documentId
 *            the id of the document
 * @param word
 *            the word (lemma or stem)
 * @return the TF/IDF
 */
public double getTFIDFWeight(Long documentId, String word) {
    persistenceStore.beginTransaction();

    SQLQuery q = persistenceStore
            .createSQLQuery("select count(*) as count from ( " + "select word from word w,  revision r "
                    + "where w.id_revision_created = r.id " + "and w.id_revision_deleted is null "
                    + "and r.id_document = :documentId " + "and word = :word) words");

    q.setLong("documentId", documentId).setString("word", word);
    q.addScalar("count", Hibernate.INTEGER);

    int wordDocumentFreq = (Integer) q.uniqueResult();

    q = persistenceStore.createSQLQuery("select count(*) as count from ( "
            + "select word from word w,  revision r " + "where w.id_revision_created = r.id "
            + "and w.id_revision_deleted is null " + "and r.id_document = :documentId) words");

    q.setLong("documentId", documentId);
    q.addScalar("count", Hibernate.INTEGER);

    int allDocumentFreq = (Integer) q.uniqueResult();

    q = persistenceStore.createSQLQuery("select count(*) as count from document");
    q.addScalar("count", Hibernate.INTEGER);

    int documentCount = (Integer) q.uniqueResult();

    q = persistenceStore.createSQLQuery(
            "select count(*) as count from ( " + "   select distinct d.id from document d, revision rc, word w "
                    + "   where w.word=:word " + "   and w.id_revision_created = rc.id "
                    + "   and w.id_revision_deleted is null " + "   and rc.id_document = d.id) word");

    q.setString("word", word);
    q.addScalar("count", Hibernate.INTEGER);

    int wordCorpusFreq = (Integer) q.uniqueResult();

    persistenceStore.endTransaction();

    double tf = (double) wordDocumentFreq / (double) allDocumentFreq;
    double idf = Math.log((double) documentCount / (double) wordCorpusFreq);

    return tf * idf;
}

From source file:de.csw.expertfinder.persistence.PersistenceStoreFacade.java

License:Open Source License

/**
 * Returns all distinct words as a set of Strings for a given document.
 * @param document/*from  ww w .  ja  v a 2 s .c o m*/
 * @return all distinct words as a set of Strings for the given document
 */
public List<String> getBagOfWordsForLatestRevision(Document document) {
    Session session = sessionFactory.getCurrentSession();
    SQLQuery q = session.createSQLQuery(
            "select distinct word from word w,  revision r " + "where w.id_revision_created = r.id "
                    + "and w.id_revision_deleted is null " + "and r.id_document = :documentId");

    q.setLong("documentId", document.getId());
    q.addScalar("word", Hibernate.STRING);

    List<String> result = q.list();

    return result;
}

From source file:de.csw.expertfinder.persistence.PersistenceStoreFacade.java

License:Open Source License

/**
 * /*ww w . j  a v  a2 s  .  c  o m*/
 * @param concept
 * @param author
 * @return
 */
public List<Object[]> getContributionForConcept(long conceptId, long authorId) {
    Session session = sessionFactory.getCurrentSession();
    SQLQuery query = session.createSQLQuery(
            "select  d.id as documentId, rc.count as rcCount, rd.count as rdCount, rd.id_author as deletor "
                    + "from word w " + "join  revision rc " + "on w.id_revision_created = rc.id "
                    + "and rc.id_author = :authorId " + "join document d " + "on rc.id_document = d.id  "
                    + "left outer join revision rd " + "on w.id_revision_deleted = rd.id "
                    + "where w.id_concept = :conceptId");
    query.setLong("authorId", authorId);
    query.setLong("conceptId", conceptId);
    query.addScalar("documentId", Hibernate.LONG);
    query.addScalar("rcCount", Hibernate.LONG);
    query.addScalar("rdCount", Hibernate.LONG);
    query.addScalar("deletor", Hibernate.LONG);

    return query.list();
}

From source file:de.csw.expertfinder.persistence.PersistenceStoreFacade.java

License:Open Source License

/**
 * Gets all contributions by a given author to a given concept by section
 * (or document, because the top level section spans and has the same title
 * as the document itself).<br/>//from w w  w. j a va  2 s . c om
 * For performance reasons, this method does not return an object graph but
 * a list of arrays of ids and other numbers (see returns section).
 * 
 * @return A list of Object[] arrays. The items in each object array are:<br/>
 *         0: the document id (Long)<br/>
 *         1: the section id (Long)<br/>
 *         2: the section level (Integer)<br/>
 *         3: the id of the revision where the contribution was added (Long)<br/>
 *         4: the id of the revision where the contribution was deleted or
 *            null if the contribution has not been deleted (Long)<br/>
 *         5: the id of the author who has deleted the contribution or null
 *            if the contribution has not been deleted (Long)<br/>
 *         6: a similarity value between 0.0 and 1.0 if a section could not
 *            be mapped to any concept but one of its parent sections or the
 *            document itself (Double). If one or more concepts could be found
 *            for this section, this value is null.<br/>
 * 
 */
@SuppressWarnings("unchecked")
public List<Object[]> getContributionsToSectionsWithConceptForAuthor(Concept concept, Author author) {
    Session session = sessionFactory.getCurrentSession();

    SQLQuery query = session.createSQLQuery(
            "select d.id as documentId, s.id as sectionId, s.level as sectionLevel, revCreated.count as revisionCreated, revDeleted.count as revisionDeleted, a.id as deletor, sc.similarity as similarity "
                    + "from word w " +

                    "join revision revCreated " + "   on w.id_revision_created = revCreated.id "
                    + "   and revCreated.id_author = :authorId " + "left outer join revision revDeleted "
                    + "   on w.id_revision_deleted = revDeleted.id " + "left outer join author a "
                    + "   on revDeleted.id_author = a.id " + "join section s " + "   on w.id_section = s.id "
                    + "join section_has_concept sc " + "   on sc.id_section = s.id "
                    + "   and sc.id_concept = :conceptId " + "join document d "
                    + "   on d.id = revCreated.id_document " + "where w.id_concept is null "
                    + "group by word, sectionId, revisionCreated, revisionDeleted "
                    + "order by documentId, sectionId, revisionCreated, revisionDeleted");

    query.addScalar("documentId", Hibernate.LONG).addScalar("sectionId", Hibernate.LONG)
            .addScalar("sectionLevel", Hibernate.INTEGER).addScalar("revisionCreated", Hibernate.LONG)
            .addScalar("revisionDeleted", Hibernate.LONG).addScalar("deletor", Hibernate.LONG)
            .addScalar("similarity", Hibernate.DOUBLE);

    query.setLong("conceptId", concept.getId());
    query.setLong("authorId", author.getId());

    return (List<Object[]>) query.list();
}

From source file:de.csw.expertfinder.persistence.PersistenceStoreFacade.java

License:Open Source License

/**
 * Retrieves ALL different words that are currently in the entire wiki (not
 * deleted), along with the number of their occurences.
 * /*ww w .  j a  v  a 2s  .  c o  m*/
 * @return A list of Object[] arrays. The first object in each array is an
 *         Integer representing the number of occurrences of the word
 *         (grouped by their lemmas), the second object is a String
 *         containing the word's lemma itself.
 */
@SuppressWarnings("unchecked")
public List<Object[]> getAllNonDeletedWords() {
    Session session = sessionFactory.getCurrentSession();
    SQLQuery query = session.createSQLQuery("select count(*) as wordcount, word from word "
            + "where id_revision_deleted is null " + "group by word");
    query.addScalar("wordcount", Hibernate.INTEGER).addScalar("word", Hibernate.STRING);

    return (List<Object[]>) query.list();
}

From source file:de.fhdo.terminologie.ws.search.ListValueSetContents.java

License:Apache License

public ListValueSetContentsResponseType ListValueSetContents(ListValueSetContentsRequestType parameter,
        org.hibernate.Session session, String ipAddress) {
    if (logger.isInfoEnabled())
        logger.info("====== ListValueSetContents gestartet ======");

    // Return-Informationen anlegen
    ListValueSetContentsResponseType response = new ListValueSetContentsResponseType();
    response.setReturnInfos(new ReturnType());

    // Parameter prfen
    if (validateParameter(parameter, response) == false) {
        return response; // Fehler bei den Parametern
    }// w  ww .  j  a v  a  2 s  .  c om

    boolean createHibernateSession = (session == null);

    // Login-Informationen auswerten (gilt fr jeden Webservice)
    boolean loggedIn = false;
    AuthenticateInfos loginInfoType = null;
    if (parameter != null && parameter.getLoginToken() != null) {
        loginInfoType = Authorization.authenticate(ipAddress, parameter.getLoginToken());
        loggedIn = loginInfoType != null;
    }

    try {
        // Hibernate-Block, Session ffnen
        org.hibernate.Session hb_session = null;

        if (createHibernateSession) {
            hb_session = HibernateUtil.getSessionFactory().openSession();
        } else {
            hb_session = session;
        }

        //hb_session.getTransaction().begin();
        //List<CodeSystemEntity> entityList = null;//new LinkedList<CodeSystemEntity>();
        try {
            ValueSetVersion vsv = parameter.getValueSet().getValueSetVersions().iterator().next();
            long valueSetVersionId = vsv.getVersionId();

            logger.debug("valueSetVersionId: " + valueSetVersionId);

            // Zuerst passenden Level-Metadataparameter lesen
            long metadataParameter_Level_Id = 0; // 355

            if (parameter.getReadMetadataLevel() != null && parameter.getReadMetadataLevel().booleanValue()) {
                logger.debug("Finde Level...");
                String hql = "select distinct mp from MetadataParameter mp join mp.valueSet vs join vs.valueSetVersions vsv where vsv.versionId="
                        + valueSetVersionId + " and mp.paramName='Level'";
                List list = hb_session.createQuery(hql).list();

                if (list != null && list.size() > 0) {
                    MetadataParameter mp = (MetadataParameter) list.get(0);
                    metadataParameter_Level_Id = mp.getId();
                }
            }

            logger.debug("metadataParameter_Level_Id: " + metadataParameter_Level_Id);

            String sql = "select * from code_system_entity_version csev"
                    + " JOIN concept_value_set_membership cvsm ON csev.versionId=cvsm.codeSystemEntityVersionId"
                    + " JOIN code_system_concept csc ON csev.versionId=csc.codeSystemEntityVersionId"
                    + " JOIN code_system_entity cse ON csev.codeSystemEntityId=cse.id"
                    + " JOIN code_system_version_entity_membership csvem ON csvem.codeSystemEntityId=cse.id"
                    + " JOIN code_system_version csv ON csv.versionId=csvem.codeSystemVersionId";

            if (metadataParameter_Level_Id > 0)
                sql += " LEFT JOIN value_set_metadata_value vsmv ON csev.versionId=vsmv.codeSystemEntityVersionId";

            boolean virtualValueSet = false;

            ValueSetVersion vsv_db = (ValueSetVersion) hb_session.get(ValueSetVersion.class,
                    vsv.getVersionId());
            if (vsv_db == null) {
                response.getReturnInfos().setOverallErrorCategory(ReturnType.OverallErrorCategory.WARN);
                response.getReturnInfos().setStatus(ReturnType.Status.FAILURE);
                response.getReturnInfos()
                        .setMessage("Value Set with id " + vsv.getVersionId() + " does not exist.");
                return response;
            }
            if (vsv_db.getVirtualCodeSystemVersionId() != null && vsv_db.getVirtualCodeSystemVersionId() > 0) {
                // get concepts from a codesystem version
                sql = "select * from code_system_entity_version csev"
                        + " JOIN code_system_concept csc ON csev.versionId=csc.codeSystemEntityVersionId"
                        + " JOIN code_system_entity cse ON csev.codeSystemEntityId=cse.id"
                        + " JOIN code_system_version_entity_membership csvem ON csvem.codeSystemEntityId=cse.id"
                        + " JOIN code_system_version csv ON csv.versionId=csvem.codeSystemVersionId";

                virtualValueSet = true;
            }

            // Parameter dem Helper hinzufgen
            // bitte immer den Helper verwenden oder manuell Parameter per Query.setString() hinzufgen,
            // sonst sind SQL-Injections mglich
            HQLParameterHelper parameterHelper = new HQLParameterHelper();
            if (virtualValueSet == false) {
                parameterHelper.addParameter("", "cvsm.valuesetVersionId", valueSetVersionId);
            }

            if (metadataParameter_Level_Id > 0)
                parameterHelper.addParameter("", "vsmv.metadataParameterId", metadataParameter_Level_Id);

            if (loggedIn == false) {
                parameterHelper.addParameter("csev.", "statusVisibility",
                        Definitions.STATUS_CODES.ACTIVE.getCode());
            }

            if (virtualValueSet == false && vsv.getConceptValueSetMemberships() != null
                    && vsv.getConceptValueSetMemberships().size() > 0) {
                ConceptValueSetMembership cvsm = vsv.getConceptValueSetMemberships().iterator().next();
                if (cvsm.getStatusDate() != null)
                    parameterHelper.addParameter("cvsm.", "statusDate", cvsm.getStatusDate());
            }

            if (virtualValueSet) {
                parameterHelper.addParameter("csv.", "versionId", vsv_db.getVirtualCodeSystemVersionId());
            }

            if (parameter.getCodeSystemEntity() != null
                    && parameter.getCodeSystemEntity().getCodeSystemEntityVersions() != null
                    && parameter.getCodeSystemEntity().getCodeSystemEntityVersions().size() > 0) {
                CodeSystemEntityVersion csev = (CodeSystemEntityVersion) parameter.getCodeSystemEntity()
                        .getCodeSystemEntityVersions().toArray()[0];
                parameterHelper.addParameter("csev.", "statusVisibilityDate", csev.getStatusVisibilityDate());

                if (csev.getCodeSystemConcepts() != null && csev.getCodeSystemConcepts().size() > 0) {
                    CodeSystemConcept csc = (CodeSystemConcept) csev.getCodeSystemConcepts().toArray()[0];
                    parameterHelper.addParameter("csc.", "code", csc.getCode());
                    parameterHelper.addParameter("csc.", "term", csc.getTerm());
                    parameterHelper.addParameter("csc.", "termAbbrevation", csc.getTermAbbrevation());
                    parameterHelper.addParameter("csc.", "isPreferred", csc.getIsPreferred());

                    /*if (csc.getCodeSystemConceptTranslations() != null && csc.getCodeSystemConceptTranslations().size() > 0)
                    {
                      CodeSystemConceptTranslation csct = (CodeSystemConceptTranslation) csc.getCodeSystemConceptTranslations().toArray()[0];
                      parameterHelper.addParameter("csct.", "term", csct.getTerm());
                      parameterHelper.addParameter("csct.", "termAbbrevation", csct.getTermAbbrevation());
                      if (csct.getLanguageCd() != null && csct.getLanguageCd().length() > 0)
                      {
                        languageCd = csct.getLanguageCd();
                      }
                    }*/
                }
            }

            // Parameter hinzufgen (immer mit AND verbunden)
            // Gesamt-Anzahl lesen
            String where = parameterHelper.getWhere("");

            String sortStr = " ORDER BY cvsm.orderNr,csc.code";

            if (virtualValueSet)
                sortStr = " ORDER BY csc.code";

            if (parameter.getSortingParameter() != null) {
                if (parameter.getSortingParameter().getSortType() == null || parameter.getSortingParameter()
                        .getSortType() == SortingType.SortType.ALPHABETICALLY) {
                    sortStr = " ORDER BY";

                    if (parameter.getSortingParameter().getSortBy() != null
                            && parameter.getSortingParameter().getSortBy() == SortingType.SortByField.TERM) {
                        sortStr += " csc.term";
                    } else {
                        sortStr += " csc.code";
                    }

                    if (parameter.getSortingParameter().getSortDirection() != null && parameter
                            .getSortingParameter().getSortDirection() == SortingType.SortDirection.DESCENDING) {
                        sortStr += " desc";
                    }
                }
            }

            String where_all = where + sortStr;
            sql += " " + where_all;

            logger.debug("SQL: " + sql);

            // Query erstellen
            SQLQuery q = hb_session.createSQLQuery(sql);
            q.addScalar("csc.code", StandardBasicTypes.TEXT); // Index: 0
            q.addScalar("csc.term", StandardBasicTypes.TEXT);
            q.addScalar("csc.termAbbrevation", StandardBasicTypes.TEXT);
            q.addScalar("csc.description", StandardBasicTypes.TEXT);
            q.addScalar("csc.isPreferred", StandardBasicTypes.BOOLEAN);
            q.addScalar("csc.codeSystemEntityVersionId", StandardBasicTypes.LONG);

            q.addScalar("csev.effectiveDate", StandardBasicTypes.DATE); // Index: 6
            q.addScalar("csev.insertTimestamp", StandardBasicTypes.DATE);
            q.addScalar("csev.isLeaf", StandardBasicTypes.BOOLEAN);
            q.addScalar("csev.majorRevision", StandardBasicTypes.INTEGER);
            q.addScalar("csev.minorRevision", StandardBasicTypes.INTEGER);
            q.addScalar("csev.statusVisibility", StandardBasicTypes.INTEGER);
            q.addScalar("csev.statusVisibilityDate", StandardBasicTypes.DATE);
            q.addScalar("csev.versionId", StandardBasicTypes.LONG);
            q.addScalar("csev.codeSystemEntityId", StandardBasicTypes.LONG);

            q.addScalar("cse.id", StandardBasicTypes.LONG); // Index: 15
            q.addScalar("cse.currentVersionId", StandardBasicTypes.LONG);

            q.addScalar("csc.meaning", StandardBasicTypes.TEXT); //Index: 17
            q.addScalar("csc.hints", StandardBasicTypes.TEXT);

            if (virtualValueSet == false) {
                q.addScalar("cvsm.valueOverride", StandardBasicTypes.TEXT); //Index: 19
                q.addScalar("cvsm.status", StandardBasicTypes.INTEGER);
                q.addScalar("cvsm.statusDate", StandardBasicTypes.DATE);
                q.addScalar("cvsm.isStructureEntry", StandardBasicTypes.BOOLEAN);
                q.addScalar("cvsm.orderNr", StandardBasicTypes.LONG);
                q.addScalar("cvsm.description", StandardBasicTypes.TEXT);
                q.addScalar("cvsm.hints", StandardBasicTypes.TEXT);
            }

            q.addScalar("csvem.isAxis", StandardBasicTypes.BOOLEAN); // Index: 26
            q.addScalar("csvem.isMainClass", StandardBasicTypes.BOOLEAN);

            q.addScalar("csv.previousVersionID", StandardBasicTypes.LONG); // Index: 28
            q.addScalar("csv.name", StandardBasicTypes.TEXT);
            q.addScalar("csv.status", StandardBasicTypes.INTEGER);
            q.addScalar("csv.statusDate", StandardBasicTypes.DATE);
            q.addScalar("csv.releaseDate", StandardBasicTypes.DATE);
            q.addScalar("csv.expirationDate", StandardBasicTypes.DATE);
            q.addScalar("csv.source", StandardBasicTypes.TEXT);
            q.addScalar("csv.preferredLanguageCd", StandardBasicTypes.TEXT);
            q.addScalar("csv.oid", StandardBasicTypes.TEXT);
            q.addScalar("csv.licenceHolder", StandardBasicTypes.TEXT);
            q.addScalar("csv.underLicence", StandardBasicTypes.BOOLEAN);
            q.addScalar("csv.insertTimestamp", StandardBasicTypes.DATE);
            q.addScalar("csv.validityRange", StandardBasicTypes.LONG); // Index: 40

            q.addScalar("csv.versionId", StandardBasicTypes.LONG);

            if (metadataParameter_Level_Id > 0) {
                q.addScalar("vsmv.parameterValue", StandardBasicTypes.TEXT); // Index: 42
            }

            // TODO bersetzungen
            /*q.addScalar("translation_term", StandardBasicTypes.TEXT);  // Index: 17
             q.addScalar("translation_termAbbrevation", StandardBasicTypes.TEXT);
             q.addScalar("translation_languageId", StandardBasicTypes.LONG);
             q.addScalar("translation_description", StandardBasicTypes.TEXT);
             q.addScalar("translation_id", StandardBasicTypes.LONG);
                    
             */
            parameterHelper.applySQLParameter(q);
            //q.setLong("languageId", languageId);

            //+ " ORDER BY csc.code"
            //q.setParameter("codeSystemVersionId", codeSystemVersionId);

            /*List<CodeSystemConcept> conceptList = (List<CodeSystemConcept>) q.list();
                    
             for (CodeSystemConcept csc : conceptList)
             {
             logger.debug(csc.getCode());
             anzahl++;
             }*/
            response.setCodeSystemEntity(new LinkedList<CodeSystemEntity>());

            List conceptList = (List) q.list();

            logger.debug("Anzahl: " + conceptList.size());

            long lastCodeSystemEntityVersionId = 0;
            CodeSystemEntity cse;
            CodeSystemEntityVersion csev;
            CodeSystemConcept csc;
            CodeSystemVersionEntityMembership csvem;
            CodeSystemVersion csv;
            ConceptValueSetMembership cvsm;
            boolean fertig = false;
            int anzahl = 0;

            Iterator it = conceptList.iterator();

            while (it.hasNext()) {
                Object[] item = null;
                long codeSystemEntityVersionId = 0;
                do {
                    if (it.hasNext() == false) {
                        fertig = true;
                        break;
                    }

                    item = (Object[]) it.next();

                    // Prfen, ob Translation (1:N)
                    codeSystemEntityVersionId = (Long) item[5];
                    /*if (lastCodeSystemEntityVersionId == codeSystemEntityVersionId)
                     {
                     // Gleiches Konzept, Assoziation hinzufgen
                     // TODO Sprachen hinzufgen ?
                     //if (parameter.isLookForward())
                     //  addAssociationToEntityVersion(csev, item);
                     }*/
                } while (lastCodeSystemEntityVersionId == codeSystemEntityVersionId);

                if (fertig)
                    break;

                // Konzepte zusammenbauen
                cse = new CodeSystemEntity();
                csev = new CodeSystemEntityVersion();
                csc = new CodeSystemConcept();
                csvem = new CodeSystemVersionEntityMembership();
                csvem.setCodeSystemVersion(new CodeSystemVersion());
                cvsm = new ConceptValueSetMembership();
                csv = new CodeSystemVersion();

                int index = 0;

                // Konzept
                /*if (item[0] != null)
                 csc.setCode(item[0].toString());
                 if (item[1] != null)
                 csc.setTerm(item[1].toString());
                 if (item[2] != null)
                 csc.setTermAbbrevation(item[2].toString());
                 if (item[3] != null)
                 csc.setDescription(item[3].toString());
                 if (item[4] != null)
                 csc.setIsPreferred((Boolean) item[4]);
                 if (item[5] != null)
                 csc.setCodeSystemEntityVersionId((Long) item[5]);
                 if (item[17] != null)
                 csc.setMeaning(item[17].toString());
                 if (item[18] != null)
                 csc.setHints(item[18].toString());
                        
                 // Entity Version
                 if (item[6] != null)
                 csev.setEffectiveDate((Date) item[6]);
                 if (item[7] != null)
                 csev.setInsertTimestamp((Date) item[7]);
                 if (item[8] != null)
                 csev.setIsLeaf((Boolean) item[8]);
                 if (item[9] != null)
                 csev.setMajorRevision((Integer) item[9]);
                 if (item[10] != null)
                 csev.setMinorRevision((Integer) item[10]);
                 if (item[11] != null)
                 csev.setStatusVisibility((Integer) item[11]);
                 if (item[12] != null)
                 csev.setStatusVisibilityDate((Date) item[12]);
                 if (item[13] != null)
                 {
                 csev.setVersionId((Long) item[13]);
                 cvsm.setId(new ConceptValueSetMembershipId(csev.getVersionId(), valueSetVersionId));
                 }
                 // Code System Entity
                 if (item[15] != null)
                 cse.setId((Long) item[15]);
                 if (item[16] != null)
                 cse.setCurrentVersionId((Long) item[16]);
                        
                 if (virtualValueSet == false)
                 {
                 if (item[19] != null)
                 cvsm.setValueOverride(item[19].toString());
                 if (item[20] != null)
                 cvsm.setStatus((Integer) item[20]);
                 if (item[21] != null)
                 cvsm.setStatusDate((Date) item[21]);
                 if (item[22] != null)
                 cvsm.setIsStructureEntry((Boolean) item[22]);
                 if (item[23] != null)
                 cvsm.setOrderNr((Long) item[23]);
                 if (item[24] != null)
                 cvsm.setDescription(item[24].toString());
                 if (item[25] != null)
                 cvsm.setHints(item[25].toString());
                 }
                        
                 if (item[26] != null)
                 csvem.setIsAxis((Boolean) item[26]);
                 if (item[27] != null)
                 csvem.setIsMainClass((Boolean) item[27]);
                        
                 if (item[28] != null)
                 csv.setPreviousVersionId((Long) item[28]);
                 if (item[29] != null)
                 csv.setName(item[29].toString());
                 if (item[30] != null)
                 csv.setStatus((Integer) item[30]);
                 if (item[31] != null)
                 csv.setStatusDate((Date) item[31]);
                 if (item[32] != null)
                 csv.setReleaseDate((Date) item[32]);
                 if (item[33] != null)
                 csv.setExpirationDate((Date) item[33]);
                 if (item[34] != null)
                 csv.setSource(item[34].toString());
                 if (item[35] != null)
                 csv.setPreferredLanguageCd(item[35].toString());
                 if (item[36] != null)
                 csv.setOid(item[36].toString());
                 if (item[37] != null)
                 csv.setLicenceHolder(item[37].toString());
                 if (item[38] != null)
                 csv.setUnderLicence((Boolean) item[38]);
                 if (item[39] != null)
                 csv.setInsertTimestamp((Date) item[39]);
                 if (item[40] != null)
                 csv.setValidityRange((Long) item[40]);
                        
                 // Metadaten hinzufgen
                 if (item[41] != null)
                 csv.setVersionId((Long) item[41]);*/
                csc.setCode(getItemStr(item, index++));
                csc.setTerm(getItemStr(item, index++));
                csc.setTermAbbrevation(getItemStr(item, index++));
                csc.setDescription(getItemStr(item, index++));
                csc.setIsPreferred(getItemBool(item, index++));
                csc.setCodeSystemEntityVersionId(getItemLong(item, index++));

                csev.setEffectiveDate(getItemDate(item, index++));
                csev.setInsertTimestamp(getItemDate(item, index++));
                csev.setIsLeaf(getItemBool(item, index++));
                csev.setMajorRevision(getItemInt(item, index++));
                csev.setMinorRevision(getItemInt(item, index++));
                csev.setStatusVisibility(getItemInt(item, index++));
                csev.setStatusVisibilityDate(getItemDate(item, index++));
                csev.setVersionId(getItemLong(item, index++));
                if (csev.getVersionId() != null)
                    cvsm.setId(new ConceptValueSetMembershipId(csev.getVersionId(), valueSetVersionId));

                long cse_id = getItemLong(item, index++);

                // Code System Entity
                cse.setId(getItemLong(item, index++));
                cse.setCurrentVersionId(getItemLong(item, index++));

                csc.setMeaning(getItemStr(item, index++)); // Index: 17
                csc.setHints(getItemStr(item, index++));

                if (virtualValueSet == false) {
                    cvsm.setValueOverride(getItemStr(item, index++));
                    cvsm.setStatus(getItemInt(item, index++));
                    cvsm.setStatusDate(getItemDate(item, index++));
                    cvsm.setIsStructureEntry(getItemBool(item, index++));
                    cvsm.setOrderNr(getItemLong(item, index++));
                    cvsm.setDescription(getItemStr(item, index++));
                    cvsm.setHints(getItemStr(item, index++));
                }

                csvem.setIsAxis(getItemBool(item, index++));
                csvem.setIsMainClass(getItemBool(item, index++));

                csv.setPreviousVersionId(getItemLong(item, index++));
                csv.setName(getItemStr(item, index++));
                csv.setStatus(getItemInt(item, index++));
                csv.setStatusDate(getItemDate(item, index++));
                csv.setReleaseDate(getItemDate(item, index++));
                csv.setExpirationDate(getItemDate(item, index++));
                csv.setSource(getItemStr(item, index++));
                csv.setPreferredLanguageCd(getItemStr(item, index++));
                csv.setOid(getItemStr(item, index++));
                csv.setLicenceHolder(getItemStr(item, index++));
                csv.setUnderLicence(getItemBool(item, index++));
                csv.setInsertTimestamp(getItemDate(item, index++));
                csv.setValidityRange(getItemLong(item, index++));

                // Metadaten hinzufgen
                if (metadataParameter_Level_Id > 0)
                    csv.setVersionId(getItemLong(item, index++));

                // Metadaten hinzufgen
                if (item != null && item.length > 42 && item[42] != null) {
                    ValueSetMetadataValue mv = new ValueSetMetadataValue();
                    mv.setParameterValue(item[42].toString());
                    mv.setCodeSystemEntityVersion(null);
                    mv.setMetadataParameter(null);
                    csev.setValueSetMetadataValues(new HashSet<ValueSetMetadataValue>());
                    csev.getValueSetMetadataValues().add(mv);
                }

                // TODO Sprachen hinzufgen
                csvem.setCodeSystemVersion(csv);
                csev.setCodeSystemConcepts(new HashSet<CodeSystemConcept>());
                csev.getCodeSystemConcepts().add(csc);
                csev.setConceptValueSetMemberships(new HashSet<ConceptValueSetMembership>());
                csev.getConceptValueSetMemberships().add(cvsm);
                cse.setCodeSystemEntityVersions(new HashSet<CodeSystemEntityVersion>());
                cse.getCodeSystemEntityVersions().add(csev);
                cse.setCodeSystemVersionEntityMemberships(new HashSet<CodeSystemVersionEntityMembership>());
                cse.getCodeSystemVersionEntityMemberships().add(csvem);
                response.getCodeSystemEntity().add(cse);

                lastCodeSystemEntityVersionId = codeSystemEntityVersionId;

                anzahl++;
            }

            response.getReturnInfos().setCount(anzahl);

            if (response.getCodeSystemEntity() == null || anzahl == 0) {
                //response.getReturnInfos().setMessage("Zu dem angegebenen ValueSet wurden keine Konzepte gefunden!");
                response.getReturnInfos().setMessage("No concepts found for given Value Set!");
                response.getReturnInfos().setStatus(ReturnType.Status.FAILURE);
            } else {
                if (virtualValueSet)
                    response.getReturnInfos()
                            .setMessage(anzahl + " concepts successfully returned (Virtual Value Set).");
                else
                    response.getReturnInfos().setMessage(anzahl + " concepts successfully returned.");
                response.getReturnInfos().setStatus(ReturnType.Status.OK);
            }
            response.getReturnInfos().setOverallErrorCategory(ReturnType.OverallErrorCategory.INFO);
        } catch (Exception e) {

            //hb_session.getTransaction().rollback();
            // Fehlermeldung an den Aufrufer weiterleiten
            response.getReturnInfos().setOverallErrorCategory(ReturnType.OverallErrorCategory.ERROR);
            response.getReturnInfos().setStatus(ReturnType.Status.FAILURE);
            response.getReturnInfos()
                    .setMessage("Fehler bei 'ListValueSetContents', Hibernate: " + e.getLocalizedMessage());

            logger.error("Fehler in 'ListValueSetContents', Hibernate: " + e.getLocalizedMessage());
            LoggingOutput.outputException(e, this);
        } finally {
            if (createHibernateSession)
                hb_session.close();
        }
    } catch (Exception e) {
        // Fehlermeldung an den Aufrufer weiterleiten
        response.getReturnInfos().setOverallErrorCategory(ReturnType.OverallErrorCategory.ERROR);
        response.getReturnInfos().setStatus(ReturnType.Status.FAILURE);
        response.getReturnInfos().setMessage("Fehler bei 'ReturnConceptDetails': " + e.getLocalizedMessage());

        logger.error("Fehler bei 'ListValueSetContents': " + e.getLocalizedMessage());
        LoggingOutput.outputException(e, this);
    }

    return 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  w  w. j  av a  2 s . c  o m
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.
 *///from   www.j av  a2  s  .  c o m
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 //ww  w .j ava 2s .com
 */
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.
 *//*from w w  w  . j a  v  a 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;
}