JDBC in pratica

5 Lesson1

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:

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.

New Project:

Add to project a component JFrame:

Add to Jform all others components from Palette Swing:

Casella di testo: Add to project MySql driver

In Netbeans5.5

In Netbeans6.0

In the 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 the file NewJFrame.java add at end this declarations:

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

Set for variable jScrollPane1 this modifiers public static :

In the file NewJFrame.java replacement constructor follow :

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("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        }
    }

Programming Beans:

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

FilteredRowSetPopola.setEnabled(false);
try {
            con=DriverManager.getConnection(url, "root", "root");
            con.setAutoCommit(false);
            frs=new FilteredRowSetImpl();

            frs.addRowSetListener(TabMia);
           frs.setCommand("SELECT code,firstname,name,date,zip,balance FROM Customer ");
            frs.execute(con);
        } catch (SQLException ex) {
             ex.printStackTrace();
             jOptionPane1.showMessageDialog(null,"Error: operation failed!",
              "Error",jOptionPane1.ERROR_MESSAGE);
        }

2: Swing: JButton VariableName: Ricerca Code for Event: ActionPerformed

try {
    /* ATTENTION: CHECK DATA cognome.getText(),cap.getText() */
            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 VariableName: ricercaOff Code for Event: ActionPerformed

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

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

Ricerca.setEnabled(true);

5: Swing: JTextField NameVariable: cap

6: Swing: JTextField NameVariable: cognome

7: Swing: JTable Nome: TabUtenti

Inspector:

Add to project the new class java named Filtro:

In the file Filtro.java add this import:

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

In file Filtro.java replacement 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) {
    }

}

5 Lesson1

Tools:

In the file NewJFrame.java add this import:

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

In the file NewJFrame.java replacement constructor follow :

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

Ricerca.setEnabled(true);

Add to project the new class java named Filtro:

In the file Filtro.java add this import:

In file Filtro.java replacement code:

with this code:

Add to project a new class java named NewTabella:

In file NewTabella.java add this import:

In file NewTabella.java replacement code:

with this code:

Compile the project.