CODEWITHSUNDEEP
javahibernatehql
user2563516
user2563516

Reputation: 105

How to fetch data from Entity by using HQL?

I am using HQL to fetch the data from database i got sql syntext exception please check my code

Emp.java

@Entity

@Table(name="TBL_STD_EMP")
public class Emp 
{

@Id 
@GeneratedValue
   @Column(name = "id")
   private int id;

   @Column(name = "first_name")
   private String firstName;

   @Column(name = "last_name")
   private String lastName;

   @Column(name="SALARY")
   private int salary;  
   @Temporal(TemporalType.DATE)
   private Date joinDate;
   @ElementCollection
   @JoinTable(name="EMP_ADDRESS" ,joinColumns=@JoinColumn(name="USER_ID"))
   java.util.Collection <Address> address= new java.util.ArrayList<Address>();
     //setter and geeter methods

} My Embeddable class

@Embeddable
public class Address {
@Column(name="DOOR_NO")
int doorNO;

public int getDoorNO() {
    return doorNO;
}
public void setDoorNO(int doorNO) {
    this.doorNO = doorNO;
}
public String getStreetName() {
    return streetName;
}
public void setStreetName(String streetName) {
    this.streetName = streetName;
}
public String getCountryName() {
    return countryName;
}
public void setCountryName(String countryName) {
    this.countryName = countryName;
}
@Column(name="STREET_NAME")
String streetName;
@Column(name="COUNTY_NAME")
String countryName;

} My main class package com.hib.busness;

 import java.util.Date; 
 import java.util.List;

 import org.hibernate.HibernateException;
 import org.hibernate.Query;
 import org.hibernate.Session;
 import org.hibernate.SessionFactory;
 import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;

 import com.hib.dto.Address;
 import com.hib.dto.EMP;

 public class MainClass {

/**
 * @param args
 */
private static SessionFactory factory;

public static void main(String[] args) {
    // TODO Auto-generated method stub

    try {
        factory = new Configuration().configure().buildSessionFactory();

    } catch (Throwable ex) {
        System.err.println("Failed to create sessionFactory object." + ex);
        throw new ExceptionInInitializerError(ex);
    }

    Transaction tx = null;
    Integer employeeID = null;
    Session session = factory.openSession();
    try {
        tx = session.beginTransaction();
        EMP employee = new EMP();
        employee.setFirstName("bhanu1");
        employee.setLastName("prasad");
        employee.setSalary(30000);
        employee.setJoinDate(new Date());
        employee.setDescription("Working as ProductEngineer");
        Address address = new Address();
        address.setCountryName("India");
        address.setDoorNO(1);
        address.setStreetName("mainStreet");
        Address address1 = new Address();
        address1.setCountryName("India1");
        address1.setDoorNO(2);
        address1.setStreetName("mainStreet1");
        employee.getAddress().add(address);
        employee.getAddress().add(address1);

        employeeID = (Integer) session.save(employee);
        tx.commit();
        tx = session.beginTransaction();
        String hql = "FROM EMP where  lastName='prasad' and doorNO=2";
        Query query = session.createQuery(hql);
        List results = query.list();
        System.out.println(results);
    } catch (HibernateException e) {
        if (tx != null)
            tx.rollback();
        e.printStackTrace();
    } finally {
        session.close();
    }

}

 }

I got SQLSyntex exception any one help me.

Upvotes: 2

Views: 1680

Answers (2)

user2563516
user2563516

Reputation: 105

I found solution for my question above:

FROM EMP e where e.lastName='prasad' and DOOR_NO=1

Upvotes: 0

Kevin Bowersox
Kevin Bowersox

Reputation: 94499

In HQL you can navigate the fields of an entity using dot notation. Having established an association between Emp and Address you can use this in your HQL statement. Since the doorNO field is on the Address entity you should access it through the address.

Try:

String hql = "FROM EMP e where e.lastName='prasad' and e.address.doorNO=2";

Upvotes: 1

Related Questions