How to query a database using Hibernate?

Question

I understand some might simply answer this question with "Why didn't you just Google it"... But I did, and the more I researched this the more confused I got. I'm trying to query my database with Hibernate, the query has a 'where' clause.

Now creating a database entry is easy enough, in the case where I have a 'User' class, I simply do this:

// Gets a new session
Session session = HibernateUtil.getSessionFactory().openSession();
session.beginTransaction();

// Creates a new User object
User user = new User("John", "p@55w0rd*", "[email protected]");

// Save and commit
session.save(user);
session.getTransaction().commit();

But what do I do when I what to for instance

select * from Users where id = '3';

My Google searches pointed to something called HQL, which makes me wonder why I couldn't of just used straight JDBC then. Also it doesn't seem very object oriented. And then there's something like

session.createCriteria(.......

But I'm not sure how to use this.. Any help? Thanks guys.

The hibernate uses the HQL, which uses the ORM (object relational mapping). So if you want the regular jdbc query, you don't need to go for Hibernate. Still you want to use the jdbc related query, you may use the native query. — MGPJ
– MGPJ, Commented Jul 21, 2014 at 10:07
No I don't want to use JDBC, nor do I want to use HQL... or is there no other way? — user818700
– user818700, Commented Jul 21, 2014 at 10:16

Logan H · Accepted Answer · 2016-09-26 21:14:54Z

2

When you use Native Query (non HQL ) you need to tell hibernate explicitely to handle it like below :

In below query createSQLQuery is special function to handle native sql's

String sql = "SELECT * FROM EMPLOYEE WHERE id = :employee_id";
SQLQuery query = session.createSQLQuery(sql);
query.addEntity(User.class);
query.setParameter("employee_id", 3);
List<User> results = query.list();

This can be done using criteria as well for that following is good starting point:

  Criteria criteria = sess.createCriteria( User.class);
  List<User> users= criteria.list();

http://www.developerhelpway.com/framework/hibernate/criteria/index.php

edited Sep 26, 2016 at 21:14

Logan H

3,4332 gold badges35 silver badges50 bronze badges

answered Jul 21, 2014 at 10:12

Pramod S. Nikam

4,5628 gold badges41 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

rtc11 · Accepted Answer · 2014-07-21 10:21:57Z

0

First of all, you need a hibernate.cfg.xml which contains properties for hibernate. This is e.g url, username and password, the driver and dialect. This file is placed in a package called resources.

You have to choose between using Hibernate Annotations example

or using hbm.xml files example

This is how you tell hibernate what your database is like. It wil automatically create queries for you based on how you annotates or defines in e.g user.hbm.xml.

Create a HibernateUtil.java class which holds the session factory.

You can fetch data from the database with

Criteria crit = getSessionFactory().getCurrentSession().createCriteria(User.class);

Example using queries:

  List<?> hibTuppleResultList = currentSession.createQuery(
      "from Person p, Employment e "
    + "where e.orgno like ? and p.ssn = e.ssn and p" 
    + ".bankno = ?")
       .setString(0, orgNo).setString(1, bankNo).list();

 for (Object aHibTuppleResultList : hibTuppleResultList)
    {
        Object[] tuple = (Object[]) aHibTuppleResultList;
        Person person = (Person) tuple[0];
        hibList.add(person);
    }

edited Jul 21, 2014 at 10:21

answered Jul 21, 2014 at 10:07

rtc11

78810 silver badges23 bronze badges

1 Comment

user818700 Over a year ago

Thanks, I have my hibernate.cfg.xml with everything set up. Like I said I can successfully create entries in the database with my current hibernate setup, it's the querying part with 'where' clauses that I'm not sure about. Also I'm using annotations.

user818700 · Accepted Answer · 2014-07-21 10:25:27Z

0

In the end all I really wanted was to know that if you don't want to use HQL you get something called 'Criteria Queries', and that in my case I'd do something like this:

Criteria cr = session.createCriteria(User);
cr.add(Restrictions.eq("id", 3));
List results = cr.list();

Me: "Thanks!" Me: "No problem :)"

PS - we can really delete this question.

answered Jul 21, 2014 at 10:25

user818700

Comments

Ashish Ratan · Accepted Answer · 2014-07-22 05:27:21Z

0

Query q = session.createQuery("from User as u where u.id = :u.id");

q.setString("id", "3");

List result = q.list();

Query with Criteria:

Criteria cr = session.createCriteria(User.class);
List results = cr.list();

Restrictions with Criteria:

Criteria cr = session.createCriteria(User.class);
cr.add(Restrictions.eq("id", 3));
// You can add as many as Restrictions as per your requirement
List results = cr.list();

You could also use it like this

List results = session.createCriteria(User.class).add(Restrictions.eq("id", 3)).list();

Some example for Crieteria Rsetriction query

Criteria cr = session.createCriteria(Employee.class);

// To get records having salary more than 2000
cr.add(Restrictions.gt("salary", 2000));

// To get records having salary less than 2000
cr.add(Restrictions.lt("salary", 2000));

// To get records having fistName starting with zara cr.add(Restrictions.like("firstName", "zara%"));

// Case sensitive form of the above restriction. 
cr.add(Restrictions.ilike("firstName", "zara%"));

// To get records having salary in between 1000 and 2000
cr.add(Restrictions.between("salary", 1000, 2000));

// To check if the given property is null 
cr.add(Restrictions.isNull("salary"));

// To check if the given property is not null 
cr.add(Restrictions.isNotNull("salary"));

// To check if the given property is empty 
cr.add(Restrictions.isEmpty("salary"));

// To check if the given property is not empty
cr.add(Restrictions.isNotEmpty("salary"));

You can create AND or OR conditions using LogicalExpression restrictions as follows:

Criteria cr = session.createCriteria(Employee.class);

Criterion salary = Restrictions.gt("salary", 2000);
Criterion name = Restrictions.ilike("firstNname","zara%");

// To get records matching with OR condistions
LogicalExpression orExp = Restrictions.or(salary, name);
cr.add( orExp );


// To get records matching with AND condistions
LogicalExpression andExp = Restrictions.and(salary, name);
cr.add( andExp );

List results = cr.list();

I think this will help you

edited Jul 22, 2014 at 5:27

answered Jul 21, 2014 at 10:09

Ashish Ratan

2,8681 gold badge26 silver badges51 bronze badges

3 Comments

Pramod S. Nikam Over a year ago

in this HQL is used. Relborg dont want that

user818700 Over a year ago

Don't want no filthy HQL

Ashish Ratan Over a year ago

then u could use crieteria, crieterian, Restrictions etc.

Collectives™ on Stack Overflow

How to query a database using Hibernate?

4 Answers 4

Comments

1 Comment

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest