How to restrict duplicacy when getting data from database in hibernate

Question

I am fetching data from database using hibernate. I have 3 tables in my db and i have created 3 entity classes for them. I am fetching results on the basis of two criterias profession of user and city of user.

Here is my DAO class

    @SuppressWarnings({ "unchecked", "null" })
public List<User> getUsersWithSameProfessionsById(long id) throws Exception {
    session = sessionFactory.openSession();
    String queryString = "SELECT DISTINCT a FROM Profession p1, Profession p2, Address a JOIN p2.user u WHERE p1.u_id=:id AND p2.profession=p1.profession AND a.uid=u.id";
    Query query = session.createQuery(queryString);
    query.setLong("id", id);
    query.setMaxResults(10);

    Set<User> foo = new HashSet<User>(query.list());

    Set<User> foo1 = new HashSet<User>();

    Query query1=null;
    if(professionList.size()<10){

        String queryString1 = "SELECT DISTINCT a FROM Address c1, Address c2, Address a JOIN c2.user u WHERE c1.uid=:id AND c2.city=c1.city AND a.uid=u.id";
        query1 = session.createQuery(queryString1);
        query1.setLong("id", id);

        foo1.addAll(query1.list());

    }

    List<User> professionList3 = new ArrayList<User>();
    Set<User> foo2 = new HashSet<User>();
    foo2.addAll(foo);
    foo2.addAll(foo1);
    professionList3.addAll(foo2);

    return professionList3;
}

Here i am fetching records from first query on the basis of profession but if results are less than 10 then i am running second query on the basis of city. But if results are matching with both criterias then i am getting duplicate results and i dont want it..i want if results have been come in first query then those results not come in second query. I am new in hql queries so please help me.

Thanx in advance

Thanx for your reply... but i am unable to join queries using NOT IN clause... can you do this please? — artle
– artle, Commented Aug 19, 2015 at 8:48

jozzy · Accepted Answer · 2015-08-19 09:04:32Z

2

If you don't want to tweak queries, you could take the following approach

Override equals and hashcode in User Object so that you can treat two user objects as same if their id's as same.

The use a Set to hold your data. Duplicates will be discarded. Finally return the set or simply transform the set to a list

list.addAll(set)

answered Aug 19, 2015 at 9:04

jozzy

2,9433 gold badges17 silver badges12 bronze badges

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

3 Comments

artle Over a year ago

but how will i get query result in set... now i am getting list of result like list= query.list();

jozzy Over a year ago

Set<User> foo = new HashSet<User>(yourList);

artle Over a year ago

thanx @jozzy its working but the results are not coming in sequence...i want results in sequence like the results from first query should be on top and if records are less than 10 then second query results should be in bottom.

FDirlikli · Accepted Answer · 2015-08-19 10:35:43Z

1

On top of jozzy's answer and comments you can use a TreeSet instead of a HashSet which is a SortedSet implementation and will keep your user records sorted.

So it should go like,

Set<User> foo = new TreeSet<User>(yourList);

answered Aug 19, 2015 at 10:35

FDirlikli

1951 silver badge4 bronze badges

1 Comment

artle Over a year ago

but after adding both results i am getting more than 10 results while i want only 10

Anudeep Gade · Accepted Answer · 2015-08-19 12:51:25Z

1

Seems you are selecting address "SELECT DISTINCT a ".. may be you meant "SELECT DISTINCT u". Assuming you are selecting users, after doing your first query you can collect the userIds in a list..

List<Long> alreadyFoundUserIds = new ArrayList<>();
for(User user : foo){
    alreadyFoundUserIds.add(user.getId()); 
}

Then in your second query you can exclude those users and fetch remaining (10 - foo.size() ).

   String queryString1 = "SELECT DISTINCT u FROM Address c1, Address c2, Address a JOIN c2.user u WHERE c1.uid=:id AND c2.city=c1.city AND a.uid=u.id
 AND u.id NOT IN (:alreadyFoundUserIds )   ";

        query1 = session.createQuery(queryString1);
        query1.setLong("id", id);
        query1.setParameterList("alreadyFoundUserIds ", alreadyFoundUserIds ); 
        query1.setMaxResults(10 - alreadyFoundUserIds.size());

answered Aug 19, 2015 at 12:51

Anudeep Gade

1,3951 gold badge9 silver badges18 bronze badges

5 Comments

artle Over a year ago

okay thanx for your reply..but how can i return only 10 records..i am getting more than 10 results in list

Anudeep Gade Over a year ago

No you cannot get more than 10, first query will return max 10, second query will return max 10-firstqueryResultSize..example if first query returns 3 , second query returns 10-3 = 7 , so total you will have 3 +7 = 10.

artle Over a year ago

actually i am using Set now and when i am passing my both lists to set then duplicacy is removed from results then results are less than 10

artle Over a year ago

i have one another problem... which id i am passing into url to gwt results i am getting that id record too in my results... how can i remove that?

Anudeep Gade Over a year ago

i think you have to post another question for GWT , its not related to hibernate,sql like this question. i have no idea about gwt.

Collectives™ on Stack Overflow

How to restrict duplicacy when getting data from database in hibernate

3 Answers 3

3 Comments

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related