We can directly use a hand-written core SQL query with hibernate. This is a useful feature if we want to execute a database-specific query that is not supported by the hibernate API, such as query hints or the CONNECT keyword in an Oracle database.
This is a useful feature when the developer has a ready native SQL. We can perform the Select, non-select, and Bulk operations as well.
We can use Session.createSQLQuery(String query) to execute a SQL query. We have multiple APIs available to execute the SQL query, and we will take a look at these in detail:
- Scalar queries
- Entity queries
This is a basic type of query that returns a list of values (scalar).
For example, the following code shows how to select all the products from the product table:
SQLQuery sqlQuery = session.createSQLQuery("SELECT * FROM product");
List<Object[]> list = sqlQuery.list();
for(Object[] object : list){
System.out.println("
Id: " + object[0]);
System.out.println("Name: " + object[1]);
System.out.println("Price: " + object[2]);
System.out.println("Category id: " + object[3]);
}Hibernate: SELECT * FROM product Id: 1 Name: Meeting room table Price: 100.23 Category id: 1 Id: 2 Name: Metal bookcases Price: 120.0 Category id: 1 Id: 3 Name: Lighting Price: 70.36 Category id: 1 Id: 4 Name: Business envelopes Price: 40.92 Category id: 2 Id: 5 Name: Paper clips Price: 20.61 Category id: 2 Id: 6 Name: Highlighters Price: 30.0 Category id: 2
We can understand from the output that hibernate directly uses the query that is provided by us.
Here, we used the SELECT * FROM product query, which is equivalent to SELECT id, name, price, category_id FROM product, with which it would select all four fields from the product table. So, we can get Object[] of size 4. Hibernate returns List of Object array (List<Object[]>).
When we use this methodology to execute a SQL query, hibernate uses ResultSetMetadata to determine the order and data type of the fields. So, it will create an overhead for hibernate to get the field detail. To remove this overhead from hibernate, we can use the addScalar(String fieldName, Type dataType) method in the following way:
SQLQuery sqlQuery = session.createSQLQuery("SELECT id, name, price, category_id FROM product");
sqlQuery.addScalar("id", new org.hibernate.type.LongType());
sqlQuery.addScalar("name", new org.hibernate.type.StringType());
sqlQuery.addScalar("price", new org.hibernate.type.DoubleType());
sqlQuery.addScalar("category_id", new org.hibernate.type.LongType());
sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
List list = sqlQuery.list();Here, we defined a data type for each field using the addScalar(...) method. Another thing to add here is that we used ResultsetTransformers to transform the result to Map. So, now it returns a List of Map (List<Map>).
In the previous section, we went through the scalar queries, which always return a list of values, and we have to iterate all the values horizontally and vertically, which means over rows and columns. It is useful to remove this iteration from our (developer) end Entity query. The Entity query automatically fills an entity from the values returned by the query.
Here, as a part of our recipe, we will execute the following query to select all the products from the table and get the returned data into the Product entity:
SQLQuery sqlQuery = session.createSQLQuery("SELECT * FROM category");
/* Line 2 */ sqlQuery.addEntity(Category.class);
List<Category> list = sqlQuery.list();
for(Category category: list){
System.out.println("
Category id: " + category.getId());
System.out.println("Category name: " + category.getName());
}