Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hello ,

Can anyone please tell me how do solve this.

I am using hiberante to query from a legacy database model.

Hibernate version: 3

I have two tables

Customers and Products

Customer table has the folowing columns
cust Id, registration key, company name, and other customer details.

custId and registration key, uniquely identify each row in Customers table.

Products table has
cust Id, inet Id, Product Id.

Note: Cust Id is different from inetId. inetId is like userId

A user Id in Products table has multiple customers.

example data in Products table

inetId productId custId
aaa 16 5475
aaa 125 5475
aaa 17 5415
aaa 149 1146

sample data in Customers table

custId regKey companyName startDate ...
5475 abcdef yyy 1/2/3
5475 abc123 yyy 1/2/3
5475 def123 yyy 1/2/3

5415 xyz123 zzz 1/2/3
5415 mno123 zzz 1/2/3

1146 pqr123 abc 1/2/3




I have CustomerDetails and CustomerProducts POJO as below

CustomerDetails implements Serializable {

private custId;
private regKey;
private companyName;
etc...

/getters and setters

}

CustomerProducts implements Serializable{

private custId;
private inetId;
private productId;

Set custDetailsSet= new HashSet();

// setters and getters
}


Mapping documents:

my hbm file for Products table is as below
<class name="CustomerProducts" table="00003" lazy="false" mutable="false" >


<composite-id>
<key-property name="custId" type="integer"
column="cst_id"/>
</composite-id>

<property name="inetId" type="string" column="inet_id"/>
<property name="productId" type="integer" column="prd_id"/>

<set name="custDetailsSet" lazy="false" table="00001" schema="dbo">
<key>
<column name="cst_id" not-null="true" sql-type="integer"/>
</key>
<one-to-many class="CustomerDetails"/>
</set>

</class>

hbm file for CustomerDetails is as shown below

<class name="CustomerDetails" table="00001" >

<composite-id>
<key-property name="customerId" type="integer"
column="cst_id"/>
<key-property name="regKey" type="string"
column="cst_reg_key"/>
</composite-id>


<property name="companyName" type="string" column="cpny"/>
<property name="seatsPurchased" type="integer" column="no_seats_purch"/>
<property name="seatsUsed" type="integer" column="seats_used"/>
<property name="startDate" type="timestamp" column="cntrc_start_dt"/>
<property name="endDate" type="timestamp" column="cntrc_end_dt"/>
<property name="entBypass" type="string" column="f_ent_bypass"/>
<property name="bypassStartDate" type="timestamp" column="d_bypass_start"/>
<property name="bypassEndDate" type="timestamp" column="d_bypass_end"/>

</class>


What I want to do is when I give a inet id, I want to all the customers with all their details.

example
if i say inetId as 'aaa', I would like to get the following result set, not interested in getting the productId now. I mean I want to get a distinct customer from Products table and get all details for that customer from Customers table.

custId regKey companyName Date inetId
5475 abcdef yyy 1/2/3 aaa
5475 abc123 yyy 1/2/3 aaa
5475 def123 yyy 1/2/3 aaa

5415 xyz123 zzz 1/2/3 aaa
5415 mno123 zzz 1/2/3 aaa

1146 pqr123 abc 1/2/3 aaa



I am doing the following query

from CustomerProducts cp where cp.inetId = 'aaa'

The result I am getting is

custId regKey companyName Date inetId
5475 abcdef yyy 1/2/3 aaa
5475 abc123 yyy 1/2/3 aaa
5475 def123 yyy 1/2/3 aaa

5475 abcdef yyy 1/2/3 aaa
5475 abc123 yyy 1/2/3 aaa
5475 def123 yyy 1/2/3 aaa

5415 xyz123 zzz 1/2/3 aaa
5415 mno123 zzz 1/2/3 aaa

1146 pqr123 abc 1/2/3 aaa

The customer data is repeating for each different product Id. Can anyone please tell me how do I specify to select only distinct custId from Products table.

Thanks in advance and sorry for the long post.

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:

Name and version of the database you are using:

The generated SQL (show_sql=true):

Hibernate: select top 5 customerpr0_.cst_id as cst1_, customerpr0_.inet_id as inet2_2_, customerpr0_.prd_id as prd3_2_ from dbo.inv00003 customerpr0_ where customerpr0_.inet_id='aaauser'

[5/8/06 12:06:00:700 EDT] 22890ce2 SystemOut O Hibernate: select custdetail0_.cst_id as cst1_1_, custdetail0_.cst_reg_key as cst2_1_, custdetail0_.cst_id as cst1_0_, custdetail0_.cst_reg_key as cst2_0_, custdetail0_.i_grp_prd as i3_0_0_, custdetail0_.no_seats_purch as no4_0_0_, custdetail0_.seats_used as seats5_0_0_, custdetail0_.cntrc_start_dt as cntrc6_0_0_, custdetail0_.cntrc_end_dt as cntrc7_0_0_, custdetail0_.f_ent_bypass as f8_0_0_, custdetail0_.d_bypass_start as d9_0_0_, custdetail0_.d_bypass_end as d10_0_0_, custdetail0_.cst_id as cst1_0_0_ from dbo.inv00001 custdetail0_ where custdetail0_.cst_id=?

Debug level Hibernate log excerpt:

This mapping file in your post is confusing



When you have only one column(or property) why do use composite-id element? Instead you can use <id> element.

Could you also post Java code how you are accessing information? I mean are you using HQL, SQL or API based queries..

{
PS : Please use code tags, it makes lot easier to understand the question
}

Even if I used <id> tag in my mapping file, it did not make any difference

i tried



Regarding the java code, actually we have a kind of framework for hibernate resultset pagination, and we pass the query to the frame work code and it obtains the results and paginates the result set. The frame work code is basically doing the followig to execute the query which is not anything fancy



Its the same

Could you try below snippet which uses Criteria instead of Query object. I guess there would be some typos - modify accordingly to your needs.

I am just curious to know, how does using Criteria help in getting distinct results? Anyways If I have to try this, I do not have the source code for the our framework code.

Thanks,