CODEWITHSUNDEEP
sqlhibernate
pepuch
pepuch

Reputation: 6516

How to write sql join query using HQL

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.

Upvotes: 0

Views: 317

Answers (1)

RAS
RAS

Reputation: 8158

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.

Upvotes: 1

Related Questions