Hibernate Multiple join with dynamic parameters

Question

I want to get a result set of a multiple join queries with zero to multiple parameters, my query in SQL is something like this:

    SELECT c.*
      FROM users u
INNER JOIN user_roles ur ON u.username = ur.username
INNER JOIN customer   c  ON u.username = c.customer_id;

Following diagram shows the relationship between tables;

Entities defined as following:

@Entity
@Table(name = "users")
public class User {
    @Id
    @Column(name = "username", unique = true, nullable = false, length = 45)
    private String username;

    @OneToOne(mappedBy = "user", cascade = CascadeType.ALL)
    private Customer customer;

    @OneToMany(mappedBy = "user", cascade = CascadeType.ALL)
    private Set<UserRole> userRole = new HashSet<UserRole>(0);

    //getter & setters
}


@Entity
@Table(name = "CUSTOMER")
public class Customer {
    @Id
    @Column(name = "customer_id")
    @GeneratedValue(generator = "gen")
    @GenericGenerator(name = "gen",
            strategy = "foreign", parameters = @Parameter(name = "property", value = "user"))
    private String id;

    @OneToOne
    @PrimaryKeyJoinColumn
    private User user;

    //getter & setters
}

@Entity
@Table(name = "user_roles")
public class UserRole {
    @Id
    @GeneratedValue(strategy = IDENTITY)
    @Column(name = "user_role_id", unique = true, nullable = false)
    private Integer userRoleId;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "username", nullable = false)
    private User user;

    @Column(name = "role", nullable = false, length = 45)
    private String role;

    //getter & setters
}

I have read that you can join columns with a criteria but I haven't been successful with it. How can I achieve it?

Answer 1

I got the answer:

        Criteria criteria = session.createCriteria(UserRole.class,"userRole")
                            .createCriteria("user","user")
                            .createCriteria("customer","customer");
        criteria.add(Restrictions.eq("user.enabled", enabled));

        if(userName!=null){
            criteria.add(Restrictions.eq("user.username", userName));
        }if(role!=null){
            criteria.add(Restrictions.eq("userRole.role", role));
        }if(fName!=null){
            criteria.add(Restrictions.eq("customer.customerFirstname", fName));
        }if(lName!=null){
            criteria.add(Restrictions.eq("customer.customerLastname", lName));
        }
        List<UserRole> results = criteria.list();