There are two table. One is dictionary table--DICT. Another is user definition table--USERS. The association of the two tables can be described by the follwoing SQL:
select a.username, a.password, a.role_code, b.name role_name
from USERS a, DICT b
where a.role_code=b.no and b.type='role_code'

Can the following tow tables be OR mapped by Hibernate? The following is database script, Hibernate VO classes and Mapping XML files.

The last Users.hbm.xml is wrong because it didn't reflect correct relationship. Who can fix it? Or we only can use HQL to realize?

//Mysql database script
CREATE TABLE `DICT` (
`ID` int(11) NOT NULL auto_increment,
`TYPE` varchar(20) default NULL,
`NO` varchar(10) default NULL,
`NAME` varchar(50) default NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `UK_TYPE_NO` (`TYPE`,`NO`),
KEY `TYPE` (`TYPE`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
insert into DICT(type,no,name) values ('role_code','00','Admin');
insert into DICT(type,no,name) values ('role_code','01','Buyer');
insert into DICT(type,no,name) values ('role_code','02','Seller');
insert into DICT(type,no,name) values ('gender','00','unknow');
insert into DICT(type,no,name) values ('gender','01','male');
insert into DICT(type,no,name) values ('gender','02','female');

CREATE TABLE USERS /*users table*/
(
USERNAME VARCHAR(15) NOT NULL PRIMARY KEY,
PASSWORD VARCHAR(20),
ROLE_CODE VARCHAR(5), /*00:admin, 01:buyer, 02:seller */
FREEZED VARCHAR(2), /*0:not freezed, 1:freezed */
UNITID int
)ENGINE=InnoDB DEFAULT CHARSET=latin1;
insert into USERS(username,password,role_code,freezed,unitid) values ('admin','0','00','0',1);

//vo.Dict.java
package vo;

/**
* Dict generated by hbm2java
*/

public class Dict implements java.io.Serializable {

// Fields
private Integer id;
private String type;
private String no;
private String name;

// Constructors

/** default constructor */
public Dict() {
}

/** minimal constructor */
public Dict(Integer id) {
this.id = id;
}

/** full constructor */
public Dict(Integer id, String type, String no, String name) {
this.id = id;
this.type = type;
this.no = no;
this.name = name;
}

// Property accessors
public Integer getId() {
return this.id;
}

public void setId(Integer id) {
this.id = id;
}

public String getType() {
return this.type;
}

public void setType(String type) {
this.type = type;
}

public String getNo() {
return this.No;
}

public void setNo(String no) {
this.no = no;
}

public String getName() {
return this.name;
}

public void setName(String name) {
this.name = name;
}
}

//Dict.hmb.xml
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Jul 21, 2008 3:30:05 PM by Hibernate Tools 3.1.0 beta1JBIDERC2 -->
<hibernate-mapping>
<class name="vo.Dict" table="DICT" catalog="report">
<id name="id" type="integer">
<column name="ID" />
<generator class="assigned" />
</id>
<property name="type" type="string">
<column name="TYPE" length="20" />
</property>
<property name="no" type="string">
<column name="NO" length="10" />
</property>
<property name="name" type="string">
<column name="NAME" length="50" />
</property>
</class>
</hibernate-mapping>

//vo.Users.java
public class Users implements java.io.Serializable {

// Fields

private String username;
private String password;
private Dict roleObj;
private String freezed;
private Integer unitid;

// Constructors

/** default constructor */
public Users() {
}

/** minimal constructor */
public Users(String username) {
this.username = username;
}

/** full constructor */
public Users(String username, String password, Dict roleObj, String freezed, Integer unitid) {
this.username = username;
this.password = password;
this.roleObj = roleObj;
this.freezed = freezed;
this.unitid = unitid;
}

// Property accessors
public String getUsername() {
return this.username;
}

public void setUsername(String username) {
this.username = username;
}

public String getPassword() {
return this.password;
}

public void setPassword(String password) {
this.password = password;
}

public Dict getRoleObj() {
return this.roleObj;
}

public void setRoleObj(Dict roleObj) {
this.roleObj = roleObj;
}

public String getFreezed() {
return this.freezed;
}

public void setFreezed(String freezed) {
this.freezed = freezed;
}

public Integer getUnitid() {
return this.unitid;
}

public void setUnitid(Integer unitid) {
this.unitid = unitid;
}
}

//Users.hbm.xml
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Jul 21, 2008 3:30:05 PM by Hibernate Tools 3.1.0 beta1JBIDERC2 -->
<hibernate-mapping>
<class name="vo.Users" table="USERS" catalog="report">
<id name="username" type="string">
<column name="USERNAME" length="15" />
<generator class="assigned" />
</id>
<property name="password" type="string">
<column name="PASSWORD" length="20" />
</property>
<property name="freezed" type="string">
<column name="FREEZED" length="2" />
</property>
<property name="unitid" type="integer">
<column name="UNITID" />
</property>
<many-to-one name="roleObj" class="vo.Dict" column="ROLE_CODE" unique="true"/>
</class>
</hibernate-mapping>

What is the key problem you are running into when mapping these classes? They don't appear to be problematic.

The last file of Users.hbm.xml is wrong because it didn't reflect correct relationship. I don't know how to fix it. Can you fix it?

-----------