Hibernate Native SQL

vembubalaji
8,661 views

Open Source Your Knowledge, Become a Contributor

Technology knowledge has to be shared and made accessible for free. Join the movement.

Create Content
Previous: Hibernate Native SQL - Introduction Next: Adding scalars

Use parameter binding in native SQL queries.

From JPA 2.1 onwards, to create native Queries, we can use the org.hibernate.Session object’s createNativeQuery method to create and execute Native Queries

List<Object[]> users1 = session.createNativeQuery(
	"SELECT user_Id,name,username,isadmin,region_id,team_id,domain_id FROM user").list();
			
users1.stream().forEach(objects -> {
				Integer id = (Integer) objects[0];
				String name = (String) objects[1];
				if (logger.isLoggable(Level.INFO)) {
					logger.info(String.format("User[ %d, %s ]", id, name));
				}
			});

In the above query, if there needs to be a where case, the same can be done via parameter binding

A quick sample. Check out the JAVA class and SQL file
// {...}
List<Object[]> users = session.createNativeQuery(
"SELECT user_Id,name,username,isadmin,region_id,team_id,domain_id FROM user u where u.user_id=?")
.setParameter(1, 1).list();
users.stream().forEach(objects -> {
Integer id = (Integer) objects[0];
String name = (String) objects[1];
System.out.println(String.format("Info: User[ %d, %s ]", id, name));
});
}
}
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
  • Here, we filter the query based on the user_id. In the query, the ‘?’ acts as a place-holder for setting the parameter and the .setParameter(1,1) sets the value to the place-holder. In the setParameter, the first parameter is the placeholder position and the send parameter is the value for the placeholder.
  • Hibernate uses the ResultSetMetadata to deduce the type of the columns returned by the query.
Open Source Your Knowledge: become a Contributor and help others learn. Create New Content