---

Persistence, Binding, Annotations: an example with NetBeans6.0 and Access.

by Angelo Cristella

NetBeans6.x is ideal IDE for quickly and easily build an application JPA based: I show you how do!
In this document we will build a sample DB desktop application with master/detail technical using a desktop database Microsoft Access. We implement it using Java Persistence API of the Java platform. The Java Persistence API is a POJO persistence API for object/relational mapping. It contains a full object/relational mapping specification supporting the use of Java language metadata annotations and/or XML descriptors to define the mapping between Java objects and a relational database. In pratice Java Persistence API facilitates your use of POJOs as entity beans and how to use it in Java SE desktop applications that require object persistence. The application performs CRUD operations. 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 Swings in NetBeans IDE.

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

• NetBeans IDE 6.x (download)
• JDK 6 or more
• 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 NULL,
  `lastname` 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`, `lastname`, `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 "CustomerAccessDB" a ODBC user data source

---

1.Create from operating system WindowsXP:

Create a new project

---

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

2. Click Next to continue

Add "TopLink library" to project

---

1. TopLink library is the implementation of JPA included in the GlassFish project
Mouse right-click to project jpaAccess:

a. Choose tag Libraries and Compile:

b. Choose tag Libraries and Run:

Building GUI

---

1. Add to project a component JFrame:

a. Choose a name for component, default is NewJFrame

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

3. In file NewJFrame.java add this import:

import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.SwingUtilities;
import javax.swing.UIManager;
import javax.swing.UnsupportedLookAndFeelException;
import javax.swing.table.DefaultTableModel;
import java.sql.*;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import javax.persistence.*;

4. In file NewJFrame.java replacement this constructor:

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();
        }        
   }

Implement Entity class

---

1. Add to project the new Entity class java named Town :
(Entity class represent a table DB Town and every istance is a record)

2. In file Town.java replacement ALL code with this customized @annotations code:

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package myPack;

import java.io.Serializable;
import java.util.Collection;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;

/**
 *
 * @author Angelo
 */
@Entity
@Table(name="Town")
public class Town implements Serializable {
    private static final long serialVersionUID = 1L;
    private String zip;
    private String city;
    private Collection<Customers> cust;

    public void setzip(String zip) {
        this.zip = zip;
    }

    @Id   
    @Column(name="Zip")
    public String getzip() {
        return zip;
    }

    public void setcity(String city) {
        this.city = city;
    }
    
    @Column(name="Town")
    public String getcity() {
        return city;
    }
    
    public void setcust(Collection<Customers> cust) {
        this.cust = cust;
    }
    
    @OneToMany(cascade=javax.persistence.CascadeType.ALL,mappedBy = "city")
    public Collection<Customers> getcust() {
        return cust;
    }
    
    @Override
    public int hashCode() {
        int hash = 0;
        hash += (zip != null ? zip.hashCode() : 0);
        return hash;
    }

    @Override
    public boolean equals(Object object) {
        // TODO: Warning - this method won't work in the case the id fields are not set
        if (!(object instanceof Town)) {
            return false;
        }
        Town other = (Town) object;
        if ((this.zip == null && other.zip != null) || (this.zip != null && !this.zip.equals(other.zip))) {
            return false;
        }
        return true;
    }

    @Override
    public String toString() {
        return "myPack.Town[id=" + zip + "]";
    }
}

3. Add to project the new Entity class java named Customers :
(Entity class represent a table DB Customers and every istance is a record)

4. In file Customers.java replacement ALL code with this customized @annotations code:

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package myPack;

import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;
import java.util.Date;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

/**
 *
 * @author Angelo
 */
@Entity
@Table(name="Customers")
public class Customers implements Serializable {
    private static final long serialVersionUID = 1L; 
    private String id;
    private String firstName;
    private String lastName;
    private Date  cdate;
    private Town city;
    private Float  balance;

    public void setId(String id) {
        this.id = id;
    }

    @Id
    @Column(name="Code")
    public String getId() {
        return id;
    }

    public void setfirstName(String firstName){
        this.firstName=firstName;
    }
    
    @Column(name="FirstName")
    public String getfirstName(){
      return firstName; 
    }
    
    public void setlastName(String lastName){
         this.lastName=lastName;
    }
    
    @Column(name="Name")
    public String getlastName(){
      return  lastName; 
    }
    
    public void setcdate(Date cdate){
        this.cdate=cdate;
    }
    
    @Column(name="CDate")
    @Temporal(TemporalType.DATE)
    public Date getcdate(){
      return cdate;
    }
    
    public void setcity(Town city){
        this.city=city;
    }
    
    @ManyToOne()
    @JoinColumn(name="Zip",referencedColumnName = "Zip")
    public Town getcity(){
      return city;
    }
    
    public void setbalance(Float balance){
        this.balance=balance;
    }
    
    @Column(name="Balance")
    public Float getbalance(){
      return  balance;  
    }
    
    @Override
    public int hashCode() {
        int hash = 0;
        hash += (id != null ? id.hashCode() : 0);
        return hash;
    }

    @Override
    public boolean equals(Object object) {
        // TODO: Warning - this method won't work in the case the id fields are not set
        if (!(object instanceof Customers)) {
            return false;
        }
        Customers other = (Customers) object;
        if ((this.id == null && other.id != null) || (this.id != null && !this.id.equals(other.id))) {
            return false;
        }
        return true;
    }

    @Override
    public String toString() {
        return "myPack.Customers[id=" + id + "]";
    }
}

Implement a Persistence Unit

---

1. Add to project the new PersistenceUnit named DBAccessPU :

Add "Java Persistence" beans

---

1. Add to project bean EntityManager from palette JavaPersistence :

Please note before proceding:

These data binding components are NOT visual, so it is not possible to locate them as icons on frame's surface but they are accessible from the Project Inspector or Navigator.

The properties of just created objects may not be displayed after the creation, so you have to open their Property pane by hands:

Open Window->Navigation->Inspector).

From within Inspector: select "Form NewJFrame"->"Other Components"->"entityManager1 etc.."->"Right Click"->"Properties"

These properties are read only, that means the IDE won't let you edit them in source pane.

Note: you may also use Ctrl+Shift+7 shortcut to Open Properties. (Naturally, the needed component has to be selected in design mode).

2. From the palette JavaPersistence add a bean named "Query" :

3. From the palette JavaPersistence add a bean named QueryResult :

Bind beans tables with persistence

---

1. Change name jTable1 in TabTowns

2. Binding TabTowns bean with persistance table Town (list1 is QueryResult!)

3. Make no editable field Zip

4. Change name jTable2 in TabCustomer

5. Binding TabCustomer bean with persistance table Customers

6. Make no editable field Id

More important components

---

1. Add to project bean OptionPane from palette SwingWindows:
Drag&Drop the bean on to blank area NOT on JForm!

Programming beans

---

§ 1: Swing: JTable NameVariable: TabTowns

§ 2: Swing: JTable NameVariable: TabCustomer

§ 3: Swing: JButton NameVariable: UpdateTown Code for Event: ActionPerformed

    
        /* Update all date */

        entityManager1.getTransaction().begin();
        entityManager1.getTransaction().commit();        

§ 4: Swing: JButton NameVariable: DeleteTown Code for Event: ActionPerformed

  
        /* Delete all rows selected */
        int[] selected = TabTowns.getSelectedRows();
        if (selected.length==0) return;
        List<myPack.Town> toRemove = new ArrayList<myPack.Town>(selected.length);
        entityManager1.getTransaction().begin();
        for (int idx=0; idx<selected.length; idx++) {
            myPack.Town t = list1.get(TabTowns.convertRowIndexToModel(selected[idx]));
            toRemove.add(t);
            entityManager1.remove(t);
        }
        entityManager1.getTransaction().commit(); 
        list1.removeAll(toRemove);
        
        if (list1.size()==0) return;
        TabTowns.setRowSelectionInterval(0,0);
        TabTowns.scrollRectToVisible(TabTowns.getCellRect(0, 0, true));              

§ 5: Swing: JButton NameVariable: NewTown Code for Event: ActionPerformed

  
        /* Insert a new entity Town */
        myPack.Town t = new myPack.Town();
        String newZip=jOptionPane1.showInputDialog(this,"Input  
                                   zip","NewRecord",jOptionPane1.QUESTION_MESSAGE);
        if ((newZip==null)?false:(newZip.length()>0)){ 
          t.setzip(newZip);        
          entityManager1.getTransaction().begin();
          entityManager1.persist(t);
          entityManager1.getTransaction().commit(); 
          list1.add(t);
          int row = list1.size()-1;
          
          TabTowns.setRowSelectionInterval(row, row);
          TabTowns.scrollRectToVisible(TabTowns.getCellRect(row, 0, true));
        }else jOptionPane1.showMessageDialog(this,"Zip cannot a null
                                       value!","Error",jOptionPane1.ERROR_MESSAGE);  

§ 6: Swing: JButton NameVariable: NewCustomer Code for Event: ActionPerformed

  
        /* Insert a new entity Customer */
        int index = TabTowns.getSelectedRow();
        if (index==-1) return;
        myPack.Town t = list1.get(TabTowns.convertRowIndexToModel(index));
        Collection<myPack.Customers> cs = t.getcust();       
        myPack.Customers c = new myPack.Customers();
        String newCode=jOptionPane1.showInputDialog(this,"Input Code","NewRecord",jOptionPane1.QUESTION_MESSAGE);        
       if ((newCode==null)?false:(newCode.length()>0)){      
          c.setId(newCode);          
          c.setcity(t);  
          c.setbalance(0.0f);
          c.setcdate(Date.valueOf("2008-01-01"));          
          entityManager1.getTransaction().begin();
          entityManager1.persist(c);
          entityManager1.getTransaction().commit();  
          cs.add(c);
          TabTowns.clearSelection();
          TabTowns.setRowSelectionInterval(index, index);
          int row = cs.size()-1;
          TabCustomer.setRowSelectionInterval(row, row);
          TabCustomer.scrollRectToVisible(TabCustomer.getCellRect(row, 0, true));          
        }else jOptionPane1.showMessageDialog(this,"Code cannot a null  value!","Error",jOptionPane1.ERROR_MESSAGE);        

§ 7: Swing: JButton NameVariable: DeleteCustomer Code for Event: ActionPerformed

 
        /* Delete all rows selected */
        int selectedTown=TabTowns.getSelectedRow();  
        if (selectedTown==-1) return;

        myPack.Town t=list1.get(TabTowns.convertRowIndexToModel(selectedTown));
        Collection<myPack.Customers> cst= t.getcust();
        int[] selected =  TabCustomer.getSelectedRows();
        if (cst.size()==0) return;
        List<myPack.Customers> toRemove = new ArrayList<myPack.Customers>(selected.length);
        entityManager1.getTransaction().begin();
        for (int idx=0; idx<selected.length; idx++) {
            selected[idx] = TabCustomer.convertRowIndexToModel(selected[idx]);
            int count = 0;
            Iterator<myPack.Customers> iter = cst.iterator();
            while (count++ < selected[idx]) iter.next();
            myPack.Customers u = iter.next();
            toRemove.add(u);
            entityManager1.remove(u);
        }
        entityManager1.getTransaction().commit(); 
        cst.removeAll(toRemove);
        TabTowns.clearSelection();
        TabTowns.setRowSelectionInterval(selectedTown, selectedTown);    

§ 8: Swing: JButton NameVariable: UpdateCustomer Code for Event: ActionPerformed

  
        /* ATTENTION: CHECK DATA for code,firstname,name,date,zip,balance  */
        /* Update all date */
                
        entityManager1.getTransaction().begin();
        entityManager1.getTransaction().commit();                
                

Compile the project!

Thank you for attention.
jdbc.altervista.org

Attachments