How to create a group of strings where each string has 1000 customer-id's to be used in Oracle query expression [duplicate]

Question

I have the input as customer number (CSR_ID BigDecimal(11,0)). The below code fails as Oracle query expression has the limitation of 1000:

//Start of the code String cust_ids = null; int cnt_cust = 0; java.util.StringJoiner joiner = new java.util.StringJoiner(","," ( "," ) "); //This is a loop which gets CSR_ID as input: joiner.add(row5.CSR_ID.toString()); cnt_cust++; //End of the code System.out.println(joiner); // (1,2,3,...,100045) cust_ids = joiner.toString();

Query looks like:

"select col1, col2 from customers where cust_id in " + "(1,2,3,...,100045)";

I wanted to split this like below:

"select col1, col2 from customers where" + " cust_id in (1,2,3...,1000)" + " or cust_id in (1001,.....2000)" + ..... ;

How do I build this query expression in bulk of 1000 customer-id. Note: customer-id is not be sequential, an random values.

Don't use string concatenation to build the query use a parameterised query and if you need to pass an array of values then bind a Java array parameter to an Oracle collection or use a temporary table. — MT0
– MT0, Commented Oct 3, 2019 at 10:30

LHCHIN · Accepted Answer · 2019-10-03 10:17:21Z

A solution without refinement is shown as follows and I think there are more elegant ways by using Stream in Java 8.

The concept is to transform the cust_ids into List, then you can divide the list with desired block size.

 int blockSize = 1000; List<String> cust_idsList = Arrays.asList(cust_ids.substring(1, cust_ids.length()-1).split(",")); System.out.println(cust_idsList.size()); StringBuilder sb = new StringBuilder(); sb.append("select col1, col2 from customers where cust_ids in ("); for (int i = 0; i < cust_idsList.size(); i++) { sb.append(cust_idsList.get(i)).append(","); if (i !=0 && i%blockSize == blockSize-1) { sb.setLength(sb.length()-1); if (i != cust_idsList.size()-1) { sb.append(")\n or cust_ids in ("); } } } sb.setLength(sb.length()-1); sb.append(")"); System.out.println(sb.toString());

Popeye · Accepted Answer · 2019-10-03 10:25:30Z

I think you can take advantage of multi-column comparision in IN as following:

select col1, col2 from customers where cust_id in (1,2,3,...,100045); -- This will fail select col1, col2 from customers where (cust_id,1) in ((1,1),(2,1),(3,1),...,(100045,1)); -- This will work

Cheers!!

Thanks, I want to build this string in java. How to do that.

Collectives™ on Stack Overflow

How to create a group of strings where each string has 1000 customer-id's to be used in Oracle query expression [duplicate]

2 Answers 2

Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Linked

Related