Trafodion dialect for Hibernate

//Trafodion dialect for Hibernate

An Object-Relational Mapping (ORM) provides a framework that allows applications to query and manipulate data from a database using an object paradigm. The framework is implemented in many languages that encapsulate the code needed to manipulate the data. That way, you don’t need to know SQL specifics but can access the data using an object that hides that every database has variations in query language.

 

Consider the Employees table having the following records:

IdNameAddressDepartmentSalary
1JohnMilpitas, CAEngineer$ 100,000
2TomCupertino, CASupport$ 80,000
3JamesSunnyvale, CAPubs$ 70,000
4MikeSan Jose, CAMarketing$ 95,000
5MayaFremont, CASales Rep$ 80,000

.
Following is an example that retrieves ID, Name and Salary columns for customers available in an Employees table:

 SQL> SELECT ID, NAME, SALARY FROM Employees where ID = 1 ;

Programmatically, you have to create the following code:

 String selectSQL = "SELECT ID, NAME FROM Employees WHERE ID = 1" ;
 Statement = dbConnection.createStatement() ;
 ResultSet rs = Statement.executeQuery(selectSQL ) ;
 while (rs.next())
 {
    String userid = rs.getString("ID") ;
    String username = rs.getString("NAME") ;
 }

With an ORM, the code changes to:

 Employee_list = EmployeeTable.query(ID=1) ;

As you can see, all the complexity is handled automatically by the ORM. There are many additional benefits when using ORM ; for example, it:

  • Saves time since data model is in one place.
  • Is easier to update, maintain and reuse the code.
  • Is very flexible because you code in your preferred programming language.

Of course, you have to spend time learning ORM to enjoy these benefits. There are a lot of ORMs around like Hibernate (Java), Propel or Doctrine (PHP), Django or SQLAlchemy (Python) etc.

 

In this post, we focus on Hibernate. Hibernate ORM (Hibernate) is an object-relational mapping framework for the Java language, which provides a  framework for mapping an object-oriented domain model to a traditional relational database. Hibernate solves object-relational impedance mismatch problems by replacing direct persistence-related database accesses with high-level object handling functions. Hibernate is free software that is distributed under the GNU Lesser General Public License 2.1. Features include mapping from Java classes to database tables (and from Java data types to SQL data types), provides data query and retrieval facilities. It generates SQL calls and relieves the developer from manual result set handling and object conversion. Applications using Hibernate are portable to support SQL databases with little performance overhead.

 

Hibernate sits between traditional Java objects and the database server handling all the work required to persist those objects based on the appropriate O/R mechanisms and patterns.

 

 

Hibernate’ s layered architecture isolates from knowing the underlying APIs. Hibernate makes use of the database and configuration data to provide persistence services (and persistent objects) to the application. Following is an illustration of the high level architecture:

 

 

Next, let’s examine how Trafodion supports the Hibernate framework. To support Hibernate, a database vendor has to create a dialect that achieves optimal performance and that leverages enriched functionality. At times, there might be certain features/functions that are only available in Trafodion (or another database) but not supported by Hibernate. This means the Hibernate SQL Dialect translates your Hibernate application into the SQL language that should be used to talk with your database. To support such function/features, we have created a Trafodion dialect that allows Hibernate to interpret such features when encountered by Hibernate applications. JIRA for the Trafodion dialect:  https://hibernate.atlassian.net/browse/HHH-10216

Steps and sample code of using Trafodion dialect

Step 1:  Create Trafodion database table:

 set schema TRAFODION.SEABASE;
 drop table employee;
 create table EMPLOYEE (
 id INT NOT NULL ,
 first_name VARCHAR(20) default NULL,
 last_name  VARCHAR(20) default NULL,
 salary     INT  default NULL,
 PRIMARY KEY (id)
 );
Create sequence trafodion.seabase.empseq;

Step 2:  Create POJO classes

Contents of Employee.java

 package trafodion.hibernate.common;
public class Employee {
   private int id;
   private String firstName;
   private String lastName;
   private int salary;
   public Employee() {}
   public Employee(String fname, String lname, int salary) {
      this.firstName = fname;
      this.lastName = lname;
      this.salary = salary;
   }
   public int getId() {
      return id;
   }
   public void setId( int id ) {
      this.id = id;
   }
   public String getFirstName() {
      return firstName;
   }
   public void setFirstName( String first_name ) {
      this.firstName = first_name;
   }
   public String getLastName() {
      return lastName;
   }
   public void setLastName( String last_name ) {
      this.lastName = last_name;
   }
   public int getSalary() {
      return salary;
   }
   public void setSalary( int salary ) {
      this.salary = salary;
   }
}

Contents of HibernateUtil.java:

 package trafodion.hibernate.persistence;
 import org.hibernate.SessionFactory;
 import org.hibernate.cfg.Configuration;
 public class HibernateUtil {
    private static final SessionFactory sessionFactory = buildSessionFactory();
    private static SessionFactory buildSessionFactory() {
       try {
          // Create the SessionFactory from hibernate.cfg.xml
          return new Configuration().configure().buildSessionFactory();
       }
       catch (Throwable ex) {
          // Make sure you log the exception, as it might be swallowed
          System.err.println("Initial SessionFactory creation failed." + ex);
          throw new ExceptionInInitializerError(ex);
       }
    }
    public static SessionFactory getSessionFactory() {
       return sessionFactory;
    }
    public static void shutdown() {
       // Close caches and connection pools
       getSessionFactory().close();
    }
 }

Step 3: Create the mapping configuration file.

The following file tells Hibernate how to map the Java class to the database tables:

Contents of Employee.hbm.xml file:

 "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

The Java class contains the employee detail:

trafodion.seabase.empseq

Contents of hibernate configuration file for Trafodion database:

 
 "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
 "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
false
org.trafodion.jdbc.t4.T4Driver
zz
jdbc:t4jdbc://10.12.10.21:12345/:
zz
org.hibernate.dialect.TrafodionDialect
true

Step 4: Create application class file (ManageEmployee.java)

package trafodion.hibernate.common;
import java.util.List;
import java.util.Date;
import java.util.Iterator;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
public class ManageEmployee {
   private static SessionFactory factory;
   public static void main(String[] args) {
      try{
         factory = new Configuration().configure().buildSessionFactory();
      }catch (Throwable ex) {
         System.err.println("Failed to create sessionFactory object." + ex);
         throw new ExceptionInInitializerError(ex);
      }
      ManageEmployee ME = new ManageEmployee();
      /* Add few employee records in database */
      Integer empID1 = ME.addEmployee("AAA", "Ali", 1000);
      Integer empID2 = ME.addEmployee("BBB", "Das", 5000);
      Integer empID3 = ME.addEmployee("CCC", "Paul", 10000);
      /* List down all the employees */
      ME.listEmployees();
      /* Update employee's records */
      ME.updateEmployee(empID1, 5000);
      /* List down all the employees */
      ME.listEmployees();
      /* Delete an employee from the database */
      ME.deleteEmployee(empID2);
      /* List down new list of the employees */
      ME.listEmployees();
   }
   /* Method to CREATE an employee in the database */
   public Integer addEmployee(String fname, String lname, int salary){
      Session session = factory.openSession();
      Transaction tx = null;
      Integer employeeID = null;
      try{
         tx = session.beginTransaction();
         Employee employee = new Employee(fname, lname, salary);
         employeeID = (Integer) session.save(employee);
         tx.commit();
      }catch (HibernateException e) {
         if (tx!=null) tx.rollback();
         e.printStackTrace();
      }finally {
         session.close();
      }
      return employeeID;
   }
   /* Method to  READ all the employees */
   public void listEmployees( ){
      Session session = factory.openSession();
      Transaction tx = null;
      try{
         tx = session.beginTransaction();
         Query qry= session.createQuery("FROM Employee");
         List employees= qry.list();
         //List employees = session.createQuery("FROM Employee").list();
         for (Iterator iterator =
                    employees.iterator(); iterator.hasNext();){
            Employee employee = (Employee) iterator.next();
            System.out.print("First Name: " + employee.getFirstName());
            System.out.print("  Last Name: " + employee.getLastName());
            System.out.println("  Salary: " + employee.getSalary());
         }
         tx.commit();
      }catch (HibernateException e) {
         if (tx!=null) tx.rollback();
         e.printStackTrace();
      }finally {
         session.close();
      }
   }
   /* Method to UPDATE salary for an employee */
   public void updateEmployee(Integer EmployeeID, int salary ){
      Session session = factory.openSession();
      Transaction tx = null;
      try{
         tx = session.beginTransaction();
         Employee employee =
            (Employee)session.get(Employee.class, EmployeeID);
         employee.setSalary( salary );
         session.update(employee);
         tx.commit();
      }catch (HibernateException e) {
         if (tx!=null) tx.rollback();
         e.printStackTrace();
      }finally {
         session.close();
      }
   }
   /* Method to DELETE an employee from the records */
   public void deleteEmployee(Integer EmployeeID){
      Session session = factory.openSession();
      Transaction tx = null;
      try{
         tx = session.beginTransaction();
         Employee employee =
             (Employee)session.get(Employee.class, EmployeeID);
         session.delete(employee);
         tx.commit();
      }catch (HibernateException e) {
         if (tx!=null) tx.rollback();
         e.printStackTrace();
      }finally {
         session.close();
      }
    }
 }


Acknowledgements:
We would like to thank Haifeng Li, Chief Data Scientist of ADP Global Products and Technology, for creating and contributing to Trafodion Dialect.

2018-06-05T15:07:20+00:00

About the Author: