Simple Java Insert using JDBC

Question

My Database Table Schema is something like

DocumentID: Name1: Name2: Name3: Name4: Location1: Location2: Location3....:Organization1:..

Now I have 3 Hashset's available having the above values (i.e one for name, one for location and one for organization)

In each single iteration of loop these hashset are being populated with above values.

At the end of each iteration the data from these hashset's is removed and new one's are created.

Now my problem is at each iteration I have to populate the sql table row (just 1 row each iteration) from these hashset values.

What I am not able to understand is if I have hard coded strings than simply I can use something like:

String sql = "INSERT INTO Table " +
               "VALUES ('100', 'Zara', 'Akli', '18','100', 'Zara', 'Ali', '18')";

However I need to iterate through each hashset and insert (something like above) the data of all 3 hashset's in a single row. I am confused of how to write such statement. Remember my table is initially completely empty so I cant't use the where clause (something like "insert into.....where documentID="23423")

As described, I don't see how you could. HashSet returns elements in an arbitrary order so when you try to pair up elements from two or more sets the results can change each time. — John Watts
– John Watts, Commented Oct 4, 2012 at 11:32

helios · Accepted Answer · 2012-10-09 10:23:33Z

1

Assuming you have created these Sets these way:

long DocumentId
names {"A", "B", "C"}
location {"1", "2", "3"}
and so on...

I think the easiest is to build dinamically the SQL to execute:

{
...
    StringBuilder sb = new StringBuilder("insert into mytable (");
    List<Object> params = new ArrayList<Object>();

    addColumnAndValue(sb, "DocumentID", docIdYouHaveSomewhere, params);
    int i = 0;
    for (String name: names)
       addColumnAndValue(sb, ",name" + i, name, params);
    i = 0;
    for (String location: locations)
       addColumnAndValue(sb, ",location" + i, location, params);
    // now close the thing
    sb.append(") values (?");
    for (i = 1; i<params.size(); i++)
       sb.append(",?");
    sb.append("=");

    // and now sb.toString() will contain valid SQL and the param values for a PreparedStatement will be in params array-list:

    PreparedStatement ps = conn.prepareStatement(sb.toString());
    for (i=0; i<params.size(); i++)
       ps.setObject(i+1, params.get(i));

    ps.executeUpdate(); // voi là!
    ...

}

private void add addColumnAndValue(StringBuilder sb, String columnName, Object value, List<Object> params) {
   sb.append(columnName);
   params.add(value);
}

edited Oct 9, 2012 at 10:23

answered Oct 4, 2012 at 11:36

helios

13.9k2 gold badges50 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

avinash shah Over a year ago

Fantastic & Brilliant !! I could not resist adding one more comment:)

helios Over a year ago

Well, thank you so much! It's just previous experience with generating SQL :)

Kent · Accepted Answer · 2012-10-04 11:38:09Z

0

i guess you need to first do some work on your 3 "HashSet"s.

Since you said the data in 3 Sets will finally go to single row in database. so I suggest that convert your 3 hashset into a Map, or at least a List, with same order as the fields in your insert statement. so that later you could set those values by name or index as parameters to your PS.

and I have never seen an insert statement like "Insert into table values (....) where id=123" are u sure it will work?

answered Oct 4, 2012 at 11:38

Kent

197k36 gold badges248 silver badges317 bronze badges

Collectives™ on Stack Overflow

Simple Java Insert using JDBC

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related