Stand Alone JPA client using Oracle 11g Database HR schema

In this sample we will create a stand alone java app which uses the JPA (Toplink) api to connect to the HR sample schema in a Oracle database.

The hr schema must be unlocked and database must be accessible via the network.

We will start with the persistence.xml file.

/home/broersa/work/CountryApp/CountryJPA/build/META-INF/persistence.xml

<persistence xmlns="http://java.sun.com/xml/ns/persistence" version="1.0">
<persistence-unit name="default">
    <provider>
        oracle.toplink.essentials.PersistenceProvider
    </provider>
    <exclude-unlisted-classes>false</exclude-unlisted-classes>
    <properties>
        <property name="toplink.logging.level" value="INFO"/>
        <property name="toplink.jdbc.driver" value="oracle.jdbc.OracleDriver"/>
        <property name="toplink.jdbc.url" value="jdbc:oracle:thin:centos.bekijkhet.com:l521:orcl"/>
        <property name="toplink.jdbc.password" value="hr"/>
        <property name="toplink.jdbc.user" value="hr"/>
    </properties>
</persistence-unit>
</persistence>

After this the entities which represent the database objects. I choose the regions and countries tables.

/home/broersa/work/CountryApp/CountryJPA/src/com/bekijkhet/entity/Region.java

package com.bekijkhet.entity;

import java.io.Serializable;
import javax.persistence.*;
import static javax.persistence.CascadeType.*;

import java.util.List;
import java.util.ArrayList;

@Entity
@Table(name="REGIONS")
public class Region implements Serializable {
        private int id;
        private String name;
        private List<Country> countries = new ArrayList<Country>();;

        @Id
        @Column(name="REGION_ID")
        public int getId() {
                return id;
        }

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

        @Column(name="REGION_NAME")
        public String getName() {
                return name;
        }

        public void setName(String name) {
                this.name = name;
        }

        @OneToMany(cascade={CascadeType.ALL}, mappedBy="region")
        public List<Country> getCountries() {
                return countries;
        }

        public void setCountries(List<Country> newValue) {
                this.countries = newValue;
        }
}

/home/broersa/work/CountryApp/CountryJPA/src/com/bekijkhet/entity/Country.java

package com.bekijkhet.entity;

import java.io.Serializable;
import javax.persistence.*;

@Entity
@Table(name="COUNTRIES")
public class Country implements Serializable {
        private String id;
        private String name;
        private Region region;

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

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

        @Column(name="COUNTRY_NAME")
        public String getName() {
                return name;
        }

        public void setName(String name) {
                this.name = name;
        }

        @ManyToOne()
        @JoinColumn(name="REGION_ID",nullable=false)
        public Region getRegion() {
                return region;
        }

        public void setRegion(Region region) {
                this.region = region;
        }
}

 Compile the entities:

cd /home/broersa/work/CountryApp/CountryJPA

javac -cp /home/broersa/oc4j_client_11110_preview/j2ee/home/lib/persistence.jar:build -d build src/com/bekijkhet/entity/*.java

After this the client app:

/home/broersa/work/CountryApp/CountryJPA/src/com/bekijkhet/client/Client.java:

package com.bekijkhet.client;

import javax.persistence.EntityManager;
import javax.persistence.Persistence;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Query;

import java.util.List;

import com.bekijkhet.entity.*;

public class Client {
  private static EntityManagerFactory emf;
  private static EntityManager em;

  public static void main(String[] args) {

    emf = Persistence.createEntityManagerFactory("default");
    em = emf.createEntityManager();

    // Find a country with region
    Query q1 = em.createQuery("select c from Country c where c.id = :id");
    q1.setParameter("id","NL");
    Country c1 = (Country) q1.getSingleResult();
    System.out.println(c1.getName() + " - " + c1.getRegion().getName());

    // Find a region with countries
    Query q2 = em.createQuery("select r from Region r where r.id = :id");
    q2.setParameter("id",1);
    Region r2 = (Region) q2.getSingleResult();
    System.out.println(r2.getName());
    List<Country> l2 = r2.getCountries();
    for (Country c2 : l2) {
      System.out.println("  " + c2.getName());
    }

    // Add a region with two countries
    em.getTransaction().begin();
    Region r3 = new Region();
    r3.setId(1001);
    r3.setName("MyRegion1");
    Country c31 = new Country();
    c31.setId("31");
    c31.setName("MyCountry31");
    c31.setRegion(r3);
    r3.getCountries().add(c31);
    Country c32 = new Country();
    c32.setId("32");
    c32.setName("MyCountry32");
    c32.setRegion(r3);
    r3.getCountries().add(c32);

    em.persist(r3);
    em.getTransaction().commit();

    // List the added region with countries
    Query q4 = em.createQuery("select r from Region r where r.id = :id");
    q4.setParameter("id",1001);
    Region r4 = (Region) q4.getSingleResult();
    System.out.println(r4.getName());
    List<Country> l4 = r4.getCountries();
    for (Country c4 : l4) {
      System.out.println("  " + c4.getName());
    }

    // Change the country names
    em.getTransaction().begin();
    Query q5 = em.createQuery("select r from Region r where r.id = :id");
    q5.setParameter("id",1001);
    Region r5 = (Region) q5.getSingleResult();
    List<Country> l5 = r5.getCountries();
    for (Country c5 : l5) {
      c5.setName(c5.getName()+"-changed");
    }
    em.getTransaction().commit();

    // List the added region with countries
    Query q6 = em.createQuery("select r from Region r where r.id = :id");
    q6.setParameter("id",1001);
    Region r6 = (Region) q6.getSingleResult();
    System.out.println(r6.getName());
    List<Country> l6 = r6.getCountries();
    for (Country c6 : l6) {
      System.out.println("  " + c6.getName());
    }

    // Remove samples
    // Remove a single detail
    em.getTransaction().begin();
    Query q7 = em.createQuery("select r from Region r where r.id = :id");
    q7.setParameter("id",1001);
    Region r7 = (Region) q7.getSingleResult();
    System.out.println(r7.getName());
    List<Country> l7 = r7.getCountries();
    em.remove(l7.get(1));
    em.getTransaction().commit();

    // Remove samples
    // Remove the whole region
    em.getTransaction().begin();
    Query q8 = em.createQuery("select r from Region r where r.id = :id");
    q8.setParameter("id",1001);
    Region r8 = (Region) q8.getSingleResult();
    System.out.println(r8.getName());
    em.remove(r8);
    em.getTransaction().commit();

    em.close();

  }
}

compile and run:

cd /home/broersa/work/CountryApp/CountryJPA

javac -cp /home/broersa/oc4j_client_11110_preview/j2ee/home/lib/persistence.jar:build -d build src/com/bekijkhet/client/*.java

java -cp /home/broersa/oc4j_client_11110_preview/j2ee/home/lib/persistence.jar:/home/broersa/toplink_11.1.1.0_071214_preview-3/lib/java/api/toplink.jar:/home/broersa/myclasses/ojdbc6.jar:build -javaagent:/home/broersa/toplink_11.1.1.0_071214_preview-3/lib/java/internal/toplink-essentials-agent.jar com.bekijkhet.client.Client

[TopLink Info]: 2008.01.28 08:09:38.718–ServerSession(9519074)–TopLink, version: Oracle TopLink Essentials – 2.0 (Build SNAPSHOT (06/04/2007))
[TopLink Info]: 2008.01.28 08:09:41.744–ServerSession(9519074)–file:/home/broersa/work/CountryApp/CountryJPA/build/-default login successful
Netherlands – Europe
Europe
Belgium
Switzerland
Germany
Denmark
France
Italy
Netherlands
United Kingdom
MyRegion1
MyCountry31
MyCountry32
MyRegion1
MyCountry31-changed
MyCountry32-changed
MyRegion1
MyRegion1