情况是这样的:
User表(userid,username,)
Role表(roleid,rolename,)
UserRole表(userid,roleid)就2个字段User和Role有对应的po,但UserRole表是联合主键,所以我没有映射。现在要做select username,rolename from User,Role,UserRole where...
这样的查询,用hql该如何写?
User表(userid,username,)
Role表(roleid,rolename,)
UserRole表(userid,roleid)就2个字段User和Role有对应的po,但UserRole表是联合主键,所以我没有映射。现在要做select username,rolename from User,Role,UserRole where...
这样的查询,用hql该如何写?
要不你就建一个UserRole Entity,该Entity和User,Role发生关系
要不你就使用原生SQL
select a.username,b.rolename from User a,Role b,UserRole c where a.userid=c.useridand b.roleid=c.roleid
<composite-id>
<key-property name="userid"/>
<key-property name="roleid"/>
</composite-id>必须对UserRole表进行映射,才能使用HQL。
* UserRoleId entity.
*
* @author MyEclipse Persistence Tools
*/public class UserRoleId implements java.io.Serializable { // Fields private Long userId;
private Long roleId; // Constructors /** default constructor */
public UserRoleId() {
} /** full constructor */
public UserRoleId(Long userId, Long roleId) {
this.userId = userId;
this.roleId = roleId;
} // Property accessors public Long getUserId() {
return this.userId;
} public void setUserId(Long userId) {
this.userId = userId;
} public Long getRoleId() {
return this.roleId;
} public void setRoleId(Long roleId) {
this.roleId = roleId;
} public boolean equals(Object other) {
if ((this == other))
return true;
if ((other == null))
return false;
if (!(other instanceof UserRoleId))
return false;
UserRoleId castOther = (UserRoleId) other; return ((this.getUserId() == castOther.getUserId()) || (this
.getUserId() != null
&& castOther.getUserId() != null && this.getUserId().equals(
castOther.getUserId())))
&& ((this.getRoleId() == castOther.getRoleId()) || (this
.getRoleId() != null
&& castOther.getRoleId() != null && this.getRoleId()
.equals(castOther.getRoleId())));
} public int hashCode() {
int result = 17; result = 37 * result
+ (getUserId() == null ? 0 : this.getUserId().hashCode());
result = 37 * result
+ (getRoleId() == null ? 0 : this.getRoleId().hashCode());
return result;
}}
package model;/**
* UserRole entity.
*
* @author MyEclipse Persistence Tools
*/public class UserRole implements java.io.Serializable { // Fields private UserRoleId id; // Constructors /** default constructor */
public UserRole() {
} /** full constructor */
public UserRole(UserRoleId id) {
this.id = id;
} // Property accessors public UserRoleId getId() {
return this.id;
} public void setId(UserRoleId id) {
this.id = id;
}}
xml文件:
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!--
Mapping file autogenerated by MyEclipse Persistence Tools
-->
<hibernate-mapping>
<class name="model.UserRole" table="T_USER_ROLE" schema="CHUANSHA">
<composite-id name="id" class="model.UserRoleId">
<key-property name="userId" type="java.lang.Long">
<column name="USER_ID" precision="10" scale="0" />
</key-property>
<key-property name="roleId" type="java.lang.Long">
<column name="ROLE_ID" precision="10" scale="0" />
</key-property>
</composite-id>
</class>
</hibernate-mapping>和传统的不太一样,接下来该怎么查询?
select a.username,b.rolename from User a,Role b,UserRole c where a.userid=c.id.userIdand b.roleid=c.id.roleId
比如:
<set name="userrole" table="UserRole" lazy="false">
<key column="userid"/>
<many-to-many class="×.User" column="roleid"/>
</set>
映射后,数据库就会自动生成这个具有联合主键的中间表。2.HQL查询语句可以根据这个中间表中的联合主键进行书写(不知道你where后面是什么条件,下面仅列出式样):
hql="SELECT u.username,r.rolename FROM User u,Role r join u.userrole ur where ……";
where u.userId = ur.id.userid
and r.roleId = ur.id.roleId