我使用基于JPA2.0的Hibernate 3.6.10,创建了两个多对多关系的对象。
@Entity
@Table(name="Project")
public class Project implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Integer id;
private String topic;
@Lob
@Basic(fetch=FetchType.LAZY)
private String content;
@ManyToMany(fetch=FetchType.EAGER)
@JoinTable(name="TrProj_Area",joinColumns=@JoinColumn(name="TrProj_ID"), inverseJoinColumns=@JoinColumn(name="Area_ID"))
private Set<Area> areas = new HashSet<Area>();
//getters and setters
}
@Entity
@Table(name="Area")
public class Area implements Serializable{
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Integer id;
private String name;
@ManyToMany(mappedBy="areas")
private Set<Project> projects;
//getters and setters
}
Project实体有一个content属性,它是Lob和LazyFetch,当我创建这样的查询时:
select distinct o from Project o join o.areas a where a.id in(?,?)
它在MySQL 5.5上运行良好,但SQLServer2005则不然。SQLServer错误日志如下:
Error code 306, SQL state S0001: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator
我知道这个问题是由content属性引起的,似乎lazy fetch配置不起作用,文本列不能包含在select distinct语句中,但我无法将其从实体中删除,我该如何解决这个问题?
添加于20120722
我已经跟踪了MySQL(MySQL-connector-java-5.110-bin.jar作为驱动程序)在hibernate中生成的SQL,如下所示:
Hibernate:
select
distinct proj0_.id as id1_,
proj0_.content as content1_,
proj0_.topic as topic1_,
from
Project proj0_
inner join
Proj_Area areas1_
on proj0_.id=areas1_.Proj_ID
inner join
Area area2_
on areas1_.Area_ID=area2_.id
where
area2_.id in (
? , ?
)
order by
proj0_.id desc limit ?
这是由SQLServer2005(sqljdbc4.jar作为驱动程序)生成的,我担心错误是由组通过语句引起的
Hibernate:
WITH query AS (select
ROW_NUMBER() OVER (
order by
proj0_.id desc) as __hibernate_row_nr__,
proj0_.id as id1_,
proj0_.content as content1_,
proj0_.topic as topic1_,
from
project proj0_
inner join
proj_area areas1_
on proj0_.id=areas1_.proj_id
inner join
area area2_
on areas1_.area_id=area2_.id
where
area2_.id in (? , ?))
group by
proj0_.id,
proj0_.content,
proj0_.topic) SELECT
*
FROM
query
WHERE
__hibernate_row_nr__ BETWEEN ? AND ?
解决方法是使用子查询来选择一组不同的id值,并使用它来限制返回的值。它之所以有效,是因为将distinct应用于id以外的任何东西都是多余的。
SELECT b
FROM Project b
WHERE b.id IN(SELECT distinct o.id
FROM Project o join o.areas a
WHERE a.id IN(?,?))
正如JB Nizet所说,并参考http://docs.jboss.org/hibernate/core/3.6/reference/en-US/html_single/#performance-获取懒散的财产。这是因为提取lazyproperties功能不起作用。根据该文件,有两种解决方案。最后,我选择了一个首选的解决方案作为文档注释。
避免不必要的列读取的另一种方法,至少对于只读事务,是使用HQL或条件查询。这就避免了构建时字节码的需要处理,当然是一个优选的解决方案。
还有更多的解释@Basic(fetch=FetchType.LAZY)不工作?