兩個表聯合時，hql動態拼接，需要後置查詢條件的解決辦法

兩表聯合後，需要添加多個查詢條件的解決方案

一、 這是我第一次在項目組做融資租賃時遇到的問題，代碼如下：

public ComposedBean OverdueReminder(Map param) throws CommonException{
ComposedBean composedBean = new ComposedBean();
StringBuffer sql = new StringBuffer();
sql.append(" select * from (")
//0申請編號， 1 申請人類型
.append(" select m.asqbh,m.asqlx, ")
//通過對申請人類型的判斷設置客戶姓名
.append(" case m.asqlx when '1' then i.akhxm when '2' then i.aQymc2 else '' end as akhxm, ")
//4操作人員， 5客戶電話， 6車輛類型， 7產品方案， 8提交日期
.append(" m.aczry,i.asjhm,n.acllx,m.acpfamc,m.dsctjrq ")
.append(" from lb_apply_car n ")
.append(" left join lb_apply_lessee_info i on i.asqbh = n.asqbh ")
.append(" left join lb_apply_main m on m.asqbh = n.asqbh ")
.append(" where n.acllx = '1' and m.dfkrq is not null ")
.append(" and not exists (select 1 from tinfo_insurance_policy p where n.asqbh = p.asqbh) ")
.append(" union ")//聯合下表
.append(" select m.asqbh,m.asqlx, ")
.append(" case m.asqlx when '1' then i.akhxm when '2' then i.aQymc2 else '' end as akhxm, ")
.append(" m.aczry,i.asjhm,n.acllx,m.acpfamc,m.dsctjrq from lb_apply_car_invoice t ")
.append(" left join lb_apply_main m on m.asqbh = t.asqbh ")
.append(" left join lb_apply_car n on n.asqbh = t.asqbh ")
.append(" left join lb_apply_lessee_info i on m.asqbh =i.asqbh ")
.append(" where t.aclfph is null and m.dfkrq is not null and n.acllx = '1' ) where 1=1");
//申請編號
if(StringUtils.isNotBlank((String)param.get("asqbh"))){
sql.append(" and asqbh ='"+param.get("asqbh")+"'");
}
//客戶姓名
if(StringUtils.isNotBlank((String)param.get("akhxm"))){
sql.append(" and akhxm like '%"+param.get("akhxm")+"%'");
}

composedBean.setSql(sql.toString());
return composedBean;
}

sql 代碼如下：

SELECT
*
FROM
(
SELECT
m.asqbh,
m.asqlx,
CASE m.asqlx
WHEN '1'
THEN i.akhxm
WHEN '2'
THEN i.aQymc2
ELSE ''
END AS akhxm,
m.aczry,
i.asjhm,
n.acllx,
m.acpfamc,
m.dsctjrq
FROM
lb_apply_car n
LEFT JOIN
lb_apply_lessee_info i
ON
i.asqbh = n.asqbh
LEFT JOIN
lb_apply_main m
ON
m.asqbh = n.asqbh
WHERE
n.acllx = '1'
AND m.dfkrq IS NOT NULL
AND NOT EXISTS
(
SELECT
1
FROM
tinfo_insurance_policy p
WHERE
n.asqbh = p.asqbh)
UNION
SELECT
m.asqbh,
m.asqlx,
CASE m.asqlx
WHEN '1'
THEN i.akhxm
WHEN '2'
THEN i.aQymc2
ELSE ''
END AS akhxm,
m.aczry,
i.asjhm,
n.acllx,
m.acpfamc,
m.dsctjrq
FROM
lb_apply_car_invoice t
LEFT JOIN
lb_apply_main m
ON
m.asqbh = t.asqbh
LEFT JOIN
lb_apply_car n
ON
n.asqbh = t.asqbh
LEFT JOIN
lb_apply_lessee_info i
ON
m.asqbh =i.asqbh
WHERE
t.aclfph IS NULL
AND m.dfkrq IS NOT NULL
AND n.acllx = '1' )
WHERE
1=1
AND asqbh ='0007576'
AND akhxm LIKE '%錢林泉%'

添加查詢條件就是添加where，我們可以在主體sql中通過寫代碼 where 1=1，來為後面的sql 拼接提供where ，後面的代碼就可以直接書寫and。。。。 了