[Original]java or JS code logic to handle the problem of breaking the limit of Oracle SQL "in" length

Note: This article is from: Bo owner's own research to verify the feasibility

[Original]java or JS code logic to handle the problem of breaking the limit of Oracle SQL "in" length

In many cases, use SELECT * from Tab_name t where t.id in (...).  1000); The fixed way to query the data. There will be a situation where this occurs:

The content in the in is too long, while the Oracle database supports 100 content.

So what should we do about this situation? In fact it is very simple, in itself is all the content or query.

The way to do this is to split the inside parameters into a number.

The specific implementation is as follows:

Implementation of the 1:java code:

1   PublicList stapletoczgkzxnew (daofactory daofactory,string orgtype, List lstsource, list LSTVO_JJFL)throwsSQLException, Exception {2 3List Lsttarget =NewArrayList ();4 5         Try{6 /** 7 * Required economic classification of auxiliary accounting 8 * /9List LSTIDS_JJFL =NewArrayList ();Ten //Remove the ID from the collection One if(Lstvo_jjfl.size () >0) { AIterator ITSOURCE_JJFL = Lstvo_jjfl.iterator (); -       while(Itsource_jjfl.hasnext ()) { -Zfpzvo vo = (zfpzvo) itsource_jjfl.next (); theLstids_jjfl.add (String.valueof (vo.getgk_zfpz_id ())); -} -  //---Start 20180315 added-------purpose: To increase the execution speed of the generated data and to query the data according to the voucher number. Cancels the merge problem with the same data--------------------- - //The ID is stitched into a string 1,2,3,4,5,6 +         intListlentgh=lstids_jjfl.size (); -     intintfalg=50;//in contains the value of the valve value +List lsid=NewArrayList ();//Store ID string Collection AString stra= ""; at      for(inti=0;i<listlentgh;i++) { -    if(I%intfalg = = 0 && i! = 0) { -Lsid.add (Stra); -Stra= ""; -} -   if(I%intfalg = = 0) { inStra = ""+lstids_jjfl.get (i); -}Else{ toStra = stra+ ","+lstids_jjfl.get (i); +} -   if(I+1==listlentgh && (listlentgh!=1 &&i%intfalg! = 0)) { theLsid.add (Stra); *}Else if(I+1==listlentgh && listlentgh ==1 && i%intfalg = = 0) { $Lsid.add (Stra);Panax Notoginseng} -} the   //---End 20180315 new---------------------------- +  A  the      //---Start 20180315 modify-------Purpose: Increase the execution speed of the generated data--------------------- +List gkzfpz_jjfllist =zwvchcommonhelper.factory (). Getgkzfpz_jjfllist (Daofactory, Lsid,orgtype); -｝ $  $ //The following is the logical processing of splicing SQL -  -  the*/ -      PublicList Getgkzfpz_jjfllist (daofactory daofactory, List lstzfpzid,string orgtype) {WuyiString strSQL = "SELECT * from View_k3_zfzxz_zfpz v WHERE 1 = 1"; thelist<map<string, Object>>lstzfpz =NewArrayList (); -  Wu     Try{ -  About   if(Lstzfpzid.size () >0) { $strSQL + = "and ("; -            for(intindex=0; index <lstzfpzid.size (); index + +) { -           if(index = = 0) { -strSQL + = "V.id in ("+lstzfpzid.get (Index) +")"; A}Else{ +strSQL + = "OR v.id in ("+lstzfpzid.get (Index) +")"; the} -  $} thestrSQL + = ")"; the} theLog.info ("_______ Treasury Payment Center account: Direct Payment voucher ___________strsql="+strsql); theLstzfpz = Daofactory.getdao (). Getjdbctemplate (). queryForList (strSQL); -                for(intI=0;i<lstzfpz.size (); i++) { inLstzfpz.get (i). put ("Org_type", Orgtype); the} theLog.info ("_______ Treasury Payment Center account: Direct Payment voucher ___________ Query result ="+lstzfpz.size () +";======:"+lstzfpz.tostring ()); About  the}Catch(Exception e) { theE.printstacktrace (); the} +      returnLstzfpz; -} the 

2: Logic processing using JS

1             varRows=[];2 3    rows = Maingrid.getselections ();5 if(Rows.length>0) {6 Debugger;7                  varBalance_type= "";8                A                 varRowslength=rows.length; -             varintfalg=30;//in contains the value of the valve value -             varIds=[];//id Mosaic Storage Collection the             varStr_id= ""; -              for(vari=0;i<rowslength;i++) { -            varRow =rows[i]; -            if(I%intfalg = = 0 && i! = 0) { +Ids.push (str_id); -Str_id= ""; +} A           if(I%intfalg = = 0) { atstr_id = ""+row.id; -}Else{ -str_id = str_id+ ","+row.id; -} -           if(I+1==rowslength && (rowslength!=1 &&i%rowslength! = 0)) { -Ids.push (str_id); in}Else if(I+1==rowslength && rowslength ==1 && i%intfalg = = 0) { -Ids.push (str_id); to} +} -｝ the   

[Original]java or JS code logic to handle the problem of breaking the limit of Oracle SQL "in" length