Latest Tutorials| Questions and Answers|Ask Questions?|Site Map



Home Answers Viewqa Swing-AWT How to insert and update all column values of database from jtable.
Login         

View Questions and Answers by Category

Advertisements


 
Have Programming Question? Ask it here!
 
 
 


vikas pandey
How to insert and update all column values of database from jtable.
0 Answer(s)      2 years and 2 months ago
Posted in : Swing AWT


Hello Sir, I have developed a swing application in which database table is shown in the jtable.. of my jframe window.Now as per my requirement i have to add ,update,delete database values from jtable only so i added three buttons add,update,delete .. mydatabase contains five columns id,name,address,contact,email

Now, delete button is working properly. but add button is not working properly .

when we add new records into the database then last last record of particular row is not getting added into the database whereas all other records are getting added successfully..everytime...

and update button is not working atall..

Sir, I am posting my codes ... Plz have look and give me the solution of my problem...Thank you Sir.

print("code sample");
import javax.swing.*;
import java.awt.event.*;
import java.awt.*;
import javax.swing.event.*;
import javax.swing.table.*;

public class MyTableApp extends JFrame{

JTable myTable;
JButton update;
JButton insert;
JButton delete;
JPanel p;
MyTableModel tm;
JScrollPane myPane;

   MyTableApp(){
      try{
         UIManager.setLookAndFeel("Interactive Jtable In Java Swing");
      }
      catch(Exception e){
         System.out.println("Error on look and feel");
      } 
      update = new JButton("Update");
      insert = new JButton("Add");
      delete = new JButton("Delete");
      p = new JPanel();
      tm = new MyTableModel();


      myTable = new JTable(tm);

      myPane = new JScrollPane(myTable,
                           JScrollPane.VERTICAL_SCROLLBAR_AS_NEEDED,
                           JScrollPane.HORIZONTAL_SCROLLBAR_AS_NEEDED);
      myTable.setSelectionForeground(Color.white);
      myTable.setSelectionBackground(Color.red);
      myTable.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);
      p.add(myPane);
      p.add(update);
      p.add(insert);
      p.add(delete);
      update.addActionListener(new ActionListener(){
         public void actionPerformed(ActionEvent e){
             tm.updateDB();
         }
      });
      insert.addActionListener(new ActionListener(){
         public void actionPerformed(ActionEvent e){
            tm.addRow();
            myTable.setEditingRow(tm.getRowCount());
            myTable.setRowSelectionInterval(tm.getRowCount()-1,tm.getRowCount()-1);
         }
      });
      delete.addActionListener(new ActionListener(){
         public void actionPerformed(ActionEvent e){
            int rowToDelete = myTable.getSelectedRow();
            tm.deleteRow(rowToDelete);
            myTable.setEditingRow(rowToDelete -1);
            myTable.setRowSelectionInterval(rowToDelete -1,rowToDelete -1);
         }
      });
      this.addWindowListener(new WindowAdapter(){
         public void windowClosing(WindowEvent e){
            System.exit(0);
         }
      }); // end windowlistener
      this.setContentPane(p);
      this.setVisible(true);

      this.pack();
   } // constructor

   public static void main (String args[]){
       new MyTableApp();
   } // main
} //class



print("code sample");
// new class. This is the table model
import javax.swing.table.*;
import java.sql.*;
import java.util.Vector;

public class MyTableModel extends AbstractTableModel {
Connection con;
Statement stat;
ResultSet rs;
int li_cols = 0;
Vector allRows;
Vector row;
Vector newRow;
Vector colNames;
String dbColNames[];
String pkValues[];
String tableName;
ResultSetMetaData myM;
String pKeyCol;
Vector deletedKeys;
Vector newRows;
boolean ibRowNew = false;
boolean ibRowInserted = false;

   MyTableModel(){
      try{
        Class.forName("com.mysql.jdbc.Driver");
      }
      catch (ClassNotFoundException e){
            System.out.println("Cannot Load Driver!");
      }
      try{
         String url = "jdbc:mysql://localhost:3306/techsoft";
          String user = "root";
          String pass = "techsoft";
         con = DriverManager.getConnection(url,user,pass);
         stat = con.createStatement();
         rs = stat.executeQuery("SELECT * from pandey");
         deletedKeys = new Vector();
         newRows = new Vector();
         myM = rs.getMetaData();
         tableName = myM.getTableName(1);
         li_cols = myM.getColumnCount();
         dbColNames = new String[li_cols];
         for(int col = 0; col < li_cols; col ++){
            dbColNames[col] = myM.getColumnName(col + 1);
         }
         allRows = new Vector();
         while(rs.next()){
            newRow = new Vector();
            for(int i = 1; i <= li_cols; i++){
               newRow.addElement(rs.getObject(i));
            } // for
            allRows.addElement(newRow);
         } // while
      } 
      catch(SQLException e){
         System.out.println(e.getMessage());
      } 
   }
   public Class getColumnClass(int col){
      return getValueAt(0,col).getClass();
   }
   public boolean isCellEditable(int row, int col){
      if (ibRowNew){
         return true;
      }
      if (col == 0){
         return  false;
      } else {
         return true;
      }
   }
   public String getColumnName(int col){
      return dbColNames[col];
   }
   public int getRowCount(){
      return allRows.size();
   } 
   public int getColumnCount(){
      return li_cols;
   }
   public Object getValueAt(int arow, int col){
      row = (Vector) allRows.elementAt(arow);
      return row.elementAt(col);
   }
   public void setValueAt(Object aValue, int aRow, int aCol) {
      Vector dataRow = (Vector) allRows.elementAt(aRow);
      dataRow.setElementAt(aValue, aCol);
      fireTableCellUpdated(aRow, aCol);
   }
   public void updateDB(){
 String updateLine[] = new String[dbColNames.length];
      try{
         DatabaseMetaData dbData = con.getMetaData();
         String catalog;
         // Get the name of all of the columns for this table
         String curCol;
         colNames = new Vector();
         ResultSet rset1 = dbData.getColumns(null,null,tableName,null);
         while (rset1.next()) {

            curCol = rset1.getString(4);
            System.out.println(curCol);
            colNames.addElement(curCol);
           // System.out.println(colNames);
         }
         rset1.close();
         pKeyCol = colNames.firstElement().toString();

         // Go through the rows and perform INSERTS/UPDATES/DELETES
         int totalrows;
         totalrows = allRows.size();
         String dbValues[];
         Vector currentRow = new Vector();
         pkValues = new String[allRows.size()];

         // Get column names and values
         for(int i=0;i < totalrows;i++){
            currentRow = (Vector) allRows.elementAt(i);
            int numElements = currentRow.size();
            dbValues = new String[numElements];
            for(int x = 0; x < numElements; x++){
               String classType = currentRow.elementAt(x).getClass().toString();
               int pos = classType.indexOf("String");
               if(pos > 0){ // we have a String

                  dbValues[x] = "'" + currentRow.elementAt(x) + "'";
                  updateLine[x] = dbColNames[x] + " = " + "'" + currentRow.elementAt(x) + "',";
                  if (dbColNames[x].toUpperCase().equals(pKeyCol.toUpperCase())){
                    pkValues[i] = currentRow.elementAt(x).toString() ;

                  }
               }
               pos = classType.indexOf("Integer");
               if(pos > 0){ // we have an Integer
                  dbValues[x] = currentRow.elementAt(x).toString();
                  if (dbColNames[x].toUpperCase().equals(pKeyCol.toUpperCase())){
                     pkValues[i] = currentRow.elementAt(x).toString();
                  }
                  else{
                     updateLine[x] = dbColNames[x] + " = " + currentRow.elementAt(x).toString() + ",";
                  }
               }


               pos = classType.indexOf("Boolean");
               if(pos > 0){ // we have a Boolean
                  dbValues[x] = currentRow.elementAt(x).toString();
                  updateLine[x] = dbColNames[x] + " = " + currentRow.elementAt(x).toString() + ",";
                  if (dbColNames[x].toUpperCase().equals(pKeyCol.toUpperCase())){
                     pkValues[i] = currentRow.elementAt(x).toString() ;
                  }
               }

            }


          // For Loop

            // If we are here, we have read one entire row of data. Do an UPDATE or an INSERT
            int numNewRows = newRows.size();
            int insertRow = 0;
            boolean newRowFound;

            for (int z = 0;z < numNewRows;z++){
               insertRow = ((Integer) newRows.get(z)).intValue();
               if(insertRow == i+1){
                  StringBuffer InsertSQL = new StringBuffer();
                  InsertSQL.append("INSERT INTO " + tableName + " ("); 
                  for(int zz=0;zz<=dbColNames.length-1;zz++){
                     if (dbColNames[zz] != null){
                        InsertSQL.append(dbColNames[zz] + ",");
                     }
                  }
                  // Strip out last comma
                   InsertSQL.replace(InsertSQL.length()-1,InsertSQL.length(),")");
                   InsertSQL.append(" VALUES(" + pkValues[i] + ",");
                   //System.out.println(   InsertSQL.append(" VALUES(" + pkValues[i] + ","));
                  for(int c=1;c<dbValues.length;c++){
                     InsertSQL.append(dbValues[c]+",");
                    // System.out.println(InsertSQL.append(dbValues[c] + ","));
                  } 

                  InsertSQL.replace(InsertSQL.length()-1,InsertSQL.length(),")"); 
                 // System.out.println(InsertSQL.replace(InsertSQL.length()-1,InsertSQL.length(),")"));
                  stat.executeUpdate(InsertSQL.toString());
                  ibRowInserted=true;
                 // System.out.println(InsertSQL);

               }
            } // End of INSERT Logic

            // If row has not been INSERTED perform an UPDATE
            if(ibRowInserted == false){
               StringBuffer updateSQL = new StringBuffer();
               updateSQL.append("UPDATE " + tableName + " SET ");
               for(int z=0;z<=updateLine.length-1;z++){
                  if (updateLine[z] != null){
                     updateSQL.append(updateLine[z]);

                  }
               }
               // Replace the last ',' in the SQL statement with a blank. Then add WHERE clause
               updateSQL.replace(updateSQL.length()-1,updateSQL.length(),"");
               updateSQL.append(" WHERE " + pKeyCol + " = " + pkValues[i] );
               stat.executeUpdate(updateSQL.toString());
               System.out.println(updateSQL);
               } //for
            }
         }
         catch(Exception ex){
            System.out.println("SQL Error! Cannot perform SQL UPDATE " + ex.getMessage());
         }
         // Delete records from the DB
         try{
            int numDeletes = deletedKeys.size();
            String deleteSQL;
            for(int i = 0; i < numDeletes;i++){
               deleteSQL = "DELETE FROM " + tableName + " WHERE " + pKeyCol + " = " +
                                            ((Integer) deletedKeys.get(i)).toString();
            System.out.println(deleteSQL);
               stat.executeUpdate(deleteSQL);
            }
            // Assume deletes where successful. Recreate Vector holding PK Keys
            deletedKeys = new Vector();
         }
         catch(Exception ex){
            System.out.println(ex.getMessage());
         }
      }
      public void deleteRow(int rowToDelete){
         // Mark row for a SQL DELETE from the Database
         Vector deletedRow = (Vector) allRows.get(rowToDelete);
         Integer pkKey = (Integer) deletedRow.get(0);
         deletedKeys.add(pkKey);
         allRows.remove(rowToDelete);
         fireTableRowsDeleted(rowToDelete,rowToDelete);
      }
      public void addRow(){
         // Mark the row for a SQL INSERT in the Database
         newRows.add(new Integer(allRows.size() +1));
         // Get the total number of rows in the Vector
         int rowNumber = allRows.size();
         int pos;

         // Get what a row looks like
         int numElements = newRow.size();
         Vector newRowVect = new Vector();
         for(int i = 0; i < numElements; i++){
            String classType = newRow.elementAt(i).getClass().toString();
            pos = classType.indexOf("String");
            if(pos > 0){ // we have a String
               String blankString = new String();
               newRowVect.addElement(blankString);
            }
            pos = classType.indexOf("Integer");
            if(pos > 0){ // we have an Integer
               Integer blankInt = new Integer("0");
               newRowVect.addElement(blankInt);
            }

            pos = classType.indexOf("Boolean");
            if(pos > 0){ // we have a Boolean
               Boolean blankBool = new Boolean(false);
               newRowVect.addElement(blankBool);
            }

         }
         allRows.addElement(newRowVect);
         ibRowNew = true;
         this.isCellEditable(allRows.size(),0);
         System.out.println(allRows.size());
         fireTableRowsInserted(rowNumber,rowNumber);
      }
   }

Advertisement
View Answers

Related Tutorials/Questions & Answers:
How to insert and update all column values of database from jtable.
How to insert and update all column values of database from jtable.  ... ,update,delete database values from jtable only so i added three buttons add... of particular row is not getting added into the database whereas all other records
How to insert and update all column values of database from jtable.
How to insert and update all column values of database from jtable.  ... ,update,delete database values from jtable only so i added three buttons add,update... row is not getting added into the database whereas all other records are getting
Advertisements
How to insert and update all column values of database from jtable.
How to insert and update all column values of database from jtable.  ... ,update,delete database values from jtable only so i added three buttons add,update... row is not getting added into the database whereas all other records are getting
How to update,Delete database values from jtable cells ..
How to update,Delete database values from jtable cells ..  Hello Sir... from database to jtable .Now as per my requirement i need to update and delete the database records from the table cells by entering new values there only
How to update,Delete database values from jtable cells ..
How to update,Delete database values from jtable cells ..  Hello Sir, I am working on a project in which i have to fetch the values from database to jtable .Now as per my requirement i need to update and delete the database
How to update,Delete database values from jtable cells ..
How to update,Delete database values from jtable cells ..  hello Sir... from database into jtable of a jpanel.. Now Sir, According to my need i have to update the cell values from there only means that whatever values i ma entering
sum of all values in a column of jtable
sum of all values in a column of jtable  hey everyone, is there a code to display the sum of all values in a column of a jtable namely CARTtbl... or deleted.   Here is an example of jtable that sums up the values of two
How to update table column from the values of Arraylist in java
How to update table column from the values of Arraylist in java  Hii Sir, I have an arraylist containing these values [2, 1, 1, 1, 1, 1, 1... column of database table. plz give me the code of this problem.Thank you Sir
How to extract values from SOAP Response message and insert in database
How to extract values from SOAP Response message and insert in database ... values from SOAP Response XML (i.e. Empname,EmpID,Phnumber) but I don't have any idea of XML.Also I need to update these parsed values into the database. Can you
how to make JTable to add delete and update sql database table
how to make JTable to add delete and update sql database table  Hello all I want to know how to make JTable to act actively to add delete and update database table. i am struck ed here from long time please help me
how update JTable after adding a row into database
in JTable, and it's OK, but after adding a row into database table does't update. How update JTable after adding a row into database? package djile pak.java...how update JTable after adding a row into database  J have two
insert values from excel file into database
the following link: Insert values from excel file to database...insert values from excel file into database   hi i want to insert values from Excel file into database.Whatever field and contents are there in excel
how to insert values from jsp into ms access
how to insert values from jsp into ms access   how to insert values using jsp into ms access database
How to delete and update from Jtable cell in swing app
How to delete and update from Jtable cell in swing app  Hii Sir, I am developing a swing app for adding,updating and deleting from jtable... is getting removed from the jtable but selected row is getting deleted from
How to insert rows in jTable?
How to insert rows in jTable?  Hi, I need to take input from user using JTable. I want an empty row to appear after clicking a insert button... not figure out how to. I used DefaultTableModel but wasnt able to insert a row
simple web appllication to insert, update or display from database - JSP-Servlet
in which we can insert, update or delete data from database. i can also display...simple web appllication to insert, update or display from database  hello sir i am using netbeans 5.5 ide with tomcat and oracle 9 i as database
Removing a Column from a JTable
you in how to remove a column from a JTable. For this, have or make  a table... Removing a Column from a JTable     ... from a JTable component that uses the table model. Removing a column from a JTable
inserting all the values in a html table column
inserting all the values in a html table column  strong text hai everyone, i'm a fresher to servlet i need to know that, how can i insert all the values in a html table column into a database. Thanks in advance
How to display all the Select values from the MySQL database table in where condition= In JSP?
How to display all the Select values from the MySQL database table in where... to display all the select values from MySQL DB** only first value is displayed in the jsp file. @select * from table dept where dept_no=10;" jsp code i have used
Inserting a Column in JTable
to insert a column in JTable at a specified location. As, you have learnt in previous...: In this program we will  insert a column in JTable at a particular position. First... Inserting a Column in JTable     
How to insert dynamic textbox values into database using Java?
How to insert dynamic textbox values into database using Java?  Hi I am trying to insert dynamic textbox values to database, my jsp form have 2... these dynamic textbox values to database(Oracle 11g)...Please help me out. I have
How to insert data from a combobox and textbox values into DB using JSP?
How to insert data from a combobox and textbox values into DB using JSP?  hi, How to insert a comb-box and a text box values in to DB using JSP? @DB:student; @table:stu_info; Combobox values:(class1,class2,class3); textbox1
How to insert multiple drop down list data in single column in sql database using servlet
How to insert multiple drop down list data in single column in sql database using servlet  i want to insert date of birth of user by using separate drop down list box for year,month and day into dateofbirth column in sql server
Insert database values in the file
Insert database values in the file In this section, you will learn how to retrieve the data from the database and insert into the file. Description of code: This is a simple task. First of all we have retrieved the values from
Update Database from jsp
Update Database from jsp   I want to update my oracle database column from a text box ,so whenever I input some text value in the text box and click UPDATE button the database field should be updated . I have a drop down menu
How to insert rows from Excel spreadsheet into database by browsing the excel file?
How to insert rows from Excel spreadsheet into database by browsing the excel file?  I want to insert rows from excel sheet to database.for this i... excel file and insert rows into MSSQL database in JSP???   Have a look
how to get the values to dropdownlist from oracle database
how to get the values to dropdownlist from oracle database   </script> </head> <body> <select name... * from countryname"); while(rs.next()){ %> <option value="<
how to get values for same column name from two different tables in SQL
how to get values for same column name from two different tables in SQL  how to get values for same column name from two different tables in SQL???? column name is emp_id loacated in these two tables company,employee
How to insert image in sql database from user using servlet
How to insert image in sql database from user using servlet  pls tell me accept image from user and insert image in sql server 2005 database using servlet and jsp
Show multiple identical rows into JTable from database
Show multiple identical rows into JTable from database In this tutorial, you will learn how to display the multiple rows from database to JTable. Here... rows from database on clicking search button to jtable. The given code accepts
how to import values from database to the drop down box
how to import values from database to the drop down box   hi iam final year student in my project i will insert all employee details in search employee page i kept a drop down box for employee Ids i want all employee IDs
Advertisements
 

 

 

DMCA.com