jpa 联表查询 返回自定义对象 hql语法 原生sql 语法 1.11.9版本

时间：2021-06-06 19:38:01

-----业务场景中经常涉及到联查，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

相关文章