Join tables with JPA

Question

Join tables with JPA

Navigation

#1 by (3 votes)

4

I request your help because I need to make a union of two tables in a query in order to create a list, but I do not know how to do it, this is the query that you create:

SELECT p.id,
       p.nombre_departamento,
       p.ciudad_departamento,
       f.nombre_facultad,
       p.estado_departamento
FROM par_departamento p, par_facultad f
WHERE p.id_facultad = f.id

ParDepartment Table:

public class ParDepartamento implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "id")
private Integer id;
@Basic(optional = false)
@NotNull
@Size(min = 1, max = 255)
@Column(name = "nombre_departamento")
private String nombreDepartamento;
@Basic(optional = false)
@NotNull
@Size(min = 1, max = 255)
@Column(name = "ciudad_departamento")
private String ciudadDepartamento;
@Basic(optional = false)
@NotNull
@Column(name = "fecha_creacion")
@Temporal(TemporalType.DATE)
private Date fechaCreacion;
@Basic(optional = false)
@NotNull
@Column(name = "estado_departamento")
private boolean estadoDepartamento;
@JoinColumn(name = "id_facultad", referencedColumnName = "id")
@ManyToOne
private ParFacultad idFacultad;

ParFacultad Table:

public class ParFacultad implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "id")
private Integer id;
@Basic(optional = false)
@NotNull
@Size(min = 1, max = 2147483647)
@Column(name = "nombre_facultad")
private String nombreFacultad;
@Basic(optional = false)
@NotNull
@Size(min = 1, max = 2147483647)
@Column(name = "ciudad_facultad")
private String ciudadFacultad;
@Basic(optional = false)
@NotNull
@Column(name = "fecha_creacion")
@Temporal(TemporalType.DATE)
private Date fechaCreacion;
@Basic(optional = false)
@NotNull
@Column(name = "estado_facultad")
private boolean estadoFacultad;
@OneToMany(mappedBy = "idFacultad")
private Collection<ParDepartamento> parDepartamentoCollection;

I tried to do this and it generates an error:

private EntityManager em;
em.createQuery("SELECT   p.id,p.nombre_departamento,p.ciudad_departamento,f.nombre_facultad,p.estado_departamento FROM par_departamento p, par_facultad f where p.id_facultad = f.id");

sql java jpa

asked by Gdaimon 15.05.2016 в 17:57

source

1 answer

Error opening the Mac Terminal Change the id of a Youtube video in HTML

score 3 · Accepted Answer

In JPQL, the union JOIN is executed directly in the entities, you do not need to write it as if it were pure SQL. JPQL is based on the entities loaded by JPA and allows to consult on those entities more than on the tables. In the same way, the union JOIN is made through the relationships established between the entities.

Here is an example of what the JPQL query looks like:

SELECT
  dep.id,
  dep.nombreDepartamento,
  dep.ciudadDepartamento,
  fac.nombreFacultad,
  dep.estadoDepartamento
FROM ParDepartamento dep
    JOIN dep.idFacultad fac

But apparently what you really want to get are the departments and their faculties, so maybe this JPQL solves your problem:

SELECT dep FROM ParDepartamento dep -- por defecto traerá las facultades de cada ParDepartamento