arraylist in insert query

Question

i am trying to insert data from excel sheet into database in java. for that i have connected both the database using ODBC. excelsheet has 2 columns cname and title. after querying the Excel spreadsheet i stored the resultSet values of cname and title into arraylist.

      List Cname = new  ArrayList();
      List Title=new ArrayList();

            Cname.add(rs.getString("Cname"));
            Title.add(rs.getString("Title"));

this will result like this

      [aaa, bbb,cccc, dddd]
      [tree,human,animal,bird]

when i try to insert this into SQL database using insert query it is getting stored like this.

    statement1.executeUpdate("INSERT INTO dbo.company (Cname,Title)  SELECT      
   '"+Cname+"','"+Title+"'");


       Cname                               Title

    [aaa, bbb,cccc, dddd]        [tree,human,animal,bird]

but i want to store this as

      Cname               Title
     ___________________________
      aaa                  tree
      bbb                 human
      cccc                animal
      ddd                  bird

how do i do this???pls help to solve in this.

Andreas Dolk · Accepted Answer · 2012-02-14 09:03:56Z

1

You'll have to insert/update the list values, actually you're inserting the string representations of the entire lists..

Assuming, both lists do exist (not null) and have the same length, then this is a trivial solution:

for (int i = 0; i < Cname.size(); i++ {
   statement1.executeUpdate("INSERT INTO dbo.company (Cname,Title)  SELECT      
     '"+Cname.get(i)+"','"+Title.get(i)+"'");
}

Note - every java class has an implementation of the toString() method, and that method is called, when you "use an object as a string", like in the expression to create the SQL statement. For lists, the method returns a String that simply includes the (String-representations of) the list element in brackets.

edited Feb 14, 2012 at 9:03

answered Feb 14, 2012 at 8:40

Andreas Dolk

115k20 gold badges185 silver badges275 bronze badges

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

Comments

juergen d · Accepted Answer · 2012-02-14 08:40:08Z

1

Put a for loop around your insert statement:

for(int i = 0; i < Cname.size(); i++)
    statement1.executeUpdate("INSERT INTO dbo.company (Cname,Title) values ( '"+Cname.get(i)+"','"+Title.get(i)+"')");

answered Feb 14, 2012 at 8:40

juergen d

205k40 gold badges305 silver badges377 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:11:23Z

0

If your db is SQL Server, you can reference the followng Q & A. Just prepare xml data and pass to Stored Procedure.

How to Update SQL Server Table With Data From Other Source (DataTable)

edited May 23, 2017 at 12:11

CommunityBot

11 silver badge

answered Feb 14, 2012 at 8:41

Thit Lwin Oo

3,4283 gold badges22 silver badges23 bronze badges

Comments

Lukas Eder · Accepted Answer · 2012-02-14 10:35:25Z

You can create a single SQL statement of this form:

INSERT INTO dbo.company (Cname, Title) 
VALUES ('aaa', 'tree'), 
       ('bbb', 'human'),
       ('cccc', 'animal'),
       ('dddd', 'bird')

With JDBC:

StringBuilder sql = new StringBuilder();
sql.append("INSERT INTO dbo.company (Cname,Title) VALUES ");

String glue = "";
for (int i = 0; i < Cname.size(); i++) {
  sql.append(glue);
  sql.append("('");
  sql.append(Cname.get(i).toString().replace("'", "''"));
  sql.append("', '");
  sql.append(Title.get(i).toString().replace("'", "''"));
  sql.append("')");
  glue = ", ";
}

statement1.executeUpdate(sql.toString());

An alternative syntax (if the above doesn't work for Excel spreadsheets) is this:

INSERT INTO dbo.company (Cname, Title) 
SELECT 'aaa', 'tree' UNION ALL 
SELECT 'bbb', 'human' UNION ALL
SELECT 'cccc', 'animal' UNION ALL
SELECT 'dddd', 'bird'

Or with JDBC

StringBuilder sql = new StringBuilder();
sql.append("INSERT INTO dbo.company (Cname,Title) ");

String glue = "";
for (int i = 0; i < Cname.size(); i++) {
  sql.append(glue);
  sql.append("SELECT '");
  sql.append(Cname.get(i).toString().replace("'", "''"));
  sql.append("', '");
  sql.append(Title.get(i).toString().replace("'", "''"));
  sql.append("'");
  glue = " UNION ALL ";
}

statement1.executeUpdate(sql.toString());

Collectives™ on Stack Overflow

arraylist in insert query

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related