Insert Mulitple Row Data from Jtable into database
I am trying to save multiple row data from a JTable to a database. Here is my code for reference:
try{
int rows=tblCO2.getRowCount();
for(int row = 0; row<rows; row++)
{
String coitemname = (String)tblCO2.getValueAt(row, 0);
String cocateg = (String)tblCO2.getValueAt(row, 1);
String codesc = (String)tblCO2.getValueAt(row, 2);
String coloc = (String)tblCO2.getValueAt(row, 3);
String coitemtagno = (String)tblCO2.getValueAt(row, 4);
try
{
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/iotams",user,pass);
conn.setAutoCommit(false);
String queryco = "Insert into tblcheckout(CheckoutID,ItemTagNo,ItemName,Category,Description,Location) values (?,?,?,?,?)";
pst = conn.prepareStatement(queryco);
pst.setString(1, coitemname);
pst.setString(2, cocateg);
pst.setString(3, codesc);
pst.setString(4, coloc);
pst.setString(5, coitemtagno);
pst.addBatch();
}
catch(Exception e)
{
}
}
pst.executeBatch();
conn.commit();
}
catch(Exception e){
JOptionPane.showMessageDialog(this,e.getMessage());
}
The problem is that it only inserts one row of data into the database. Can someone please help me? :( thanks!
+3
source to share
2 answers
Remove the following lines from the loop and place before the loop
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/iotams",user,pass);
conn.setAutoCommit(false);
String queryco = "Insert into tblcheckout(CheckoutID,ItemTagNo,ItemName,Category,Description,Location) values (?,?,?,?,?)";
pst = conn.prepareStatement(queryco);
Example: Replace the code with the following code
try{
int rows=tblCO2.getRowCount();
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/iotams",user,pass);
conn.setAutoCommit(false);
String queryco = "Insert into tblcheckout(CheckoutID,ItemTagNo,ItemName,Category,Description,Location) values (?,?,?,?,?)";
pst = conn.prepareStatement(queryco);
for(int row = 0; row<rows; row++)
{
String coitemname = (String)tblCO2.getValueAt(row, 0);
String cocateg = (String)tblCO2.getValueAt(row, 1);
String codesc = (String)tblCO2.getValueAt(row, 2);
String coloc = (String)tblCO2.getValueAt(row, 3);
String coitemtagno = (String)tblCO2.getValueAt(row, 4);
pst.setString(1, coitemname);
pst.setString(2, cocateg);
pst.setString(3, codesc);
pst.setString(4, coloc);
pst.setString(5, coitemtagno);
pst.addBatch();
}
pst.executeBatch();
conn.commit();
}
catch(Exception e){
JOptionPane.showMessageDialog(this,e.getMessage());
}
Then launch it to make it work.
For an example of package insertion here https://my.vertica.com/docs/5.0/HTML/Master/14878.htm
+5
source to share
the above code cannot work in netbeans, However, I made a version for netbeans.
try{
int rows=jTable1.getRowCount();
for(int row = 0; row<rows; row++)
{
Integer qty = (Integer)jTable1.getValueAt(row, 0);
Double unitprice = (Double) jTable1.getValueAt(row, 1);
String description = (String)jTable1.getValueAt(row, 2);
Double total = (Double)jTable1.getValueAt(row, 3);
String queryco = "Insert into invoice(qty,unitprice,description,total) values ('"+qty+"','"+unitprice+"','"+description+"','"+total+"')";
pst = conn.prepareStatement(queryco);
pst.execute();
}
JOptionPane.showMessageDialog(null, "Successfully Save");
}
catch(Exception e){
JOptionPane.showMessageDialog(this,e.getMessage());
}
0
source to share