Batch insert from jTable to MySQL Database

[Zdneth_1]

Hello Professionals out there, i am making a point-of-sales systems in java. And i have a jTable named "tbitempurchase" wherein I add the ordered items using an add button.I also have a table in mysql database named "itemSold". Now my problem goes like this, I want to insert the data added from the "tbitempurchase" to mySQL table "itemSold" all at once using a save button with a batch insert query. And i just don't know how to do it. Please, i need help with this. It would be an honor to receive generous and wise comments from you guys.

[JamesCherrill]

Can you please be more precise about the help you need? Eg Do you need to know how to get the data froma JTable? Do you need to know know how to execute an SQL insert statement? Etc?

[Zdneth_1]

yeah.. i want to get the data from the JTable and insert it in mysql using a batch query

[JamesCherrill]

yeah... you already made that clear.

[Zdneth_1]

I have a code like this sir JamesCherrill

String bsave="Insert into itemsold (itemid, qpurchase,qamount) values(?,?,?)"; 
             preps=conn.prepareStatement(bsave);
             final int batchSize= 1000;
             int count=0;
             Object [][] temp = new Object[data.length+1][5];
             for(int i=0; i<data.length; i++)
             {
                 for(int j=0;j<5;j++)
                 {
                     //temp[i][j]= data[i][j];
                   // cashier.tbsalesrec.getValueAt(i, j);
                    preps.setObject(1, cashier.tbsalesrec.getValueAt(i, 0).toString());
                    preps.setObject(2, cashier.tbsalesrec.getValueAt(i, 3).toString());
                    preps.setObject(3, cashier.tbsalesrec.getValueAt(i, 4).toString());
                    preps.addBatch();

                    if(++count % batchSize == 0)
                    {
                        preps.executeBatch();
                    }
                 }
             }
             preps.executeBatch();
             preps.close();

            JOptionPane.showMessageDialog(null, "Transaction Saved");

[Zdneth_1]

I have made a code like this sir JamesCherrill

String bsave="Insert into itemsold (itemid, qpurchase,qamount) values(?,?,?)"; 
             preps=conn.prepareStatement(bsave);
             final int batchSize= 1000;
             int count=0;
             Object [][] temp = new Object[data.length+1][5];
             for(int i=0; i<data.length; i++)
             {
                 for(int j=0;j<5;j++)
                 {
                     //temp[i][j]= data[i][j];
                   // cashier.tbsalesrec.getValueAt(i, j);
                    preps.setObject(1, cashier.tbsalesrec.getValueAt(i, 0).toString());
                    preps.setObject(2, cashier.tbsalesrec.getValueAt(i, 3).toString());
                    preps.setObject(3, cashier.tbsalesrec.getValueAt(i, 4).toString());
                    preps.addBatch();

                    if(++count % batchSize == 0)
                    {
                        preps.executeBatch();
                    }
                 }
             }
             preps.executeBatch();
             preps.close();

            JOptionPane.showMessageDialog(null, "Transaction Saved");

[JamesCherrill]

OK. That's a start...
You don't need the inner loop at all - just look at its content and you will see that j is not used anywhere. Similarly you need/use your temp array.

Posting in batches of 5 is a sensible idea, but maybe start with just 1 then 2 records first before trying batches >5.

It's not perfect yet, but you're at the point whee you should start to execute and debug your program. Add lots of print statements in that code so youy can see what is being execute and how often, and print the values of the main variables so you can see if they are being set as you expect. It probably won't work fist time, but the print statements will allow you to see what's happening, where it's going wrong, and what needs to change to fix it.