how to pass list from java to oracle table and get all values at once

Question

I have a list of type Class A items.

Class A{
private String item1;
private String item2;
private long item3;
private long item4;
private long item5;
}

I am reading values of item1,item 2 from excel. I have to populate item3,item4 & item5 by passing item1 & item2.

Ex:
SELECT DISTINCT a.item3, 
  b.item4, 
  a.item5 
FROM ABC a
INNER JOIN XYZ b
AND a.item1     ='Test1'
AND b.item2     = 'Test2';

I am running above query for number of rows in excel and its effecting the performance.

How do I get all matching values from db at once in java. I know we can create a temp table in db and write a join to populate the ietm3,item4 & item5 But is there a way in java to handle this?

Hi, perhaps pass in the item1/item2 combinations as a CTE that the rest of the query can join against? — jspcal

Karuna Karuna · Accepted Answer · 2019-06-28T01:45:23

I have opted to create a global temporary table in db and join the fields. Instead of loading millions of records in to java object which will result in memory and performance issue creating a temporary table in database side is best for this case.

how to pass list from java to oracle table and get all values at once

1 Answers