ConnectingToMsAccessDB

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.x (download)
  • Microsoft Access database
  • Jdk1.6.x

}}center UPDATE 2009/10/29 }}


}}center Expected duration: 30 minutes }} [[{TableOfContentsTitle=TableOfContents} | {TableOfContents title='Table of Contents'}]]


Building Database


ATTENTION: Description MyCustomers.mdb database with SQL language

File:ConnectingToMsAccessDB/References ConnectingToMsAccessDB.PNG

   

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,
  `cdate` date default NULL,
  `zip` varchar(5) default NULL,
  `balance` float default NULL,
  PRIMARY KEY  (`code`),
  KEY `Icap` (`zip`)
) ;



INSERT INTO `customer` (`code`, `firstname`, `name`, `cdate`, `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) File:ConnectingToMsAccessDB/Img1 ConnectingToMsAccessDB.PNG

2. Click Next to continue File:ConnectingToMsAccessDB/Img2 ConnectingToMsAccessDB.PNG


Building GUI


1. Add to project a component JFrame: File:ConnectingToMsAccessDB/Img3 ConnectingToMsAccessDB.PNG

          a. Choose a name for component, default is NewJFrame
          File:ConnectingToMsAccessDB/Img4 ConnectingToMsAccessDB.PNG

2. Add to JForm all others components from Palette Swing: File:ConnectingToMsAccessDB/Img5 ConnectingToMsAccessDB.PNG

3. Drag and Drop Swing beans to assemble the form below: File:ConnectingToMsAccessDB/Img6 ConnectingToMsAccessDB.PNG


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;
import java.util.logging.Level;       /* Update 29-10-2009  */
import java.util.logging.Logger;      /* Update 29-10-2009  */


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();
 /* Update 29-10-2009  */
private Connection con;
public Statement stmt;
public ResultSet rs;
/* Update 29-10-2009  */


6. Set for variable jScrollPane1 the modifiers public static : File:ConnectingToMsAccessDB/Img7 ConnectingToMsAccessDB.PNG

7. In file NewJFrame.java replacement this constructor:

public NewJFrame() {
        initComponents();
}

with this constructor:

public NewJFrame() {        
        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();
        }
        initComponents(); 
        Find.setEnabled(false);
        /* Update 29-10-2009  */
        try {
               con=DriverManager.getConnection(url, "", "");                    
               stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); 
            } catch (SQLException ex) {
                Logger.getLogger(NewJFrame.class.getName()).log(Level.SEVERE, null, ex);}
        /* Update 29-10-2009  */
    }


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 {
            /* Update 29-10-2009  */
            rs = stmt.executeQuery ("SELECT * FROM Customers");    
            frs=new FilteredRowSetImpl();                         
            frs.addRowSetListener(TabCustomer); 
            frs.populate(rs);    
            /* Update 29-10-2009  */                                                                       
        } 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 
    /* Update 29-10-2009  */
int row=TabCustomer.getSelectedRow();
int col=0;
try {
     /* ATTENTION: CHECK DATA for code,firstname,name,cdate,zip,balance  */
frs.absolute(TabCustomer.getSelectedRow()+1);
String oldCode=frs.getString("code");
String sqlUpd="UPDATE Customers SET code='"+ (String)TabCustomer.getValueAt(TabCustomer.getSelectedRow(),0);
     sqlUpd+="' ,firstName='"+(String)TabCustomer.getValueAt(TabCustomer.getSelectedRow(),1);
     sqlUpd+="',name='" +(String)TabCustomer.getValueAt(TabCustomer.getSelectedRow(),2);
     sqlUpd+="',cdate='"+(TabCustomer.getValueAt(TabCustomer.getSelectedRow(),3).toString());
     sqlUpd+="',zip='"+(String)TabCustomer.getValueAt(TabCustomer.getSelectedRow(),4);
     sqlUpd+="',balance="+((Float)TabCustomer.getValueAt(TabCustomer.getSelectedRow(),5)).toString();
     sqlUpd+=" WHERE code='"+oldCode+"'";
     stmt.executeUpdate(sqlUpd);
           
     rs = stmt.executeQuery ("SELECT * FROM Customers");   
     frs.populate(rs, 1);   
 
      /* Update 29-10-2009  */
} 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 {  
        /* Update 29-10-2009  */  
       stmt.executeUpdate("INSERT INTO Customers(code,firstName,name,cdate,zip,balance) 
                                     values('AAA99','firstname','name',2007-01-01,'12340',0)");      
      /* Update 29-10-2009  */                              
   } catch (SQLException ex) {ex.printStackTrace();}


§ 10: Swing: JButton NameVariable: Delete Enabled: False

      Code for Event: ActionPerformed
     /* Update 29-10-2009  */
     int row=TabCustomer.getSelectedRow();
     int col=0;
     String sqlDel="DELETE FROM Customers WHERE code='"+TabCustomer.getValueAt(row, col)+"'"  ;
     try {
            stmt.executeUpdate(sqlDel);
            rs = stmt.executeQuery ("SELECT * FROM Customers");    
            frs.populate(rs, 1);  
        } catch (SQLException ex) {
            ex.printStackTrace();
        }      
     /* Update 29-10-2009  */ 


§ 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 {
            /* Update 29-10-2009  */

            rs = stmt.executeQuery ("SELECT * FROM Customers");    
            frs.populate(rs, 1);    
   
            /* Update 29-10-2009  */    
        } catch (SQLException ex) {ex.printStackTrace();}  

Implement a class Filter


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

File:ConnectingToMsAccessDB/Img8 ConnectingToMsAccessDB.PNG

File:ConnectingToMsAccessDB/Img9 ConnectingToMsAccessDB.PNG


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 :

File:ConnectingToMsAccessDB/Img10 ConnectingToMsAccessDB.PNG

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.netsons.org
Not logged in. Log in, Register

By use of this website, you agree to the NetBeans Policies and Terms of Use. © 2012, Oracle Corporation and/or its affiliates. Sponsored by Oracle logo