Solved

Problems with SQL and JTable in Swing

Posted on 2003-11-17
5
9,373 Views
Last Modified: 2013-11-23
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();

}
}
0
Comment
Question by:Frank-22
  • 3
  • 2
5 Comments
 
LVL 20

Expert Comment

by:Venabili
ID: 9766445
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
 

Author Comment

by:Frank-22
ID: 9766724
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
 
LVL 20

Expert Comment

by:Venabili
ID: 9766893
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
 

Author Comment

by:Frank-22
ID: 9766972
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
 
LVL 20

Accepted Solution

by:
Venabili earned 250 total points
ID: 9766984
It is setModal. The idea is the values to be taken after the dialog is closed. Give me the StackTrace of the exception?
0

Featured Post

How your wiki can always stay up-to-date

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

Join & Write a Comment

Suggested Solutions

Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

747 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

10 Experts available now in Live!

Get 1:1 Help Now