Hi,
I have many years experience with SQL but I am new to Hibernate and Java. I have worked out the basics of Hibernate and built most of the required code.
The UPDATE logic below is what is required; however I changed the table names to make sure its not revealing company details.
Also please note the SQL must run as one SQL statement - other applications could update the tables at the same time this one is running.
The table design can't change as other applications are already in production using them.
Could someone please demonstrate how to do the update below in Hibernate?
Is createNativeQuery an option??
Thanks heaps for your help.
Code:
update
PERSON P
set
P.STATUS = 'Happy'
where
P.PK_ID = :person_id
and
(
select count(1)
from WORK_JOBS WJ
where WJ.RESOLVED_STATUS is null
) = 0
and
(
select count(1)
from EXPENSES EX
where EX.COST > :max_exp_cost
) = 0
;
The simplified table definitions are below.
Code:
create table PERSON
(
PK_ID NUMBER NOT NULL,
LAST_NAME VARCHAR2(100),
FIRST_NAME VARCHAR2(100),
STATUS VARCHAR2(30),
PRIMARY KEY PK_ID
)
;
create table WORK_JOBS
(
PK_ID NUMBER NOT NULL,
SUMMARY VARCHAR2(100),
DESCRIPTION VARCHAR2(1000),
RESOLVED_STATUS VARCHAR2(50),
PRIMARY KEY PK_ID
)
;
create table EXPENSES
(
PK_ID NUMBER NOT NULL,
SUMMARY VARCHAR2(100),
DESCRIPTION VARCHAR2(1000),
COST DECIMAL(10,2),
PRIMARY KEY PK_ID
)
;
Login
FAQ
Search