Hibernate version:
3.2.5.ga
Mapping documents:
SQL Table Creation:
Code:
DROP TABLE IF EXISTS stay;
CREATE TABLE stay
(
stay_id SERIAL,
stay_cntr VARCHAR(75),
stay_univ VARCHAR(100),
stay_city VARCHAR(75),
stay_country VARCHAR(50),
stay_subj VARCHAR(150),
stay_start_date DATE,
stay_end_date DATE,
CONSTRAINT PK_STAY PRIMARY KEY(stay_id)
);
Mapped Attributes in Entity:
Code:
@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
@Column(name="stay_id")
private Integer stayID;
@Column(name="stay_cntr") private String center;
@Column(name="stay_univ") private String university;
@Column(name="stay_city") private String city;
@Column(name="stay_country") private String country;
@Column(name="stay_subj") private String subject;
@Formula(value="stay_end_date - stay_start_date") private Integer duration;
@Column(name="stay_start_date") @Temporal(TemporalType.DATE) private Calendar startDate;
@Column(name="stay_end_date") @Temporal(TemporalType.DATE) private Calendar endDate;
Name and version of the database you are using:PostgreSQL 8.3
The generated SQL (show_sql=true):(Sequence of insert one new entroy and retrieving all elements in the table)
Code:
Hibernate:
insert
into
stay
(stay_cntr, stay_city, stay_country, stay_end_date, stay_start_date, stay_subj, stay_univ)
values
(?, ?, ?, ?, ?, ?, ?)
Hibernate:
select
currval('stay_stay_id_seq')
Hibernate:
select
stay0_.stay_id as stay1_8_,
stay0_.stay_cntr as stay2_8_,
stay0_.stay_city as stay3_8_,
stay0_.stay_country as stay4_8_,
stay0_.stay_end_date as stay5_8_,
stay0_.stay_start_date as stay6_8_,
stay0_.stay_subj as stay7_8_,
stay0_.stay_univ as stay8_8_,
stay0_.stay_end_date - stay0_.stay_start_date as formula0_
from
stay stay0_
Question
Hi! My problem refers to use a "derivate field" in my entity from two other attributes (in the database). Those attributes are
Calendar and are mapped as
Date in the DB, but I need to calculate the days (or weeks) between them.
Due to this, I try to use the @Formula above, because using PostgreSQL we can subtract dates, but this doesn't work finally retrieved null to the entity's attribute.
I think (because now i'm a bit noob in Hibernate using) @Formula can help me in this situation (due the attribute "duration" is like transient -- not represented in the database), but I don't know the way to build the appropiated query.
So, the question is: ¿any way to subtract dates in PostgreSQL using Hibernate? Perhaps... ¿declaring a custom function in PostgreSQL and call it from the query formula?
Thanks.
P.D. I also tried with a "SELECT ..." in the formula, but it didn't work
Login
FAQ
Search