how to fetch data from two tables in JPA

I am not able to fetch all records from two tables using the below query

I have tried this but I am getting a result from one table only. I want a result of both the tables i.e, client_software_param_mapping and client_file_configuration having the same ClientId which is a foreign key from third pojo(client_software_configuration) to first and second pojo.

public Result showClientConfiguration() {EntityManagerFactory entityManagerFactory =                          Persistence.createEntityManagerFactory("defaultPU");
  EntityManager entityManager = entityManagerFactory.createEntityManager();

Query q=entityManager.
  createQuery("SELECT c FROM client_software_param_mapping c JOIN fetch client_file_configuration f ON c.ClientId=f.ClientId");
  List data =q.getResultList();
  return ok(Json.toJson(data));
}

first pojo

@Entity
public class client_file_configuration {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private int id;

  private String sourceFolder;
  private String sourceFile;
  private String processingFolder;
  private String processingFile;
  private String processedFolder;
  private int intervalInMin;
  private String readAfterDelay;
  private String parserClass;
  private String directoryMode;
  private String fileMode;
  private String actionMode;
  private String type;
  private String fileExpressionResolver;

  @OneToOne
  @JoinColumn(name = "ClientId")

  private client_software_configuration clientSoftwareConfiguration;

  public client_software_configuration getClientSoftwareConfiguration() {
    return clientSoftwareConfiguration;
  }

  public void setClientSoftwareConfiguration(client_software_configuration clientSoftwareConfiguration) {
    this.clientSoftwareConfiguration = clientSoftwareConfiguration;
  }
}

secondpojo

@Entity
public class client_software_param_mapping {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private int id;

  private String paramKey;
  private String paramValue;


  public int getId() {
    return id;
  }

  public void setId(int id) {
    this.id = id;
  }

  public String getParamKey() {
    return paramKey;
  }

  public void setParamKey(String paramKey) {
    this.paramKey = paramKey;
  }

  public String getParamValue() {
    return paramValue;
  }

  public void setParamValue(String paramValue) {
    this.paramValue = paramValue;
  }

  @ManyToOne
  @JoinColumn(name = "ClientId")

  private client_software_configuration clientSoftwareConfiguration;

  public client_software_configuration getClientSoftwareConfiguration() {
    return clientSoftwareConfiguration;
  }

  public void setClientSoftwareConfiguration(client_software_configuration clientSoftwareConfiguration) {
    this.clientSoftwareConfiguration = clientSoftwareConfiguration;
  }
}

thirdpojo

@Entity
public class client_software_configuration {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private int id;

  private String url;
  private int port;
  private String endPoint;
  private String isPost;
  private String isPing;
  private String params;
  private int serialNo;

  private String dateFormat;
  private String token;
}

Answer

this is the right query as it is returning the object of the third pojo present at that ClientId so it is able to understand the type of ClientId.JPQL never uses table and column names. It always uses entity names and their mapped fields/properties names.so here I have taken the object of the third pojo having the ClientId field.

select c,p from client_file_configuration c,client_software_param_mapping p where c.clientSoftwareConfiguration = p.clientSoftwareConfiguration

Advertisement

Answer