Hibernate Query
- If you do not know the identifiers of the objects you are looking for, you need a query.
- Hibernate supports an easy-to-use but powerful object oriented query language (HQL).
- For programmatic query creation, Hibernate supports a sophisticated Criteria and Example query feature (QBC and QBE).
- You can also express your query in the native SQL of your database, with optional support from Hibernate for result set conversion into objects.
hibernate.cfg.xml
<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="connection.username">root</property>
<property name="connection.url">jdbc:mysql://localhost:3306/javaskoolDB</property>
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="pfname">javaskoolMySQLDriver</property>
<property name="connection.password">admin</property>
<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hbm2ddl.auto">update</property>
<property name="show_sql">true</property>
<mapping resource="com/javaskool/item.hbm.xml" />
</session-factory>
</hibernate-configuration>
Item.java
package com.javaskool;
//Item Bean
public class Item {
//Item Property
private Integer itemId;
private String itemName;
private Integer qty;
//Item Methods
public Integer getItemId() {
return itemId;
}
public void setItemId(Integer itemId) {
this.itemId = itemId;
}
public String getItemName() {
return itemName;
}
public void setItemName(String itemName) {
this.itemName = itemName;
}
public Integer getQty() {
return qty;
}
public void setQty(Integer qty) {
this.qty = qty;
}
}
item.hbm.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.javaskool">
<class name="Item" table="ItemList">
<id name="ItemId" column="itemId">
<generator class="increment"></generator>
</id>
<property name="itemName" column="itemName"></property>
<property name="qty" column="qty"></property>
</class>
</hibernate-mapping>
</pre>
TestDrive.java
package com.javaskool;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
public class TestDrive {
public static void main(String[] args) {
Item iObj=new Item();
iObj.setItemName("Mouse");
iObj.setQty(30);
Configuration config=new Configuration().configure();
SessionFactory sf=config.buildSessionFactory();
Session ss=sf.openSession();
Transaction tran=ss.beginTransaction();
ss.save(iObj);
tran.commit();
ss.flush();
ss.close();
}
}
Output
mysql> select * from itemList;
+--------+----------+------+
| itemId | itemName | qty |
+--------+----------+------+
| 1 | Mouse | 30 |
+--------+----------+------+
1 row in set (0.00 sec)
hibernate.cfg.xml
<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="connection.username">root</property>
<property name="connection.url">jdbc:mysql://localhost:3306/javaskoolDB</property>
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="pfname">javaskoolMySQLDriver</property>
<property name="connection.password">admin</property>
<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hbm2ddl.auto">update</property>
<property name="show_sql">true</property>
<mapping resource="com/javaskool/item.hbm.xml" />
</session-factory>
</hibernate-configuration>
Item.java
package com.javaskool;
//Item Bean
public class Item {
//Item Property
private Integer itemId;
private String itemName;
private Integer qty;
//Item Methods
public Integer getItemId() {
return itemId;
}
public void setItemId(Integer itemId) {
this.itemId = itemId;
}
public String getItemName() {
return itemName;
}
public void setItemName(String itemName) {
this.itemName = itemName;
}
public Integer getQty() {
return qty;
}
public void setQty(Integer qty) {
this.qty = qty;
}
}
item.hbm.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.javaskool">
<class name="Item" table="ItemList">
<id name="ItemId" column="itemId">
<generator class="increment"></generator>
</id>
<property name="itemName" column="itemName"></property>
<property name="qty" column="qty"></property>
</class>
</hibernate-mapping>
TestDrive.java
package com.javaskool;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
public class TestDrive {
public static void main(String[] args) {
//1. configuring hibernate
Configuration confi=new Configuration().configure();
//2. create sessionfactory
SessionFactory sf=confi.buildSessionFactory();
//3. Get Session object
Session sess=sf.openSession();
//4. Starting Transaction
Transaction tran=sess.beginTransaction();
//5. Loading items from session object
Item i1=(Item)sess.get(Item.class, 2);
//6. Changing value for the row
i1.setItemName("Notebook");
i1.setQty(99);
//7. Updating row
sess.update(i1);
// also
// sess.saveOrUpdate(i1);
tran.commit();
}
}
Hibernate Generated Query
Hibernate: select item0_.itemId as itemId0_0_, item0_.itemName as itemName0_0_, item0_.qty as qty0_0_ from ItemList item0_ where item0_.itemId=?
Hibernate: update ItemList set itemName=?, qty=? where itemId=?
Output: After Update
mysql> select * from itemList;
+--------+----------+------+
| itemId | itemName | qty |
+--------+----------+------+
| 1 | Mouse | 30 |
| 2 | Notebook | 99 |
| 3 | monitor | 50 |
+--------+----------+------+
3 rows in set (0.00 sec)
Before Update
mysql> select * from itemList;
+--------+----------+------+
| itemId | itemName | qty |
+--------+----------+------+
| 1 | Mouse | 30 |
| 2 | keyboard | 40 |
| 3 | monitor | 50 |
+--------+----------+------+
3 rows in set (0.01 sec)
hibernate.cfg.xml
<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="connection.username">root</property>
<property name="connection.url">jdbc:mysql://localhost:3306/javaskoolDB</property>
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="pfname">javaskoolMySQLDriver</property>
<property name="connection.password">admin</property>
<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hbm2ddl.auto">update</property>
<property name="show_sql">true</property>
<mapping resource="com/javaskool/item.hbm.xml" />
</session-factory>
</hibernate-configuration>
Item.java
package com.javaskool;
//Item Bean
public class Item {
//Item Property
private Integer itemId;
private String itemName;
private Integer qty;
//Item Methods
public Integer getItemId() {
return itemId;
}
public void setItemId(Integer itemId) {
this.itemId = itemId;
}
public String getItemName() {
return itemName;
}
public void setItemName(String itemName) {
this.itemName = itemName;
}
public Integer getQty() {
return qty;
}
public void setQty(Integer qty) {
this.qty = qty;
}
}
item.hbm.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.javaskool">
<class name="Item" table="ItemList">
<id name="ItemId" column="itemId">
<generator class="increment"></generator>
</id>
<property name="itemName" column="itemName"></property>
<property name="qty" column="qty"></property>
</class>
</hibernate-mapping>
TestDrive.java
package com.javaskool;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
public class TestDrive {
public static void main(String[] args) {
//1. configuring hibernate
Configuration confi=new Configuration().configure();
//2. create sessionfactory
SessionFactory sf=confi.buildSessionFactory();
//3. Get Session object
Session sess=sf.openSession();
//4. Starting Transaction
Transaction tran=sess.beginTransaction();
//5. Loading items from session object
Item i1=(Item)sess.get(Item.class, 2);
//6. Deleting row
sess.delete(i1);
tran.commit();
}
}
Hibernate Generated Query
Hibernate: select item0_.itemId as itemId0_0_, item0_.itemName as itemName0_0_, item0_.qty as qty0_0_ from ItemList item0_ where item0_.itemId=?
Hibernate: delete from ItemList where itemId=?
Output:-After Delete
mysql> select * from itemList;
+--------+----------+------+
| itemId | itemName | qty |
+--------+----------+------+
| 1 | Mouse | 30 |
| 3 | monitor | 50 |
+--------+----------+------+
2 rows in set (0.00 sec)
Output:-Before Delete
mysql> select * from itemList;
+--------+----------+------+
| itemId | itemName | qty |
+--------+----------+------+
| 1 | Mouse | 30 |
| 2 | Notebook | 99 |
| 3 | monitor | 50 |
+--------+----------+------+
3 rows in set (0.00 sec)
hibernate.cfg.xml
<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="connection.username">root</property>
<property name="connection.url">jdbc:mysql://localhost:3306/javaskoolDB</property>
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="pfname">javaskoolMySQLDriver</property>
<property name="connection.password">admin</property>
<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hbm2ddl.auto">update</property>
<property name="show_sql">true</property>
<mapping resource="com/javaskool/item.hbm.xml" />
</session-factory>
</hibernate-configuration>
Item.java
package com.javaskool;
//Item Bean
public class Item {
//Item Property
private Integer itemId;
private String itemName;
private Integer qty;
//Item Methods
public Integer getItemId() {
return itemId;
}
public void setItemId(Integer itemId) {
this.itemId = itemId;
}
public String getItemName() {
return itemName;
}
public void setItemName(String itemName) {
this.itemName = itemName;
}
public Integer getQty() {
return qty;
}
public void setQty(Integer qty) {
this.qty = qty;
}
}
item.hbm.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.javaskool">
<class name="Item" table="ItemList" lazy="false">
<id name="ItemId" column="itemId">
<generator class="increment"></generator>
</id>
<property name="itemName" column="itemName"></property>
<property name="qty" column="qty"></property>
</class>
</hibernate-mapping>
TestDrive.java
package com.javaskool;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
public class TestDrive {
public static void main(String[] args) {
//1. configuring hibernate
Configuration confi=new Configuration().configure();
//2. create sessionfactory
SessionFactory sf=confi.buildSessionFactory();
//3. Get Session object
Session sess=sf.openSession();
//4. Starting Transaction
Transaction tran=sess.beginTransaction();
//5. Loading items from session object
Item i1=(Item)sess.load(Item.class, 2);
System.out.println(i1.getItemId() +" "+i1.getItemName()+" "+i1.getQty());
}
/*
* Lazy setting decides whether to load child objects
* while loading the Parent Object.
* You need to do this setting respective hibernate mapping file
* of the parent class. Lazy = true (means not to load child)
* By default the lazy loading of the child objects is true
* lazy=false ==> it is eager loading
*/
}
Output
Hibernate: select item0_.itemId as itemId0_0_, item0_.itemName as itemName0_0_, item0_.qty as qty0_0_ from ItemList item0_ where item0_.itemId=?
2 keyboard 40
hibernate.cfg.xml
<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="connection.username">root</property>
<property name="connection.url">jdbc:mysql://localhost:3306/javaskoolDB</property>
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="pfname">javaskoolMySQLDriver</property>
<property name="connection.password">admin</property>
<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="hbm2ddl.auto">update</property>
<property name="show_sql">true</property>
<mapping resource="com/javaskool/item.hbm.xml" />
</session-factory>
</hibernate-configuration>
Item.java
package com.javaskool;
//Item Bean
public class Item {
//Item Property
private Integer itemId;
private String itemName;
private Integer qty;
//Item Methods
public Integer getItemId() {
return itemId;
}
public void setItemId(Integer itemId) {
this.itemId = itemId;
}
public String getItemName() {
return itemName;
}
public void setItemName(String itemName) {
this.itemName = itemName;
}
public Integer getQty() {
return qty;
}
public void setQty(Integer qty) {
this.qty = qty;
}
}
item.hbm.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.javaskool">
<class name="Item" table="ItemList">
<id name="ItemId" column="itemId">
<generator class="increment"></generator>
</id>
<property name="itemName" column="itemName"></property>
<property name="qty" column="qty"></property>
</class>
</hibernate-mapping>
</pre>
TestDrive.java
package com.javaskool;
import java.util.List;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
public class TestDrive {
public static void main(String[] args) {
//1. configuring hibernate
Configuration confi=new Configuration().configure();
//2. create sessionfactory
SessionFactory sf=confi.buildSessionFactory();
//3. Get Session object
Session sess=sf.openSession();
//4. Starting Transaction
Transaction tran=sess.beginTransaction();
//5. listing items
List<Item> items=sess.createCriteria(Item.class).list();
System.out.println("ItemId \t ItemName \t Qty");
for(Item i: items)
{
System.out.println(i.getItemId()+"\t"+i.getItemName()+"\t"+i.getQty());
}
tran.commit();
}
}
Output
+--------+----------+------+
| itemId | itemName | qty |
+--------+----------+------+
| 1 | Mouse | 30 |
| 2 | keyboard | 40 |
| 3 | monitor | 50 |
+--------+----------+------+
3 rows in set (0.00 sec)
Pagination
If you need to specify bounds upon your result set, that is, the maximum number of rows you want to retrieve and/or the first row you want to retrieve, you can use methods of the Query interface:
Query q = sess.createQuery("from Student s");
q.setFirstResult(20);
q.setMaxResults(10);
List cats = q.list();
Hibernate knows how to translate this limit query into the native SQL of your DBMS.
Iterating results
- Occasionally, you might be able to achieve better performance by executing the query using the iterate() method.
- This will usually be the case if you expect that the actual entity instances returned by the query will already be in the session or second-level cache.
- If they are not already cached, iterate() will be slower than list() and might require many database hits for a simple query, usually 1 for the initial select which only returns identifiers, and n additional selects to initialize the actual instances.
// fetch ids
Iterator iter = sess.createQuery("from eg.Qux q order by q.likeliness").iterate();
while ( iter.hasNext() ) {
Qux qux = (Qux) iter.next(); // fetch the object
// something we couldnt express in the query
if ( qux.calculateComplicatedAlgorithm() ) {
// delete the current instance
iter.remove();
// dont need to process the rest
break;
}
}
- Queries can specify a property of a class in the select clause.
-
They can even call SQL aggregate functions.
-
Properties or aggregates are considered “scalar” results and not entities in persistent state.
Iterator results = sess.createQuery(
"select cat.color, min(cat.birthdate), count(cat) from Cat cat " +
"group by cat.color")
.list()
.iterator();
while ( results.hasNext() ) {
Object[] row = (Object[]) results.next();
Color type = (Color) row[0];
Date oldest = (Date) row[1];
Integer count = (Integer) row[2];
.....
}
Bind parameters
- Methods on Query are provided for binding values to named parameters or JDBC-style ? parameters.
-
Contrary to JDBC, Hibernate numbers parameters from zero.
-
Named parameters are identifiers of the form :name in the query string.
The advantages of named parameters are as follows:
- named parameters are insensitive to the order they occur in the query string
-
they can occur multiple times in the same query
-
they are self-documenting
//named parameter (preferred)
Query q = sess.createQuery("from DomesticCat cat where cat.name = :name");
q.setString("name", "Fritz");
Iterator cats = q.iterate();//positional parameter
Query q = sess.createQuery("from DomesticCat cat where cat.name = ?");
q.setString(0, "Izi");
Iterator cats = q.iterate();//named parameter list
List names = new ArrayList();
names.add("Izi");
names.add("Fritz");
Query q = sess.createQuery("from DomesticCat cat where cat.name in (:namesList)");
q.setParameterList("namesList", names);
List cats = q.list();
Scrollable iteration
If your JDBC driver supports scrollable ResultSets, the Query interface can be used to obtain a ScrollableResults object that allows flexible navigation of the query results.
Query q = sess.createQuery("select cat.name, cat from DomesticCat cat " + "order by cat.name");
ScrollableResults cats = q.scroll();
if ( cats.first() ) {
// find the first name on each page of an alphabetical list of cats by name
firstNamesOfPages = new ArrayList();
do {
String name = cats.getString(0);
firstNamesOfPages.add(name);
}
while ( cats.scroll(PAGE_SIZE) );
// Now get the first page of cats
pageOfCats = new ArrayList();
cats.beforeFirst();
int i=0;
while( ( PAGE_SIZE > i++ ) && cats.next() ) pageOfCats.add( cats.get(1) );
}
cats.close();
Note that an open database connection and cursor is required for this functionality. Use setMaxResult() / setFirstResult() if you need offline pagination functionality.
Replicating object between two different Data stores
It is sometimes useful to be able to take a graph of persistent instances and make them persistent in a different datastore, without regenerating identifier values.
//retrieve a cat from one database
Session session1 = factory1.openSession();
Transaction tx1 = session1.beginTransaction();
Cat cat = session1.get(Cat.class, catId);
tx1.commit();
session1.close();
//reconcile with a second database
Session session2 = factory2.openSession();
Transaction tx2 = session2.beginTransaction();
session2.replicate(cat, ReplicationMode.LATEST_VERSION);
tx2.commit();
session2.close();
The ReplicationMode determines how replicate() will deal with conflicts with existing rows in the database:
- ReplicationMode.IGNORE :ignores the object when there is an existing database row with the same identifier.
-
ReplicationMode.OVERWRITE :overwrites any existing database row with the same identifier
-
ReplicationMode.EXCEPTION : throws an exception if there is an existing database row with the same identifier
-
ReplicationMode.LATEST_VERSION :overwrites the row if its version number is earlier than the version number of the object, or ignore the object otherwise
Usecases for this feature include reconciling data entered into different database instances, upgrading system configuration information during product upgrades, rolling back changes made during non-ACID transactions and more.
Using metadata
- Hibernate requires a rich meta-level model of all entity and value types.
-
This model can be useful to the application itself.
-
For example, the application might use Hibernate’s metadata to implement a “smart” deep-copy algorithm that understands which objects should be copied (eg. mutable value types) and which objects that should not (e.g. immutable value types and, possibly, associated entities).
Hibernate exposes metadata via the ClassMetadata and CollectionMetadata interfaces and the Type hierarchy. Instances of the metadata interfaces can be obtained from the SessionFactory.
Cat fritz = ......;
ClassMetadata catMeta = sessionfactory.getClassMetadata(Cat.class);
Object[] propertyValues = catMeta.getPropertyValues(fritz);
String[] propertyNames = catMeta.getPropertyNames();
Type[] propertyTypes = catMeta.getPropertyTypes();
// get a Map of all properties which are not collections or associations
Map namedValues = new HashMap();
for ( int i=0; i <propertyNames.length; i++ ) {
if ( !propertyTypes[i].isEntityType() && !propertyTypes[i].isCollectionType() ) {
namedValues.put( propertyNames[i], propertyValues[i] );
}
}
Flushing the Session
Sometimes the Session will execute the SQL statements needed to synchronize the JDBC connection’s state with the state of objects held in memory. This process, called flush, occurs by default at the following points:
-
before some query executions
-
from org.hibernate.Transaction.commit()
-
from Session.flush()
The SQL statements are issued in the following order:
- all entity insertions in the same order the corresponding objects were saved using Session.save()
-
all entity updates
-
all collection deletions
-
all collection element deletions, updates and insertions
-
all collection insertions
-
all entity deletions in the same order the corresponding objects were deleted using Session.delete()
-
An exception is that objects using native ID generation are inserted when they are saved.
Except when you explicitly flush(), there are absolutely no guarantees about when the Session executes the JDBC calls, only the order in which they are executed. However, Hibernate does guarantee that the Query.list(..) will never return stale or incorrect data.
It is possible to change the default behavior so that flush occurs less frequently. The FlushMode class defines three different modes: only flush at commit time when the Hibernate Transaction API is used, flush automatically using the explained routine, or never flush unless flush() is called explicitly. The last mode is useful for long running units of work, where a Session is kept open and disconnected for a long time.
sess = sf.openSession();
Transaction tx = sess.beginTransaction();
sess.setFlushMode(FlushMode.COMMIT); // allow queries to return stale state
Cat izi = (Cat) sess.load(Cat.class, id);
izi.setName(iznizi);
// might return stale data
sess.find("from Cat as cat left outer join cat.kittens kitten");
// change to izi is not flushed!
...
tx.commit(); // flush occurs
sess.close();
Click the below Link to download complete Example
Recent Comments