@Override
public List<Category> getCategoryByParameters(Map<String, Object> parameters) {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Category> cq = cb.createQuery(Category.class);
Root<Category> r = cq.from(Category.class);
Join<RssUser, Category> ru = r.join("rssUserList", JoinType.LEFT);
Join<RssUser, Rss> rss = ru.join("rss", JoinType.LEFT);
Predicate p = cb.conjunction();
for (Map.Entry<String, Object> param : parameters.entrySet()) {
if (param.getKey().equals("rssId")) {
p = cb.and(p, cb.equal(rss.get("rssId"), param.getValue()));
} else if (param.getKey().equals("guid")) {
p = cb.and(p, cb.equal(r.get(param.getKey()), param.getValue()));
}
}
p = cb.or(p, cb.equal(r.get("categoryId"), DEFAULT_CATEGORY_ID));
cq.distinct(true);
cq.multiselect().where(p);
TypedQuery typedQuery = em.createQuery(cq);
List<Category> resultList = typedQuery.getResultList();
return resultList;
}
@Override
public Collection<ProductListItem> findByParams(Map<String, Object> params) {
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<ProductListItem> criteriaQuery =
criteriaBuilder.createQuery(ProductListItem.class);
Root<Product> enityRoot = criteriaQuery.from(Product.class);
Predicate criteria = buildCriteria(criteriaBuilder, enityRoot, params);
Join<Object, Object> categoryJoin = enityRoot.join("category", JoinType.LEFT);
criteriaQuery
.multiselect(
enityRoot.<Long>get("id"),
enityRoot.<String>get("name"),
categoryJoin.<String>get("name"),
enityRoot.<String>get("producer"),
enityRoot.<BigDecimal>get("price"),
enityRoot.<Calendar>get("createDate"))
.where(criteria);
String orderDir = (String) params.get("order_dir");
if (orderDir == null) {
orderDir = "desc";
}
String orderCol = (String) params.get("order_col");
if (orderCol == null) {
orderCol = "id";
}
Path<Object> col = null;
if (orderCol.equals("category_name")) {
col = categoryJoin.get("name");
} else {
col = enityRoot.get(orderCol);
}
if (orderDir.equals("asc")) {
criteriaQuery.orderBy(criteriaBuilder.asc(col));
} else if (orderDir.equals("desc")) {
criteriaQuery.orderBy(criteriaBuilder.desc(col));
}
TypedQuery<ProductListItem> query = entityManager.createQuery(criteriaQuery);
Integer pageSize = (Integer) params.get("page_size");
Integer pageIndex = (Integer) params.get("page_index");
if (pageSize != null && pageIndex != null) {
query.setMaxResults(pageSize);
query.setFirstResult(pageIndex * pageSize);
}
return query.getResultList();
}
@Test
public void testInvalidTupleIndexAccess() {
EntityManager em = entityManagerFactory().createEntityManager();
em.getTransaction().begin();
Customer c1 = new Customer();
c1.setId("c1");
c1.setAge(18);
c1.setName("Bob");
em.persist(c1);
em.getTransaction().commit();
em.close();
// the actual assertion block
em = entityManagerFactory().createEntityManager();
em.getTransaction().begin();
final CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Tuple> criteria = builder.createTupleQuery();
Root<Customer> customerRoot = criteria.from(Customer.class);
criteria.multiselect(customerRoot.get(Customer_.name), customerRoot.get(Customer_.age));
List<Tuple> results = em.createQuery(criteria).getResultList();
assertEquals(1, results.size());
Tuple tuple = results.get(0);
try {
tuple.get(99);
fail("99 is invalid index");
} catch (IllegalArgumentException expected) {
}
try {
tuple.get(99, String.class);
fail("99 is invalid index");
} catch (IllegalArgumentException expected) {
}
tuple.get(0, String.class);
tuple.get(1, Integer.class);
try {
tuple.get(0, java.util.Date.class);
fail("Date is invalid type");
} catch (IllegalArgumentException expected) {
}
em.getTransaction().commit();
em.close();
em = entityManagerFactory().createEntityManager();
em.getTransaction().begin();
em.createQuery("delete Customer").executeUpdate();
em.getTransaction().commit();
em.close();
}
@Override
public List<T> findAllBySelectInputs(List<String> stringsOfSelect) {
CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<T> query = builder.createQuery(getDomainClass());
Root<T> c = query.from(getDomainClass());
List<Selection<?>> inputs = new ArrayList<Selection<?>>();
for (String select : stringsOfSelect) {
inputs.add(c.get(select));
}
query.multiselect(inputs);
List<T> results = em.createQuery(query).getResultList();
return results;
}
public List<Object[]> findPurchaseList(Date beginDate, Date endDate, Integer count) {
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Object[]> criteriaQuery = criteriaBuilder.createQuery(Object[].class);
Root<Member> member = criteriaQuery.from(Member.class);
Join<Product, Order> orders = member.join("orders");
criteriaQuery.multiselect(
member.get("id"),
member.get("username"),
member.get("email"),
member.get("point"),
member.get("amount"),
member.get("balance"),
criteriaBuilder.sum(orders.<BigDecimal>get("amountPaid")));
Predicate restrictions = criteriaBuilder.conjunction();
if (beginDate != null) {
restrictions =
criteriaBuilder.and(
restrictions,
criteriaBuilder.greaterThanOrEqualTo(orders.<Date>get("createDate"), beginDate));
}
if (endDate != null) {
restrictions =
criteriaBuilder.and(
restrictions,
criteriaBuilder.lessThanOrEqualTo(orders.<Date>get("createDate"), endDate));
}
restrictions =
criteriaBuilder.and(
restrictions,
criteriaBuilder.equal(orders.get("orderStatus"), Order.OrderStatus.completed),
criteriaBuilder.equal(orders.get("paymentStatus"), Order.PaymentStatus.paid));
criteriaQuery.where(restrictions);
criteriaQuery.groupBy(
member.get("id"),
member.get("username"),
member.get("email"),
member.get("point"),
member.get("amount"),
member.get("balance"));
criteriaQuery.orderBy(
criteriaBuilder.desc(criteriaBuilder.sum(orders.<BigDecimal>get("amountPaid"))));
TypedQuery<Object[]> query =
entityManager.createQuery(criteriaQuery).setFlushMode(FlushModeType.COMMIT);
if (count != null && count >= 0) {
query.setMaxResults(count);
}
return query.getResultList();
}
@Override
protected CriteriaQuery<Object[]> getListQuery() {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Object[]> cq = cb.createQuery(Object[].class);
Root<OutgoingDocument> root = cq.from(OutgoingDocument.class);
cq.multiselect(
root.get(OutgoingDocument_.id),
root.get(OutgoingDocument_.regDate),
root.get(OutgoingDocument_.regNumber),
root.get(OutgoingDocument_.sendDate),
root.get(OutgoingDocument_.description),
root.get(OutgoingDocument_.registratorEmployee).get(Employee_.lastName));
cq.orderBy(cb.desc(root.get(OutgoingDocument_.regDate)));
cq.where(getPredicates(cb, root));
return cq;
}
@Test
public void testVariousTupleAccessMethods() {
EntityManager em = entityManagerFactory().createEntityManager();
em.getTransaction().begin();
Customer c1 = new Customer();
c1.setId("c1");
c1.setAge(18);
c1.setName("Bob");
em.persist(c1);
em.getTransaction().commit();
em.close();
em = entityManagerFactory().createEntityManager();
em.getTransaction().begin();
final CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Tuple> criteria = builder.createTupleQuery();
Root<Customer> customerRoot = criteria.from(Customer.class);
Path<String> namePath = customerRoot.get(Customer_.name);
namePath.alias("NAME");
Path<Integer> agePath = customerRoot.get(Customer_.age);
agePath.alias("AGE");
criteria.multiselect(namePath, agePath);
List<Tuple> results = em.createQuery(criteria).getResultList();
Tuple tuple = results.get(0);
assertNotNull(tuple);
assertNotNull(tuple.get("NAME"));
assertNotNull(tuple.get("NAME", String.class));
try {
tuple.get("NAME", Date.class);
fail("Accessing Customer#name tuple as Date should have thrown exception");
} catch (IllegalArgumentException expected) {
}
em.getTransaction().commit();
em.close();
em = entityManagerFactory().createEntityManager();
em.getTransaction().begin();
em.createQuery("delete Customer").executeUpdate();
em.getTransaction().commit();
em.close();
}
@Test
public void testTuple() {
EntityManager em = entityManagerFactory().createEntityManager();
em.getTransaction().begin();
Customer c1 = new Customer();
c1.setId("c1");
c1.setAge(18);
c1.setName("Bob");
em.persist(c1);
em.getTransaction().commit();
em.close();
em = entityManagerFactory().createEntityManager();
em.getTransaction().begin();
final CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Tuple> criteria = builder.createTupleQuery();
Root<Customer> customerRoot = criteria.from(Customer.class);
Path<String> namePath = customerRoot.get(Customer_.name);
Path<Integer> agePath = customerRoot.get(Customer_.age);
agePath.alias("age");
criteria.multiselect(namePath, agePath);
List<Tuple> results = em.createQuery(criteria).getResultList();
assertEquals(1, results.size());
Object resultElement = results.get(0);
assertTrue("Check result 'row' as Tuple", Tuple.class.isInstance(resultElement));
Tuple resultElementTuple = (Tuple) resultElement;
Object[] tupleArray = resultElementTuple.toArray();
assertEquals(2, tupleArray.length);
assertEquals(tupleArray[0], resultElementTuple.get(0));
assertEquals(resultElementTuple.get(namePath), resultElementTuple.get(0));
assertEquals(tupleArray[1], resultElementTuple.get(1));
assertEquals(resultElementTuple.get(agePath), resultElementTuple.get(1));
assertEquals(resultElementTuple.get(agePath), resultElementTuple.get("age"));
em.getTransaction().commit();
em.close();
em = entityManagerFactory().createEntityManager();
em.getTransaction().begin();
em.createQuery("delete Customer").executeUpdate();
em.getTransaction().commit();
em.close();
}
@Test
public void testIllegalArgumentExceptionBuildingTupleWithSameAliases() {
EntityManager em = entityManagerFactory().createEntityManager();
em.getTransaction().begin();
final CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Tuple> criteria = builder.createTupleQuery();
Root<Customer> customerRoot = criteria.from(Customer.class);
Path<String> namePath = customerRoot.get(Customer_.name);
namePath.alias("age");
Path<Integer> agePath = customerRoot.get(Customer_.age);
agePath.alias("age");
try {
criteria.multiselect(namePath, agePath);
fail(
"Attempt to define multi-select with same aliases should have thrown IllegalArgumentException");
} catch (IllegalArgumentException expected) {
}
em.getTransaction().commit();
em.close();
}
@Override
public List<String> getImageAlbums(int firstResult, int maxResults, Map<String, String> sorts) {
// From Query
CriteriaQuery<String> fromQuery = this.builder.createQuery(String.class);
Root<ImageImpl> images = fromQuery.from(ImageImpl.class);
// Select Query
CriteriaQuery<String> selectQuery = fromQuery.multiselect(images.get("album"));
selectQuery.distinct(true);
if (sorts != null) {
List<Order> orders = new ArrayList<>();
for (Map.Entry<String, String> entry : sorts.entrySet()) {
String field = entry.getKey();
String sortType = entry.getValue();
if (field != null && sortType != null) {
if (field.equals("album")) {
if (sortType.equalsIgnoreCase("asc")) {
orders.add(this.builder.asc(images.get(field)));
} else if (sortType.equalsIgnoreCase("desc")) {
orders.add(this.builder.asc(images.get(field)));
}
}
}
}
selectQuery.orderBy(orders);
}
// Final Query
TypedQuery<String> typedQuery = this.entityManager.createQuery(selectQuery);
// First Result
if (firstResult >= 0) {
typedQuery = typedQuery.setFirstResult(firstResult);
}
// Max Results
if (maxResults >= 0) {
typedQuery = typedQuery.setMaxResults(maxResults);
}
return typedQuery.getResultList();
}
public List<BookPresentationInformation> updateBookPresentationPriceForBooksWithMultipleFormats(
int numberOfFormats, double amount) {
EntityManager entityManager = Utilities.getEntityManagerFactory().createEntityManager();
EntityTransaction entityTransaction = null;
try {
entityTransaction = entityManager.getTransaction();
entityTransaction.begin();
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Book> criteriaQueryBook = criteriaBuilder.createQuery(Book.class);
Root<Book> rootBook = criteriaQueryBook.from(Book.class);
criteriaQueryBook.select(rootBook);
CriteriaQuery<BookPresentation> criteriaQueryBookPresentation =
criteriaBuilder.createQuery(BookPresentation.class);
Subquery<Long> subqueryBookPresentation = criteriaQueryBookPresentation.subquery(Long.class);
Root<BookPresentation> rootBookPresentation =
subqueryBookPresentation.from(BookPresentation.class);
subqueryBookPresentation.select(
criteriaBuilder.count(rootBookPresentation.get(BookPresentation_.id)));
subqueryBookPresentation.where(
criteriaBuilder.equal(
rootBookPresentation.get(BookPresentation_.book), rootBook.get(Book_.id)));
criteriaQueryBook.where(criteriaBuilder.gt(subqueryBookPresentation, numberOfFormats));
TypedQuery<Book> typedQueryBook = entityManager.createQuery(criteriaQueryBook);
List<Book> booksList = typedQueryBook.getResultList();
entityManager
.createNativeQuery(
"CREATE TABLE IF NOT EXISTS bookstore.book_tmp ( "
+ "book_id INT(10) UNSIGNED NOT NULL "
+ ");",
Book.class)
.executeUpdate();
for (Book book : booksList) {
entityManager
.createNativeQuery(
"INSERT INTO bookstore.book_tmp VALUES (" + book.getId() + ");", Book.class)
.executeUpdate();
}
entityManager
.createNativeQuery(
"UPDATE bookstore.book_presentation SET price = price * :amount WHERE book_id IN (SELECT book_id FROM bookstore.book_tmp)",
Book.class)
.setParameter("amount", amount)
.executeUpdate();
CriteriaQuery<BookPresentationInformation> criteriaQueryBookPresentationInformation =
criteriaBuilder.createQuery(BookPresentationInformation.class);
Root<BookPresentation> root =
criteriaQueryBookPresentationInformation.from(BookPresentation.class);
Join<BookPresentation, Book> joinBookPresentation = root.join(BookPresentation_.book);
Join<BookPresentation, Format> joinFormat = root.join(BookPresentation_.format);
criteriaQueryBookPresentationInformation.multiselect(
root.get(BookPresentation_.id),
joinBookPresentation.get(Book_.id),
joinFormat.get(Format_.id),
root.get(BookPresentation_.price));
criteriaQueryBookPresentationInformation.where(
root.get(BookPresentation_.book).in(booksList));
TypedQuery<BookPresentationInformation> typedQueryBookPresentationInformation =
entityManager.createQuery(criteriaQueryBookPresentationInformation);
List<BookPresentationInformation> bookPresentationInformationList =
typedQueryBookPresentationInformation.getResultList();
entityManager.createNativeQuery("DROP TABLE bookstore.book_tmp;", Book.class).executeUpdate();
entityTransaction.commit();
return bookPresentationInformationList;
} catch (Exception exception) {
System.out.println("An exception has occurred: " + exception.getMessage());
if (Constants.DEBUG) {
exception.printStackTrace();
}
entityTransaction.rollback();
} finally {
entityManager.close();
}
return null;
}
public static <T> List<T> queryEntities(EntityManager em, Class<T> entity, QueryParameters q) {
if (q == null)
throw new IllegalArgumentException(
"Query parameters can't be null. "
+ "If you don't have any parameters either pass a empty object or "
+ "use the queryEntities(EntityManager, Class<T>) method.");
log.finest("Querying entity: '" + entity.getSimpleName() + "' with parameters: " + q);
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<T> cq = cb.createQuery(entity);
CriteriaQuery<Tuple> ct = cb.createTupleQuery();
Root<T> r = cq.from(entity);
Root<T> rt = ct.from(entity);
if (!q.getFilters().isEmpty()) {
Predicate whereQuery = createWhereQuery(cb, r, q);
Predicate whereQueryTuple = createWhereQuery(cb, rt, q);
cq.where(whereQuery);
ct.where(whereQueryTuple);
}
if (!q.getOrder().isEmpty()) {
List<Order> orders = createOrderQuery(cb, r, q);
List<Order> ordersTuple = createOrderQuery(cb, rt, q);
cq.orderBy(orders);
ct.orderBy(ordersTuple);
}
cq.select(r);
ct.multiselect(createFieldsSelect(rt, q, getEntityIdField(em, entity)));
TypedQuery<T> tq = em.createQuery(cq);
TypedQuery<Tuple> tqt = em.createQuery(ct);
if (q.getLimit() != null && q.getLimit() > -1) {
tq.setMaxResults(q.getLimit().intValue());
tqt.setMaxResults(q.getLimit().intValue());
}
if (q.getOffset() != null && q.getOffset() > -1) {
tq.setFirstResult(q.getOffset().intValue());
tqt.setFirstResult(q.getOffset().intValue());
}
if (q.getFields().isEmpty()) {
return tq.getResultList();
} else {
return createEntityFromTuple(tqt.getResultList(), entity);
}
}
