Hibernate Native Query Example
By AmarSivas | | Updated : 2022-02-19 | Viewed : 75 times

The current article talks about hibernate native query concept. So, we are going to learn the hibernate native query with a nice example.
Table of Contents:
Hibernate Native Query
Sometimes, we need to write the database queries to explore the database\'s specific features. So it is only possible to perform some of the operations (like DDL) in the database only with the database client as it is not possible with hibernate HQL. So we better use the native SQL query options to perform the required operations. And hibernate 3.x also allows to perform store procedures and other curd operations in DB.
Hibernate Native Query Example
In Hibernate Native Query is created by Session's method which is createSQLQuery(). Notice below given syntax for creating the Native Query in Hibernate.
Query query = session.createSQLQuery("select book_id, book_name, book_price from Book");
We will now see different ways of using Native SQL Query examples.
Hibernate Native Sql Select Query Example
Query String is very simple and not possible to pass the dynamic values into the query if required anything.
// Get All books
Query query = session.createSQLQuery("select book_id, book_name, book_price from Book");
List<Object[]> bookList = query.list();
for (Object[] row : bookList) {
Book book = new Book();
book.setBookId(Long.parseLong(row[0].toString()));
book.setBookName(row[1].toString());
book.setBookPrice(Double.parseDouble(row[2].toString()));
System.out.println(book);
}
Hibernate Native SQL Query Example With addscalar
While preparing the resultset as an object array Hibernate will use
// Get All books with setting up hibernate data types
query = session.createSQLQuery("select book_id, book_name, book_price from Book")
.addScalar("book_id", new LongType())
.addScalar("book_name", new StringType())
.addScalar("book_price", new DoubleType());
bookList = query.list();
for (Object[] row : bookList) {
Book book = new Book();
book.setBookId(Long.parseLong(row[0].toString()));
book.setBookName(row[1].toString());
book.setBookPrice(Double.parseDouble(row[2].toString()));
System.out.println(book);
}
Hibernate Native SQL Query Example With Multiple Tables
Sometimes Join is required in the query to make to get the combined result of those two tables. We will see now this kind of example given below.
// Get multiple tables like Book and Author
query = session.createSQLQuery("select {b.*}, {a.*} from Book b join Author a ON b.author_id=a.author_id")
.addEntity("b", Book.class)
.addJoin("a", "b.author");
bookList = query.list();
for (Object[] row : bookList) {
for (Object obj : row) {
System.out.print(obj + "::");
}
System.out.println("\n");
}
for (Object[] row : bookList) {
Book e = (Book) row[0];
System.out.println("Employee Info::" + e);
Author a = (Author) row[1];
System.out.println("Address Info::" + a);
}
Hibernate Native SQL Query Example With Parameters
As we noticed it is not possible to pass the parameter into the query for that we can use below.
query = session
.createSQLQuery("select book_id, book_name, book_price from Book where book_id = ?1");
List<Object[]> bookData = query.setLong(1, 33L).list();
for (Object[] row : bookData) {
Book book = new Book();
book.setBookId(Long.parseLong(row[0].toString()));
book.setBookName(row[1].toString());
book.setBookPrice(Double.parseDouble(row[2].toString()));
System.out.println(book);
}
query = session
.createSQLQuery("select book_id, book_name, book_price from Book where book_id = :id");
bookData = query.setLong("id", 35L).list();
for (Object[] row : bookData) {
Book book = new Book();
book.setBookId(Long.parseLong(row[0].toString()));
book.setBookName(row[1].toString());
book.setBookPrice(Double.parseDouble(row[2].toString()));
System.out.println(book);
}
Please refer to the GitHub Repo for Hibernate-Native-Query-Example-App