Hibernate Native SQL
vembubalaji
54.6K views
01 Hibernate Native SQL
Hibernate Native SQL - Introduction Use parameter binding in native SQL queries Adding scalars Native Joins Adding joins with entity mapping Basic Result set mapping using @SqlResultSetMapping Mapping Non-managed entities via result transformer Mapping Non-managed entities via result Custom Transformer
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));
});
}
}
1
- 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.
Create your playground on Tech.io
This playground was created on Tech.io, our hands-on, knowledge-sharing platform for developers.