Ksql批量插入

leehuilin

INSERT INTO bdc_applicant T ( T, T.version, T.Event_id, T.fSQRXM, )
select guid(), 0, '828FA1BAFD694B66A20357EA690516D0', T1.fSQRXM from bdc_applicant T1  where T1.Event_id = '3B522B1BA6334352B06FFF30837EAA67' and T1.fSQRLB = '0'以上语句，ksql执行时会报错，guid()这个方法ksql使用什么来代替；select 会有多条数据

jishuang

:guid()

leehuilin

jishuang 发表于 2019-6-11 11:57
:guid()

select存在多条，不好使啊

jishuang

这个肯定没问题，看具体的完整的错误提示，确认是主键的问题？

leehuilin

jishuang 发表于 2019-6-11 13:19
这个肯定没问题，看具体的完整的错误提示，确认是主键的问题？

java.lang.reflect.InvocationTargetException
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at com.justep.system.action.Engine.invokeActions(Native Method)
        at com.justep.system.action.Engine.invokeActions(Unknown Source)
        at com.justep.business.server.BusinessServer.doAction(Unknown Source)
        at com.justep.business.server.BusinessServer.doExcute(Unknown Source)
        at com.justep.business.server.BusinessServer.excute(Unknown Source)
        at com.justep.business.server.BusinessServerServlet.execService(Unknown Source)
        at com.justep.business.server.BusinessServerServlet.service(Unknown Source)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
        at com.justep.x.bs.BusinessServerServlet.service(Unknown Source)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:857)
        at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
        at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
        at java.lang.Thread.run(Thread.java:662)
Caused by: com.justep.exception.BusinessException: 编码: JUSTEP150210; 提示: 执行KSQL出错, KSQL: INSERT INTO bdc_applicant T ( T, T.version, T.Event_id, T.fSQRXM, T.fSQRLB, T.fZJZL, T.fZJHM, T.fDHHM, T.fXB, T.fSQRLX, T.fGYFS, T.fDZ, T.fFDDBR, T.fFRDHHM, T.fSZXY, T.fYBBM, T.fGZDW, T.fDZYJ, T.fQLMJ, T.fQLBL, T.fFZJG, T.fDLJGMC, T.fDLRXM, T.fDLRDH, T.fIsHolder, T.fCountryCode, T.fDomicPrvCode, T.fOwnIndCode) (select :guid(), 0, '99047B40837E4E15A9F0356EC3F96F23', T1.fSQRXM, '1', T1.fZJZL, T1.fZJHM, T1.fDHHM, T1.fXB, T1.fSQRLX, T1.fGYFS, T1.fDZ, T1.fFDDBR, T1.fFRDHHM, T1.fSZXY, T1.fYBBM, T1.fGZDW, T1.fDZYJ, T1.fQLMJ, T1.fQLBL, T1.fFZJG, T1.fDLJGMC, T1.fDLRXM, T1.fDLRDH, T1.fIsHolder, T1.fCountryCode, T1.fDomicPrvCode, T1.fOwnIndCode from bdc_applicant T1  where T1.Event_id = 'A7A15EEAE0C64A0081EAB2F4041C18C5' and T1.fSQRLB = '0')
        at com.justep.exception.BusinessException.create(Unknown Source)
        at com.justep.system.data.impl.TableUtilsImplement.ksqlInsert(Unknown Source)
        at com.justep.system.data.impl.TableUtilsImplement.ksqlExecuteUpdate(Unknown Source)
        at com.justep.system.data.KSQL.executeUpdate(Unknown Source)
        at com.justep.system.data.KSQL.executeUpdate(Unknown Source)
        at com.justep.system.data.KSQL.executeUpdate(Unknown Source)
        at com.re.com.unit.utils.ReUnitUtils.gencRaByUnit(ReUnitUtils.java:149)
        at com.re.com.unit.ReUnit.gencRaByUnit(ReUnit.java:37)
        ... 26 more
Caused by: com.justep.exception.BusinessException: 编码: JUSTEP150039; 提示: 执行sql: INSERT INTO bdc_applicant(bdc_applicant.fID, bdc_applicant.VERSION, bdc_applicant.EVENT_ID, bdc_applicant.FSQRXM, bdc_applicant.FSQRLB, bdc_applicant.FZJZL, bdc_applicant.FZJHM, bdc_applicant.FDHHM, bdc_applicant.FXB, bdc_applicant.FSQRLX, bdc_applicant.FGYFS, bdc_applicant.FDZ, bdc_applicant.FFDDBR, bdc_applicant.FFRDHHM, bdc_applicant.FSZXY, bdc_applicant.FYBBM, bdc_applicant.FGZDW, bdc_applicant.FDZYJ, bdc_applicant.FQLMJ, bdc_applicant.FQLBL, bdc_applicant.FFZJG, bdc_applicant.FDLJGMC, bdc_applicant.FDLRXM, bdc_applicant.FDLRDH, bdc_applicant.FISHOLDER, bdc_applicant.FCOUNTRYCODE, bdc_applicant.FDOMICPRVCODE, bdc_applicant.FOWNINDCODE)(SELECT  ?, ?, ?, T1.FSQRXM AS fSQRXM, ?, T1.FZJZL AS fZJZL, T1.FZJHM AS fZJHM, T1.FDHHM AS fDHHM, T1.FXB AS fXB, T1.FSQRLX AS fSQRLX, T1.FGYFS AS fGYFS, T1.FDZ AS fDZ, T1.FFDDBR AS fFDDBR, T1.FFRDHHM AS fFRDHHM, T1.FSZXY AS fSZXY, T1.FYBBM AS fYBBM, T1.FGZDW AS fGZDW, T1.FDZYJ AS fDZYJ, T1.FQLMJ AS fQLMJ, T1.FQLBL AS fQLBL, T1.FFZJG AS fFZJG, T1.FDLJGMC AS fDLJGMC, T1.FDLRXM AS fDLRXM, T1.FDLRDH AS fDLRDH, T1.FISHOLDER AS fIsHolder, T1.FCOUNTRYCODE AS fCountryCode, T1.FDOMICPRVCODE AS fDomicPrvCode, T1.FOWNINDCODE AS fOwnIndCode FROM bdc_applicant T1  WHERE  (T1.EVENT_ID = ?) AND (T1.FSQRLB = ?) ), binds: [2511329205A04A73A5A936AC51DE8B21, 0, 99047B40837E4E15A9F0356EC3F96F23, 1, A7A15EEAE0C64A0081EAB2F4041C18C5, 0]出错
        at com.justep.exception.BusinessException.create(Unknown Source)
        at com.justep.system.ksql.KSQLUtils.execSqlUpdate(Unknown Source)
        ... 33 more
Caused by: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: 违反唯一约束条件 (BDC.SYS_C0022358)

        at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:85)
        at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
        at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
        at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
        at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:953)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3387)
        at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3468)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1350)
        at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
        at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
        ... 34 more

jishuang

INSERT INTO bdc_applicant T ( T, T.version) (select :guid(), 0)
这样测试看看

leehuilin

jishuang 发表于 2019-6-11 14:04
INSERT INTO bdc_applicant T ( T, T.version) (select :guid(), 0)
这样测试看看

只有一条的时候是不会报错的，存在多条的时候才会提示主键重复

jishuang

这样写ksql中提供的就有guid函数：insert into AP_CSINSERT test(test,test.fName,test.fCode)(Select guid(),test1.fName,test1.fCode from AP_Test test1 );

也可以用数据库函数
insert into AP_CSINSERT test(test,test.fName,test.fCode)(Select SQL.sys_guid(),test1.fName,test1.fCode from AP_Test test1 );
sys_guid()是oracle的，不同的数据库函数不一样确认自己使用的数据库

leehuilin

jishuang 发表于 2019-6-11 15:15
这样写ksql中提供的就有guid函数：insert into AP_CSINSERT test(test,test.fName,test.fCode)(Select guid ...

已找到：select 没有用括号；guid（）前不加冒号

正确写法