We help IT Professionals succeed at work.

Loading ArrayList to Oracle table

awking00 asked
This is simplified but, assume an Oracle table like
tblNumbers(id, dt_crte, num1, num2, num3) and a Java ArrayList like [1,3,7],
How would I write the Java code to produce the following table values:
id = 1, dt_crte = 2015-03-02, num1 = 1, num2 = 3, num3 = 7) where the id would be sequential, the dt_crte todays date (i.e. date() if from Java or sysdate if from Oracle) and the num columns equivalent to the indexed columns of the ArrayList?
Watch Question

Top Expert 2016

The problem with that denormalised structure is that it would confine you to only three values. One-to-many might be better.
awking00Information Technology Specialist


Actually, the arraylist will always contain 20 members, none of which will be null, and the table will also contain 20 "num" columns.
It depends on how you're actually passing the query to the database, but something along these lines should get you started.

(If you're using a PreparedStatement rather than a Statement then it's similar code, but the values would be "?" and then you pass the actual params in a comma separated list at the end.)


int nColumns = 20 ;
long id = 1 ;
java.sql.Date now = new java.sql.Date() ;

// Assume you put 20 values into this somehow
ArrayList<Long> list = new ArrayList<>() ;

// Build up the list of columns
StringBuilder query = new StringBuilder("INSERT INTO tblNumbers(id, dt_crte") ;
for (int i = 0 ; i < nColumns ; i++) {
   query.append(", num" + (i+1) ) ;

// Build up the of values
query.append(") VALUES (" + id + "," + now) ;
for (int i = 0 ; i < nColumns ; i++) {
   query.append("," + list[i]) ;
query.append(")") ;

// Now execute this fullQuery
// (exactly how you do that depends on the framework you're using, but you have the query itself by this point)
String fullQuery = query.toString() ;

Open in new window

Top Expert 2016

Actually, the arraylist will always contain 20 members

Yes, that's not really the point. Let's say in a month's time they decide to have 25. That's going to give you a maintenance headache. A normalised one-to-many wouldn't.

Either way, you should probably use PreparedStatement