Super fast JPA with MySQL Cluster and with no JDBC or SQL

With Oracle / Sun MySQL Cluster 7.1 is it now possible to use JPA and without a JDBC driver and without any SQL conversion, this will give your java application or web application a great performance boot. With the 7.1 version you can use the ClusterJPA and ClusterJ libraries instead of the MySQL JDBC Driver. And the best thing, you still can use the JDBC driver or mysql utility. ( Best of both worlds )

With ClusterJPA is a query in this already fast memory cluster two times faster and an insert, update or delete at least three times faster. And the ClusterJPA library is cluster aware so no need for a Multi Datasource in Weblogic.
Ocklin's Blog and Andrew Morgan’s MySQL Cluster Database Blog already made some great articles about OpenJPA and MySQL Cluster 7.1 In my blog I go a little further by making a more complex example and deploy it in an EJB Session Bean on a Weblogic 10.3.2 ( WLS FMW 11g ) .server

I started with installing on  two machines Oracle Enterprise Linux version 5.5 ( Oracle edelivery ). Download in my case all the 32 bits Red Hat RPM's of the MySQL Cluster Community Edition. Install these packages on both servers and configure the cluster. it took me 30 minutes. I love this cluster , fast and easy.
ClusterJPA only supports for now, the Apache OpenJPA persistence.Oracle is working on other implementations like eclipselink / hibernate. You need to download the latest 1.2 release of OpenJPA ( I just 1.2.2 ) Version 2.0 is not working yet. Download the latest MySQL Connector/J jar and the ClusterJPA / ClusterJ jars from one of your Linux servers ( located in /usr/share/mysql/java/ ). And the last part is optional when your Weblogic server is also running on one of these linux servers. I am using JDeveloper 11g on my windows laptop so I also need to download the mysql cluster edition for Windows ( Windows edition is new ). I need the ndbclient.dll from the mysql lib folder and put this in one of my path folders.

open mysql  and create a clusterdb database: create database clusterdb;
create a test user on both mysql nodes: grant all on clusterdb.* to test@'%' identified by 'test';

I use JDeveloper 11g as my IDE so I first need to create a new java Application and add the following libraries to your project.

Next step is to create a persistence.xml which must be located in the META-INF folder. I add two persistence units one for the java application and one which uses the Weblogic JTA.

<persistence xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="1.0">
 <persistence-unit name="clusterdb" transaction-type="RESOURCE_LOCAL">
  <provider>org.apache.openjpa.persistence.PersistenceProviderImpl</provider>
  <class>nl.whitehorses.openjpa.mysql.cluster.entities.Employee</class>
  <class>nl.whitehorses.openjpa.mysql.cluster.entities.Department</class>
  <properties>
   <property name="openjpa.jdbc.SynchronizeMappings" value="buildSchema" />
   <property name="openjpa.ConnectionDriverName" value="com.mysql.jdbc.Driver" />
   <property name="openjpa.ConnectionURL" value="jdbc:mysql://10.10.10.50:3306/clusterdb" />
   <property name="openjpa.ConnectionUserName" value="test" />
   <property name="openjpa.ConnectionPassword" value="test" />
   <property name="openjpa.BrokerFactory" value="com.mysql.clusterj.openjpa.NdbOpenJPABrokerFactory" />
   <property name="openjpa.jdbc.DBDictionary" value="TableType=ndbcluster" />
   <property name="openjpa.ndb.connectString" value="10.10.10.50:1186" />
   <property name="openjpa.ndb.database" value="clusterdb" />
  </properties>
 </persistence-unit>
<persistence-unit name="clusterdbJTA" transaction-type="JTA"  >
  <provider>org.apache.openjpa.persistence.PersistenceProviderImpl</provider>
  <class>nl.whitehorses.openjpa.mysql.cluster.entities.Employee</class>
  <class>nl.whitehorses.openjpa.mysql.cluster.entities.Department</class>
  <properties>
   <property name="openjpa.jdbc.SynchronizeMappings" value="buildSchema" />
   <property name="openjpa.ConnectionDriverName" value="com.mysql.jdbc.Driver" />
   <property name="openjpa.ConnectionURL" value="jdbc:mysql://10.10.10.50:3306/clusterdb" />
   <property name="openjpa.ConnectionUserName" value="test" />
   <property name="openjpa.ConnectionPassword" value="test" />
   <property name="openjpa.BrokerFactory" value="com.mysql.clusterj.openjpa.NdbOpenJPABrokerFactory" />
   <property name="openjpa.jdbc.DBDictionary" value="TableType=ndbcluster" />
   <property name="openjpa.ndb.connectString" value="10.10.10.50:1186" />
   <property name="openjpa.ndb.database" value="clusterdb" />
  </properties>
 </persistence-unit>
</persistence>

The openjpa.ndb.connectString property need to have the management server url value.

Now you can create the example Entities: Department and Employee. You dont need to create these tables with mysql. ClusterJPA will do this for you.
the Department entity

package nl.whitehorses.openjpa.mysql.cluster.entities;

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

@NamedQueries({
   @NamedQuery(name = "Departments.findAll", query = "select o from department o")
,  @NamedQuery(name = "Departments.findByKey", query = "select o from department o where o.Id = :dept ")

})@Entity(name = "department")
public class Department implements Serializable {

    private int version;
    private int Id;
    private String Site;

    List<Employee> employees;


    public Department() {
    }


    @OneToMany(targetEntity = Employee.class, cascade = CascadeType.ALL,
               mappedBy = "department")
    public List<Employee> getEmployees() {
        return employees;
    }

    public void setEmployees(List<Employee> employees) {
        this.employees = employees;
    }


    @Id
    public int getId() {
        return Id;
    }

    public void setId(int id) {
        Id = id;
    }

    @Column(name = "location")
    public String getSite() {
        return Site;
    }

    public void setSite(String site) {
        Site = site;
    }

    @Version
    @Column(name = "version_field")
    // not required
    public int getVersion() {
        return version;
    }

    public void setVersion(int version) {
        this.version = version;
    }


    public String toString() {
        return "Department: " + getId() + " based in " + getSite();
    }
}

the Employee entity

package nl.whitehorses.openjpa.mysql.cluster.entities;

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

@Entity(name = "employee") //Name of the table
public class Employee implements Serializable {
    private int version;
    private int Id;
    private String First;
    private String Last;
    private String City;
    private String Started;
    private String Ended;
    protected  Department department;


    public Employee() {
    }

    @ManyToOne
    @JoinColumn(name="department", nullable=false)
    public Department getDepartment()
    {
        return department;
    }

    public void setDepartment(Department department)
    {
        this.department = department;
    }


    @Id
    public int getId() {
        return Id;
    }

    public void setId(int id) {
        Id = id;
    }

    public String getFirst() {
        return First;
    }

    public void setFirst(String first) {
        First = first;
    }

    public String getLast() {
        return Last;
    }

    public void setLast(String last) {
        Last = last;
    }

    @Column(name = "municipality")
    public String getCity() {
        return City;
    }

    public void setCity(String city) {
        City = city;
    }

    public String getStarted() {
        return Started;
    }

    public void setStarted(String date) {
        Started = date;
    }

    public String getEnded() {
        return Ended;
    }

    public void setEnded(String date) {
        Ended = date;
    }

    @Version
    @Column(name = "version_field")
    // not required
    public int getVersion() {
        return version;
    }

    public void setVersion(int version) {
        this.version = version;
    }



    public String toString() {
        return getFirst() + " " + getLast() + " (Dept " + getDepartment() +
            ") from " + getCity() + " started on " + getStarted() +
            " & left on " + getEnded();
    }
}

Now you can add a test class so you can test this. This will create the tables and add a department with an employee. Make sure you add the ndbclient library to your java path.

package nl.whitehorses.openjpa.mysql.test;

import java.util.List;

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

import nl.whitehorses.openjpa.mysql.cluster.entities.Department;
import nl.whitehorses.openjpa.mysql.cluster.entities.Employee;

public class Main {

    public static void main(String[] args) throws java.io.IOException {

        EntityManagerFactory entityManagerFactory =
            Persistence.createEntityManagerFactory("clusterdb");
        EntityManager em = entityManagerFactory.createEntityManager();
        EntityTransaction userTransaction = em.getTransaction();

        userTransaction.begin();

        Department sales = em.find(Department.class, 10);
        if ( sales == null) {
            System.out.println("Create sales department");
            sales = new Department();
            sales.setId(10);
            sales.setSite("Amsterdam");
            sales.setEmployees(null);
            em.persist(sales);
        } else {
            System.out.println("Found sales department");
        }
        userTransaction.commit();

        userTransaction.begin();
        Employee edwin = em.find(Employee.class, 1);
        if ( edwin == null) {
            System.out.println("Create employee edwin");
            edwin = new Employee();
            edwin.setId(1);
            edwin.setDepartment(sales);
            edwin.setFirst("Edwin");
            edwin.setLast("Biemond");
            em.persist(edwin);
        } else {
            System.out.println("Found employee edwin");
        }
        userTransaction.commit();



        Query q = em.createQuery("select x from department x where x.id=10");
        for (Department dep : (List<Department>)q.getResultList()) {
            System.out.println(dep.toString());
            for (Employee emp : dep.getEmployees()) {
                System.out.println(emp.toString());
            }
         }

        em.close();
        entityManagerFactory.close();
    }
}

The next step is to make an EJB Session Bean with a remote interface where we do the same as the java test client.

package nl.whitehorses.openjpa.mysql.cluster.session;

import java.util.List;

import javax.ejb.Remote;
import javax.ejb.Stateless;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import nl.whitehorses.openjpa.mysql.cluster.entities.Department;

@Stateless(name = "HrSessionEJB", mappedName = "OpenJPACluster-model-HrSessionEJB")
@Remote
public class HrSessionEJBBean implements HrSessionEJB {
    public HrSessionEJBBean() {
    }
    @PersistenceContext(unitName="clusterdbJTA")

    private EntityManager em;


    public Object mergeEntity(Object entity) {
        return em.merge(entity);
    }

    public Object persistEntity(Object entity) {
        em.persist(entity);
        return entity;
    }

    public List<Department> getDepartmentsFindAll() {
        return em.createNamedQuery("Departments.findAll").getResultList();
    }

    public Department getDepartmentFindByKey(int dept) {
        return (Department)em.createNamedQuery("Departments.findByKey").setParameter("dept", dept).getSingleResult();
    }

}

Make a EJB deployment profile and an application deployment profile (EAR) where you also include the OpenJPA and MySQL jars.

With Weblogic 10.3 and higher, Oracle replaced the default JPA provider with Eclipselink. So when you deploy this to a Weblogic 10.3 server, this will not work with Apache OpenJPA. So you need to add an weblogic deployment descriptor ( weblogic-application.xml). With this you can control the class loading.

<?xml version = '1.0' encoding = 'windows-1252'?>
<weblogic-application xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                      xsi:schemaLocation="http://www.bea.com/ns/weblogic/weblogic-application http://www.bea.com/ns/weblogic/weblogic-application/1.0/weblogic-application.xsd"
                      xmlns="http://www.bea.com/ns/weblogic/weblogic-application">
  <prefer-application-packages>
    <package-name>com.mysql.*</package-name>
    <package-name>org.apache.*</package-name>
  </prefer-application-packages>
</weblogic-application>

Before you can test it you need to add the ndbclient.dll to a weblogic path. ( wlserver_10.3\server\native\win\32 )
and the part is the EJB Session bean client.

package nl.whitehorses.openjpa.mysql.cluster;

import java.util.Hashtable;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;

import javax.naming.NamingException;

import nl.whitehorses.openjpa.mysql.cluster.entities.Department;
import nl.whitehorses.openjpa.mysql.cluster.session.HrSessionEJB;

public class HrSessionEJBClient {

    private static Context getInitialContext() throws NamingException {
        Hashtable env = new Hashtable();
        // WebLogic Server 10.x connection details
        env.put( Context.INITIAL_CONTEXT_FACTORY, "weblogic.jndi.WLInitialContextFactory" );
        env.put(Context.PROVIDER_URL, "t3://localhost:7101");
        return new InitialContext( env );
    }

    public static void main(String [] args) {
        try {
            final Context context =  getInitialContext();

            HrSessionEJB hRSessionEJB = (HrSessionEJB)
                context.lookup("OpenJPACluster-model-HrSessionEJB#nl.whitehorses.openjpa.mysql.cluster.session.HrSessionEJB");
            for (Department departments : (List<Department>)hRSessionEJB.getDepartmentsFindAll()) {
                System.out.println( "department = " + departments.getId());
                System.out.println( "location = " + departments.getSite());
                System.out.println( "employeesList = " + departments.getEmployees() );
            }

        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

}

Thats all, here you can download my JDeveloper 11g Workspace.