• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Retrieving SQL generated value

 
Rory Slegtenhorst
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,

I'm still pretty new to hibernate, so I shall quickly explain my situation.

I am converting a php based webapp into java. I have most of the work already done, but I get stuck on the same problem I had in php LOL...

In the previous version I was using stored functions to calculate an "availability" date. Here's the function from MySQL:

FUNCTION `sf_CalcAvailDate`(user_id int) RETURNS date
BEGIN
DECLARE employDate DATE;
DECLARE startDate DATE;
DECLARE endDate DATE;
DECLARE availDate DATE;
SELECT employees.employDate INTO employDate FROM employees WHERE id=user_id;
SET startDate = sf_CalcStartDate(user_id);
SET endDate = sf_CalcEndDate(user_id);
IF employDate > CURDATE() THEN
SET availDate = employDate;
ELSE
IF startDate > CURDATE() THEN
SET availDate = NULL;
ELSE
IF endDate > CURDATE() THEN
SET availDate = endDate;
ELSE
SET availDate = NULL;
END IF;
END IF;
END IF;
RETURN availDate;
END

And yes, the sf_CalcStartDate/sf_CalcEndDate are also stored funtions:

FUNCTION `sf_CalcStartDate`(user_id int) RETURNS date
RETURN (SELECT MIN(start) FROM offers WHERE employee=user_id GROUP BY employee)

and

FUNCTION `sf_CalcEndDate`(user_id int) RETURNS date
RETURN (SELECT MIN(end) FROM offers WHERE employee=user_id GROUP BY employee)

I would call it using something like:

SELECT employees.id employees.name, employees.price...... , sf_CalcAvailDate(id) as availdate FROM .....

This means that the function will be called for each employee row retrieved.

In the new version I have properly annotated my business objects, and use them with much pleasure in my servlets.
But now I really need this "availability" date to come alive too

How can I solve this stinky little prob that "seems" so easy...?

The real question of course is:
How do I annotate my Employee class so that this field becomes an automatic?
The real snag is that this retrieved value uses the current date for it's calculation (hence I came to the conclusion to use stored functions when in php).

I hope someone can point me in the right direction.
Feel free to request more info, I'd be happy to provide it.

Thanks in advance and cheers all,
Rory
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well you could map the property to a "formula" and if Hibernate doesn't understand the formula as a standard SQL, then it is supposed to pass it literally as you typed it in the formula value.

Now, Hibernate, also allows you to set the "insert" sql statement that is used for a class to the database, that might be another route to take.

One of those, might work for you. I suggest trying the first suggestion, and if that doesn't work, try the second.

Mark
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Here is a link to the Hibernate docs on formula, it is just an attribute of the property tag.

http://www.hibernate.org/hib_docs/v3/reference/en/html/mapping.html#mapping-declaration-property

Mark
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic