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

Example 1 with ItemSummary

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

Example 2 with ItemSummary

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

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

the class NativeQueries method executeQueries.

@Test
public void executeQueries() throws Exception {
    TestDataCategoriesItems testData = storeTestData();
    UserTransaction tx = TM.getUserTransaction();
    try {
        tx.begin();
        EntityManager em = JPA.createEntityManager();
        {
            // Simple SQL projection
            Query query = em.createNativeQuery("select NAME, AUCTIONEND from {h-schema}ITEM");
            List<Object[]> result = query.getResultList();
            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
            Query query = em.createNativeQuery("select * from ITEM", Item.class);
            List<Item> result = query.getResultList();
            assertEquals(result.size(), 3);
            assertNotNull(result.get(0));
        }
        em.clear();
        {
            // Positional parameter binding
            Long ITEM_ID = testData.items.getFirstId();
            Query query = em.createNativeQuery("select * from ITEM where ID = ?", Item.class);
            // Starts at one!
            query.setParameter(1, ITEM_ID);
            List<Item> result = query.getResultList();
            assertEquals(result.size(), 1);
            assertEquals(result.get(0).getId(), ITEM_ID);
        }
        em.clear();
        {
            // Named parameter binding
            Long ITEM_ID = testData.items.getFirstId();
            Query query = em.createNativeQuery("select * from ITEM where ID = :id", Item.class);
            query.setParameter("id", ITEM_ID);
            List<Item> result = query.getResultList();
            assertEquals(result.size(), 1);
            assertEquals(result.get(0).getId(), ITEM_ID);
        }
        em.clear();
        {
            // Automatic marshaling of resultset to entity class with aliases
            Query query = em.createNativeQuery("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", "ItemResult");
            List<Item> result = query.getResultList();
            assertEquals(result.size(), 3);
            assertNotNull(result.get(0));
        }
        em.clear();
        {
            // Automatic marshaling of resultset to entity class with aliases (externalized)
            Query query = em.createNativeQuery("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", "ExternalizedItemResult");
            List<Item> result = query.getResultList();
            assertEquals(result.size(), 3);
            assertNotNull(result.get(0));
        }
        em.clear();
        {
            // Automatic marshaling of resultset to several mapped entity classes
            Query query = em.createNativeQuery("select " + "i.ID as ITEM_ID, " + "i.NAME, " + "i.CREATEDON, " + "i.AUCTIONEND, " + "i.AUCTIONTYPE, " + "i.APPROVED, " + "i.BUYNOWPRICE, " + "i.SELLER_ID, " + "u.ID as USER_ID, " + "u.USERNAME, " + "u.FIRSTNAME, " + "u.LASTNAME, " + "u.ACTIVATED, " + "u.STREET, " + "u.ZIPCODE, " + "u.CITY " + "from ITEM i join USERS u on u.ID = i.SELLER_ID", "ItemSellerResult");
            List<Object[]> result = query.getResultList();
            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
            Query query = em.createNativeQuery("select " + "u.ID, " + "u.USERNAME, " + "u.FIRSTNAME, " + "u.LASTNAME, " + "u.ACTIVATED, " + "u.STREET as USER_STREET, " + "u.ZIPCODE as USER_ZIPCODE, " + "u.CITY as USER_CITY " + "from USERS u", "UserResult");
            List<User> result = query.getResultList();
            assertEquals(result.size(), 3);
            assertNotNull(result.get(0));
        }
        em.clear();
        {
            // Automatic marshaling of resultset to entity class and additional column
            Query query = em.createNativeQuery("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", "ItemBidResult");
            List<Object[]> result = query.getResultList();
            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
            Query query = em.createNativeQuery("select ID, NAME, AUCTIONEND from ITEM", "ItemSummaryResult");
            List<ItemSummary> result = query.getResultList();
            assertTrue(result.get(0) instanceof ItemSummary);
            assertEquals(result.size(), 3);
        }
        em.clear();
        {
            // Automatic marshaling of resultset to entity class, constructor, additional column
            Query query = em.createNativeQuery("select " + "u.*, " + "i.ID as ITEM_ID, i.NAME as ITEM_NAME, i.AUCTIONEND as ITEM_AUCTIONEND, " + "count(b.ID) as NUM_OF_BIDS " + "from ITEM i " + "join USERS u on u.ID = i.SELLER_ID " + "left join BID b on b.ITEM_ID = i.ID " + "group by u.ID, u.USERNAME, u.FIRSTNAME, u.LASTNAME, " + "u.ACTIVATED, u.STREET, u.ZIPCODE, u.CITY, " + "ITEM_ID, ITEM_NAME, ITEM_AUCTIONEND", "SellerItemSummaryResult");
            List<Object[]> result = query.getResultList();
            for (Object[] tuple : result) {
                // Wrong order of results, Hibernate issue HHH-8678!
                assertTrue(tuple[0] instanceof User);
                assertTrue(tuple[1] instanceof BigInteger);
                assertTrue(tuple[2] instanceof ItemSummary);
            }
            assertEquals(result.size(), 3);
        }
        em.clear();
        tx.commit();
        em.close();
    } finally {
        TM.rollback();
    }
}