CODEWITHSUNDEEP
jpajpql
Oracle Man
Oracle Man

Reputation: 19

JPA Add extra fields by calling a function

How can I add an extra column in JPA query?
Suppose we have a table "users" created by following pseudocode:

create table users(name,birthdate);

User jpa entity is something like this :

@Entity
@Table("users")
public class User {

    @Basic
    @Column
    private String name;

    @Basic
    @Column
    private Date birthDate;

    @Transient
    private int age;

    //getters and setters are here
}

Note that the Age field is not exsists in the table
I want to write the following query in JQL using 

entityManager.createQuery(jql)

and calculate age over the database

select u.*, sysdate-u.birthdate age from users

Upvotes: 0

Views: 1888

Answers (3)

Oracle Man
Oracle Man

Reputation: 19

The problem is solved.
The query text is like :

String jql = "select NEW entity.User(u.id, u.name, u.birthDate, sysdate-u.birthDate as age) from User u";


and also a appropriate constructor is added to the entity

public User(int id, String name, Date birthDate, double age) {
    this.id = id;
    this.name = name;
    this.birthDate = birthDate;
    this.age = age;
}

and uses entitymanaget.createQuery(jql) instead of createNativeQuery

Upvotes: 1

aschoerk
aschoerk

Reputation: 3593

Possibly using a DB-View might help. Do you want the entity to be updateable? Would it be reasonable to have a "readonly"-entity derived from a kind of "maintenance"-entity which would contain the calculated columns by using a database-view as @Table?

create view usersview as name, birthdate, sysdate - birthdate as age from users;

Entity:

@Entity
@Table("usersview")
public class UserExtended extends User {

If you don't want two different Entity-Classes (even when one is derived from the other), there would be some exploration necessary how JPA allows updating on views, if the dbms supports it (oracle does), I have no experience in that, sorry.

Upvotes: 0

Youcef LAIDANI
Youcef LAIDANI

Reputation: 59950

Usully i solve this problem using this way :

select new name.of.package.users(u.*, sysdate - u.birthdate as age) from users u;

Edit

It leads to ORA-00923: FROM keyword not found where expected My Query text is : String jql = "select new entity.User(u.*, sysdate-u.birthdate as age) from User u";

The query contain an error in :

select new entity.User(u.*, sysdate-u.birthdate as age) from User u
//--------------------------------^^^

Upvotes: 0

Related Questions