CODEWITHSUNDEEP
mysqlhibernate
aniltilanthe
aniltilanthe

Reputation: 5245

Hibernate HQL query get data from table associated with another table

I have an entity called Locality as:-

@Entity
@Table(name = "CMN_LOCALITY_MASTER")
public class Locality {
    @Id
    @Column(name = "LOCALITY_ID", unique = true, nullable = false,length = 11)
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    int localityId;

    @Column(name = "LOCALITY_DESCRIPTION",length=70)
    String localityDescription;

    @JsonProperty(access = Access.WRITE_ONLY)
    @ManyToOne
    @JoinColumn(name = "PINCODE_ID")
    Pincode pinCode;

    @JsonIgnore
    @ManyToOne
    City city;
}

which contains another entity called City and Pincode.

City is as below:-

@Entity
@Table(name = "CMN_CITY_MASTER")
public class City{

    @Id
    @Column(name = "CITY_ID", unique = true, nullable = false,length = 11)
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int cityId;

    @Column(name = "CITY",length = 150)
    private String description;

    @JsonIgnore
    @ManyToOne
    @JoinColumn(name = "STATE_ID")
    private State state;
}

I want to Get all data from Locality entity/table which has City ID = (e.g. 1)

I tried below queries:-

@Query("SELECT a FROM Locality a INNER JOIN a.city c WHERE c.cityId=?1")
List<Locality>getAllLocalityByCity(int cityId);

and also

@Query("SELECT a FROM Locality a WHERE a.city.cityId=?1")
List<Locality>getAllLocalityByCity(int cityId);

But these are not working.

Could you please suggest me something/way to query the data?

Also, is there an Eclipse Plug-In/Tool to test HQL queries in a faster way than restarting the server for every change in the query?

Could you also suggest reading documents/book for learning HQL?

Upvotes: 2

Views: 3435

Answers (1)

CAPS LOCK
CAPS LOCK

Reputation: 2040

Since you are not providing any logs or explanation I can suggest you try the following:

@Query("SELECT a FROM Locality a INNER JOIN a.city c WHERE c.cityId = :cityId")
List<Locality>getAllLocalityByCity(@Param("cityId") int cityId);

For learning the HQL I would start with Hibernate Docs. You can take a look at Criteria API as well.

Upvotes: 1

Related Questions