	AllExperts > Experts
	Search

Java

Volunteer
Answers to thousands of questions

Home · More Questions · Answer Library

· Encyclopedia ·

Topic: Java

Expert: Artemus Harper
Date: 7/7/2008
Subject: updating JTable(URGENT)

Question
QUESTION: i want to edit the cells in JTable which takes its data from a MS-Access database and those edits should get saved in the database.i defined the setValueAt ,getValueAt IN TABLEMODEL and have made a class implementing TableModelListener and called that in the main class (main class containing the jtable).i am able to change the values in cell but the values are not being saved on clicking of a button "update" .
PLEASE HELP ME AS SOON AS POSSIBLE.

ANSWER: In your Connection object (assuming you are using the java.sql package) you would first turn off auto commit in the Connection interface, e.g. con.setAutoCommit(false). Then you any changes made should be done though the updateXXX method in ResultSet. When you want to save the changes you can call commit() in the Connection interface, or if you want to revert you can call rollback().

There is no reason to have a table listener if you are already implementing the setValueAt method.

---------- FOLLOW-UP ----------

QUESTION: thanks so much i will try to implement it bt cn u provide a sample code for it please its really urgent i ve been stuck at it since a month...ill be highly thankful to you..

ANSWER: Not having too much skill with databases, I've made up a simple example of what you want... you can see the code here:
http://rafb.net/p/8lL21z90.html

There are two classes there, a main, which I used to create the table and connect to the database, you will need to modify this to your database specifications (do make sure you turn auto commit off though). Note that no updates are made to the database until the button is pressed.

The second is a model for the JTable that handles the display and update. This is pretty simple, and you may not like how some objects like dates are handled.

---------- FOLLOW-UP ----------

QUESTION: this is my code one is the jdbctablemodel
and the other is trhe main one in which i m making my gui and displaying the jtable.
whenever i change the values in the cells it throws exception "SQL syntax error and COUNT error"

import java.util.*;
import java.sql.*;
import javax.swing.table.*;
import javax.swing.event.*;
import java.util.Vector;
import javax.swing.table.AbstractTableModel;
import javax.swing.event.TableModelEvent;

public class JDBCTableModel extends AbstractTableModel
{
   //JDBC objects
   Connection connection;
   Statement statement;
   PreparedStatement delStatement;
   ResultSet resultSet;
   ResultSetMetaData metaData;

   static String queryStr = "select * from Table2";


   // Table objects
   Vector names;
   Vector types;
   Vector data;

public JDBCTableModel(String url, String driverName,
String user, String passwd)
{
   data = new Vector();
   names = new Vector();
   url="jdbc:odbc:swati";
   user="";
   passwd="";
   driverName="sun.jdbc.odbc.JdbcOdbcDriver";
   try
   {
       //load the driver
       Class.forName(driverName);
       connection = DriverManager.getConnection(url, user, passwd);

       statement = connection.createStatement();

       // Create a compiled statement for executing deletes.
       delStatement =connection.prepareStatement("delete from Table2 where CUSTID=?");

       // Initialize the table.
       this.executeQuery();
   }
   catch (Exception exp)
   {
       System.out.println("Error connecting: "+exp);
   }
}

public void executeQuery()
{
   int i,max;
   Vector rowData;
   int curType;
   try
   {
       data = new Vector();
       names = new Vector();
       resultSet = statement.executeQuery(queryStr);
       metaData = resultSet.getMetaData();
       max = metaData.getColumnCount();
       //get the column names
       for(i=0;i<max;i++)
       {
           //adjust for meta data index start at 1
           names.addElement(metaData.getColumnLabel(i+1));
       }
       //load the data
       while (resultSet.next())
       {
           rowData = new Vector();
           for (i=0;i<max;i++)
           {
               rowData.addElement(resultSet.getObject(i+1));
           }
           data.addElement(rowData);
       }
       fireTableChanged(null);
   }
   catch (Exception exp)
   {
       System.out.println("Error performing query: "+exp);
       exp.printStackTrace();
   }
}

public void executeDelete(int row)
{
   if(row > -1)
   {
       // Get the product ID.
       Object id = this.getValueAt(row,0);
       if(statement != null)
       {
           try
           {
           /*
             Set the value of the first paramter in
             this compiled delete statement.
           */
               delStatement.setObject(1,id);
               // Execute the statement.
               delStatement.executeUpdate();
               //Refresh. In JDBC 2.0, there is a refresh method.
               this.executeQuery();
           }catch(SQLException e)
           {
               System.out.println(e);
               return;
           }
       }
   }
}

public void setValueAt(Object value, int row, int col)
               {


   Vector rowData = (Vector)data.elementAt(row);
  Object o = dataRow.elementAt(col);

   if(o! = value)
   {

       try
       {
           String s = "UPDATE Table2 SET " +
                      metaData.getColumnName(col+1) + "=";
           int curType = metaData.getColumnType(col+1);

           switch(curType)
           {
               case Types.CHAR:
               case Types.VARCHAR:
               case Types.LONGVARCHAR:
               case Types.DATE:
                   s += "‘"+value.toString()+"’";
                   break;
               case Types.TINYINT:
               case Types.SMALLINT:
               case Types.INTEGER:
               case Types.BIGINT:
               case Types.FLOAT:
               case Types.DOUBLE:
                   s += value.toString();
               break;
                   default:
                   s += "‘"+value.toString()+"’";
           }

           s += " WHERE CUSTID = " + dataRow.elementAt(0)+"";
           statement.executeUpdate(s);
           // refresh
           this.executeQuery();
connection.commit();

       } catch(SQLException e) { System.out.println(e);}
   }
}

public boolean commitTransaction()
{
   boolean commitIsOk = false;

   try
   {
   /*
       Check if autocommit is on.
       If it is, don’t commit manually.
   */
       commitIsOk = !connection.getAutoCommit();

       if(commitIsOk)
       {
           // Commit transaction
           connection.commit();

           // Turn on AutoCommit; this is default.
           connection.setAutoCommit(false);
       }
   }catch(SQLException e)
   {
       System.out.println(e);
       commitIsOk = false;
   }

   //refresh
   if(commitIsOk)
       this.executeQuery();
   return commitIsOk;
}

public void close() throws SQLException
{
   System.out.println("Closing db connection");
   resultSet.close();
   statement.close();
   connection.close();
}

protected void finalize() throws Throwable
{
   close();
   super.finalize();
}

public String getColumnName(int col)
{
   String retVal;

   retVal = (String) names.elementAt(col);

   if(retVal == null)
       retVal = "";

   return retVal;
}

public boolean isCellEditable(int row, int col)
{ return true; }


/*public void setValueAt(Object value, int row, int col)
{
   Vector dataRow = (Vector) data.elementAt(row);
   dataRow.setElementAt(value, col);
   fireTableCellUpdated(row, col);
   }*/

public int getColumnCount()

{
    return names.size();
}
public int getRowCount()
{
   return data.size();
}

public Object getValueAt(int row, int col)

{
    Vector rowData = (Vector)data.elementAt(row);

    return rowData.elementAt(col);
}

}

and this is the main class in which i m creating that jtable
here u'll see one button "btn"
this is my SECOND QUESTION :
i have one column custid in my table "Table2"
and i ve a label CUSTID and textfield(corrsponding to it) in my gui .so i want that whenever i type a custid in that textfield only those rows having that id should get displayed in jtable.. i ve tried to do that with set value method but execptions are coming.. what to do i m lost

please help
import java.sql.*;
import javax.swing.JPanel.*;
import javax.swing.*;
import javax.swing.border.*;
import java.awt.*;
import java.awt.event.*;
import java.awt.event.WindowAdapter;
import java.awt.event.WindowEvent;
import java.awt.Dimension;
import java.awt.*;
import java.io.*;
import java.util.*;
import javax.swing.table.TableModel;

/*<applet code="project2" width=200 height=200>
</applet> */
public class project2 extends JFrame implements ActionListener
{
private JButton btnsave,btnexit,btnaddnew,btnprice,btnquery,btnok,btndel,btn,btnupdate;
private JTextField tf1,tf2,tf3,tf4,tf6;
private JLabel lblname,lblid,lblmsg,lblp,lbldate,lblitemid;
int r=0,s;
JTextArea txa=new JTextArea(10,20);
//static String deleteStr ="Delete Current Row";

private JPanel p1,p2,p3,psouth;
JDBCTableModel model;
JTable tableView;

public project2()
{
   //initializing buttons
   btnsave = new JButton("Save");
   btnsave.addActionListener(this);
   btnexit = new JButton("Exit");
   btnexit.addActionListener(this);
   btnaddnew = new JButton("AddNew");
   btnaddnew.addActionListener(this);
   btnprice=new JButton("Price");
   btnprice.addActionListener(this);
   btndel=new JButton("Delete");
   btndel.addActionListener(this);
   btn=new JButton("btn");
   btn.addActionListener(this);
   btnupdate=new JButton("update");
   btnupdate.addActionListener(this);

   //btnbill= new JButton("Total");
   //btnbill.addActionListener(this);

   btnquery=new JButton("Query");
   btnquery.addActionListener(this);
   btnok=new JButton("Ok");
   btnok.addActionListener(this);



   //initializing textfields
   tf1 = new JTextField(12);
   tf2 = new JTextField(12);
   tf3 = new JTextField(12);
   tf4 = new JTextField(12);
   tf6 = new JTextField(12);


   lblname = new JLabel("Customer Name");
   lblid = new JLabel("Cust Id");
   lbldate=new JLabel("Date");
   lblitemid=new JLabel("ItemId");
   lblp=new JLabel("Price");
   lblmsg = new JLabel("",JLabel.CENTER);

   //initializing panels
   p1 = new JPanel();
   p2 = new JPanel();
   p3 = new JPanel();
   psouth = new JPanel();




   p1.setLayout(new FlowLayout());

   p1.add(btnprice);
   p1.add(btnsave);
   p1.add(btnok);
   p1.add(btnaddnew);
   p1.add(btnquery);
   p1.add(btnexit);
   p1.add(btndel);
   p1.add(btn);
   p1.add(btnupdate);
   //adding lblmsg to panel p3
   p3.add(lblmsg);




       this.addWindowListener(new WindowAdapter() {
       public void windowClosing(WindowEvent e) {System.exit(0);}});

      model = new JDBCTableModel("jdbc:odbc:swati","sun.jdbc.odbc.JdbcOdbcDriver","","");


       // Create the table
       tableView = new JTable();
       tableView.setModel(model);
       tableView.createDefaultColumnsFromModel();
      //ableView.setRowSelectionAllowed(true);


       JScrollPane scrollpane = new JScrollPane(tableView);
       scrollpane.setPreferredSize(new Dimension(700, 200));



   //adding label and textfields to panel p2
   p2.setLayout(new GridLayout(5,1));
   //setting line and titled border for panel p2
   p2.setBorder(BorderFactory.createLineBorder(Color.red));
   p2.setBorder(BorderFactory.createTitledBorder("*********************** SHOWROOM ************************"));
   p2.add(lblname);
   p2.add(tf1);
   p2.add(lblid);
   p2.add(tf2);
   p2.add(lblitemid);
   p2.add(tf6);
   p2.add(lbldate);
   p2.add(tf3);
   p2.add(lblp);
   p2.add(tf4);
   p3.add(scrollpane);

   psouth.setLayout(new GridLayout(2,1));
   psouth.add(p3);
   psouth.add(p1);


   this.getContentPane().add(p2,"Center");
   this.getContentPane().add(psouth,"South");

   this.setSize(700,200);
   this.setLocation(400,200);
   //this.getContentPane().add(scrollpane);
   this.pack();
       //frame.setVisible(true);

   this.show();
   }

    public static void main(String args[])
  {
    project2 pr = new project2();
    pr.setVisible(true);
   }





   public void actionPerformed(ActionEvent e)

   {


   if(e.getSource()==btndel)
   {
   model.executeDelete(tableView.getSelectedRow());
   }

    if(e.getSource()==btnaddnew)
   {
     tf1.setText("");
     tf2.setText("");
     tf3.setText("");
     tf4.setText("");
     tf6.setText("");
     }

      if(e.getSource()==btnsave)
  {

     PreparedStatement pstm;
     ResultSet rs;
     String sql;

    try
    {
     Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

       //connection object created using DriverManager class
       //swati is the name of the database
       Connection connect =DriverManager.getConnection("jdbc:odbc:swati");

       //creating prepared statement object pstm so that query can be
     //sent to database
     pstm=connect.prepareStatement("insert into Table2 values(?,?,?,?)");
       pstm.setString(1,tf2.getText());
       pstm.setString(2,tf6.getText());
       pstm.setString(3,tf3.getText());
       pstm.setString(4,tf4.getText());
       //execute method to execute the query
       pstm.executeUpdate();
       lblmsg.setText("Details have been added to database");

        //closing the prepared statement and connection object
        pstm.close();
        connect.close();
      }
      catch(SQLException sqe)
      {
        System.out.println("SQl error");
       }
       catch(ClassNotFoundException cnf)
       {
        System.out.println("Class not found error");
       }
      }
      if(e.getSource()==btnexit)
      {
         System.exit(0);
      }

      if(e.getSource()==btnprice)
{
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection c=DriverManager.getConnection("jdbc:odbc:swati");
PreparedStatement pt=c.prepareStatement("select * from item where ITEMID = ?");
String s1=tf6.getText().trim();
pt.setString(1,(s1));
ResultSet rs=pt.executeQuery();
while(rs.next())
{
tf4.setText(rs.getString(2).trim());
}
rs.close();
pt.close();
c.close();
      }
catch(SQLException sqe)
      {
        System.out.println("SQl error");
       }
       catch(ClassNotFoundException cnf)
       {
        System.out.println("Class not found error");
       }
}

if(e.getSource()==btnok)
{
  try{
     Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection c=DriverManager.getConnection("jdbc:odbc:swati");
PreparedStatement pstm;
ResultSet rs;
  pstm=c.prepareStatement("insert into Table1 values(?,?)");
       pstm.setString(1,tf2.getText());
       pstm.setString(2,tf1.getText());
        pstm.executeUpdate();
   c.close();
   pstm.close();
  }
   catch(SQLException sqe)
      {
        System.out.println("SQl error");
       }
       catch(ClassNotFoundException cnf)
       {
        System.out.println("Class not found error");
       }
}
if(e.getSource()==btn)
{
int row=0;
try
{

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection c=DriverManager.getConnection("jdbc:odbc:swati");
PreparedStatement pt=c.prepareStatement("select * from Table2 where CUSTID = ?");
String s1;
s1= tf2.getText().trim();
pt.setString(1,(s1));
ResultSet resultSet=pt.executeQuery();
ResultSetMetaData md=resultSet.getmetaData();


  model = new JDBCTableModel("jdbc:odbc:swati","sun.jdbc.odbc.JdbcOdbcDriver","","");
   tableView.setModel(model);
  while(resultSet.next())

     tableView.setValueAt(resultSet.getString(1),row,0);
     tableView.setValueAt(resultSet.getString(2),row,1);
     tableView.setValueAt(resultSet.getString(3),row,2);
     tableView.setValueAt(resultSet.getString(4),row,3);

     }
     catch(SQLException sqe)
      {
        System.out.println("SQl error");
       }
       catch(ClassNotFoundException cnf)
       {
        System.out.println("Class not found error");
       }
}

      if(e.getSource()==btnquery)
        {



    model = new JDBCTableModel("jdbc:odbc:swati","sun.jdbc.odbc.JdbcOdbcDriver","","");

    tableView.setModel(model);
    tableView.createDefaultColumnsFromModel();
    //bleView.setRowSelectionAllowed(true);
        }

        /*if (e.getSource()==btnupdate)
        {

           //tableView.getModel().addTableModelListener(new SimpleDemo());

        }*/



}
}

Answer
For your first question, I can't really help with that since I don't have your database (or even Access). You should print out the query that you are executing before executing and manually ensure that it is valid.

As for the second question, you could keep a map of the rows you are showing in the table along with the index they have in the query in order to make the correct update. Or you could just create a new query to update the item based on the key of the row (e.g. the custid), remember as long as autocommit is turrned off, you can update when ever a change is made, and only call commit when the user presses the update button.

Also... please do not concatenate strings when forming queries. You never know when a ' might appear in an entry and screw up things. Use the prepared query with the ?. You currently do this in the setValueAt method.

Add to this Answer Ask a Question

About Us | Advertise on This Site | User Agreement | Privacy Policy | Help
Copyright © 2008 About, Inc. About and About.com are registered trademarks of About, Inc. The About logo is a trademark of About, Inc. All rights reserved.