Hibernate - select existence of one-to-many

In hibernate I have entities A and B. There's is a one-to-many from A to B (A -< B).

I need to write a query where I can select A (plus criteria) plus an additional column that reflects the existence, or not, of any B for each A (with some additional criteria on B)

So there might be one A with no Bs and another A with 2 Bs. In this case I want 2 rows back, one for each A with "no" for the first and "yes" for the second. I am not fussed what "no" and "yes" actually are, just so I can distinguish.

If I use a left outer join I will get more than one row per A in the many B case.

How I can I write this in HQL? Do I perhaps need a group by (but you can't group by entities I think)?

Additional: I also need to restrict the Bs that are relevant based on some conditions.

-------------Problems Reply------------

One possible solution is to use case clause:

select a, case when (a.bs is empty) then false else true end from A a

join with group by can be used as well, but, as you noticed, depending on your DBMS you may need to enumerate all properties of your entity in group by clause.

Category:hibernate Views:0 Time:2012-01-03

Related post

  • Hibernate - Selecting across multiple joins with collections 2009-06-01

    I'm having trouble getting a hibernate select to return a correctly populated object graph, when the select contains joins across many collections. Eg: String sql = "select distinct changeset " + "from Changeset changeset " + "join fetch changeset.ch

  • (N)Hibernate Selecting Constants in Query 2008-12-08

    does anyone know if you can do something like this using the (N)Hibernate criteria api: Select 1 AS obj.Property0, obj.Property1, obj.Property2 from Class Baiscally I want to select a constant value for one of my properties in the query. I can do thi

  • Hibernate, select by id or unique column 2010-06-17

    I am using hibernate for Java, and I want to be able to select users by id or by name from the database. Nice thing about Hibernate is that it caches the result by id, but I seem to be unable to make it cache by name. static Session openSession = Fac

  • Hibernate select one to many help 2011-03-23

    I have the following classes mapped as one to many: Reader and book when one reader can hold more then one book: Book: @Entity @Table(name = "book") public class Book implements Serializable{ private static final long serialVersionUID = 1L; private L

  • java hibernate: selecting the discriminator column in polymorphic hql query 2011-04-07

    In hibernate, I want to select the discriminator value. Something like select discriminator, id, name, age from Animal The idea is to send the result of this query to the client side, so that I can display a different icon based on the value of the d

  • hibernate select all projections group by 2011-07-13

    In hibernate Criteria specification, if I want to group property in table, I must use Projections.groupProperty. The problem is I can't select all the field in the table. What I want to is: SELECT * FROM entity GROUP BY field If i use group in hibern

  • Hibernate Select Top and Bottom n Rows with Criteria 2011-07-31

    Let's say I have two tables, Books and Reviews. Reviews has a column, stars, that can have a value between 1 and 5. A Book can have many Reviews. How would I select all books such that only the top and bottom 3 reviews for each book are returned (ins

  • Why is Hibernate selecting same columns 4 times? Help with Mapping? 2011-08-25

    My classes look like these. Why does the same column get selected 4 times? What is problem with the mapping? @Entity @Table(name="CLIENTS") public class Client implements Serializable { @Id @GeneratedValue @Column(name="GENERATED_ID") private Long id

  • one-to-many: making Hibernate select the reference's id instead of joining it 2011-08-25

    I have two classes stored in my database using Hibernate. Let's call them Container and Item. Item has a one-to-many relation to Container: @entity(name = "containers") public class Container { @Id @GeneratedValue private long id; } @entity(name = "i

  • Hibernate selecting from multiple mapped entities? 2011-09-21

    I'm trying to figure out how Hibernate handles the following situation: Say I have entities A, and B properly mapped in an hbm file. If I write an HQL query the selects from both of them (e.g. from A, B where ...) what is returned? I assume it cannot

  • Hibernate: SELECT in UPDATE 2011-10-01

    I need do something like that: session.getTransaction().begin(); session.createQuery("update Pack p set p.book = (select b from Book b where ...) where p.id = :id") .setLong("id", pack.getId()) .executeUpdate(); session.getTransaction().commit(); And

  • jpa hibernate - select statement pulls old data 2012-01-31

    I am using jpa with hibernate to retrieve data objects with a MySql (InnoDB) database. Here is the workflow of my application. User queries results User modifies one of the results The database "inactivates" the current row and runs an insert for the

  • Hibernate: Select N rows, but with only unique values in one column 2012-02-01

    I want to implement this with Hibernate criteria. Let's say I have data like(from the link): ID Name City Birthyear 1 Egon Spengler New York 1957 2 Mac Taylor New York 1955 3 Sarah Connor Los Angeles 1959 4 Jean-Luc Picard La Barre 2305 5 Ellen Riple

  • Hibernate select query with different where clause 2012-02-20

    This might be quite simple but I have spent hours in Google but couldn't find a relevant answer. I am trying to put the below logic into hibernate but i am not able to figure out how. This is my setup: Mysql database with 2 tables T1(ID,C1,C2) and T2

  • Hibernate select query returns nothing 2012-02-23

    I got a little issue with hibernate since hibernate doesn't accept a normal sql query syntax. When i send my query with a select statement which should return an exact integer of 37 to the database i get nothing in return instead. This is the query i

  • Hibernate selecting all rows of a table (using .* ) join with multiple tables, giving Exception 2012-04-06

    Is possible with Hibernate do this? select A.something, B.something, C.something, D.something , E.* from A LEFT OUTER JOIN B on A.id = B.id_fk LEFT OUTER JOIN C ON B.id = C.id_fk LEFT OUTER JOIN D ON C.id = D.id_fk LEFT OUTER JOIN E ON A.abc = E.abc;

  • Before Insert in many-to-many table hibernate selects to many columns 2012-04-06

    I've got two Entities Customer and Product with relation many-to-many. ` @ManyToMany(cascade = CascadeType.MERGE, fetch=FetchType.LAZY) @JoinTable( name = "customer_products", joinColumns={@JoinColumn(name="customer_id")}, inverseJoinColumns = {@Join

  • JPA / Hibernate Select Column Subset on Join 2008-11-01

    In SQL it is easy to do a join and only return the columns you want from the joined table. What is the best way to map this in JPA / Hibernate? For example, there is a Folder entity mapped to the EMAIL_FOLDER and an Email entity mapped to the EMAIL t

  • Hibernate select groupProperty , rowCount with rowCount > n? 2011-03-24

    Sorry if it is a dumb question but I've stuck with this problem for a whole afternoon but cannot find a solution because I'm not skilled with complicated SQL : I want to find "Top n message-sending users from a table with msg sent count > threshol

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.135 (s). 11 q(s)