I have 3 tables in mysql
**User**
---------------------
`id` (auto increment)(PK)
`user_name`
`password`
`first_name`
`last_name`
`created_at`
`modified_at`
`email`
User_Role
-----------------------
`user_id` (PK)
`role_id` (PK)
Role
----------------------
`id`(PK)
`name`
The Mapping file User.hbm.xml
Expand|Select|Wrap|Line Numbers
- <?xml version="1.0" encoding="utf-8" ?>
- <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
- assembly="WorkOrder.Model"
- namespace="WorkOrder.Model">
- <class name="User" table="user">
- <id name="id">
- <generator class="identity"/>
- </id>
- <property name="UserName" column="user_name" type="String"/>
- <property name="Password" column="password" type="String"/>
- <property name="FirstName" column="first_name" type="String"/>
- <property name="LastName" column="last_name" type="String"/>
- <property name="CreatedAt" column="created_at" type="DateTime"/>
- <property name="ModifiedAt" column="modified_at" type="DateTime"/>
- <property name="Email" column="email" type="String"/>
- </class>
- </hibernate-mapping>
- The Mapping file User_Role.hbm.xml
- <?xml version="1.0" encoding="utf-8" ?>
- <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
- assembly="WorkOrder.Model"
- namespace="WorkOrder.Model">
- <class name="User_Role" table="user_role">
- <composite-id>
- <key-property name="UserId" column="id" type="Integer"></key-property>
- <key-property name="RoleId" column="role_id" type="Integer"></key-property>
- </composite-id>
- <many-to-one class="User" name="User_Id" column="User_Id" cascade="all"> </many-to-one>
- <many-to-one class="Role" name="Role_Id" column="Role_Id" cascade="all"> </many-to-one>
- </class>
- </hibernate-mapping>
- The Mapping file Role.hbm.xml
- <?xml version="1.0" encoding="utf-8" ?>
- <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
- assembly="WorkOrder.Model"
- namespace="WorkOrder.Model">
- <class name="Role" table="role">
- <id name="RoleId" column="id">
- <generator class="identity"/>
- </id>
- <property name="Name" column="name" length="64"/>
- </class>
- </hibernate-mapping>
Expand|Select|Wrap|Line Numbers
- SELECT u.*,r.name
- FROM user u
- JOIN user_role ur ON UR.user_id=u.id
- JOIN role r on UR.role_id=r.id
Expand|Select|Wrap|Line Numbers
- ICriteria Criteria = session.CreateCriteria(typeof(User));
- Criteria.CreateAlias("User_Role", "User_Role");
- Criteria.CreateAlias("User_Role.Role", "UsrRole");
- Criteria.Add(Expression.Eq("id", Uid));
can anyone please help?