JDBC in pratica

8 Lesson3

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: Customers

Target:

This lesson will build an application that loads data from table Customer of the database MyCustomers and displays them in a custom mask as Data Entry.
Also performing searches on the data according to field Firstname and Zip by means of a filter. It is also managed the field photos type Blob.
The application performs operations Edit, Cancellation and Inserting records.

New Project:

Casella di testo: Build a new project named Lezione3
-------------------------------------------------
Take a look at Lesson1

Add to JForm all others components from Palette Swing:

In the file NewJFrame.java add this import:

import com.sun.rowset.FilteredRowSetImpl;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.RowSetEvent;
import javax.sql.RowSetListener;
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?emulateLocators=true";
public static FilteredRowSet frs;
private Filtro fil;
private Connection con;

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);
            frs=new FilteredRowSetImpl();                        
            frs.addRowSetListener(this);            
            frs.setCommand("SELECT code,firstname,name,date,zip,balance FROM Customer");
            frs.execute(con);               
        }catch (SQLException ex) { ex.printStackTrace(); }
         catch (ClassNotFoundException ex) {ex.printStackTrace();}        
    }

Programming Beans:

1: Swing: JComboBox           NameVariable:  codice            Code for Event:  ActionPerformed

try {
            int selezione;
            if (codice.getItemCount()>0) { 
              if (codice.getSelectedIndex()!=-1) {
                 selezione=codice.getSelectedIndex()+1; 
                 frs.absolute(selezione);
                    numRecord.setText(Integer.toString(selezione)+"/"+(jScrollBar1.getMaximum()-1));
                 //aggiornaMaschera();
                 jScrollBar1.setValue(selezione);
              } else jOptionPane1.showMessageDialog(null,"Error:code not in archive","Error",jOptionPane1.ERROR_MESSAGE);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

2: Swing: JTextField          NameVariable:  cognome             



3: Swing: JTextField          NameVariable:  nome





4: Swing: JTextField          NameVariable:  data





5: Swing: JTextField          NameVariable:  cap





6: Swing: JTextField          NameVariable:  saldo

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

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

8: Swing: JLabel NameVariable: numRecord

9: Swing: JButton NameVariable: Foto Code for Event: ActionPerformed

       ResultSet rs;        
      try {   
            /* Begin insert photo into DB */
            FileInputStream fis=null;  
            String img=jOptionPane1.showInputDialog(this,"Input file","Image",jOptionPane1.QUESTION_MESSAGE);
            fis=new FileInputStream(img);                        
            PreparedStatement pStmt = con.prepareStatement("UPDATE Customer SET photo=? WHERE code=?");            
            pStmt.setBlob(1,fis);
            pStmt.setString(2,(String)codice.getSelectedItem());
            pStmt.executeUpdate();
            con.commit();
            /* End insert photo  */
            foto.setIcon(new javax.swing.ImageIcon(img));
          } catch (SQLException ex) {ex.printStackTrace();
          } catch (FileNotFoundException ex) {ex.printStackTrace();}

10: Swing: JRadioButton NameVariable: cercaOff Code for Event: ActionPerformed

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

11: Swing: JRadioButton NameVariable: cercaOn Code for Event: ActionPerformed

Cerca.setEnabled(true);

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

try {
            fil=new Filtro(cognome.getText(),cap.getText());
            frs.beforeFirst();
            frs.setFilter(fil);                     
                 
            frs.rowSetPopulated(new RowSetEvent(frs),1);  
        } catch (SQLException ex) {ex.printStackTrace();}

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

try {
            frs.absolute(codice.getSelectedIndex()+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) {
            ex.printStackTrace();
            jOptionPane1.showMessageDialog(null,"Error: operation failed!","Error",jOptionPane1.ERROR_MESSAGE);
        }

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

try { 
            /* ATTENTION: CHECK DATA for code,firstname,name,date,zip,balance  */
            int curRec=frs.getRow();
            frs.updateString("code",(String)codice.getSelectedItem());
            frs.updateString("firstname",cognome.getText());
            frs.updateString("name",nome.getText());            
            Date d=Date.valueOf(data.getText());
            frs.updateDate("date",d);
            frs.updateString("zip",cap.getText());
            frs.updateFloat("balance",new Float(saldo.getText()));
            frs.setFilter(null);
            frs.updateRow();             
            frs.acceptChanges(con);
            
            if (fil!=null) {
                frs.setFilter(fil);
                frs.execute(con);
            }            
            
            jScrollBar1.setValue(curRec);
        } catch (SQLException ex) {ex.printStackTrace();
        jOptionPane1.showMessageDialog(null,"Error: operation failed!","Error",jOptionPane1.ERROR_MESSAGE);
        }

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

try {                     
            frs.setCommand("INSERT INTO Customer(code,firstname,name,date,zip,balance) values(?,?,?,?,?,?)");
            frs.setString(1,"ZZZ99");
            frs.setString(2,"cognome");
            frs.setString(3,"nome");
            Date d=Date.valueOf("2007-01-01");
            frs.setDate(4,d);
            frs.setString(5,"12340");
            frs.setFloat(6,0.0f);
            frs.execute(con);
            
            frs.setCommand("SELECT code,firstname,name,date,zip,balance FROM Customer");
            frs.execute(con);     
            frs.last();
            //aggiornaMaschera();	
            jScrollBar1.setValue(frs.getRow());
        } catch (SQLException ex) {
            ex.printStackTrace();
           jOptionPane1.showMessageDialog(null,”Error: operation failed!”,”Error”,jOptionPane1.ERROR_MESSAGE);
        }

16: Swing: JLabel NameVariable: foto

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

public void rowSetChanged(RowSetEvent event) {
        int num=1;
        frs.removeRowSetListener(this);
        codice.removeAllItems();
        try { frs.beforeFirst();           
              while (frs.next()){
               codice.addItem(frs.getString("code"));
               num++;
              }                                           
            jScrollBar1.setValues(1,1,1,num);   
            jScrollBar1.setBlockIncrement(5);
            frs.first();
            aggiornaMaschera();                          
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        frs.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():

public void aggiornaMaschera(){
        ResultSet rs;        
        try {
            if (frs.getRow()>0){
            codice.setSelectedItem(frs.getString("code"));
            cognome.setText(frs.getString("firstname"));
            nome.setText(frs.getString("name"));
            data.setText(frs.getDate("date").toString());
            cap.setText(frs.getString("zip"));
            saldo.setText(frs.getString("balance").toString()); 
            /* View photo */
            PreparedStatement pStmt = con.prepareStatement("SELECT code,'photo' AS fotoImg FROM Customer WHERE code=?");
            pStmt.setString(1,frs.getString("code"));
            rs=pStmt.executeQuery();                                                            
            rs.next();
            Blob blb=rs.getBlob(2);            
            if (blb.length()>0) {
               System.out.println(blb.length());
               foto.setIcon(new javax.swing.ImageIcon(blb.getBytes(1L,(int)blb.length())));  
            } else foto.setIcon(null);
          }
        } catch (SQLException ex) { ex.printStackTrace(); }            
    }

Add to project the class java named Filtro used in the Lesson1.

Compile the project.

Thank you for attention!