Ndtyjky

How to find a reference when we have composite key(two or more columns) to pass on as second parameter to the JPA entityManager.find(Object.class, compositeKey)?

My try-
I have created an Arraylist and added the values forming compositeKey it and then passing this list to the find method.

For ex: In my situation, userid and projectid together is the key for the UserProject table and these two have been added to the arraylist named as list, which will be passed as a second parameter to the entityManager find method as shown below:

List<Integer> list = new ArrayList<Integer>();

        list.add(userProjectDO.getUserid());

        list.add(userProjectDO.getProjectid());



        UserProject userProject = em.find(UserProject.class,list);

But this always return as null, even though userid and projectId exists on the table. Has anyone been into similar issue? Solution?

JPA's EntityManager#find doesn't accept arrays as key but Object. Since you are talking about composite key you should implement your key in a separate class which will represent the composite key by listing all the key separate properties. You can achieve this using EmbeddedId for instance.

For example:

You should define the composite key class and annotate with @Embeddable:

public class UserProjectKey implements Serializable{

    private String userId;

    private String projectId;



    //constructors, getters, setters

}

and use it as @EmbeddedId in your entity.

To search by the key you can do:

UserProjectKey key = new UserProjectKey("userIdExample", "projectIdExample");

em.find(UserProject.class, key);

I have found another approach i.e. writing namedQuery to search the table. Posting the implementation just in case it helps anyone.

final Query query = em.createNamedQuery("UserProject.findByAll");

UserProject Entity class:

@Entity

@Table(name = "userproject", schema = "public")



@NamedQueries({ @NamedQuery(name = "UserProject.findByAll", query = "SELECT a FROM UserProject a where a.userid = :userid and a.projectid = :projectid"),

        @NamedQuery(name = "UserProject.findByUserId", query = "SELECT a FROM UserProject a where a.userid = :userid"),

        @NamedQuery(name = "UserProject.findById", query = "SELECT a FROM UserProject a where a.id = :id" )})

public class UserProject implements Serializable {

    private static final long serialVersionUID = 1L;





    @Id @GeneratedValue(strategy= GenerationType.IDENTITY)

    @Column(name = "id")

    private Integer id;



    @Column(name = "userid")

    private Integer userid;



    @Column(name = "projectid")

    private Integer projectid;



    @Column(name = "created")

    private Timestamp created;



    @Column(name = "modified")

    private Timestamp modified;



    @Column(name = "modifiedbyid")

    private Integer modifiedbyid;



    @Column(name = "role")

    private String role;



    public Integer getId() {

        return id;

    }



    public void setId(final Integer id) {

        this.id = id;

    }



    public Integer getUserid() {

        return userid;

    }



    public void setUserid(final Integer userid) {

        this.userid = userid;

    }





    public void setProjectid(final Integer projectid) {

        this.projectid = projectid;

    }



    public Timestamp getCreated() {

        return created;

    }



    public void setCreated(final Timestamp created) {

        this.created = created;

    }



    public Timestamp getModified() {

        return modified;

    }



    public void setModified(final Timestamp modified) {

        this.modified = modified;

    }



    public Integer getModifiedbyid() {

        return modifiedbyid;

    }



    public void setModifiedbyid(final Integer modifiedbyid) {

        this.modifiedbyid = modifiedbyid;

    }



    public String getRole() {

        return role;

    }



    public void setRole(final String role) {

        this.role = role;

    }

}

And finally set the query parameters i.e. compositeKey values(userid,projectid) as :

final Query query = em.createNamedQuery("UserProject.findByAll");

        query.setParameter("userid",userProjectDO.getUserid());

        query.setParameter("projectid",userProjectDO.getProjectid());

        List<UserProject> userProjectList = query.getResultList();

userProjectList would contain the row which matches the compositeKey (userId,projectId)

One advantage I see with this approach is that I can write N number of named queries inside the entity class as per the need/requirement. For ex: If we need to work on a view created out of this table. It can be easily achieved by first creating the view and then write another named query to work on it.