CODEWITHSUNDEEP
javamysqlarraylistindexoutofboundsexception
iks_in
iks_in

Reputation: 133

Insert values of arraylist into mysql table

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.

Upvotes: 0

Views: 6355

Answers (4)

Markus Mitterauer
Markus Mitterauer

Reputation: 1610

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.

Upvotes: 1

Markus Mitterauer
Markus Mitterauer

Reputation: 1610

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();
}

Upvotes: 2

paresh
paresh

Reputation: 351

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);

Upvotes: 1

Chandu D
Chandu D

Reputation: 501

try this one 

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

Upvotes: 1

Related Questions