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

Example 26 with Item

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

the class HibernateCriteria method foo.

@Test
public void foo() throws Exception {
    TestDataCategoriesItems testData = storeTestData();
    UserTransaction tx = TM.getUserTransaction();
    try {
        tx.begin();
        EntityManager em = JPA.createEntityManager();
        Session session = em.unwrap(Session.class);
        {
            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();
        tx.commit();
        em.close();
    } finally {
        TM.rollback();
    }
}

Example 27 with Item

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

the class TransformResults method executeQueries.

@Test
public void executeQueries() throws Exception {
    storeTestData();
    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.id as itemId, i.name as name, i.auctionEnd as auctionEnd from Item i");
        {
            // Access List of Object[]
            List<Object[]> result = query.list();
            for (Object[] tuple : result) {
                Long itemId = (Long) tuple[0];
                String name = (String) tuple[1];
                Date auctionEnd = (Date) tuple[2];
            // ...
            }
            assertEquals(result.size(), 3);
        }
        em.clear();
        {
            // Transform to List of Lists
            query.setResultTransformer(ToListResultTransformer.INSTANCE);
            List<List> result = query.list();
            for (List list : result) {
                Long itemId = (Long) list.get(0);
                String name = (String) list.get(1);
                Date auctionEnd = (Date) list.get(2);
            // ...
            }
            assertEquals(result.size(), 3);
        }
        em.clear();
        {
            // Transform to List of Maps
            query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
            List<Map> result = query.list();
            // You can access the aliases of the query
            assertEquals(query.getReturnAliases(), new String[] { "itemId", "name", "auctionEnd" });
            for (Map map : result) {
                Long itemId = (Long) map.get("itemId");
                String name = (String) map.get("name");
                Date auctionEnd = (Date) map.get("auctionEnd");
            // ...
            }
            assertEquals(result.size(), 3);
        }
        em.clear();
        {
            // Transform to List of Maps with entity aliases
            org.hibernate.Query entityQuery = session.createQuery("select i as item, u as seller from Item i join i.seller u");
            entityQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
            List<Map> result = entityQuery.list();
            for (Map map : result) {
                Item item = (Item) map.get("item");
                User seller = (User) map.get("seller");
                assertEquals(item.getSeller(), seller);
            // ...
            }
            assertEquals(result.size(), 3);
        }
        em.clear();
        {
            // Transform to List of JavaBean calling setters/fields
            query.setResultTransformer(new AliasToBeanResultTransformer(ItemSummary.class));
            List<ItemSummary> result = query.list();
            for (ItemSummary itemSummary : result) {
                Long itemId = itemSummary.getItemId();
                String name = itemSummary.getName();
                Date auctionEnd = itemSummary.getAuctionEnd();
            // ...
            }
            assertEquals(result.size(), 3);
        }
        em.clear();
        {
            // Custom ResultTransformer
            query.setResultTransformer(new ResultTransformer() {

                /**
                 * For each result "row", an <code>Object[]</code> tuple has to be transformed into
                 * the desired result value for that row. Here you access each projection element by
                 * index in the tuple array, and then call the <code>ItemSummaryFactory</code> to produce
                 * the query result value. Hibernate passes the method the aliases found in the query, for each
                 * tuple element. You don't need the aliases in this transformer, though.
                 */
                @Override
                public Object transformTuple(Object[] tuple, String[] aliases) {
                    Long itemId = (Long) tuple[0];
                    String name = (String) tuple[1];
                    Date auctionEnd = (Date) tuple[2];
                    // You can access the aliases of the query if needed
                    assertEquals(aliases[0], "itemId");
                    assertEquals(aliases[1], "name");
                    assertEquals(aliases[2], "auctionEnd");
                    return ItemSummaryFactory.newItemSummary(itemId, name, auctionEnd);
                }

                /**
                 * You can wrap or modify the result list after after transforming the tuples.
                 * Here you make the returned <code>List</code> unmodifiable,
                 * ideal for a reporting screen where nothing should change the data.
                 */
                @Override
                public List transformList(List collection) {
                    // The "collection" is a List<ItemSummary>
                    return Collections.unmodifiableList(collection);
                }
            });
            List<ItemSummary> result = query.list();
            assertEquals(result.size(), 3);
        }
        em.clear();
        /* Hibernate has an internal CriteriaResultTransformer for JPA criteria queries
               TODO https://hibernate.atlassian.net/browse/HHH-8196
            {
                CriteriaBuilder cb = JPA.getEntityManagerFactory().getCriteriaBuilder();

                CriteriaQuery criteria = cb.createQuery();
                Root<Item> i = criteria.from(Item.class);
                i.alias("i");
                criteria.multiselect(
                    i.get("id").alias("itemId"),
                    i.get("name").alias("name"),
                    i.get("auctionEnd").alias("auctionEnd")
                );

                Query query = em.createQuery(criteria);
                org.hibernate.Query hibernateQuery = ((HibernateQuery)query).getHibernateQuery();

                /*
                assertEquals(
                    hibernateQuery.getQueryString(),
                    "select i.id as itemId, i.name as name, i.auctionEnd as auctionEnd from Item as i"
                );
                // Actual: select i.id, i.name, i.auctionEnd from Item as i

                assertEquals(
                    hibernateQuery.getReturnAliases(),
                    new String[] {"itemId", "name", "auctionEnd"}
                );
                // Actual: 0, 1, 2

                // Overrides the internal CriteriaResultTransformer, breaks JPA converters
                hibernateQuery.setResultTransformer(
                    new AliasToBeanResultTransformer(ItemSummary.class)
                );

                List<ItemSummary> result = query.getResultList();
                assertEquals(result.size(), 3);
            }
            em.clear();
            */
        tx.commit();
        em.close();
    } finally {
        TM.rollback();
    }
}

Example 28 with Item

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

the class Projection 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();
        {
            // Product
            CriteriaQuery criteria = cb.createQuery();
            Root<Item> i = criteria.from(Item.class);
            Root<Bid> b = criteria.from(Bid.class);
            criteria.select(cb.tuple(i, b));
            /* Convenient alternative:
                criteria.multiselect(
                    criteria.from(Item.class),
                    criteria.from(Bid.class)
                );
                */
            Query q = em.createQuery(criteria);
            List<Object[]> result = q.getResultList();
            // Cartesian product!
            assertEquals(result.size(), 12);
            Set<Item> items = new HashSet();
            Set<Bid> bids = new HashSet();
            for (Object[] row : result) {
                assertTrue(row[0] instanceof Item);
                items.add((Item) row[0]);
                assertTrue(row[1] instanceof Bid);
                bids.add((Bid) row[1]);
            }
            assertEquals(items.size(), 3);
            assertEquals(bids.size(), 4);
        }
        em.clear();
        {
            // Transient result
            CriteriaQuery criteria = cb.createQuery();
            Root<User> u = criteria.from(User.class);
            // Returns List of Object[]
            criteria.multiselect(u.get("id"), u.get("username"), u.get("homeAddress"));
            Query q = em.createQuery(criteria);
            List<Object[]> result = q.getResultList();
            assertEquals(result.size(), 3);
            Object[] firstRow = result.get(0);
            assertTrue(firstRow[0] instanceof Long);
            assertTrue(firstRow[1] instanceof String);
            assertTrue(firstRow[2] instanceof Address);
        }
        em.clear();
        {
            // Distinct
            CriteriaQuery<String> criteria = cb.createQuery(String.class);
            criteria.select(criteria.from(Item.class).<String>get("name"));
            Query q = em.createQuery(criteria);
            assertEquals(q.getResultList().size(), 3);
        }
        em.clear();
        {
            // Distinct
            CriteriaQuery<String> criteria = cb.createQuery(String.class);
            criteria.select(criteria.from(Item.class).<String>get("name"));
            criteria.distinct(true);
            Query q = em.createQuery(criteria);
            assertEquals(q.getResultList().size(), 3);
        }
        em.clear();
        {
            // Dynamic instance creation
            CriteriaQuery criteria = cb.createQuery();
            Root<Item> i = criteria.from(Item.class);
            criteria.select(cb.construct(// Must have the right constructor!
            ItemSummary.class, i.get("id"), i.get("name"), i.get("auctionEnd")));
            Query q = em.createQuery(criteria);
            List<ItemSummary> result = q.getResultList();
            assertEquals(result.size(), 3);
        }
        em.clear();
        {
            // Tuple API
            CriteriaQuery<Tuple> criteria = cb.createTupleQuery();
            // Or: CriteriaQuery<Tuple> criteria = cb.createQuery(Tuple.class);
            criteria.multiselect(// Aliases optional!
            criteria.from(Item.class).alias("i"), criteria.from(Bid.class).alias("b"));
            TypedQuery<Tuple> query = em.createQuery(criteria);
            List<Tuple> result = query.getResultList();
            Set<Item> items = new HashSet();
            Set<Bid> bids = new HashSet();
            for (Tuple tuple : result) {
                // Indexed
                Item item = tuple.get(0, Item.class);
                Bid bid = tuple.get(1, Bid.class);
                // Alias
                item = tuple.get("i", Item.class);
                bid = tuple.get("b", Bid.class);
                // Meta
                for (TupleElement<?> element : tuple.getElements()) {
                    Class clazz = element.getJavaType();
                    String alias = element.getAlias();
                    Object value = tuple.get(element);
                }
                items.add(item);
                bids.add(bid);
            }
            // Cartesian product!
            assertEquals(result.size(), 12);
            assertEquals(items.size(), 3);
            assertEquals(bids.size(), 4);
        }
        em.clear();
        // All following are more function call examples, with variations
        {
            // Concat
            CriteriaQuery criteria = cb.createQuery();
            Root<Item> i = criteria.from(Item.class);
            criteria.select(cb.concat(cb.concat(i.<String>get("name"), ":"), // Note the cast of Date!
            i.<String>get("auctionEnd")));
            Query q = em.createQuery(criteria);
            assertEquals(q.getResultList().size(), 3);
        }
        em.clear();
        {
            // Coalesce
            CriteriaQuery criteria = cb.createQuery();
            Root<Item> i = criteria.from(Item.class);
            criteria.multiselect(i.get("name"), cb.coalesce(i.<BigDecimal>get("buyNowPrice"), 0));
            Query q = em.createQuery(criteria);
            List<Object[]> result = q.getResultList();
            assertEquals(result.size(), 3);
            for (Object[] row : result) {
                assertTrue(row[0] instanceof String);
                // Never NULL!
                assertTrue(row[1] instanceof BigDecimal);
            }
        }
        em.clear();
        {
        // Case When
        /*
                CriteriaQuery criteria = cb.createQuery();
                TODO String literals not supported, Hibernate bug HHH-8124
                Root<User> u = criteria.from(User.class);
                criteria.multiselect(
                    u.get("username"),
                    cb.selectCase()
                        .when(
                            cb.equal(
                                cb.length(u.get("homeAddress").<String>get("zipcode")), 5
                            ), "Germany"
                        )
                        .when(
                            cb.equal(
                                cb.length(u.get("homeAddress").<String>get("zipcode")), 4
                            ), "Switzerland"
                        )
                        .otherwise("Other")
                );

                Query q = em.createQuery(criteria);
                List<Object[]> result = q.getResultList();
                assertEquals(result.size(), 3);
                for (Object[] row : result) {
                    assertTrue(row[0] instanceof String);
                    assertTrue(row[1] instanceof String);
                }
                */
        }
        em.clear();
        {
            // Count
            CriteriaQuery criteria = cb.createQuery();
            criteria.select(cb.count(criteria.from(Item.class)));
            Query q = em.createQuery(criteria);
            Long count = (Long) q.getSingleResult();
            assertEquals(count, new Long(3));
        }
        em.clear();
        {
            // count(distinct ...)
            CriteriaQuery criteria = cb.createQuery();
            criteria.select(cb.countDistinct(criteria.from(Item.class).get("name")));
            Query q = em.createQuery(criteria);
            Long count = (Long) q.getSingleResult();
            assertEquals(count, new Long(3));
        }
        em.clear();
        {
            // Sum
            CriteriaQuery<Number> criteria = cb.createQuery(Number.class);
            criteria.select(cb.sum(criteria.from(Bid.class).<BigDecimal>get("amount")));
            Query q = em.createQuery(criteria);
            BigDecimal sum = (BigDecimal) q.getSingleResult();
            assertEquals(sum.compareTo(new BigDecimal("304.99")), 0);
        }
        em.clear();
        {
            // Min/Max
            CriteriaQuery criteria = cb.createQuery();
            Root<Bid> b = criteria.from(Bid.class);
            criteria.multiselect(cb.min(b.<BigDecimal>get("amount")), cb.max(b.<BigDecimal>get("amount")));
            criteria.where(cb.equal(b.get("item").<Long>get("id"), cb.parameter(Long.class, "itemId")));
            Query q = em.createQuery(criteria);
            q.setParameter("itemId", testData.items.getFirstId());
            Object[] result = (Object[]) q.getSingleResult();
            assertEquals(((BigDecimal) result[0]).compareTo(new BigDecimal("99")), 0);
            assertEquals(((BigDecimal) result[1]).compareTo(new BigDecimal("101")), 0);
        }
        em.clear();
        tx.commit();
        em.close();
    } finally {
        TM.rollback();
    }
}

Example 29 with Item

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

the class CallStoredProcedures method callReturningMultipleResults.

@Test(groups = "MYSQL")
public void callReturningMultipleResults() throws Exception {
    storeTestData();
    UserTransaction tx = TM.getUserTransaction();
    try {
        tx.begin();
        EntityManager em = JPA.createEntityManager();
        /*
            em.unwrap(Session.class).doWork(
                new CallProcedureAndPrintResult() {
                    @Override
                    protected CallableStatement prepare(Connection connection) throws SQLException {
                        return connection.prepareCall("{call APPROVE_ITEMS()}");
                    }
                }
            );
            */
        StoredProcedureQuery query = em.createStoredProcedureQuery("APPROVE_ITEMS", // Or name of result set mapping
        Item.class);
        boolean[] expectedResults = new boolean[3];
        int i = 0;
        /* 
               First, execute the procedure call with <code>execute()</code>. This method returns <code>true</code> if
               the first result of the call is a result set and <code>false</code> if the first result is an update
               count.
             */
        boolean isCurrentReturnResultSet = query.execute();
        /* 
               Next, process all results of the call in a loop. You stop looping when there are
               no more results available, which is always indicated by <code>hasMoreResults()</code>
               returning <code>false</code> and <code>getUpdateCount()</code> returning <code>-1</code>.
             */
        while (true) {
            /* 
                   If the current result is a result set, read and process it. Hibernate will map the
                   columns in each result set to managed instances of the <code>Item</code> class.
                   Alternatively, provide a result set mapping name applicable to all result sets
                   returned by the call.
                 */
            if (isCurrentReturnResultSet) {
                List<Item> result = query.getResultList();
                // one row, the second with two rows
                if (i == 0) {
                    assertEquals(result.size(), 1);
                } else if (i == 1) {
                    assertEquals(result.size(), 2);
                }
                expectedResults[i] = true;
            } else {
                /* 
                       If the current result is an update count, <code>getUpdateCount()</code> returns a value
                       greater than <code>-1</code>.
                     */
                int updateCount = query.getUpdateCount();
                if (updateCount > -1) {
                    // ...
                    // We also updated one row in the procedure
                    assertEquals(updateCount, 1);
                    expectedResults[i] = true;
                } else {
                    // No more update counts, exit loop
                    break;
                }
            }
            /* 
                   The <code>hasMoreResults()</code> method advances to the next result and indicates the
                   type of that result.
                 */
            isCurrentReturnResultSet = query.hasMoreResults();
            i++;
        }
        for (boolean haveResult : expectedResults) {
            assertTrue(haveResult);
        }
        tx.commit();
        em.close();
    } finally {
        TM.rollback();
    }
}

Example 30 with Item

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

the class CallStoredProcedures method callWithInOutParametersNative.

@Test(groups = "MYSQL")
public void callWithInOutParametersNative() throws Exception {
    TestDataCategoriesItems testDataCategoriesItems = storeTestData();
    final Long ITEM_ID = testDataCategoriesItems.items.getFirstId();
    UserTransaction tx = TM.getUserTransaction();
    try {
        tx.begin();
        EntityManager em = JPA.createEntityManager();
        Session session = em.unwrap(Session.class);
        org.hibernate.procedure.ProcedureCall call = session.createStoredProcedureCall("FIND_ITEM_TOTAL", Item.class);
        /* 
               Register all parameters; you can bind input values directly.
             */
        call.registerParameter(1, Long.class, ParameterMode.IN).bindValue(ITEM_ID);
        /* 
               Output parameter registrations can be reused later to read the output value.
             */
        ParameterRegistration<Long> totalParameter = call.registerParameter(2, Long.class, ParameterMode.OUT);
        org.hibernate.procedure.ProcedureOutputs callOutputs = call.getOutputs();
        boolean expectedResult = false;
        /* 
               Process all returned result sets before you access any output parameters.
             */
        org.hibernate.result.Output output;
        while ((output = callOutputs.getCurrent()) != null) {
            if (output.isResultSet()) {
                org.hibernate.result.ResultSetOutput resultSetOutput = (org.hibernate.result.ResultSetOutput) output;
                List<Item> result = resultSetOutput.getResultList();
                for (Item item : result) {
                    // ...
                    assertEquals(item.getId(), ITEM_ID);
                }
                assertEquals(result.size(), 1);
                expectedResult = true;
            }
            if (!callOutputs.goToNext())
                break;
        }
        /* 
               Access the output parameter value through the registration.
             */
        Long totalNumberOfItems = callOutputs.getOutputParameterValue(totalParameter);
        assertTrue(expectedResult);
        assertEquals(totalNumberOfItems, new Long(3));
        tx.commit();
        em.close();
    } finally {
        TM.rollback();
    }
}