Examples with TypedQuery javax.persistence.TypedQuery used on opensource projects

Example 31 with TypedQuery

use of javax.persistence.TypedQuery in project microservices by pwillhan.

the class CreateExecuteQueries method createTypedQueries.

@Test
public void createTypedQueries() throws Exception {
    TestDataCategoriesItems testData = storeTestData();
    Long ITEM_ID = testData.items.getFirstId();
    UserTransaction tx = TM.getUserTransaction();
    try {
        tx.begin();
        EntityManager em = JPA.createEntityManager();
        {
            Query query = em.createQuery("select i from Item i where i.id = :id").setParameter("id", ITEM_ID);
            Item result = (Item) query.getSingleResult();
            assertEquals(result.getId(), ITEM_ID);
        }
        {
            TypedQuery<Item> query = em.createQuery("select i from Item i where i.id = :id", Item.class).setParameter("id", ITEM_ID);
            // No cast needed!
            Item result = query.getSingleResult();
            assertEquals(result.getId(), ITEM_ID);
        }
        {
            CriteriaBuilder cb = em.getCriteriaBuilder();
            CriteriaQuery<Item> criteria = cb.createQuery(Item.class);
            Root<Item> i = criteria.from(Item.class);
            criteria.select(i).where(cb.equal(i.get("id"), ITEM_ID));
            TypedQuery<Item> query = em.createQuery(criteria);
            // No cast needed!
            Item result = query.getSingleResult();
            assertEquals(result.getId(), ITEM_ID);
        }
        tx.commit();
        em.close();
    } finally {
        TM.rollback();
    }
}

Example 32 with TypedQuery

use of javax.persistence.TypedQuery in project microservices by pwillhan.

the class CreateExecuteQueries method namedQueries.

@Test(groups = { "H2", "ORACLE", "MYSQL" })
public void namedQueries() 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);
        {
            Query query = em.createNamedQuery("findItems");
            assertEquals(query.getResultList().size(), 3);
        }
        {
            TypedQuery<Item> query = em.createNamedQuery("findItemById", Item.class);
            query.setParameter("id", ITEM_ID);
            Item result = query.getSingleResult();
            assertEquals(result.getId(), ITEM_ID);
        }
        {
            org.hibernate.Query query = session.getNamedQuery("findItems");
            assertEquals(query.list().size(), 3);
        }
        {
            Query query = em.createNamedQuery("findItemsSQL");
            List<Item> items = query.getResultList();
            assertEquals(items.size(), 3);
            assertEquals(items.get(0).getId(), ITEM_ID);
        }
        {
            Query query = em.createNamedQuery("findItemsOrderByName");
            List<Item> items = query.getResultList();
            assertEquals(items.size(), 3);
            assertEquals(items.get(2).getId(), ITEM_ID);
        }
        tx.commit();
        em.close();
    } finally {
        TM.rollback();
    }
}

Example 33 with TypedQuery

use of javax.persistence.TypedQuery in project microservices by pwillhan.

the class CreateExecuteQueries method executeQueries.

@Test
public void executeQueries() throws Exception {
    TestDataCategoriesItems testData = storeTestData();
    Long ITEM_ID = testData.items.getFirstId();
    UserTransaction tx = TM.getUserTransaction();
    try {
        tx.begin();
        EntityManager em = JPA.createEntityManager();
        {
            // Get a list
            Query query = em.createQuery("select i from Item i");
            List<Item> items = query.getResultList();
            assertEquals(items.size(), 3);
        }
        {
            // Get a list of scalar values
            Query query = em.createQuery("select i.name from Item i");
            List<String> itemNames = query.getResultList();
            assertEquals(itemNames.size(), 3);
        }
        {
            // Single result
            TypedQuery<Item> query = em.createQuery("select i from Item i where i.id = :id", Item.class).setParameter("id", ITEM_ID);
            Item item = query.getSingleResult();
            assertEquals(item.getId(), ITEM_ID);
        }
        {
            // Single scalar result
            TypedQuery<String> query = em.createQuery("select i.name from Item i where i.id = :id", String.class).setParameter("id", ITEM_ID);
            String itemName = query.getSingleResult();
            assertEquals(em.find(Item.class, ITEM_ID).getName(), itemName);
        }
        {
            // No (single) result
            boolean gotException = false;
            try {
                TypedQuery<Item> query = em.createQuery("select i from Item i where i.id = :id", Item.class).setParameter("id", 1234l);
                Item item = query.getSingleResult();
            // ...
            } catch (NoResultException ex) {
                // ...
                gotException = true;
            }
            assertTrue(gotException);
        }
        {
            // Not a unique result
            boolean gotException = false;
            try {
                Query query = em.createQuery("select i from Item i where name like '%a%'");
                Item item = (Item) query.getSingleResult();
            // ...
            } catch (NonUniqueResultException ex) {
                // ...
                gotException = true;
            }
            assertTrue(gotException);
        }
        {
            // Scrolling with a database cursor
            Session session = em.unwrap(Session.class);
            org.hibernate.Query query = session.createQuery("select i from Item i order by i.id asc");
            org.hibernate.ScrollableResults cursor = query.scroll(org.hibernate.ScrollMode.SCROLL_INSENSITIVE);
            // Jump to third result row
            cursor.setRowNumber(2);
            // Get first "column"
            Item item = (Item) cursor.get(0);
            // Required!
            cursor.close();
            assertEquals(item.getName(), "Baz");
        }
        {
            // Iterating through a result
            Session session = em.unwrap(Session.class);
            org.hibernate.Query query = session.createQuery("select i from Item i");
            int count = 0;
            // select ID from ITEM
            Iterator<Item> it = query.iterate();
            while (it.hasNext()) {
                // select * from ITEM where ID = ?
                Item next = it.next();
                // ...
                count++;
            }
            // Iterator must be closed, either when the Session
            // is closed or manually:
            Hibernate.close(it);
            assertEquals(count, 3);
        }
        tx.commit();
        em.close();
    } finally {
        TM.rollback();
    }
}

Example 34 with TypedQuery

use of javax.persistence.TypedQuery 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 35 with TypedQuery

use of javax.persistence.TypedQuery in project opencast by opencast.

the class SearchServiceDatabaseImpl method populateSeriesData.

private void populateSeriesData() throws SearchServiceDatabaseException {
    EntityManager em = null;
    EntityTransaction tx = null;
    try {
        em = emf.createEntityManager();
        tx = em.getTransaction();
        tx.begin();
        TypedQuery<SearchEntity> q = (TypedQuery<SearchEntity>) em.createNamedQuery("Search.getNoSeries");
        List<SearchEntity> seriesList = q.getResultList();
        for (SearchEntity series : seriesList) {
            String mpSeriesId = MediaPackageParser.getFromXml(series.getMediaPackageXML()).getSeries();
            if (StringUtils.isNotBlank(mpSeriesId) && !mpSeriesId.equals(series.getSeriesId())) {
                logger.info("Fixing missing series ID for episode {}, series is {}", series.getMediaPackageId(), mpSeriesId);
                series.setSeriesId(mpSeriesId);
                em.merge(series);
            }
        }
        tx.commit();
    } catch (Exception e) {
        logger.error("Could not update media package: {}", e.getMessage());
        if (tx.isActive()) {
            tx.rollback();
        }
        throw new SearchServiceDatabaseException(e);
    } finally {
        if (em != null)
            em.close();
    }
}