Vertica Integration with Hibernate: Connection Guide

About Vertica Connection Guides

Vertica connection guides provide basic instructions for connecting a third-party partner product to Vertica. Connection guides are based on our testing with specific versions of Vertica and the partner product.

Vertica and Hibernate: Software Versions

Most Recent Testing

This document is based on the results of testing Hibernate 5.4.1 with Vertica 9.2.

Requirements

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

• JDK 8.0 or later
• VerticaDialect.jar
• Hibernate 5 or later
• Vertica client 9.0 or later
• Vertica server 9.0 or later

Hibernate Overview

Hibernate is an ORM (object-relational-mapping) tool for the Java programming language. Hibernate provides a framework for mapping an object-oriented domain model to a relational database.

Hibernate is free, open-source software. To learn more about Hibernate, visit the Hibernate website.

Using Hibernate with VerticaDialect

A Hibernate dialect specifies the SQL dialect of an underlying database so that Hibernate can generate the proper SQL statements. (See Download VerticaDialect.jar.)

This documents steps you through the process of 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

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 JDK
• Download the Vertica Client Driver
• Download VerticaDialect.jar

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 JDK

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

Download the Vertica Client Driver

1. Navigate to the Client Drivers page on the Vertica website.

2. Download the JDBC driver package.

   Note  

   For details about client and server compatibility, see Client Driver and Server Version Compatibility in the Vertica documentation.

3. Copy the Vertica JDBC .jar file to the client directory for external libraries.

Download VerticaDialect.jar

1. Right-click here.
2. Select Save link as... to save VerticaDialect.jar on your computer.
3. Copy VerticaDialect .jar to the client directory for external libraries.

Create the Hibernate Mapping File

You must create a Hibernate mapping file so that Hibernate can 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>
            </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;
	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 VerticaJDBC.jar, VerticaDialect.jar, and other dependent jar files. (See Download the Vertica Client Driver and Download VerticaDialect.jar.)

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:

Java –cp ”C:\Hibernate\lib\antlr-2.7.7.jar”;
	”C:\Hibernate\lib\classmate-1.3.4.jar”;
	”C:\Hibernate\lib\commons-collections-3.2.1.jar”;
	”C:\Hibernate\lib\dom4j-2.1.1.jar”;
	”C:\Hibernate\lib\byte-buddy-1.9.5.jar”;
	”C:\Hibernate\lib\FastInfoset-1.2.15.jar”;
	”C:\Hibernate\lib\hibernate-commons-annotations-5.1.0.Final.jar”;
	”C:\Hibernate\lib\hibernate-jpa-2.1-api-1.0.0.Final.jar”;
	”C:\Hibernate\lib\jandex-2.0.5.Final.jar”;
	”C:\Hibernate\lib\javassist-3.24.0.GA.jar”;
	”C:\Hibernate\lib\jboss-logging-3.3.2.Final.jar”;
	”C:\Hibernate\lib\jboss-transaction-api_1.2_spec-1.1.1.Final.jar”;
	”C:\Hibernate\lib\slf4j-api-1.5.8.jar”;
	”C:\Hibernate\lib\VerticaDialect.jar”;
	”C:\Hibernate\lib\vertica-jdbc-9.2.0-0.jar";
	”C:\Hibernate\lib\hibernate-core-5.4.1.Final.jar”; 
	”C:\Hibernate\lib\istack-commons-runtime-3.0.7.jar”; 
	”C:\Hibernate\lib\javax.activation-api-1.2.0.jar”; 
	”C:\Hibernate\lib\javax.persistence-api-2.2.jar”; 
	”C:\Hibernate\lib\jaxb-api-2.3.1.jar”; 
	”C:\Hibernate\lib\jaxb-runtime-2.3.1.jar”; 
	”C:\Hibernate\lib\stax-ex-1.8.jar”; 
	”C:\Hibernate\lib\txw2-2.3.1.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\lib\antlr-2.7.7.jar”;
	”C:\Hibernate\lib\classmate-1.3.4.jar”;	
	”C:\Hibernate\lib\commons-collections-3.2.1.jar”;
	”C:\Hibernate\lib\dom4j-2.1.1.jar”;
	”C:\Hibernate\lib\byte-buddy-1.9.5.jar”;
	”C:\Hibernate\lib\FastInfoset-1.2.15.jar”;
	”C:\Hibernate\lib\hibernate-commons-annotations-5.1.0.Final.jar”;
	”C:\Hibernate\lib\hibernate-jpa-2.1-api-1.0.0.Final.jar”;
	”C:\Hibernate\lib\jandex-2.0.5.Final.jar”;
	”C:\Hibernate\lib\javassist-3.24.0.GA.jar”;
	”C:\Hibernate\lib\jboss-logging-3.3.2.Final.jar”;
	”C:\Hibernate\lib\jboss-transaction-api_1.2_spec-1.1.1.Final.jar”;
	”C:\Hibernate\lib\slf4j-api-1.5.8.jar”;
	”C:\Hibernate\lib\VerticaDialect.jar”;
	”C:\Hibernate\lib\vertica-jdbc-9.2.0-0.jar";
	”C:\Hibernate\lib\hibernate-core-5.4.1.Final.jar”; 
	”C:\Hibernate\lib\istack-commons-runtime-3.0.7.jar”;
	”C:\Hibernate\lib\javax.activation-api-1.2.0.jar”;
	”C:\Hibernate\lib\javax.persistence-api-2.2.jar”;
	”C:\Hibernate\lib\jaxb-api-2.3.1.jar”;
	”C:\Hibernate\lib\jaxb-runtime-2.3.1.jar”;
	”C:\Hibernate\lib\stax-ex-1.8.jar”;
	”C:\Hibernate\lib\txw2-2.3.1.jar”;com.simple.TestHibernate	

The following shows the sample output:

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

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

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: select
  employeewi0_.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=?

For More Information

• Hibernate
• Vertica Community Edition
• Vertica User Community
• Vertica Documentation