hibernate是否支持运行MERGE语句?
问题描述:
当我执行下面的本地查询:hibernate是否支持运行MERGE语句?
String sql="merge into omfx.OM_PART_LOOKUP toLookup\r\n" +
" using omfx.OM_PART_LOOKUP fromLookup\r\n" +
" on(\r\n" +
" toLookup.PROJECT_ID= "+toProjectID+" and \r\n" +
" fromLookup.PROJECT_ID= "+fromProjectId +" and \r\n" +
" toLookup.scope =0 and \r\n" +
" UPPER(toLookup.PN) = UPPER(fromLookup.PN) and\r\n" +
" UPPER(toLookup.MAN) = UPPER(fromLookup.MAN)\r\n" +
" )\r\n" +
" when matched then update \r\n" +
" set \r\n" +
" \r\n" +
" toLookup.SEPN = fromLookup.SEPN ,\r\n" +
" toLookup.SE_MAN_ID = fromLookup.SE_MAN_ID ,\r\n" +
" toLookup.COM_ID = fromLookup.COM_ID ,\r\n" +
" toLookup.SE_MAN_NAME = fromLookup.SE_MAN_NAME ,\r\n" +
" toLookup.PART_CATEGORY = fromLookup.PART_CATEGORY \r\n" +
" \r\n" +
" \r\n" +
" when NOT matched then \r\n" +
" insert (PN,MAN,SEPN, SE_MAN_ID, COM_ID,SE_MAN_NAME ,PART_CATEGORY ,INSERT_DATE,PROJECT_ID)\r\n" +
" values (fromLookup.PN,fromLookup.MAN,fromLookup.SEPN, fromLookup.SE_MAN_ID, fromLookup.COM_ID , fromLookup.SE_MAN_NAME ,fromLookup.PART_CATEGORY , SYSDATE,"+toProjectID+") where\r\n" +
" fromLookup.PROJECT_ID = "+fromProjectId+" and fromLookup.scope = 0\r\n" +
" ";
System.out.println("projectOperationsBean.mergeManLookup()::sql=="+sql);
em.createNativeQuery(sql).getSingleResult();
我发现下面的错误
本地异常堆栈: 异常[EclipseLink的-4002(Eclipse持久服务 - 2.4.2.v20130514-5956486) :org.eclipse.persistence.exceptions.DatabaseException 内部异常:值java.sql.SQLException:ORA-00900:无效的SQL语句
错误代码:0
但是当我在oracle中执行这个查询时,它运行良好
是否支持hibernate运行MERGE语句?
答
感谢@Jorge坎波斯
MERGE
是一个DML语句,所以我必须执行.executeUpdate()
没有.getSingleResult()
那是什么都没有做与休眠。您正在以错误的方式执行此查询。首先从查询字符串中删除所有'\ r \ n'。其次,确保这些连接是正确的(通过这种做法的错误方式,您应该使用准备好的语句)。 –
另外,这是一个声明,应该使用executeUpdate执行而不是getSingleResult –
@JorgeCampos:'\ r \ n'不是问题,Oracle可以毫无问题地处理这个问题。 –