CODEWITHSUNDEEP
htmlsqljpaplayframeworkjpa-2.0
Daniel
Daniel

Reputation: 77

Join in play framework

i have never use jpa and i would like to ask if there is a join

if i do this 

String queryStringGet="SELECT c.* FROM Category c "+

"LEFT JOIN User us ON us.id = c.id "+

"+"WHERE c.user_id="+id;

List<Object[]> list = JPA.em().createNativeQuery(queryString).getResultList();

Works but comes out as an object, and i don't know how to list in the view :(

Since play.db.jpa.Model does not suport ON how do i do a join ? and how do i List the objects?

Thanks

Upvotes: 2

Views: 1422

Answers (3)

emt14
emt14

Reputation: 4896

Since you are using a native query, it will run correctly as long as the sql is valid on your database.

With a custom query, this code will return a list of array of objects with the implicit datatype. So if the first column you return is a varchar, object[0] will be a string. You will have to do the mapping to your object yourself

    List<Category> categories = query.getResultList();
    Category cat = null;
    for (Object[] objects : categories ) {
        if (cat == null)
            cat = new Category();
        cat.name = (String) objects[0];
        ...
    }

Upvotes: 1

mcls
mcls

Reputation: 9792

JPQL doesn't support joins. There is an explanation of how to do it here.

And you can get the results in a List like so:

String hql = "SELECT c FROM Category c "
           + "LEFT JOIN User us WITH us.id = c.id "
           + "WHERE c.user_id= :userId";
Query query = JPA.em().createQuery(hql, Categories.class);
query.setParameter("userId", userId);
List<Category> categories = query.getResultList();

Upvotes: 2

F3rr31r4
F3rr31r4

Reputation: 268

Try to read this ;)

http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/queryhql.html

Upvotes: 1

Related Questions