Friday, 26 December 2014

HQL : select clause


“select” clause provides more control over the results than “from” clause. In previous post, we have seen “from” clause return all coulnmn names from table. By using select clause, we can select the columns in need. In this post I am going to explain, select clause to fetch only firstName field from employee class. I will explain how to fetch multiple columns using select clause in subsequent posts.

package myFirstHibernate;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;


@Entity
public class Employee {
  @Id @GeneratedValue
  private int id;
  private String firstName;
  private String lastName;
  
  public int getId() {
    return id;
  }
  
  public void setId(int id) {
    this.id = id;
  }
  
  public String getFirstName() {
    return firstName;
  }
  
  public void setFirstName(String firstName) {
    this.firstName = firstName;
  }
  
  public String getLastName() {
    return lastName;
  }
  
  public void setLastName(String lastName) {
    this.lastName = lastName;
  }
  
}


hibernate.cfg.xml
<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>

  <session-factory>
  
    <!--  Database Connection settings -->
    <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
    <property name="connection.url">jdbc:mysql://localhost/sample</property>
    <property name="connection.username">root</property>
    <property name="connection.password">tiger</property>
    
    <!-- Enable the logging of all the generated SQL statements to the console -->
    <property name="show_sql">true</property>
    
    <!-- Format the generated SQL statement to make it more readable, -->
    <property name="format_sql">false</property>
    
    <!-- Hibernate will put comments inside all generated SQL statements to hint what’s the generated SQL trying to do -->
    <property name="use_sql_comments">false</property>
    
    <!-- This property makes Hibernate generate the appropriate SQL for the chosen database. -->
    <property name="dialect">org.hibernate.dialect.MySQLDialect</property>
    
    <!-- Drop and re-create the database schema on startup -->
    <property name="hbm2ddl.auto">create</property>
    
    <!-- mappings for annotated classes -->
    <mapping class="myFirstHibernate.Employee" />
    
  </session-factory>
  
</hibernate-configuration>

package myFirstHibernate;

import java.util.List;
import java.util.Iterator;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.cfg.Configuration;

public class TestEmployee {
  
  /* Step 1: Create session factory */
  private static SessionFactory getSessionFactory() {
    Configuration configuration = new Configuration().configure();
    StandardServiceRegistryBuilder builder = new StandardServiceRegistryBuilder().
    applySettings(configuration.getProperties());
    SessionFactory factory = configuration.buildSessionFactory(builder.build());
        return factory;
    }
  
  public static void main(String args[]){
    Employee emp1 = new Employee();
    Employee emp2 = new Employee();
    Employee emp3 = new Employee();
    Employee emp4 = new Employee();
    Employee emp5 = new Employee();
    Employee emp6 = new Employee();
    Employee emp7 = new Employee();
    Employee emp8 = new Employee();
    
    emp1.setFirstName("Hari Krishna");
    emp1.setLastName("Gurram");
    
    emp2.setFirstName("Shreyas");
    emp2.setLastName("Desai");
    
    emp3.setFirstName("Piyush");
    emp3.setLastName("Rai");
    
    emp4.setFirstName("Rama Krishna");
    emp4.setLastName("Gurram");
    
    emp5.setFirstName("Sailaja");
    emp5.setLastName("Navakotla");
    
    emp6.setFirstName("Keerthi");
    emp6.setLastName("Parush");
    
    emp7.setFirstName("Gopi");
    emp7.setLastName("Battu");
    
    emp8.setFirstName("Jaideep");
    emp8.setLastName("Gera");
    
    SessionFactory sessionFactory = getSessionFactory();
    Session session = sessionFactory.openSession();
    session.beginTransaction();
    
    /* Saving objects to database */
    session.save(emp1);
    session.save(emp2);
    session.save(emp3);
    session.save(emp4);
    session.save(emp5);
    session.save(emp6);
    session.save(emp7);
    session.save(emp8);
  
    Query query = session.createQuery("select firstName from Employee");
    List<String> list = (List<String>) query.list();
    
    Iterator<String> iter = list.iterator();
    while(iter.hasNext()){
      String firstName = iter.next();
      System.out.println(firstName);
    }
    
    session.getTransaction().commit();
    session.close(); 
    
  }
}


Run TestEmployee class, you will get output like below.

Hibernate: drop table if exists Employee
Hibernate: create table Employee (id integer not null auto_increment, firstName varchar(255), lastName varchar(255), primary key (id))
Dec 23, 2014 9:17:56 AM org.hibernate.tool.hbm2ddl.SchemaExport execute
INFO: HHH000230: Schema export complete
Hibernate: insert into Employee (firstName, lastName) values (?, ?)
Hibernate: insert into Employee (firstName, lastName) values (?, ?)
Hibernate: insert into Employee (firstName, lastName) values (?, ?)
Hibernate: insert into Employee (firstName, lastName) values (?, ?)
Hibernate: insert into Employee (firstName, lastName) values (?, ?)
Hibernate: insert into Employee (firstName, lastName) values (?, ?)
Hibernate: insert into Employee (firstName, lastName) values (?, ?)
Hibernate: insert into Employee (firstName, lastName) values (?, ?)
Hibernate: select employee0_.firstName as col_0_0_ from Employee employee0_
Hari Krishna
Shreyas
Piyush
Rama Krishna
Sailaja
Keerthi
Gopi
Jaideep

Prevoius                                                 Next                                                 Home

No comments:

Post a Comment