Q: How can I configure Hibernate to execute my native SQL queries?

I will be assuming that you already know the basics about Hibernate in regards to mapping tables and creating the Hibernate configuration file for your project. The code that you see will be written in Java.

A:

Hibernate is an Open Source object relational mapping framework, which is highly touted but still has some flaws. There are many good reasons to use Hibernate to map tables directly to plain old java objects (POJOs), and also many good reasons to continue to use native SQL queries. Some of the reasons to use Hibernate include the simplicity of working with objects that represent relational tabular data, the benefits of reduced overhead by eliminating repetitive database calls, the benefits of first and second level caching, and the ease with which one can update and/or insert data. Some of the issues I have encountered with learning and using Hibernate include the Hibernate Query Language (HQL) syntax, the complexity of Hibernate-generated SQL code, and the habit that Hibernate has of wanting to continuously generate full table scans on my very large data sets.

Recently I have had the task of implementing a four level embedded SQL query in Hibernate. Step one was to write the SQL and run it through SQL Developer in order to validate the result set. Step two was to convert the SQL to HQL in order to process the result set as a POJO. This was not fun. Hibernate does not like us to think in regular SQL syntax when writing HQL code. Instead it prefers that we set up criteria and joins through the Hibernate session object. While this might not be a particularly complex process once you master the API, I have found it to be cumbersome to attempt to implement for the first time under strict project deadlines.

To remedy this situation, I have decided to use the function session.createSQLQuery(sql_string). There are a couple considerations that we must take into account when we use this function. Firstly, the syntax of the SQL string is still not identical to the way we might write a direct SQL query, nor is it identical to the Hibernate version of the same query. Secondly, Hibernate must know about the data types of the fields in the result set. Third, even though this is more representative of SQL syntax, we must still set the variable values in the SQL string using setString, setLong, etc…

In this very, very simple example I will be working with a client table and a project table, and I would have previously mapped both tables to Hibernate classes named Client and Project respectively. Normally for this type of query I would just use HQL, but for demonstrative purposes I will try to keep it simple. The purpose of the query is to gather client data for all clients in New Hampshire that have active projects.

---

First we must define the objects that we need:

		// *************************************************************
		// SQLQuery is from the Hibernate jar
		// List is from the Java JRE
		// Session is from the Hibernate JAR, ClientsDAO is generated by Hibernate,
		// an explanation of this process is out of scope of this article.
		// Transaction is from the Hibernate jar
		// String is from the Java JRE
		// *************************************************************

SQLQuery q = null;
List result = null;
Session session = ClientsDAO.getInstance().getSession();
Transaction tx = session.beginTransaction();
String sql = '';
String stateName = 'New Hampshire';

We will start with our basic sequel query where we define 'clients' to be an alias for the Clients table.

sql = 'SELECT * FROM Clients clients WHERE clients.state='New Hampshire' AND EXISTS (SELECT 1 FROM Projects WHERE clientID= clients.clientID)'

Hibernate will expect us to remove all constants and insert them later. New Hampshire is our constant and we will use ':state' as the placeholder.

sql = 'SELECT * FROM Clients clients WHERE clients.state=:state AND EXISTS (SELECT 1 FROM Projects WHERE clientID= clients.clientID)

Next we must define a return type for the result set. Since we are going to process 'Select * From Clients' as a Client object, we will map it as such, and we must tell Hibernate that the wildcard (*) refers to the Client data type. Note the use of curly braces ({ and }) rather than parenthesis or brackets. This is how we map an alias to a result set when using createSQLQuery( ). The new query string will look like this:

sql = 'SELECT {clients.*} FROM Clients clients WHERE clients.state=:state AND EXISTS (SELECT 1 FROM Projects WHERE clientID= clients.clientID)

Now that our SQL string is complete, we can create our SQL query in Hibernate.

q = session.createSQLQuery(sql)

Now we must map the data type of the alias that we used in our query string, and the value of any placeholders that we used in our code.

q.addEntity('clients', Clients.class);
q.setString('state', stateName);

In order to retrieve the results, we need to generate and parse a list of results.

resultSet = q.list( );

for(int i = 0; i < resultSet.size(); i++) {
	Object obj = resultSet.get(i);
	Client theClient = (Client)obj;
	// Do some operations on the Client object here
}

// ********************************************************************
// Final code might look like this.
// ********************************************************************
  
public void printClientDataByState(String stateName) {
	SQLQuery q = null;	
	List result = null;	
	Session session = ClientsDAO.getInstance().getSession();
	Transaction tx = session.beginTransaction();
  
	String sql = "SELECT {clients.*} " +
				 "FROM Clients clients " +
				 "WHERE clients.state=:state AND " +
				 "EXISTS (" +
					"SELECT 1 " +
					"FROM Projects " +
					"WHERE clientID= clients.clientID)"; 

	q = session.createSQLQuery(sql);
  	q.addEntity("clients", Clients.class);
  	q.setString("state", stateName);
  
	resultSet = q.list( );
  
	for(int i = 0; i < resultSet.size(); i++) {	
		Object obj = resultSet.get(i);
		Client theClient = (Client)obj;
		// Do some operations on the Client object here, perhaps printing.
	}
}

That's about all there is to know about the basics of using createSQLQuery for returning a full table/class. The reason I decided to write this article is that I searched around for two days before I finally found up-to-date material on this topic. Best of luck to all!