In the last post I have showed you a very basic example of usage in Hibernate.
In this post I would like to show you all the different ways of executing a query while using Hibernate.
The examples in this post use the same example from the last post.
1. Using the session functions
The session object itself gives you methods to persist objects to the DB.
For example:
Session session = sessionFactory.openSession(); Contact fromDb = (Contact) session.load(Contact.class, 111); session.flush(); session.close();
This code will fetch a row from the DB with primary key=111 and convert it to Contact object.
Which is the equivalent to this SQL code:
SELECT* FROM CONTACT WHERE ID=111;
Another example:
Contact contact = new Contact(111,"avi", "yehuda", "aviyehuda@yahoo.com");
Session session = sessionFactory.openSession();
session.save(contact);
session.flush();
session.close();
This code will insert a new row to the table.
In other words this is the equivalent to this SQL code:
INSERT INTO CONTACT VALUES (111,'avi', 'yehuda', 'aviyehuda@yahoo.com');
Another example:
Contact contact = new Contact(111,"avi", "yehuda", "aviyehuda@yahoo.com");
Session session = sessionFactory.openSession();
session.update(contact);
session.flush();
session.close();
This code will update a row in the table where the primary key is 111.
In other words this is the equivalent to this SQL code:
UPDATE CONTACT SET FIRST_NAME='avi', LAST_NAME='yehuda', EMAIL='aviyehuda@yahoo.com' WHERE ID=111;
But as you probably noticed, the possibilities here are vary narrow. But there are other and better ways creating queries in Hibernate.
2. Using a Query object with HQL
HQL is a query language designed by Hibernate
It resembles SQL but has more options to it.
Example:
Session session = sessionFactory.openSession();
String hql = "from Contact where id=111";
Query query = session.createQuery(hql);
Contact fromDb =(Contact)query.uniqueResult();
session.flush();
session.close();
This will return a single result. That is why we use the function query.uniqueResult();
If you would like to return multiple rows use the function query.list();
Example:
Session session = sessionFactory.openSession();
String hql = "from Contact";
Query query = session.createQuery(hql);
List fromDb = query.list();
session.flush();
session.close();
3. Using a Query with HQL + parameter injections by name
Example:
Session session = sessionFactory.openSession();
Query query = session.createQuery("from Contact where id=:id");
query.setParameter("id", id);
Contact fromDb =(Contact)query.uniqueResult();
session.flush();
session.close();
The difference here as you can see, is that in the HQL string we write a parameter with ‘:’ in front of it. And after that we give it a value.
This way is good for 2 reasons:
1. Making static HQL strings where the parameter are injected dynamically.
2. If you have objects like dates, than the conversion is done automatically without having to deal with formats.
4. Using a Query with HQL + parameter injections by index
Example:
Session session = sessionFactory.openSession();
Query query = session.createQuery("from Contact where id=?");
query.setLong(111, id); //also possible - query.setParameter(0, id);
Contact fromDb =(Contact)query.uniqueResult();
session.flush();
session.close();
This is a lot like the last example, only here the parameters are injected by their index and not by name.
This is especially good if you have big tables with lots of columns.
5. Using named queries
Named queries are queries written in the hbm xml file in HQL.
Example:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="com.avi.Contact" table="CONTACT" lazy="true" >
<id name="id" type="long" column="ID" >
<generator class="assigned"/>
<!-- generator class="increment"/> -->
</id>
<property name="firstName" >
<column name="FIRSTNAME" />
</property>
<property name="lastName">
<column name="LASTNAME"/>
</property>
<property name="email">
<column name="EMAIL"/>
</property>
</class>
<query name="getAllContacts" >from Contact</query>
<query name="getContactById" >from Contact where id=?</query>
<query name="getContactByName" >from Contact where name=:name</query>
</hibernate-mapping>
To execute these queries you have to load them in your java code using the function session.getNamedQuery()
Example:
Session session = sessionFactory.openSession();
Query query = session.getNamedQuery("getContactById");
query.setLong(0, id);
Contact fromDb =(Contact)query.uniqueResult();
session.flush();
session.close();
Of course, same applies here, we can inject parameters by name or by index, and we can also use session.list() or session.uniqueResult().
6. Using native SQL
Using native SQL means the Hibernate will transfer the SQL query string to the DB itself.
You do that by calling the function session.createSQLQuery();
Session session = sessionFactory.openSession();
SQLQuery query =
session.createSQLQuery("SELECT FIRSTNAME from CONTACT WHERE ID="+id);
query.setLong(0, id);
Contact fromDb =(Contact)query.uniqueResult();
session.flush();
session.close();
7. Using native SQL with scalar
When using native SQL (as appose to HQL) the Hibernate can return raw data from the DB as objects but it has no idea what are the fields which this object consist of and what are their types.
Adding scalar to a native SQL will specify the fields and their types.
This is done by using the function SQLQuery.addScalar().
Example:
Session session = sessionFactory.openSession();
Query query =
session.createSQLQuery("select firstname from Contact where id="+id)
.addScalar("firstName", Hibernate.STRING).addScalar("lastName");
Object fromDb = query.uniqueResult();
session.flush();
session.close();
As you can see, you can specify the name and type of the field or you can just specify the fields name and let the Hibernate figure the type on it’s own.
8. Using native SQL with Entity
Using native SQl, with or without scalar, will return raw data. But there is better option, you can use native SQL and still get a business object as a result.
This is done by adding entities to the SQL query by using the function SQLQuery.addEntity().
Example:
Session session = sessionFactory.openSession();
Query query =
session.createSQLQuery("select firstname from Contact where id="+id)
.addEntity("contact",Contact.class);
Contact fromDb = (Contact)query.uniqueResult();
session.flush();
session.close();
9. Using named native SQL queries
Same as named HQL queries, you can create SQL queries in the hbm file and use it from inside the code.
Example:
<sql-query name="getContactById_SQL">
<return alias="contact" class="com.avi.Contact" />
SELECT * from CONTACT contact where ID=:id
</sql-query>
and the java function to use is also session.getNamedQuery.
Session session = sessionFactory.openSession();
Query query =
session.getNamedQuery("getContactById_SQL")
.setLong("id", id);
Contact fromDb = (Contact)query.uniqueResult();
session.flush();
session.close();
10. Using Criteria
Believe it or not, another way exits to make DB queries.
Criteria is a much more neat way to make queries. It may have all the options of SQL and HQL queries but it has all the basic options and it is very easy.
This is done by calling session.createCriteria().
Examples:
Returning all contacts ordered by id (descending)
Session session = sessionFactory.openSession();
Criteria criteria = session.createCriteria(Contact.class)
.addOrder(Order.desc("id"));
List fromDB = criteria.list();
session.flush();
session.close();
Returning all contacts where their first name starts with ‘A’
Session session = sessionFactory.openSession();
Criteria criteria = session.createCriteria(Contact.class)
.add( Restrictions.like("firstName", "A%") )
List fromDB = criteria.list();
session.flush();
session.close();
Criteria has a lot more options. It can be very useful to know how to use it.
download example