Three ways of querying hibernate

Posted by DavidT on Fri, 10 Jul 2020 17:19:12 +0200

There are three main query modes common to hibernate: HQL, QBC (named query), and using native SQL query (SqlQuery)

HQL Query

* HQL (Hibernate Query Language) provides a rich and flexible way of querying, and using HQL for querying is also Hibernate's official recommended way of querying.

* HQL is very similar in syntax structure to SQL statements, so it can be used quickly.Using HQL requires using a Query object in Hibernate that specifically performs HQL-style operations.

1
2
3
4
5
6
7
8
session.beginTransaction();
String hql = "from User"; // from is followed by the object to be queried, not the table
Query query = session.createQuery(hql);
List<User> userList = query.list();
for(User user:userList){
  System.out.println(user.getUserName());
}
session.getTransaction().commit();


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
session.beginTransaction();
String hql = "from User where userName = 'James'";
Query query = session.createQuery(hql);
List<User> userList = query.list();
for(User user:userList){
  System.out.println(user.getUserName());
}
session.getTransaction().commit();
/*
 The attribute name of the persisted object is used in the where statement in HQL, such as userName in the example above.Aliases can also be used in HQL, of course
*/
String hql = "from User as u where u.userName = 'James'";
/*
Filter condition 
Various fi lt er conditions can also be used in where statements, such as: =, <>, <, >, >=, <=, between, not between,
in,not in,is,like,and,or etc.
*/

   HQL Placeholder
1
2
3
4
5
6
7
8
9
10
11
12
session.beginTransaction();
String hql = "from User where userName = ?";
Query query = session.createQuery(hql);
// Index starts at 0 
query.setString(0, "James");
List<User> userList = query.list();
for(User user:userList){
  System.out.println(user.getUserName());
}
session.getTransaction().commit();


2. QBC (Query By Criteria) Query

* Criteria objects provide an object-oriented way to query databases.The Criteria object needs to be obtained using the Session object.

* A Criteria object represents a query for a persisted class.

Query all
1
2
3
4
5
6
7
8
9
10
11
session.beginTransaction();
Criteria c = session.createCriteria(User.class);
List<User> userList = c.list();
for(User user:userList){
  System.out.println(user.getUserName());
}
session.getTransaction().commit();

 

                        where
1
2
3
4
5
6
7
8
9
10
11
session.beginTransaction();
Criteria c = session.createCriteria(User.class);
c.add(Restrictions.eq("userName", "James"));
List<User> userList = c.list();
for(User user:userList){
  System.out.println(user.getUserName());
}
session.getTransaction().commit();

1
2
3
4
5
6
Criteria c = session.createCriteria(User.class);
c.add(Restrictions.like("userName", "J"));
c.add(Restrictions.eq("id", 120));
c.add(Restrictions.or(Restrictions.eq("userName", "James"),
  Restrictions.eq("userName", "Alex")));

1
2
3
4
5
6
7
8
9
10
session.beginTransaction();
Criteria c = session.createCriteria(User.class);
c.add(Restrictions.eq("id", 120));
User user = (User) c.uniqueResult();
System.out.println(user.getUserName());
session.getTransaction().commit();




3. Native SQL Query:

session.beginTransaction();
String sql = "select id,username,userpwd from t_user";
List list = session.createSQLQuery(sql).list();
for(Object item : list){
  Object[] rows = (Object[]) item;
  System.out.println("id:" + rows[0] + "username:"
    + rows[1] + "userpwd:" + rows[2]);
}
session.getTransaction().commit();

Topics: Session SQL Hibernate Attribute