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

Let me get this straight: the entire content of arraylist1 goes to one column and that of arraylist2 goes to the second column? and the values would be stored in a comma-separated format? is that what you're looking for? — Sujay
– Sujay, Commented Aug 26, 2012 at 4:00

Sujay · Accepted Answer · 2012-08-26 04:08:29Z

5

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

answered Aug 26, 2012 at 4:08

Sujay

6,7812 gold badges32 silver badges49 bronze badges

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

1 Comment

Sujay Over a year ago

@bdfios: Glad to help! Let me know if this has worked for you :)

SLaks · Accepted Answer · 2012-08-26 03:36:13Z

2

You cannot store an ArrayList in a varchar column.

You need to store a string.

answered Aug 26, 2012 at 3:36

SLaks

891k182 gold badges1.9k silver badges2k bronze badges

5 Comments

João Silva Over a year ago

He's converting the list to a String.

bdfios Over a year ago

Thank you. Is there a way to convert an arraylist to a string on the fly? I tried a cast and it din't work.

bdfios Over a year ago

Or what datatype do you think I could change the column to so as to accept an arraylist?

SLaks Over a year ago

@bdfios: What do you want the string to say? Most databases do not support array columns.

bdfios Over a year ago

@Slaks, all I want to do is to store data in the form of "123444, 324555, 6423643, 532326" to each column. As I described in my post, I already have the data in an arraylist, but since I could not write it as arraylist into the database, that is why I am looking for a workaround, hence my question, asking if it is possible to convert the arraylist to a string on the fly, so that I can then store the string in a database.

Mohammod Hossain · Accepted Answer · 2012-08-26 04:30:48Z

2

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

edited Aug 26, 2012 at 4:30

answered Aug 26, 2012 at 3:46

Mohammod Hossain

4,1042 gold badges28 silver badges37 bronze badges

6 Comments

bdfios Over a year ago

Thanks. I am a little confused about "Record record: arraylist1". Is Record a class library that I need to import? It is underlined as an error and I don't know how to handle the Record. BTW, I'm a newbie, thanks.

Mohammod Hossain Over a year ago

Record is an entity object of list generic type and record is item.eg. Book book

Sujay Over a year ago

@MohammodHossain: what is the point of initializing index to 1 and then trying to increment it in the same loop? You would basically end up resetting the index to 1

Mohammod Hossain Over a year ago

for each loop will be continue on list size . after insert one record index will reinitialize

Mohammod Hossain Over a year ago

After insert one record index will be reinitialize and for each loop continue until list size 0

|

Sujay · Accepted Answer · 2012-08-26 07:51:03Z

0

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

edited Aug 26, 2012 at 7:51

Sujay

6,7812 gold badges32 silver badges49 bronze badges

answered Aug 26, 2012 at 5:07

Mohammod Hossain

4,1042 gold badges28 silver badges37 bronze badges

1 Comment

Sujay Over a year ago

Did you just paste a part of code that you had recently coded? Is there any specific reason of having con.getTransactionIsolation(); over here? OP never had KIT0053MBean used anywhere either! I can find a lot of issues with your code over here, not to mention how much confused you would make the OP with this answer! I really don't understand your reasoning behind posting this answer, when you already have another one over here (which obviously you can edit)

Sandip Ingle · Accepted Answer · 2016-01-14 11:24:46Z

-2

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

answered Jan 14, 2016 at 11:24

Sandip Ingle

1

Collectives™ on Stack Overflow

Write arraylist to a database java

5 Answers 5

1 Comment

5 Comments

6 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

5 Comments

6 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related