/**
* ラボモジュールを検索する。
*
* @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);
}
}
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);
}
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;
}
}
}
@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);
}
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));
}
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));
}
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);
}
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;
}
}
/**
* 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();
}
