Solved

Deleting from a JTable physically

Posted on 2003-11-16
69
1,029 Views
Last Modified: 2013-11-23
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
0
Comment
Question by:Frank-22
  • 33
  • 32
  • 4
69 Comments
 
LVL 92

Expert Comment

by:objects
ID: 9760187
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
 
LVL 16

Accepted Solution

by:
gnoon earned 250 total points
ID: 9761459
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
 
LVL 92

Expert Comment

by:objects
ID: 9761562
You don't need to recreate the model each time, you can simply remove the row from the existing table.
0
 

Author Comment

by:Frank-22
ID: 9762362
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
 
LVL 92

Expert Comment

by:objects
ID: 9766677
You don't add your data to the model:

model = new DefaultTableModel(rows, columnHeads);
0
 

Author Comment

by:Frank-22
ID: 9766834
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
 
LVL 92

Expert Comment

by:objects
ID: 9766868
I don't see where model is declared.
0
 

Author Comment

by:Frank-22
ID: 9766908
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
 
LVL 92

Expert Comment

by:objects
ID: 9766975
You need to declare it as a DefaultTableModel.
0
 

Author Comment

by:Frank-22
ID: 9767132
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
 
LVL 92

Expert Comment

by:objects
ID: 9767180
You should be able to delete whatever rows you want. Can you post your code that handles the delete.
0
 

Author Comment

by:Frank-22
ID: 9767248
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
 
LVL 92

Expert Comment

by:objects
ID: 9767263
You haven't added code to delete the row from the model yet :)

model.removeRow(selectedRow);
0
 

Author Comment

by:Frank-22
ID: 9767324
hi

I add that to the deleteRecord function above ?

Frank
0
 
LVL 92

Expert Comment

by:objects
ID: 9767402
If thats when you want to remove the row from the JTable then yes.
0
 

Author Comment

by:Frank-22
ID: 9767584
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
 
LVL 92

Expert Comment

by:objects
ID: 9767628
> You got a surgestion on how to implement removabillity in my Table-model ?

yes:)

model.removeRow(row);
0
 

Author Comment

by:Frank-22
ID: 9767713
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
 
LVL 92

Expert Comment

by:objects
ID: 9767734
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
 

Author Comment

by:Frank-22
ID: 9769905
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
 
LVL 92

Expert Comment

by:objects
ID: 9774476
The table already has a method that returns the currently selected row.

int row = table.getSelectedRow();
0
 

Author Comment

by:Frank-22
ID: 9774636
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
 
LVL 92

Expert Comment

by:objects
ID: 9774661
No you don't have selectedRow, selectedColumn member variables anymore.
Instead get these values from the table.
0
 

Author Comment

by:Frank-22
ID: 9774738
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
 
LVL 92

Expert Comment

by:objects
ID: 9774794
Same way you already are:

String cn = getColumnhead(table.getSelectedColumn());
0
 

Author Comment

by:Frank-22
ID: 9774882
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
 
LVL 92

Expert Comment

by:objects
ID: 9774923
No, getColumnAsString() takes two parameters doesn't it? You need to pass it the row and column numbers.
0
 

Author Comment

by:Frank-22
ID: 9774971
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
 
LVL 92

Expert Comment

by:objects
ID: 9775009
you've got the row and column numbers the wrong way around by the looks of it.

0
 

Author Comment

by:Frank-22
ID: 9775078
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
 
LVL 92

Expert Comment

by:objects
ID: 9775159
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
 

Author Comment

by:Frank-22
ID: 9775205
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
 
LVL 92

Expert Comment

by:objects
ID: 9775227
You need to declare model as a DefaultTableModel (as I stated earlier).
0
 

Author Comment

by:Frank-22
ID: 9775270
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 92

Expert Comment

by:objects
ID: 9775281
Sounds like you never set 'model'.
Check that you actually create it.
0
 

Author Comment

by:Frank-22
ID: 9775405
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
 
LVL 92

Expert Comment

by:objects
ID: 9775454
Your SQL query is invalid.

http://www.w3schools.com/sql/sql_update.asp
0
 

Author Comment

by:Frank-22
ID: 9776599
Okay Thanks...

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

Sincerely

Frank
0
 
LVL 92

Expert Comment

by:objects
ID: 9776651
Yes, setValueAt().
See your javadoc for more information, it's reference you should always keep handy :)
0
 

Author Comment

by:Frank-22
ID: 9780007
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
 
LVL 92

Expert Comment

by:objects
ID: 9781952
> 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
 

Author Comment

by:Frank-22
ID: 9782145
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
 
LVL 92

Expert Comment

by:objects
ID: 9782253
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
 

Author Comment

by:Frank-22
ID: 9782399
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
 
LVL 92

Expert Comment

by:objects
ID: 9782491
looks about right
0
 

Author Comment

by:Frank-22
ID: 9782539
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
 
LVL 92

Expert Comment

by:objects
ID: 9782573
String clnew = (String) table.getValueAt(column, row);
0
 

Author Comment

by:Frank-22
ID: 9782750
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
 
LVL 92

Expert Comment

by:objects
ID: 9782788
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
 

Author Comment

by:Frank-22
ID: 9783040
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
 
LVL 92

Expert Comment

by:objects
ID: 9783122
> 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
 

Author Comment

by:Frank-22
ID: 9783349
Hi

Any idear on how I generate this unique key ?

Frank
0
 
LVL 92

Expert Comment

by:objects
ID: 9783378
Doesn't your database table already have a primary key.
0
 

Author Comment

by:Frank-22
ID: 9783407
Yeah is emailadr...

The other columns are (firstname, lastname, alias)

Frank
0
 
LVL 92

Expert Comment

by:objects
ID: 9783452
Then use that.
0
 

Author Comment

by:Frank-22
ID: 9783484
I change the first cn in the query to emailadr then ?

I will be able to update other fields than email ?

Frank
0
 
LVL 92

Expert Comment

by:objects
ID: 9783518
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
 

Author Comment

by:Frank-22
ID: 9783791
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
 
LVL 92

Expert Comment

by:objects
ID: 9783815
> where emailadr " +cn + " = " + cl +  "'";

You don't need cn in there. Just:

where emaladr=abc@xyz.com.au
0
 

Author Comment

by:Frank-22
ID: 9783995
Okay this mean that the final query must look like this ?

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

Frank
0
 
LVL 92

Expert Comment

by:objects
ID: 9784010
if cl is the email address for the current row then yes.
0
 
LVL 16

Expert Comment

by:gnoon
ID: 9785205
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
 
LVL 92

Expert Comment

by:objects
ID: 9785242
> Below is my example that you can copy and paste.

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

0
 
LVL 16

Expert Comment

by:gnoon
ID: 9785406
>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
 

Author Comment

by:Frank-22
ID: 9785840
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
 
LVL 16

Expert Comment

by:gnoon
ID: 9786121
>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
 

Author Comment

by:Frank-22
ID: 9786188
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
 

Author Comment

by:Frank-22
ID: 9786231
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
 
LVL 92

Expert Comment

by:objects
ID: 9791888
> 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

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
strCopies  challenge 17 73
mapBully challenge 6 89
for i loop in grovy 1 32
Unexpected HttpURLConnection connection behavior 2 50
Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now