NoSQL Zone is brought to you in partnership with:

Istvan Szegedi is an IT Technical Architect at Vodafone UK. He has been working at Hewlett-Packard, Nokia Networks, Google, Morgan Stanley and Vodafone. He holds certificates such as Sun Certified System Administrator, Sun Certified Java Programmer, Sun Certified Web Component Developer, Salesforce.com Certified Force.com Developer, TOGAF Certified Enterprise Architect. As a big fan of mobile and cloud computing, he likes to believe that these technologies will eventually push aside the desktop/client-server architecture Istvan is a DZone MVB and is not an employee of DZone and has posted 38 posts at DZone. You can read more from them at their website. View Full User Profile

NuoDB and Object-Relational Mapping – Hibernate Support in NuoDB

05.13.2013
| 4672 views |
  • submit to reddit

Curator's Note: This is the second in a series of three articles that explore NuoDB.  You can learn more about NuoDB, as well as access a free trial, by heading over to the NuoDB website.

Introduction

In a previous article, we introduced NuoDB as a completely new approach to relational databases that supports scalability as well as 100% SQL and ACID properties. At the end of the article, we showed an example Java program using JDBC to connect a NuoDB database.

The advantages of using JDBC are clean and simple SQL processing and good performance. However, when it comes to object-oriented languages like Java, there is a mismatch between the relational database representation and the object model. In RDBMS, we traditionally use rows and columns to represent data, while in object-oriented languages we have encapsulated data fields/attributes accessed via getters and setters. There is a mismatch with regards to identity (primary key versus objects equals() method), association (foreign keys versus object references), and inheritance (no inheritance in RDBMS as opposed to object-oriented languages where it is a common concept). That is where Object-Relational Mapping (ORM) comes to the rescue - it is a programming concept to convert data between RDBMS and object-oriented languages. There are several frameworks in Java that support persistent data.  One of the most popular frameworks is Hibernate, which was started in 2001 as an alternative to J2E entity beans and became a de facto leader among persistent Java solutions.

NuoDB’s Hibernate support

Hibernate has a dialect class. An abstract class called org.hibernate.dialect.Dialect represents a dialect of SQL implemented by a particular RDBMS. There are many subclasses of this abstract class, such as MySQLDialect, PostgreSQLDialect, OracleDialect, SQLServerDialect, DB2Dialect, SybaseDialect, TeradataDialect, etc, that are used to implement Hibernate compatibility for relational databases.

NuoDB has its own Hibernate dialect class called NuoDBDialect. It is part of the nuodb-hibernate-1.0.jar file.  If a program wants to use Hibernate with NuoDB then it needs to have this .jar file in the classpath. NuoDB’s development edition comes with a sample Hibernate code from the popular book Java Persistence with Hibernate. The complete example can be found here:  http://jpwh.org.

In order to run the example, we need to have Maven installed. To make it clear: NuoDB itself does not require Maven; neither the NuoDB server components  (transaction engines or storage managers), nor the NuoDB agent, nor the NuoDB database manager. It is solely needed to compile and run this Hibernate example.

Below is an example of how to try out NuoDB Hibernate on an Ubuntu 12.04 LTS system. Please, note that the NuoDB installation file (nuodb-1.0.1.linux.x64.tar.gz)  was installed under the user's home directory by simply using the following commands:

 $ cd
 $ tar xvzf nuodb-1.0.1.linux.x64.tar.gz
 $ ln -s nuodb-1.0.1.128.linux.x86_64 nuodb                                                                                                                                               
 $ ls -l nuodb
lrwxrwxrwx 1 notroot notroot 29 Mar 21 23:25 nuodb -> nuodb-1.0.1.128.linux.x86_64/
$ sudo apt-get install maven
$ cd ~/nuodb/samples/hibernate
# Install nuodbjdbc.jar and nuodb-hibernate-1.0.jar files into ~/.m2/repository/com/nuodb directory (local maven repository)
$ mvn install:install-file -DgroupId=com.nuodb -DartifactId=nuodb-jdbc -Dversion=1.0 -Dpackaging=jar -Dfile=/opt/nuodb/jar/nuodbjdbc.jar
$ mvn install:install-file -DgroupId=com.nuodb -DartifactId=nuodb-hibernate -Dversion=1.0 -Dpackaging=jar -Dfile=/opt/nuodb/jar/nuodb-hibernate-1.0.jar 
$ ls ~/.m2/repository/com/nuodb
nuodb-hibernate  nuodb-jdbc

# Compile the code
$ mvn compile

# Execute the java program
$ mvn exec:java
...
...
Found 2 user records:
User (3/0), Username: fred, Name: Fred Flintstone, admin
  home: Street: '301 Cobblestone Way', Zipcode: '00001', City: 'Bedrock'
  bill: Street: '301 Cobblestone Way', Zipcode: '00001', City: 'Bedrock'
  ship: Street: '1 Slate Drive', Zipcode: '00002', City: 'Granitetown'
User (4/0), Username: barney, Name: Barney Rubble, member

  home: Street: '303 Cobblestone Way', Zipcode: '00001', City: 'Bedrock'
  bill: Street: '303 Cobblestone Way', Zipcode: '00001', City: 'Bedrock'
  ship: Street: '1 Slate Drive', Zipcode: '00002', City: 'Granitetown'
Found 2 user records:
Found 2 user records:
User (3/1), Username: fred, Name: FRED FLINTSTONE, member
  home: Street: '301 Cobblestone Way', Zipcode: '00001', City: 'Bedrock'
  bill: Street: '301 COBBLESTONE WAY', Zipcode: '00001', City: 'BEDROCK'
  ship: Street: '1 Slate Drive', Zipcode: '00002', City: 'Granitetown'
User (4/1), Username: barney, Name: BARNEY RUBBLE, member

  home: Street: '303 Cobblestone Way', Zipcode: '00001', City: 'Bedrock'
  bill: Street: '303 COBBLESTONE WAY', Zipcode: '00001', City: 'BEDROCK'
  ship: Street: '1 Slate Drive', Zipcode: '00002', City: 'Granitetown'
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------

The main class for the NuoDB Hibernate sample code is SampleMain.java. It creates two Users and prints out the records. Then it capitalizes the user name and the billing address and prints out the results again. It is worth noting that the code is using Hibernate transactions. The createUsers(), modifyUsers() and printUSers() methods are relying on transactions ensuring that the two users are modified in the same transaction. The save() method of the Hibernate Session class persists the transient instance. In cases of successful operations, the transactions are committed, whereas they are rolled back in case exceptions.

public class SampleMain
{
  private static Logger LOG = Logger.getLogger(SampleMain.class);

  public static void main(String[] args)
  {
    runit();  
  }

  public static int runit() 
  {
    Configuration configuration = new Configuration();
    configuration.configure();
	
    String chorus = System.getProperty("nuodb.url");
	
	if (chorus != null)
		{
		System.out.println("Using JDBC URL: " + chorus);
		configuration.setProperty("hibernate.connection.url", chorus);
		}
	
    SessionFactory factory = configuration.buildSessionFactory();
    Session session = factory.openSession();
    int users = 0;
    try {
      createUsers(session);
      printUsers(session);
      modifyUsers(session);
      users = printUsers(session);
    } finally {
      session.close();
      factory.close();
    }
    return users;
  }
    
  /**
   * Create new user records using the given hibernate session.
   */
  private static void createUsers(Session session)
  {
    Transaction txn = session.beginTransaction();
    try {
      // @formatter:off
      createUser(session,
                 "Fred", "Flintstone", 
                 "fredf@example.com", "fred", true,
                 "301 Cobblestone Way", "Bedrock", "00001", 
                 "1 Slate Drive", "Granitetown", "00002");
      createUser(session,
                 "Barney", "Rubble", 
                 "barney@example.com", "barney", false,
                 "303 Cobblestone Way", "Bedrock", "00001", 
                 "1 Slate Drive", "Granitetown", "00002");
      // @formatter:on
      txn.commit();
    } finally {
      if (txn.isActive())
        txn.rollback();
    }
  }

  /**
   * Instantiate a new User instance and save it to the given session.
   */
  // @formatter:off
  private static User createUser(Session session,
                                 String firstName, String lastName, 
                                 String email, String userName, boolean admin,
                                 String homeStreet, String homeCity, String homeZip, 
                                 String shipStreet, String shipCity, String shipZip)
  // @formatter:on
  {
    User user = new User(firstName, lastName, userName, "", email);
    user.setHomeAddress(new Address(homeStreet, homeZip, homeCity));
    user.setAdmin(admin);
    // always use home address for billing
    user.setBillingAddress(new Address(homeStreet, homeZip, homeCity));
    user.setShippingAddress(new AddressEntity(shipStreet, shipZip, shipCity));
    user.getShippingAddress().setUser(user);
    session.save(user);
    LOG.info("Created user " + user);
    return user;
  }

  /**
   * Modify all Users by converting the name and billing address fields to upper
   * case. Home and shipping address are not changed.
   */
  private static void modifyUsers(Session session)
  {
    Transaction txn = session.beginTransaction();
    try {
      Query query = session.createQuery("from " + User.class.getName());

      @SuppressWarnings("unchecked")
      List<User> users = query.list();
      System.out.println("Found " + users.size() + " user records:");
      for (User user : users) {
        LOG.info("Updating " + user);
        user.setFirstname(user.getFirstname().toUpperCase());
        user.setLastname(user.getLastname().toUpperCase());
        user.setAdmin(false);
        Address bill = user.getBillingAddress();
        bill.setStreet(bill.getStreet().toUpperCase());
        bill.setCity(bill.getCity().toUpperCase());
        bill.setZipcode(bill.getZipcode().toUpperCase());
      }
      txn.commit();
    } finally {
      if (txn.isActive())
        txn.rollback();
    }
  }

  /**
   * Iterate over all User records and print them.
   */
  private static int printUsers(Session session)
  {
    int count = 0;
    Transaction txn = session.beginTransaction();
    try {
      Query query = session.createQuery("from " + User.class.getName());

      @SuppressWarnings("unchecked")
      List<User> users = query.list();
      System.out.println("Found " + users.size() + " user records:");
      for (User user : users) {
        System.out.println(user);
        System.out.println("  home: " + user.getHomeAddress());
        System.out.println("  bill: " + user.getBillingAddress());
        System.out.println("  ship: " + user.getShippingAddress());
	count++;
      }
      txn.commit();
    } finally {
      if (txn.isActive())
        txn.rollback();
    }
    return count;
  }
}

The data model classes are User, Address, and AddressEntity. Traditionally, Object-Relational Mappings are defined in XML documents to represent data transformation from Plain Old Java Object (POJO) to database tables and vice versa.  Hibernate also supports annotations like @Entity, @Table, @Column to define mappings without using XML files. The original NuoDB Hibernate example that comes with the installation package is using Hibernate XML mapping files but I have rewritten the model Java files to use Hibernate annontations instead.

The following modifications were required to support Hibernate annotations:

First, I had to change pom.xml file to add hibernate-annotations and ejb3-persistence:

   <dependencies>
   ...  
    <dependency>
      <groupId>org.hibernate</groupId>
      <artifactId>hibernate-annotations</artifactId>
      <version>3.4.0.GA</version>
    </dependency>
    <dependency>
      <groupId>org.hibernate</groupId>
      <artifactId>ejb3-persistence</artifactId>
      <version>1.0.1.GA</version>
    </dependency>     
    ...
  </dependencies>                                                                                                                                                 

Then I modified the three model files – Address.java, AddressEntity.java and User.java -  to use annotations

After moving from Hibernate mapping files to Hibernate annotations Address.java file looks as follows

package com.nuodb.sample.model;

import java.io.Serializable;

import javax.persistence.*;

   
@Embeddable
public class Address implements Serializable
{
  private static final long serialVersionUID = 1L;

  @Column(length = 255, nullable = false)
  private String street;

  @Column(length = 16, nullable = false)
  private String zipcode;

  @Column(length = 255, nullable = false)
  private String city;

  public Address()
  {
  }

  public Address(String street, String zipcode, String city)
  {
    this.street = street;
    this.zipcode = zipcode;
    this.city = city;
  }
...
...
}

The AddressEntity.java file looks as follows with Hibernate annotations:

package com.nuodb.sample.model;

import java.io.Serializable;

import javax.persistence.*;

@org.hibernate.annotations.GenericGenerator(
    name = "userAddressSharedPKGenerator",
    strategy ="foreign",
    parameters = @org.hibernate.annotations.Parameter(name = "property", value = "user")
)

@Entity
@Table(name = "ADDRESS")
public class AddressEntity implements Serializable
{
  private static final long serialVersionUID = 1L;

  @Id @GeneratedValue(generator = "userAddressSharedPKGenerator")
  @Column(name = "ADDRESS_ID")
  private Long id = null;

  @Version
  @Column(name = "OBJ_VERSION")
  private int version = 0;

  @Column(name = "STREET", length = 255, nullable = false)
  private String street;

  @Column(name = "ZIPCODE", length = 16, nullable = false)
  private String zipcode;

  @Column(name = "CITY", length = 255, nullable = false)
  private String city;

  @OneToOne(optional = false, fetch = FetchType.LAZY)
  @PrimaryKeyJoinColumn
  private User user;

  public AddressEntity()
  {
  }                                                                                                                                                 
...
}

And the User.java file looks as follows with Hibernate annotations:

package com.nuodb.sample.model;

import java.io.Serializable;
import java.util.Date;

import javax.persistence.*;

@Entity
@Table(name = "USERS")
@SecondaryTable(
    name = "BILLING_ADDRESS",
    pkJoinColumns = {
        @PrimaryKeyJoinColumn(name="USER_ID")
    }
)
@org.hibernate.annotations.BatchSize(size = 10)
public class User implements Serializable, Comparable<User>
{
  private static final long serialVersionUID = 1L;

  @Id @GeneratedValue
  @Column(name = "USER_ID")
  private Long id = null;

  @Version
  @Column(name = "OBJ_VERSION")
  private int version = 0;

  @Column(name = "FIRSTNAME", length = 255, nullable= false)
  private String firstname;

  @Column(name = "LASTNAME", length= 255, nullable= false)
  private String lastname;

  //@Column(name = "USERNAME", length = 16, nullable = false, unique = true) -- example taken from JPA book, not immutable
  @Column(name = "USERNAME", length = 16, nullable = false, unique = true, updatable = false)
  private String username; // Unique and immutable

  @Column(name = "`PASSWORD`", length = 12, nullable = false)
  private String password;

  @Column(name = "EMAIL", length = 255 , nullable = false)
  private String email;

  @Column(name = "RANK", nullable = false)
  private int ranking = 0;

  @Column(name = "IS_ADMIN", nullable = false)
  private boolean admin = false;

  @Embedded
  @AttributeOverrides( {
      @AttributeOverride(name   = "street",
                         column = @Column(name="HOME_STREET", length = 255) ),
      @AttributeOverride(name   = "zipcode",
                         column = @Column(name="HOME_ZIPCODE", length = 16) ),
      @AttributeOverride(name   = "city",
                         column = @Column(name="HOME_CITY", length = 255) )
      })
  private Address homeAddress;

  @Embedded
  @AttributeOverrides( {
      @AttributeOverride(
          name   = "street",
          column = @Column(name="STREET", length = 255,
                           table = "BILLING_ADDRESS")
      ),
      @AttributeOverride(
          name   = "zipcode",
          column = @Column(name="ZIPCODE", length = 16,
                           table = "BILLING_ADDRESS")
      ),
      @AttributeOverride(
          name   = "city",
          column = @Column(name="CITY", length = 255,
                           table = "BILLING_ADDRESS")
      )
  })
  private Address billingAddress;

  @OneToOne(cascade = {CascadeType.PERSIST, CascadeType.MERGE})
  @PrimaryKeyJoinColumn
  private AddressEntity shippingAddress;

  @Temporal(TemporalType.TIMESTAMP)
  @Column(name="CREATED", nullable = false, updatable = false)
  private Date created = new Date();

  public User()
  {
  }
                                                                                                                                                
...
}

Finally, we need to remove the hbm.xml files from model directory. After all these changes we can run mvn compile and mvn exec:java commands, as described above.

If we login to the test NuoDB database, we can verify the 3 tables that were created: Users, Address, Billing Address - each table has two rows:

$ bin/nuosql test --user cloud --password user
SQL> use sample
SQL> show tables;

	Tables in schema SAMPLE

		ADDRESS
		BILLING_ADDRESS
		USERS
SQL> select * from USERS;

 USER_ID  OBJ_VERSION  FIRSTNAME   LASTNAME  USERNAME  PASSWORD        EMAIL        RANK  IS_ADMIN          CREATED             HOME_STREET     HOME_ZIPCODE  HOME_CITY  
 -------- ------------ ---------- ---------- --------- --------- ------------------ ----- --------- ----------------------- ------------------- ------------- ---------- 

    11         1         FRED     FLINTSTONE  fred               fredf@example.com    0       0     2013-03-29 20:01:35.362 301 Cobblestone Way     00001      Bedrock   
    12         1         BARNEY   RUBBLE      barney             barney@example.com   0       0     2013-03-29 20:01:35.389 303 Cobblestone Way     00001      Bedrock 

SQL> select * from ADDRESS;

 ADDRESS_ID  OBJ_VERSION     STREET     ZIPCODE     CITY     
 ----------- ------------ ------------- -------- ----------- 

     11           0       1 Slate Drive  00002   Granitetown 
     12           0       1 Slate Drive  00002   Granitetown 

SQL> select * from BILLING_ADDRESS;

 USER_ID        STREET        ZIPCODE   CITY   
 -------- ------------------- -------- ------- 

    11    301 COBBLESTONE WAY  00001   BEDROCK 
    12    303 COBBLESTONE WAY  00001   BEDROCK 

Conclusion

NuoDB offers full SQL and ACID relational database capabilities.  In a modern RDBMS environment, it is now a common requirement to have support for Object-Relational Mapping. This is how we can easily bridge the gap between object oriented languages and relational databases. NuoDB has Hibernate support that can ease software development in a large scale development project where we need scalable relational databases with Java integration. 

Published at DZone with permission of Istvan Szegedi, author and DZone MVB.

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)