Example #1
0
  /**
   * ラボモジュールを検索する。
   *
   * @param patientId 対象患者のID
   * @param firstResult 取得結果リストの最初の番号
   * @param maxResult 取得する件数の最大値
   * @return ラボモジュールのリスト
   */
  @Override
  public List<NLaboModule> getLaboTest(String fidPid, int firstResult, int maxResult) {

    // String fidPid = SessionHelper.getQualifiedPid(ctx, patientId);

    //
    // 検体採取日の降順で返す
    //
    List<NLaboModule> ret =
        (List<NLaboModule>)
            em.createQuery(QUERY_MODULE_BY_FIDPID)
                .setParameter(FIDPID, fidPid)
                .setFirstResult(firstResult)
                .setMaxResults(maxResult)
                .getResultList();

    for (NLaboModule m : ret) {

      if (m.getReportFormat() != null && m.getReportFormat().equals(WOLF)) {
        List<NLaboItem> items =
            (List<NLaboItem>)
                em.createQuery(QUERY_ITEM_BY_MID_ORDERBY_SORTKEY)
                    .setParameter(MID, m.getId())
                    .getResultList();
        m.setItems(items);

      } else {
        List<NLaboItem> items =
            (List<NLaboItem>)
                em.createQuery(QUERY_ITEM_BY_MID).setParameter(MID, m.getId()).getResultList();
        m.setItems(items);
      }
    }
    return ret;
  }
  /**
   * Deletes all Results and ResultSets of a test, selftest or survey
   *
   * @param olatRes
   * @param olatResDet
   * @param repRef
   * @return deleted ResultSets
   */
  public int deleteAllResults(Long olatRes, String olatResDet, Long repRef) {
    StringBuilder sb = new StringBuilder();
    sb.append("select rset from ").append(QTIResultSet.class.getName()).append(" as rset ");
    sb.append(
        " where rset.olatResource=:resId and rset.olatResourceDetail=:resSubPath and rset.repositoryRef=:repoKey ");

    EntityManager em = dbInstance.getCurrentEntityManager();
    List<QTIResultSet> sets =
        em.createQuery(sb.toString(), QTIResultSet.class)
            .setParameter("resId", olatRes)
            .setParameter("resSubPath", olatResDet)
            .setParameter("repoKey", repRef)
            .getResultList();

    StringBuilder delSb = new StringBuilder();
    delSb
        .append("delete from ")
        .append(QTIResult.class.getName())
        .append(" as res where res.resultSet.key=:setKey");
    Query delResults = em.createQuery(delSb.toString());
    for (QTIResultSet set : sets) {
      delResults.setParameter("setKey", set.getKey()).executeUpdate();
      em.remove(set);
    }
    return sets.size();
  }
 @Override
 public List<BluetoothDwell> getBluetoothDwellInRangeByCampaign(
     Date start, Date end, Company c, Campaign camp) {
   Query q;
   if (start == null || end == null) {
     q =
         em.createQuery(
             "SELECT bdwell FROM BluetoothDwell bdwell WHERE bdwell.company = ?1 and bdwell.campaign = ?2");
     q.setParameter(1, c);
     q.setParameter(2, camp);
   } else {
     q =
         em.createQuery(
             "SELECT bdwell FROM BluetoothDwell bdwell WHERE bdwell.company = ?1 AND bdwell.eventDate BETWEEN ?2 AND ?3 AND bdwell.campaign = ?4");
     q.setParameter(1, c);
     q.setParameter(2, start, TemporalType.TIMESTAMP);
     q.setParameter(3, end, TemporalType.TIMESTAMP);
     q.setParameter(4, camp);
   }
   List<BluetoothDwell> results = (List<BluetoothDwell>) q.getResultList();
   if (results.size() > 0) {
     return results;
   }
   return null;
 }
 protected TypedQuery<RefreshToken> getRefreshTokensQuery(
     Client c, UserSubject resourceOwnerSubject) {
   if (c == null && resourceOwnerSubject == null) {
     return entityManager.createQuery(
         "SELECT t FROM " + REFRESH_TOKEN_TABLE_NAME + " t", RefreshToken.class);
   } else if (c == null) {
     return entityManager.createQuery(
         "SELECT t FROM "
             + REFRESH_TOKEN_TABLE_NAME
             + " t JOIN t.subject s WHERE s.login = '******'",
         RefreshToken.class);
   } else if (resourceOwnerSubject == null) {
     return entityManager.createQuery(
         "SELECT t FROM "
             + REFRESH_TOKEN_TABLE_NAME
             + " t JOIN t.client c WHERE c.clientId = '"
             + c.getClientId()
             + "'",
         RefreshToken.class);
   } else {
     return entityManager.createQuery(
         "SELECT t FROM "
             + REFRESH_TOKEN_TABLE_NAME
             + " t JOIN t.subject s JOIN t.client c WHERE s.login = '******' AND c.clientId = '"
             + c.getClientId()
             + "'",
         RefreshToken.class);
   }
 }
Example #5
0
  public Page<Object[]> getCustomerConsist(String type, int pageNo, int pageSize) {

    String hql =
        "select count(id) from Customer c "
            + "where c."
            + type
            + " is not null "
            + "group by c."
            + type;

    Query query = entityManager.createQuery(hql);
    int total = query.getResultList().size();

    hql =
        "select c."
            + type
            + " , count(id) from Customer c where c."
            + type
            + " is not null "
            + "group by c."
            + type;
    query = entityManager.createQuery(hql);
    query.setFirstResult((pageNo - 1) * pageSize).setMaxResults(pageSize);
    List content = query.getResultList();

    PageRequest pageable = new PageRequest(pageNo - 1, pageSize);

    PageImpl<Object[]> page = new PageImpl<Object[]>(content, pageable, total);

    return page;
  }
  public void findAllEmployeesWithPhoneNumbers() {
    EntityManager em = createEntityManager();
    ExpressionBuilder builder = new ExpressionBuilder();
    Expression whereClause = builder.isEmpty("phoneNumbers").not();

    ReadAllQuery raq = new ReadAllQuery(Employee.class);
    raq.setSelectionCriteria(whereClause);
    raq.useDistinct();

    List expectedResult = (List) getServerSession().executeQuery(raq);

    String ejbqlString = "SELECT DISTINCT e FROM Employee e, IN (e.phoneNumbers) l";
    Query query = em.createQuery(ejbqlString);
    if (usesSOP() && getServerSession().getPlatform().isOracle()) {
      // distinct is incompatible with blob in selection clause on Oracle
      query.setHint(QueryHints.SERIALIZED_OBJECT, "false");
    }
    List firstResult = query.getResultList();

    String alternateEjbqlString = "SELECT e FROM Employee e WHERE e.phoneNumbers IS NOT EMPTY";
    List secondResult = em.createQuery(alternateEjbqlString).getResultList();
    // 14 employees returned
    Assert.assertEquals(
        "Ejbql statements returned different results: data validation error",
        firstResult.size(),
        14);
    Assert.assertTrue(
        "Equivalent Ejbql statements returned different results",
        comparer.compareObjects(secondResult, firstResult));
    Assert.assertTrue(
        "Find all employees with phone numbers test failed",
        comparer.compareObjects(expectedResult, firstResult));
  }
  public void getOrderForCustomer() {
    EntityManager em = createEntityManager();
    ExpressionBuilder builder = new ExpressionBuilder();
    Expression whereClause = builder.get("name").equal("Jane Smith");

    ReadAllQuery raq = new ReadAllQuery(Customer.class);
    raq.setSelectionCriteria(whereClause);

    Customer expectedCustomer = (Customer) (((List) getServerSession().executeQuery(raq)).get(0));
    SalesPerson salesPerson =
        ((Order) (expectedCustomer.getOrders().iterator().next())).getSalesPerson();

    String ejbqlString =
        "SELECT DISTINCT c FROM Customer c JOIN c.orders o JOIN o.salesPerson s WHERE s.id = "
            + salesPerson.getId();
    List firstResult = em.createQuery(ejbqlString).getResultList();
    String alternateEjbqlString =
        "SELECT DISTINCT c FROM Customer c, IN(c.orders) o WHERE o.salesPerson.id = "
            + salesPerson.getId();
    List secondResuslt = em.createQuery(alternateEjbqlString).getResultList();

    // only 1 order for this customer
    Assert.assertEquals(
        "Get order for customer test failed: data validation error", firstResult.size(), 1);
    Assert.assertTrue(
        "Get order for customer test failed: two equivalent ejb queries return different results",
        comparer.compareObjects(secondResuslt, firstResult));
    Assert.assertTrue(
        "Get order for customer test failed",
        comparer.compareObjects(expectedCustomer, firstResult));
  }
  public LetterModule getLetter(long letterPk) {

    LetterModule ret =
        (LetterModule)
            em.createQuery(QUERY_LETTER_BY_ID).setParameter(ID, letterPk).getSingleResult();
    // item
    List<LetterItem> items =
        (List<LetterItem>)
            em.createQuery(QUERY_ITEM_BY_ID).setParameter(ID, ret.getId()).getResultList();
    ret.setLetterItems(items);

    // text
    List<LetterText> texts =
        (List<LetterText>)
            em.createQuery(QUERY_TEXT_BY_ID).setParameter(ID, ret.getId()).getResultList();
    ret.setLetterTexts(texts);

    // date
    List<LetterDate> dates =
        (List<LetterDate>)
            em.createQuery(QUERY_DATE_BY_ID).setParameter(ID, ret.getId()).getResultList();
    ret.setLetterDates(dates);

    return ret;
  }
  public void delete(long pk) {
    List<LetterItem> itemList =
        (List<LetterItem>) em.createQuery(QUERY_ITEM_BY_ID).setParameter(ID, pk).getResultList();
    for (LetterItem item : itemList) {
      em.remove(item);
    }

    List<LetterText> textList =
        (List<LetterText>) em.createQuery(QUERY_TEXT_BY_ID).setParameter(ID, pk).getResultList();

    for (LetterText txt : textList) {
      em.remove(txt);
    }

    List<LetterDate> dateList =
        (List<LetterDate>) em.createQuery(QUERY_DATE_BY_ID).setParameter(ID, pk).getResultList();

    for (LetterDate date : dateList) {
      em.remove(date);
    }

    LetterModule delete =
        (LetterModule) em.createQuery(QUERY_LETTER_BY_ID).setParameter(ID, pk).getSingleResult();
    em.remove(delete);
  }
  @Override
  public List<Object[]> getAllStatReconCtiMsc(String x, String where) {
    // TODO Auto-generated method stub
    if (x.contains("to_char")) {
      return em.createQuery(
              "Select "
                  + x
                  + " ,SUM(nbAppelCti) From StatReconCtiMsc s where  "
                  + where
                  + "  Group By to_date(dateAppel,'YYMMDD') Order By to_date(dateAppel,'YYMMDD') ASC ")
          .getResultList();

    } else {
      return em.createQuery(
              "Select "
                  + x
                  + " ,SUM(nbAppelCti)  From StatReconCtiMsc s where   "
                  + where
                  + "  Group By "
                  + x
                  + " Order BY "
                  + x
                  + " ASC")
          .getResultList();
    }
  }
  public void changeRenderingStates() {

    ApplicationContext context =
        FacesContextUtils.getWebApplicationContext(FacesContext.getCurrentInstance());
    SaveAppointments saveAppointmentsObjectInAvaliableAppointments =
        (SaveAppointments) context.getBean("saveAppointments");

    TypedQuery<Object[]> doctorAndTimeQuery =
        em.createQuery(
            "SELECT u.doktoradi,FUNCTION('DATE',u.tarih),u.uygunrandevuid FROM Uygunrandevular AS u WHERE u.doktorid=:doctorid ORDER BY u.tarih ASC",
            Object[].class);
    doctorAndTimeQuery.setParameter(
        "doctorid",
        saveAppointmentsObjectInAvaliableAppointments.selectedAppointment.getDoktorid());
    doctorAndTimeList = new ArrayList<>();
    doctorAndTimeList = doctorAndTimeQuery.getResultList();

    TypedQuery<Randevusaatleri> query =
        em.createQuery(
            "SELECT c FROM Randevusaatleri c WHERE c.doktorid=:doctorid", Randevusaatleri.class);
    System.out.println(
        "Seçilen Randevunun ID'si"
            + saveAppointmentsObjectInAvaliableAppointments.selectedAppointment
                .getUygunrandevuid());
    query.setParameter(
        "doctorid",
        saveAppointmentsObjectInAvaliableAppointments.selectedAppointment.getDoktorid());
    appointmentClockResults = new ArrayList<>();
    appointmentClockResults = query.getResultList();

    setRenderingTakingAppointmentInfo(false);
    setRenderingClocks(true);
  }
Example #12
0
  public static void jpql(EntityManager em) {
    String jpql = "select b from oopQueryBasicBoard b";
    System.out.println(em.createQuery(jpql, Board.class).getResultList());

    jpql = "select m from oopQueryBasicMember m where titleName = 'titlename'";
    System.out.println(em.createQuery(jpql, Member.class).getResultList());
  }
 public List<Zajecia> pobierzNieObecnosciDoRaportu(
     long idStudenta, long idGrupy, long idPrzedmiotu) {
   List<Zajecia> obecnosci =
       em.createQuery("select o.zajecia from Obecnosci o where o.student.id = :idStudenta")
           .setParameter("idStudenta", idStudenta)
           .getResultList();
   List<Zajecia> nieobecnosci = new ArrayList<Zajecia>();
   if (obecnosci.size() > 0) {
     nieobecnosci =
         em.createQuery(
                 "from Zajecia as z where z.grupa = :grupaId and z.przedmiot = :przedmiotId and not z in (:obecnosci) ")
             .setParameter("grupaId", idGrupy)
             .setParameter("przedmiotId", idPrzedmiotu)
             .setParameter("obecnosci", obecnosci)
             .getResultList();
   } else {
     nieobecnosci =
         em.createQuery(
                 "from Zajecia as z where z.grupa = :grupaId and z.przedmiot = :przedmiotId")
             .setParameter("grupaId", idGrupy)
             .setParameter("przedmiotId", idPrzedmiotu)
             .getResultList();
   }
   return nieobecnosci;
 }
  public Map<String, Object> find(
      Person examplePerson, int first, int max, String[] order, boolean[] desc) {
    Map<String, Object> result = new HashMap<String, Object>(4);
    String from = "from Person e ";
    String where = "where lower(e.lastName) like :lastName ";
    String orderBy =
        order != null && order.length > 0
            ? "order by e." + order[0] + (desc[0] ? " desc" : "")
            : "";
    String lastName = examplePerson.getLastName() != null ? examplePerson.getLastName() : "";
    Query qc = entityManager.createQuery("select count(e) " + from + where);
    qc.setParameter("lastName", "%" + lastName.toLowerCase() + "%");
    long resultCount = (Long) qc.getSingleResult();
    if (max == 0) max = 36;
    Query ql = entityManager.createQuery("select e " + from + where + orderBy);
    ql.setFirstResult(first);
    ql.setMaxResults(max);
    ql.setParameter("lastName", "%" + lastName.toLowerCase() + "%");
    List<?> resultList = ql.getResultList();
    result.put("firstResult", first);
    result.put("maxResults", max);
    result.put("resultCount", resultCount);
    result.put("resultList", resultList);

    return result;
  }
 @Test
 public void createAwithB() {
   DynamicType simpleTypeA = helper.getType("SimpleA");
   assertNotNull(simpleTypeA);
   DynamicType simpleTypeB = helper.getType("SimpleB");
   assertNotNull(simpleTypeB);
   EntityManager em = emf.createEntityManager();
   assertNotNull(JpaHelper.getServerSession(emf).getDescriptorForAlias("SimpleB"));
   DynamicEntity simpleInstanceB = simpleTypeB.newDynamicEntity();
   simpleInstanceB.set("id", 1);
   simpleInstanceB.set("value1", "B2");
   DynamicEntity simpleInstanceA = simpleTypeA.newDynamicEntity();
   simpleInstanceA.set("id", 1);
   simpleInstanceA.set("value1", "A2");
   simpleInstanceA.<Collection<DynamicEntity>>get("b").add(simpleInstanceB);
   simpleInstanceB.set("a", simpleInstanceA);
   em.getTransaction().begin();
   em.persist(simpleInstanceB);
   em.persist(simpleInstanceA);
   em.getTransaction().commit();
   int simpleCountB =
       ((Number) em.createQuery("SELECT COUNT(s) FROM SimpleB s").getSingleResult()).intValue();
   assertEquals(1, simpleCountB);
   int simpleCountA =
       ((Number) em.createQuery("SELECT COUNT(s) FROM SimpleA s").getSingleResult()).intValue();
   assertEquals(1, simpleCountA);
   em.close();
 }
  /**
   * @param dsft The DatasetFieldType in which to look up a ControlledVocabularyValue.
   * @param strValue String value that may exist in a controlled vocabulary of the provided
   *     DatasetFieldType.
   * @param lenient should we accept alternate spellings for value from mapping table
   * @return The ControlledVocabularyValue found or null.
   */
  public ControlledVocabularyValue findControlledVocabularyValueByDatasetFieldTypeAndStrValue(
      DatasetFieldType dsft, String strValue, boolean lenient) {
    TypedQuery<ControlledVocabularyValue> typedQuery =
        em.createQuery(
            "SELECT OBJECT(o) FROM ControlledVocabularyValue AS o WHERE o.strValue = :strvalue AND o.datasetFieldType = :dsft",
            ControlledVocabularyValue.class);
    typedQuery.setParameter("strvalue", strValue);
    typedQuery.setParameter("dsft", dsft);
    try {
      ControlledVocabularyValue cvv = typedQuery.getSingleResult();
      return cvv;
    } catch (NoResultException | NonUniqueResultException ex) {
      if (lenient) {
        // if the value isn't found, check in the list of alternate values for this datasetFieldType
        TypedQuery<ControlledVocabAlternate> alternateQuery =
            em.createQuery(
                "SELECT OBJECT(o) FROM ControlledVocabAlternate as o WHERE o.strValue = :strvalue AND o.datasetFieldType = :dsft",
                ControlledVocabAlternate.class);
        alternateQuery.setParameter("strvalue", strValue);
        alternateQuery.setParameter("dsft", dsft);
        try {
          ControlledVocabAlternate alternateValue = alternateQuery.getSingleResult();
          return alternateValue.getControlledVocabularyValue();
        } catch (NoResultException | NonUniqueResultException ex2) {
          return null;
        }

      } else {
        return null;
      }
    }
  }
Example #17
0
  @Test
  @Transactional
  public void testCopy() {
    EntityManager em = bookDao.getEntityManager();
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Book> criteria = cb.createQuery(Book.class);

    // Fetch join
    Root<Book> root = criteria.from(Book.class);
    Path<String> path = root.join("author").<String>get("name");
    root.fetch("author");
    criteria.select(root);

    // SubQuery
    Subquery<String> sq = criteria.subquery(String.class);
    Root<Author> author = sq.from(Author.class);
    sq.select(author.<String>get("name"));
    sq.where(cb.equal(author.<String>get("name"), "Rod"));

    criteria.where(cb.in(path).value(sq));

    CriteriaQuery<Book> copy = cb.createQuery(Book.class);
    JpaUtils.copyCriteria(criteria, copy);

    List<Book> copyBooks = em.createQuery(copy).getResultList();
    List<Book> books = em.createQuery(criteria).getResultList();
    assertEquals(books, copyBooks);
  }
Example #18
0
 public static Page<Tuserinfo> findTuserinfoesByPage(String queryParam, Page<Tuserinfo> page) {
   EntityManager em = Tuserinfo.entityManager();
   String sql = "SELECT o FROM Tuserinfo o ";
   String countSql = "SELECT count(*) FROM Tuserinfo o ";
   StringBuilder whereSql = new StringBuilder(" WHERE 1=1 ");
   if (StringUtils.isNotBlank(queryParam)) {
     whereSql.append(" AND (userno like :queryParam1 OR username like :queryParam2 )");
   }
   StringBuilder orderSql = new StringBuilder(" ORDER BY ");
   if (page.isOrderBySetted()) {
     orderSql.append(" " + page.getOrderBy() + " " + page.getOrderDir());
   } else {
     orderSql.append(" userno ASC");
   }
   String tsql = sql + whereSql.toString() + orderSql.toString();
   String tCountSql = countSql + whereSql.toString();
   TypedQuery<Tuserinfo> q = em.createQuery(tsql, Tuserinfo.class);
   TypedQuery<Long> total = em.createQuery(tCountSql, Long.class);
   if (StringUtils.isNotBlank(queryParam)) {
     q.setParameter("queryParam1", "%" + queryParam + "%");
     total.setParameter("queryParam1", "%" + queryParam + "%");
     q.setParameter("queryParam2", "%" + queryParam + "%");
     total.setParameter("queryParam2", "%" + queryParam + "%");
   }
   q.setFirstResult(page.getPageIndex()).setMaxResults(page.getMaxResult());
   List<Tuserinfo> resultList = q.getResultList();
   int count = total.getSingleResult().intValue();
   page.setList(resultList);
   page.setTotalResult(count);
   return page;
 }
  public void parameterTest() {
    EntityManager em = createEntityManager();
    List employeeList = getServerSession().readAllObjects(Employee.class);
    Employee expectedEmployee = (Employee) employeeList.get(0);
    int i = 1;
    while (expectedEmployee.getPhoneNumbers().size() == 0) {
      expectedEmployee = (Employee) employeeList.get(i);
      i++;
    }
    String phoneNumber = (expectedEmployee.getPhoneNumbers().iterator().next()).getNumber();
    String ejbqlString;
    String alternateEjbqlString;
    if (usesSOP() && getServerSession().getPlatform().isOracle()) {
      // distinct is incompatible with blob in selection clause on Oracle
      ejbqlString = "SELECT e FROM Employee e, IN(e.phoneNumbers) p WHERE p.number = ?1";
      alternateEjbqlString =
          "SELECT e FROM Employee e, IN(e.phoneNumbers) p WHERE p.number = :number";
    } else {
      ejbqlString = "SELECT DISTINCT e FROM Employee e, IN(e.phoneNumbers) p WHERE p.number = ?1";
      alternateEjbqlString =
          "SELECT DISTINCT e FROM Employee e, IN(e.phoneNumbers) p WHERE p.number = :number";
    }

    List firstResult = em.createQuery(ejbqlString).setParameter(1, phoneNumber).getResultList();
    List secondResult =
        em.createQuery(alternateEjbqlString).setParameter("number", phoneNumber).getResultList();
    // random test cant duplicate
    Assert.assertTrue(
        "Parameter test failed: two equivalent ejb queries return different results",
        comparer.compareObjects(secondResult, firstResult));
    Assert.assertTrue(
        "Parameter test failed", comparer.compareObjects(expectedEmployee, firstResult));
  }
Example #20
0
 private void cleanDB() {
   EntityManager entityManager = StopTime.entityManager();
   entityManager.createQuery("DELETE FROM StopTime").executeUpdate();
   entityManager.createQuery("DELETE FROM Trip").executeUpdate();
   entityManager.createQuery("DELETE FROM Route").executeUpdate();
   entityManager.createQuery("DELETE FROM Stop").executeUpdate();
 }
  public void updateAllTest() {
    if ((JUnitTestCase.getServerSession()).getPlatform().isSymfoware()) {
      getServerSession()
          .logMessage(
              "Test updateAllTest skipped for this platform, "
                  + "Symfoware doesn't support UpdateAll/DeleteAll on multi-table objects (see rfe 298193).");
      return;
    }
    EntityManager em = createEntityManager();

    String empName = "Saunders";
    String manName = "Smitty";

    String ejbqlString = "SELECT DISTINCT e FROM Employee e WHERE e.lastName = '" + empName + "'";
    Employee employee = (Employee) em.createQuery(ejbqlString).getSingleResult();
    Address addr = em.find(Address.class, employee.getAddress().getID());

    String ejbqlString2 = "SELECT DISTINCT e FROM Employee e WHERE e.lastName = '" + manName + "'";
    Employee manager = (Employee) em.createQuery(ejbqlString2).getSingleResult();

    beginTransaction(em);

    em.createQuery("UPDATE Employee e SET e.manager = :manager " + "WHERE e.address = :addr ")
        .setParameter("manager", manager)
        .setParameter("addr", addr)
        .executeUpdate();

    commitTransaction(em);

    String ejbqlString3 =
        "SELECT DISTINCT e.manager FROM Employee e WHERE e.lastName = '" + empName + "'";
    String result = ((Employee) em.createQuery(ejbqlString3).getSingleResult()).getLastName();

    Assert.assertTrue("UpdateAll test failed", result.equals(manName));
  }
Example #22
0
  public void queryMedicalHistory4() {
    EntityManager em = emf.createEntityManager();
    Map medicals = new HashMap();
    long ssn = 0;
    EntityTransaction tran = em.getTransaction();
    tran.begin();
    String jpql = "select m from MedicalHistory2 m";
    Query q = em.createQuery(jpql);
    List<MedicalHistory2> ms = q.getResultList();
    for (MedicalHistory2 m : ms) {
      ssn = m.getId();
    }
    tran.commit();
    em.close();

    em = emf.createEntityManager();
    tran = em.getTransaction();
    tran.begin();
    jpql = "select m from MedicalHistory2 m where m.patient.ssn = " + ssn;
    q = em.createQuery(jpql);
    ms = q.getResultList();
    for (MedicalHistory2 m : ms) {
      assertMedicalHistory2(m);
    }
    tran.commit();
    em.close();

    findObj4(ssn);
  }
Example #23
0
  public Page<Object[]> getCustomerService(int pageNo, int pageSize, Date minDate, Date maxDate) {
    String hql =
        "select count(cs.serviceType) from CustomerService cs "
            + "  where cs.customer is not null And cs.serviceType is not null "
            + "  and cs.createDate between ? and ?";

    Query query = entityManager.createQuery(hql);
    query.setParameter(1, minDate);
    query.setParameter(2, maxDate);
    long total = (long) query.getSingleResult();

    hql =
        "select cs.serviceType , count(cs.serviceType) from CustomerService cs "
            + "where cs.customer is not null and cs.serviceType is not null and cs.createDate between ? and ? "
            + " group by cs.serviceType";

    query = entityManager.createQuery(hql);
    query.setParameter(1, minDate);
    query.setParameter(2, maxDate);

    query.setFirstResult((pageNo - 1) * pageSize).setMaxResults(pageSize);
    List<Object[]> content = query.getResultList();

    PageRequest pageable = new PageRequest(pageNo - 1, pageSize);

    PageImpl<Object[]> page = new PageImpl<Object[]>(content, pageable, total);

    return page;
  }
  public void testMetamodelOnClauseOverCollection() {
    EntityManager em = createEntityManager();
    Query query =
        em.createQuery("Select e from Employee e join e.phoneNumbers p on p.areaCode = '613'");
    List baseResult = query.getResultList();

    CriteriaBuilder qb = em.getCriteriaBuilder();
    CriteriaQuery<Employee> cq = qb.createQuery(Employee.class);
    Metamodel metamodel = em.getMetamodel();
    EntityType<Employee> entityEmp_ = metamodel.entity(Employee.class);
    EntityType<PhoneNumber> entityPhone_ = metamodel.entity(PhoneNumber.class);

    Root<Employee> root = cq.from(entityEmp_);
    Join phoneNumber = root.join(entityEmp_.getCollection("phoneNumbers"));
    phoneNumber.on(qb.equal(phoneNumber.get(entityPhone_.getSingularAttribute("areaCode")), "613"));
    List testResult = em.createQuery(cq).getResultList();

    clearCache();
    closeEntityManager(em);

    if (baseResult.size() != testResult.size()) {
      fail(
          "Criteria query using ON clause did not match JPQL results; "
              + baseResult.size()
              + " were expected, while criteria query returned "
              + testResult.size());
    }
  }
  private Element crearEstructuraXML() {

    Element historialNotas = new Element(ConstantesArchivosXML.SUPERPADRE);
    Query q = entityManager.createQuery(ConsultasJpql.HISTORIAL_NOTAS_ESTUDIANTES);
    @SuppressWarnings("unchecked")
    List<Usuario> usuarios = (List<Usuario>) q.getResultList();
    for (Usuario usuario : usuarios) {
      Element usuarioXML = crearXMLUsuario(usuario);
      Query q2 = entityManager.createQuery(ConsultasJpql.HISTORIAL_NOTAS);
      q2.setParameter("parametro", usuario.getId());
      @SuppressWarnings("unchecked")
      List<HistorialNotas> notasCurso = (List<HistorialNotas>) q2.getResultList();
      for (HistorialNotas curso : notasCurso) {
        Element cursoXML =
            crearXMLCurso(
                curso.getGrupoCurso().getCursoGrupo(),
                curso.getNota(),
                curso.getGrupoCurso().getIdGrupo(),
                curso.getGrupoCurso().getSemestre());
        usuarioXML.addContent(cursoXML);
      }
      historialNotas.addContent(usuarioXML);
    }
    return historialNotas;
  }
  public void testMetamodelOnClauseWithLeftJoin() {
    EntityManager em = createEntityManager();
    Query query =
        em.createQuery(
            "Select e from Employee e left join e.address a on a.city = 'Ottawa' "
                + "where a.postalCode is not null");
    List baseResult = query.getResultList();

    Metamodel metamodel = em.getMetamodel();
    EntityType<Employee> entityEmp_ = metamodel.entity(Employee.class);
    EntityType<Address> entityAddr_ = metamodel.entity(Address.class);

    CriteriaBuilder qb = em.getCriteriaBuilder();
    CriteriaQuery<Employee> cq = qb.createQuery(Employee.class);
    Root<Employee> root = cq.from(entityEmp_);
    Join address = root.join(entityEmp_.getSingularAttribute("address"), JoinType.LEFT);
    address.on(qb.equal(address.get(entityAddr_.getSingularAttribute("city")), "Ottawa"));
    cq.where(qb.isNotNull(address.get(entityAddr_.getSingularAttribute("postalCode"))));
    List testResult = em.createQuery(cq).getResultList();

    clearCache();
    closeEntityManager(em);

    if (baseResult.size() != testResult.size()) {
      fail(
          "Criteria query using ON clause with a left join did not match JPQL results; "
              + baseResult.size()
              + " were expected, while criteria query returned "
              + testResult.size());
    }
  }
  /**
   * Returns a collection of bounds for reactions included in the given's model metabolism. Note
   * that it has to be a sum of bounds of the parent model (if the given model has a parent) and the
   * bounds directly associated with current model.
   *
   * @param model
   * @return collection of bounds for the given model
   */
  Collection<EBounds> getBoundsForModel(EModel model, EntityManager em) {

    if (model.getParent() == null) {
      return model.getEBoundsCollection();
    } else {
      /*
       * There are two sources of bounds for each model.
       * First, we retrieve bounds that have been defined for this model
       */
      List<EBounds> res1 =
          em.createQuery("SELECT e FROM EBounds e WHERE e.model = :model")
              .setParameter("model", model)
              .getResultList();

      /*
       * The remaining bounds are taken from the parent model
       */
      List<EBounds> res2 =
          em.createQuery(
                  " SELECT b FROM EModel m, EBounds b WHERE "
                      + " m.id = :modelID AND m.parent IS NOT NULL AND "
                      + " b.model = m.parent AND NOT EXISTS (SELECT c "
                      + " FROM EBounds c WHERE c.model = :model AND "
                      + " b.reaction = c.reaction )")
              .setParameter("modelID", model.getId())
              .setParameter("model", model)
              .getResultList();
      res1.addAll(res2);
      return res1;
    }
  }
Example #28
0
  /**
   * Deletes a tag.
   *
   * @param tagId Tag ID
   * @param userId User ID
   */
  public void delete(String tagId, String userId) {
    EntityManager em = ThreadLocalContext.get().getEntityManager();

    // Get the tag
    Query q = em.createQuery("select t from Tag t where t.id = :id and t.deleteDate is null");
    q.setParameter("id", tagId);
    Tag tagDb = (Tag) q.getSingleResult();

    // Delete the tag
    Date dateNow = new Date();
    tagDb.setDeleteDate(dateNow);

    // Delete linked data
    q =
        em.createQuery(
            "update DocumentTag dt set dt.deleteDate = :dateNow where dt.tagId = :tagId and dt.deleteDate is not null");
    q.setParameter("dateNow", dateNow);
    q.setParameter("tagId", tagId);
    q.executeUpdate();

    q =
        em.createQuery(
            "update Acl a set a.deleteDate = :dateNow where a.sourceId = :tagId and a.deleteDate is null");
    q.setParameter("tagId", tagId);
    q.setParameter("dateNow", dateNow);
    q.executeUpdate();

    // Create audit log
    AuditLogUtil.create(tagDb, AuditLogType.DELETE, userId);
  }
  @SuppressWarnings({"unchecked"})
  private void cleanup() {
    EntityManager em = getOrCreateEntityManager();
    em.getTransaction().begin();

    for (Hoarder hoarder : (List<Hoarder>) em.createQuery("from Hoarder").getResultList()) {
      hoarder.getItems().clear();
      em.remove(hoarder);
    }

    for (Category category : (List<Category>) em.createQuery("from Category").getResultList()) {
      if (category.getExampleItem() != null) {
        category.setExampleItem(null);
        em.remove(category);
      }
    }

    for (Item item : (List<Item>) em.createQuery("from Item").getResultList()) {
      item.setCategory(null);
      em.remove(item);
    }

    em.createQuery("delete from Item").executeUpdate();

    em.getTransaction().commit();
    em.close();
  }
  @Test
  public void testQuotientAndMultiply() {
    EntityManager em = getOrCreateEntityManager();
    em.getTransaction().begin();
    CriteriaQuery<Number> criteria = builder.createQuery(Number.class);
    criteria.from(Product.class);
    criteria.select(
        builder.quot(
            builder.prod(
                builder.literal(BigDecimal.valueOf(10.0)),
                builder.literal(BigDecimal.valueOf(5.0))),
            BigDecimal.valueOf(2.0)));
    Number result = em.createQuery(criteria).getSingleResult();
    assertEquals(25.0d, result.doubleValue(), 0.1d);

    criteria.select(
        builder.prod(
            builder.quot(
                builder.literal(BigDecimal.valueOf(10.0)),
                builder.literal(BigDecimal.valueOf(5.0))),
            BigDecimal.valueOf(2.0)));
    result = em.createQuery(criteria).getSingleResult();
    assertEquals(4.0d, result.doubleValue(), 0.1d);

    em.getTransaction().commit();
    em.close();
  }