—–业务场景中经常涉及到联查，jpa的hql语法提供了内连接的查询方式（不支持复杂hql，比如left join ，right join）. 上代码了
1.我们要联查房屋和房屋用户中间表，通过房屋id关联 homeInfo是房屋表，homeUser是房屋用户中间表.
homeInfo
package management.entity.carandhome; import java.io.Serializable;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Set; import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.OneToMany;
import javax.persistence.OneToOne;
import javax.persistence.Table; import org.hibernate.annotations.NotFound;
import org.hibernate.annotations.NotFoundAction; import com.alibaba.fastjson.annotation.JSONField;
import management.entity.paymentfee.LifeFee;
import management.entity.paymentfee.ManageFee; @Entity
@Table(name = "t_ss_w_home")
// 房屋信息表
public class HomeInfo implements Serializable { /**
*
*/
private static final long serialVersionUID = 1L; public HomeInfo() {
super();
// TODO Auto-generated constructor stub
} @Id
@GeneratedValue
// 房屋ID
private Long homeID; // 用户id，这个是标识房主的用户id,关联表查询出来的房主不靠谱
private Long userID; // @NotNull
// @Size(min = 3, max = 16)
// 房屋面积
private BigDecimal grossArea; // 实际面积
private BigDecimal realArea; // 户型
private String houseType; // 朝向
private String orientaion; // 入住时间
@JSONField(format = "yyyy-MM-dd")
private Date chechTime; // 楼号
private String homeNo; // 单元号
private String homeUnit; // 房间号
private String homeRoom; // 手机号
private String mobileNo; // 备注
private String homeRemark; // 房屋状态
private String homeState; // 房产性质
private String homeNature; // 楼层
private String homeFloor; // 房屋位置
private String homeLo; public Long getUserID() {
return userID;
} public void setUserID(Long userID) {
this.userID = userID;
}
// @OneToMany(fetch = FetchType.EAGER, mappedBy = "homeInfo")
@NotFound(action = NotFoundAction.IGNORE)
private Set<HomeUser> homeUserList = new HashSet<>(); @OneToMany(mappedBy = "homeInfo", fetch = FetchType.EAGER)
@NotFound(action = NotFoundAction.IGNORE)
private Set<ManageFee> manageFeeList = new HashSet<>(); @OneToMany(mappedBy = "homeInfo", fetch = FetchType.EAGER)
@NotFound(action = NotFoundAction.IGNORE)
private Set<LifeFee> lifeFeeList = new HashSet<>(); @NotFound(action = NotFoundAction.IGNORE)
@OneToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "homeID", referencedColumnName = "homeID", insertable = false, updatable = false)
private HouseConfirm houseConfirm; public List<HomeUser> getHomeUserList() {
return new ArrayList<>(homeUserList);
} public void setHomeUserList(Set<HomeUser> homeUserList) {
this.homeUserList = homeUserList;
} public Long getHomeID() {
return homeID;
} public void setHomeID(Long homeID) {
this.homeID = homeID;
} public BigDecimal getGrossArea() {
return grossArea;
} public void setGrossArea(BigDecimal grossArea) {
this.grossArea = grossArea;
} public BigDecimal getRealArea() {
return realArea;
} public void setRealArea(BigDecimal realArea) {
this.realArea = realArea;
} public String getHouseType() {
return houseType;
} public void setHouseType(String houseType) {
this.houseType = houseType;
} public String getOrientaion() {
return orientaion;
} public void setOrientaion(String orientaion) {
this.orientaion = orientaion;
} public Date getChechTime() {
return chechTime;
} public void setChechTime(Date chechTime) {
this.chechTime = chechTime;
} public String getMobileNo() {
return mobileNo;
} public void setMobileNo(String mobileNo) {
this.mobileNo = mobileNo;
} public String getHomeRemark() {
return homeRemark;
} public void setHomeRemark(String homeRemark) {
this.homeRemark = homeRemark;
} public String getHomeState() {
return homeState;
} public void setHomeState(String homeState) {
this.homeState = homeState;
} public String getHomeNature() {
return homeNature;
} public void setHomeNature(String homeNature) {
this.homeNature = homeNature;
} public String toString() {
return "{homeState:" + this.homeState + "}";
} public String getHomeLo() {
return homeLo;
} public void setHomeLo(String homeLo) {
this.homeLo = homeLo;
} public List<ManageFee> getManageFeeList() {
return new ArrayList<>(manageFeeList);
} public void setManageFeeList(Set<ManageFee> manageFeeList) {
this.manageFeeList = manageFeeList;
} public List<LifeFee> getLifeFeeList() {
return new ArrayList<>(lifeFeeList);
} public void setLifeFeeList(Set<LifeFee> lifeFeeList) {
this.lifeFeeList = lifeFeeList;
} public String getHomeNo() {
return homeNo;
} public void setHomeNo(String homeNo) {
this.homeNo = homeNo;
} public String getHomeUnit() {
return homeUnit;
} public void setHomeUnit(String homeUnit) {
this.homeUnit = homeUnit;
} public String getHomeRoom() {
return homeRoom;
} public void setHomeRoom(String homeRoom) {
this.homeRoom = homeRoom;
} public String getHomeFloor() {
return homeFloor;
} public void setHomeFloor(String homeFloor) {
this.homeFloor = homeFloor;
} public HouseConfirm getHouseConfirm() {
return houseConfirm;
} public void setHouseConfirm(HouseConfirm houseConfirm) {
this.houseConfirm = houseConfirm;
} }
homeUser
package management.entity.carandhome; import com.alibaba.fastjson.annotation.JSONField;
import com.fasterxml.jackson.annotation.JsonIgnore;
import org.hibernate.annotations.NotFound;
import org.hibernate.annotations.NotFoundAction; import javax.persistence.*;
import java.io.Serializable; @Entity
@Table(name = "t_ss_w_home_user")
public class HomeUser implements Serializable { /**
*
*/
private static final long serialVersionUID = 1L; @Id
@GeneratedValue
private Long homeUserID; private Long userID; private Long homeID; @JSONField
@JsonIgnore
@ManyToOne
@JoinColumn(name = "homeID", insertable = false, updatable = false)
@NotFound(action = NotFoundAction.IGNORE)
private HomeInfo homeInfo; public HomeUser() {
super();
} public HomeInfo getHomeInfo() {
return homeInfo;
} public void setHomeInfo(HomeInfo homeInfo) {
this.homeInfo = homeInfo;
} public Long getHomeUserID() {
return homeUserID;
} public void setHomeUserID(Long homeUserID) {
this.homeUserID = homeUserID;
} public Long getUserID() {
return userID;
} public void setUserID(Long userID) {
this.userID = userID;
} public Long getHomeID() {
return homeID;
} public void setHomeID(Long homeID) {
this.homeID = homeID;
} @Override
public String toString() {
return "HomeUser [homeUserID=" + homeUserID + ", userID=" + userID + ", homeID=" + homeID + "]";
} }
自定义的联查结果实体类
package management.entity.carandhome; import java.io.Serializable;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Set; import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.OneToMany;
import javax.persistence.OneToOne;
import javax.persistence.Table; import org.hibernate.annotations.NotFound;
import org.hibernate.annotations.NotFoundAction; import com.alibaba.fastjson.annotation.JSONField;
import management.entity.paymentfee.LifeFee;
import management.entity.paymentfee.ManageFee; public class HomeUserAppVO implements Serializable { /**
*
*/
private static final long serialVersionUID = 1L; public HomeUserAppVO() {
super();
}
// 这个构造方法必须有， 且要与后面的联查hql语句对应
public HomeUserAppVO(Long homeID, String homeNo, String homeUnit, String homeRoom, String mobileNo,
String homeState, String homeNature, String homeFloor, String homeLo) {
super();
this.homeID = homeID;
this.homeNo = homeNo;
this.homeUnit = homeUnit;
this.homeRoom = homeRoom;
this.mobileNo = mobileNo;
this.homeState = homeState;
this.homeNature = homeNature;
this.homeFloor = homeFloor;
this.homeLo = homeLo;
}
private Long homeID; // 楼号
private String homeNo; // 单元号
private String homeUnit; // 房间号
private String homeRoom; // 手机号
private String mobileNo; // 房屋状态
private String homeState; // 房产性质
private String homeNature; // 楼层
private String homeFloor; // 房屋位置
private String homeLo;
// 用户角色（房主，家人，租客）
private String familyRelationship;
public Long getHomeID() {
return homeID;
}
public void setHomeID(Long homeID) {
this.homeID = homeID;
}
public String getHomeNo() {
return homeNo;
}
public void setHomeNo(String homeNo) {
this.homeNo = homeNo;
}
public String getHomeUnit() {
return homeUnit;
}
public void setHomeUnit(String homeUnit) {
this.homeUnit = homeUnit;
}
public String getHomeRoom() {
return homeRoom;
}
public void setHomeRoom(String homeRoom) {
this.homeRoom = homeRoom;
}
public String getMobileNo() {
return mobileNo;
}
public void setMobileNo(String mobileNo) {
this.mobileNo = mobileNo;
}
public String getHomeState() {
return homeState;
}
public void setHomeState(String homeState) {
this.homeState = homeState;
}
public String getHomeNature() {
return homeNature;
}
public void setHomeNature(String homeNature) {
this.homeNature = homeNature;
}
public String getHomeFloor() {
return homeFloor;
}
public void setHomeFloor(String homeFloor) {
this.homeFloor = homeFloor;
}
public String getHomeLo() {
return homeLo;
}
public void setHomeLo(String homeLo) {
this.homeLo = homeLo;
}
public String getFamilyRelationship() {
return familyRelationship;
}
public void setFamilyRelationship(String familyRelationship) {
familyRelationship = familyRelationship;
}
@Override
public String toString() {
return "HomeUserAppVO [homeID=" + homeID + ", homeNo=" + homeNo + ", homeUnit=" + homeUnit + ", homeRoom="
+ homeRoom + ", mobileNo=" + mobileNo + ", homeState=" + homeState + ", homeNature=" + homeNature
+ ", homeFloor=" + homeFloor + ", homeLo=" + homeLo + ", FamilyRelationship=" + familyRelationship
+ "]";
} }
2.实体类建好，这里我们直接写持久层代码
package management.dao.carandhome;import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import java.util.Set;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import management.dao.BaseDao;
import management.entity.carandhome.HomeInfo;
import management.entity.carandhome.HomeUserAppVO;
import management.vo.carHome.HomeInfoVO;@Repository
public interface HomeInfoDao extends BaseDao<HomeInfo> { //联查，返回自定义对象，使用hql语句. 这里需要注意三点【1.new 全类路径名(属性1，属性2) 2.属性名称要与实体类一一对应，大小写都不能差，否则会报异常 3.自定义类的构造方法里的字段要与这里一一对应】
@Query(
value = "select new management.entity.carandhome.HomeUserAppVO(h.homeID,h.homeNo,h.homeUnit,h.homeRoom,"
+ "h.mobileNo,h.homeState,h.homeNature,h.homeFloor,h.homeLo) from HomeInfo h , HomeUser hu where h.homeID=hu.homeID"
+ " and hu.userID=:id")
List<HomeUserAppVO> findHomeInfoByUserId(@Param("id") Long id); // 原生sql联查语句写法，但是返回对象是一个object【】，因为使用了原生语句，只能自己再去一一对应，然后封装到自己的业务bean中。
@Query(
value = "SELECT " + " h.*, t.countUser " + "FROM " + " ( " + " SELECT "
+ " hu.HomeID homeID, " + " count(hu.UserID) countUser " + " FROM " + " ( "
+ " SELECT " + " hu.HomeID homeID " + " FROM " + " t_ss_w_home_user hu "
+ " WHERE " + " hu.UserID = :userID " + " ) t "
+ " LEFT JOIN t_ss_w_home_user hu ON hu.HomeID = t.homeID " + " GROUP BY "
+ " hu.HomeID " + " ) t " + "LEFT JOIN t_ss_w_home h ON t.homeID = h.HomeID",
nativeQuery = true)
List<Object[]> findByUserIdAndCountUser(@Param("userID") Long userID);}
4.再写原生的sql时，别名 如果不能使用，需要在连接mysql配置时 增加 useOldAliasMetadataBehavior=true（有此问题的需要加，没有的就不用，我的版本不用，这里顺带提一下）
url: jdbc:mysql://mysql:3306/property_manager?useUnicode=true&characterEncoding=utf-8&useOldAliasMetadataBehavior=true
