Examples with User org.jpwh.model.querying.User used on opensource projects

Example 1 with User

use of org.jpwh.model.querying.User in project microservices by pwillhan.

the class QueryingTest method storeTestData.

public TestDataCategoriesItems storeTestData() throws Exception {
    UserTransaction tx = TM.getUserTransaction();
    tx.begin();
    EntityManager em = JPA.createEntityManager();
    Long[] categoryIds = new Long[4];
    Long[] itemIds = new Long[3];
    Long[] userIds = new Long[3];
    User johndoe = new User("johndoe", "John", "Doe");
    Address homeAddress = new Address("Some Street 123", "12345", "Some City");
    johndoe.setActivated(true);
    johndoe.setHomeAddress(homeAddress);
    em.persist(johndoe);
    userIds[0] = johndoe.getId();
    User janeroe = new User("janeroe", "Jane", "Roe");
    janeroe.setActivated(true);
    janeroe.setHomeAddress(new Address("Other Street 11", "1234", "Other City"));
    em.persist(janeroe);
    userIds[1] = janeroe.getId();
    User robertdoe = new User("robertdoe", "Robert", "Doe");
    em.persist(robertdoe);
    userIds[2] = robertdoe.getId();
    Category categoryOne = new Category("One");
    em.persist(categoryOne);
    categoryIds[0] = categoryOne.getId();
    Item item = new Item("Foo", CalendarUtil.TOMORROW.getTime(), johndoe);
    item.setBuyNowPrice(new BigDecimal("19.99"));
    em.persist(item);
    itemIds[0] = item.getId();
    categoryOne.getItems().add(item);
    item.getCategories().add(categoryOne);
    for (int i = 1; i <= 3; i++) {
        Bid bid = new Bid(item, robertdoe, new BigDecimal(98 + i));
        item.getBids().add(bid);
        em.persist(bid);
    }
    item.getImages().add(new Image("Foo", "foo.jpg", 640, 480));
    item.getImages().add(new Image("Bar", "bar.jpg", 800, 600));
    item.getImages().add(new Image("Baz", "baz.jpg", 1024, 768));
    Category categoryTwo = new Category("Two");
    categoryTwo.setParent(categoryOne);
    em.persist(categoryTwo);
    categoryIds[1] = categoryTwo.getId();
    item = new Item("Bar", CalendarUtil.TOMORROW.getTime(), johndoe);
    em.persist(item);
    itemIds[1] = item.getId();
    categoryTwo.getItems().add(item);
    item.getCategories().add(categoryTwo);
    Bid bid = new Bid(item, janeroe, new BigDecimal("4.99"));
    item.getBids().add(bid);
    em.persist(bid);
    item = new Item("Baz", CalendarUtil.AFTER_TOMORROW.getTime(), janeroe);
    item.setApproved(false);
    em.persist(item);
    itemIds[2] = item.getId();
    categoryTwo.getItems().add(item);
    item.getCategories().add(categoryTwo);
    Category categoryThree = new Category("Three");
    categoryThree.setParent(categoryOne);
    em.persist(categoryThree);
    categoryIds[2] = categoryThree.getId();
    Category categoryFour = new Category("Four");
    categoryFour.setParent(categoryTwo);
    em.persist(categoryFour);
    categoryIds[3] = categoryFour.getId();
    CreditCard cc = new CreditCard("John Doe", "1234123412341234", "06", "2015");
    em.persist(cc);
    BankAccount ba = new BankAccount("Jane Roe", "445566", "One Percent Bank Inc.", "999");
    em.persist(ba);
    LogRecord lr = new LogRecord("johndoe", "This is a log message");
    em.persist(lr);
    lr = new LogRecord("johndoe", "Another log message");
    em.persist(lr);
    tx.commit();
    em.close();
    TestDataCategoriesItems testData = new TestDataCategoriesItems();
    testData.categories = new TestData(categoryIds);
    testData.items = new TestData(itemIds);
    testData.users = new TestData(userIds);
    return testData;
}

Example 2 with User

use of org.jpwh.model.querying.User in project microservices by pwillhan.

the class HibernateCriteria method executeQueries.

@Test
public void executeQueries() throws Exception {
    TestDataCategoriesItems testData = storeTestData();
    UserTransaction tx = TM.getUserTransaction();
    try {
        tx.begin();
        EntityManager em = JPA.createEntityManager();
        Session session = em.unwrap(Session.class);
        {
            // Selection
            org.hibernate.Criteria criteria = session.createCriteria(Item.class);
            List<Item> items = criteria.list();
            assertEquals(items.size(), 3);
        }
        em.clear();
        {
            DetachedCriteria criteria = DetachedCriteria.forClass(Item.class);
            List<Item> items = criteria.getExecutableCriteria(session).list();
            assertEquals(items.size(), 3);
        }
        em.clear();
        {
            // Ordering
            List<User> users = session.createCriteria(User.class).addOrder(Order.asc("firstname")).addOrder(Order.asc("lastname")).list();
            assertEquals(users.size(), 3);
            assertEquals(users.get(0).getFirstname(), "Jane");
            assertEquals(users.get(1).getFirstname(), "John");
            assertEquals(users.get(2).getFirstname(), "Robert");
        }
        em.clear();
        {
            // Restriction
            List<Item> items = session.createCriteria(Item.class).add(Restrictions.eq("name", "Foo")).list();
            assertEquals(items.size(), 1);
        }
        em.clear();
        {
            List<User> users = session.createCriteria(User.class).add(Restrictions.like("username", "j", MatchMode.START).ignoreCase()).list();
            assertEquals(users.size(), 2);
        }
        em.clear();
        {
            List<User> users = session.createCriteria(User.class).add(Restrictions.eq("homeAddress.city", "Some City")).list();
            assertEquals(users.size(), 1);
            assertEquals(users.get(0).getUsername(), "johndoe");
        }
        em.clear();
        {
            List<User> users = session.createCriteria(User.class).add(Restrictions.sqlRestriction("length({alias}.USERNAME) < ?", 8, StandardBasicTypes.INTEGER)).list();
            assertEquals(users.size(), 2);
        }
        em.clear();
        {
            // Projection
            List<Object[]> result = session.createCriteria(User.class).setProjection(Projections.projectionList().add(Projections.property("id")).add(Projections.property("username")).add(Projections.property("homeAddress"))).list();
            assertEquals(result.size(), 3);
            for (Object[] tuple : result) {
                assertTrue(tuple[0] instanceof Long);
                assertTrue(tuple[1] instanceof String);
                assertTrue(tuple[2] == null || tuple[2] instanceof Address);
            }
        }
        em.clear();
        {
            List<String> result = session.createCriteria(Item.class).setProjection(Projections.projectionList().add(Projections.sqlProjection("NAME || ':' || AUCTIONEND as RESULT", new String[] { "RESULT" }, new Type[] { StandardBasicTypes.STRING }))).list();
            assertEquals(result.size(), 3);
        }
        em.clear();
        {
            List<Object[]> result = session.createCriteria(User.class).setProjection(Projections.projectionList().add(Projections.groupProperty("lastname")).add(Projections.rowCount())).list();
            assertEquals(result.size(), 2);
            for (Object[] tuple : result) {
                assertTrue(tuple[0] instanceof String);
                assertTrue(tuple[1] instanceof Long);
            }
        }
        em.clear();
        {
            List<Object[]> result = session.createCriteria(Bid.class).setProjection(Projections.projectionList().add(Projections.groupProperty("item")).add(Projections.avg("amount"))).list();
            assertEquals(result.size(), 2);
            for (Object[] tuple : result) {
                assertTrue(tuple[0] instanceof Item);
                assertTrue(tuple[1] instanceof Double);
            }
        }
        em.clear();
        {
            // Joins
            List<Bid> result = session.createCriteria(Bid.class).createCriteria(// Inner join
            "item").add(Restrictions.like("name", "Fo", MatchMode.START)).list();
            assertEquals(result.size(), 3);
            for (Bid bid : result) {
                assertEquals(bid.getItem().getId(), testData.items.getFirstId());
            }
        }
        em.clear();
        {
            List<Bid> result = session.createCriteria(Bid.class).createCriteria(// Inner join
            "item").add(Restrictions.isNotNull("buyNowPrice")).createCriteria(// Inner join
            "seller").add(Restrictions.eq("username", "johndoe")).list();
            assertEquals(result.size(), 3);
        }
        em.clear();
        {
            List<Object[]> result = session.createCriteria(Bid.class, "b").createAlias("item", "i", JoinType.RIGHT_OUTER_JOIN).add(Restrictions.or(Restrictions.isNull("b.id"), Restrictions.gt("amount", new BigDecimal(100)))).setResultTransformer(Criteria.PROJECTION).list();
            assertEquals(result.size(), 2);
            // Criteria quirk: the root entity alias is always last in the result tuple
            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);
        }
        em.clear();
        {
            List<Bid> result = session.createCriteria(Bid.class).createCriteria(// Inner join
            "item").createAlias("seller", // Inner join
            "s").add(Restrictions.and(Restrictions.eq("s.username", "johndoe"), Restrictions.isNotNull("buyNowPrice"))).list();
            assertEquals(result.size(), 3);
        }
        em.clear();
        {
            // Fetching
            List<Item> result = session.createCriteria(Item.class).setFetchMode("bids", FetchMode.JOIN).list();
            // 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 : result) {
                // No more lazy loading!
                em.detach(item);
                if (item.getBids().size() > 0) {
                    haveBids = true;
                    break;
                }
            }
            assertTrue(haveBids);
        }
        em.clear();
        {
            List<Item> result = session.createCriteria(Item.class).setFetchMode("bids", FetchMode.JOIN).setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).list();
            // 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();
        {
            List<Item> result = session.createCriteria(Item.class).createAlias("bids", "b", JoinType.LEFT_OUTER_JOIN).setFetchMode("b", FetchMode.JOIN).createAlias("b.bidder", "bdr", JoinType.INNER_JOIN).setFetchMode("bdr", FetchMode.JOIN).createAlias("seller", "s", JoinType.LEFT_OUTER_JOIN).setFetchMode("s", FetchMode.JOIN).list();
            result = new ArrayList<Item>(new LinkedHashSet<Item>(result));
            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();
        {
            // Subquery
            DetachedCriteria sq = DetachedCriteria.forClass(Item.class, "i");
            sq.add(Restrictions.eqProperty("i.seller.id", "u.id"));
            sq.setProjection(Projections.rowCount());
            List<User> result = session.createCriteria(User.class, "u").add(Subqueries.lt(1l, sq)).list();
            assertEquals(result.size(), 1);
            User user = result.iterator().next();
            assertEquals(user.getId(), testData.users.getFirstId());
        }
        em.clear();
        {
            DetachedCriteria sq = DetachedCriteria.forClass(Bid.class, "b");
            sq.add(Restrictions.eqProperty("b.item.id", "i.id"));
            sq.setProjection(Projections.property("amount"));
            List<Item> result = session.createCriteria(Item.class, "i").add(Subqueries.geAll(new BigDecimal(10), sq)).list();
            assertEquals(result.size(), 2);
        }
        em.clear();
        {
            // Query by example: Find all users with last name "Doe"
            /* 
                   First, create an "empty" instance of <code>User</code> as a template for your
                   search and set the property values you are looking for. You want to retrieve
                   people with the last name "Doe".
                 */
            User template = new User();
            template.setLastname("Doe");
            /* 
                   Create an instance of <code>Example</code> with the template, this API allows
                   you to fine-tune the search. You want the case of the last name to be ignored,
                   and a substring search, so "Doe", "DoeX", or "Doe Y" would match. In addition, the
                   <code>User</code> class has a <code>boolean</code> property called
                   <code>activated</code>. As a primitive, it can't be <code>null</code> and its
                   default value is <code>false</code>, so Hibernate would include it in the search
                   and only return users that aren't activated. As you want all users, you tell
                   Hibernate to ignore that property when building the search query.
                 */
            org.hibernate.criterion.Example example = Example.create(template);
            example.ignoreCase();
            example.enableLike(MatchMode.START);
            example.excludeProperty("activated");
            /* 
                   The <code>Example</code> is added to a <code>Criteria</code> as a restriction.
                 */
            List<User> users = session.createCriteria(User.class).add(example).list();
            assertEquals(users.size(), 2);
        }
        em.clear();
        {
            // Find all items which have a name starting with "B" or "b", and a
            // seller with the last name "Doe"
            Item itemTemplate = new Item();
            itemTemplate.setName("B");
            Example exampleItem = Example.create(itemTemplate);
            exampleItem.ignoreCase();
            exampleItem.enableLike(MatchMode.START);
            exampleItem.excludeProperty("auctionType");
            exampleItem.excludeProperty("createdOn");
            User userTemplate = new User();
            userTemplate.setLastname("Doe");
            Example exampleUser = Example.create(userTemplate);
            exampleUser.excludeProperty("activated");
            List<Item> items = session.createCriteria(Item.class).add(exampleItem).createCriteria("seller").add(exampleUser).list();
            assertEquals(items.size(), 1);
            assertTrue(items.get(0).getName().startsWith("B"));
            assertEquals(items.get(0).getSeller().getLastname(), "Doe");
        }
        em.clear();
        tx.commit();
        em.close();
    } finally {
        TM.rollback();
    }
}

Example 3 with User

use of org.jpwh.model.querying.User 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();
    }
}

Example 4 with User

use of org.jpwh.model.querying.User 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();
    }
}

Example 5 with User

use of org.jpwh.model.querying.User in project microservices by pwillhan.

the class HibernateSQLQueries method executeQueries.

@Test
public void executeQueries() throws Exception {
    TestDataCategoriesItems testData = storeTestData();
    UserTransaction tx = TM.getUserTransaction();
    try {
        tx.begin();
        EntityManager em = JPA.createEntityManager();
        Session session = em.unwrap(Session.class);
        {
            // Simple SQL projection
            org.hibernate.SQLQuery query = session.createSQLQuery("select NAME, AUCTIONEND from {h-schema}ITEM");
            List<Object[]> result = query.list();
            for (Object[] tuple : result) {
                assertTrue(tuple[0] instanceof String);
                assertTrue(tuple[1] instanceof Date);
            }
            assertEquals(result.size(), 3);
        }
        em.clear();
        {
            // Automatic marshaling of resultset to mapped entity class
            org.hibernate.SQLQuery query = session.createSQLQuery("select * from ITEM");
            query.addEntity(Item.class);
            List<Item> result = query.list();
            assertEquals(result.size(), 3);
            assertNotNull(result.get(0));
        }
        em.clear();
        {
            // Positional parameter binding
            Long ITEM_ID = testData.items.getFirstId();
            org.hibernate.SQLQuery query = session.createSQLQuery("select * from ITEM where ID = ?");
            query.addEntity(Item.class);
            // Starts at zero!
            query.setParameter(0, ITEM_ID);
            List<Item> result = query.list();
            assertEquals(result.size(), 1);
            assertEquals(result.get(0).getId(), ITEM_ID);
        }
        em.clear();
        {
            // Named parameter binding
            Long ITEM_ID = testData.items.getFirstId();
            org.hibernate.SQLQuery query = session.createSQLQuery("select * from ITEM where ID = :id");
            query.addEntity(Item.class);
            query.setParameter("id", ITEM_ID);
            List<Item> result = query.list();
            assertEquals(result.size(), 1);
            assertEquals(result.get(0).getId(), ITEM_ID);
        }
        em.clear();
        {
            // Automatic marshaling of resultset to entity class with aliases
            org.hibernate.SQLQuery query = session.createSQLQuery("select " + "i.ID as {i.id}, " + "'Auction: ' || i.NAME as {i.name}, " + "i.CREATEDON as {i.createdOn}, " + "i.AUCTIONEND as {i.auctionEnd}, " + "i.AUCTIONTYPE as {i.auctionType}, " + "i.APPROVED as {i.approved}, " + "i.BUYNOWPRICE as {i.buyNowPrice}, " + "i.SELLER_ID as {i.seller} " + "from ITEM i");
            query.addEntity("i", Item.class);
            List<Item> result = query.list();
            assertEquals(result.size(), 3);
            assertNotNull(result.get(0));
        }
        em.clear();
        {
            // Automatic marshaling of resultset to entity class with aliases (alternative)
            org.hibernate.SQLQuery query = session.createSQLQuery("select " + "i.ID, " + "'Auction: ' || i.NAME as EXTENDED_NAME, " + "i.CREATEDON, " + "i.AUCTIONEND, " + "i.AUCTIONTYPE, " + "i.APPROVED, " + "i.BUYNOWPRICE, " + "i.SELLER_ID " + "from ITEM i");
            query.addRoot("i", Item.class).addProperty("id", "ID").addProperty("name", "EXTENDED_NAME").addProperty("createdOn", "CREATEDON").addProperty("auctionEnd", "AUCTIONEND").addProperty("auctionType", "AUCTIONTYPE").addProperty("approved", "APPROVED").addProperty("buyNowPrice", "BUYNOWPRICE").addProperty("seller", "SELLER_ID");
            List<Item> result = query.list();
            assertEquals(result.size(), 3);
            assertNotNull(result.get(0));
        }
        em.clear();
        {
            // Automatic marshaling of resultset to entity class with aliases (using existing mapping)
            org.hibernate.SQLQuery query = session.createSQLQuery("select " + "i.ID, " + "'Auction: ' || i.NAME as EXTENDED_NAME, " + "i.CREATEDON, " + "i.AUCTIONEND, " + "i.AUCTIONTYPE, " + "i.APPROVED, " + "i.BUYNOWPRICE, " + "i.SELLER_ID " + "from ITEM i");
            query.setResultSetMapping("ItemResult");
            List<Item> result = query.list();
            assertEquals(result.size(), 3);
            assertNotNull(result.get(0));
        }
        em.clear();
        {
            // Automatic marshaling of resultset to several mapped entity classes
            org.hibernate.SQLQuery query = session.createSQLQuery("select " + "{i.*}, {u.*} " + "from ITEM i join USERS u on u.ID = i.SELLER_ID");
            query.addEntity("i", Item.class);
            query.addEntity("u", User.class);
            List<Object[]> result = query.list();
            for (Object[] tuple : result) {
                assertTrue(tuple[0] instanceof Item);
                assertTrue(tuple[1] instanceof User);
                Item item = (Item) tuple[0];
                assertTrue(Persistence.getPersistenceUtil().isLoaded(item, "seller"));
                assertEquals(item.getSeller(), tuple[1]);
            }
            assertEquals(result.size(), 3);
        }
        em.clear();
        {
            // Automatic marshaling of resultset to entity class with component aliases
            org.hibernate.SQLQuery query = session.createSQLQuery("select " + "u.ID as {u.id}, " + "u.USERNAME as {u.username}, " + "u.FIRSTNAME as {u.firstname}, " + "u.LASTNAME as {u.lastname}, " + "u.ACTIVATED as {u.activated}, " + "u.STREET as {u.homeAddress.street}, " + "u.ZIPCODE as {u.homeAddress.zipcode}, " + "u.CITY as {u.homeAddress.city} " + "from USERS u");
            query.addEntity("u", User.class);
            List<User> result = query.list();
            assertEquals(result.size(), 3);
            assertNotNull(result.get(0));
        }
        em.clear();
        {
            // Automatic marshaling of resultset with eager fetch of collection
            /* 
                   The query (outer) joins the <code>ITEM</code> and <code>BID</code> tables, the projection
                   returns all columns required to construct <code>Item</code> and <code>Bid</code> instances.
                   The query renames duplicate columns such as <code>ID</code> with aliases, so field names are
                   unique in the result.
                 */
            org.hibernate.SQLQuery query = session.createSQLQuery("select " + "i.ID as ITEM_ID, " + "i.NAME, " + "i.CREATEDON, " + "i.AUCTIONEND, " + "i.AUCTIONTYPE, " + "i.APPROVED, " + "i.BUYNOWPRICE, " + "i.SELLER_ID, " + "b.ID as BID_ID," + "b.ITEM_ID as BID_ITEM_ID, " + "b.AMOUNT, " + "b.BIDDER_ID " + "from ITEM i left outer join BID b on i.ID = b.ITEM_ID");
            /* 
                   Because of the renamed fields, you have to map all columns to their respective
                   entity property.
                 */
            query.addRoot("i", Item.class).addProperty("id", "ITEM_ID").addProperty("name", "NAME").addProperty("createdOn", "CREATEDON").addProperty("auctionEnd", "AUCTIONEND").addProperty("auctionType", "AUCTIONTYPE").addProperty("approved", "APPROVED").addProperty("buyNowPrice", "BUYNOWPRICE").addProperty("seller", "SELLER_ID");
            /* 
                   You add a <code>FetchReturn</code> for the <code>bids</code> collection with the alias of the
                   owning entity <code>i</code>, and map the <code>key</code> and <code>element</code> special
                   properties to the foreign key column <code>BID_ITEM_ID</code> and the identifier of the
                   <code>Bid</code>. Then the code maps each property of <code>Bid</code> to a field of the result set.
                   Note that the code maps some fields twice, as required by Hibernate for construction of the
                   collection.
                 */
            query.addFetch("b", "i", "bids").addProperty("key", "BID_ITEM_ID").addProperty("element", "BID_ID").addProperty("element.id", "BID_ID").addProperty("element.item", "BID_ITEM_ID").addProperty("element.amount", "AMOUNT").addProperty("element.bidder", "BIDDER_ID");
            List<Object[]> result = query.list();
            /* 
                   The number of rows in the result set is a product: 1 item has 3 bids, 1 item has 1 bid, and
                   the last item has no bids, for a total of 5 rows in the result.
                 */
            assertEquals(result.size(), 5);
            for (Object[] tuple : result) {
                /* 
                       The first element of the result tuple is the <code>Item</code> instance; Hibernate initialized
                       the bids collection.
                     */
                Item item = (Item) tuple[0];
                assertTrue(Persistence.getPersistenceUtil().isLoaded(item, "bids"));
                /* 
                       The second element of the result tuple is each <code>Bid</code>.
                     */
                Bid bid = (Bid) tuple[1];
                if (bid != null)
                    assertTrue(item.getBids().contains(bid));
            }
        }
        em.clear();
        {
            // Automatic marshaling of resultset with eager fetch of collection (short version)
            org.hibernate.SQLQuery query = session.createSQLQuery("select " + "{i.*}, " + "{b.*} " + "from ITEM i left outer join BID b on i.ID = b.ITEM_ID");
            query.addEntity("i", Item.class);
            query.addFetch("b", "i", "bids");
            List<Object[]> result = query.list();
            assertEquals(result.size(), 5);
            for (Object[] tuple : result) {
                Item item = (Item) tuple[0];
                assertTrue(Persistence.getPersistenceUtil().isLoaded(item, "bids"));
                Bid bid = (Bid) tuple[1];
                if (bid != null)
                    assertTrue(item.getBids().contains(bid));
            }
        }
        em.clear();
        {
            // Automatic marshaling of resultset to entity class and additional column
            org.hibernate.SQLQuery query = session.createSQLQuery("select " + "i.*, " + "count(b.ID) as NUM_OF_BIDS " + "from ITEM i left join BID b on b.ITEM_ID = i.ID " + "group by i.ID, i.NAME, i.CREATEDON, i.AUCTIONEND, " + "i.AUCTIONTYPE, i.APPROVED, i.BUYNOWPRICE, i.SELLER_ID");
            query.addEntity(Item.class);
            query.addScalar("NUM_OF_BIDS");
            List<Object[]> result = query.list();
            for (Object[] tuple : result) {
                assertTrue(tuple[0] instanceof Item);
                assertTrue(tuple[1] instanceof Number);
            }
            assertEquals(result.size(), 3);
            assertNotNull(result.get(0));
        }
        em.clear();
        {
            // Automatic marshaling of resultset to data-transfer class constructor
            org.hibernate.SQLQuery query = session.createSQLQuery("select ID, NAME, AUCTIONEND from ITEM");
            /* 
                   You can use an existing result mapping.
                 */
            // query.setResultSetMapping("ItemSummaryResult");
            /* 
                   Alternatively, you can map the fields returned by the query as scalar values. Without a
                   result transformer, you would simply get an <code>Object[]</code> for each result row.
                 */
            query.addScalar("ID", StandardBasicTypes.LONG);
            query.addScalar("NAME");
            query.addScalar("AUCTIONEND");
            /* 
                   Apply a built-in result transformer to turn the <code>Object[]</code> into instances
                   of <code>ItemSummary</code>.
                 */
            query.setResultTransformer(new AliasToBeanConstructorResultTransformer(ItemSummary.class.getConstructor(Long.class, String.class, Date.class)));
            List<ItemSummary> result = query.list();
            assertNotNull(result.get(0));
            assertEquals(result.size(), 3);
        }
        em.clear();
        tx.commit();
        em.close();
    } finally {
        TM.rollback();
    }
}