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就完事了