home |contents |previous |next |seek  

 

 
 

 

 
     

9   Lesson4

Tools:

Casella di testo: IDE : Netbeans5.5 or Netbeans6.0 Beans: Swing VisualEditor: Matisse DB Server : MySql 5.0 Driver: com.mysql.jdbc.Driver Database: MyCustomers Tables: Town,Customer  

 

 

 

 

 

 

 

 

Target:

This lesson will build an application that manages data in tables Town and Customer  from the database MyCustomers with the technique of Master / Detail.
Master  table is Town, while Detail table is Customer.
Town and Customer tables are using the relational field  zip:

Also performing searches on the data according to FirstName field by means of a filter.
The application performs operations Edit, Cancellation and Inserting records in both tables.

 

 

New Project:

 

Add to JForm all others components from Palette Swing:


 

In the  file NewJFrame.java  add this import:

import com.sun.rowset.CachedRowSetImpl;
import com.sun.rowset.FilteredRowSetImpl;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.SQLException;
import javax.sql.RowSetEvent;
import javax.sql.RowSetListener;
import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.FilteredRowSet;
import javax.swing.SwingUtilities;
import javax.swing.UIManager;
import javax.swing.UnsupportedLookAndFeelException;

 

In the file NewJFrame.java   add at end  this  declarations:

private String url = "jdbc:mysql://localhost:3306/MyCustomers";
private Connection con;
public static FilteredRowSet frs;
public static CachedRowSet crs;
private NewTabella TabMia=new NewTabella();
private Filtro fil;

 

Set for  variable jScrollPane1  modifiers  public static :


 

In the  file NewJFrame.java    replacement constructor follow :

public NewJFrame() {
        initComponents();
}

 

with this constructor:

public NewJFrame() {
        initComponents();
        setDefaultLookAndFeelDecorated(true);
        try {
            UIManager.setLookAndFeel(UIManager.getSystemLookAndFeelClassName());            
            SwingUtilities.updateComponentTreeUI(this);
            this.pack();
        } catch (UnsupportedLookAndFeelException ex) {
            ex.printStackTrace();
        } catch (InstantiationException ex) {
            ex.printStackTrace();
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        } catch (IllegalAccessException ex) {
            ex.printStackTrace();
        }
        
        try {            
            Class.forName("com.mysql.jdbc.Driver");           
            con=DriverManager.getConnection(url, "root", "root");
            con.setAutoCommit(false);
            crs=new CachedRowSetImpl();                        
            crs.addRowSetListener(this);            
            crs.setCommand("SELECT * FROM town");
            crs.execute(con);  
            
            frs=new FilteredRowSetImpl();                        
            frs.addRowSetListener(TabMia);  
            aggiornaTabella();              
        }catch (SQLException ex) { ex.printStackTrace(); }
         catch (ClassNotFoundException ex) {ex.printStackTrace();}   
    }


 

 

Programming Beans:

 

 

1: Swing: JComboBox           NameVariable:  capCitta           Code for Event:  ActionPerformed
try {
            int selezione;
            if (capCitta.getItemCount()>0) { 
              if (capCitta.getSelectedIndex()!=-1) {
                 selezione=capCitta.getSelectedIndex()+1; 
                 crs.absolute(selezione);
                 numRecord.setText(Integer.toString(selezione)+"/"
                                                         +(jScrollBar1.getMaximum()-1));                                 
                 jScrollBar1.setValue(selezione);                                               
              } else jOptionPane1.showMessageDialog(null,"Errore:codice 
                                      inesistente","Errore",jOptionPane1.ERROR_MESSAGE);
            }
        } catch (SQLException ex) {ex.printStackTrace(); }

 

2: Swing: JTextField          NameVariable:  comune

 

3: Swing: JLabel                NameVariable:  numRecord

4: Swing: JScrollBar                 NameVariable:  jScrollBar1            Code for Event:  AdjustmentValueChanged

 
try {
            crs.absolute(evt.getValue()); 
            numRecord.setText(Integer.toString(evt.getValue())+"/"+(jScrollBar1.getMaximum()-1));
            aggiornaMaschera();
            aggiornaTabella();   
        } catch (SQLException ex) { ex.printStackTrace();  }

5: Swing: JTable        NameVariable:  jTable1

6: Swing: JButton           NameVariable:  Aggiungi          Code for Event:  ActionPerformed 

    try {         
            crs.setCommand("INSERT INTO Town(zip,town) values(?,?)");
            crs.setString(1,"99999");
            crs.setString(2,"town");            
            crs.execute(con);
            
            crs.setCommand("SELECT * FROM town");
            crs.execute(con);
            frs.setCommand("SELECT code,firstname,name,date,zip,balance FROM Customer");
            frs.execute(con);     
            crs.last();
            //aggiornaMaschera();
            jScrollBar1.setValue(crs.getRow());
        } catch (SQLException ex) { 
            jOptionPane1.showMessageDialog(null,”Error: operation failed!”,
                     ”Error”,jOptionPane1.ERROR_MESSAGE);
            ex.printStackTrace();  }


 

7: Swing: JButton           NameVariable:  Cancella         Code for Event:  ActionPerformed 

try {
            int risp=jOptionPane1.showConfirmDialog(this,"Attention delete customers!","Confirm",
                    jOptionPane1.YES_NO_OPTION,jOptionPane1.INFORMATION_MESSAGE);
            if (risp==jOptionPane1.YES_OPTION){
               crs.absolute(capCitta.getSelectedIndex()+1);                 
               crs.deleteRow();
               crs.acceptChanges(con);              
               
               //crs.rowSetPopulated(new RowSetEvent(crs),1);
              }
        } catch (SQLException ex) { ex.printStackTrace(); 
         jOptionPane1.showMessageDialog(null,"Error: operation failed!","Error",jOptionPane1.ERROR_MESSAGE);
        }

 

8: Swing: JButton           NameVariable:  Modifica         Code for Event:  ActionPerformed 

try { 
            /* ATTENTION: CHECK DATA for comune,capCitta  */
            int curRec=crs.getRow();
            crs.updateString("zip",(String)capCitta.getSelectedItem());
            crs.updateString("town",comune.getText());            
            crs.updateRow();             
            crs.acceptChanges(con);
            crs.absolute(curRec);
            //aggiornaMaschera();
            jScrollBar1.setValue(curRec);
        } catch (SQLException ex) { ex.printStackTrace();
           jOptionPane1.showMessageDialog(null,"Error: operation failed!","Error",jOptionPane1.ERROR_MESSAGE);
        }

 

9: Swing: JButton           NameVariable:  AnnullaFiltro        Enabled: False   Code for Event:  ActionPerformed 

  try {
              AnnullaFiltro.setEnabled(false);
              Cerca.setEnabled(true);
              fil=null;
              frs.setFilter(null);
              frs.execute(con);
        } catch (SQLException ex) { ex.printStackTrace(); }

 

10: Swing: JButton           NameVariable:  Cerca        Code for Event:  ActionPerformed 

   try {
            AnnullaFiltro.setEnabled(true);
            Cerca.setEnabled(false);
            String cognome=jOptionPane1.showInputDialog(this,"Input FirstName","Search",
                              jOptionPane1.QUESTION_MESSAGE);
            fil=new Filtro(cognome);
            frs.beforeFirst();
            frs.setFilter(fil);                  
                       
            frs.rowSetPopulated(new RowSetEvent(frs),1);  
        } catch (SQLException ex) {ex.printStackTrace();}

 

11: Swing: JButton           NameVariable:  ModificaUtenti        Code for Event:  ActionPerformed 

try {
            /* ATTENTION: CHECK DATA for code,firstname,name,date,zip,balance */
            frs.absolute(TabMia.getSelectedRow()+1);            
            frs.updateString("code",(String)TabMia.getValueAt(TabMia.getSelectedRow(),0));
            frs.updateString("firstname",(String)TabMia.getValueAt(TabMia.getSelectedRow(),1));
            frs.updateString("name",(String)TabMia.getValueAt(TabMia.getSelectedRow(),2));            
           Date d=Date.valueOf((TabMia.getValueAt(TabMia.getSelectedRow(),3).toString()));
            frs.updateDate("date",d);
            frs.updateString("zip",(String)TabMia.getValueAt(TabMia.getSelectedRow(),4));
            frs.updateFloat("balance",(Float)TabMia.getValueAt(TabMia.getSelectedRow(),5));
            frs.setFilter(null);
            frs.updateRow();             
            frs.acceptChanges(con);  
            if (fil!=null) {
                frs.setFilter(fil);
                frs.execute(con);
               }     
        } catch (SQLException ex) { ex.printStackTrace();
         jOptionPane1.showMessageDialog(null,"Error: operation failed!","Error",jOptionPane1.ERROR_MESSAGE);
        }

 
 

12: Swing: JButton           NameVariable:  CancellaUtenti        Code for Event:  ActionPerformed 

     try {
            frs.absolute(TabMia.getSelectedRow()+1);  
            frs.setFilter(null);
            frs.deleteRow();
            frs.acceptChanges(con);
            if (fil!=null) {
                frs.setFilter(fil);
                frs.execute(con);
            }   
           
            frs.rowSetPopulated(new RowSetEvent(frs),1);
        } catch (SQLException ex) { 
          jOptionPane1.showMessageDialog(null,”Error: operation failed!”,”Error”,jOptionPane1.ERROR_MESSAGE);
         ex.printStackTrace();  }

13: Swing: JButton           NameVariable:  AggiungiUtenti        Code for Event:  ActionPerformed 

   try {  
            frs.setCommand("INSERT INTO Customer(code,firstname,name,date,zip,balance) values('ZZZ99',' ',' ','2007-01-01',?,0)");
            frs.setString(1,(String)capCitta.getSelectedItem());
            frs.execute(con);
            
            frs.setCommand("SELECT code,firstname,name,date,zip,balance FROM Customer where zip=?");
            frs.setString(1,(String)capCitta.getSelectedItem());
            frs.execute(con);     
            
        } catch (SQLException ex) {ex.printStackTrace(); 
        jOptionPane1.showMessageDialog(null,"Error: operation failed!","Error",jOptionPane1.ERROR_MESSAGE);
        }

 

 

 

Insert in the file NewFrame.java below the main method the following code that implements the interface RowSetListener:

public void rowSetChanged(RowSetEvent event) {        
        crs.removeRowSetListener(this);
        capCitta.removeAllItems();
        try { crs.beforeFirst();           
              while (crs.next()){
               capCitta.addItem(crs.getString("zip"));              
              }                                             
            jScrollBar1.setValues(1,1,1,crs.size()+1);   
            jScrollBar1.setBlockIncrement(5);
            crs.first();
            aggiornaMaschera();             
        } catch (SQLException ex) {ex.printStackTrace(); }
        crs.addRowSetListener(this);
    }

    public void rowChanged(RowSetEvent event) {
    }

    public void cursorMoved(RowSetEvent event) {
    }

 

 

Insert in the file NewFrame.java below the main method the following code that implements the interface aggiornaMaschera()  e  aggiornaTabella() :

 

public void aggiornaMaschera(){
        try {
            if (crs.getRow()>0){
            capCitta.setSelectedItem(crs.getString("zip"));
            comune.setText(crs.getString("town"));    
            }        
        } catch (SQLException ex) { ex.printStackTrace();  }            
    } 


    
    public void aggiornaTabella(){ 
        try {                 
            if (frs!=null){
              if (capCitta.getItemCount()>0){
              frs.setCommand("SELECT code,firstname,name,date,zip,balance FROM Customer where (zip=?)");
              frs.setString(1,(String)capCitta.getSelectedItem());              
              frs.execute(con); 
	      }
            }
        } catch (SQLException ex) { ex.printStackTrace(); }        
    }


 

Inspector:

 

Add to project a new class java named Filtro:

 

 

 

In file Filtro.java add this import:

import java.sql.SQLException;
import javax.sql.RowSet;
import javax.sql.rowset.Predicate;

 

In file Filtro.java replacement this code:

public class Filtro{

    /** Creates a new instance of Filtro */
    public Filtro() {
        
    }
}

with this code:

public class Filtro implements Predicate{
    String firstnameSeek;
    
    /** Creates a new instance of Filtro */
    public Filtro(String cognome) {
        this.firstnameSeek=cognome;
    }

    public boolean evaluate(RowSet rs) {
        boolean valutata=true; 
        try { 
           if (rs.getRow()>0) valutata=firstnameSeek.equalsIgnoreCase(rs.getString("firstname"));            
           else valutata=false;
        } catch (SQLException ex) {ex.printStackTrace(); }          
        return valutata;  
    }

    public boolean evaluate(Object value, int column) throws SQLException {
        return true;
    }

    public boolean evaluate(Object value, String columnName) throws SQLException {
        return true;
    }
    
}

 

Add to project  class java named  NewTabella see in the Lesson1.

  

Compile the project. 

 

Thank you for attention!

 

Copyright©2008. All rights reserved