Use join to map user by id

Question

I want to implement Join between tables:

@Table(name = "users")
public class Users implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", unique = true, updatable = false, nullable = false)
    private int id;

    @Column(length = 255)
    private String login;
}

@Table(name = "transactions")
public class Transactions implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", unique = true, updatable = false, nullable = false)
    private int id;

    @Column(name = "user_id", length = 20)
    private Integer userId;
}

Using login I want to get to select the reference id from table users and map it to userId and list app rows which match the id from table Transactions.

I tried this HQL query:

SELECT c FROM Users u LEFT JOIN Transactions t ON u.id = t.id;

It's not clear to me how to select the appropriate login to id. Can you guide me?

Answer 1

Take a look at this thread: How to join two unrelated entities using JPA and Hibernate

The top two answers show pretty well how to build your left join and what the implications are based on your version(s) of hibernate (i.e. whether you can or whether you must revert to SQL). Note you can expect each Row returned to contained one of both objects..

There are other ways you can map this relationship with lazy oneToManys and what not which you could entertain, but that would be beyond the scope of the question as asked.

As for where to filter on a user, it would be in your where clause.. i.e.

select ... WHERE user.id = transaction.user_id AND user.login = '[email protected]';

Also unrelated but if your user_id MUST be on the transaction table, you might consider making it an "int" instead of an "Integer". Having it as "Integer" is more useful if it can be null (since unlike a primitive it can be set nullable).