Home javax.persistence @Column mapping of 2 columns of two different table
Reply: 1

javax.persistence @Column mapping of 2 columns of two different table

lazyduckiy
1#
lazyduckiy Published in 2018-01-11 07:53:59Z

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?

Simon Martinelli
2#
Simon Martinelli Reply to 2018-01-11 08:21:54Z

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.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.348898 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO