[FIXED] Spring Data JPA/Hibernate - using EntityManager for queries

Issue

I have these entities, from which I want get List of Tuples containing information from both of them:

which should look like this:

+--------+-----+---------+
|name    |login|user_type|
+--------+-----+---------+
|        |admin|admin    |
|John Doe|john |user     |
|Jane Doe|janed|user     |
|........|.....|.........|

The thing is, my JPA skill got quite rusty and I forgot how to use Entity Managers. I know how to make basic JPQL queries or build-in stuff, but sadly it's not enough for me (since I need to use that list for filling up table in my UI). So, how did I should use Entity Managers? (if I should use that at all ;) )

Edit: now I think that using DTO projection is better; here's my mapping class

public class PersonUserMap {
    private Integer personID;
    private String name;
    private String login;
    private UserType userType;
    public Integer getPersonID() {
        return personID;
    }
    public String getName() {
        return name;
    }
    public String getLogin() {
        return login;
    }
    public UserType getUserType() { //custom Enum
        return userType;
    }
}

my annotation in People class:

@SqlResultSetMapping(
    name = "PersonUserMapping",
    classes = @ConstructorResult(
        columns = { @ColumnResult(name = "personID", type=Integer.class),
            @ColumnResult(name = "name"),
            @ColumnResult(name = "login"),
            @ColumnResult(name = "userType",type = UserType.class)},
        targetClass = PersonUserMap.class))

and when using native query like this: Query q = entityManager.createNativeQuery("Select p.personid, p.first_name || ' ' || p.last_name as name, u.login, u.user_type from people p join users u on p.user_idusers = u.idusers","PersonUserMapping"); it throws exception Could not resolve column name in result set [userType]

Solution

Thanks for @Chris help I finally got somewhere; my set mapping is looking like this:

@SqlResultSetMapping(
    name = "PersonUserMapping",
    classes = @ConstructorResult(
        columns = { @ColumnResult(name = "personid", type=Integer.class),
            @ColumnResult(name = "name"),
            @ColumnResult(name = "login"),
            @ColumnResult(name = "user_type",type = UserType.class)},
        targetClass = PersonUserMap.class))

and my query looks like this

Query q = entityManager.createNativeQuery("select personid, concat(first_name, ' ', last_name) as 'name', users.login, users.user_type from aspirejestracja.people"
                + " full join aspirejestracja.users on user_idusers = users.idusers ", "PersonUserMapping");

now I can display all users which I need :)

Answered By - Kris_1313
Answer Checked By - Senaida (JavaFixing Volunteer)