Deleting from a JTable physically

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.

You don't need to recreate the model each time, you can simply remove the row from the existing table.

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

You don't add your data to the model:

model = new DefaultTableModel(rows, columnHeads);

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

I don't see where model is declared.

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

You need to declare it as a DefaultTableModel.

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  

You should be able to delete whatever rows you want. Can you post your code that handles the delete.

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

You haven't added code to delete the row from the model yet :)

model.removeRow(selectedRow);

hi

I add that to the deleteRecord function above ?

Frank

If thats when you want to remove the row from the JTable then yes.

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

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

yes:)

model.removeRow(row);

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);
}

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.

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

The table already has a method that returns the currently selected row.

int row = table.getSelectedRow();

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
 

No you don't have selectedRow, selectedColumn member variables anymore.
Instead get these values from the table.

One Question though how do I read-in the name of column into the sql-statement ?

Is it String ch = tabel.getColumnHead() ?

Sincerely

Frank

Same way you already are:

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

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

No, getColumnAsString() takes two parameters doesn't it? You need to pass it the row and column numbers.

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

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

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

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.

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

You need to declare model as a DefaultTableModel (as I stated earlier).

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

Sounds like you never set 'model'.
Check that you actually create it.

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

Your SQL query is invalid.

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

Okay Thanks...

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

Sincerely

Frank

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

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

> 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).

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

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);

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

looks about right

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

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

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

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.

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

> 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.

Hi

Any idear on how I generate this unique key ?

Frank

Doesn't your database table already have a primary key.

Yeah is emailadr...

The other columns are (firstname, lastname, alias)

Frank

Then use that.

I change the first cn in the query to emailadr then ?

I will be able to update other fields than email ?

Frank

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)

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

> where emailadr " +cn + " = " + cl +  "'";

You don't need cn in there. Just:

where emaladr=abc@xyz.com.au

Okay this mean that the final query must look like this ?

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

Frank

if cl is the email address for the current row then yes.

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-

> Below is my example that you can copy and paste.

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

>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. :)

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

>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.

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();
    }
}

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

> 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).