Share this article:

Vertica Integration with Hibernate: Connection Guide

For Vertica 7.2.x 

About Vertica Connection Guides

Vertica connection guides provide basic information about setting up connections to Vertica from software that our technology partners create. These documents provide guidance using one specific version of Vertica and one specific version of the third-party vendor’s software. Other versions of the third-party product may work with Vertica. However, other versions may not have been tested. This document provides guidance using the latest versions of Vertica and Hibernate as of June, 2016.

Using Hibernate with VerticaDialect

Hibernate is an ORM (object-relational-mapping) tool that automatically converts Vertica database tables to Java classes. This documents steps through the process for creating a console-based Hibernate application. Use this application to establish a connection to your Vertica database using the VerticaDialect. In addition, use this application to execute all CRUD operations, including insert, delete, and simple SQL statements.

This document uses the following SQL statement as an example:

=> SELECT class_desc,Item_DESC,
      sum(units_received) 
      from lookup_item,Inventory_orders
      where lookup_item.item_nbr=Inventory_ORDERS.item_id
      group by 
 class_desc,Item_DESC 
      order by class_desc,Item_DESC

This document is based on the results of testing Hibernate 4 with Vertica 7.2.x.

To use Hibernate with Vertica, you must have the following:

  • JDK 6.0 or later
  • VerticaDialect.jar available on the Big Data Marketplace
  • Hibernate 4 or later
  • Vertica client 7.0.x or later
  • Vertica server 7.0.x or later

Set up the Environment

Before you can begin to use the Hibernate application, you must set up the environment by completing the following:

  • Download and Install Hibernate
  • Download and Install the Vertica Client Driver
  • Download and Install the JDK

Download and Install Hibernate

You can download releases of Hibernate from the list at https://sourceforge.net/projects/hibernate/files/hibernate-orm/. The name of the .zip file is hibernate-release-xxx.Final.zip. To install Hibernate, follow the instructions in the Hibernate Installation Wizard.

Download and Install the Vertica Client Driver

Before you can connect to Vertica using Hibernate, you must install the Vertica client driver package. This package includes the JDBC client driver that Hibernate uses to connect to Vertica.

  1. Go to the Vertica Client Drivers
  2. Download the version of the Vertica client package that is compatible with the architecture of your operating system and Vertica server version.

Note Vertica drivers are forward compatible, so you can connect to the Vertica server using previous versions of the client. For more information about client and server compatibility, see Client Driver and Server Version Compatibility in the Vertica documentation.

Based on the client driver package you downloaded, follow the steps for installation from the Vertica documentation.

Download and Install the JDK

Download and install the Java Development Kit (JDK) following the instructions from the Oracle website.

Create the Hibernate Mapping File

You must create a Hibernate mapping file for Hibernate to know how to load and store objects of the persistent class. The mapping file tells Hibernate which database tables and table columns to map to the Java class.  

For the scope of this example, this document looks at creating two class object mappings. One is for the Inventory_Orders table and the other is for the Lookup_Item table. Because these two tables join, this example also includes the associations between the two objects.

The following shows the creation of the InventoryOrders.hbm.xml file:

<?xml version =”1.0”?>
 <!DOCTYPE hibernate-mapping PUBLIC 
 “-//Hibernate/Hibernate Mapping DTD 3.0//EN”
 http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd>
<hibernate-mapping>
          <class name="com.sample.InventoryOrders" table="inventory_orders">
                <id name="item_id" type="float" column="item_id">
                              <generator class="native"/>
                </id>
                <property name="units_received">
                              <column name="units_received"/>
                </property>
                <bag name="item" inverse="true" cascade="all,delete-orphan">
                              <key column="item_nbr"/>
                              <one-to-many class="com.sample.Item"/>
                </bag>
         </class>
</hibernate-mapping>

The following shows the creation of the Item.hbm.xml file:

<?xml version =”1.0”?>
 <!DOCTYPE hibernate-mapping PUBLIC 
 “-//Hibernate/Hibernate Mapping DTD 3.0//EN”
 http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd>
<hibernate-mapping>
          <class name="com.sample.Item" table="lookup_item">
                <id name="item_nbr" type="float" column="item_nbr">
                              <generator class="increment"/>
                </id>
                <property name="class_desc" type="string">
                              <column name="class_desc"/>
                </property>
                <property name= "item_desc"
                              <column_name="item_desc"/>
                </property>
                <many-to-one name="inventoryorders" 
class="com.sample.InventoryOrders" column="item_id" insert="false" 
update="false"/>
         </class>
</hibernate-mapping>

Create the Java Persistent Class

After you create the Hibernate mapping, you must create a persistent class for a table object. This class uses the standard JavaBean naming conventions for property and setter methods, and for private visibility for the fields.

The following shows the creation of the InventoryOrders.java file:

package com.sample;
public class InventoryOrders {
         float item_id;
         float units_received;
         Item item;
         public item getItem(){
                   return item;
         }
         public void setItem(Item item) {
                   this.Item = item;
         }
         public float getItem_id() {
                   return item_id;
         }
         public void setItem_id(float item_id) {
                   thisitem_id = item_id;
         }
         public float getUnits_received() {
                   return units_received;
          }
          public void setUnits_received(float units_received) {
                   thisunits_received = units_received;
          }
}

The following shows the creation of the Item.java file:

package com.sample;
public class Item {
           float item_nbr;
           String class_desc;
           String item_desc;
           InventoryOrders inventoryorders;
           public InventoryOrders getInventoryorders(){
                      return inventoryorders;
           }
           public void setInventoryorders(InventoryOrders inventoryorders) {
                      this.inventoryorders = inventoryorders;
           }
           public float getItem_nbr() {
                      return item_nbr;
           }
           public void setItem_nbr(float item_nbe=r) {
                      thisitem_nbr = item_nbr;
           }
           public String getClass_desc() {
                      return class_desc;
           }
           public void setClass_desc(String class_desc) {
                      this.class_desc = class_desc;
           }
           public String getItem_desc() {
                      return item_desc;
           }
           public void setItem_desc(String item_desc) {
                      thisitem_desc = item_desc;
           }
}

Create Configuration File Entries 

To connect Hibernate with Vertica, you must add the Vertica JDBC.jar, VerticaDialect.jar and other dependent jar files. You can download the VerticaDialect.jar from the Big Data Marketplace.

To tell Hibernate how to connect your application to Vertica, you must create a Hibernate configuration file. The configuration file specifies the JDBC driver information, including database, server name, login, and password. Modify the following sample Hibernate configuration file to reflect your personal environment.

<?xml version =”1.0” encoding=”UTF-8”?>
<!DOCTYPE hibernate-configuration PUBLIC”-//Hibernate/Hibernate Configuration DTD 3.0//EN”
“http://www.hibernate.org/dtd/hibernate-configuration-2.0.dtd">
<hibernate-configuration>
           <session factory>
                      <property name=”hibernate.connection.driver_class”>com.vertica.jdbc.Driver</property>
 
                      <property name=hibernate.connection.password”>password</property>
                      <property name=”hibernate.connection.url>jdbcvertica://<IP_address>/hibernate_poc</property>
                      <property name=”hibernate.connection.username”>dbadmin</property>
                      <property name=”hibernate.dialect”>org.hibernate.dialect.VerticaDialect</property>
                      <property name=”show_sql”>true</property>
<mapping resource=”com/sample/Item.hbm.xml”>
<mapping resource=”com/sample/InventoryOrders.hbm.xml”/> </session-factory>
           </session-factory>
</hibernate-configuration>

Create the Main Class

The following main class file performs the following:

  • Configures the log.
  • Establishes a new session based on the settings in the hibernate.cf.xml file.
  • Builds and executes a query.
  • Formats the results.
package com.sample; 

import java.util.Iterator;
import org.hibernateQuery;
import org.hibernate.Session;

public class TestHibernate {
           public static void main(String[] args) {
                     Session session = SessionFactoryUtilgetSessionFactory() openSession();
                     Session.beginTransaction();
                     queryInventory(session);
                     System.out.printIn(“done”);
           }
           private static void queryInventory(Session session) {
                     Query query = sessioncreateQuery(“select a.class_desc,a.item_desc,a.item_desc,sum(b.units_received) from
Item a ,InventoryOrders b where a.item_nbr=b.item_id group by a.class_desc,a.item_desc order by a-class_desc,a.item_desc”);
                     System.out.printIn(“Class Desc \t\t”+”Item Desc \t\t”+”Total Unit”);
                     For(Iterator it=query.Iterate()it.hasNext();)
                     {
                                Object[]row=(Object[])it.next();
                                System.out.print(row[0]);
                                System.out.print(“\t\t”+row[1]);
                                System.out.print(“\t\t”+row[2]);
                                System.out.println();
                     }
                     Session.close();
          }
 }

Build and Run the Hibernate Application

Create the Build File

The following build file tells the compiler to compile all files ending with .java and to add the jar files to the CLASSPATH. Modify the sample build file to reflect your personal settings. You can run this as an executable file or on the Hibernate command line:

javac com\sample\*.java -cp.;”C:\hibernate-4.0\lib\antlr-2.7.7.jar”;”C:\hibernate-4.0\lib\classmate-0.5.4.jar”;”C:\hibernate-4.0\lib\commons-collections-3.2.1.jar”;”C:\hibernate-4.0\lib\dom4j-1.6.1.jar”;”C:\hibernate-4.0\lib\hibernate-commons-annotations-4.0.0.Final.jar”;”C:\hibernate-4.0\lib\hibernate-core-4.0.0.CR6.jar”;”C:\hibernate-4.0\lib\hibernate-jpa-2.0-api-1.0.1.Final.jar”;”C:\hibernate-4.0\lib\jandex-1.0.3.Final.jar”;”C:\hibernate-4.0\lib\javassist-3.12.1.GA.jar”;”C:\hibernate-4.0\lib\jboss-logging-3.1.0.CR1.jar”;”C:\hibernate-4.0\lib\jboss-transaction-api_1.1_spec-1.0.0.Final.jar”;”C:\hibernate-4.0lib\slf4j-api-1.5.8.jar”;”C:\hibernate\VerticaDialect.jar”;”C:\hibernate\vertica-jdbc-7.0.0-0.jar”; System.out.println();

Run the Hibernate Application

After you create the build file, run the Hibernate application. The following is a sample run file: 

Java –cp.;”C:\hibernate-4.0\lib\antlr-2.7.7.jar”;”C:\hibernate-4.0\lib\classmate-0.5.4.jar”;”C:\hibernate-4.0\lib\commons-collections-3.2.1.jar”;”C:\hibernate-4.0\lib\dom4j-1.6.1.jar”;”C:\hibernate-4.0\lib\hibernate-commons-annotations-4.0.0.Final.jar”;”C:\hibernate-4.0\lib\hibernate-core-4.0.0.CR6.jar”;”C:\hibernate-4.0\lib\hibernate-jpa-2.0-api-1.0.1.Final.jar”;”C:\hibernate-4.0\lib\jandex-1.0.3.Final.jar”;”C:\hibernate-4.0\lib\javassist-3.12.1.GA.jar”;”C:\hibernate-4.0\lib\jboss-logging-3.1.0.CR1.jar”;”C:\hibernate-4.0\lib\jboss-transaction-api_1.1_spec-1.0.0.Final.jar”;”C:\hibernate-4.0lib\slf4j-api-1.5.8.jar”;”C:\hibernate\VerticaDialect.jar”;”C:\hibernate\vertica-jdbc-7.0.0-0.jar”;com.simple.TestHibernate

The following shows the sample output:

SampleOutput.png

Custom Functions with VerticaDialect

With VerticaDialect, you can create custom functions. If you want to add custom functionality that overrides the existing VerticaDialect, use CustomVerticaDialect.

Hibernate Configuration Files

Create the hibernate.cfg.xml file in your project to connect to your Vertica database, as shown in the following: 

<?xml version =”1.0” encoding=”UTF-8”?>
<!DOCTYPE hibernate-configuration PUBLIC”-//Hibernate/Hibernate Configuration DTD 3.0//EN”
“http://www.hibernate.org/dtd/hibernate-configuration-2.0.dtd">
hibernate-configuration>
<session-factory>
           <property 
 name = ”hibernate.connection.driver_class”>com.vertica.jdbc.Driver </property>
 
            <property name=hibernate.connection.password”>password</property>
            <property name=”hibernate.connection.url>jdbcvertica://<IP_address>/hibernate_poc</property>
            <property name=”hibernate.connection.username”>dbadmin</property>
                      <property name=”hibernate.dialect”>org.hibernate.dialect.CustomVerticaDialect</property> 
 
                      <property name=”show_sql”>true</property>
           </session-factory>
</hibernate-configuration>

Register a Function with VerticaDialect

To add a custom function in VerticaDialect that Hibernate 4 supports, create a Java class that extends VerticaDialect and register your function, as shown in the following:

package org.hibernate.dialect;

import org.hibernate.dialect.VerticaDialect;
import org.hibernate.dialect.function.NvlFunction;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.type.StandardBasicTypes;

public class VerticaDialectCustom extends VerticaDialect {
          public VerticaDialectCustom() {
                     super
                     registerFunctions();
          }
          protected void registerFunction(){
                     registerFunction(“substring”, new StandardSQLFunction(“substr”,StandardBasic Types.STRING));
                     registerFunction(“bit_length”, new SQLFunctionTemplate(StandardBasicTypes.INTEGER, “vsize(?1)*8”));
                     registerFunction(“coalesce”, new NvlFunction() );
          }
          };

Hibernate Batch Operation in Vertica

The Hibernate Batch insert uploads large numbers of result sets to the Vertica database as a batch of records rather than every time a single row is inserted.

Hibernate Configuration

To use the batch processing feature, you must set the hibernate,jdbc.batch_size to a number that depends on the object size. The value varies based on the total number of the result set. This value tells the Hibernate container that every X number of rows should be inserted as batch. To implement the batch size into your code, use the following example:

<?xml version =”1.0” encoding=”UTF-8”?>
<!DOCTYPE hibernate-configuration PUBLIC”-//Hibernate/Hibernate Configuration DTD 3.0//EN”
 “http://www.hibernate.org/dtd/hibernate-configuration-2.0.dtd">
<hibernate-configuration>
           <session-factory>
                      <property name=”hibernate.connection.driver_class”>com.vertica.jdbc.Driver</property>
 
                      <property name=hibernate.connection.password”>password</property>
                      <property name=”hibernate.connection.url>jdbcvertica://<IP_address>/hibernate_poc</property>
                      <property name=”hibernate.connection.username”>dbadmin</property>
                      <property name=”hibernate.dialect”>org.hibernate.dialect.VerticaDialect</property> 
 
                      <property name=”show_sql”>true</property>
                      <property name=”hibernate.jdbc.batch_size”>1000</property>
           </session-factory>
</hibernate-configuration>

For an example of how to implement the Hibernate Batch Operation, see the Hibernate documentation.

Known Limitations

The following section explains a known limitation with data behavior when using Hibernate with Vertica.

Data Behavior

Hibernate maintains object relational mapping for all its data and maintains all the concepts of the OLTP database. Because Vertica is an OLAP database and does not adhere to all characteristics of an OLTP database, Hibernate does not maintain the uniqueness of the primary key.

Hibernate gives you an error when you try to:

  • Insert a duplicate record in a primary key table:
    Failed to create sessionFactory
    object.org.hibernate.NonUniqueObjectException: A different object with the same identifier value was already associated with the session:
    [com.ver.emp.Employee#2500]
    org.hibernate.NonUniqueObjectException: A different object with the same identifier value was already associated with the session:
  • Delete a record when the table contains duplicate data:
    DELETE:
    Hibernate: selectemployeewi0_.EMPID as EMPID19_, employeewi0_.EMPNAME as EMPNAME19_, employeewi0_.EMPSAL as EMPSAL19_, employeewi0_.DOB as DOB19_, employeewi0_.JOINING_DATE as JOININGS5_19_ from
    hibernate.public.EMPLOYEE_WithPrimarykey employeewi0_
    Size16
    Record to be deleted :::::
    Employee: 1|Mark|10000.0|2012-11-27
    Hibernate: delete from hibernate.public.EMPLOYEE_WithPrimarykey where EMPID=?

Note You can avoid issues with the following limitation by using the Constraint Enforcement Parameters introduced in Vertica 7.2.x. With these parameters, Vertica enables the primary key and unique key constraints. Hibernate works as expected with this parameter enabled.

For More Information

For More Information About… See…

Hibernate

http://hibernate.org/

Vertica Community Edition

https://my.vertica.com/community/

Vertica Documentation

http://my.vertica.com/docs/latest/HTML/index.htm

Big Data and Analytics Community

https://my.vertica.com/big-data-analytics-community-content/

Share this article: