Hibernate Native SQL
Open Source Your Knowledge, Become a Contributor
Technology knowledge has to be shared and made accessible for free. Join the movement.
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
// {
package com.tu.nativesqlsample;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.hibernate.Session;
import com.tu.hibernate.HibernateUtil;
public class NativeQueryParameterBinding {
static Logger logger = Logger.getLogger(NativeQueryNativeJoinWithEntity.class.getName());
static String dash = "--------------------------------------------------------------";
public static void main(String[] args) {
Session session = null;
try {
session = HibernateUtil.getSessionFactory().openSession();
logger.info(dash);
// 2.2 Native query - parameter binding
parameterBinding(session);
} catch (Exception e) {
logger.warning(e.toString());
} finally {
if (session != null) {
session.close();
}
}
HibernateUtil.shutdown();
}
@SuppressWarnings("unchecked")
public static void parameterBinding(Session session) {
// }
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));
});
}
}
Press desired key combination and then press ENTER.
1
Press desired key combination and then press ENTER.
- 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