Hibernate Criteria查询之多表连接分页-1

最近尝试用Hibernate的Criteria查询实现多表连接下的分页，发现一些Hibernate的奇怪问题：多表连接后的分页求总条数始终报错，检查生成的sql也不正确。研究许久找到问题所在，特贴如下：

Mysql脚本如下：

学生 测试数据，请勿见笑:)

/*Navicat MySQL Data TransferSource Server         : localhostSource Server Version : 50018Source Host           : localhost:3306Source Database       : houseTarget Server Type    : MYSQLTarget Server Version : 50018File Encoding         : 65001Date: 2011-08-10 13:38:12*/SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for `district`-- ----------------------------DROP TABLE IF EXISTS `district`;CREATE TABLE `district` (  `ID` int(11) NOT NULL auto_increment,  `name` varchar(50) NOT NULL,  PRIMARY KEY  (`ID`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------ Records of district-- ----------------------------INSERT INTO `district` VALUES ('1', '青羊区');INSERT INTO `district` VALUES ('2', '金牛区');INSERT INTO `district` VALUES ('3', '抚琴小区');INSERT INTO `district` VALUES ('4', '朝阳区');INSERT INTO `district` VALUES ('5', '阳光小区');INSERT INTO `district` VALUES ('6', '名扬小区');-- ------------------------------ Table structure for `house`-- ----------------------------DROP TABLE IF EXISTS `house`;CREATE TABLE `house` (  `ID` int(11) NOT NULL auto_increment,  `user_id` int(11) NOT NULL,  `type_id` int(11) NOT NULL,  `title` varchar(50) NOT NULL,  `description` varchar(2000) NOT NULL,  `price` int(11) NOT NULL,  `pubdate` date NOT NULL,  `floorage` int(11) NOT NULL,  `contact` varchar(255) NOT NULL,  `street_id` int(11) NOT NULL,  PRIMARY KEY  (`ID`),  KEY `fk_user_house` (`user_id`),  KEY `fk_type_house` (`type_id`),  KEY `fk_street_house` (`street_id`),  CONSTRAINT `fk_street_house` FOREIGN KEY (`street_id`) REFERENCES `street` (`ID`),  CONSTRAINT `fk_type_house` FOREIGN KEY (`type_id`) REFERENCES `type` (`ID`),  CONSTRAINT `fk_user_house` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------ Records of house-- ----------------------------INSERT INTO `house` VALUES ('1', '1', '1', '第一个房子', '很好，向阳，方便舒适', '1234', '2011-07-26', '123', '1354135423', '1');INSERT INTO `house` VALUES ('2', '2', '2', '第二个房子', '简单家具，宽敞明亮', '4543', '2011-07-04', '343', '1435465656', '3');INSERT INTO `house` VALUES ('3', '1', '1', '第三个房子', '干净宽敞明亮', '123', '2011-07-13', '123', '1342533333', '3');INSERT INTO `house` VALUES ('4', '3', '4', '第四个房子', '和低速复苏阿发塑封股', '214', '2011-07-04', '3444', '323243423442', '5');INSERT INTO `house` VALUES ('5', '3', '3', '第五间房', '是不是哦马哈好似奥挥洒出', '12', '2011-07-03', '878', '13541234567', '3');INSERT INTO `house` VALUES ('6', '4', '2', '第六间房', '非hiusa公司udgfbsdugf 岁噶', '344', '2011-07-12', '546', '1423343243', '3');INSERT INTO `house` VALUES ('8', '1', '4', 'wrwegrhtrhgh', 'dgv hyhhhhhhhhhhhhh', '44', '2011-07-11', '676', '154354545455', '1');INSERT INTO `house` VALUES ('9', '1', '2', 'gfdgggggggggggggg', 'afdgdsgdgdf', '2342', '2011-06-27', '12423', '1535436546', '1');INSERT INTO `house` VALUES ('10', '1', '2', 'dfegggg', 'fdfdgfd', '21423', '2011-07-10', '1243', '243254354', '1');INSERT INTO `house` VALUES ('11', '2', '2', 'rrrrr', 'vdfgrdtg', '2423', '2011-07-11', '232', '13423432435', '2');-- ------------------------------ Table structure for `street`-- ----------------------------DROP TABLE IF EXISTS `street`;CREATE TABLE `street` (  `ID` int(11) NOT NULL auto_increment,  `name` varchar(50) NOT NULL,  `district_id` int(11) NOT NULL,  PRIMARY KEY  (`ID`),  KEY `fk_street_district_id` (`district_id`),  CONSTRAINT `fk_street_district_id` FOREIGN KEY (`district_id`) REFERENCES `district` (`ID`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------ Records of street-- ----------------------------INSERT INTO `street` VALUES ('1', '东南路', '1');INSERT INTO `street` VALUES ('2', '知春路', '1');INSERT INTO `street` VALUES ('3', '中关村大街', '2');INSERT INTO `street` VALUES ('4', '学院路', '3');INSERT INTO `street` VALUES ('5', '朝阳路', '3');INSERT INTO `street` VALUES ('6', '成荫路', '4');INSERT INTO `street` VALUES ('7', '哈哈路', '3');-- ------------------------------ Table structure for `type`-- ----------------------------DROP TABLE IF EXISTS `type`;CREATE TABLE `type` (  `ID` int(11) NOT NULL auto_increment,  `name` varchar(10) NOT NULL,  PRIMARY KEY  (`ID`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------ Records of type-- ----------------------------INSERT INTO `type` VALUES ('1', '一室一厅');INSERT INTO `type` VALUES ('2', '两室一厅');INSERT INTO `type` VALUES ('3', '三室一厅');INSERT INTO `type` VALUES ('4', '四室一厅');INSERT INTO `type` VALUES ('5', '两室两厅');-- ------------------------------ Table structure for `users`-- ----------------------------DROP TABLE IF EXISTS `users`;CREATE TABLE `users` (  `id` int(11) NOT NULL auto_increment,  `name` varchar(50) NOT NULL,  `password` varchar(50) NOT NULL,  `telephone` varchar(15) NOT NULL,  `userName` varchar(50) NOT NULL,  `idAdmin` varchar(5) NOT NULL,  PRIMARY KEY  (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------ Records of users-- ----------------------------INSERT INTO `users` VALUES ('1', 'admin', '12345', '13541305274', 'Admin', '1');INSERT INTO `users` VALUES ('2', 'zhang', '12345', '12345678444', '张小花', '2');INSERT INTO `users` VALUES ('3', 'li', '12345', '12234242354', 'zhngxaida', '1');INSERT INTO `users` VALUES ('4', 'luo', 'luo', '143435y4385', 'hsafgdsavds', '1');INSERT INTO `users` VALUES ('5', 'huang', 'huang', '14235435465', 'hdusigfsu', '1');

现有实体类如下：

区实体类:

package org.accp.mhouse.entities;import java.util.HashSet;import java.util.Set;import javax.persistence.CascadeType;import javax.persistence.Column;import javax.persistence.Entity;import javax.persistence.FetchType;import javax.persistence.GeneratedValue;import static javax.persistence.GenerationType.IDENTITY;import javax.persistence.Id;import javax.persistence.OneToMany;import javax.persistence.Table;/** * District entity. @author MyEclipse Persistence Tools */@Entity@Table(name = "district", catalog = "house")public class District implements java.io.Serializable {// Fieldsprivate Integer id;private String name;private Set<Street> streets = new HashSet<Street>(0);// Constructors/** default constructor */public District() {}/** minimal constructor */public District(String name) {this.name = name;}/** full constructor */public District(String name, Set<Street> streets) {this.name = name;this.streets = streets;}// Property accessors@Id@GeneratedValue(strategy = IDENTITY)@Column(name = "ID", unique = true, nullable = false)public Integer getId() {return this.id;}public void setId(Integer id) {this.id = id;}@Column(name = "name", nullable = false, length = 50)public String getName() {return this.name;}public void setName(String name) {this.name = name;}@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "district")public Set<Street> getStreets() {return this.streets;}public void setStreets(Set<Street> streets) {this.streets = streets;}}

街道实体类：

package org.accp.mhouse.entities;import java.util.HashSet;import java.util.Set;import javax.persistence.CascadeType;import javax.persistence.Column;import javax.persistence.Entity;import javax.persistence.FetchType;import javax.persistence.GeneratedValue;import static javax.persistence.GenerationType.IDENTITY;import javax.persistence.Id;import javax.persistence.JoinColumn;import javax.persistence.ManyToOne;import javax.persistence.OneToMany;import javax.persistence.Table;/** * Street entity. @author MyEclipse Persistence Tools */@Entity@Table(name = "street", catalog = "house")public class Street implements java.io.Serializable {// Fieldsprivate Integer id;private District district;private String name;private Set<House> houses = new HashSet<House>(0);// Constructors/** default constructor */public Street() {}/** minimal constructor */public Street(District district, String name) {this.district = district;this.name = name;}/** full constructor */public Street(District district, String name, Set<House> houses) {this.district = district;this.name = name;this.houses = houses;}// Property accessors@Id@GeneratedValue(strategy = IDENTITY)@Column(name = "ID", unique = true, nullable = false)public Integer getId() {return this.id;}public void setId(Integer id) {this.id = id;}@ManyToOne(fetch = FetchType.LAZY)@JoinColumn(name = "district_id", nullable = false)public District getDistrict() {return this.district;}public void setDistrict(District district) {this.district = district;}@Column(name = "name", nullable = false, length = 50)public String getName() {return this.name;}public void setName(String name) {this.name = name;}@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "street")public Set<House> getHouses() {return this.houses;}public void setHouses(Set<House> houses) {this.houses = houses;}}

房屋类型实体类：

package org.accp.mhouse.entities;import java.util.HashSet;import java.util.Set;import javax.persistence.CascadeType;import javax.persistence.Column;import javax.persistence.Entity;import javax.persistence.FetchType;import javax.persistence.GeneratedValue;import static javax.persistence.GenerationType.IDENTITY;import javax.persistence.Id;import javax.persistence.OneToMany;import javax.persistence.Table;/** * Type entity. @author MyEclipse Persistence Tools */@Entity@Table(name = "type", catalog = "house")public class Type implements java.io.Serializable {// Fieldsprivate Integer id;private String name;private Set<House> houses = new HashSet<House>(0);// Constructors/** default constructor */public Type() {}/** minimal constructor */public Type(String name) {this.name = name;}/** full constructor */public Type(String name, Set<House> houses) {this.name = name;this.houses = houses;}// Property accessors@Id@GeneratedValue(strategy = IDENTITY)@Column(name = "ID", unique = true, nullable = false)public Integer getId() {return this.id;}public void setId(Integer id) {this.id = id;}@Column(name = "name", nullable = false, length = 10)public String getName() {return this.name;}public void setName(String name) {this.name = name;}@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "type")public Set<House> getHouses() {return this.houses;}public void setHouses(Set<House> houses) {this.houses = houses;}}

用户实体类：

package org.accp.mhouse.entities;import java.util.HashSet;import java.util.Set;import javax.persistence.CascadeType;import javax.persistence.Column;import javax.persistence.Entity;import javax.persistence.FetchType;import javax.persistence.GeneratedValue;import static javax.persistence.GenerationType.IDENTITY;import javax.persistence.Id;import javax.persistence.OneToMany;import javax.persistence.Table;/** * Users entity. @author MyEclipse Persistence Tools */@Entity@Table(name = "users", catalog = "house")public class Users implements java.io.Serializable {// Fieldsprivate Integer id;private String name;private String password;private String telephone;private String userName;private String idAdmin;private Set<House> houses = new HashSet<House>(0);// Constructors/** default constructor */public Users() {}/** minimal constructor */public Users(String name, String password, String telephone,String userName, String idAdmin) {this.name = name;this.password = password;this.telephone = telephone;this.userName = userName;this.idAdmin = idAdmin;}/** full constructor */public Users(String name, String password, String telephone,String userName, String idAdmin, Set<House> houses) {this.name = name;this.password = password;this.telephone = telephone;this.userName = userName;this.idAdmin = idAdmin;this.houses = houses;}// Property accessors@Id@GeneratedValue(strategy = IDENTITY)@Column(name = "id", unique = true, nullable = false)public Integer getId() {return this.id;}public void setId(Integer id) {this.id = id;}@Column(name = "name", nullable = false, length = 50)public String getName() {return this.name;}public void setName(String name) {this.name = name;}@Column(name = "password", nullable = false, length = 50)public String getPassword() {return this.password;}public void setPassword(String password) {this.password = password;}@Column(name = "telephone", nullable = false, length = 15)public String getTelephone() {return this.telephone;}public void setTelephone(String telephone) {this.telephone = telephone;}@Column(name = "userName", nullable = false, length = 50)public String getUserName() {return this.userName;}public void setUserName(String userName) {this.userName = userName;}@Column(name = "idAdmin", nullable = false, length = 5)public String getIdAdmin() {return this.idAdmin;}public void setIdAdmin(String idAdmin) {this.idAdmin = idAdmin;}@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "users")public Set<House> getHouses() {return this.houses;}public void setHouses(Set<House> houses) {this.houses = houses;}}

房屋实体类：

package org.accp.mhouse.entities;import java.util.Date;import javax.persistence.Column;import javax.persistence.Entity;import javax.persistence.FetchType;import javax.persistence.GeneratedValue;import static javax.persistence.GenerationType.IDENTITY;import javax.persistence.Id;import javax.persistence.JoinColumn;import javax.persistence.ManyToOne;import javax.persistence.Table;import javax.persistence.Temporal;import javax.persistence.TemporalType;/** * House entity. @author MyEclipse Persistence Tools */@Entity@Table(name = "house", catalog = "house")public class House implements java.io.Serializable {// Fieldsprivate Integer id;private Users users;//many to oneprivate Type type;//many to oneprivate Street street;//many to oneprivate String title;private String description;private Integer price;private Date pubdate;private Integer floorage;private String contact;// Constructors/** default constructor */public House() {}/** full constructor */public House(Users users, Type type, Street street, String title,String description, Integer price, Date pubdate, Integer floorage,String contact) {this.users = users;this.type = type;this.street = street;this.title = title;this.description = description;this.price = price;this.pubdate = pubdate;this.floorage = floorage;this.contact = contact;}// Property accessors@Id@GeneratedValue(strategy = IDENTITY)@Column(name = "ID", unique = true, nullable = false)public Integer getId() {return this.id;}public void setId(Integer id) {this.id = id;}@ManyToOne(fetch = FetchType.LAZY)@JoinColumn(name = "user_id", nullable = false)public Users getUsers() {return this.users;}public void setUsers(Users users) {this.users = users;}@ManyToOne(fetch = FetchType.LAZY)@JoinColumn(name = "type_id", nullable = false)public Type getType() {return this.type;}public void setType(Type type) {this.type = type;}@ManyToOne(fetch = FetchType.LAZY)@JoinColumn(name = "street_id", nullable = false)public Street getStreet() {return this.street;}public void setStreet(Street street) {this.street = street;}@Column(name = "title", nullable = false, length = 50)public String getTitle() {return this.title;}public void setTitle(String title) {this.title = title;}@Column(name = "description", nullable = false, length = 2000)public String getDescription() {return this.description;}public void setDescription(String description) {this.description = description;}@Column(name = "price", nullable = false)public Integer getPrice() {return this.price;}public void setPrice(Integer price) {this.price = price;}@Temporal(TemporalType.DATE)@Column(name = "pubdate", nullable = false, length = 10)public Date getPubdate() {return this.pubdate;}public void setPubdate(Date pubdate) {this.pubdate = pubdate;}@Column(name = "floorage", nullable = false)public Integer getFloorage() {return this.floorage;}public void setFloorage(Integer floorage) {this.floorage = floorage;}@Column(name = "contact", nullable = false)public String getContact() {return this.contact;}public void setContact(String contact) {this.contact = contact;}}

查询示范1，查询区为“xxx”区的所有房屋信息，并抓取查询房屋对象关联的街道对象，街道对象关联的区对象

首先查询总条数，通常思维习惯如下：

//求总条数session.createCriteria(House.class).setFetchMode("street", org.hibernate.FetchMode.JOIN).setFetchMode("street.district",org.hibernate.FetchMode.JOIN).createAlias("street.district", "d").add(Restrictions.eq("d.name", "青羊区")).setProjection(Projections.rowCount()).uniqueResult();

很遗憾，查询结果大失所望：

控制异常如下：

Hibernate:     select        count(*) as y0_     from        house.house this_     where        d1_.name=?Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not execute queryat org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)at org.hibernate.loader.Loader.doList(Loader.java:2220)at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2108)at org.hibernate.loader.Loader.list(Loader.java:2103)at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:94)at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1570)at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:283)at org.hibernate.impl.CriteriaImpl.uniqueResult(CriteriaImpl.java:305)at org.accp.mhouse.dao.MainModule.main(MainModule.java:164)Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'd1_.name' in 'where clause'at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)at java.lang.reflect.Constructor.newInstance(Constructor.java:513)at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)at com.mysql.jdbc.Util.getInstance(Util.java:381)at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1885)at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:187)at org.hibernate.loader.Loader.getResultSet(Loader.java:1791)at org.hibernate.loader.Loader.doQuery(Loader.java:674)at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)at org.hibernate.loader.Loader.doList(Loader.java:2217)... 7 more

但是以上语句如果是查询结果那是绝对正确的，如下：

//结果session.createCriteria(House.class).setFetchMode("street", org.hibernate.FetchMode.JOIN).setFetchMode("street.district",org.hibernate.FetchMode.JOIN).createAlias("street.district", "d").add(Restrictions.eq("d.name", "青羊区")).list();

控制台输出如下：

Hibernate:     select        this_.ID as ID2_2_,        this_.contact as contact2_2_,        this_.description as descript3_2_2_,        this_.floorage as floorage2_2_,        this_.price as price2_2_,        this_.pubdate as pubdate2_2_,        this_.street_id as street8_2_2_,        this_.title as title2_2_,        this_.type_id as type9_2_2_,        this_.user_id as user10_2_2_,        street3_.ID as ID1_0_,        street3_.district_id as district3_1_0_,        street3_.name as name1_0_,        d1_.ID as ID4_1_,        d1_.name as name4_1_     from        house.house this_     inner join        house.street street3_             on this_.street_id=street3_.ID     inner join        house.district d1_             on street3_.district_id=d1_.ID     where        d1_.name=?

就是上面那个求总条数的问题困扰我许久，网络搜索无果，几经查找摸索，正确如下：

求总条数：

//求总条数session.createCriteria(House.class).createAlias("street","st",CriteriaSpecification.INNER_JOIN).createAlias("st.district","d",CriteriaSpecification.INNER_JOIN).createAlias("users", "u", CriteriaSpecification.INNER_JOIN).createAlias("type","t",CriteriaSpecification.INNER_JOIN).add(Restrictions.eq("d.name", "青羊区")).setProjection(Projections.rowCount()).uniqueResult();

Hibernate API文档对createAlias()方法的解释:

createAlias

Criteria createAlias(String associationPath,                     String alias,                     int joinType)                     throws HibernateException

Join an association using the specified join-type, assigning an alias to the joined association.

The joinType is expected to be one of CriteriaSpecification.INNER_JOIN (the default), CriteriaSpecification.FULL_JOIN, or CriteriaSpecification.LEFT_JOIN.

Parameters:

associationPath - A dot-seperated property path

alias - The alias to assign to the joined association (for later reference).

joinType - The type of join to use.

Returns:

this (for method chaining)

Throws:

HibernateException

很明显上面的查询更加复杂，房屋关联街道，街道关联区，房屋关联类型，房屋关联用户，筛选条件是区是“青羊区”,我们看查询结果:

Hibernate:     select        count(*) as y0_     from        house.house this_     inner join        house.street st1_             on this_.street_id=st1_.ID     inner join        house.district d2_             on st1_.district_id=d2_.ID     inner join        house.type t4_             on this_.type_id=t4_.ID     inner join        house.users u3_             on this_.user_id=u3_.id     where        d2_.name=?

OK,终于搞定这个棘手的问题。