Solved

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

Posted on 2003-11-15
26
1,573 Views
Last Modified: 2013-11-23
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

 
0
Comment
Question by:Frank-22
  • 13
  • 13
26 Comments
 

Author Comment

by:Frank-22
Comment Utility
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
 

Author Comment

by:Frank-22
Comment Utility
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
 
LVL 86

Expert Comment

by:CEHJ
Comment Utility
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
 

Author Comment

by:Frank-22
Comment Utility
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
 
LVL 86

Expert Comment

by:CEHJ
Comment Utility
No - place the function after the one called 'getNextRow'
0
 

Author Comment

by:Frank-22
Comment Utility
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
 
LVL 86

Expert Comment

by:CEHJ
Comment Utility
>>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
 

Author Comment

by:Frank-22
Comment Utility
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
 
LVL 86

Expert Comment

by:CEHJ
Comment Utility
import javax.swing.table.TableModel
0
 
LVL 86

Expert Comment

by:CEHJ
Comment Utility
(with ending colon obviously)
0
 

Author Comment

by:Frank-22
Comment Utility
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
 
LVL 86

Expert Comment

by:CEHJ
Comment Utility
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
 

Author Comment

by:Frank-22
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 86

Expert Comment

by:CEHJ
Comment Utility


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

0
 

Author Comment

by:Frank-22
Comment Utility
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
 
LVL 86

Expert Comment

by:CEHJ
Comment Utility
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
 
LVL 86

Expert Comment

by:CEHJ
Comment Utility
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
 

Author Comment

by:Frank-22
Comment Utility
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
 
LVL 86

Expert Comment

by:CEHJ
Comment Utility
Please post separate questions
0
 

Author Comment

by:Frank-22
Comment Utility
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
 
LVL 86

Accepted Solution

by:
CEHJ earned 200 total points
Comment Utility
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
 
LVL 86

Expert Comment

by:CEHJ
Comment Utility
...which can be called thus:

System.out.println(getColumnAsString(table.getSelectedRow(), table.getSelectedColumn()));
0
 

Author Comment

by:Frank-22
Comment Utility
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
 
LVL 86

Expert Comment

by:CEHJ
Comment Utility
>>I meant post *new* questions, but just this one, then
0
 

Author Comment

by:Frank-22
Comment Utility
Okay sorry..


Can this done ?

a sql statement with an abitrary string input ?

Sincerely

Frank
0
 

Author Comment

by:Frank-22
Comment Utility
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Programmer's Notepad is, one of the best free text editing tools available, simply because the developers appear to have second-guessed every weird problem or issue a programmer is likely to run into. One of these problems is selecting and deletiā€¦
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.

728 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

9 Experts available now in Live!

Get 1:1 Help Now