JDBC in pratica

6 Lesson1 (Access)

Tools:

Target:

In this lesson will build an application that loads data from the database MyCustomers the table Customer and displays them in a table managed by JTable component Swing.

Also performing searches on the data according to field FirstName and Zip by means of a filter.

Attention:
If you are looking for a complete JDBC application CRUD take a look at the following link

If you are looking for a complete JPA application CRUD take a look at the following link

New Project:

Add to project a component JFrame:

Add all other component from Palette Swing:

In file NewJFrame.java add this import:

import com.sun.rowset.FilteredRowSetImpl;
import com.sun.rowset.JdbcRowSetImpl;
import java.sql.*;
import javax.sql.RowSet;
import javax.sql.RowSetEvent;
import javax.sql.RowSetListener;
import javax.sql.rowset.FilteredRowSet;
import javax.sql.rowset.JdbcRowSet;
import javax.swing.SwingUtilities;
import javax.swing.UIManager;
import javax.swing.UnsupportedLookAndFeelException;
import javax.swing.table.DefaultTableModel;

In file NewJframe.java add at end this declarations:

private String url="jdbc:odbc:Driver={Microsoft Access Driver *.mdb)};DBQ=MieiClienti.mdb";
public static FilteredRowSet frs;
private Filtro fil;
private NewTabella TabCustomer=new NewTabella();
private Connection con;

Set for variable jScrollPane1 the modifiers public static :

In file NewJFrame.java replacement this constructor:

public NewJFrame() {
initComponents();
}

with this constructor:

public NewJFrame() {
        initComponents(); 
        Ricerca.setEnabled(false);
        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("sun.jdbc.odbc.JdbcOdbcDriver");
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        }
    }

Programming Beans:

1: Swing: JButton NameVariable: FilteredRowSetPopola Code for Event: ActionPerformed

FilteredRowSetPopola.setEnabled(false);
try {
            frs=new FilteredRowSetImpl();            
            frs.setUsername("");
            frs.setPassword("");
            frs.setUrl(url);	
            frs.addRowSetListener(TabCustomer);            
            frs.setCommand("SELECT code,firstname,name,date,zip,balance FROM Customer");
            frs.execute();               
        } catch (SQLException ex) {ex.printStackTrace();
          jOptionPane1.showMessageDialog(null,”Error: operation failed!”,
                  ”Error”,jOptionPane1.ERROR_MESSAGE);
        }

2: Swing: JButton NameVariable: Ricerca 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();}

3: Swing: JRadioButton NameVariable: ricercaOff Code for Event: ActionPerformed

    Ricerca.setEnabled(false);
       fil=null;
       try {
            if (!FilteredRowSetPopola.isEnabled()){
                         frs.setFilter(null);
                         frs.execute();
            } 
        } catch (SQLException ex) { ex.printStackTrace(); }

4: Swing: JRadioButton NameVariable: ricercaOn Code for Event: ActionPerformed

Ricerca.setEnabled(true);

5: Swing: JTextField NameVariable: cap

6: Swing: JTextField NameVariable: cognome

7: Swing: JTable NomeVariable: TabUtenti

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;
    String zipSeek;
    
    /** Creates a new instance of Filtro */
    public Filtro(String cognome,String cap) {
        this.firstnameSeek=cognome;
        this.zipSeek=cap;
    }

    public boolean evaluate(RowSet rs) {
        boolean valutata=true; 
        try { 
           if (rs.getRow()>0) {
               if (firstnameSeek.length()>0) valutata=firstnameSeek.equalsIgnoreCase(rs.getString("firstname"));             
               if (zipSeek.length()>0) valutata=zipSeek.equalsIgnoreCase(rs.getString("zip")) && valutata;  
           }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 a new class java named NewTabella:

In file NewTabella.java add this import:

import java.sql.SQLException;
import javax.sql.RowSetEvent;
import javax.sql.RowSetListener;
import javax.swing.*;
import javax.swing.event.TableModelEvent;
import javax.swing.event.TableModelListener;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableModel;

In file NewTabella.java replacement code:

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

  }

with this code:

public class NewTabella extends JTable implements RowSetListener{
   
    
    /**
     * Creates a new instance of NewTabella
     */
    public NewTabella() {        
    }
    
    public void rowSetChanged(RowSetEvent event) {         
                 Object[] record;
                 String [] intestazCol=new String [] {"CODE", "FIRST NAME", "LAST NAME", "DATE", "ZIP", "BALANCE"};
                 DefaultTableModel dtm=new DefaultTableModel(intestazCol,0){
                 Class[] types = new Class [] {
                  java.lang.String.class, java.lang.String.class, java.lang.String.class, 
                  java.lang.Object.class, java.lang.String.class, java.lang.Float.class};
                      public Class getColumnClass(int columnIndex) {
                        return types [columnIndex];
                     }             
                 };
                 try {    
                   NewJFrame.frs.beforeFirst();                   
                   while (NewJFrame.frs.next()){
                    record=new Object[]{NewJFrame.frs.getString("code"), NewJFrame.frs.getString("firstname"), 
                               NewJFrame.frs.getString("name"), NewJFrame.frs.getDate("date"),
                               NewJFrame.frs.getString("zip"),NewJFrame.frs.getFloat("balance")};
                    dtm.addRow(record);
                   }              
                 } catch (SQLException ex) {ex.printStackTrace();} 
                  
                  this.setModel(dtm);   
                  this.setShowHorizontalLines(false);
                  this.setShowVerticalLines(false);
                  this.setFont(new java.awt.Font("Trebuchet MS", 0, 13));
                  this.setForeground(java.awt.Color.gray);                
                  NewJFrame.jScrollPane1.setViewportView(this);  
                  System.out.println(event.toString());
    }

    public void rowChanged(RowSetEvent event) {
    }

    public void cursorMoved(RowSetEvent event) {
        
    }
    
}

Compile the project!

Attention:
If you are looking for a complete application CRUD take a look at the following link