sql java queries

Question

I have a java object 'star' that consists of two columns, string name (the name of the star) and string List fans (the list of fans of this star). I'd like to persist this class using JPA1 or hibernate. I've done so using the annotation @collectionOfElements on the list. It works fine, and creates two tables.

Now I'd like to get all stars whose fans are 'alice' or 'bob' or 'charlie'. How can I do that in the easiest way (only one query rather than 3, and without using 'OR' statements if possible), using jpa queries (hibernate if it's a must), and without retrieving the whole list of fans ?

Thanks

Why would you want to avoid using or? This is meant to be used in such cases. — Bastien Jansen
– Bastien Jansen, Commented Jan 29, 2013 at 20:08
If all my other requirements are met, I think I can live with a OR. — shkra19
– shkra19, Commented Jan 29, 2013 at 20:14

Bastien Jansen · Accepted Answer · 2013-01-29 20:16:59Z

1

The following query should help you:

select s.* from star s where s.fans.name in ('alice', 'bob', 'charlie')

answered Jan 29, 2013 at 20:16

Bastien Jansen

8,8462 gold badges39 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

shkra19 Over a year ago

This doesn't seem to work. The fans column doesn't exist, it's persisted in another table.

Bastien Jansen Over a year ago

It's kinda hard to guess without having your code. Could you provide entities and mappings? My example is written in JPAQL, so I assumed your Star object has a List<Fan> fans attribute.

Collectives™ on Stack Overflow

sql java queries

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related