Insert values of arraylist into mysql table

Question

I am trying to insert values from arraylist into mysql table.

List lstUnique=new ArrayList<Object>();
//code to feed the data into list from resultset.
try{          
    Class.forName("com.mysql.jdbc.Driver");
    con=DriverManager.getConnection("jdbc:mysql://localhost:3306/MYdb","root","");
    stmt=con.createStatement();
    String sql1="select SourceFrom,Updated,Location_Type,Industry,ET_Rank,BT_Rank from mytable";
    rs1=stmt.executeQuery(sql1);
    rsmd=rs1.getMetaData();
    columnNumber=rsmd.getColumnCount();

    while(rs1.next()){
       lstUnique.add(rs1.getString("SourceFrom")+","+rs1.getString("Updated")+","+rs1.getString("Location_Type")+","+
                 rs1.getString("Industry")+","+rs1.getString("ET_Rank")+","+rs1.getString("BT_Rank")); 
    }
    String insertsql="";
    String SourceFrom=lstUnique.get(0).toString(); //its first column of the table.
    insertsql="Insert into java_uniquedata (SourceFrom,field2,field3,field4,field5) values(?,?,?,?,?)";
    PreparedStatement ps=con.prepareStatement(insertsql);

    //I tried this way also.
    for(int i=0;i<lstUnique.size();i++){
        SourceFrom=lstUnique.get(i).toString();
    }
    for(int i=0;i<lstUnique.size();i++){
        System.out.println("\n" + lstUnique.get(i).toString());
    }
    rs1.close();
    con.close();
}catch(Exception e){
    System.out.println(e);
}

But I am getting error

java.lang.IndexOutOfBoundsException: Index: 1, Size: 1

My list has only one record in it, which has total 5 columns' values. Can you guide me how do I fetch values of first record from arraylist and insert it into mysql table.

@AndrewTobilko how do I store all fields value from arraylist and then insert the same in various columns of mytable in mysql? — iks_in
– iks_in, Commented Sep 1, 2016 at 10:35
it seems you didn't fetch anything from the db, can you put a sout call inside the while(rs1.next()) and provide a result? — Andrew
– Andrew, Commented Sep 1, 2016 at 10:35
just debug , how namy times your for loop execute..If you dont know to debug try to remove your current code and print sysout — Jekin Kalariya
– Jekin Kalariya, Commented Sep 1, 2016 at 10:54

Markus Mitterauer · Accepted Answer · 2016-09-02 05:52:23Z

2

You should separate the values for your insert statement. Either use a custom datastructure (class) or use a List<String>.

Something like this might work:

List<List<String>> lstUnique=new ArrayList<>();  // note the List of List    
try {          
    /* ... your database query here ... */

    while(rs1.next()){
       List<String> values = new ArrayList<>(); // create list for values
       values.add(rs1.getString("SourceFrom"));
       values.add(rs1.getString("Updated"));
       values.add(rs1.getString("Location_Type"));
       values.add(rs1.getString("Industry"));
       values.add(rs1.getString("ET_Rank"));
       values.add(rs1.getString("BT_Rank"));
       lstUnique.add(values);  // add values for each row
    }

    String insertsql="Insert into java_uniquedata (SourceFrom,field2,field3,field4,field5) values(?,?,?,?,?)";
    PreparedStatement ps=con.prepareStatement(insertsql);
    for(List<String> values : lstUnique) { // use for each for rows
        for(int i=0;i<values.size();i++) { // set argument values to prepared statement
            ps.setString((i+1), values.get(i));
        }
        ps.execute(); // execute insert statement
    }
    ps.close();
} catch(Exception e){
    System.out.println(e);
} finally {  // close recordset and connection in finally-block! (or use 'try-with-resource'!)
    rs1.close();
    con.close();
}

edited Sep 2, 2016 at 5:52

answered Sep 1, 2016 at 11:03

Markus Mitterauer

1,6481 gold badge18 silver badges31 bronze badges

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

4 Comments

iks_in Over a year ago

Thank you for your help. I am trying on it. Will let you know if I find any difficulties.

iks_in Over a year ago

I am getting an error in inner for loop, 'for(List<String> values ; lstUnique.size() ) { // use for each for rows for(int i=0;i<values.size();i++) What do I do?

Markus Mitterauer Over a year ago

Sorry, my bad. Edited the answer.

iks_in Over a year ago

Oh my bad too. Did not notice such simple thing. Thanks a lot.. Trying again.

Markus Mitterauer · Accepted Answer · 2016-09-01 11:34:05Z

1

try this one

for(int i=0; i < lstUnique.size();i++){
     ps.setString(1, lstUnique.get(i).toString());
}
ps.execute(insertsql);

edited Sep 1, 2016 at 11:34

Markus Mitterauer

1,6481 gold badge18 silver badges31 bronze badges

answered Sep 1, 2016 at 10:47

Chandu D

5013 silver badges17 bronze badges

3 Comments

iks_in Over a year ago

But do I need to change in my insert query? Its having ? in values (?,?) field.

iks_in Over a year ago

I've tried this way but now its giving me an error for com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?,?,?,?,?,' at line 1

Chandu D Over a year ago

i dont know unfortunately

Markus Mitterauer · Accepted Answer · 2016-09-01 12:32:39Z

1

I tried this and it inserts data into database

ArrayList<String> lis = new ArrayList<String>();
lis.add("pare1");
lis.add("2");

//code to feed the data into list from resultset.
Class.forName("com.mysql.jdbc.Driver"); 
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "paresh");
String insertsql="";
insertsql="Insert into stud (name,age) VALUES (?,?)";
PreparedStatement ps=connection.prepareStatement(insertsql);

//this will set value for your insert statement

System.out.println(lis.get(0).toString());
ps.setString(1, lis.get(0).toString());
ps.setInt(2, Integer.parseInt(lis.get(1)));
System.out.println(ps);
ps.execute(insertsql);

edited Sep 1, 2016 at 12:32

Markus Mitterauer

1,6481 gold badge18 silver badges31 bronze badges

answered Sep 1, 2016 at 10:39

paresh

3511 gold badge3 silver badges13 bronze badges

4 Comments

Markus Mitterauer Over a year ago

Question was updated. lstUnique does not contain the single values, but a concatenated string. That might be one of the problems.

iks_in Over a year ago

@paresh, I've tried this way also but it still gave me the same error.

iks_in Over a year ago

@MarkusMitterauer, yes I felt the same. Then what is the way to fetch data from arraylist and insert into mysql table?

Markus Mitterauer Over a year ago

@iks_in See my new answer.

Markus Mitterauer · Accepted Answer · 2016-09-02 06:47:35Z

1

Completely different aproach for a solution to the task, if it is not mandatory to be solved in Java(?):

Since your code sample gives the impression that you're reading from and writing to the same database, you might also consider to copy the data in-database using the SQL INSERT-SELECT Syntax:

INSERT INTO java_uniquedata (SourceFrom, field2, field3, field4, field5) 
    SELECT DISTINCT SourceFrom, Updated, Location_Type, Industry, ET_Rank, BT_Rank 
    FROM mytable;

See MySQL INSERT ... SELECT Syntax.

edited Sep 2, 2016 at 6:47

answered Sep 2, 2016 at 6:41

Markus Mitterauer

1,6481 gold badge18 silver badges31 bronze badges

6 Comments

iks_in Over a year ago

Thanks for your kind help. +1 for that. I am aware of this. But I am doing it in java because I actually want to find unique records from my huge database. So I take the data into resultset then add it in a list, through list, I will insert first row into the table, then I will compare each row with the existing rows of table whether they are duplicates, if yes then I will not insert such record, if its partially duplicate then I will update the existing record of my table else I will insert the row.

Markus Mitterauer Over a year ago

You're welcome. If it's just about the data, you can ensure uniqueness with SQL too, using the DISTINCT keyword. Updated my answer. // btw. you can do that too with your Java solution.

iks_in Over a year ago

I've tried distinct too, but I've very very bad data. So the problem is, if field1,field2,f3 are there in one row while firld1,field4,field5 in another row, it didnt give me unique record. And there are actually 45 columns in that database. Its too messy. They way data is stored, its difficult to find unique records using SQL.

Markus Mitterauer Over a year ago

Ah. Okay. Couldn't see that from the code in your questions. That would probably make it difficult with SQL.

iks_in Over a year ago

Yeah. That's a big challenge for me now.

|

Collectives™ on Stack Overflow

Insert values of arraylist into mysql table

4 Answers 4

4 Comments

3 Comments

4 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

3 Comments

4 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related