• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

How to get multiple rows based on a single column value?

 
Ranch Hand
Posts: 622
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello
I need to retrieve rows from database based on a particular column value.



In this example, there are 4 rows with enrollmentNo as 1110. It runs successfully, but prints the same row data 4 times. (In the table enrollmentNo is the foreign key, does it affects the output? i guess no)
 
Kunal Lakhani
Ranch Hand
Posts: 622
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
What changes does my code need?
 
Kunal Lakhani
Ranch Hand
Posts: 622
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have tried with Native Sql, but the result is same.

Code


Output


The query gets the same rows 4 times, and it returns the same object (Verified after printing the list)

What changes does my code needs?
 
Bartender
Posts: 1682
7
Android Mac OS X IntelliJ IDE Spring Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Why you are getting duplicates is impossible to diagnose without knowing your relationships.

You could add



after you are done setting your restrictions. Be aware you will still contain all the duplicates in your result set, this does not reduce the amount of data fetched from the DB or what is sent over the wire. The removing of duplicates is handled on the java side.

Alternatively you can use projections. If you go that route be aware you have to specify all the property's you want fetched.

 
Kunal Lakhani
Ranch Hand
Posts: 622
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The table contains a foreign key , i.e enrollmentno. However, the query gets to know that there are 4 rows with the enrollment no given (and i need all those 4 rows), but it loads the same row(only a single row) four times
 
Kunal Lakhani
Ranch Hand
Posts: 622
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator



You could add

view plaincopy to clipboardprint?
cr.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);


after you are done setting your restrictions.



This gets me a single record with the given enrollment number. But i need all those 4 rows with the given enrollment Number

 
Bill Gorder
Bartender
Posts: 1682
7
Android Mac OS X IntelliJ IDE Spring Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The problem is the 4 rows you are getting back are the same entity (they are duplicates). They have the same persistent id whatever that is for your TuitionPaymentEntity. If that were not the case then you would have more than one result. You need to re-examine your relationships and mappings.
 
Kunal Lakhani
Ranch Hand
Posts: 622
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
My table Db Structure


The enrollment number used here (and in my code) refers to a primary key of another table(i.e ENROLLMENTNO column of studentpersonal table)

Here is the hbm.xml part for this structure


Is this the problem? Is Foreign key mapped in a different way?

I have not coded for any type of mappings like one-to one or one-to-many.
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
i think are creating single session object..so
Try to create new session object and  then apply criteria query................
You will get different result .

 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic