No results retrieved from database view using Java

Question

I have a view named V_ENTITY in my database which is populated with a few entries. Using SQL scrapbook in Eclipse I can easily do queries which return desired results.

However, when I try to retrieve the entries from my DAO class, I get nothing. My count returns 0 as number of entries, while there are 7 of them, which I can fetch with native SQL queries via scrapbook.

I have created a separate entity for that particular view:

@Entity
@Table(name = "V_ENTITY")
public class ViewEntity {
    ...
}

I use JPA2 CriteriaQuery API for dynamic queries I do. Here is the example for count that is bugging me:

    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<ViewEntity> transactionQuery = cb.createQuery(ViewEntity.class);
    Root<ViewEntity> ViewEntityRoot = transactionQuery.from(ViewEntity.class);

    Predicate[] predicates = new Predicate[<someSize>]
    predicates[<someIndex>] = cb.equal(
        root.get("foreignName").as(String.class),
        "%" + foreignName + "%"
    )

    CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
    countQuery.select(cb.count(ViewEntityRoot));
    countQuery.where(predicates);

    TypedQuery<Long> finalQuery = entityManager.createQuery(countQuery);
    return finalQuery.getSingleResult().intValue();

Can anyone help me find a flaw in my code/thinking?

I'm not familiar with CriteriaQuery, nor with ScrapBook. But my first idea is: Did you commit your values to the database? As long as they're not commited, they're only visible for the user who inserted the values. — Michaël Benjamin Saerens, Nov 12 '13 at 11:25
@Templar, please post your comment as an answer, I found the solution with your advice. — ioreskovic, Nov 12 '13 at 12:30

score 2 · Accepted Answer · answered Nov 12 '13 at 12:52

2

Use the <property name="show_sql">true</property> property to show us the generated sql pls

answered Nov 12 '13 at 12:52

Lukas Eichler

5,689
1
24
43

No results retrieved from database view using Java

1 Answers1