How to join multiple tables in hibernate

Question

I am developing a web application using hibernate.

The same application i have created in jsp servlet but now i am using spring and hibernate but unable to do same thing which i did using jsp/servlet

jsp code

String sql = "select city from addres_table where id=2";
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);
while(rs.next()){
String city = rs.getString("city");
String sql1 = "select name from user_table where city='"+city+"'";
Statement st1 = con.createStatement();
ResultSet rs1 = st1.executeQuery(sql1);
while(rs1.next()){
String username = rs.getString("name");
}
}

Basically i am fetching city from addres_table now i am fetching name of users who are living in that city from User_table.

Same thing i want to do in hibernate but i am unable to do.

I have fetched city from addres_table but now how can i fetch name from User_table using that city.

Here is my hibernate code

@SuppressWarnings({ "unchecked", "rawtypes" })
public List<Address> getProfessionById(long id) throws Exception {
session = sessionFactory.openSession();
Criteria cr = session.createCriteria(Address.class);
cr.add(Restrictions.eq("uid", id));
List results = cr.list();
tx = session.getTransaction();
session.beginTransaction();
tx.commit();
return results;
}

Please help me i want to fetch name from User_table using this city

Address class

@Entity
@Table(name="user_profession")
@JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
public class Address implements Serializable {

private static final long serialVersionUID = 1L;
@Id
@GeneratedValue
@Column(name="id")
private long id;

@Column(name="city")
private String city;

public long getId() {
    return id;
}


public void setId(long id) {
    this.id = id;
}

public String getCity() {
    return city;
}

    public void setCity(String city) {
    this.city = city;
}
}

User class

@Entity
@Table(name="user_table")
@JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
public class User implements Serializable {

private static final long serialVersionUID = 1L;
@Id
@GeneratedValue
@Column(name="id")
private long id;

@Column(name="full_name")
private String fullName;

public long getId() {
    return id;
}
public void setId(long id) {
    this.id = id;
}

public String getFullName() {
    return fullName;
}
public void setFullName(String fullName) {
    this.fullName = fullName;
}
}

Answer 1

First of all you have to map the relation between the two entities, change your classes like this:

Adress class:

@Entity
@Table(name="user_profession")
@JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
public class Address implements Serializable {

    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue
    @Column(name="id")
    private long id;

    @Column(name="city")
    private String city;

    //Map the users here
    @OneToMany(mappedBy="adress")
    private Set<User> users;
    //getter and setter

    public long getId() {
        return id;
    }


    public void setId(long id) {
        this.id = id;
    }

    public String getCity() {
        return city;
    }

    public void setCity(String city) {
        this.city = city;
    }
}

User Class:

@Entity
@Table(name="user_table")
@JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
public class User implements Serializable {

    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue
    @Column(name="id")
    private long id;

    @Column(name="full_name")
    private String fullName;

    //The Adress mapping here
    @ManyToOne(fetch=FetchType.LAZY)
    @JoinColumn(name = "Adress_ID", referencedColumnName = "id")
    private Address address;

    public long getId() {
        return id;
    }
    public void setId(long id) {
    this.id = id;
    }

    public String getFullName() {
        return fullName;
    }
    public void setFullName(String fullName) {
        this.fullName = fullName;
    }
}

And then to get the list of the users with a specific city do the following:

String hql = "SELECT user.name FROM USER_TABLE user WHERE user.address IN (SELECT address FROM ADDRESS_TABLE address WHERE address.id = :id)";
Query query = session.createQuery(hql);
query.setParameter("id",2);
List results = query.list();

EDIT:

Use this query instead:

String hql = "from User u INNER JOIN u.address ad where ad.id = :id";
Query query = session.createQuery(hql);
query.setParameter("id",2);
List results = query.list();

And you will get what you need.