JPA Nullable JoinColumn

I have an entity:

public class Foo {
   @Id
   @GeneratedValue
   private Long id;

   private String username;

   @ManyToOne(cascade = { CascadeType.MERGE }, fetch = FetchType.LAZY, optional = true)
   @JoinColumn(name = "ParentID", nullable = true)
   private Foo parent;

   // other fields
}

With this kind of relationship, each Foo object has a parent, except the first Foo instance in the DB which has a NULL ParentID. When i try to create a query via criteria api and try to search for the first Foo object (null parent id) using any of it's properties (ID, username etc..), i get a:

javax.persistence.NoResultException: No entity found for query

How should the JoinColumn be implemented in this case? How should i get the first Foo object in the DB which has a null parent ID?

Thanks

Updated September 28 2011

What i am trying to achieve is to look lookfor all Foos with a username starting in "foo" and would like to use as separate object being returned then:

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder;
开发者_如何学PythonCriteriaQuery<FooDto> criteriaQuery = criteriaBuilder.createQuery(FooDto.class);
Root<Foo> foo = criteriaQuery.from(Foo.class);

criteriaQuery.multiselect(foo.get("id"), foo.get("username"), foo.get("parent").get("username"), foo.get("property1")
//other selected properties);

Predicate predicate = criteriaBuilder.conjunction();
predicate = criteriaBuilder.and(predicate, criteriaBuilder.like(foo.get("username").as(String.class), criteriaBuilder.parameter(String.class, "username")));
// other criteria

criteriaQuery.where(predicate);

TypedQuery<FooDto> typedQuery = entityManager.createQuery(criteriaQuery);
typedQuery.setParameter("username", "foo%");
// other parameters

// return result

assuming that the usernames are foo1, foo2 and foo3 wherein foo1 has a null parent, the result set will only return foo2 and foo3 even if the foo1 has a username that starts with the specified predicate. and also searching for foo1 alone will throw a

javax.persistence.NoResultException: No entity found for query 

Is there a way to still include foo1 with the rest of the results? or foo1 will always be a special case since i have to add a criteria specifying that the parent is null? Or perhaps ai have missed an option in the joinColumn to do so.

Thanks

UPDATED Thu Sep 29 13:03:41 PHT 2011 @mikku I have updated the criteria part of the post above (property1), because i think this is the part that is responsible for foo1 not to be included in the Result Set.

Here's a partial view of the generated query from the logs:

select 
    foo.id as id, 
    foo.username as username, 
    foo.password as password, 
    foo.ParentID as parentId, 
    foo_.username as parentUsername, 
    foo.SiteID as siteId, 
from FOO_table foo, FOO_table foo_ cross join Sites site2_ 
where foo.ParentID=foo_.id and foo.SiteID=site2_.id and 1=1 
and foo.username=? and site2_.remoteKey=? limit ?

and this wont return Foo1, obviously because username value is from foo_ which is why my original question is 'how should i get to Foo1'.

On the part that i have commented that ill use SelectCase and mix it up with your first reply, what i did is to add this part on the multiselect:

criteriaBuilder
   .selectCase()
      .when(criteriaBuilder.isNotNull(agent.get("parent")), agent.get("parent").get("username"))
      .otherwise(criteriaBuilder.literal("")),

replacing the

foo.get("parent").get("username")

However this wont be able to get Foo1 as well. My Last resort though inefficient would probably check if the parameters are of Foo1 and create a criteriaQuery specifying a literal value for username, and using the default query otherwise.

Suggestions/alternatives are very much appreciated.

---

EDIT:

In your edited question nullable joincolumn is not problem - missing construction of FooDto, multiselect etc are. You can achieve your goal with following:

Querying:

    /**
     * Example data:
     * FOO
     * |id|username|property1|parentid|
     * | 1| foo    |  someval|    null|<= in result
     * | 2| fooBoo | someval2|       1|<= in result
     * | 3|somename| someval3|    null|
     */
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<FooDto> c = cb.createQuery(FooDto.class);
    Root<Foo> fooRoot = c.from(Foo.class);
    Predicate predicate = cb.like(fooRoot.get("username").as(String.class),
                                  cb.parameter(String.class, "usernameParam"));

    c.select(
            cb.construct(FooDto.class,
                         fooRoot.get("id"),
                         fooRoot.get("username"),
                         fooRoot.get("property1")))
    .where(predicate);

    TypedQuery<FooDto> fooQuery = em.createQuery(c);
    fooQuery.setParameter("usernameParam", "foo%");

    List<FooDto> results = fooQuery.getResultList();

Object to hold data:

public class FooDto {
    private final long id;
    private final String userName;
    private final String property1;
    //you need constructor that matches to type and order of arguments in cb.construct
    public FooDto(long id, String userName, String property1) {
        this.id = id;
        this.userName = userName;
        this.property1 = property1;
    }

    public long getId() { return id; }
    public String getUserName() { return userName; }
    public String getProperty1() { return property1; }

}