CODEWITHSUNDEEP
javahibernatejpa
Rag
Rag

Reputation: 65

JPA Entity and HQL JOIN command

I'm trying to achieve something like sql command below by using HQL and JPA. Instead of "SELECT user_id..." I need SELECT OBJECT(o).

SELECT user_id FROM posix_user o INNER JOIN postgre_user n ON n.id=o.user_id WHERE n.name='USERNAME2'

I have some problems with this part of the code in JPA DAO:

public List<PosixUserEntity> listPosixUsers(final String uid_number) {


final StringBuilder queryString = new StringBuilder("SELECT OBJECT(o) FROM ");
   queryString.append(this.entityClass.getSimpleName());
   queryString.append(" o JOIN com.services.dao.user.jpa.UserEntity n ON (n.id=o.user_id) WHERE n.name LIKE :uid_number");

 final Query findByNameQuery = entityManager.createQuery(queryString.toString()).setParameter("uid_number", uid_number);

   return findByNameQuery.getResultList();
    }

JOIN ON is not allowet here and I did not know how to replace it. Also how can I replace com.services.dao.user.jpa.UserEntity by something cleaner.

There is my Entites, they may need to be improved:

@Entity
@Table(name = "posix_user")
public class PosixUserEntity implements Serializable {
    private static final long serialVersionUID = 1L;

@Id
//@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "user_id")
private String user_id;

@Column(name = "uid_number")
private String uid_number;

@Column(name = "home_directory")
private String home_directory;

@Column(name = "login_shell")
private String login_shell;

@Column(name = "group_id")
private String group_id;

//getters,setters....

@Entity
@Table(name = "postgre_user")
@SQLDelete(sql = "update postgre_user set status = 'removed' where id = ?")
public class UserEntity implements Serializable {

    private static final long serialVersionUID = 1L;

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

    @Column(name = "name", unique = true, nullable = false)
    private String name;

    @Column(name = "password")
    private String password;

    @Enumerated(EnumType.STRING)
    @Column(name = "status")
    private UserStatus status;

    @Column(name = "firstname")
    private String firstName;

    @Column(name = "lastname")
    private String lastName;

    @Column(name = "email")
    private String email;

    @Column(name = "usertype")
    private String userType;

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "user")
    private List<UserRoleTargetGroupEntity> userRoleTargetGroupEntity;

    @Column(name = "last_login")
    private String lastLogin;

    @Column(name = "previous_login")
    private String previousLogin;

    @JsonIgnore
    @Column(name = "change_password_flag")
    private Boolean userPasswordResetFlag;

    @OneToOne(cascade=CascadeType.ALL)
    @PrimaryKeyJoinColumn
    private PosixUserEntity posixUserEntity;

You may also need to know that FOREIGN KEY (user_id) REFERENCES postgre_user (id) - it should look like that

Can you know how can I modify my SELECT?

Upvotes: 0

Views: 2166

Answers (1)

RubioRic
RubioRic

Reputation: 2468

I've tested a simplified version of your classes

@Entity
@Table(name = "posix_user")
public class PosixUserEntity {

     @Id
     @Column(name = "user_id")
     private Long user_id;

     // getter + setter
}

@Entity
@Table(name = "postgre_user")
public class UserEntity {

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

     private String name;

     @OneToOne(cascade=CascadeType.ALL)
     @PrimaryKeyJoinColumn
     private PosixUser posixUserEntity;

     // getter + setter
}

And this JPQL query works as expected

 String jpql = "SELECT p " 
              + "FROM UserEntity n JOIN n.posixUserEntity p " 
              + "WHERE n.name LIKE :uid_number)";

JOIN is allowed because you have mapped the relationship in UserEntity. and you don't need to specify the complete name of your entity class. Check if it has been included when you define your persistence unit.

Hope this helps.

Upvotes: 2

Related Questions