Loading ArrayList to Oracle table

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?
LVL 32
awking00Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
CEHJCommented:
The problem with that denormalised structure is that it would confine you to only three values. One-to-many might be better.
0
 
awking00Author Commented:
Actually, the arraylist will always contain 20 members, none of which will be null, and the table will also contain 20 "num" columns.
0
 
dpearsonCommented:
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.)

Doug

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

0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
CEHJCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.