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
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();