Deleting/Updating a JTable and commiting the changes to a mysql-db

Hi

I have been working on to creat a addressbook-program in Java, that uses an MYSQL-db for storing records.

My database consists of the following columns (emailadr, firstname, lastname, alias).

When I do a search for a record I type-in a firstname in the firstname JTextField and press search, if a record is found where the firstname - field is found be equal to the one inputted in the search-field, The record is then displayed in a JTable in a Swing GUI.

I would like to be able to delete a record in the JTable and commit this change to the db firstname by first clicking once on record in JTable so it turns blue and next hitting the JButton marked "delete".

Secondly I would like also to be able to update columns in a record in JTable and commiting these changes to db, by first double clicking on the JTable colums and changing the values and finally hitting the JButton update and thereby commit the changes to the mysql-db.

I hope that there are somebody out there who has idears/surgestions or how I can make the above work.

Here is my existing source:
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 javax.swing.JButton;
import javax.swing.JDialog;
import javax.swing.JFrame;
import javax.swing.JTextField;

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 JScrollPane scroller;
    private JButton searchButton;
    private JButton newButton;
    private JButton addButton;

   static
{
     try
     {
          Class.forName( "com.mysql.jdbc.Driver" );
     }
     catch ( ClassNotFoundException cnfex )
     {
          System.err.println( "Failed to load JDBC driver." );
          cnfex.printStackTrace();
          System.exit( 1 );
     }

}


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 redX = new ImageIcon("redX.gif");
JButton button1 = new JButton("Delete", redX);  

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

firstname = new JTextField();
scroller = new JScrollPane();
firstname.setPreferredSize(new Dimension(125,20));
   
buttonsPane.add(newButton);
buttonsPane.add(button1);
buttonsPane.add(button2);
buttonsPane.add(label);
buttonsPane.add(firstname);
buttonsPane.add(searchButton);

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

newButton.addActionListener(this);
button1.addActionListener(this);
button2.addActionListener(this);
searchButton.addActionListener(this);

}

public void actionPerformed(ActionEvent e)
    {
        JButton src = (JButton)e.getSource();
        if (src == searchButton)
        {
            this.getTable();
        }
        else if (src == newButton)
        {
            this.launchAnotherFrame();
        }
        else if (src == addButton)
        {
          this.addRecord();
        }
    }

private void getTable()
{
Statement statement;
ResultSet resultSet;

try {  
    String str = firstname.getText();
    String query = "select* from contacts where firstname like '" + str + "'";
    statement = connection.createStatement();
    System.out.println(query);
    resultSet = statement.executeQuery( query );
    displayResultSet( resultSet );
    statement.close();
}
catch ( SQLException sqlex ) {
    sqlex.printStackTrace();

}
}

 private void addRecord() {
     
     Statement statement;
 
    try {
      String st = emailadr.getText();
      String str = firstname.getText();
      String strr = lastname.getText();
      String strrr = alias.getText();
      String query = "INSERT INTO contacts (emailadr, firstname, lastname, alias) VALUES ('" +  
          st + "','" +
          str  + "','" +    
          strr + "','" +
          strrr + "')";
      
      statement = connection.createStatement();
      statement.executeUpdate( query );
      statement.close();
    }
    catch ( SQLException sqlex ) {
      sqlex.printStackTrace();
    }
 }

private void deleteRecord() {
}

private void updateRecord() {
}

 private void launchAnotherFrame()
    {
        JFrame frame = new JFrame("Add a new record");
        this.setContents(frame.getContentPane());
        frame.pack();
        frame.show();
    }

private void setContents(Container con) {
   
    addButton = new JButton("add");      
      addButton.addActionListener(this);
      JButton clr = new JButton("clear");
        JLabel eml = new JLabel("emailadr:");
        JLabel frst = new JLabel("firstname:");
        JLabel lst = new JLabel("lastname:");
        JLabel als = new JLabel("alias:");
         emailadr = new JTextField( 20 );
        firstname = new JTextField( 20 );
        lastname = new JTextField( 20 );
        alias = new JTextField( 20 );

        JPanel labelsPanel = new JPanel(new GridLayout(4, 1));
        labelsPanel.add(eml);
        labelsPanel.add(frst);
        labelsPanel.add(lst);
        labelsPanel.add(als);
       
        JPanel inputPanel = new JPanel(new GridLayout(4, 1));
        inputPanel.add(emailadr);
        inputPanel.add(firstname);
        inputPanel.add(lastname);
        inputPanel.add(alias);
       
        JPanel labelsAndInputs = new JPanel(new FlowLayout());
        labelsAndInputs.add(labelsPanel);
        labelsAndInputs.add(inputPanel);
       
        JPanel buttonPanel = new JPanel(new FlowLayout());
        buttonPanel.add(addButton);
       
        JPanel panel = new JPanel(new BorderLayout());
        panel.add(labelsAndInputs, BorderLayout.CENTER);
        panel.add(buttonPanel, BorderLayout.SOUTH);
       
        con.add(panel);
       
this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
}


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

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

public static void main( String args[] ) {
Addressbook adr = new Addressbook();
adr.show();

}
}

Cheers

Frank

 
Frank-22Asked:
Who is Participating?
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.

Frank-22Author Commented:
Okay let me rephrase the question:

Is it possible if the user clicks on a row in a JTable with the mouse to copy the contents of
each column-field to string variables ?

Sincerely
Frank
0
Frank-22Author Commented:
Hi again...

I have found out that to get my program to read-in the contents of a column-field I have to have a mouse-event a bit like this:

         this.addMouseListener(new MouseAdapter()
              {
                   public void mouseClicked(MouseEvent e)
                   {
                         // Set 'selectedRow'
                         int selectedRow = table.getSelectedRow();
                   }
             });

My question is how do I get on from there ?

How I save the selected row  ?

Is it possible to save a specific column from the JTable into a String ?


Hope that there is somebody out there who can assist me in solving my problem !

 Sincerely
Frank
0
CEHJCommented:
One of the reasons this question's not been worked on, I think, is that you've asked too much in one question.

>>How I save the selected row  ?

You made a good start on this though. I would do the following call the following function with your 'selectedRow' variable:

String getSelectedRowAsString(int row) {
       StringBuffer sb = new StringBuffer();
       TableModel model = yourTable.getModel();
       for(int col = 0;col < model.getColumnCount();col++) {
             sb.append(model.getValueAt(row, col).toString());
             sb.append(","); // (or any other separator you want)
      }
      String result = sb.toString();
      return result.substring(0, result.length() - 1);
}
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Frank-22Author Commented:
Hi and thanks for Your answer

I have placed your surgested code in this function:

Is that the correct spot to place it ?

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

      this.addMouseListener(new MouseAdapter(){
                    public void mouseClicked(MouseEvent e)
                   {
                         int selectedRow = table.getSelectedRow();
                        System.out.println(selectedRow);
                   }
             });

String getSelectedRowAsString(int row) {
      StringBuffer sb = new StringBuffer();
      TableModel model = yourTable.getModel();
      for(int col = 0;col < model.getColumnCount();col++) {
           sb.append(model.getValueAt(row, col).toString());
           sb.append(","); // (or any other separator you want)
     }
     String result = sb.toString();
     return result.substring(0, result.length() - 1);
}


// display table with ResultSet contents
      table = new JTable( rows, columnHeads );
      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;
}


Sincerely

Frank
0
CEHJCommented:
No - place the function after the one called 'getNextRow'
0
Frank-22Author Commented:
Hi again and thanks for Your answer !

My table is generated under the displayResultset - function, do I define that as my table model ?

Sincerely
Frank
0
CEHJCommented:
>>do I define that as my table model

The TableModel is got from the table reference. Simply replace 'yourTable' in my code with a valid reference to your table
0
Frank-22Author Commented:
Okay thanks...

I have done that, but then I try to compile I get the error:

symbol  : class TableModel
location: class Addressbook
    TableModel model;

Im I missing a package of some sort ?

Sincerely

Frank
0
CEHJCommented:
import javax.swing.table.TableModel
0
CEHJCommented:
(with ending colon obviously)
0
Frank-22Author Commented:
Okay Thanks,

Any way to check if infact the select row has been read into the String result ?

I tried to put in the following System.out.println(result);

Without any result.

Sincerely

Frank
0
CEHJCommented:
If you haven't changed this:



     this.addMouseListener(new MouseAdapter(){
                 public void mouseClicked(MouseEvent e)
                {
                     int selectedRow = table.getSelectedRow();
                    System.out.println(selectedRow);
                }
           });

then you're not calling my function, so you won't get any output
0
Frank-22Author Commented:
Hi Again..

Do You mean like this ? :

      this.addMouseListener(new MouseAdapter(){
            public void mouseClicked(MouseEvent e)
                   {
                         int selectedRow = table.getSelectedRow();
                          getSelectedRowAsString(int row);
                   }
             });


Sincerely
Frank
0
CEHJCommented:


     this.addMouseListener(new MouseAdapter(){
          public void mouseClicked(MouseEvent e)
                {
                     int selectedRow = table.getSelectedRow();
                       System.out.println(getSelectedRowAsString(int selectedRow));
                }
           });

0
Frank-22Author Commented:
Hi

Sorry to trouble You again.

I compiles now, but the selected row is still not display in the System.out.println - statement.

Am I missing anything ?

Here is displayResultSet function again:

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() );
   
        this.addMouseListener(new MouseAdapter(){
          public void mouseClicked(MouseEvent e)
                {
                     int selectedRow = table.getSelectedRow();
                     System.out.println(getSelectedRowAsString(selectedRow));
                 System.out.println(selectedRow);
              }
           });

      // display table with ResultSet contents
      table = new JTable( rows, columnHeads );
      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 getSelectedRowAsString(int row) {
      StringBuffer sb = new StringBuffer();
   TableModel  model = table.getModel();
      for(int col = 0;col < model.getColumnCount();col++) {
           sb.append(model.getValueAt(row, col).toString());
           sb.append(","); // (or any other separator you want)
     }
     String result = sb.toString();
 return result.substring(0, result.length() - 1);
     }

Sincerely

Frank
0
CEHJCommented:
Change

>>
this.addMouseListener(new MouseAdapter(){
public void mouseClicked(MouseEvent e)
{
      int selectedRow = table.getSelectedRow();
      System.out.println(getSelectedRowAsString(selectedRow));
      System.out.println(selectedRow);
}
});

// display table with ResultSet contents
table = new JTable( rows, columnHeads );
>>

to

// display table with ResultSet contents
table = new JTable( rows, columnHeads );
table.addMouseListener(new MouseAdapter(){
      public void mouseClicked(MouseEvent e)
      {
            int selectedRow = table.getSelectedRow();
            System.out.println(getSelectedRowAsString(selectedRow));
      }
});




0
CEHJCommented:
Change

>>
this.addMouseListener(new MouseAdapter(){
public void mouseClicked(MouseEvent e)
{
      int selectedRow = table.getSelectedRow();
      System.out.println(getSelectedRowAsString(selectedRow));
      System.out.println(selectedRow);
}
});

// display table with ResultSet contents
table = new JTable( rows, columnHeads );
>>

to

// display table with ResultSet contents
table = new JTable( rows, columnHeads );
table.addMouseListener(new MouseAdapter(){
      public void mouseClicked(MouseEvent e)
      {
            int selectedRow = table.getSelectedRow();
            System.out.println(getSelectedRowAsString(selectedRow));
      }
});




0
Frank-22Author Commented:
Hi

Now it displays the row then I  click upon it.

My motivation for wanting to do the is that I would like able to delete/update the row and commit the changes to a mysql-db.

Therefore I have three questions:

First all if only I to move the value of column in a a selected row to a string: How do I do that ?

Secondly:

How do I delete a selected row from a JTable ?

The final question is about sql-statement:

Any idear on how I write a function with sql-statemtents that able to take an abitrary -input in the sql-statement such that if I e.g. click on a column named 'firstname' it executes an sql-statement like:

query = delete from contacts where "clickedColumn= firstname" like 'valueof(clickedColumn);

Secondly if the user wants to delete another row, but this time he clicks on the column named "lastname" and the value the clicked column is passed to the above sql-statement !

Is it possible to do this ?

Again thanks for all Your answers !

Sincerely
Frank
0
CEHJCommented:
Please post separate questions
0
Frank-22Author Commented:
Okay

Question:

First all if only I only want to move the value of column in a a selected row to a string: How do I do that ?

Sincerely

Frank
0
CEHJCommented:
I meant post *new* questions, but just this one, then:

>>
I only want to move the value of column in a a selected row to a string: How do I do that ?
>>

Call this function instead:

String getColumnAsString(int row, int col) {
      TableModel  model = table.getModel();
      return model.getValueAt(row, col).toString();
}
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
CEHJCommented:
...which can be called thus:

System.out.println(getColumnAsString(table.getSelectedRow(), table.getSelectedColumn()));
0
Frank-22Author Commented:
Okay thanks that works now:

Any idear on how I write a function with sql-statemtents that able to take an abitrary -input in the sql-statement such that if I e.g. click on a column named 'firstname' it executes an sql-statement like:

query = delete from contacts where "clickedColumn= firstname" like 'valueof(clickedColumn);

Secondly if the user wants to delete another row, but this time he clicks on the column named "lastname" and the value the clicked column is passed to the above sql-statement !

Is it possible to do this ?

Again thanks for all Your answers !

Sincerely
Frank
0
CEHJCommented:
>>I meant post *new* questions, but just this one, then
0
Frank-22Author Commented:
Okay sorry..


Can this done ?

a sql statement with an abitrary string input ?

Sincerely

Frank
0
Frank-22Author Commented:
Hi Again....

I come up with a possible sql-statement myself, but I get a couple of compiler-errors:

Addressbook.java:182: cannot resolve symbol
symbol  : variable selectedColumn
location: class Addressbook
    getColumnAsString(selectedRow, selectedColumn);
                                   ^
Addressbook.java:185: cannot resolve symbol
symbol  : variable selectColumn
location: class Addressbook
    String query = "delete from contacts where " + selectColumn + "like"  + valueAt(selectedColumn) + "'";
                                                   ^
Addressbook.java:185: cannot resolve symbol
symbol  : variable selectedColumn
location: class Addressbook
    String query = "delete from contacts where " + selectColumn + "like"  + valueAt(selectedColumn) + "'";
                                                                                    ^
3 errors

---code-----
private void deleteRecord() {
    Statement statement;
    getColumnAsString(selectedRow, selectedColumn);

try {
    String query = "delete from contacts where " + selectColumn + "like"  + valueAt(selectedColumn) + "'";
}
    catch ( SQLException sqlex ) {
      sqlex.printStackTrace();
}
}

Maybe You see what wrong ?

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.