use of javax.persistence.criteria.Join in project hibernate-orm by hibernate.
the class CorrelatedSubqueryTest method testCorrelatedJoinsFromSubquery.
@Test
@TestForIssue(jiraKey = "HHH-8556")
public void testCorrelatedJoinsFromSubquery() {
CriteriaBuilder builder = entityManagerFactory().getCriteriaBuilder();
CriteriaQuery<Customer> cquery = builder.createQuery(Customer.class);
Root<Customer> customer = cquery.from(Customer.class);
cquery.select(customer);
Subquery<Order> sq = cquery.subquery(Order.class);
Join<Customer, Order> sqo = sq.correlate(customer.join(Customer_.orders));
sq.select(sqo);
Set<Join<?, ?>> cJoins = sq.getCorrelatedJoins();
// ensure the join is returned in #getCorrelatedJoins
assertEquals(cJoins.size(), 1);
}
use of javax.persistence.criteria.Join in project hibernate-orm by hibernate.
the class QueryStructure method renderJoins.
@SuppressWarnings({ "unchecked" })
private void renderJoins(StringBuilder jpaqlQuery, RenderingContext renderingContext, Collection<Join<?, ?>> joins) {
if (joins == null) {
return;
}
for (Join join : joins) {
((FromImplementor) join).prepareAlias(renderingContext);
jpaqlQuery.append(renderJoinType(join.getJoinType())).append(((FromImplementor) join).renderTableExpression(renderingContext));
renderJoins(jpaqlQuery, renderingContext, join.getJoins());
renderFetches(jpaqlQuery, renderingContext, join.getFetches());
}
}
use of javax.persistence.criteria.Join in project microservices by pwillhan.
the class Grouping method executeQueries.
@Test
public void executeQueries() throws Exception {
storeTestData();
CriteriaBuilder cb = JPA.getEntityManagerFactory().getCriteriaBuilder();
UserTransaction tx = TM.getUserTransaction();
try {
tx.begin();
EntityManager em = JPA.createEntityManager();
{
// Group
CriteriaQuery criteria = cb.createQuery();
Root<User> u = criteria.from(User.class);
criteria.multiselect(u.get("lastname"), cb.count(u));
criteria.groupBy(u.get("lastname"));
Query q = em.createQuery(criteria);
List<Object[]> result = q.getResultList();
assertEquals(result.size(), 2);
for (Object[] row : result) {
assertTrue(row[0] instanceof String);
assertTrue(row[1] instanceof Long);
}
}
em.clear();
{
// Average
CriteriaQuery criteria = cb.createQuery();
Root<Bid> b = criteria.from(Bid.class);
criteria.multiselect(b.get("item").get("name"), cb.avg(b.<BigDecimal>get("amount")));
criteria.groupBy(b.get("item").get("name"));
Query q = em.createQuery(criteria);
List<Object[]> result = q.getResultList();
assertEquals(result.size(), 2);
for (Object[] row : result) {
assertTrue(row[0] instanceof String);
assertTrue(row[1] instanceof Double);
}
}
em.clear();
{
// Average Workaround
CriteriaQuery criteria = cb.createQuery();
Root<Bid> b = criteria.from(Bid.class);
Join<Bid, Item> i = b.join("item");
criteria.multiselect(i, cb.avg(b.<BigDecimal>get("amount")));
criteria.groupBy(i.get("id"), i.get("name"), i.get("createdOn"), i.get("auctionEnd"), i.get("auctionType"), i.get("approved"), i.get("buyNowPrice"), i.get("seller"));
Query q = em.createQuery(criteria);
List<Object[]> result = q.getResultList();
assertEquals(result.size(), 2);
for (Object[] row : result) {
assertTrue(row[0] instanceof Item);
assertTrue(row[1] instanceof Double);
}
}
em.clear();
{
// Having
CriteriaQuery criteria = cb.createQuery();
Root<User> u = criteria.from(User.class);
criteria.multiselect(u.get("lastname"), cb.count(u));
criteria.groupBy(u.get("lastname"));
criteria.having(cb.like(u.<String>get("lastname"), "D%"));
Query q = em.createQuery(criteria);
List<Object[]> result = q.getResultList();
assertEquals(result.size(), 1);
for (Object[] row : result) {
assertTrue(row[0] instanceof String);
assertTrue(row[1] instanceof Long);
}
}
em.clear();
tx.commit();
em.close();
} finally {
TM.rollback();
}
}
use of javax.persistence.criteria.Join in project microservices by pwillhan.
the class Joins method executeQueries.
@Test
public void executeQueries() throws Exception {
TestDataCategoriesItems testData = storeTestData();
CriteriaBuilder cb = JPA.getEntityManagerFactory().getCriteriaBuilder();
UserTransaction tx = TM.getUserTransaction();
try {
tx.begin();
EntityManager em = JPA.createEntityManager();
{
// Implicit inner join
CriteriaQuery criteria = cb.createQuery();
Root<Bid> b = criteria.from(Bid.class);
criteria.select(b).where(cb.like(b.get("item").<String>get("name"), "Fo%"));
Query q = em.createQuery(criteria);
List<Bid> result = q.getResultList();
assertEquals(result.size(), 3);
for (Bid bid : result) {
assertEquals(bid.getItem().getId(), testData.items.getFirstId());
}
}
em.clear();
{
// Multiple inner
CriteriaQuery<Bid> criteria = cb.createQuery(Bid.class);
Root<Bid> b = criteria.from(Bid.class);
criteria.select(b).where(cb.equal(b.get("item").get("seller").get("username"), "johndoe"));
Query q = em.createQuery(criteria);
List<Bid> result = q.getResultList();
assertEquals(result.size(), 4);
}
em.clear();
{
// Multiple inner
CriteriaQuery criteria = cb.createQuery();
Root<Bid> b = criteria.from(Bid.class);
criteria.select(b).where(cb.and(cb.equal(b.get("item").get("seller").get("username"), "johndoe"), cb.isNotNull(b.get("item").get("buyNowPrice"))));
Query q = em.createQuery(criteria);
List<Bid> result = q.getResultList();
assertEquals(result.size(), 3);
}
em.clear();
{
// Explicit inner
CriteriaQuery criteria = cb.createQuery();
Root<Item> i = criteria.from(Item.class);
Join<Item, Bid> b = i.join("bids");
criteria.select(i).where(cb.gt(b.<BigDecimal>get("amount"), new BigDecimal(100)));
Query q = em.createQuery(criteria);
List<Item> result = q.getResultList();
assertEquals(result.size(), 1);
assertEquals(result.get(0).getId(), testData.items.getFirstId());
}
em.clear();
{
// Explicit outer
CriteriaQuery criteria = cb.createQuery();
Root<Item> i = criteria.from(Item.class);
Join<Item, Bid> b = i.join("bids", JoinType.LEFT);
b.on(cb.gt(b.<BigDecimal>get("amount"), new BigDecimal(100)));
criteria.multiselect(i, b);
Query q = em.createQuery(criteria);
List<Object[]> result = q.getResultList();
assertEquals(result.size(), 3);
assertTrue(result.get(0)[0] instanceof Item);
assertTrue(result.get(0)[1] instanceof Bid);
assertTrue(result.get(1)[0] instanceof Item);
assertEquals(result.get(1)[1], null);
assertTrue(result.get(2)[0] instanceof Item);
assertEquals(result.get(2)[1], null);
}
em.clear();
{
// Explicit right outer
/* TODO Right outer joins not supported in criteria, Hibernate bug JPA-2
CriteriaQuery criteria = cb.createQuery();
Root<Bid> b = criteria.from(Bid.class);
Join<Bid, Item> i = b.join("item", JoinType.RIGHT);
criteria.multiselect(b, i).where(
cb.or(
cb.isNull(b),
cb.gt(b.<BigDecimal>get("amount"), new BigDecimal(100)))
);
Query q = em.createQuery(criteria);
List<Object[]> result = q.getResultList();
assertEquals(result.size(), 2);
assertTrue(result.get(0)[0] instanceof Bid);
assertTrue(result.get(0)[1] instanceof Item);
assertEquals(result.get(1)[0], null);
assertTrue(result.get(1)[1] instanceof Item);
*/
}
em.clear();
{
// A typical "fetch the bids collections for all items as a side effect" query
CriteriaQuery criteria = cb.createQuery();
Root<Item> i = criteria.from(Item.class);
i.fetch("bids", JoinType.LEFT);
criteria.select(i);
Query q = em.createQuery(criteria);
List<Item> result = q.getResultList();
// 3 items, 4 bids, 5 "rows" in result!
assertEquals(result.size(), 5);
// In-memory "distinct"
Set<Item> distinctResult = new LinkedHashSet<Item>(result);
// It was only three items all along...
assertEquals(distinctResult.size(), 3);
boolean haveBids = false;
for (Item item : distinctResult) {
// No more lazy loading!
em.detach(item);
if (item.getBids().size() > 0) {
haveBids = true;
break;
}
}
assertTrue(haveBids);
}
em.clear();
{
// Hibernate can remove the duplicate SQL resultset rows for you
// (...who knows, some of you actually WANT the duplicates!)
CriteriaQuery criteria = cb.createQuery();
Root<Item> i = criteria.from(Item.class);
i.fetch("bids", JoinType.LEFT);
criteria.select(i).distinct(true);
Query q = em.createQuery(criteria);
List<Item> result = q.getResultList();
// Hibernate filtered it for us in-memory
assertEquals(result.size(), 3);
boolean haveBids = false;
for (Item item : result) {
// No more lazy loading!
em.detach(item);
if (item.getBids().size() > 0) {
haveBids = true;
break;
}
}
assertTrue(haveBids);
}
em.clear();
{
// Fetch multiple associated instances/collections
CriteriaQuery criteria = cb.createQuery();
Root<Item> i = criteria.from(Item.class);
Fetch<Item, Bid> b = i.fetch("bids", JoinType.LEFT);
// These are non-nullable foreign key columns, inner join or
b.fetch("bidder");
// outer doesn't make a difference!
i.fetch("seller", JoinType.LEFT);
criteria.select(i).distinct(true);
Query q = em.createQuery(criteria);
List<Item> result = q.getResultList();
assertEquals(result.size(), 2);
boolean haveBids = false;
boolean haveBidder = false;
boolean haveSeller = false;
for (Item item : result) {
// No more lazy loading!
em.detach(item);
if (item.getBids().size() > 0) {
haveBids = true;
Bid bid = item.getBids().iterator().next();
if (bid.getBidder() != null && bid.getBidder().getUsername() != null) {
haveBidder = true;
}
}
if (item.getSeller() != null && item.getSeller().getUsername() != null)
haveSeller = true;
}
assertTrue(haveBids);
assertTrue(haveBidder);
assertTrue(haveSeller);
}
em.clear();
{
// SQL Cartesian product of multiple collections! Bad!
CriteriaQuery criteria = cb.createQuery();
Root<Item> i = criteria.from(Item.class);
i.fetch("bids", JoinType.LEFT);
// Cartesian product, bad!
i.fetch("images", JoinType.LEFT);
criteria.select(i).distinct(true);
Query q = em.createQuery(criteria);
List<Item> result = q.getResultList();
assertEquals(result.size(), 3);
boolean haveBids = false;
boolean haveImages = false;
for (Item item : result) {
// No more lazy loading!
em.detach(item);
if (item.getBids().size() > 0)
haveBids = true;
if (item.getImages().size() > 0)
haveImages = true;
}
assertTrue(haveBids);
assertTrue(haveImages);
}
em.clear();
{
// Theta style inner join
CriteriaQuery criteria = cb.createQuery();
Root<User> u = criteria.from(User.class);
Root<LogRecord> log = criteria.from(LogRecord.class);
criteria.where(cb.equal(u.get("username"), log.get("username")));
criteria.multiselect(u, log);
Query q = em.createQuery(criteria);
List<Object[]> result = q.getResultList();
for (Object[] row : result) {
assertTrue(row[0] instanceof User);
assertTrue(row[1] instanceof LogRecord);
}
assertEquals(result.size(), 2);
}
em.clear();
{
// Theta style inner join, multiple
CriteriaQuery criteria = cb.createQuery();
Root<Item> i = criteria.from(Item.class);
Root<Bid> b = criteria.from(Bid.class);
criteria.where(cb.equal(b.get("item"), i), cb.equal(i.get("seller"), b.get("bidder")));
criteria.multiselect(i, b);
Query q = em.createQuery(criteria);
List<Object[]> result = q.getResultList();
assertEquals(result.size(), 0);
}
em.clear();
{
// Inner join with implicit ID comparison
CriteriaQuery criteria = cb.createQuery();
Root<Item> i = criteria.from(Item.class);
Root<User> u = criteria.from(User.class);
criteria.where(cb.equal(i.get("seller"), u), cb.like(u.<String>get("username"), "j%"));
criteria.multiselect(i, u);
Query q = em.createQuery(criteria);
List<Object[]> result = q.getResultList();
assertEquals(result.size(), 3);
for (Object[] row : result) {
assertTrue(row[0] instanceof Item);
assertTrue(row[1] instanceof User);
}
}
em.clear();
{
// Inner join with explicit ID comparison
CriteriaQuery criteria = cb.createQuery();
Root<Item> i = criteria.from(Item.class);
Root<User> u = criteria.from(User.class);
criteria.where(cb.equal(i.get("seller").get("id"), u.get("id")), cb.like(u.<String>get("username"), "j%"));
criteria.multiselect(i, u);
Query q = em.createQuery(criteria);
List<Object[]> result = q.getResultList();
assertEquals(result.size(), 3);
for (Object[] row : result) {
assertTrue(row[0] instanceof Item);
assertTrue(row[1] instanceof User);
}
}
em.clear();
{
// Binding an entity parameter
CriteriaQuery criteria = cb.createQuery();
Root<Item> i = criteria.from(Item.class);
criteria.where(cb.equal(i.get("seller"), cb.parameter(User.class, "seller")));
criteria.select(i);
Query q = em.createQuery(criteria);
User someUser = em.find(User.class, testData.users.getFirstId());
q.setParameter("seller", someUser);
List<Item> result = q.getResultList();
assertEquals(result.size(), 2);
}
em.clear();
{
// Binding ID parameter
CriteriaQuery criteria = cb.createQuery();
Root<Item> i = criteria.from(Item.class);
criteria.where(cb.equal(i.get("seller").get("id"), cb.parameter(Long.class, "sellerId")));
criteria.select(i);
Query q = em.createQuery(criteria);
Long USER_ID = testData.users.getFirstId();
q.setParameter("sellerId", USER_ID);
List<Item> result = q.getResultList();
assertEquals(result.size(), 2);
}
em.clear();
{
// Not a join, just ID comparison
CriteriaQuery<Bid> criteria = cb.createQuery(Bid.class);
Root<Bid> b = criteria.from(Bid.class);
criteria.where(cb.equal(b.get("item").get("id"), cb.parameter(Long.class, "itemId")));
criteria.select(b);
Query q = em.createQuery(criteria);
q.setParameter("itemId", testData.items.getFirstId());
List<Bid> result = q.getResultList();
assertEquals(result.size(), 3);
}
em.clear();
tx.commit();
em.close();
} finally {
TM.rollback();
}
}
use of javax.persistence.criteria.Join in project microservices by pwillhan.
the class Typesafe method executeQueries.
@Test
public void executeQueries() throws Exception {
TestDataCategoriesItems testData = storeTestData();
CriteriaBuilder cb = JPA.getEntityManagerFactory().getCriteriaBuilder();
UserTransaction tx = TM.getUserTransaction();
try {
tx.begin();
EntityManager em = JPA.createEntityManager();
{
// Typesafe path navigation
CriteriaQuery<User> criteria = cb.createQuery(User.class);
Root<User> u = criteria.from(User.class);
criteria.select(u).where(cb.equal(u.get(User_.homeAddress).get(Address_.city), "Some City"));
TypedQuery<User> q = em.createQuery(criteria);
User user = q.getSingleResult();
assertEquals(user.getId(), testData.users.getFirstId());
}
em.clear();
{
// Typesafe operands for joins and restriction
CriteriaQuery<Item> criteria = cb.createQuery(Item.class);
Root<Item> i = criteria.from(Item.class);
Join<Item, Bid> b = i.join(Item_.bids);
criteria.select(i).where(cb.gt(b.get(Bid_.amount), new BigDecimal(100)));
// cb.gt(b.get(Bid_.amount), "100")); // Wouldn't compile!
TypedQuery<Item> q = em.createQuery(criteria);
List<Item> result = q.getResultList();
assertEquals(result.size(), 1);
assertEquals(result.get(0).getId(), testData.items.getFirstId());
}
em.clear();
tx.commit();
em.close();
} finally {
TM.rollback();
}
}
Aggregations