when i execute below query in sql server 2008 console its working fine.but when execute it through java program
its not giving any error or else its not proceeding further.
what this query does is based on the select query condition(if copies and number_copies not equal) it will be inserted into the JOBS table and return the primary key value.
previously i have written select query and insert statement in different transactions.
based on the conditions used to execute INSERT statement.
but now want to execute both (SELECT and INSERT) in the same transaction to avoid race conditions.
INSERT INTO JOBS(TYPE,DOC_ID,JOB_DATE)
SELECT COUNT(*) COPIES,
ID =646464 AND
J.TYPE IN('T2') AND
J.STATUS IN('NEW') AND
GROUP BY T.COPIES ,T.NUMBER_COPIES
HAVING T.COPIES <> T.NUMBER_COPIES
we are using JPA and also Hibernate for the back end layer.
Query query = em.createNativeQuery(prop.getJobQuery());//getting above from properties file.
List query= query.getResultList();