Hibernate Tutorials and Guides

Hibernate Native Query Example

By | | Updated : 2022-02-19 | Viewed : 179 times

Hibernate Native Query Example

The current article talks about hibernate native query concept. So, we are going to learn the hibernate native query with a nice example.

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.

Hibernate Native SQL Query Syntax
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.

Hibernate Native SQL Query Example
	// 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 ResultSetMetadata to identify the table's metadata such as number of columns, type of columns, name of columns, and Size of columns. Now, the Dialect class gives appropriate mappings for SQL types, and Finally, the resultset will be prepared. So to avoid the usage of ResultSetMetadata, We can pass types through addScalar() method in hibernate.

Hibernate Native SQL Query Example With addscalar
// 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.

Hibernate Native SQL Query Example With Multiple Tables
	// 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.

Hibernate Native SQL Query Example With Parameters
	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

Leave A Reply