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

Example 1 with Item

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

the class CreateExecuteQueries method createHibernateQueries.

@Test
public void createHibernateQueries() throws Exception {
    TestDataCategoriesItems testData = storeTestData();
    Long ITEM_ID = testData.items.getFirstId();
    UserTransaction tx = TM.getUserTransaction();
    try {
        tx.begin();
        EntityManager em = JPA.createEntityManager();
        {
            Session session = em.unwrap(Session.class);
            org.hibernate.Query query = session.createQuery("select i from Item i");
            // Proprietary API: query.setResultTransformer(...);
            assertEquals(query.list().size(), 3);
        }
        {
            Session session = em.unwrap(Session.class);
            org.hibernate.SQLQuery query = session.createSQLQuery("select {i.*} from ITEM {i}").addEntity("i", Item.class);
            assertEquals(query.list().size(), 3);
        }
        {
            Session session = em.unwrap(Session.class);
            org.hibernate.Criteria query = session.createCriteria(Item.class);
            query.add(org.hibernate.criterion.Restrictions.eq("id", ITEM_ID));
            Item result = (Item) query.uniqueResult();
            assertEquals(result.getId(), ITEM_ID);
        }
        {
            javax.persistence.Query query = em.createQuery(// ...
            "select i from Item i");
            org.hibernate.Query hibernateQuery = query.unwrap(org.hibernate.jpa.HibernateQuery.class).getHibernateQuery();
            hibernateQuery.getQueryString();
            hibernateQuery.getReturnAliases();
        // ... other proprietary API calls
        }
        tx.commit();
        em.close();
    } finally {
        TM.rollback();
    }
}

Example 2 with Item

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

the class CreateExecuteQueries method createQueries.

@Test
public void createQueries() throws Exception {
    storeTestData();
    UserTransaction tx = TM.getUserTransaction();
    try {
        tx.begin();
        EntityManager em = JPA.createEntityManager();
        {
            Query query = em.createQuery("select i from Item i");
            assertEquals(query.getResultList().size(), 3);
        }
        {
            CriteriaBuilder cb = em.getCriteriaBuilder();
            // Also available on EntityManagerFactory:
            // CriteriaBuilder cb = entityManagerFactory.getCriteriaBuilder();
            CriteriaQuery criteria = cb.createQuery();
            criteria.select(criteria.from(Item.class));
            Query query = em.createQuery(criteria);
            assertEquals(query.getResultList().size(), 3);
        }
        {
            // Note: This old JPA 1.0 method does not return a TypedQuery!
            Query query = em.createNativeQuery("select * from ITEM", Item.class);
            assertEquals(query.getResultList().size(), 3);
        }
        tx.commit();
        em.close();
    } finally {
        TM.rollback();
    }
}

Example 3 with Item

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

the class CreateExecuteQueries method pagination.

@Test
public void pagination() throws Exception {
    storeTestData();
    UserTransaction tx = TM.getUserTransaction();
    try {
        tx.begin();
        EntityManager em = JPA.createEntityManager();
        List<Item> items;
        {
            // Limiting result rows
            Query query = em.createQuery("select i from Item i");
            query.setFirstResult(40).setMaxResults(10);
            items = query.getResultList();
            assertEquals(items.size(), 0);
        }
        {
            // Rewrite SQL
            Query query = em.createNativeQuery("select * from ITEM");
            query.setFirstResult(40).setMaxResults(10);
            items = query.getResultList();
            assertEquals(items.size(), 0);
        }
        {
            // Getting total count with a cursor
            Query query = em.createQuery("select i from Item i");
            /* 
                   Unwrap the Hibernate API to use scrollable cursors.
                 */
            org.hibernate.Query hibernateQuery = query.unwrap(org.hibernate.jpa.HibernateQuery.class).getHibernateQuery();
            /* 
                   Execute the query with a database cursor; this does not retrieve the
                   result set into memory.
                 */
            org.hibernate.ScrollableResults cursor = hibernateQuery.scroll(org.hibernate.ScrollMode.SCROLL_INSENSITIVE);
            /* 
                   Jump to the last row of the result in the database, then get the row number.
                   Since row numbers are zero-based, add one to get the total count of rows.
                 */
            cursor.last();
            int count = cursor.getRowNumber() + 1;
            /* 
                   You must close the database cursor.
                 */
            cursor.close();
            /* 
                   Now execute the query again and retrieve an arbitrary page of data.
                 */
            query.setFirstResult(40).setMaxResults(10);
            assertEquals(count, 3);
            items = query.getResultList();
            assertEquals(items.size(), 0);
        }
        tx.commit();
        em.close();
    } finally {
        TM.rollback();
    }
}

Example 4 with Item

use of org.jpwh.model.querying.Item 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 5 with Item

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