ORACLE SQL语句in超过1000个的处理方法

表现的情况:

sql 代码

Hibernate operation: could not execute query; bad SQL grammar [select count(*) as col_0_0_ from KAMS.TOTAL_CASH_PROMOTION_DATA totalcashp0_, KAMS.GROUP_USER_INFO groupuseri1_ where totalcashp0_.GROUP_USER_ID=groupuseri1_.GROUP_USER_ID and (groupuseri1_.GROUP_USER_CODE in ('JMU0200001' , 'TSN20131' , 'SIA20148' , 'HET20001' , 'SZX10007' , 'NGB60006' , 'NGB60002' , 'SIA20290' , 'URC30024' , 'KMG50002' , 'NGB20141' , 'SIA30050' , 'PEK20114' , 'CKG20005' , 'NGB20137' , 'HRB40002' , 'URC0200002' , 'SIA30010' , 'TSN40003' , 'PEK0200002' , 'SIA20208' , 'TSN20012' ,
'NGB20117' , 'SIA20172' , 'SIA20167' , 'PEK20109' , 'HAK20040' , 'SIA20257' , 'CTU20011' , 'SZX20056' , 'URC10047' , 'NKG20013' , 'TYN20065' , 'SZX20030' , 'SIA10045' , 'SZX20026' , 'TYN20055' , 'SIA20306' , 'TSN10011' , 'KMG20007' , 'KWL20002' , 'URC20048' , 'URC50006' , 'TSN20018' , 'SZX20061' , 'SZX20059' , 'URC20100' , 'SIA20255' , 'SIA30076' , 'SIA20110' , 'SIA20237' , 'SIA20197' , 'TYN20095' , 'URC10018' , 'SIA20205' , 'SIA30059' , 'SIA30060' , 'TYN10005' , 'TSN20127' , 'SIA20191' , 'HFE20001' , 'TSN20117' , 'SIA20261' , 'URC20152' , 'SIA20134' ,
'URC30017' , 'TYN20028' , 'PEK10034' , 'SYX20003' , 'TNA40001' , 'TSN20093' , 'SIA30013' , 'HGH20003' , 'TYN60004' , 'SIA10025' , 'SIA10055' , 'TYN10004' , 'TAO20002' , 'TYN20007' , 'TYN20035' , 'HAK0200001' , 'TSN40004' , 'SIA30070' , 'URC10042' , 'NNG20004' , 'HET20002' , 'URC10052' , 'URC30011' , 'CAN20002' , 'NGB20130' , 'JMU0200003' , 'TSN20032' , 'URC20123' , 'TSN20121' , 'TYN20092' , 'CTU10001' , 'SIA30095' , 'NGB20140' , 'SIA30045' , 'PEK10009' , 'PEK60003' , 'TSN0200003' , 'TYN20014' , 'URC20133' , 'SIA30078' , 'TSN10002' , 'PEK40009' , 'SIA20132' ,
'SIA60003' , 'NGB20148' , 'SZX60004' , 'HRB40001' , 'URC20087' , 'WUH20012' , 'SIA20006' , 'SIA30066' , 'TSN10005' , 'TYN20088' , 'TYN20052' , 'URC10013' , 'SIA10028' , 'SIA20226' , 'SIA60012' , 'NGB20103' , 'NGB20156' , 'PEK20041' , 'TYN20066' , 'SIA30100' , 'URC20050' , 'TSN20034' , 'URC20114' , 'SIA20181' , 'SZX10010' , 'SIA20038' , 'PEK20121' , 'SIA20307' , 'SIA30036' , 'TYN30039' , 'URC20120' , 'SIA20242' , 'TYN20072' , 'TYN20070' , 'SIA20248' , 'URC20139' , 'SIA20310' , 'TSN20013' , 'SIA20165' , 'PEK20112' , 'URC20141' , 'SIA20291' , 'INC80001' , 'PEK20104' ,
'TYN20084' , 'PEK30019' , 'TSN20136' , 'TSN30042' , 'HAK10006' , 'NGB20128' , 'PEK30014' , 'SIA20243' , 'PEK10040' , 'SIA10020' , 'SIA30046' , 'NGB60003' , 'TSN20008' , 'URC20116' , 'SYX20004' , 'CKG20004' , 'SIA30065' , 'SIA20145' , 'URC10053' , 'TSN20022' , 'URC50001' , 'CAN20003' , 'URC60002' , 'PEK20048' , 'CKG0200001' , 'SIA30178' , 'SIA20177' , 'URC10002' , 'TSN10007' , 'SIA20149' , 'TYN20012' , 'TYN20005' , 'SIA20185' , 'TYN20057' , 'SZX20046' , 'PEK20154' , 'null' , 'SZX10002' , 'SIA20313' , 'TYN20085' , 'URC20154' , 'TYN20015' , 'SIA20120' , 'CAN80002' ,
'SYX40002' , 'SYX30002' , 'URC20099' , 'TSN10010' , 'SIA20302' , 'SIA20223' , 'URC30023' , 'URC20001' , 'URC20049' , 'SIA20166' , 'SIA10051' , 'SIA20228' , 'NGB20129' , 'URC80001' , 'PEK20122' , 'SZX10012' , 'SIA20275' , 'NGB20033' , 'TSN20030' , 'URC20059' , 'TSN30010' , 'TSN20035' , 'HAK20052' , 'SIA20152' , 'HAK30011' , 'SIA20159' , 'SIA80002' , 'HAK20039' , 'URC20016' , 'SIA20155' , 'NGB20133' , 'SIA30052' , 'URC20098' , 'SZX20025' , 'KMG60001' , 'SIA10034' , 'SIA10042' , 'NGB10006' , 'NGB20096' , 'SIA20186' , 'SIA30061' , 'URC20161' , 'PEK20118' , 'TSN50002' ,
'HAK20025' , 'TYN20102' , 'SIA30038' , 'TYN20075' , 'CKG50002' , 'CSX0300003' , 'SIA20254' , 'SIA20046' , 'TSN20033' , 'URC10049' , 'TYN20025' , 'SIA10058' , 'SIA20233' , 'SIA20182' , 'SZX20053' , 'SIA20169' , 'URC20056' , 'NNG20005' , 'PEK20059' , 'TYN30038' , 'TSN30043' , 'PEK50002' , 'SIA20150' , 'TYN20071' , 'PEK30015' , 'PEK20116' , 'URC20126' , 'NGB20094' , 'TYN20048' , 'CTU0200001' , 'HAK0600002' , 'URC20118' , 'SIA30067' , 'SIA20231' , 'SIA20217' , 'NKG20015' , 'TYN20037' , 'TYN30041' , 'SZX20047' , 'URC20051' , 'SIA20207' , 'SIA30062' , 'TYN60002' ,
'NGB60001' , 'TSN50006' , 'SIA10061' , 'URC20031' , 'NGB20164' , 'SIA10017' , 'SIA20116' , 'TSN20025' , 'PEK20167' , 'SIA20269' , 'SIA20316' , 'TYN20016' , 'TSN20020' , 'NGB20134' , 'TYN20080' , 'WUH20001' , 'URC20091' , 'PEK20123' , 'SIA20308' , 'CKG60003' , 'TSN10009' , 'NGB20108' , 'TYN20077' , 'KWL20001' , 'URC20089' , 'TSN20074' , 'CTU20009' , 'NGB20139' , 'TYN20068' , 'HAK20045' , 'PEK20051' , 'NGB10004' , 'SIA30098' , 'WUH10003' , 'URC20140' , 'PEK10018' , 'SIA20218' , 'TYN20096' , 'SYX0100001' , 'TSN20113' , 'WUH20011' , 'TSN20010' , 'SIA20309' , 'TYN20034' ,
'NGB20152' , 'NGB20144' , 'SIA30053' , 'SIA10065' , 'SHA60001' , 'PEK30024' , 'SIA30091' , 'TYN30040' , 'URC20158' , 'NGB60005' , 'SIA20161' , 'SIA10052' , 'SIA20144' , 'SIA20030' , 'SIA80001' , 'TSN20133' , 'TSN20017' , 'NGB20002' , 'CSX0300002' , 'SIA20314' , 'SIA20178' , 'HAK60002' , 'SIA20190' , 'TYN20074' , 'JMU0400001' , 'SIA30051' , 'TSN20103' , 'SIA20283' , 'SIA10037' , 'SYX40005' , 'SIA20163' , 'SIA20204' , 'NGB20063' , 'SIA30039' , 'PEK20043' , 'URC20061' , 'SIA20130' , 'SIA20121' , 'NGB20123' , 'SIA60010' , 'SIA20222' , 'SIA20127' , 'TSN20046' , 'SIA30058' ,
'SIA20122' , 'TSN20123' , 'SIA30025' , 'URC20029' , 'SIA10059' , 'URC20149' , 'SIA20263' , 'URC20115' , 'SIA20136' , 'TSN30011' , 'SIA30072' , 'URC20057' , 'TSN50005' , 'PEK10045' , 'SIA20241' , 'URC20032' , 'PEK10025' , 'NGB20142' , 'SIA30088' , 'HAK10004' , 'TYN20054' , 'SIA20011' , 'HAK10016' , 'URC20153' , 'SZX20048' , 'PEK10035' , 'TYN20098' , 'SIA20133' , 'KMG0200002' , 'NGB20099' , 'PEK0100002' , 'KMG50003' , 'SZX10004' , 'TSN20045' , 'TSN20031' , 'TYN20045' , 'WUH20009' , 'SZX10011' , 'PEK80003' , 'SIA10027' , 'SIA20196' , 'URC0100002' , 'URC10014' , 'WUH20010' ,
 'TYN20017' , 'SIA20183' , 'SIA20095' , 'URC20138' , 'URC30014' , 'TYN20020' , 'TYN30031' , 'XMN0100001' , 'SIA20153' , 'SIA20209' , 'SIA30042' , 'SIA60008' , 'SIA30111' , 'TYN20094' , 'TSN20118' , 'SIA10022' , 'SIA20117' , 'SIA30068' , 'TSN20130' , 'TYN10001' , 'NGB20028' , 'URC10055' , 'TYN20008' , 'SIA10001' , 'HAK20050' , 'PEK20063' , 'TYN20060' , 'TSN20011' , 'WUH10002' , 'SIA20266' , 'URC20146' , 'SHA10004' , 'URC20159' , 'NGB20003' , 'SIA20225' , 'SIA20211' , 'SIA20256' , 'LHW80002' , 'SIA30048' , 'URC30002' , 'TSN50004' , 'NNG10002' , 'SIA20221' , 'TSN30008' ,
 'SIA20198' , 'TSN20110' , 'SIA20220' , 'SIA20318' , 'SZX20041' , 'TSN20019' , 'HAK60001' , 'SIA20212' , 'SIA10056' , 'SIA20229' , 'SIA20180' , 'SZX20058' , 'SIA20215' , 'SIA60014' , 'TSN20115' , 'CKG60002' , 'TYN20067' , 'TSN30012' , 'SIA20112' , 'PEK80002' , 'TYN20047' , 'URC50003' , 'SIA20158' , 'SIA30047' , 'PEK10049' , 'SIA20102' , 'SIA30040' , 'SIA20164' , 'HAK20008' , 'SIA20301' , 'CAN0200001' , 'TNA0400001' , 'URC20095' , 'URC10041' , 'SIA20048' , 'URC10024' , 'SIA20240' , 'TSN0200001' , 'SIA20020' , 'NGB20112' , 'PEK60002' , 'JMU0200002' , 'URC20132' , 'URC20024' ,
'PEK40014' , 'SYX10001' , 'TSN30031' , 'PEK10010' , 'SIA20219' , 'CTU20002' , 'SHA0200001' , 'KMG50005' , 'CTU0400001' , 'SIA20176' , 'TSN20126' , 'PEK10030' , 'SIA30022' , 'PEK10006' , 'SIA30041' , 'SIA20202' , 'SIA20029' , 'TYN20061' , 'SIA20138' , 'SIA20156' , 'SIA30032' , 'SZX20063' , 'URC10057' , 'URC30018' , 'SIA30069' , 'SIA30001' , 'TSN30029' , 'SIA20270' , 'URC0100001' , 'TSN10001' , 'TSN30006' , 'NGB20122' , 'SIA20251' , 'URC20117' , 'TSN20085' , 'SIA20143' , 'SIA20114' , 'URC10037' , 'PEK20012' , 'TSN20037' , 'URC20107' , 'PEK20158' , 'TYN20062' , 'URC20062' ,
'PEK10016' , 'TYN20044' , 'HAK10005' , 'SIA20265' , 'SIA30108' , 'SIA10036' , 'PEK20019' , 'URC20080' , 'URC20052' , 'CSX20023' , 'TYN20001' , 'WUH20015' , 'SIA20096' , 'TSN20145' , 'SIA20033' , 'URC20124' , 'TYN20039' , 'SZX30004' , 'NGB20107' , 'CSX20017' , 'WUH0200001' , 'NGB20132' , 'NGB20009' , 'SIA10009' , 'NGB20019' , 'HAK60006' , 'PEK20108' , 'LHW80001' , 'NNG20001' , 'URC20043' , 'TSN20038' , 'PEK10003' , 'TYN20051' , 'NGB20163' , 'NGB20111' , 'URC20097' , 'TSN50003' , 'NNG10001' , 'URC20147' , 'SIA30085' , 'SZX20060' , 'URC10005' , 'SIA20064' , 'SIA0300001' ,
'SIA30017' , 'HAK20038' , 'SIA30109' , 'TSN20135' , 'SIA20315' , 'SIA20129' , 'SYX0200002' , 'TSN20125' , 'SIA20142' , 'TSN20048' , 'SIA20317' , 'PEK20155' , 'SIA20044' , 'URC10039' , 'SIA30063' , 'SIA20173' , 'PEK10043' , 'TYN20091' , 'SIA10053' , 'SIA60007' , 'SIA20232' , 'SIA20042' , 'SIA20247' , 'TYN30035' , 'SIA10031' , 'HAK20015' , 'SIA20124' , 'SIA60013' , 'NGB20109' , 'SIA10018' , 'URC20156' , 'SIA20213' , 'YIN0200001' , 'URC50002' , 'CTU30001' , 'URC20042' , 'TSN10006' , 'SIA20260' , 'SZX20012' , 'URC10050' , 'TYN20042' , 'URC20055' , 'TYN20058' , 'SIA10050' ,
 'SIA20192' , 'SIA10019' , 'URC10051' , 'URC20127' , 'SIA30097' , 'SZX10006' , 'HAK20049' , 'SIA20101' , 'SZX20024' , 'TYN20036' , 'KWL30001' , 'URC30013' , 'SIA20272' , 'NGB20154' , 'SIA20234' , 'SIA20135' , 'SIA20141' , 'TYN20087' , 'TYN20076' , 'NKG50001' , 'SIA10029' , 'TYN10006' , 'SIA30071' , 'SIA20087' , 'CTU80001' , 'SZX20055' , 'SIA20201' , 'TSN10008' , 'URC10046' , 'URC30016' , 'URC10048' , 'TSN20132' , 'URC30009' , 'TYN20078' , 'SYX30001' , 'KMG20002' , 'SIA30094' , 'NGB20047' , 'SIA20250' , 'SIA20206' , 'SZX20049' , 'URC20034' , 'KWL20003' , 'PEK60004' ,
 'PEK20031' , 'URC20137' , 'TYN30043' , 'TSN20057' , 'URC30021' , 'TSN20029' , 'PEK20111' , 'SIA20171' , 'TSN20007' , 'TYN10003' , 'SIA60009' , 'URC10054' , 'URC10020' , 'NGB60004' , 'SZX20032' , 'TSN20116' , 'SIA20162' , 'SIA20131' , 'PEK30018' , 'URC20054' , 'URC40008' , 'SYX0200001' , 'SIA20184' , 'TYN30042' , 'URC20035' , 'SIA30006' , 'NGB20143' , 'URC10038' , 'SIA10039' , 'SIA30035' , 'PEK20160' , 'CAN80001' , 'SIA20139' , 'SIA10010' , 'SIA30105' , 'SIA10057' , 'SIA10066' , 'HGH20002' , 'TSN30026' , 'URC10043' , 'TYN20031' , 'SIA20036' , 'NGB20151' , 'SIA10038' ,
 'URC0100003' , 'SIA60002' , 'URC20109' , 'SIA20268' , 'SIA30092' , 'SIA0300002' , 'TYN20097' , 'SIA20125' , 'SYX10003' , 'NGB20124' , 'PEK10042' , 'TAO20001' , 'PEK20096' , 'TSN20036' , 'TSN20094' , 'URC20110' , 'TYN20041' , 'SIA30055' , 'TSN20026' , 'PEK20107' , 'KMG20006' , 'TYN20082' , 'URC20083' , 'SIA20128' , 'TYN20011' , 'CKG20008' , 'NKG20014' , 'PEK10046' , 'CSX10002' , 'PEK10007' , 'SIA20288' , 'PEK0100001' , 'TYN20069' , 'SIA20238' , 'PEK10050' , 'KWL20004' , 'TSN20016' , 'SIA20249' , 'URC0300001' , 'HAK20054' , 'URC30015' , 'URC20119' , 'URC20067' ,
'PEK10051' , 'SIA20195' , 'URC10001' , 'PEK20075' , 'SIA10043' , 'SZX50001' , 'SZX30006' , 'NGB20153' , 'SZX20054' , 'TYN20064' , 'TYN10002' , 'SZX20018' , 'SIA20264' , 'WUH20014' , 'URC30012' , 'WUH20016' , 'URC20155' , 'URC20058' , 'NGB20098' , 'CSX0300001' , 'NGB20150' , 'SIA20239' , 'SIA10003' , 'NGB20064' , 'URC20045' , 'TYN60003' , 'NGB0200004' , 'TYN20059' , 'KMG50001' , 'SIA20160' , 'NGB20115' , 'NGB20001' , 'TYN20100' , 'TYN30037' , 'SZX20044' , 'PEK20053' , 'TYN20013' , 'SIA30090' , 'SIA30054' , 'TSN20129' , 'SIA20007' , 'SHA10002' , 'CSX0100001' ,
'SIA20244' , 'TYN20056' , 'SIA30057' , 'SIA20193' , 'CTU20005' , 'KMG50004' , 'SZX10008' , 'SIA20305' , 'TSN20139' , 'TSN20041' , 'TSN20044' , 'PEK20113' , 'TYN20006' , 'URC20135' , 'URC10017' , 'URC50004' , 'TYN10007' , 'HRB20002' , 'SIA30024' , 'SIA20154' , 'CSX20003' , 'SIA20126' , 'URC20162' , 'SIA20151' , 'SIA20246' , 'TSN20109' , 'SIA20179' , 'SIA20188' , 'TYN20093' , 'SIA10030' , 'TYN30044' , 'URC20065' , 'TYN20083' , 'URC0200004' , 'PEK20009' , 'SIA20200' , 'TYN20073' , 'SIA30102' , 'URC10040' , 'SIA20253' , 'TYN30034' , 'SZX20057' , 'TYN20019' , 'PEK80001' ,
 'SIA20146' , 'KHN20001' , 'URC20136' , 'PEK20150' , 'SZX20052' , 'SIA10023' , 'URC20144' , 'TYN20033' , 'CKG20007' , 'SIA10041' , 'URC80002' , 'SIA10033' , 'SIA20027' , 'SIA20303' , 'PEK10001' , 'TYN20101' , 'PEK20106' , 'HAK10009' , 'SYX20007' , 'KMG0200001' , 'PEK10041' , 'CKG20001' , 'SIA20214' , 'SIA30029' , 'SIA20267' , 'URC20102' , 'WUH20013' , 'WUH0200002' , 'SIA30049' , 'HAK20002' , 'NGB20149' , 'NGB20110' , 'SIA10060' , 'SZX10005' , 'NGB20049' , 'TYN20010' , 'SIA20140' , 'NGB10007' , 'URC20157' , 'URC0200003' , 'SIA20203' , 'SIA30093' , 'TSN20014' , 'SIA20060' ,
'URC20030' , 'SZX20051' , 'URC50005' , 'TYN20046' , 'SIA20274' , 'TSN30013' , 'SIA20300' , 'TSN20004' , 'SIA20259' , 'CSX20022' , 'URC20046' , 'SIA20199' , 'SIA20312' , 'TSN20100' , 'TYN20099' , 'SZX20043' , 'HRB20001' , 'SIA20168' , 'TSN20120' , 'SZX20062' , 'TSN0200004' , 'PEK0200001' , 'TSN20015' , 'SZX20050' , 'SIA20170' , 'SIA20236' , 'URC20128' , 'SIA20210' , 'SIA30043' , 'CTU20006' , 'SIA20157' , 'TYN20026' , 'TYN20086' , 'SIA30044' , 'URC0200001' , 'TSN30004' , 'TYN60001' , 'SIA20271' , 'SIA20069' , 'TSN20052' , 'TNA40002' , 'HAK20037' , 'NGB20116' , 'TYN30036' ,
 'TSN20009' , 'TYN20004' , 'URC40006' , 'TSN20137' , 'HAK10018' , 'SIA20319' , 'SIA10012' , 'CTU20010' , 'URC20028' , 'URC20033' , 'URC20060' , 'HAK30002' , 'WNZ0300001' , 'PEK20033' , 'TYN20030' , 'TSN10012' , 'SIA30089' , 'SIA10032' , 'SZX20016' , 'SIA20119' , 'TYN20090' , 'TYN20018' , 'PEK20044' , 'SIA10026' , 'SIA20311' , 'SIA20189' , 'TYN30022' , 'TYN20003' , 'SIA20174' , 'NGB20007' , 'URC20096' , 'SIA30096' , 'PEK20162' , 'TSN20021' , 'KMG10004' , 'TYN20081' , 'TYN20009' , 'TSN20072' , 'TSN20027' , 'TSN50001' , 'NGB20038' , 'TYN20063' , 'HAK10010' , 'URC10008' ,
 'TYN20050' , 'URC20122' , 'NGB20101' , 'URC30022' , 'CTU80002' , 'TYN20079' , 'TYN20029' , 'SIA10063' , 'URC20068' , 'URC20125' , 'TSN20114' , 'SIA30073' , 'HRB0200001' , 'SZX20020' , 'TSN20128' , 'URC20041' , 'SIA20014' , 'TYN20053' , 'SIA20216' , 'SIA20282' , 'URC60003' , 'TYN30030' , 'TYN20089' , 'URC20148' , 'SIA20137' , 'PEK20119' , 'SIA20147' , 'KWL30002' , 'SIA10035' , 'SIA20262' , 'NGB20126' , 'URC10045' , 'PEK40008' , 'URC20142' , 'SIA20245' , 'TSN30036' , 'SIA20287' , 'CKG50001' , 'TSN20140' , 'SIA60011' , 'SIA30107' , 'TSN20024'))];
nested exception is java.sql.SQLException: ORA-01795: 列表中的最大表达式数为 1000

处理方法:

java 代码

Iterator agitr = args.iterator();
        Set rst = new HashSet();
        Set rst1 = new HashSet();
        while (agitr.hasNext()) {
            String gpcode = (String) agitr.next();
            boolean isretain = true;
            if (grpusr_code != null && grpusr_code.length() > 0) {
                isretain &= cdlist.contains(gpcode);
            }
            if (grpusr_name != null && grpusr_name.length() > 0) {
                isretain &= nmlist.contains(gpcode);
            }
            if (citystr != null && citystr.length() > 0) {
                isretain &= arlist.contains(gpcode);
            }
            if (propstr != null && propstr.length() > 0) {
                isretain &= pplist.contains(gpcode);
            }
            isretain &= uslist.contains(gpcode);
            if (isretain) {
                if (rst.size() <=800){
                    rst.add("'" + gpcode + "'");
                }else{
                    rst1.add("'" + gpcode + "'");
                }

            }
        }
        String cdstr = StringUtils.join(rst.iterator(), ",");
        String cdstr1 = StringUtils.join(rst1.iterator(),",");
        // 查询总积分

        if (cdstr != null && cdstr.length() > 0) {
            if (cdstr1 !=null && cdstr1.length()>0){
                String hql = "select tot_cash from TotalCashPromotionData as tot_cash where tot_cash.groupUserInfo.groupUserCode in ("
                    + cdstr + ") or tot_cash.groupUserInfo.groupUserCode in ("+cdstr1+")";
                return this.paginatedDAO.getPaginatedList(hql, page, pageSize);
            }else{
                String hql = "select tot_cash from TotalCashPromotionData as tot_cash where tot_cash.groupUserInfo.groupUserCode in ("
                    + cdstr + ")";
                return this.paginatedDAO.getPaginatedList(hql, page, pageSize);
            }

        } else {
            String hql = "select tot_cash from TotalCashPromotionData as tot_cash where tot_cash.groupUserInfo.groupUserCode in ('0')";
            return this.paginatedDAO.getPaginatedList(hql, page, pageSize);
        }