CODEWITHSUNDEEP
javaspringhibernatejpaspring-data-jpa
Neel Kamal
Neel Kamal

Reputation: 710

Conditional where clause in JPA

I have two tables "table1" and "table2". For each row in table1, there can be multiple rows in table2. To get the data, I have created following classes

@Entity
@EntityListeners(AuditingEntityListener.class) 
@Table(name = "table1", schema = "dbo")
public class Table1 extends BaseDomain{
  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  private Integer vendorId;
  private String name;
  private Integer companyId;
  private String details;

  @OneToMany(mappedBy = "table1",cascade = CascadeType.ALL)
  @NotFound(action = NotFoundAction.IGNORE)
  private Set<Table2> table2;

 // getter and setter for above
}

and 

    @Entity
    @EntityListeners(AuditingEntityListener.class) 
    @Table(name = "table2", schema = "dbo")
    public class Table2 extends BaseDomain{
      @Id
      @GeneratedValue(strategy = GenerationType.AUTO)
      private Integer id;
      private Integer cityId;
      private Integer companyId;
      @OneToOne
      @JoinColumn(name = "vendorId")
      @JsonIgnore
      private Table1 table1;

     // getter and setter for above
    }

Then I have created repository like this

@Transactional
public interface Table1Repository  extends JpaRepository<Table1, Integer> {

  Page<Table1> findByCompanyId(Integer companyId,Pageable pageable);

}

This will give me the list of rows from table1 as per companyId and list of rows with corresponding vendorId in Table2.

Till now my implementation is working fine.

The response is

{
  "content": [
    {
      "vendorId": 23,
      "name": "vendorname",
      "details": details,
      "table2": [
                 {
                  "id" :1,
                 "cityId":1,
                 },
                  "id" :2,
                  "cityId" : 3
                ]
    },
   {....}
]
}

Currently I am taking companyId as input and gives the above response. Now I want to take cityId as an input as well. So table2 output should contain the details of given cityId only.

So can anyone help how can I achieve it? I don't want to create another model for table1 and table2.

Upvotes: 1

Views: 1175

Answers (1)

pvpkiran
pvpkiran

Reputation: 27048

Page<Table1> findByCompanyIdAndTable2_CityId(Integer companyId, Integer cityId, Pageable pageable);

This method will take care of fetching by cityId. Underscore(_) refers to nested fields. But for this to work, you need to fetch Table2 eagerly. 

@OneToMany(mappedBy = "table1",cascade = CascadeType.ALL, fetch=FetchType.EAGER)

Upvotes: 2

Related Questions