jpa多表leftjoin 查询，自定义返回结果

sql代码

自定义返回结果

package com.cdp.ecosaas.coredb.uua.sync.vo;

import java.io.Serializable;

public class UuaUserNotifyTemp implements Serializable {

    /**
     * 
     */
    private static final long serialVersionUID = 1L;
    
    private String id;

    private String sysCode;

    private String sysName;

    private String idNumber;

    private String email;

    private String phone;

    private String tenantId;

    private String isAuto;

    private String isEmail;

    private String isMobile;

    private String emailLang;

    private String msmLang;

    private String mailNotify;

    private String mobileNotify;

    private String state;


    public String getIdNumber() {
        return idNumber;
    }

    public void setIdNumber(String idNumber) {
        this.idNumber = idNumber;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public String getTenantId() {
        return tenantId;
    }

    public void setTenantId(String tenantId) {
        this.tenantId = tenantId;
    }

    public String getIsAuto() {
        return isAuto;
    }

    public void setIsAuto(String isAuto) {
        this.isAuto = isAuto;
    }

    public String getIsEmail() {
        return isEmail;
    }

    public void setIsEmail(String isEmail) {
        this.isEmail = isEmail;
    }

    public String getIsMobile() {
        return isMobile;
    }

    public void setIsMobile(String isMobile) {
        this.isMobile = isMobile;
    }

    public String getEmailLang() {
        return emailLang;
    }

    public void setEmailLang(String emailLang) {
        this.emailLang = emailLang;
    }

    public String getMsmLang() {
        return msmLang;
    }

    public void setMsmLang(String msmLang) {
        this.msmLang = msmLang;
    }

    public String getMailNotify() {
        return mailNotify;
    }

    public void setMailNotify(String mailNotify) {
        this.mailNotify = mailNotify;
    }

    public String getMobileNotify() {
        return mobileNotify;
    }

    public void setMobileNotify(String mobileNotify) {
        this.mobileNotify = mobileNotify;
    }

    public String getState() {
        return state;
    }

    public void setState(String state) {
        this.state = state;
    }

    
    public String getSysCode() {
        return sysCode;
    }

    
    public void setSysCode(String sysCode) {
        this.sysCode = sysCode;
    }

    
    public String getSysName() {
        return sysName;
    }

    
    public void setSysName(String sysName) {
        this.sysName = sysName;
    }

    
    public String getId() {
        return id;
    }

    
    public void setId(String id) {
        this.id = id;
    }

    public UuaUserNotifyTemp(String id, String sysCode, String sysName, String idNumber, String email, String phone,
            String tenantId, String isAuto, String isEmail, String isMobile, String emailLang, String msmLang,
            String mailNotify, String mobileNotify, String state) {
        super();
        this.id = id;
        this.sysCode = sysCode;
        this.sysName = sysName;
        this.idNumber = idNumber;
        this.email = email;
        this.phone = phone;
        this.tenantId = tenantId;
        this.isAuto = isAuto;
        this.isEmail = isEmail;
        this.isMobile = isMobile;
        this.emailLang = emailLang;
        this.msmLang = msmLang;
        this.mailNotify = mailNotify;
        this.mobileNotify = mobileNotify;
        this.state = state;
    }

    
    

}

 

 

sql代码

@Query(value = "SELECT new com.cdp.ecosaas.coredb.uua.sync.vo.UuaUserNotifyTemp(uu.id,ue.sysCode,ue.sysName,uu.idNumber,uu.email,uu.phone,ue.tenantId,unc.isAuto,unc.isEmail,unc.isMobile,unc.emailLang,unc.msmLang, uu.mailNotify, uu.mobileNotify, uu.state) FROM UuaUser uu LEFT JOIN UuaUserExtsysRef uuer on uu.id = uuer.id.userId LEFT JOIN UuaExtsy ue on ue.id = uuer.id.extsysId LEFT JOIN UuaNotifyConfig unc ON unc.tenantId = ue.tenantId where unc.isAuto = 1 and (uu.state = 0 or uu.state is null) and (uu.mailNotify = 0 or uu.mailNotify is null) and (uu.mobileNotify = 0 or uu.mobileNotify is null) ")
    List<UuaUserNotifyTemp> findNotifyUserTemp();

注意点 1先把原来sql写好 保证能把执行，自定义返回结果只支持hql语句，不支持原生sql

2表连接的时候注意使用的对象的属性连接，比如属性是id，数据库字段是_id,这里用的是id，如果sql能执行，hql也一定能执行

3注意使用高版本的hibernate  

<hibernate.version>5.2.12.Final</hibernate.version>

之前大家说的什么hql leftjoin  不识别 on 是版本的问题

4自定义返回结果要跟hql写的字段顺序对应。

 

建议如果使用jpa 还是推荐把表之间关联关系维护好，不要走这用歪路子。特别是加载策略以及级联关系。不要都扔一个all就完事了