Problems with SQL and JTable in Swing

Hi all,

I got a couple problems regarding a Addressbook-program, that I have written:

1) The add-function in the new JFrame:
       
1a) When I have added a record to the db via the new-frame gui its not possible for do a working search because if I try the program doesn't register the input I make in the main GUI !

What wrong ?

1b) How do I make a function that closes the new frame by hitting a JButton and not the main-gui ?


2) The Update-function:

I would like to be able to update columns in a row, first by double-clicking on the column then entering a      new value for this column and hitting the JButton Update.

2a) The sql-statement is wrong because I get an error then trying to use it, what do I need to change ?
2b) How do I make the JTable refresh automaticly after updating a column of record ?


3) The delete-function:

I'm able to delete a record from mysql-db first by clicking on one of its columns in the JTable and hitting the deleteButton which executes a sql-statement that inturn delete the selected from db.

But my problem here is: How do I get a record in the JTable to be removed also then clicking on the delete-button, so its no longer displayed in JTable after deletion ?

I "really" hope that there somebody out there who can help me solve these problems !!!!!

Sincerely Yours

Frank  

Here is my sourcecode:

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 String columnHead;
     private int selectedRow;
     private int selectedColumn;

  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");
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));
   
buttonsPane.add(newButton);
buttonsPane.add(deleteButton);
buttonsPane.add(updateButton);
buttonsPane.add(label);
buttonsPane.add(firstname);
buttonsPane.add(searchButton);

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

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

public void actionPerformed(ActionEvent e)
    {
        JButton src = (JButton)e.getSource();
        if (src == searchButton)
        {
            this.searchRecord();
        }
        else if (src == newButton)
        {
            this.launchAnotherFrame();
        }
        else if (src == addButton)
        {
         this.addRecord(); }
     else if (src == deleteButton) {
          this.deleteRecord(); }
     else if (src == updateButton) {
        this.updateRecord();
     }
    }

private void showTable()
{
    Statement statement;
    ResultSet resultSet;
    try {
     String query = "select *from contacts";
     statement = connection.createStatement();
     resultSet = statement.executeQuery( query );
     displayResultSet( resultSet );
     statement.close();
    }
    catch (SQLException sqlex ) {
     sqlex.printStackTrace();
    }
}

private void searchRecord()
{
    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() {
     int result;
     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();
     result = statement.executeUpdate( query );
     if (result == 1) {
         JOptionPane.showMessageDialog(this, "Record inserted successfully.");
     }
     emailadr.setText(null);
     firstname.setText(null);
     lastname.setText(null);
     alias.setText(null);
     statement.close();
     
    }
    catch ( SQLException sqlex ) {
     sqlex.printStackTrace();
    }
 }

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

 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 where ID =" + table.getValueAt(row, 0);
     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();
 }
}

 private void launchAnotherFrame()
    {
        JFrame frame = new JFrame("Add a new record");
       this.setContents(frame.getContentPane());
     frame.pack();
     frame.show();
     closeAnotherFrame();
    }
public void closeAnotherFrame (){
    //launchAnotherFrame();
    //frame.dispose();
    //setVisible(false);
}

private void setContents(Container con) {
   
    addButton = new JButton("add");    
     addButton.addActionListener(this);
       cancelButton = new JButton("cancel");
       cancelButton.addActionListener(this);
        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);
     buttonPanel.add(cancelButton);
       
        JPanel panel = new JPanel(new BorderLayout());
        panel.add(labelsAndInputs, BorderLayout.CENTER);
     //model = new DefaultTableModel();
     //table = new JTable( model );
     panel.add(buttonPanel, BorderLayout.SOUTH);
     

        con.add(panel);
     setDefaultCloseOperation(DISPOSE_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
     //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();
    }
}

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

}
}
Frank-22Asked:
Who is Participating?
 
VenabiliConnect With a Mentor Commented:
It is setModal. The idea is the values to be taken after the dialog is closed. Give me the StackTrace of the exception?
0
 
VenabiliCommented:
1a:)

Change:
private void launchAnotherFrame()
  {
   JDialog frame = new JDialog(this, "Add a new record");
  this.setContents(frame.getContentPane());
 frame.pack();
 frame.show();
 frame.setModal(true);
 //get the values from the fields here and add them in the database and the table. As the textfields are in the main class just make them GLobal variables so that you can take their values
  }

1b) just call frame.dispose();

2) Write a table CellRenderer/ TableCellEditor

2a) What is the error? Give the StakTrace

2B) You can not do it automatically. You should write a function that listen to the record and update the UI when needed

3. Remove the record from the Model of the table.



0
 
Frank-22Author Commented:
Thanks for Your answer...


1a)

I have inserted Your surgested code for private void launchAnotherFrame(), but the result is still the same.

When I have closed the add new record window after adding a record to the db I try to search for an existing record in the search field.
But the program doesn't register the input it make here:

It registeres the field af empty so the program runs an empty sql-statement like:
select* from contacts where firstname like ' '

Eventhough I type-in a value in the JTextField e.g. Bill.

It almost like the JTexField is blocked for input after have inputted an entire new record !

Here is my search-function:

private void searchRecord()
{
    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();

}
}

Anything wrong with it ?

1b)

I have written this function that is suppose to close the new Frame window by hitting the cancel-button, but nothing happens.

public void closeAnotherFrame (){
    launchAnotherFrame();
    frame.dispose();
    setVisible(false);
}

Whats wrong with it ?

2)
How do I do that from my existing tablemodel  private void displayResultSet( ResultSet rs )
?


2a)
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) ',' emailadr'' at line 1"

Here is the update - function:


private void updateRecord() {
    Statement statement;
    ResultSet rs;
    String cl = getColumnAsString(selectedRow, selectedColumn);
    String cn = getColumnhead(selectedColumn);
    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();
    }
}

It is a primitiv atempt to be able to update the column then the user has clicked on it !


2b)

How do I do that ?

Thanks again

Sincerely
Frank



0
 
VenabiliCommented:
1a: - you need to update it manually. I put a comment in the code I gave you. You should write it alone.

1b: if frame is the Window to be closed:
public void closeAnotherFrame (){
    frame.dispose();
    frame.setVisible(false);
}

2: Read this and try to write it alone:
http://java.sun.com/docs/books/tutorial/uiswing/components/table.html

2a: It is not correct SQL syntax:
String query = "update contacts set (emailadr, firstname, lastname, alias)" + cl + " ',' " + cn + "'";
Read here for the proper syntax;
http://www.1keydata.com/sql/sqlupdate.html
http://www.w3schools.com/sql/sql_update.asp

2b : You want when a column in the database is changed to change the TAble UI? No matter how you changed the column value?

If I can help further, ask :) But I'll not write the whoile code for you - it is against the rules. So just read the tutorials and try to fix the troubles. I'll be around if you have troubles.

Venabili


0
 
Frank-22Author Commented:
Okay fair enough.

But one quick question:

This code here:

private void launchAnotherFrame()
  {
   JDialog frame = new JDialog(this, "Add a new record");
  this.setContents(frame.getContentPane());
 frame.pack();
 frame.show();
 frame.setModal(true);
 //get the values from the fields here and add them in the database and the table. As the textfields are in the main class just make them GLobal variables so that you can take their values
  }

Then I exchange it for my own:

frame.setModal(true) gives me a boolean error !

and is setModal and not setModel ?

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

All Courses

From novice to tech pro — start learning today.