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

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