How to write sql join query using HQL

Question

I've got two entities AddressEntity and CompanyEntity.

Address entity

@Entity
@Table(name = "address")
public class AddressEntity implements Serializable {

    private static final long serialVersionUID = 6149442393833549397L;

    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private Integer id;

    @Column(name = "city", nullable = false)
    private String city;

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

    @Column(name = "street", nullable = false)
    private String street;

    @Column(name = "building_nr", nullable = false)
    private Integer buildingNr;

    @Column(name = "flat_nr")
    private Integer flatNr;

    // setters and getters
}

Company entity

@Entity
@Table(name = "company")
public class CompanyEntity implements Serializable {

    private static final long serialVersionUID = 3635072833730133590L;

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id")
    private Integer id;

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

    @OneToOne
    @Cascade(CascadeType.ALL)
    private AddressEntity address = new AddressEntity();

    @OneToMany(mappedBy = "company")
    @Cascade(CascadeType.ALL)
    private Set<DescriptionEntity> descriptions = new HashSet<DescriptionEntity>();

    @OneToMany(mappedBy = "company")
    @Cascade(CascadeType.ALL)
    private List<EmployeeEntity> employees = new ArrayList<EmployeeEntity>();

}

I want to select all addresses which are used by companies like in this SQL query SELECT a.city, c.name FROM address a INNER JOIN company c ON c.address_id=a.id; but using HQL query (I want to use JOIN, not WHERE). How can I do it? I want to select addresses using address table, not company table. I know that I can select addresses using company table like this select c.address.city, c.name from CompanyEntity c or using WHERE select a.city, c.name from CompanyEntity c, AddressEntity a WHERE c.address.id=a.id.

if you want to select addresses used by companies using address table u need to add relation between adress and company in address entity — wxyz
– wxyz, Commented Nov 1, 2013 at 11:17
I'm totally new in Hibernate. Can you please write your ideas how to solve this problem? — pepuch
– pepuch, Commented Nov 1, 2013 at 11:33

RAS · Accepted Answer · 2013-11-01 11:45:41Z

1

Criteria c = session.createCriteria(CompanyEntity.class);
c.createAlias("address", "address");
List<CompanyEntity> companies = c.list();

This will give you list of CompanyEntities having at least one AddressEntity.

If you add the following line:

c.criteria.setProjection(Projections.property("address"));

You will get a List of AddressEntities by doing c.list().

Tell me if you need something else.

answered Nov 1, 2013 at 11:45

RAS

8,16017 gold badges68 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to write sql join query using HQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related