java - Oracle MERGE 和准备好的语句

Question

我有一个备份实用程序，用于还原部分。这是我的 table :

CREATE TABLE "SBOOKS"."DEV_CORPUS" 
   (    "CORPUSID" NUMBER(9,0) NOT NULL ENABLE, 
    "CORPUS_NAME" VARCHAR2(768 BYTE) NOT NULL ENABLE, 
    "CORPUSLASTSYNC" DATE, 
     PRIMARY KEY ("CORPUSID")

在恢复类中，我想在表中查找主键，如果存在则更新行，如果不存在则插入行。
现在的问题是我需要从类中传递参数(它们不存在于任何表中)，我该怎么做？你的建议是什么？使用准备好的陈述还是什么？如何？请提供示例或来源链接。

更多信息:我正在使用 oracle sql developer 和 java netbeans。

编辑: 我正在命令窗口中尝试:

  MERGE INTO dev_corpus a  
  USING (SELECT corpusid, corpus_name, corpusdesc, corpusimageids, rocf1, rocf2, rocf3, rocc1, rocc2, rocc3, corpusactive, corpusrunfrequency, corpuslastrun, corpuslastsync, rocsettingid, corpusaffinity, corpusterms, corpusdomain FROM dual WHERE corpusId = 1000156 AND corpus_name = 'sahar' AND corpusdesc = 'sahaaaaa' AND corpusimageids IS NULL AND rocf1 IS NULL AND rocf2 IS NULL AND rocf3 IS NULL AND rocc1 IS NULL AND rocc2 IS NULL AND rocc3 IS NULL AND corpusactive IS NULL AND corpusrunfrequency IS NULL AND corpuslastrun IS NULL AND corpuslastsync IS NULL AND rocsettingid IS NULL AND corpusaffinity IS NULL AND corpusterms IS NULL AND corpusdomain IS NULL) incoming 
  ON (a.corpusid = incoming.corpusid )  
  WHEN MATCHED THEN 
  UPDATE SET corpusid = incoming.corpusid , corpus_name = incoming.corpus_name , corpusdesc = incoming.corpusdesc , corpusimageids = incoming.corpusimageids , rocf1 = incoming.rocf1 , rocf2 = incoming.rocf2 , rocf3 = incoming.rocf3 , rocc1 = incoming.rocc1 , rocc2 = incoming.rocc2 , rocc3 = incoming.rocc3 , corpusactive = incoming.corpusactive , corpusrunfrequency = incoming.corpusrunfrequency , corpuslastrun = incoming.corpuslastrun , corpuslastsync = incoming.corpuslastsync , rocsettingid = incoming.rocsettingid , corpusaffinity = incoming.corpusaffinity , corpusterms = incoming.corpusterms , corpusdomain = incoming.corpusdomain 
  WHEN NOT MATCHED THEN 
  INSERT (corpusid, corpus_name, corpusdesc, corpusimageids, rocf1, rocf2, rocf3, rocc1, rocc2, rocc3, corpusactive, corpusrunfrequency, corpuslastrun, corpuslastsync, rocsettingid, corpusaffinity, corpusterms, corpusdomain) 
  VALUES (incoming.corpusid, incoming.corpus_name, incoming.corpusdesc, incoming.corpusimageids, incoming.rocf1, incoming.rocf2, incoming.rocf3, incoming.rocc1, incoming.rocc2, incoming.rocc3, incoming.corpusactive, incoming.corpusrunfrequency, incoming.corpuslastrun, incoming.corpuslastsync, incoming.rocsettingid, incoming.corpusaffinity, incoming.corpusterms, incoming.corpusdomain)

这是真正的表:

CREATE TABLE "SBOOKS"."DEV_CORPUS" 
   (    "CORPUSID" NUMBER(9,0) NOT NULL ENABLE, 
    "CORPUS_NAME" VARCHAR2(768 BYTE) NOT NULL ENABLE, 
    "CORPUSDESC" VARCHAR2(4000 BYTE), 
    "CORPUSIMAGEIDS" VARCHAR2(768 BYTE), 
    "ROCF1" FLOAT(63), 
    "ROCF2" FLOAT(63), 
    "ROCF3" FLOAT(63), 
    "ROCC1" FLOAT(63), 
    "ROCC2" FLOAT(63), 
    "ROCC3" FLOAT(63), 
    "CORPUSACTIVE" NUMBER(3,0), 
    "CORPUSRUNFREQUENCY" NUMBER(3,0), 
    "CORPUSLASTRUN" DATE, 
    "CORPUSLASTSYNC" DATE, 
    "ROCSETTINGID" NUMBER(3,0), 
    "CORPUSAFFINITY" NUMBER(3,0), 
    "CORPUSTERMS" VARCHAR2(4000 BYTE), 
    "CORPUSDOMAIN" NUMBER(3,0), 
     PRIMARY KEY ("CORPUSID")

抛出这个错误:

Error at Command Line:2 Column:644
Error report:
SQL Error: ORA-00904: "CORPUSDOMAIN": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

.

我不知道我错过了什么。你有什么想法吗？

谢谢!

编辑 2:最后这适用于 comand win:

MERGE INTO dev_corpus a
  USING (SELECT 1000156 corpusid, 'sss2' corpus_name, 'sahaaaaaar' corpusdesc, null corpusimageids, null rocf1, null rocf2, null rocf3, null rocc1, null rocc2, null rocc3, null corpusactive, null corpusrunfrequency, null corpuslastrun, null corpuslastsync, null rocsettingid, null corpusaffinity, null corpusterms, null corpusdomain FROM dual) incoming 
  ON (a.corpusid = incoming.corpusid )  
  WHEN MATCHED THEN 
  UPDATE SET corpus_name = incoming.corpus_name , corpusdesc = incoming.corpusdesc , corpusimageids = incoming.corpusimageids , rocf1 = incoming.rocf1 , rocf2 = incoming.rocf2 , rocf3 = incoming.rocf3 , rocc1 = incoming.rocc1 , rocc2 = incoming.rocc2 , rocc3 = incoming.rocc3 , corpusactive = incoming.corpusactive , corpusrunfrequency = incoming.corpusrunfrequency , corpuslastrun = incoming.corpuslastrun , corpuslastsync = incoming.corpuslastsync , rocsettingid = incoming.rocsettingid , corpusaffinity = incoming.corpusaffinity , corpusterms = incoming.corpusterms , corpusdomain = incoming.corpusdomain 
  WHEN NOT MATCHED THEN 
  INSERT (corpusid, corpus_name, corpusdesc, corpusimageids, rocf1, rocf2, rocf3, rocc1, rocc2, rocc3, corpusactive, corpusrunfrequency, corpuslastrun, corpuslastsync, rocsettingid, corpusaffinity, corpusterms, corpusdomain) 
  VALUES (incoming.corpusid, incoming.corpus_name, incoming.corpusdesc, incoming.corpusimageids, incoming.rocf1, incoming.rocf2, incoming.rocf3, incoming.rocc1, incoming.rocc2, incoming.rocc3, incoming.corpusactive, incoming.corpusrunfrequency, incoming.corpuslastrun, incoming.corpuslastsync, incoming.rocsettingid, incoming.corpusaffinity, incoming.corpusterms, incoming.corpusdomain)

但这在 java 类 nooot 中适用于更新案例，但如果它是插入案例，它将起作用。在更新的情况下它不会抛出任何错误它只是卡住直到我从数据库中删除记录，然后它插入它，所以没有更新!。你发现这段代码有什么问题吗？

preparedStatement = dbConnection.prepareStatement("MERGE INTO dev_corpus a " +
                              "USING (SELECT ? corpusid, ? corpus_name, ? corpusdesc, ? corpusimageids, ? rocf1, ? rocf2, ? rocf3, ? rocc1, ? rocc2, ? rocc3, ? corpusactive, ? corpusrunfrequency, ? corpuslastrun, ? corpuslastsync, ? rocsettingid, ? corpusaffinity, ? corpusterms, ? corpusdomain FROM dual) incoming " +
                              "ON (a.corpusid = incoming.corpusid ) " +
                              "WHEN MATCHED THEN " +
                              "UPDATE SET corpus_name = incoming.corpus_name , corpusdesc = incoming.corpusdesc , corpusimageids = incoming.corpusimageids , rocf1 = incoming.rocf1 , rocf2 = incoming.rocf2 , rocf3 = incoming.rocf3 , rocc1 = incoming.rocc1 , rocc2 = incoming.rocc2 , rocc3 = incoming.rocc3 , corpusactive = incoming.corpusactive , corpusrunfrequency = incoming.corpusrunfrequency , corpuslastrun = incoming.corpuslastrun , corpuslastsync = incoming.corpuslastsync , rocsettingid = incoming.rocsettingid , corpusaffinity = incoming.corpusaffinity , corpusterms = incoming.corpusterms , corpusdomain = incoming.corpusdomain " +
                              "WHEN NOT MATCHED THEN " +
                              "INSERT (corpusid, corpus_name, corpusdesc, corpusimageids, rocf1, rocf2, rocf3, rocc1, rocc2, rocc3, corpusactive, corpusrunfrequency, corpuslastrun, corpuslastsync, rocsettingid, corpusaffinity, corpusterms, corpusdomain) " +
                              "VALUES (incoming.corpusid, incoming.corpus_name, incoming.corpusdesc, incoming.corpusimageids, incoming.rocf1, incoming.rocf2, incoming.rocf3, incoming.rocc1, incoming.rocc2, incoming.rocc3, incoming.corpusactive, incoming.corpusrunfrequency, incoming.corpuslastrun, incoming.corpuslastsync, incoming.rocsettingid, incoming.corpusaffinity, incoming.corpusterms, incoming.corpusdomain)");

谢谢!

Answer 1

终于成功了!下面是 SQL 命令的查询:

MERGE INTO dev_corpus a
  USING (SELECT 1000156 corpusid, 'sss2' corpus_name, 'sahaaaaaar' corpusdesc, null corpusimageids, null rocf1, null rocf2, null rocf3, null rocc1, null rocc2, null rocc3, null corpusactive, null corpusrunfrequency, null corpuslastrun, null corpuslastsync, null rocsettingid, null corpusaffinity, null corpusterms, null corpusdomain FROM dual) incoming 
  ON (a.corpusid = incoming.corpusid )  
  WHEN MATCHED THEN 
  UPDATE SET corpus_name = incoming.corpus_name , corpusdesc = incoming.corpusdesc , corpusimageids = incoming.corpusimageids , rocf1 = incoming.rocf1 , rocf2 = incoming.rocf2 , rocf3 = incoming.rocf3 , rocc1 = incoming.rocc1 , rocc2 = incoming.rocc2 , rocc3 = incoming.rocc3 , corpusactive = incoming.corpusactive , corpusrunfrequency = incoming.corpusrunfrequency , corpuslastrun = incoming.corpuslastrun , corpuslastsync = incoming.corpuslastsync , rocsettingid = incoming.rocsettingid , corpusaffinity = incoming.corpusaffinity , corpusterms = incoming.corpusterms , corpusdomain = incoming.corpusdomain 
  WHEN NOT MATCHED THEN 
  INSERT (corpusid, corpus_name, corpusdesc, corpusimageids, rocf1, rocf2, rocf3, rocc1, rocc2, rocc3, corpusactive, corpusrunfrequency, corpuslastrun, corpuslastsync, rocsettingid, corpusaffinity, corpusterms, corpusdomain) 
  VALUES (incoming.corpusid, incoming.corpus_name, incoming.corpusdesc, incoming.corpusimageids, incoming.rocf1, incoming.rocf2, incoming.rocf3, incoming.rocc1, incoming.rocc2, incoming.rocc3, incoming.corpusactive, incoming.corpusrunfrequency, incoming.corpuslastrun, incoming.corpuslastsync, incoming.rocsettingid, incoming.corpusaffinity, incoming.corpusterms, incoming.corpusdomain)

这里是使用 pstmt 的 java 代码:

preparedStatement = dbConnection.prepareStatement("MERGE INTO dev_corpus a " +
                              "USING (SELECT ? corpusid, ? corpus_name, ? corpusdesc, ? corpusimageids, ? rocf1, ? rocf2, ? rocf3, ? rocc1, ? rocc2, ? rocc3, ? corpusactive, ? corpusrunfrequency, ? corpuslastrun, ? corpuslastsync, ? rocsettingid, ? corpusaffinity, ? corpusterms, ? corpusdomain FROM dual) incoming " +
                              "ON (a.corpusid = incoming.corpusid) " +
                              "WHEN MATCHED THEN " +
                              "UPDATE SET a.corpus_name = incoming.corpus_name , a.corpusdesc = incoming.corpusdesc , a.corpusimageids = incoming.corpusimageids , a.rocf1 = incoming.rocf1 , a.rocf2 = incoming.rocf2 , a.rocf3 = incoming.rocf3 , a.rocc1 = incoming.rocc1 , a.rocc2 = incoming.rocc2 , a.rocc3 = incoming.rocc3 , a.corpusactive = incoming.corpusactive , a.corpusrunfrequency = incoming.corpusrunfrequency , a.corpuslastrun = incoming.corpuslastrun , a.corpuslastsync = incoming.corpuslastsync , a.rocsettingid = incoming.rocsettingid , a.corpusaffinity = incoming.corpusaffinity , a.corpusterms = incoming.corpusterms , a.corpusdomain = incoming.corpusdomain " +
                              "WHEN NOT MATCHED THEN " +
                              "INSERT (corpusid, corpus_name, corpusdesc, corpusimageids, rocf1, rocf2, rocf3, rocc1, rocc2, rocc3, corpusactive, corpusrunfrequency, corpuslastrun, corpuslastsync, rocsettingid, corpusaffinity, corpusterms, corpusdomain) " +
                              "VALUES (incoming.corpusid, incoming.corpus_name, incoming.corpusdesc, incoming.corpusimageids, incoming.rocf1, incoming.rocf2, incoming.rocf3, incoming.rocc1, incoming.rocc2, incoming.rocc3, incoming.corpusactive, incoming.corpusrunfrequency, incoming.corpuslastrun, incoming.corpuslastsync, incoming.rocsettingid, incoming.corpusaffinity, incoming.corpusterms, incoming.corpusdomain)");

问题部分有create table，希望对大家有帮助! :)