CODEWITHSUNDEEP
javajpajpql
marwa
marwa

Reputation: 85

How to read data from database with JPA/JPQL Query and Foreign Keys

I am new to JPA and i have a problem with it. Suppose that we have two tables which are related by a ManytoOne association. table 1: person (idp,name,#idep) table 2: departement (idep,deptname)

I want to write a jpa query like this query in sql :

select person.name,departement.deptname from person,departement where person.idep=department.idep

Person.java

@Id
@GeneratedValue(strategy=GenerationType.AUTO)
private Integer idp;
private String name;

//Jointures JPA
@ManyToOne(fetch=FetchType.LAZY)
@JoinColumn(name="id_dep")
private Departement dept;

//Getters/Setters

Departement.java

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Integer id_dep;
private String deptname;

@OneToMany(mappedBy="departement", cascade = { CascadeType.ALL })
private List<Person> listperson=new ArrayList<Person>();

//getters/setters

I tried this attempt but it didn't work:

entityManager.createQuery("select p.name,d.deptname " + " from Person a,Department d " + "where a.id_dep =d.id_dep ").getResultList();

Upvotes: 0

Views: 3326

Answers (3)

ujulu
ujulu

Reputation: 3309

The query is very simple because the two entities are associated to one another:

String queryString = "select p.name,d.deptname from Person p JOIN p.dep d";
List list = entityManager.createQuery(queryString).getResultList();

// use the result list as follows, for example:
for (Object[] o : list) {
    System.out.println("Person name: " + o[0]);
    System.out.println("Department name: " + o[1]);
}

Tip: Java has naming conventions. One of them is the name of instance fields. So the recommendation is instead of 

private Integer id_dep

write 

private Integer id; // for examle

Actually, the id field of an entity is just id by convention. If you are interested you can read Java coding conventions.

Upvotes: 1

Kamil Chalczynski
Kamil Chalczynski

Reputation: 61

To make it work you should query against fields, so a.id_dep is not correct. You also called p.name, whereas alias for Person is set to a. The query should look like: 

entityManager.createQuery("select a.name, d.deptname " + " from Person a, Department d "
 + "where a.dept.id_dep = d.id_dep ").getResultList();

If it doesn't work join might be needed.

entityManager.createQuery("select p.name, d.deptname " + " from Department d JOIN Department.listperson p "
 + "where d.id_dep = p.dept.id_dep ").getResultList();

Although it is probably not ideal solution using JPA, as you can just get values from listperson much easier.

Upvotes: 0

Gilberto Santos
Gilberto Santos

Reputation: 21

As are you using JPA , this kind query should be automatically by simply invoking the method getListPerson() which returns a List of Person. The provider, may be are you using (Hibernate) should using the JPA annotations in order to create the SQL and execute it for you, when you call the get methods.

entityManager.createQuery("") is normally used when you need an specific query that hibernate are not able to do, for e.g when you need return only specific field from an entity e.g Person.

Upvotes: 0

Related Questions