is there a way to have customer query on a oneToMany?

deanhiller · **Joined:** Thu Sep 16, 2004 4:56 pm **Posts:** 80

I have this weird legacy schema where there is a Users table with a userid field(PK) and an objId field(not PK) and other fields. Then there is this Account table. Between them, there is a UserAcct table with a useracct2users field. Well, the objId and the useracct2users are used to join the useracct and user table(yes, quite weird).

I would like to have a User.getUserAcct() method that takes no parameters(ie. it doesn't take an EntityManager). Is there wa way to define the sql used when I have a Users object and a field of List<UserAcct>. It is still a onetoMany but I would just like to define the HQL for how to load it. Is there any way to do this?

thanks,
Dean

mmerder · **Posted:** Tue Nov 24, 2009 5:40 am

You could map the Id of your User as composite-id (userId, objId). That way hibernate will use it automatically when fetching associations.

deanhiller · **Joined:** Thu Sep 16, 2004 4:56 pm **Posts:** 80

wouldn't hibernate need to have the acctuser table to have userid and objid in that case though? We don't have userid in the acctuser table and I probably won't be able persuade them to change that.

mmerder · **Posted:** Tue Nov 24, 2009 12:08 pm

I thought these were the columns in this table. Could you please clarify your schema and post an example join-statement (how it is done at the moment).

deanhiller · **Joined:** Thu Sep 16, 2004 4:56 pm **Posts:** 80

acctuser table has 3 columns (accountno/acct2user is the PK)
accountno
acct2user (this has the same as user tables objid below...quite stupid).
data

user table has (userid is the PK)
userid(PK)
objid (this matches acctuser.acct2user above).
other columns

so, to join now, we do select * from User u, AcctUser au where u.acct2user = u.objid.

Most of their schema in the database was pretty good. just this last thing needs to be cleaned up 4/5 months down the road(probably ends up being 12 months before they get to it) but I happen to be heavily using these tables now and writing code on them so in 4/5 months I would prefer to not have to rewrite the code I am writing now and preferably have a

Code:

List<AcctUser> acctUsers = user.getAccountUsers();

Dean

mmerder · **Posted:** Tue Nov 24, 2009 12:34 pm

Looks like objid is your "real" primary key then. You should map this as id.

vn_javadeveloper · **Joined:** Wed Nov 25, 2009 5:34 am **Posts:** 4

Hi,

It looks like that you want to have the list of all the User and its detail info.

If so, I think you should select all the data in User table first and put them into a list.

Think that you now have a list of all the data.

Then you do some for loop like this:

For(int i=0; i< list.size(); i++)
{

//- "select * from userAcc where objId = list.get(i)"
you will need to code a function to save the return data if the select statement returns you a result.

//After that you will have a list of all the data.
}

deanhiller · **Joined:** Thu Sep 16, 2004 4:56 pm **Posts:** 80

heh, hard to when other tables correctly use userid as the primary key :(

oh well, I guess there is no solution but to go with the HQL I already have in place. I will just have to rewrite that code once it is cleaned up later :(.

thanks,
Dean

deanhiller · **Joined:** Thu Sep 16, 2004 4:56 pm **Posts:** 80

errrr, I should have said other tables correctly use userid as an FK so userid IS the PK in the user table because of that. It is just this one table that is screwed up and we plan on fixing later so I wanted to abstract it out somehow so I didn't have to.

My solution will be to have a user.getAccountUsers(EntityManager mgr) and when we clean it up later, I can just delete the param, but was trying to find a way to just have user.getAccountUsers() really.

vn_javadeveloper · **Joined:** Wed Nov 25, 2009 5:34 am **Posts:** 4

Hi,

Please write down the columns of all three tables, and also write down which column you want to select.

roshansn · **Joined:** Thu Nov 26, 2009 2:05 am **Posts:** 5

Code:

List<AcctUser> acctUsers = user.getAccountUsers();

execute above method by setting following hibernate property.

Code:

hibernate.show_sql=true

then you can see the generated sql in logs.

deanhiller · **Joined:** Thu Sep 16, 2004 4:56 pm **Posts:** 80

Code:

List<AcctUser> acctUsers = user.getAccountUsers();

uhm, this is what I would like to have as a method and do NOT have right now ;) because of the table design so turning on sql logging does not help. ps. Use log4jdbc instead of hibernate's logging...it is much much better as it gives the params and easy to hook up ;). that is what we use as it gives way more detail(you can even cut and paste sql into an sql program except for dates).

Quote:

Please write down the columns of all three tables, and also write down which column you want to select.

3 tables should be enough to get the picture...

acctuser table has 3 columns (accountno/acct2user is the PK)
accountno
acct2user (this has the same as user tables objid below...quite stupid).
data

user table has (userid is the PK)
userid(PK)
objid (this matches acctuser.acct2user above).
other columns

roles table
id (PK)
userid (FK)
role

As you can see, what I did was add this method to user :(....

Code:

public List<AcctUser> getAccountUsers(EntityManager mgr) {
    Query query = mgr.createNamedQuery("findAccountUsersByUser");
    query.setParameter("user", this);
    return query.getResultList();
}

where HQL is something like
   select au from Users u, AcctUser au where u.objid = au.acct2user and u = :user

What I wanted was a way to have a getAccountUsers method without passing the EntityManager in so I didn't have to refactor all the client code later that passes in the EntityManager.