Executing a native SQL query

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.

How to do it…

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

Scalar 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:

Code

SQLQuery sqlQuery = session.createSQLQuery("SELECT * FROM product");

List<Object[]> list = sqlQuery.list();
for(Object[] object : list){
  System.out.println("\nId: " + object[0]);
  System...