Results 1 to 2 of 2
  1. #1
    GlWy is offline Member
    Join Date
    Jan 2011
    Posts
    10
    Rep Power
    0

    Default hibernate criteria with relations

    Hi,

    I'm trying to make a criteria in hibernate or sql/hql query, but I don't succeed.
    This is the situation:
    there are 2 tables: A and B with a 1:n relationship (so A can have multiple B's).
    In B there is a foreign key to A (FK_A_ID, so with the ID of A) and a date field.

    I would like to get all ID's of A, witch have a date that is not from this year (2011).
    Example: B has 5 records :
    ID FK_A_ID date
    1 1 01-01-2011
    2 1 30-12-2010
    3 2 30-12-2010
    4 3 30-12-2010
    5 4 01-01-2010

    I would like to create a criteria (or sql/hql query) which gives me the ID's of A (or the FK_A_ID in B) for the records of B that have no date of this year.
    So in this example I would like to retrieve the FK_A_ID's 2 and 3, as A has a record in B of this year for ID's 1 and 4 of A.
    Suppose that A has no B's (and so no records in B of this year), then that ID of A should also be selected.

    Can anyone help me with this one?

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,224
    Rep Power
    20

    Default

    The SQL would be:
    SELECT b.FK_A_ID
    FROM A a LEFT OUTER JOIN B b ON a.id = b.FK_A_ID
    WHERE (b.date > (start of year) AND b.date < (end of year))
    OR b.date is null

    This should give you enough to build the criteria.

    The outer jon should give you all As, even those without a B. The WHERE clause should then give you all the ones with a b.date that is in the current year, and the ones where b.date is null (which will be the ones where A has no B).

Similar Threads

  1. Hibernate Criteria
    By stunaz in forum JDBC
    Replies: 0
    Last Post: 12-02-2008, 04:20 PM
  2. error with traverse a relations ship
    By darkbalder in forum Enterprise JavaBeans (EJB)
    Replies: 0
    Last Post: 12-11-2007, 06:25 PM
  3. Relations RCP 1.0.0
    By JavaBean in forum Java Software
    Replies: 0
    Last Post: 11-09-2007, 11:37 PM
  4. Relations RCP 1.0.0
    By JavaBean in forum Java Software
    Replies: 0
    Last Post: 11-08-2007, 05:23 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •