---

Connecting to Microsoft Access database.

by Angelo Cristella

In this document will build an application that loads data from the database and displays them in a table managed by JTable component Swing. Also performing searches on the data by means of a filter. The application performs operations Edit, Cancellation and Inserting records. This tutorial is designed for beginners with a basic understanding of database management and application development who want to apply their knowledge to working with Microsoft Access to build desktop application with GUI in NetBeans IDE.

In order to work through this tutorial, you need to have the following software installed on your computer:

• NetBeans IDE 5.5 or NetBeans 6.0 (download)
• Microsoft Access database

Building Database

---

ATTENTION: Description MyCustomers.mdb database with SQL language

Table structure `town`

CREATE TABLE `town` (
  `zip` varchar(5) NOT NULL,
  `town` varchar(25) default NULL,
  PRIMARY KEY  (`zip`)
) ;


INSERT INTO `town` (`zip`, `town`) VALUES 
('12340', 'Milano'),
('12341', 'Roma'),
('12342', 'Napoli'),
('12343', 'Bari'),
('12344', 'Bologna');

Table structure `Customer`

CREATE TABLE `customer` (
  `code` varchar(5) NOT NULL,
  `firstname` varchar(50) default N\ULL,
  `name` varchar(50) default NULL,
  `date` date default NULL,
  `zip` varchar(5) default NULL,
  `balance` float default NULL,
  PRIMARY KEY  (`code`),
  KEY `Icap` (`zip`)
) ;



INSERT INTO `customer` (`code`, `firstname`, `name`, `date`, `zip`, `balance`) VALUES 
('AAA00', 'Rossi', 'Paolo', '2007-11-12', '12340', 123.5),
('AAA01', 'Bianchi', 'Mariaja', '2007-01-01', '12341', 321),
('AAA02', 'Bianchi', 'Laura', '2007-01-01', '12342', 321),
('AAA03', 'Galli', 'Egidio', '2007-01-10', '12343', 0),
('AAA04', 'Rossi', 'Andrea', '2007-05-05', '12344', 568),
('AAA05', 'McJoy', 'Bill', '2007-04-01', '12340', 145),
('AAA06', 'Skorpy', 'John', '2007-01-21', '12340', 0),
('AAA07', 'Verdi', 'Mario', '2007-01-01', '12344', 125),
('AAA08', 'Mullen', 'Jane', '2007-02-03', '12342', 45),
('AAA09', 'Viola', 'Paola', '2007-04-16', '12341', 574);

Constraints

 
ALTER TABLE `customer`
  ADD CONSTRAINT FOREIGN KEY (`zip`) REFERENCES `town` (`zip`) ON DELETE CASCADE ON UPDATE CASCADE;

Create a new project

---

1. From File menu choose New Project (Ctrl+Maiusc+N)

2. Click Next to continue

Building GUI

---

1. Add to project a component JFrame:

a. Choose a name for component, default is NewJFrame

2. Add to JForm all others components from Palette Swing:

3. Drag and Drop Swing beans to assemble the form below:

4. 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;

5. In file NewJframe.java add at end this declarations:

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

6. Set for variable jScrollPane1 the modifiers public static :

7. In file NewJFrame.java replacement this constructor:

public NewJFrame() {
        initComponents();
}

with this constructor:

public NewJFrame() {
        initComponents(); 
        Find.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);
       Update.setEnabled(true);
       Find.setEnabled(true);
       Delete.setEnabled(true);
       Add.setEnabled(true);
       try {
            frs=new FilteredRowSetImpl();             
            frs.setUsername("");
            frs.setPassword("");
            frs.setUrl(url);
            frs.addRowSetListener(TabCustomer);            
            frs.setCommand("select * from Customers");
            frs.execute();                
        } catch (SQLException ex) {ex.printStackTrace();}   

§ 2: Swing: JButton NameVariable: Find Code for Event: ActionPerformed

try {
            fil=new Filter(firstname.getText(),zip.getText());
            frs.beforeFirst();
            frs.setFilter(fil);              
            frs.rowSetPopulated(new RowSetEvent(frs),1);  
     } catch (SQLException ex) {ex.printStackTrace();}    
  

§ 5: Swing: JTextField NameVariable: zip

§ 6: Swing: JTextField NameVariable: firstname

§ 7: Swing: JTable Nome: TabCustInit

§ 8: Swing: JButton NameVariable: Update Enabled: False Code for Event: ActionPerformed

try {
     /* ATTENTION: CHECK DATA for code,firstname,name,date,zip,balance  */
frs.absolute(TabCustomer.getSelectedRow()+1);                              
frs.updateString("code",(String)TabCustomer.getValueAt(TabCustomer.getSelectedRow(),0));            
frs.updateString("firstName",(String)TabCustomer.getValueAt(TabCustomer.getSelectedRow(),1));
frs.updateString("name",(String)TabCustomer.getValueAt(TabCustomer.getSelectedRow(),2));            
Date d=Date.valueOf((TabCustomer.getValueAt(TabCustomer.getSelectedRow(),3).toString()));
frs.updateDate("cdate",d);
frs.updateString("zip",(String)TabCustomer.getValueAt(TabCustomer.getSelectedRow(),4));
frs.updateFloat("balance",(Float)TabCustomer.getValueAt(TabCustomer.getSelectedRow(),5));
frs.updateRow();
frs.acceptChanges();
} catch (SQLException ex) {ex.printStackTrace(); }      

§ 9: Swing: JButton NameVariable: Add Enabled: False Code for Event: ActionPerformed

        Add.setEnabled(false);
        Edit.setEnabled(true);
        Update.setEnabled(false);
        Find.setEnabled(false);
        Delete.setEnabled(false);
   try {                      
       frs.setCommand("INSERT INTO Customers(code,firstName,name,cdate,zip,balance) 
               values('AAA99','firstname','name',2007-01-01,'12340',0)");
       frs.execute();
       frs.acceptChanges();           
   } catch (SQLException ex) {ex.printStackTrace();}

§ 10: Swing: JButton NameVariable: Delete Enabled: False Code for Event: ActionPerformed

     try {
            frs.absolute(TabCustomer.getSelectedRow()+1);            
            frs.deleteRow();
            frs.acceptChanges();            
            frs.rowSetPopulated(new RowSetEvent(frs),1);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }      

§ 11: Swing: JButton NameVariable: Edit Enabled: False Code for Event: ActionPerformed

        Add.setEnabled(true);
        Edit.setEnabled(false);
        Update.setEnabled(true);
        Find.setEnabled(true);
        Delete.setEnabled(true);
        try {
            frs=new FilteredRowSetImpl();            
            frs.setUsername("");
            frs.setPassword("");
            frs.setUrl(url);
            frs.addRowSetListener(TabCustomer);            
            frs.setCommand("select * from Customers");
            frs.execute();                
        } catch (SQLException ex) {ex.printStackTrace();}  

Implement a class Filter

---

1. Add to project the new class java named Filter :

2. In the file Filter.java add this import:

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

3. In file Filter.java replacement code:

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

with this code:

public class Filter implements Predicate{
    String firstnameSeek;
    String zipSeek;
    
    /** Creates a new instance of Filter */
    public Filter(String firstname,String zip) {
        this.firstnameSeek= firstname;
        this.zipSeek=zip;
    }

    public boolean evaluate(RowSet rs) {
        boolean validate=true; 
    try { 
     if (rs.getRow()>0) {
       if (firstnameSeek.length()>0)  validate=firstnameSeek.equalsIgnoreCase(rs.getString("firstname"));             
       if (zipSeek.length()>0) validate=zipSeek.equalsIgnoreCase(rs.getString("zip")) && validate;  
     }else validate =false;
    } catch (SQLException ex) {ex.printStackTrace(); }             
     return validate;
    }
   
    public boolean evaluate(Object value, int column) throws SQLException {
        
        return true;
    }

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

Customizer a new class jTable

---

1. Add to project a new class java named NewTabella :

2. 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;

3. 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", "FirstName", "LastName", 
                      "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("cdate"),NewJFrame.frs.getString("zip"),
                     NewJFrame.frs.getFloat("balance")};
                    dtm.addRow(record);
                   }              
                 } catch (SQLException ex) {ex.printStackTrace();} 
                  
                  this.setModel(dtm);   
                  NewJFrame.jScrollPane1.setViewportView(this);  
                  
    }

    public void rowChanged(RowSetEvent event) {
    }

    public void cursorMoved(RowSetEvent event) {
        
    }
    
}

Compile the project!

Thank you for attention.
jdbc.altervista.org

Attachments