JPA Entity mapped by SQL Join Query

Question

I want to create an entity ReportEntry which is mapped by an SQL. Here are two tables - user and group.

mysql> desc user;
+------------------------+--------------+------+-----+---------+----------------+
| Field                  | Type         | Null | Key | Default | Extra          |
+------------------------+--------------+------+-----+---------+----------------+
| id                     | int(11)      | NO   | PRI | NULL    | auto_increment |
| firstName              | varchar(255) | YES  |     | NULL    |                |
| lastName               | varchar(255) | YES  |     | NULL    |                |
| active                 | bit(1)       | NO   |     | NULL    |                |
| language               | varchar(20)  | NO   |     | NULL    |                |
| activationDate         | datetime     | YES  |     | NULL    |                |
| group_id               | int(11)      | YES  | MUL | NULL    |                |
|                                                                               |
|                           ...More columns...                                  |
|                                                                               |
+------------------------+--------------+------+-----+---------+----------------+

mysql> desc group;
+----------------------------+--------------+------+-----+---------+----------------+
| Field                      | Type         | Null | Key | Default | Extra          |
+----------------------------+--------------+------+-----+---------+----------------+
| id                         | int(11)      | NO   | PRI | NULL    | auto_increment |
| displayName                | varchar(255) | YES  | MUL | NULL    |                |
| description                | varchar(255) | YES  |     | NULL    |                |
+----------------------------+--------------+------+-----+---------+----------------+

I am joining these tables on group_id using the below SQL query.

SELECT u.* FROM user u JOIN group g ON u.group_id = g.id

For the above query, I want to create an Entity and use the above SQL query as its mapping. How can I achieve this?

I don't want just the mapping, but I also want to use the entity to be able to query for records as well. For example, let's say I am able to create the mapping with entity, RecordEntry, I should be able to get a specific record with query like FROM RecordEntry WHERE id = :id" and I will passid```` as parameter. That way the final query should get executed in native form would be like(for id=1).

SELECT u.* FROM user u JOIN group g ON u.group_id = g.id AND id = 1

can you please provide an example of the result of this query ? — Mohamed Nabli, Jun 09 '16 at 08:54
Possible duplicate of [JPA : How to convert a native query result set to POJO class collection](http://stackoverflow.com/questions/13012584/jpa-how-to-convert-a-native-query-result-set-to-pojo-class-collection) — nezdolik, Jun 09 '16 at 08:58
@nezdolik, I have updated my question to give more details as I need an entity which I can query in JPA. — divinedragon, Jun 09 '16 at 09:08

tringel · Accepted Answer · 2016-06-09T09:14:45.130

Instead of mapping the result to a POJO, I would suggest creating entities for both classes and mapping them with the one to one annotation JPA provides:

@Entity
public class User{
  @Id
  private long id;

  @OneToOne
  @JoinColumn(name="GROUP_ID")
  private Group group;
  ...
}


@Entity
public class Group{
  @Id
  private long id;
  ...
}

Update to reflect updated question: You could then use e.g. the Criteria-API (or named queries), to query these entities:

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery criteriaQuery = criteriaBuilder.createQuery();
Root user = criteriaQuery.from(User.class);
criteriaQuery.where(criteriaBuilder.equal(user.get("id"), criteriaBuilder.parameter(Long.class, "id")));
Query query = entityManager.createQuery(criteriaQuery);
query.setParameter("id", id);
User result = (User)query.getSingleResult();

Source: Wikibooks on JPA

This solution is more elegant than simply creating an "all fields" POJO, because it reflects the proper association hierarchy between Users and the Group they belong to. Likewise, all User and Group attributes are still easily accessible. — Filipe Fedalto, Jun 09 '16 at 10:13
Yep. Finally did via the mappings into two classes as you suggested. — divinedragon, Jun 17 '16 at 04:57

JPA Entity mapped by SQL Join Query

1 Answers1