Hi!

I am a newbie to Hibernate.

Could please someone translate me the following query into HQL.

SELECT ofsuser.id,
permission.id
FROM permission
INNER JOIN rolepermission ON (permission.id = rolepermission.permissionid)
INNER JOIN role ON (rolepermission.roleid = role.id)
INNER JOIN userrole ON (role.id = userrole.roleid)
INNER JOIN ofsuser ON (userrole.userid = ofsuser.id)
WHERE
(
(ofsuser.id = ?)
)

Thanks a million!
- Markus

hummm.... without your mapping file, it's going to be hard.
Are your sure you need an ORM?
Now you need to think in "object", so reformulate your question :
- what do you want to do
- what is your domain model (part of mapping file you are using)

wow! thanks for the immediate reply. incredible.

here is my file:

<class name="PermissionDTO" table="permission">
<id column="id" name="id" type="long">
<generator class="native"/>
</id>
<property name="directory" type="string"/>
<property name="filter" type="string"/>
<property name="download" not-null="true" type="true_false"/>
<property name="upload" not-null="true" type="true_false"/>
</class>


<class name="UserDTO" table="ofsuser">
<id column="id" name="id" type="long">
<generator class="native"/>
</id>
<property name="email" type="string"/>
<property name="password" type="string"/>
</class>


<class name="RolePermissionDTO" table="rolepermission">
<id column="roleid" name="roleid" type="long">
<generator class="native"/>
</id>
<property name="permissionid" type="long"/>
</class>


<class name="UserRoleDTO" table="userrole">
<id column="userid" name="userid" type="long">
<generator class="native"/>
</id>
<property name="roleid" type="long"/>
</class>


<class name="RoleDTO" table="role">
<id column="id" name="id" type="long">
<generator class="native"/>
</id>
<property name="description" type="string"/>
</class>

i add some explanation to facilitate understanding:

A user may have several roles. Each role again has its permissions.

user > stores the users
role > role id and description of the role
userrole > MAPS users to roles

permissions > stores the permissions
roleperm > MAPS roles to permissions

hope that makes it clearer.

- Markus

and what do you want to do?

do you know that:
- hibernate is a persistence tool, as i can see you are thinking in term of DTO, which are only used to tranfer data --> if this is your only goal, you should consider using JDBC
- hibernate is an object/relational mapping tool, that is to say it can map a schema into a graph of objects, with associations... i don't see any association.

You should consider reading the doc, understand what is Hibernate, and you will be surprised how powerfull it is...

Try some associations with your example, it's a great way to learn.... then we'll show you how easy is HQL.

Anthony

I want to have all PermissionDTOs of a user, i.e. all permissions of a user.

Thanks for the tip with the documentation.. still I would appreciate your help if you know a solution.

- Markus

Don't name your peristent classes DTOs, you don't need "data transfer objects" anymore with POJO domain classes.

The solution for your problem is "aUser.getPermissions()" or "select p from User u join u.permissions as p where u = :user". Both require association mappings, study the documentation. Don't jump in right in the middle.

hi,

thanks for the quick reply.

could you please explain "u.permissions" in more detail? table user does not have such an attribute, so i get an error when i try to execute that statement.

- markus

just a tip and then read the doc ok?

you have many permissions for one user
this is called a one to many association:
- one(user) to many(permission)
- or many(permission) to one(user)

in other words in your class user you can have a property = collection of permission.
Now we are talking about real ORM features.
If your mapping files are well written, you'll just have to call myUser.getPermissions() to retrieve all permissions associated with your user BUT WITHOUT explicitly doing it in SQL ... magic? wait and read the docs and you'll discover much more magic in hibernate.

well, if it does not cause too much circumstances, i would appreciate if you could simply write me what i have to add so that it works.

thanks a lot..
- markus

p.s. i very kindly ask everyone who subsequently answers not to refer to the documentation again. please simply give me the answer -- thanks!

You have to add association mappings.

Oh, and your other mappings are completely broken too. You don't map "ID" properties. Read the documentation.

Hi!

I added many-to-many association now to OFSUser and Role.

But still I have not figured out how to get the permissions *directly* via OFSUser. Please help!

<class name="OFSUser" table="ofsuser">
<id column="id" name="id" type="long">
<generator class="native"/>
</id>
<property name="email" type="string"/>
<property name="password" type="string"/>

<set name="roles" table="userrole" cascade="all">
<key column="userid"/>
<many-to-many column="roleid" class="edu.samal.ofs.database.dto.Role"/>
</set>
</class>


<class name="Role" table="role">
<id column="id" name="id" type="long">
<generator class="native"/>
</id>
<property name="description" type="string"/>

<set name="permissions" table="rolepermissions" cascade="all">
<key column="roleid"/>
<many-to-many column="permissionid" class="edu.samal.ofs.database.dto.Permission"/>
</set>
</class>

Tack,
- Markus

select p from OFSUser u join u.roles as r join r.permissions as p where u = :user

Yes, it works!

Danke f