How to join multiple tables in sleep mode
I am developing a web application using hibernate.
The same application I created in the jsp servlet, but now I am using spring
and hibernate
, but cannot do the same as I do usingjsp/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 extract city
from addres_table
now I get name
of users
that live in this city
from User_table
.
The same thing that I want to do in hibernation, but I cannot do it.
I have extracted city
from addres_table
, but now how can I extract name
from User_table
using this city.
Here is my sleeping 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 get the 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;
}
}
source to share
First of all you need to map the relationship between two objects, change your classes as follows:
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;
//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 a list of users in a specific city, follow these steps:
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.
source to share