java - how to insert data from jtable to mysql database -
i want insert jtable multiple rows info mysql database on save button click. gives error....
this code...
package my.bill; import java.awt.*; import java.awt.event.*; import java.awt.event.*; import java.io.*; import java.lang.*; import java.sql.*; import java.sql.connection; import java.sql.drivermanager; import java.sql.preparedstatement; import java.sql.sqlexception; import java.util.*; import java.util.calendar; import java.util.gregoriancalendar; import javax.swing.*; import javax.swing.event.*; import javax.swing.table.*; import javax .swing.table.defaulttablemodel; public class bill extends javax.swing.jframe { /** * creates new form bill */ object[][] data=null; string[] columnnames = new string[2]; /*static final string jdbc_driver = "com.mysql.jdbc.driver"; static final string db_url="jdbc:mysql://localhost:3306/gdb?zerodatetimebehavior=converttonull"; //static final string db_url = "jdbc:mysql://localhost/"; // database credentials static final string user = "root"; static final string pass = "root";*/ public bill() { initcomponents(); currentdate(); } public void currentdate(){ calendar cal=new gregoriancalendar(); int month=cal.get(calendar.month); int year=cal.get(calendar.year); int day=cal.get(calendar.day_of_month); date.settext(day+"-"+(month+1)+"-"+year); } @suppresswarnings("unchecked") // <editor-fold defaultstate="collapsed" desc="generated code"> private void initcomponents() { jlabel1 = new javax.swing.jlabel(); date = new javax.swing.jtextfield(); jscrollpane2 = new javax.swing.jscrollpane(); jtable1 = new javax.swing.jtable(); netprofit = new javax.swing.jbutton(); netsum = new javax.swing.jtextfield(); save = new javax.swing.jbutton(); jmenubar1 = new javax.swing.jmenubar(); jmenu1 = new javax.swing.jmenu(); jmenu2 = new javax.swing.jmenu(); setdefaultcloseoperation(javax.swing.windowconstants.exit_on_close); jlabel1.settext("date"); date.addactionlistener(new java.awt.event.actionlistener() { public void actionperformed(java.awt.event.actionevent evt) { dateactionperformed(evt); } }); jtable1.setborder(javax.swing.borderfactory.createlineborder(new java.awt.color(0, 0, 0))); jtable1.setfont(new java.awt.font("tahoma", 0, 18)); // noi18n jtable1.setmodel(new javax.swing.table.defaulttablemodel( new object [][] { {"", null, null, null, null, null}, {"", null, null, null, "", null}, {null, null, null, null, null, null}, {null, null, null, null, null, null}, {null, null, null, null, null, null}, {null, null, null, null, null, null}, {null, null, null, null, null, null}, {null, null, null, null, null, null}, {null, null, null, null, null, null}, {null, null, null, null, null, null}, {null, null, null, null, null, null}, {null, null, null, null, null, null}, {null, null, null, null, null, null}, {null, null, null, null, null, null}, {null, null, null, null, null, null}, {null, null, null, null, null, null}, {null, null, null, null, null, null}, {null, null, null, null, null, null}, {null, null, null, null, null, null}, {null, null, null, null, null, null} }, new string [] { "job no", "item", "billed amount", "parts cost", "net profit", "percentage" } )); jtable1.setcolumnselectionallowed(true); jtable1.setgridcolor(new java.awt.color(0, 0, 0)); jtable1.setrowheight(20); jtable1.setrowmargin(2); jtable1.setselectionbackground(new java.awt.color(255, 255, 255)); jtable1.addinputmethodlistener(new java.awt.event.inputmethodlistener() { public void caretpositionchanged(java.awt.event.inputmethodevent evt) { } public void inputmethodtextchanged(java.awt.event.inputmethodevent evt) { jtable1inputmethodtextchanged(evt); } }); jscrollpane2.setviewportview(jtable1); jtable1.getcolumnmodel().getselectionmodel().setselectionmode(javax.swing.listselectionmodel.single_selection); if (jtable1.getcolumnmodel().getcolumncount() > 0) { jtable1.getcolumnmodel().getcolumn(0).setheadervalue("job no"); jtable1.getcolumnmodel().getcolumn(1).setheadervalue("item"); jtable1.getcolumnmodel().getcolumn(2).setheadervalue("billed amount"); jtable1.getcolumnmodel().getcolumn(3).setheadervalue("parts cost"); jtable1.getcolumnmodel().getcolumn(4).setheadervalue("net profit"); jtable1.getcolumnmodel().getcolumn(5).setheadervalue("percentage"); } netprofit.settext("total profit"); netprofit.addactionlistener(new java.awt.event.actionlistener() { public void actionperformed(java.awt.event.actionevent evt) { netprofitactionperformed(evt); } }); save.settext("save"); save.addactionlistener(new java.awt.event.actionlistener() { public void actionperformed(java.awt.event.actionevent evt) { saveactionperformed(evt); } }); jmenu1.settext("file"); jmenubar1.add(jmenu1); jmenu2.settext("edit"); jmenubar1.add(jmenu2); setjmenubar(jmenubar1); javax.swing.grouplayout layout = new javax.swing.grouplayout(getcontentpane()); getcontentpane().setlayout(layout); layout.sethorizontalgroup( layout.createparallelgroup(javax.swing.grouplayout.alignment.leading) .addcomponent(jscrollpane2) .addgroup(layout.createsequentialgroup() .addgroup(layout.createparallelgroup(javax.swing.grouplayout.alignment.leading) .addgroup(layout.createsequentialgroup() .addgap(114, 114, 114) .addcomponent(jlabel1, javax.swing.grouplayout.preferred_size, 32, javax.swing.grouplayout.preferred_size) .addpreferredgap(javax.swing.layoutstyle.componentplacement.related) .addcomponent(date, javax.swing.grouplayout.preferred_size, 77, javax.swing.grouplayout.preferred_size)) .addgroup(layout.createsequentialgroup() .addgap(350, 350, 350) .addcomponent(netprofit) .addpreferredgap(javax.swing.layoutstyle.componentplacement.related) .addcomponent(netsum, javax.swing.grouplayout.preferred_size, 75, javax.swing.grouplayout.preferred_size)) .addgroup(layout.createsequentialgroup() .addgap(221, 221, 221) .addcomponent(save))) .addcontainergap(147, short.max_value)) ); layout.setverticalgroup( layout.createparallelgroup(javax.swing.grouplayout.alignment.leading) .addgroup(layout.createsequentialgroup() .addgap(20, 20, 20) .addgroup(layout.createparallelgroup(javax.swing.grouplayout.alignment.baseline) .addcomponent(jlabel1, javax.swing.grouplayout.preferred_size, 24, javax.swing.grouplayout.preferred_size) .addcomponent(date, javax.swing.grouplayout.preferred_size, 29, javax.swing.grouplayout.preferred_size)) .addpreferredgap(javax.swing.layoutstyle.componentplacement.related) .addcomponent(jscrollpane2, javax.swing.grouplayout.preferred_size, 350, javax.swing.grouplayout.preferred_size) .addgap(18, 18, 18) .addgroup(layout.createparallelgroup(javax.swing.grouplayout.alignment.baseline) .addcomponent(netprofit) .addcomponent(netsum, javax.swing.grouplayout.preferred_size, javax.swing.grouplayout.default_size, javax.swing.grouplayout.preferred_size)) .addgap(1, 1, 1) .addcomponent(save) .addcontainergap(32, short.max_value)) ); date.getaccessiblecontext().setaccessiblename("date"); save.getaccessiblecontext().setaccessiblename("save"); pack(); }// </editor-fold> private void dateactionperformed(java.awt.event.actionevent evt) { // todo add together handling code here: } private void netprofitactionperformed(java.awt.event.actionevent evt) { // todo add together handling code here: } private void saveactionperformed(java.awt.event.actionevent evt) { // todo add together handling code here: connection conn = null; statement stmt = null; int count=jtable1.getrowcount(); int col=jtable1.getcolumncount(); string jobno[] =new string[count]; // name array , index 4 means no. of row string item[]=new string[count]; string bill[] =new string[count]; // name array , index 4 means no. of row string part[]=new string[count]; string profit[] =new string[count]; // name array , index 4 means no. of row string percent[]=new string[count]; for(int i=0;i<=count;i++) { for(int j=0;j<=col;j++) { no[i]=jtable1.getvalueat(i,j).tostring(); // value 0 row , 0 column it[i]=jtable1.getvalueat(i,j).tostring(); amount[i]=jtable1.getvalueat(i,j).tostring(); p[i]=jtable1.getvalueat(i,j).tostring(); cost[i]=jtable1.getvalueat(i,j).tostring(); s[i]=jtable1.getvalueat(i,j).tostring(); try{ string sql="insert m (no,it,amount,cost,sell,p,date) values('"+no[i]+"','"+it[i]+"','"+amount[i]+"','"+p[i]+"','"+cost[i]+"','"+s[i]+"','"+date.gettext()+"')"; // stmt.execute(sql); stmt.executequery(sql); //stmt.execute(sql); /*preparedstatement ps=conn.preparestatement(sql); ps.setstring(1, ""); ps.setstring(2, ""); ps.setstring(3, ""); ps.setstring(4, ""); ps.setstring(5, ""); ps.setstring(6, ""); ps.setstring(7,date.gettext()); ps.execute();*/ joptionpane.showmessagedialog(null,"saved"); } catch(exception e){ joptionpane.showmessagedialog(null,e); } } } private void jtable1inputmethodtextchanged(java.awt.event.inputmethodevent evt) { // todo add together handling code here: } /** * @param args command line arguments */ public static void main(string args[]) { /* set nimbus , sense */ //<editor-fold defaultstate="collapsed" desc=" , sense setting code (optional) "> /* if nimbus (introduced in java se 6) not available, remain default , feel. * details see http://download.oracle.com/javase/tutorial/uiswing/lookandfeel/plaf.html */ seek { (javax.swing.uimanager.lookandfeelinfo info : javax.swing.uimanager.getinstalledlookandfeels()) { if ("nimbus".equals(info.getname())) { javax.swing.uimanager.setlookandfeel(info.getclassname()); break; } } } grab (classnotfoundexception ex) { java.util.logging.logger.getlogger(bill.class.getname()).log(java.util.logging.level.severe, null, ex); } grab (instantiationexception ex) { java.util.logging.logger.getlogger(bill.class.getname()).log(java.util.logging.level.severe, null, ex); } grab (illegalaccessexception ex) { java.util.logging.logger.getlogger(bill.class.getname()).log(java.util.logging.level.severe, null, ex); } grab (javax.swing.unsupportedlookandfeelexception ex) { java.util.logging.logger.getlogger(bill.class.getname()).log(java.util.logging.level.severe, null, ex); } //</editor-fold> connection conn = null; statement stmt = null; try{ //step 2: register jdbc driver class.forname("com.mysql.jdbc.driver"); //class.forname("jdbc:mysql://localhost:3306/ganpatidb?zerodatetimebehavior=converttonull"); //step 3: open connection system.out.println("connecting database..."); conn = drivermanager.getconnection("jdbc:mysql://localhost:3306/ganpatidb", "root", "root"); //step 4: execute query system.out.println("creating database..."); stmt = conn.createstatement(); }catch(sqlexception se){ //handle errors jdbc // se.printstacktrace(); }catch(exception e){ //handle errors class.forname // e.printstacktrace(); }finally{ //finally block used close resources try{ if(stmt!=null) stmt.close(); }catch(sqlexception se2){ }// nil can try{ if(conn!=null) conn.close(); }catch(sqlexception se){ //se.printstacktrace(); }//end seek }//end seek //system.out.println("goodbye!"); //int r=jtable1.selectedrow; //jtable1.getvalue(3,3); /* create , display form */ java.awt.eventqueue.invokelater(new runnable() { public void run() { new bill().setvisible(true); } }); } // variables declaration - not modify private javax.swing.jtextfield date; private javax.swing.jlabel jlabel1; private javax.swing.jmenu jmenu1; private javax.swing.jmenu jmenu2; private javax.swing.jmenubar jmenubar1; private javax.swing.jscrollpane jscrollpane2; private javax.swing.jtable jtable1; private javax.swing.jbutton netprofit; private javax.swing.jtextfield netsum; private javax.swing.jbutton save; // end of variables declaration } how insert multiple rows info mysql database.... please help....
from looks like, not initializing connection , statement under saveactionperformed method, setting them null.
private void saveactionperformed(java.awt.event.actionevent evt) { // todo add together handling code here: connection conn = null; statement stmt = null; in main method indeed initializing them..
try{ //step 2: register jdbc driver class.forname("com.mysql.jdbc.driver"); //class.forname("jdbc:mysql://localhost:3306/ganpatidb?zerodatetimebehavior=converttonull"); //step 3: open connection system.out.println("connecting database..."); conn = drivermanager.getconnection("jdbc:mysql://localhost:3306/ganpatidb", "root", "root"); //step 4: execute query system.out.println("creating database..."); stmt = conn.createstatement(); }catch(sqlexception se){ //handle errors jdbc se.printstacktrace(); }catch(exception e){ //handle errors class.forname e.printstacktrace(); }finally{ //finally block used close resources try{ if(stmt!=null) stmt.close(); }catch(sqlexception se2){ }// nil can try{ if(conn!=null) conn.close(); }catch(sqlexception se){ //se.printstacktrace(); }//end seek }//end seek you need either remove code set them null or add together same code straight afterwards , remember need set code within seek , grab block.
something this:
try{ //step 2: register jdbc driver class.forname("com.mysql.jdbc.driver"); //class.forname("jdbc:mysql://localhost:3306/ganpatidb?zerodatetimebehavior=converttonull"); //step 3: open connection system.out.println("connecting database..."); conn = drivermanager.getconnection("jdbc:mysql://localhost:3306/ganpatidb", "root", "root"); //step 4: execute query system.out.println("creating database..."); stmt = conn.createstatement(); int count=jtable1.getrowcount(); int col=jtable1.getcolumncount(); string jobno[] =new string[count]; // name array , index 4 means no. of row string item[]=new string[count]; string bill[] =new string[count]; // name array , index 4 means no. of row string part[]=new string[count]; string profit[] =new string[count]; // name array , index 4 means no. of row string percent[]=new string[count]; for(int i=0; i< =count; i++) { for(int j=0; j< =col; j++) { no[i]=jtable1.getvalueat(i,j).tostring(); // value 0 row , 0 column it[i]=jtable1.getvalueat(i,j).tostring(); amount[i]=jtable1.getvalueat(i,j).tostring(); p[i]=jtable1.getvalueat(i,j).tostring(); cost[i]=jtable1.getvalueat(i,j).tostring(); s[i]=jtable1.getvalueat(i,j).tostring(); try{ string sql="insert m (no,it,amount,cost,sell,p,date) values('"+no[i]+"','"+it[i]+"','"+amount[i]+"','"+p[i]+"','"+cost[i]+"','"+s[i]+"','"+date.gettext()+"')"; stmt.execute(sql); joptionpane.showmessagedialog(null,"saved"); } catch(exception e){ joptionpane.showmessagedialog(null,e); } } catch(sqlexception se){ //handle errors jdbc se.printstacktrace(); } catch(exception e){ //handle errors class.forname e.printstacktrace(); } finally{ //finally block used close resources try{ if(stmt!=null) stmt.close(); } catch(sqlexception se2){ } // nil can try{ if(conn!=null) conn.close(); } catch(sqlexception se){ //se.printstacktrace(); } //end seek } //end seek java mysql swing jdbc jtable
No comments:
Post a Comment