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

jpa多表leftjoin 查询，自定义返回结果
原文链接：https://www.cnblogs.com/chenyangwang/p/11060667.html

sql代码

自定义返回结果

package com.cdp.ecosaas.coredb.uua.sync.vo;
import java.io.Serializable;
public class UuaUserNotifyTemp implements Serializable {
</span><span style="color: rgba(0, 128, 0, 1)">/**</span><span style="color: rgba(0, 128, 0, 1)">
 * 
 </span><span style="color: rgba(0, 128, 0, 1)">*/</span>
<span style="color: rgba(0, 0, 255, 1)">private</span> <span style="color: rgba(0, 0, 255, 1)">static</span> <span style="color: rgba(0, 0, 255, 1)">final</span> <span style="color: rgba(0, 0, 255, 1)">long</span> serialVersionUID = 1L<span style="color: rgba(0, 0, 0, 1)">;

</span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> String id;

</span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> String sysCode;

</span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> String sysName;

</span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> String idNumber;

</span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> String email;

</span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> String phone;

</span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> String tenantId;

</span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> String isAuto;

</span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> String isEmail;

</span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> String isMobile;

</span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> String emailLang;

</span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> String msmLang;

</span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> String mailNotify;

</span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> String mobileNotify;

</span><span style="color: rgba(0, 0, 255, 1)">private</span><span style="color: rgba(0, 0, 0, 1)"> String state;


</span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> String getIdNumber() {
    </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> idNumber;
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setIdNumber(String idNumber) {
    </span><span style="color: rgba(0, 0, 255, 1)">this</span>.idNumber =<span style="color: rgba(0, 0, 0, 1)"> idNumber;
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> String getEmail() {
    </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> email;
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setEmail(String email) {
    </span><span style="color: rgba(0, 0, 255, 1)">this</span>.email =<span style="color: rgba(0, 0, 0, 1)"> email;
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> String getPhone() {
    </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> phone;
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setPhone(String phone) {
    </span><span style="color: rgba(0, 0, 255, 1)">this</span>.phone =<span style="color: rgba(0, 0, 0, 1)"> phone;
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> String getTenantId() {
    </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> tenantId;
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setTenantId(String tenantId) {
    </span><span style="color: rgba(0, 0, 255, 1)">this</span>.tenantId =<span style="color: rgba(0, 0, 0, 1)"> tenantId;
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> String getIsAuto() {
    </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> isAuto;
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setIsAuto(String isAuto) {
    </span><span style="color: rgba(0, 0, 255, 1)">this</span>.isAuto =<span style="color: rgba(0, 0, 0, 1)"> isAuto;
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> String getIsEmail() {
    </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> isEmail;
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setIsEmail(String isEmail) {
    </span><span style="color: rgba(0, 0, 255, 1)">this</span>.isEmail =<span style="color: rgba(0, 0, 0, 1)"> isEmail;
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> String getIsMobile() {
    </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> isMobile;
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setIsMobile(String isMobile) {
    </span><span style="color: rgba(0, 0, 255, 1)">this</span>.isMobile =<span style="color: rgba(0, 0, 0, 1)"> isMobile;
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> String getEmailLang() {
    </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> emailLang;
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setEmailLang(String emailLang) {
    </span><span style="color: rgba(0, 0, 255, 1)">this</span>.emailLang =<span style="color: rgba(0, 0, 0, 1)"> emailLang;
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> String getMsmLang() {
    </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> msmLang;
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setMsmLang(String msmLang) {
    </span><span style="color: rgba(0, 0, 255, 1)">this</span>.msmLang =<span style="color: rgba(0, 0, 0, 1)"> msmLang;
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> String getMailNotify() {
    </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> mailNotify;
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setMailNotify(String mailNotify) {
    </span><span style="color: rgba(0, 0, 255, 1)">this</span>.mailNotify =<span style="color: rgba(0, 0, 0, 1)"> mailNotify;
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> String getMobileNotify() {
    </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> mobileNotify;
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setMobileNotify(String mobileNotify) {
    </span><span style="color: rgba(0, 0, 255, 1)">this</span>.mobileNotify =<span style="color: rgba(0, 0, 0, 1)"> mobileNotify;
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> String getState() {
    </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> state;
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setState(String state) {
    </span><span style="color: rgba(0, 0, 255, 1)">this</span>.state =<span style="color: rgba(0, 0, 0, 1)"> state;
}


</span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> String getSysCode() {
    </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> sysCode;
}


</span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setSysCode(String sysCode) {
    </span><span style="color: rgba(0, 0, 255, 1)">this</span>.sysCode =<span style="color: rgba(0, 0, 0, 1)"> sysCode;
}


</span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> String getSysName() {
    </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> sysName;
}


</span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setSysName(String sysName) {
    </span><span style="color: rgba(0, 0, 255, 1)">this</span>.sysName =<span style="color: rgba(0, 0, 0, 1)"> sysName;
}


</span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> String getId() {
    </span><span style="color: rgba(0, 0, 255, 1)">return</span><span style="color: rgba(0, 0, 0, 1)"> id;
}


</span><span style="color: rgba(0, 0, 255, 1)">public</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> setId(String id) {
    </span><span style="color: rgba(0, 0, 255, 1)">this</span>.id =<span style="color: rgba(0, 0, 0, 1)"> id;
}

</span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> 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) {
    </span><span style="color: rgba(0, 0, 255, 1)">super</span><span style="color: rgba(0, 0, 0, 1)">();
    </span><span style="color: rgba(0, 0, 255, 1)">this</span>.id =<span style="color: rgba(0, 0, 0, 1)"> id;
    </span><span style="color: rgba(0, 0, 255, 1)">this</span>.sysCode =<span style="color: rgba(0, 0, 0, 1)"> sysCode;
    </span><span style="color: rgba(0, 0, 255, 1)">this</span>.sysName =<span style="color: rgba(0, 0, 0, 1)"> sysName;
    </span><span style="color: rgba(0, 0, 255, 1)">this</span>.idNumber =<span style="color: rgba(0, 0, 0, 1)"> idNumber;
    </span><span style="color: rgba(0, 0, 255, 1)">this</span>.email =<span style="color: rgba(0, 0, 0, 1)"> email;
    </span><span style="color: rgba(0, 0, 255, 1)">this</span>.phone =<span style="color: rgba(0, 0, 0, 1)"> phone;
    </span><span style="color: rgba(0, 0, 255, 1)">this</span>.tenantId =<span style="color: rgba(0, 0, 0, 1)"> tenantId;
    </span><span style="color: rgba(0, 0, 255, 1)">this</span>.isAuto =<span style="color: rgba(0, 0, 0, 1)"> isAuto;
    </span><span style="color: rgba(0, 0, 255, 1)">this</span>.isEmail =<span style="color: rgba(0, 0, 0, 1)"> isEmail;
    </span><span style="color: rgba(0, 0, 255, 1)">this</span>.isMobile =<span style="color: rgba(0, 0, 0, 1)"> isMobile;
    </span><span style="color: rgba(0, 0, 255, 1)">this</span>.emailLang =<span style="color: rgba(0, 0, 0, 1)"> emailLang;
    </span><span style="color: rgba(0, 0, 255, 1)">this</span>.msmLang =<span style="color: rgba(0, 0, 0, 1)"> msmLang;
    </span><span style="color: rgba(0, 0, 255, 1)">this</span>.mailNotify =<span style="color: rgba(0, 0, 0, 1)"> mailNotify;
    </span><span style="color: rgba(0, 0, 255, 1)">this</span>.mobileNotify =<span style="color: rgba(0, 0, 0, 1)"> mobileNotify;
    </span><span style="color: rgba(0, 0, 255, 1)">this</span>.state =<span style="color: rgba(0, 0, 0, 1)"> 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就完事了