0

I have created two Entity Java file which indicate there are two tables, User and UserPasswordHistory, UserPasswordHistory has ManyToOne Relationship to user table, whenever i select UserPasswordHistory by the foreign key userid, it will select the User table as well.

The library i am using to test the JPA is \EclipseLink 2.5.2 and the link is the libraries which come with EclipseLink 2.5.2Libraries List

I have defined the @ManyToOne (fetch=FetchType.LAZY), but the issue is persist

User.java

@Entity
@Table(name="Users")
public class User {


     @Id
     @GeneratedValue( strategy= GenerationType.AUTO ) 
     private int userId;
     private String username;
     private String passowrd;

    public int getUserId() {
        return userId;
    }
    public void setUserId(int userId) {
        this.userId = userId;
    }
    public String getUsername() {
        return username;
    }

....

UserPasswordHistory.java

@Entity
public class UserPasswordHistory {

     @Id
     @GeneratedValue( strategy= GenerationType.AUTO )   

     private long historyId;
     private String password;
     private User user;


     public long getHistoryId() {
        return historyId;
    }
    public void setHistoryId(long historyId) {
        this.historyId = historyId;
    }

    public String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }

    @ManyToOne (fetch=FetchType.LAZY)
    @JoinColumn(name = "userId")
    public User getUser() {
        return user;
    }
    public void setUser(User user) {
        this.user = user;
    }

JPQL code

EntityManagerFactory emfactory = Persistence.createEntityManagerFactory("JPA_TEST");
        EntityManager entitymanager = emfactory.createEntityManager();
        String jpql2 = "select uph from UserPasswordHistory uph where uph.user.userId = :userid";
        Query query2 = entitymanager.createQuery(jpql2);
        query2.setParameter("userid", 1305);
        List<UserPasswordHistory> uphList = query2.getResultList();

        for (UserPasswordHistory uph:uphList ) {

            System.out.println(uph.getHistoryId() + " " + uph.getPassword());

        }

Refer to the result from the log file, it will still query the User table which is bold

[EL Info]: connection: 2019-04-12 10:45:43.733--ServerSession(1345636186)--Thread(Thread[main,5,main])--file:/C:/KWSP/jpa_test/JPA_TEST/build/classes/_JPA_TEST login successful
[EL Fine]: sql: 2019-04-12 10:45:43.999--ServerSession(1345636186)--Connection(1206569586)--Thread(Thread[main,5,main])--SELECT HISTORYID, PASSWORD, USER_USERID FROM USERPASSWORDHISTORY WHERE (USER_USERID = ?)
    bind => [1305]

This is the unexpected query:

[EL Fine]: sql: 2019-04-12 10:45:44.03--ServerSession(1345636186)--Connection(1206569586)--Thread(Thread[main,5,main])--SELECT USERID, PASSOWRD, USERNAME FROM Users WHERE (USERID = ?)
    bind => [1305]
Jim Garrison
  • 85,615
  • 20
  • 155
  • 190
chunghow
  • 21
  • 2
  • see https://stackoverflow.com/questions/30082281/manytoonefetch-fetchtype-lazy-doesnt-work-on-non-primary-key-referenced-co – Scary Wombat Apr 12 '19 at 02:54
  • Not really applicable to my case, the userid of UserPasswordHistory is a foreign key for User table and i am using JPA but not hibernate – chunghow Apr 12 '19 at 03:48
  • One things it did mention is to have the `One-Many` annotation of the *foreign* table – Scary Wombat Apr 12 '19 at 04:06
  • I have added below line to my User class @OneToMany(targetEntity=UserPasswordHistory.class) public List getPasswordHistorties() { return passwordHistories; } public void setPasswordHistorties(List passwordHistorties) { this.passwordHistories = passwordHistorties; } the side impact is: 1. Additional table Users_USERPASSWORDHISTORY will be created with store the primary key for both tables 2. If i query again with my jpql which select USERPASSWORDHISTORY, it will still query the user table which is not required – chunghow Apr 12 '19 at 04:41

0 Answers0