Two tables union, HQL dynamic splicing, the need for a post-query condition solution

Solutions that need to add multiple query criteria after two tables are federated

One, this is my first time in the project team to do financial leasing problems encountered, the code is as follows:

Public Composedbean overduereminder (Map param) throws commonexception{
Composedbean Composedbean = new Composedbean ();
StringBuffer sql = new StringBuffer ();
Sql.append ("SELECT * FROM (")
0 application number, 1 applicant type
. Append ("Select M.asqbh,m.asqlx,")
Set up a customer's name by judging the type of applicant
. Append ("Case M.ASQLX if ' 1 ' then I.AKHXM if ' 2 ' then i.aqymc2 Else ' end as AKHXM,")
4 operator, 5 customer phone, 6 vehicle type, 7 product plan, 8 submission Date
. 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")//combined with the following table
. Append ("Select M.asqbh,m.asqlx,")
. Append ("Case M.ASQLX if ' 1 ' then I.AKHXM if ' 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 are NOT null and N.ACLLX = ' 1 ') where 1=1");
Application number
if (Stringutils.isnotblank (String) param.get ("ASQBH")) {
Sql.append ("and asqbh = '" +param.get ("ASQBH") + "'");
}
Customer Name
if (Stringutils.isnotblank (String) param.get ("AKHXM")) {
Sql.append ("and akhxm like '%" +param.get ("AKHXM") + "% '");
}

Composedbean.setsql (Sql.tostring ());
return Composedbean;
}

The SQL code is as follows:

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 '% Money Lin Quan% '

Add a query condition is to add where, we can write code in the main SQL where 1=1, for the subsequent SQL stitching to provide where, the following code can directly write and .... The

Two tables union, HQL dynamic splicing, the need for a post-query condition solution