Hi guys, i was wondering how do you map a collection of values in a 3-table relationship.. for example
if you have a users table, a user_role table, and a roles table..
a user can have multiple roles.
and for each role id like to know which users have that role.
my mapping files are below.. and u can see what im trying.. but im getting sql errors and its because of my formula attributes.. which i then removed since they are optional but then i get pretty much the same error..
unknown column in field list
Hibernate version:
3.05
Mapping documents:
my Users.hbm.xml
Code:
<class name="Users" table="users">
<id name="id" column="id" type="java.lang.Integer">
<generator class="native"/>
</id>
<property name="name" column="name" type="java.lang.String" not-null="true" />
<property name="pass" column="pass" type="java.lang.String" not-null="true" />
<property name="firstname" column="firstname" type="java.lang.String" not-null="true" />
<property name="lastname" column="lastname" type="java.lang.String" not-null="true" />
<property name="email" column="email" type="java.lang.String" not-null="true" />
<property name="cityid" column="cityId" not-null="true" type="java.lang.Integer"/>
<many-to-one
name="city"
column="cityId"
class="City"
not-null="true"
fetch="join" insert="false" update="false"/>
<map name="roles" inverse="true" optimistic-lock="false" >
<key column="userId" />
<map-key type="UserRoleKey" formula="UserRole.id"/>
<element type="java.lang.Integer" unique="true" column="roleId" />
</map>
</class>
My Roles.hbm.xml
Code:
<class name="Roles" table="roles">
<id name="id" column="id" type="java.lang.Integer">
<generator class="native" />
</id>
<property name="name" column="name" type="java.lang.String" not-null="true" />
<map name="users" table="user_role" >
<key column="roleId" />
<map-key-many-to-many column="userId" formula="Users.id" class="Users" />
<element type="java.lang.Integer" unique="true" formula="UserRole.id"/>
</map>
</class>
and my UserRole.hbm.xml
Code:
<class name="UserRole" table="user_role">
<composite-id name="id" class="UserRoleKey">
<key-many-to-one name="roleid" column="roleId" class="Roles" />
<key-many-to-one name="userid" column="userId" class="Users" />
</composite-id>
</class>
Code between sessionFactory.openSession() and session.close():Full stack trace of any exception that occurs:Code:
DEBUG http-8080-Processor25 org.hibernate.util.JDBCExceptionReporter - could not initialize a collection: [com.insaini.publogs.hibernate.tables.Users.roles#1] [select roles0_.userId as userId0_, roles0_.roleId as roleId0_, roles0_.idx as idx0_ from user_role roles0_ where roles0_.userId=?]
java.sql.SQLException: Unknown column 'roles0_.idx' in 'field list'
Name and version of the database you are using:
MySQL 5.0.15
The generated SQL (show_sql=true):
Debug level Hibernate log excerpt:
Login
FAQ
Search