How to perform inner join in spring boot mvc, jpa, hibernet, maven

0

I run into a problem, the point is that I do not know how to perform a query with inner join My controller is as follows:

CONTROLLER

     @RequestMapping(value="/juan",method = RequestMethod.POST)
        public String updateProduct(
                @RequestParam("id") int id,
                @RequestParam("descripcion") String descripcion,
                @RequestParam("price") double price) {

           List nombre =userRepository.userdata(1);
           System.out.println("aqui data "+nombre);
            return "redirect:/inicio";
        }

**MODEL 1**


@Entity
@Table(name = "\"MFTP_USUARIO\"", schema = "public")
public class Users implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id_usuario")
    private int idusuario;

    @Column(name = "a_nombre")
    private String anombre;

    @Column(name = "a_paterno")
    private String apaterno;

    @Column(name = "a_materno")
    private String amaterno;

    @Column(name = "a_rfc")
    private String arfc;

    @Column(name = "a_usuario")
    private String ausuario;

    @Column(name = "a_contrasena")
    private String acontrasena;

    @Column(name = "c_status_id")
    private int cstatusid;

    @Column(name = "c_info_contacto_id")
    private int cinfocontactoid;

    @Column(name = "d_registro")
    private Date dregistro;

    @Column(name = "c_usuario_id")
    private int cusuarioid;

    @Column(name = "d_modifica")
    private Date dmodifica;

    @Column(name = "c_usuario_id_mod")
    private int cusuarioidmod;

    @Column(name = "c_usuario_mifel")
    private int cusuariotmifel;


    public Users(){

    }

    public int getIdusuario() {
        return idusuario;
    }

    public void setIdusuario(int idusuario) {
        this.idusuario = idusuario;
    }

    public String getAnombre() {
        return anombre;
    }

    public void setAnombre(String anombre) {
        this.anombre = anombre;
    }

    public String getApaterno() {
        return apaterno;
    }

    public void setApaterno(String apaterno) {
        this.apaterno = apaterno;
    }

    public String getAmaterno() {
        return amaterno;
    }

    public void setAmaterno(String amaterno) {
        this.amaterno = amaterno;
    }

    public String getArfc() {
        return arfc;
    }

    public void setArfc(String arfc) {
        this.arfc = arfc;
    }

    public String getAusuario() {
        return ausuario;
    }

    public void setAusuario(String ausuario) {
        this.ausuario = ausuario;
    }

    public String getAcontrasena() {
        return acontrasena;
    }

    public void setAcontrasena(String acontrasena) {
        this.acontrasena = acontrasena;
    }

    public int getCstatusid() {
        return cstatusid;
    }

    public void setCstatusid(int cstatusid) {
        this.cstatusid = cstatusid;
    }

    public int getCinfocontactoid() {
        return cinfocontactoid;
    }

    public void setCinfocontactoid(int cinfocontactoid) {
        this.cinfocontactoid = cinfocontactoid;
    }

    public Date getDregistro() {
        return dregistro;
    }

    public void setDregistro(Date dregistro) {
        this.dregistro = dregistro;
    }

    public int getCusuarioid() {
        return cusuarioid;
    }

    public void setCusuarioid(int cusuarioid) {
        this.cusuarioid = cusuarioid;
    }

    public Date getDmodifica() {
        return dmodifica;
    }

    public void setDmodifica(Date dmodifica) {
        this.dmodifica = dmodifica;
    }

    public int getCusuarioidmod() {
        return cusuarioidmod;
    }

    public void setCusuarioidmod(int cusuarioidmod) {
        this.cusuarioidmod = cusuarioidmod;
    }

    public int getCusuariotmifel() {
        return cusuariotmifel;
    }

    public void setCusuariotmifel(int cusuariotmifel) {
        this.cusuariotmifel = cusuariotmifel;
    }

MODEL 2

@Entity @Table (name="\" MFTP_USUARIO_PERFIL \ "", schema="public") public class UserProfile implements Serializable {     @Id     @GeneratedValue (strategy = GenerationType.AUTO)     @Column (name="c_usaurio_pfl")     private int cusauriopfl;

@Column(name = "c_usuario_id")
private int cusuarioid;

@Column(name = "c_perfil_id")
private int cperfilid;

@Column(name = "c_status_id")
private int cstatusid;

@Column(name = "d_registro")
private Date dregistro;

@Column(name = "c_usuario_id_cap")
private int cusuarioidcap;

@Column(name = "d_modifica")
private Date dmodifica;

@Column(name = "c_usuario_id_mod")
private int cusuarioidmod;


public int getCperfilid() {
    return cperfilid;
}

public void setCperfilid(int cperfilid) {
    this.cperfilid = cperfilid;
}

public int getCstatusid() {
    return cstatusid;
}

public void setCstatusid(int cstatusid) {
    this.cstatusid = cstatusid;
}

public Date getDregistro() {
    return dregistro;
}

public void setDregistro(Date dregistro) {
    this.dregistro = dregistro;
}

public int getCusuarioidcap() {
    return cusuarioidcap;
}

public void setCusuarioidcap(int cusuarioidcap) {
    this.cusuarioidcap = cusuarioidcap;
}

public Date getDmodifica() {
    return dmodifica;
}

public void setDmodifica(Date dmodifica) {
    this.dmodifica = dmodifica;
}

public int getCusuarioidmod() {
    return cusuarioidmod;
}

REPOSITORY

public interface UserRepository extends JpaRepository {

Users findByAusuarioAndAcontrasena(@Param("user") String user, @Param("password") String password);


//Table
List<Users> findAll();

//Delete
List<Users> deleteByIdusuario(int id);

//search by id
List<Users> findAllByIdusuario(int id);

@Query("SELECT use.idusuario,\t\n" +
        "use.anombre,\n" +
        "use.apaterno,\n" +
        "use.amaterno,\t\n" +
        "use.arfc,\t\n" +
        "use.ausuario,\t\n" +
        "use.acontrasena,\t\n" +
        "use.cstatusid,\t\n" +
        "use.cinfocontactoid,\t\n" +
        "use.dregistro,\t\n" +
        "use.cusuarioid,\t\n" +
        "use.dmodifica,\t\n" +
        "use.cusuarioidmod,\t\n" +
        "use.cusuariotmifel,\n" +
        "usep.cperfilid\n" +
        "FROM Users use\n" +
        "INNER JOIN UserProfile usep\n" +
        "ON idusuario = UserProfile.cusuarioid\n" +
        "WHERE  use.idusuario = (?1)")
List<Users> userdata(int id);

}

But I can not perform the query with my inner join

    
asked by Jasi Enriquez 10.11.2017 в 19:13
source

1 answer

0

First, I think you would have to declare the relationship using the annotations @OneToMany , @ManyToMany , @ManyToOne or @ManyToMany

Then, the query should only be something like this:

 @Query("SELECT use from Useres fetch join use.profiles where user.id = use.idusuario = (?1)")
 List<Users> userdata(int id);

For JPA to automatically load the model data at once.

This article tells you an example.

My advice would be to introduce yourself a bit more in the JPA specification.

Good luck.

    
answered by 15.11.2017 / 12:13
source