Hibernate - How to retrieve data from the database

[Sethu Raman]

Hi all

Am just started learning hibernate. i tried all insert, update and delete except select. Some body help me in retrieve data from the database table. There are methods like save,update,delete in session object, whatsover for select?

Thanks in advance

[Paul Sturrock]

"Sethu"

Welcome to JavaRanch!

Could you please update your display name so it complies with our naming policy. You can do this here.

Thanks!
[ October 23, 2007: Message edited by: Paul Sturrock ]

[Paul Sturrock]

If you want to load an entity by its id, use the get or load methods of the Session class. If you want to find a list of objects based on some search criteria, you can do that again from the Session class with a Query, a set of Criteria or a native SQLQuery.

Does this make sense?

(And thank you for updating your display name)
[ October 24, 2007: Message edited by: Paul Sturrock ]

[Cameron Wallace McKenzie]

Here's a little class I call my CrudRunner. It demonstrates how to use Hibernate to do the basic Create Retrieve Update and Delete (destroy?) methods.

Here's the very simple table it works with:

 
drop table if exists examscam.user
create table examscam.user (id integer not null auto_increment, login_name varchar(255) unique, password varchar(255) not null, primary key (id))

Here is the entity that uses JPA annotations to map to the table:

  @Entity
@Table(name = "user", schema = "examscam")
public class User {
  private int id;private String loginName;
  private String password;private String encryptedPassword;
  
  @Transient
  public String getEncryptedPassword() {return encryptedPassword;}
  public void setEncryptedPassword(String ep) {
    this.encryptedPassword = ep;
  }
  
  @Id
  @GeneratedValue
  @Column(name = "id")
  public int getId() {return id;}
  public void setId(int id) {this.id = id;}
  
  @Column(name = "login_name", unique = true)
  public String getLoginName() {return loginName;}
  public void setLoginName(String ln) {this.loginName = ln;}
  
  @Column(name = "password", nullable=false)
  public String getPassword() {return password;}
  public void setPassword(String pass) {this.password = pass;}
 
}
  

And here's the CrudRunner with all of the basic CRUD operations:

  
package com.examscam.hib.exam;
import java.util.List; import org.hibernate.cfg.AnnotationConfiguration;
import org.hibernate.Query;import org.hibernate.Session;
import org.hibernate.SessionFactory;
public class CrudRunner {
 public static void main(String[] args) {
  CrudRunner.create();CrudRunner.retrieve();
  CrudRunner.retrieveFromId(1);CrudRunner.updateAll();
  CrudRunner.deleteAll();
 }
 public static void create() {
  AnnotationConfiguration config = new AnnotationConfiguration();
  config.addAnnotatedClass(User.class);
  SessionFactory factory= config.configure().buildSessionFactory();
  Session session = factory.getCurrentSession();
  session.beginTransaction();
  User user = new User();
  user.setLoginName("mj");user.setPassword("abc123");
  session.save(user);
  session.getTransaction().commit();
 }
 [B]
 public static void retrieve() {
  AnnotationConfiguration config = new AnnotationConfiguration();
  config.addAnnotatedClass(User.class);
  SessionFactory factory= config.configure().buildSessionFactory();
  Session session = factory.getCurrentSession();
  session.beginTransaction();
  Query queryResult = session.createQuery("from User");
  java.util.List allUsers;
  allUsers = queryResult.list();
  for (int i = 0; i < allUsers.size(); i++) {
   User user = (User) allUsers.get(i);
  }
 System.out.println("Database contents delivered..."); 
 }
 
 public static User retrieveFromId(int idValue) {
  AnnotationConfiguration config = new AnnotationConfiguration();
  config.addAnnotatedClass(User.class);
  SessionFactory factory= config.configure().buildSessionFactory();
  Session session = factory.getCurrentSession();
  session.beginTransaction();/*lets hope an id of 1 exists!*/
  String queryString = "from User where id = :id";
  Query query = session.createQuery(queryString);
  query.setInteger("id", idValue);
  Object queryResult = query.uniqueResult();
  User user = (User)queryResult;session.getTransaction().commit();
  return user;
 }
 [/B]
/* YES�THE INFAMOUS CrudRunner!!! J */
/* if the print is too small, you�re too old!!!   */
 
 public static void updateAll() {
  AnnotationConfiguration config = 
                           new AnnotationConfiguration();
  config.addAnnotatedClass(User.class);
  SessionFactory factory= 
                  config.configure().buildSessionFactory();
  Session session = factory.getCurrentSession();
  session.beginTransaction();
  List allUsers;
  System.out.println("Updating all records...");
  Query queryResult = session.createQuery("from User");
  allUsers = queryResult.list(); 
  System.out.println("# of rows: "+ allUsers.size());
  for (int i = 0; i < allUsers.size(); i++) {
   User user = (User) allUsers.get(i);
   System.out.println(user);
   user.setPassword("password");
   session.update(user);
  }
  System.out.println("Database contents updated...");
  session.getTransaction().commit();
 }
 public static void deleteAll() {
  AnnotationConfiguration config =  new AnnotationConfiguration();
  config.addAnnotatedClass(User.class);
  SessionFactory factory=config.configure().buildSessionFactory();
  Session session = factory.getCurrentSession();
  List allUsers;
  session.beginTransaction();
  Query queryResult = session.createQuery("from User");
  allUsers = queryResult.list(); 
  for (int i = 0; i < allUsers.size(); i++) {
   User user = (User) allUsers.get(i);
   System.out.println(user);
   session.delete(user);
  }
  session.getTransaction().commit();
 }
}
 
 
  

The retrieve method starts off with some redundant and repetitive code that basically initialized the Hibernate framework and gets our program ready to interact with the database. Again, you will notice that a Hibernate session is obtainined from the SessionFactory. This session then begins a transaction for interacting with the database. We�ll eventually look at factoring this repetitive code out into a helper method or something, but for now, we�ll leave it in, just for the sake of simplicity.


/* your standard Hibernate connection code */
AnnotationConfiguration config =
new AnnotationConfiguration();
config.addAnnotatedClass(User.class);
SessionFactory factory;
factory = config.configure().buildSessionFactory();
Session session = factory.getCurrentSession();
session.beginTransaction();

Querying the Database
After the Hibernate session is obtained, we declare an object of type java.util.List called allUsers. Basically, when we do a standard, non-unique query of the database, we can expect a resultset to be returned to us in the form of a java.util.List. The allUsers list will hold the results of our query.

java.util.List allUsers;

From there, and this is a pretty important point, the createQuery method is invoked on the Hibernate session, with the argument �from User� being passed in as an argument.


Query queryResult=session.createQuery("from User");
allUsers = queryResult.list();

Say Hello to Hibernate Query Language (HQL)
As you have probably guessed, the String statement �from User�, which is passed into the Hibernate session�s createQuery method, is a special type of SQL statement. The �from User� String can be thought of as the equivalent to issuing an SQL query of SELECT * FROM USER.


Query queryResult=session.createQuery("from User");
allUsers = queryResult.list();


However, don�t be fooled by what may appear on the surface to be great similarity between standard SQL and the query language used by Hibernate. The Hibernate Query Language, HQL, is actually an object-oriented data query language, meaning that HQL understands object oriented concepts such as association, inheritance, aggregation and polymorphism. The power of HQL will become increasingly evident when we move away from simple SELECT * queries.
Execution of the createQuery(�from User�) method returns a Hibernate object called a Query, which when populated, contains all of the data returned from executing the HQL statement, all of which is stored as a collection of JavaBeans. These JavaBeans can be easily accessed by asking the Query object to provide its encapsulated data as either a java.util.Iterator or a java.util.List. The Query�s methods to do so are accordingly named iterate() and list(). In our example, we obtain the results of the Query as a list.


allUsers = queryResult.list();


Looping through the List
So, after executing an HQL query, we get ourselves a Query object, and this Query object can spill its contents out in the form of a java.util.List object. I guess the big question is: what does this java.util.List contain?
Well, it contains instances of the User class we created to represent the data in the user table of the database. Looping through the list, and extracting the User class out of the List, with a combination of the List�s get(int) method and a handy cast, gives us access to our very own User object!


User user = (User) allUsers.get(i);


Once you have the User object extracted from the List, and cast into the appropriate type, it is completely up to the you with regards to what you want to do with it. In this example, we simply print out the name of the user instance to the console, which, given the fact that I�m looping through every instance in the List, will provide me the names of all of the entries in the user table of the database. Here�s the for loop that does it:


for (int i = 0; i < allUsers.size(); i++) {
User user = (User) allUsers.get(i);
System.out.println(user.getLoginName());
}


And that�s pretty much it! To retrieve data from the database, you simply leverage the Hibernate session�s createQuery method, pass in some HQL, convert the guts of the returned Query object into an easy to use List, and then loop through the collection of your POJOs contained in the List. It�s just that easy!

-Cameron McKenzie
[ October 24, 2007: Message edited by: Cameron McKenzie ]

[Anthony Bailey]

What does the hibernate config file look like? I am getting the following error when using your retrieve example. I have not used any other methods yet.

Here is mine.

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="connection.url">jdbc:postgresql:CORMDBASE</property>
<property name="connection.driver_class">org.postgresql.Driver</property>
<property name="connection.username">postgres</property>
<property name="connection.password">admin</property>
<!-- DB schema will be updated if needed -->
<!-- <property name="hbm2ddl.auto">update</property> -->

<!-- SQL Dialect -->
<property name="dialect">org.hibernate.dialect.PostgreSQLDialect</property>

<!-- Bean Mapping -->
<mapping class="org.christianassistant.persistance.MembersEntity"/>

</session-factory>
</hibernate-configuration>