oracle查询条件拼接,两个表联合时，hql动态拼接，需要后置查询条件的解决方法...

两个表联合时，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。。。。 了