Deleting from a JTable physically

Hi

I have written an addressbook - program that stores its records in a mysql-db.

It displays its resultSets in JTable in a Swing GUI.

I click on a record-row in the JTable press a delete Button in the Swing GUI and the record is removed from the db.

But not the JTable, to do that I have to restart the program for.

Question:

Is there any way to add a feature so then I press the delete-button, the record is also deleted from JTable physically ? So I don't have to restart the program to see the change !

the columns in my table are (emailadr, firstname, lastname, alias).

Then I click on a colum in a record in the JTable to read into the sql-statement no matter if I click upon emailadr, firstname, lastname or alias.

only emailadr is read into the sql - statement !

Why is what ?

Here is my table-model:
private void displayResultSet( ResultSet rs )
    throws SQLException
{
    // position to first record
    boolean moreRecords = rs.next();
   
    // If there are no records, display a message
    if ( ! moreRecords ) {
      JOptionPane.showMessageDialog( this,
                               "ResultSet contained no records" );
      return;
    }
 
    Vector columnHeads = new Vector();
    Vector rows = new Vector();

    try {
      // get column heads
      ResultSetMetaData rsmd = rs.getMetaData();
      
      for ( int i = 1; i <= rsmd.getColumnCount(); ++i )
          columnHeads.addElement( rsmd.getColumnName( i ) );

      // get row data
      do {
          rows.addElement( getNextRow( rs, rsmd ) );
      } while ( rs.next() );
   

      // display table with ResultSet contents
      table = new JTable( rows, columnHeads );
       table.addMouseListener(new MouseAdapter(){
             public void mouseClicked(MouseEvent e)
             {
                     int selectedColumn = table.getSelectedColumn();
                 int selectedRow = table.getSelectedRow();
                   }
           });
      getContentPane().remove(scroller);
      scroller = new JScrollPane( table );
      getContentPane().add(
                       scroller, BorderLayout.CENTER );
      validate();
      getContentPane().validate();
    }
    catch ( SQLException sqlex ) {
      sqlex.printStackTrace();
    }
}

private Vector getNextRow( ResultSet rs,
                     ResultSetMetaData rsmd )
    throws SQLException
{
    Vector currentRow = new Vector();

    for ( int i = 1; i <= rsmd.getColumnCount(); ++i )
      switch( rsmd.getColumnType( i ) ) {
      case Types.VARCHAR:
          currentRow.addElement( rs.getString( i ) );
          break;
      case Types.INTEGER:
          currentRow.addElement(
                          new Long( rs.getLong( i ) ) );
          break;
      default:
          System.out.println( "Type was: " +
                        rsmd.getColumnTypeName( i ) );
      }
   
    return currentRow;
}

String getColumnAsString(int row, int col) {
     TableModel model = table.getModel();
     return model.getValueAt(row, col).toString();
}

String getColumnhead(int col) {
     TableModel model = table.getModel();
    return model.getColumnName(col);
}

Here is my source for sql-delete:

Hope that there is somebody outthere who can help me answer my questions !!

Sincerely Yours
Frank
Frank-22Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mick BarryJava DeveloperCommented:
You need to remove the row from your table model.

I'd suggest using a DefaultTableModel as the model for the table, it provides method to remove rows.
0
gnoonCommented:
You need to change your code and your imagination to display the JTable.

Your current method is creating a new JTable to store the data and add it into ContentPane everytime the displayResultSet method is called, so the ContentPane is not change the current JTable to your new Jtable, its result is your ploblem.
To solve this problem you need to add the Jtable into the ContentPane only once time, after that you just change the table's model to display the updated data.

My suggestions:
  1. You need to use the DefaultTableModel class like this():
            DefaultTableModel model;    // <---- declare as a global variable
  2. Add the JTable into ContentPane only once time (me be in constructor) like this:
            model = new DefaultTableModel();
            table = new JTable( model );
            scroller = new JScrollPane( table );
            getContentPane().add( scroller, BorderLayout.CENTER );
                       .
                       .
            // read data from database
                       .
                       .
            // display table with ResultSet contents
            displayResultSet( rs );
  3. You need to change your algorithm in the displayResultSet method like this:
           private void displayResultSet( ResultSet rs ) throws SQLException
          {
             // position to first record
             boolean moreRecords = rs.next();
       
             // If there are no records, display a message
             if ( ! moreRecords ) {
               JOptionPane.showMessageDialog( this,
                               "ResultSet contained no records" );
               return;
             }
     
             Vector columnHeads = new Vector();
             Vector rows = new Vector();

             try {
                // get column heads
                ResultSetMetaData rsmd = rs.getMetaData();
         
                for ( int i = 1; i <= rsmd.getColumnCount(); ++i )
                    columnHeads.addElement( rsmd.getColumnName( i ) );

                // get row data
                do {
                    rows.addElement( getNextRow( rs, rsmd ) );
                } while ( rs.next() );

                // set table data
                model.setDataVector(rows,columnHeads);

             } catch ( SQLException sqlex ) {
                sqlex.printStackTrace();
             }
       }
  4. The delete-button should has its actionPerformed like this:
       button.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent ae) {
                try {
                    int row = table.getSelectedRow();
                    String sql = "delete from TheTable where ID="+table.getValueAt(row,0);
                    // statement and connection are declared as global variable
                    statement.executeUpdate(sql);
                    statement = connection.createStatement();
                    ResultSet rs = statement.executeQuery("select * from TheTable");
                    displayResultSet(rs);
                } catch(Exception e) {
                    e.printStackTrace();
                }
            }
       });
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mick BarryJava DeveloperCommented:
You don't need to recreate the model each time, you can simply remove the row from the existing table.
0
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Frank-22Author Commented:
Hi Again...

Thanks for Your answer, but then I try add the code You surgested the table is no longer displayed.

Here is my source:

private void displayResultSet( ResultSet rs )
    throws SQLException
{
    // position to first record
    boolean moreRecords = rs.next();
   
    // If there are no records, display a message
    if ( ! moreRecords ) {
      JOptionPane.showMessageDialog( this,
                               "ResultSet contained no records" );
      return;
    }
 
    Vector columnHeads = new Vector();
    Vector rows = new Vector();

    try {
      // get column heads
      ResultSetMetaData rsmd = rs.getMetaData();
      
      for ( int i = 1; i <= rsmd.getColumnCount(); ++i )
          columnHeads.addElement( rsmd.getColumnName( i ) );

      // get row data
      do {
          rows.addElement( getNextRow( rs, rsmd ) );
      } while ( rs.next() );
   

      // display table with ResultSet contents
      model = new DefaultTableModel();
      table = new JTable( model );
      //table = new JTable( rows, columnHeads );
       table.addMouseListener(new MouseAdapter(){
             public void mouseClicked(MouseEvent e)
             {
                     int selectedColumn = table.getSelectedColumn();
                 int selectedRow = table.getSelectedRow();
                   }
           });
      getContentPane().remove(scroller);
      scroller = new JScrollPane( table );
      getContentPane().add(
                       scroller, BorderLayout.CENTER );
      validate();
      getContentPane().validate();
    }
    catch ( SQLException sqlex ) {
      sqlex.printStackTrace();
    }
}

private Vector getNextRow( ResultSet rs,
                     ResultSetMetaData rsmd )
    throws SQLException
{
    Vector currentRow = new Vector();

    for ( int i = 1; i <= rsmd.getColumnCount(); ++i )
      switch( rsmd.getColumnType( i ) ) {
      case Types.VARCHAR:
          currentRow.addElement( rs.getString( i ) );
          break;
      case Types.INTEGER:
          currentRow.addElement(
                          new Long( rs.getLong( i ) ) );
          break;
      default:
          System.out.println( "Type was: " +
                        rsmd.getColumnTypeName( i ) );
      }
   
    return currentRow;
}

String getColumnAsString(int row, int col) {
     TableModel model = table.getModel();
     return model.getValueAt(row, col).toString();
}

String getColumnhead(int col) {
     TableModel model = table.getModel();
    return model.getColumnName(col);
}


public void shutDown()
{
    try {
      connection.close();
    }
    catch ( SQLException sqlex ) {
      System.err.println( "Unable to disconnect" );
      sqlex.printStackTrace();
    }
}

What wrong ?

Sincerely
Frank
0
Mick BarryJava DeveloperCommented:
You don't add your data to the model:

model = new DefaultTableModel(rows, columnHeads);
0
Frank-22Author Commented:
Hi again and Thanks for Your answer.

I have the data you surgested to the constructer, and the other data to the displayResultSet function.

public Addressbook()
{
String username = "root";
String password = "";

String url = "jdbc:mysql:///addresstest";
try {
    connection = DriverManager.getConnection(url,username, password);
}
catch ( SQLException sqlex ) {
    System.err.println( "Unable to connect" );
    sqlex.printStackTrace();
}
// All this assembles the frame, so it belongs in the constructor.
    Container cont = getContentPane();
    this.setTitle( "Addressbook for ModMail" );
 
cont.setLayout(new BorderLayout());
JPanel buttonsPane = new JPanel();

ImageIcon greenV = new ImageIcon("greenV.gif");
newButton = new JButton("New", greenV);

 
ImageIcon magnif = new ImageIcon("magnif.gif");
JLabel label = new JLabel("firstname:");
searchButton = new JButton("Search", magnif);  

ImageIcon clear = new ImageIcon("clear.gif");
clearButton = new JButton("clear", clear);

ImageIcon redX = new ImageIcon("redX.gif");
deleteButton = new JButton("Delete", redX);  

ImageIcon v = new ImageIcon("v.gif");
updateButton = new JButton("Update", v);  

firstname = new JTextField();
scroller = new JScrollPane();
firstname.setPreferredSize(new Dimension(125,20));

model = new DefaultTableModel();
            table = new JTable( model );
            scroller = new JScrollPane( table );
            getContentPane().add( scroller, BorderLayout.CENTER );

buttonsPane.add(newButton);
buttonsPane.add(deleteButton);
buttonsPane.add(updateButton);
buttonsPane.add(label);
buttonsPane.add(firstname);
buttonsPane.add(searchButton);
buttonsPane.add(clearButton);

cont.add(buttonsPane,BorderLayout.SOUTH);
cont.add(scroller,BorderLayout.CENTER);
setSize( 750, 150 );
showTable();

newButton.addActionListener(this);
deleteButton.addActionListener(this);
updateButton.addActionListener(this);
searchButton.addActionListener(this);
clearButton.addActionListener(this);
}



private void displayResultSet( ResultSet rs )
    throws SQLException
{
    // position to first record
    boolean moreRecords = rs.next();
   
    // If there are no records, display a message
    if ( ! moreRecords ) {
      JOptionPane.showMessageDialog( this,
                               "ResultSet contained no records" );
      return;
    }
 
    Vector columnHeads = new Vector();
    Vector rows = new Vector();

    try {
      // get column heads
      ResultSetMetaData rsmd = rs.getMetaData();
      
      for ( int i = 1; i <= rsmd.getColumnCount(); ++i )
          columnHeads.addElement( rsmd.getColumnName( i ) );

      // get row data
      do {
          rows.addElement( getNextRow( rs, rsmd ) );
      } while ( rs.next() );
   
            model.setDataVector(rows, columnHeads);

      // display table with ResultSet contents
      //table = new JTable( rows, columnHeads );
       table.addMouseListener(new MouseAdapter(){
             public void mouseClicked(MouseEvent e)
             {
                     int selectedColumn = table.getSelectedColumn();
                 int selectedRow = table.getSelectedRow();
                   }
           });
      getContentPane().remove(scroller);
      scroller = new JScrollPane( table );
      getContentPane().add(
                       scroller, BorderLayout.CENTER );
      validate();
      getContentPane().validate();
    }
    catch ( SQLException sqlex ) {
      sqlex.printStackTrace();
    }
}

private Vector getNextRow( ResultSet rs,
                     ResultSetMetaData rsmd )
    throws SQLException
{
    Vector currentRow = new Vector();

    for ( int i = 1; i <= rsmd.getColumnCount(); ++i )
      switch( rsmd.getColumnType( i ) ) {
      case Types.VARCHAR:
          currentRow.addElement( rs.getString( i ) );
          break;
      case Types.INTEGER:
          currentRow.addElement(
                          new Long( rs.getLong( i ) ) );
          break;
      default:
          System.out.println( "Type was: " +
                        rsmd.getColumnTypeName( i ) );
      }
   
    return currentRow;
}

String getColumnAsString(int row, int col) {
     TableModel model = table.getModel();
     return model.getValueAt(row, col).toString();
}

String getColumnhead(int col) {
     TableModel model = table.getModel();
    return model.getColumnName(col);
}

But I get an can not resolve symbol with model.setDataVector(rows, columnHeads);

am I missing a package ?

Sincerely

Frank

p.s. Will Your surgestion enable the user to refresh the JTable then pressing the delete-button in the GUI ?

Sincerely
Frank

0
Mick BarryJava DeveloperCommented:
I don't see where model is declared.
0
Frank-22Author Commented:
Model is declaired here:

import java.sql.*;
import javax.swing.*;
import java.awt.*;
import java.awt.event.*;
import java.util.*;
import java.awt.Container;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import javax.swing.JButton;
import javax.swing.JDialog;
import javax.swing.JFrame;
import javax.swing.JTextField;
import javax.swing.table.TableModel;
import javax.swing.table.DefaultTableModel;

 public class Addressbook extends JFrame implements ActionListener {
     private Connection connection;
     private JTable table;
     private JTextField emailadr;
     private JTextField firstname;
     private JTextField lastname;
     private JTextField alias;
    ----> private TableModel model;
     private JScrollPane scroller;
     private JButton searchButton;
     private JButton newButton;
     private JButton addButton;
     private JButton cancelButton;
     private JButton updateButton;
     private JButton deleteButton;
     private JButton clearButton;
     private String columnHead;
     private int selectedRow;
     private int selectedColumn;

Frank
0
Mick BarryJava DeveloperCommented:
You need to declare it as a DefaultTableModel.
0
Frank-22Author Commented:
Okay thanks...

Now it compiles, but another thing then I click on a record that I want to delete in JTable and db, I'm only allowed to delete the first selected record not the second selected or the third etc !!

Is it something to do with the table model ?

Secondly how do then pressing the delete-Button delete the selected row physically from the JTable ?

I have heard is something to do with a Table-renderer or something like that !

Do You have any idear on how to do that ?

Sincerely

Frank  
0
Mick BarryJava DeveloperCommented:
You should be able to delete whatever rows you want. Can you post your code that handles the delete.
0
Frank-22Author Commented:
Yes...

Here:

private void deleteRecord() {
    Statement statement;
    ResultSet rs;
    String cl = getColumnAsString(selectedRow, selectedColumn);
    String cn = getColumnhead(selectedColumn);
    try {
      String query = "delete from contacts where " + cn + " like '" + cl + "'";
      
      statement = connection.createStatement();
      statement.executeUpdate( query );
      rs = statement.executeQuery("select *from contacts");
      //displayResultSet(rs);
      System.out.println( query);
      statement.close();
}
    catch ( Exception e) {
      e.printStackTrace();
 }
}

Its designed so the the user clicks on a abitrary column in a abitrary row and then hits delete the row is deleted from mysql-db !

Sincerely
Frank
0
Mick BarryJava DeveloperCommented:
You haven't added code to delete the row from the model yet :)

model.removeRow(selectedRow);
0
Frank-22Author Commented:
hi

I add that to the deleteRecord function above ?

Frank
0
Mick BarryJava DeveloperCommented:
If thats when you want to remove the row from the JTable then yes.
0
Frank-22Author Commented:
No the code I mentioned above is to remove a record from the mysql "not" from the JTable.

Thats what I'm missing !

You got a surgestion on how to implement removabillity in my Table-model ?

Sincerley

Frank
0
Mick BarryJava DeveloperCommented:
> You got a surgestion on how to implement removabillity in my Table-model ?

yes:)

model.removeRow(row);
0
Frank-22Author Commented:
Hi again...

Have I placed it correctly ?


Sincerely

Frank

private void displayResultSet( ResultSet rs )
    throws SQLException
{
    // position to first record
    boolean moreRecords = rs.next();
   
    // If there are no records, display a message
    if ( ! moreRecords ) {
      JOptionPane.showMessageDialog( this,
                               "ResultSet contained no records" );
      return;
    }
 
    Vector columnHeads = new Vector();
    Vector rows = new Vector();

    try {
      // get column heads
      ResultSetMetaData rsmd = rs.getMetaData();
      
      for ( int i = 1; i <= rsmd.getColumnCount(); ++i )
          columnHeads.addElement( rsmd.getColumnName( i ) );

      // get row data
      do {
          rows.addElement( getNextRow( rs, rsmd ) );
      } while ( rs.next() );
   
            model.setDataVector(rows, columnHeads);
            model.removeRow(row);
      // display table with ResultSet contents
       table.addMouseListener(new MouseAdapter(){
             public void mouseClicked(MouseEvent e)
             {
                     int selectedColumn = table.getSelectedColumn();
                 int selectedRow = table.getSelectedRow();
                   }
           });
      getContentPane().remove(scroller);
      scroller = new JScrollPane( table );
      getContentPane().add(
                       scroller, BorderLayout.CENTER );
      validate();
      getContentPane().validate();
    }
    catch ( SQLException sqlex ) {
      sqlex.printStackTrace();
    }
}

private Vector getNextRow( ResultSet rs,
                     ResultSetMetaData rsmd )
    throws SQLException
{
    Vector currentRow = new Vector();

    for ( int i = 1; i <= rsmd.getColumnCount(); ++i )
      switch( rsmd.getColumnType( i ) ) {
      case Types.VARCHAR:
          currentRow.addElement( rs.getString( i ) );
          break;
      case Types.INTEGER:
          currentRow.addElement(
                          new Long( rs.getLong( i ) ) );
          break;
      default:
          System.out.println( "Type was: " +
                        rsmd.getColumnTypeName( i ) );
      }
   
    return currentRow;
}

String getColumnAsString(int row, int col) {
     TableModel model = table.getModel();
     return model.getValueAt(row, col).toString();
}

String getColumnhead(int col) {
     TableModel model = table.getModel();
    return model.getColumnName(col);
}
0
Mick BarryJava DeveloperCommented:
I'm confused, why have you added it there. Isn't that code loading the data into the table, why are you removing a row there?

>      table.addMouseListener(new MouseAdapter(){
>           public void mouseClicked(MouseEvent e)
>           {
>                     int selectedColumn = table.getSelectedColumn();
>               int selectedRow = table.getSelectedRow();
>                }
>           });

Also, this is unnecessary. JTable already has methods to return the row and column selection.
0
Frank-22Author Commented:
Hi

This code here :

       table.addMouseListener(new MouseAdapter(){
             public void mouseClicked(MouseEvent e)
             {
                     int selectedColumn = table.getSelectedColumn();
                 int selectedRow = table.getSelectedRow();
                   }
           });

Is for then the user clicks on column-field this colum-name and value is read into the sql-statement.

But I can see now, that this method doen't in the it was intended to.


So I'm missing a method to then the user clicks on a column in the row in JTable the value and name of this column is read into the delete sql-statement !

Do You have any idear on how I implement the above ?

Secondly I'm missing a method that registers the users clicks on a row and then deleteButton is pressed the row is removed both from the JTable and as well as from the database.

Do You have any idears on how to implement this ?

Thanks for all Your help and assistance !

I really appricate it !

Sincerely
Frank
0
Mick BarryJava DeveloperCommented:
The table already has a method that returns the currently selected row.

int row = table.getSelectedRow();
0
Frank-22Author Commented:
Hi

Thanks...

I  put it into my sql-statement like this ?

 private void deleteRecord() {
    Statement statement;
    ResultSet rs;
    String cl = getColumnAsString(selectedRow, selectedColumn);
    String cn = getColumnhead(selectedColumn);
    try {
      //      String query = "delete from contacts where " + cn + " like '" + cl + "'";
      int row = table.getSelectedRow();
         String query = "delete from contacts  ........       
          statement= connection.createStatement();
      statement.executeUpdate( query );
      rs = statement.executeQuery("select *from contacts");
      //displayResultSet(rs);
      System.out.println( query);
      statement.close();
}
    catch ( Exception e) {
      e.printStackTrace();
 }
}

But what do I put into my sql-statement, so it registeres both the column name and value of the column I click upon in the JTable ?

Sincerely

Frank
 
0
Mick BarryJava DeveloperCommented:
No you don't have selectedRow, selectedColumn member variables anymore.
Instead get these values from the table.
0
Frank-22Author Commented:
One Question though how do I read-in the name of column into the sql-statement ?

Is it String ch = tabel.getColumnHead() ?

Sincerely

Frank
0
Mick BarryJava DeveloperCommented:
Same way you already are:

String cn = getColumnhead(table.getSelectedColumn());
0
Frank-22Author Commented:
Hi

If I want to get the value of the column I click upon do I do like so :

      String cl = getColumnAsString(table.getSelectedColumn());

?

Frank
0
Mick BarryJava DeveloperCommented:
No, getColumnAsString() takes two parameters doesn't it? You need to pass it the row and column numbers.
0
Frank-22Author Commented:
Thank You it does..

I have tried this, but then I set:



java.lang.ArrayIndexOutOfBoundsException: 1 >= 1:

Here is my code:

      String ch = getColumnhead(table.getSelectedColumn());
      String cl = getColumnAsString(table.getSelectedColumn(), table.getSelectedRow());
      String query = "delete from contacts where " + ch + "like" + cl + "'";

Whats wrong ?

Frank
0
Mick BarryJava DeveloperCommented:
you've got the row and column numbers the wrong way around by the looks of it.

0
Frank-22Author Commented:
Hi

Now the clicking works again....

But I'm still lacking the abillity to refresh the table after delete from mysql-db statement has been execute !

Do I need to change my function displayResultSet() or deleteRecord() ?

Frank
0
Mick BarryJava DeveloperCommented:
You just need to remove the row from the table model using the code I posted earlier. Where you put it is up to when you want it to happen.
0
Frank-22Author Commented:
Hi

Like this:

Addressbook.java:237: cannot resolve symbol
symbol  : method removeRow (int)
location: interface javax.swing.table.TableModel
      model.removeRow(row);
             ^
1 error

Here is my code:

 private void deleteRecord() {
    Statement statement;
    ResultSet rs;
      try {
      int row = table.getSelectedRow();
      int column = table.getSelectedColumn();       
      String ch = getColumnhead(table.getSelectedColumn());
      String cl = getColumnAsString(table.getSelectedRow(), table.getSelectedColumn());
      String query = "delete from contacts where " + ch + " like '" + cl + "'";
      statement  = connection.createStatement();
      statement.executeUpdate( query );
      model.removeRow(row);
      rs = statement.executeQuery("select *from contacts");
      //displayResultSet(rs);
      System.out.println( query);
      statement.close();
}
    catch ( Exception e) {
      e.printStackTrace();
 }
}

Whats wrong ?

Frank
0
Mick BarryJava DeveloperCommented:
You need to declare model as a DefaultTableModel (as I stated earlier).
0
Frank-22Author Commented:
Okay

Now I compile, but then I press delete I get this nullpointer exception:

java.lang.NullPointerException
      at Addressbook.deleteRecord(Addressbook.java:237)
      at Addressbook.actionPerformed(Addressbook.java:130)
      at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
      at javax.swing.AbstractButton$ForwardActionEvents.actionPerformed(Unknown Source)
      at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
      at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
      at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
      at java.awt.Component.processMouseEvent(Unknown Source)
      at java.awt.Component.processEvent(Unknown Source)
      at java.awt.Container.processEvent(Unknown Source)
      at java.awt.Component.dispatchEventImpl(Unknown Source)
      at java.awt.Container.dispatchEventImpl(Unknown Source)
      at java.awt.Component.dispatchEvent(Unknown Source)
      at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
      at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
      at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
      at java.awt.Container.dispatchEventImpl(Unknown Source)
      at java.awt.Window.dispatchEventImpl(Unknown Source)
      at java.awt.Component.dispatchEvent(Unknown Source)
      at java.awt.EventQueue.dispatchEvent(Unknown Source)
      at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)
      at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
      at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
      at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
      at java.awt.EventDispatchThread.run(Unknown Source)


I guess line 237 as this one

      model.removeRow(row);

I have declaired private DefaultTableModel model; in the constructer so what wrong ?

Frank
0
Mick BarryJava DeveloperCommented:
Sounds like you never set 'model'.
Check that you actually create it.
0
Frank-22Author Commented:
Hi now thats solved thanks to You...

I have another function with handels table column updates, that I have some trouble with.

First of all the sql-statement is wrong .

Because I get this error every time I try to run it !

java.sql.SQLException: Syntax error or access violation,  message from server: "You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '(emailadr, firstname, lastname, alias)'

private void updateRecord() {
    Statement statement;
    ResultSet rs;
          int row = table.getSelectedRow();
      int column = table.getSelectedColumn();
    String cl = getColumnAsString(table.getSelectedRow(), table.getSelectedColumn());
    String cn = getColumnhead(table.getSelectedColumn());
    try {
      statement = connection.createStatement();
      String query = "update contacts set (emailadr, firstname, lastname, alias)" + cl + " ',' " + cn + "'";
      statement.executeUpdate( query );
      statement.close();
}
    catch (SQLException sqlex) {
      sqlex.printStackTrace();
    }
}

Whats wrong ?

This update feature was suppose to work like this , the user double clicks on column-field that he want to update in JTable.

Next he enters a new value for column and press update...

But I'm missing a feature to register this update !

Do You have a surgestion ?

Sincerly
Frank

0
Mick BarryJava DeveloperCommented:
Your SQL query is invalid.

http://www.w3schools.com/sql/sql_update.asp
0
Frank-22Author Commented:
Okay Thanks...

Is there a command for updating a cell like the one for delete model.removeRow(row);

Sincerely

Frank
0
Mick BarryJava DeveloperCommented:
Yes, setValueAt().
See your javadoc for more information, it's reference you should always keep handy :)
0
Frank-22Author Commented:
Hi

Thanks for Your Documentation:

I have come up with this sql - update function:

private void updateRecord() {
    Statement statement;
    ResultSet rs;
          int row = table.getSelectedRow();
      int column = table.getSelectedColumn();
    String cl = getColumnAsString(table.getSelectedRow(), table.getSelectedColumn());
    String cn = getColumnhead(table.getSelectedColumn());
    try {
      statement = connection.createStatement();
      String query = "update contacts set'" + cn + "where" + cn +  "=" + cl +  "'";
      statement.executeUpdate( query );
      //
      statement.close();
}
    catch (SQLException sqlex) {
      sqlex.printStackTrace();
    }
}

The query should be right, but I get an SQL-Syntax error:

Can You see what wrong ?`

Secondly getting the system to registerer the sql - update inside the JTable Do I do it like:


  String cl = getColumnAsString(table.getSelectedRow(setValueAt(row)),table.getSelectedColumn(setValueAt(column)));
    String cn = getColumnhead(table.getSelectedColumn(SetValueAt(column)));

I don't need to call any feature in the update-function like I did in the delete-function:

model....... ?

Sincerley

Frank
0
Mick BarryJava DeveloperCommented:
> Can You see what wrong ?`

The sql statement is still wrong, you don't specify what to set the value to.

> I don't need to call any feature in the update-function like I did in the delete-function

Yes you need to call setValueAt (assuming the updates are already done on the table).
0
Frank-22Author Commented:
OKay....

Like this ?

private void updateRecord() {
    Statement statement;
    ResultSet rs;
         int row = table.getSelectedRow();
     int column = table.getSelectedColumn();
    String cl = getColumnAsString(table.getSelectedRow(), table.getSelectedColumn());
    String cn = getColumnhead(table.getSelectedColumn());
    String clnew = setValueAt(column);

    try {
     statement = connection.createStatement();
     String query = "update contacts set'" + cn + "="  + clnew + "where" + cn +  "=" + cl +  "'";
     statement.executeUpdate( query );
     //
     statement.close();
}
    catch (SQLException sqlex) {
     sqlex.printStackTrace();
    }
}

Sincerely

Frank
0
Mick BarryJava DeveloperCommented:
What value are you actually using to update the table???

> String query = "update contacts set'" + cn + "="  + clnew + "where" + cn +  "=" + cl +  "'";

you need some spaces around sql key workds.
Print out the query string to see what it generates.
System.out.println(query);
0
Frank-22Author Commented:
HI

the string clnew hot to get the new value entered in the JTable during the update,

  String clnew = table.getValueAt(column, row) ???

So String ends up as :

String query = " update contacts set '" + cn + " = "  + clnew + " where " + cn +  " = " + cl +  "'";

Is this correct then ?

Sincerely

Frank
0
Mick BarryJava DeveloperCommented:
looks about right
0
Frank-22Author Commented:
OKay, but then I compile I get this here error:

Addressbook.java:215: incompatible types
found   : java.lang.Object
required: java.lang.String
     String clnew = table.getValueAt(column, row);
                                    ^
1 error

Whats wrong with the clnew String ?

Frank
0
Mick BarryJava DeveloperCommented:
String clnew = (String) table.getValueAt(column, row);
0
Frank-22Author Commented:
Thanks now...

But the sql - syntax is still not gone:

java.sql.SQLException: Syntax error or access violation,  message from server: "You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near ''' at line 1"

Any idear what I'm doing wrong ?

private void updateRecord() {
    Statement statement;
    ResultSet rs;
         int row = table.getSelectedRow();
     int column = table.getSelectedColumn();
    String cl = getColumnAsString(table.getSelectedRow(), table.getSelectedColumn());
    String cn = getColumnhead(table.getSelectedColumn());
    String clnew = (String) table.getValueAt(column, row);

    try {
     statement = connection.createStatement();
     String query = " update contacts set " + cn + " = "  + clnew + " where " + cn +  " = " + cl +  "'";
     statement.executeUpdate( query );
     statement.close();
}
    catch (SQLException sqlex) {
     sqlex.printStackTrace();
    }
}


Sincerely

Frank
0
Mick BarryJava DeveloperCommented:
A lot easier to find problem if you print out the sql statement as I suggested above. That way you can see the sql statement that is being sent.
Looks like you aren't quoting your strings correctly.
0
Frank-22Author Commented:
Hi

I can that for some reason or another that the query don't read-in the old and new value.

If the column firstname has a value = Bill and I change it to Will by doubleclicking on the cell the query come out like this.

update set contacts set 'firstname = Bill where firstname =  Bill'

For some reason or another the function can't tell the diffence between the old and new value of column...

I guess You have too then You click on the column you wish to update, that the old value of the column is stored in the string variable cl and the new value is stored in the string variable clnew.

You got any ider on how to make it do that ?

Frank
0
Mick BarryJava DeveloperCommented:
> and I change it to Will by doubleclicking on the cell

Then the value of the cell has already been changed.

Your approach wouldn't work anyway eg. what if there were more than one records with name 'Bill'.

You need to use a unique key for your where clause.

0
Frank-22Author Commented:
Hi

Any idear on how I generate this unique key ?

Frank
0
Mick BarryJava DeveloperCommented:
Doesn't your database table already have a primary key.
0
Frank-22Author Commented:
Yeah is emailadr...

The other columns are (firstname, lastname, alias)

Frank
0
Mick BarryJava DeveloperCommented:
Then use that.
0
Frank-22Author Commented:
I change the first cn in the query to emailadr then ?

I will be able to update other fields than email ?

Frank
0
Mick BarryJava DeveloperCommented:
No you need to use email address in your where clause.
You don't want to set your email address (in fact as it's your primary key you shouldn't)
0
Frank-22Author Commented:
Hi

is this what You mean ?

private void updateRecord() {
    Statement statement;
    ResultSet rs;
         int row = table.getSelectedRow();
     int column = table.getSelectedColumn();
    String cl = getColumnAsString(table.getSelectedRow(), table.getSelectedColumn());
    String cn = getColumnhead(table.getSelectedColumn());
    String clnew = (String) table.getValueAt(column, row);

    try {
     statement = connection.createStatement();
     String query = " update contacts set " + cn + " = "  + clnew + " where emailadr " +cn + " = " + cl +  "'";
     System.out.println(query);
     statement.executeUpdate( query );
     statement.close();
}
    catch (SQLException sqlex) {
     sqlex.printStackTrace();
    }
}

Frank
0
Mick BarryJava DeveloperCommented:
> where emailadr " +cn + " = " + cl +  "'";

You don't need cn in there. Just:

where emaladr=abc@xyz.com.au
0
Frank-22Author Commented:
Okay this mean that the final query must look like this ?

 String query = " update contacts set " + cn + "  = "  + clnew +  "where emailadr"  "  = "  + cl +  "'";

Frank
0
Mick BarryJava DeveloperCommented:
if cl is the email address for the current row then yes.
0
gnoonCommented:
Hi Frank-22,

I have read your comments and I found that you need an example about JTable. Below is my example that you can copy and paste.

import javax.swing.*;
import javax.swing.table.*;
import javax.swing.event.*;
import java.awt.event.*;
import java.awt.*;
import java.util.*;

public class Addressbook extends JFrame
{
    JTable table;
    JScrollPane scroller;
    JTextField text;
    DefaultTableModel model;
    Vector rows = new Vector();
    Vector columnHeads = new Vector();
    Container cont;
    static final int UPDATE = 1;
    static final int DELETE = 2;

    public Addressbook() {
        super("Table example");
        cont = getContentPane();
        cont.setLayout(new BorderLayout());
        setSize(500,500);

        // delete-button
        JButton delButton = new JButton("Delete");
        delButton.addActionListener(new ActionListener() {
            public void actionPerformed(ActionEvent ae) {
                int col = table.getSelectedColumn();
                int row = table.getSelectedRow();
                if(row >= 0)
                    text.setText(generateSQL(DELETE,row,col));
            }
        });
        JPanel buttonPanel = new JPanel(new FlowLayout(FlowLayout.CENTER));
        buttonPanel.add(delButton);

        // create table
        model = new DefaultTableModel(columnHeads,rows);
        table = new JTable(model){
            public void editingStopped(ChangeEvent e) {
                super.editingStopped(e);
                int col = table.getSelectedColumn();
                int row = table.getSelectedRow();
                text.setText(generateSQL(UPDATE,row,col));
            }
        };
        scroller = new JScrollPane(table);

        // show text
        text = new JTextField(35);
        JPanel p = new JPanel();
        p.add(new JLabel("SQL-Statement:"));
        p.add(text);

        // add all
        cont.add(buttonPanel,BorderLayout.NORTH);
        cont.add(scroller,BorderLayout.CENTER);
        cont.add(p,BorderLayout.SOUTH);

        showTable();
    }

    private void showTable() {
        // get data
        Vector data = new Vector();
        String columnNames[] = {"emailadr","firstname","lastname","alias"};
        Object values[][] = {{"a@web.com","a","aa","1"},
                             {"b@web.com","b","bb","2"},
                             {"c@web.com","c","cc","3"}
                            };
        data.add(columnNames);
        data.add(values);

        displayResultSet(data);
    }

    private void displayResultSet(Vector data) {
        // test data
        boolean moreRecords = (((Object[][])data.elementAt(1)).length>0);

        // If there are no records, display a message
        if ( ! moreRecords ) {
         JOptionPane.showMessageDialog( this,
                               "ResultSet contained no records" );
         return;
        }

        String columnNames[] = (String[])data.elementAt(0);
        Object dataRows[][] = (Object[][])data.elementAt(1);

        model.setDataVector(dataRows,columnNames);
    }

    private String getColumnhead(int col) {
        return table.getModel().getColumnName(col);
    }

    private String getColumnAsString(int row, int col) {
        return table.getValueAt(row, col).toString();
    }

    private String getPrimaryValue(int row) {
        return table.getValueAt(row, 0).toString();
    }

    private String getPrimaryHead() {
        return table.getModel().getColumnName(0);
    }

    private String generateSQL(int type,int row,int col) {
        String sql = "",cn,cl,pricn,pricl;
        switch(type) {
            case UPDATE:
                cn = getColumnhead(col);
                cl = getColumnAsString(row,col);
                pricn = getPrimaryHead();
                pricl = getPrimaryValue(row);
                sql = "update contacts set "+cn+"='"+cl+"' where "+pricn+"='"+pricl+"'";
            break;
            case DELETE:
                pricn = getPrimaryHead();
                pricl = getPrimaryValue(row);
                sql = "delete contacts where "+pricn+"='"+pricl+"'";
            break;
        }
        return sql;
    }

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

Hope this help. :)
-gnoon-
0
Mick BarryJava DeveloperCommented:
> Below is my example that you can copy and paste.

Example looks surprisingly similiar to franks code (which is pretty much working now).

0
gnoonCommented:
>Example looks surprisingly similiar to franks code
Yes, it is because I want to make ease to compare with frank code.

>(which is pretty much working now).
Of course, coze I'm not experts but pretty good programming. :)
0
Frank-22Author Commented:
Hi

I still have problem with compiling my update-function:

First of all the compile error.

     String query = String query = " update contacts set " + cn + "  = "  + clnew +  "where emailadr"  "="  + cl +  "'";
                           ^
1 error

My source-code :

private void updateRecord() {
    Statement statement;
    ResultSet rs;
         int row = table.getSelectedRow();
     int column = table.getSelectedColumn();
    String cl = getColumnAsString(table.getSelectedRow(), table.getSelectedColumn());
    String cn = getColumnhead(table.getSelectedColumn());
    String clnew = (String) table.getValueAt(row, column);

    try {
     statement = connection.createStatement();
     String query = String query = " update contacts set " + cn + "  = "  + clnew +  "where emailadr"  "="  + cl +  "'";
  System.out.println( query );    
  statement.executeUpdate( query );
   
    statement.close();
}
    catch (SQLException sqlex) {
     sqlex.printStackTrace();
    }
}

Explaination of source:

I would to be able to Update the contents of JTable which is linked to a mysql-db, first by double clicking on the cell that I wish to update, then entering a new value and finally pressing update JButton in the GUI the changes are commited to both the JTable and the mysql-db.

My primary db key is emailadr and the other cell-names are (firstname, lastname, alias) ...

What is wrong with my code since it will not work ????

Sinerely

Frank
0
gnoonCommented:
>String query = String query = " update contacts set " + cn + "  = "  + clnew +  "where emailadr"  "="  + cl +  "'";

should be

String query = "update contacts set " + cn + "='"  + clnew + "' where emailadr='"  + cl + "'";

You must use single quote ( ' ) in your sql-statement whenever your database field type is char or varchar.
0
Frank-22Author Commented:
Hi

Now the sql-statement compiles, but I have some trouble.

First of all my table consists of four column (emailadr, firstname, lastname, alias) where emailadr is the primary key for the mysql-db.



private void updateRecord() {
    Statement statement;
    ResultSet rs;
         int row = table.getSelectedRow();
     int column = table.getSelectedColumn();
    String cl = getColumnAsString(table.getSelectedRow(), table.getSelectedColumn());
    String cn = getColumnhead(table.getSelectedColumn());
    String clnew = (String) table.getValueAt(column, 0);

    try {
     statement = connection.createStatement();
    String query = "update contacts set "+cn+"='"+cl+"' where emailadr = '"+clnew+"'";
   
System.out.println( query );    
  statement.executeUpdate( query );
   
    statement.close();
}
    catch (SQLException sqlex) {
     sqlex.printStackTrace();
    }
}
0
Frank-22Author Commented:
Sorry I didn't get it all in...

With the sql-statement above I'm only able to update the firstname - columns not the emailadr, lastname, alias.

How do I update my sql-statement so I'm able to update all the entire record ?

Sincerley

Frank
0
Mick BarryJava DeveloperCommented:
> With the sql-statement above I'm only able to update the firstname - columns not
> the emailadr, lastname, alias.

Why not? When do you call updateRecord()?
You should only need to update one at a time (as the user can only change one at a time).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Editors IDEs

From novice to tech pro — start learning today.