Write arraylist to a database java

Question

I have two arraylists to insert into 2 columns in a database table as follows:

arraylist1: 123444, 324555, 6423643, 532326
arraylist2: jkfdsl, nkjfsdlj, jdslkfjdlkj, jfsldjfsk, fjdlskjfs

I wrote the following code to insert the arraylists but it is not working. I will appreciate your help.

try {
// Prepare a statement to insert a record

String sql = "INSERT INTO soundsdata.splog (arraylist1, arraylist2) VALUES(?,?)";
pstmt = (PreparedStatement) con.prepareStatement(sql);

pstmt.setArray(1,sptospring);
pstmt.setString(2,eachList.toString());

// Insert the row
pstmt.executeUpdate();
}finally {
pstmt.close();
}

Answer 1

Here's something that you can do:

Assuming that you're trying to create one row, where the 1st column will contain the content of the first ArrayList in comma-separated format and the 2nd column will contain the content of the secondArrayList

StringBuilder buffer = new StringBuilder();
boolean processedFirst = false;
String firstParam = null, secondParam = null;

try{
    for(String record: arrayList1){
        if(processedFirst)
            buffer.append(",");
        buffer.append(record);
        processedFirst = true;
    }
    firstParam = buffer.toString();
}finally{
    buffer = null;
}
processedFirst = false;
buffer = new StringBuilder();
try{
    for(String record: arrayList2){
        if(processedFirst)
            buffer.append(",");
        buffer.append(record);
        processedFirst = true;
    }
    secondParam = buffer.toString();
}finally{
    buffer = null;
}
secondParam = buffer.toString();

String sql = "INSERT INTO soundsdata.splog (arraylist1, arraylist2) VALUES(?,?)";
try{
    psmt = (PreparedStatement) con.prepareStatement(sql);
    pstmt.setString(1,firstParam);
    pstmt.setString(2,secondParam);
    pstmt.executeUpdate();
}finally {
    pstmt.close();
}

Answer 2

You cannot store an ArrayList in a varchar column.

You need to store a string.

Answer 3

String[] stringArray = lists.toArray(new String[lists.size()]);

            String string1 = stringArray[0];
            String string2 = stringArray[1];
            String string3 = stringArray[2];
            String string4 = stringArray[3];
            String string5 = stringArray[4];

// then write query for insert into database

insert into tablename values(string1 ......)

Answer 4

Insert more than one record:

public String saveOrder(ArrayList<KIT0053MBean> insertList){     
    System.out.println("SaveOrder DAO  Method is calling " +insertList.size());
    Connection con=null;
    PreparedStatement ps2=null;
    try {
        con=open();
        con.setAutoCommit(false);
        con.getTransactionIsolation();
        ps2=con.prepareStatement(sql1);             
        Iterator<KIT0053MBean> it=insertList.iterator();            
        while(it.hasNext()){
            KIT0053MBean order=(KIT0053MBean)it.next();                 
            ps2.setString(1, model.getCustomerid());
            ps2.setString(2, model.getSerialid());                  
            ps2.addBatch();
        }               
        int i[]=ps2.executeBatch();
        System.out.println("###### insertion1### row   "+i.length);    
        con.commit();
        con.setAutoCommit(true);                
    } catch (Exception e) 
    {               
        System.out.println(e.getMessage());     
    }finally{
        close(con);         
        close(ps2);             
    }
}

Answer 5

PreparedStatement ps = connection.prepareStatement(query);            
for (Record record : arraylist1) {
    int index=1;                        
    ps.setString(index++,record.getItem());
    ps.setString(index++,record.getItem2());
    //
}
ps.executeBatch();
conn.commit();