0

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.

Improve this question
1
  • 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. Commented Oct 3, 2019 at 10:30

2 Answers 2

Reset to default
0

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());
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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!!

Improve this answer

2 Comments

Thanks, I want to build this string in java. How to do that.
Not an expert in Java. Sorry!!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.