I am trying to map an Entity from 3 tables, using @Entity, @Table, @SecondaryTables and @Column annotations as illustrated in the following example.
The Tables are: 1. employee (primary) 2. employee_detail 3. job_description
@Entity
@Table(name="EMPLOYEE")
@SecondaryTables({
@SecondaryTable(name="EMPLOYEE_DETAIL",
pkJoinColumns = @PrimaryKeyJoinColumn(name="EMPLOYEE_ID")),
@SecondaryTable(name="JOB_DESCRIPTION",
pkJoinColumns = @PrimaryKeyJoinColumn(name="JD_ID"))
})
public class Employee {
@Id
@Column(name = "ID", table= "EMPLOYEE")
private int id;
@Column(name = "FIRST_NAME", table = "EMPLOYEE")
private String firstname;
@Column(name = "LAST_NAME", table = "EMPLOYEE")
private String lastname;
@Column(name = "BANK_ACCOUNT_NO", table = "EMPLOYEE_DETAIL")
private String bankacctnumber;
@Column(name = "JOB_SUMMARY", table = "JOB_DESCRIPTION")
private String jobsummary;
@Column(name = " ??? ", table = " ?? ")
private String uniqueid;
//getters and setters for above fields
...
}
My question is, if I would like to create the field "uniqueid" by concatenating
column "ID" in table "EMPLOYEE" AND column "JOB_CODE" in table "JOB_DESCRIPTION"
This entity corresponds with the following sql query string (I used string builder for clarity):-
StringBuilder sql = new StringBuilder();
sql.append("SELECT");
sql.append(" e.FIRST_NAME AS firstname,");
sql.append(" e.LAST_NAME AS lastname,");
sql.append(" d.BANK_ACCT_NUMBER AS bankacctnumber,");
sql.append(" j.JOB_SUMMARY AS jobsummary,");
sql.append(" CONCAT(e.ID,SUBSTR(j.JOB_CODE,3,8)) AS uniqueid");
sql.append(" FROM employee e");
sql.append(" LEFT JOIN employee_detail d ON d.EMPLOYEE_ID = e.ID");
sql.append(" LEFT JOIN job_description j ON j.JD_ID = e.JD_ID ");
sql.append(" WHERE e.ID = 1 ");
exactly how should the mapping of the columns be done for the field "uniqueid"? Is this possible?
No this is not possible because JPA is using the mapped columns for reading AND writing. So a column can not be concatenated.
But you can write a simple method in your class that concatenates the fields.
Another possibility to could be a view that uses your SQL statement but then you can only read and now write because the view contains fields of more than one table.