
このSQLの悪いところがわかりません。
SQLException:列索引が無効です。
頭の中が????だらけです
A5SQLだとうまくいくのに、なぜだめなのでしょうか。
バージョン:Oracle12cです。
java
1public boolean create(Gyomu gyomu) throws SQLException, ClassNotFoundException { 2 3 Connection conn = null; 4 5 try { 6 7 conn = connectManager.getConn(false); 8 9 // INSERT文の準備(idは自動連番なので指定しない)WHERE句で指定する 10 // StringBuilderで文字列の連結ができる 11 12 13 StringBuilder sb = new StringBuilder(); 14 15 sb.append("INSERT"); 16 sb.append(" INTO GYOMUHOKOKU("); 17 sb.append("HOKOKUHIDUKE"); 18 sb.append(")VALUES("); 19 sb.append("?);"); 20 21 // sb.toStringで、文字列にする 22 // sbのままだと、prepareStatementが受け取れない 23 24 PreparedStatement pStmt = conn.prepareStatement(sb.toString()); 25 26 27 pStmt.setString(1, gyomu.getHokokuhiduke()); 28 29 30 31 int result = pStmt.executeUpdate(); 32 33 StringBuilder sb2 = new StringBuilder(); 34 35 //HOKOKUSAKIテーブルのINSERT文の発行 36 37 38 39 sb2.append(" INSERT "); 40 sb2.append(" INTO HOKOKUSAKI("); 41 sb2.append(" HOKOKUSAKISEQ"); 42 sb2.append(" , STETUSID = ?"); 43 sb2.append(" , USERID = ?"); 44 sb2.append(" , HOKOKUSAKINAME = ?"); 45 sb2.append(" , CREATEDATE = ?"); 46 sb2.append(" , CREATEUSER = ?"); 47 sb2.append(" , UPDATEDATE = ?"); 48 sb2.append(" , UPDATEUSER = ?"); 49 sb2.append(")"); 50 sb2.append("VALUES ( "); 51 sb2.append(" ( "); 52 sb2.append(" SELECT"); 53 sb2.append(" coalesce(MAX(HOKOKUSAKISEQ), '0') + 1 "); 54 sb2.append(" FROM"); 55 sb2.append(" HOKOKUSAKI"); 56 sb2.append(" ) "); 57 sb.append(" ,?"); 58 sb.append(" ,?"); 59 sb.append(" ,?"); 60 sb.append(" ,?"); 61 sb.append(" ,?"); 62 sb.append(" ,?"); 63 sb.append(" ,?"); 64 sb2.append(");"); 65 66 PreparedStatement pStmt2 = conn.prepareStatement(sb2.toString()); 67 // INSERT文中の「?」に使用する値を設定し、SQLを完成 68 69 pStmt2.setString(1, gyomu.getStetusid()); 70 pStmt2.setString(2, gyomu.getUserId()); 71 pStmt2.setString(3, gyomu.getHokokusakiname()); 72 pStmt2.setString(4, gyomu.getCreatedate()); 73 pStmt2.setString(5, gyomu.getCreateuser()); 74 pStmt2.setString(6, gyomu.getUpdatedate()); 75 pStmt2.setString(7, gyomu.getUpdateuser()); 76 77 // INSERT文を実行 78 79 int result2 = pStmt2.executeUpdate(); 80 81 if (result != 1 && result2 != 1) { 82 conn.rollback(); 83 return false; 84 } 85 86 conn.commit(); 87 88 } catch (SQLException e) { 89 e.printStackTrace(); 90 return false; 91 } finally { 92 connectManager.close(conn); 93 94 } 95 96 return false; 97 98 } 99
java
1 2package dto; 3 4public class Gyomu { 5 6 private String kanrino; 7 private String stetusid; 8 private String stetusname; 9 private String hokokuhiduke; 10 private String yotei; 11 private String userId; 12 private String username; 13 private String hokokusakiseq; 14 private String hokokusakiname; 15 private String zisseki; 16 private String tokkizikou; 17 private String shokan; 18 private String kakunosaki; 19 private String zikaiyotei; 20 private String komento; 21 private String biko; 22 private String createdate; 23 private String createuser; 24 private String updatedate; 25 private String updateuser; 26 27 public String getKanrino() { 28 return kanrino; 29 } 30 public void setKanrino(String kanrino) { 31 this.kanrino = kanrino; 32 } 33 public String getStetusid() { 34 return stetusid; 35 } 36 public void setStetusid(String stetusid) { 37 this.stetusid = stetusid; 38 } 39 public String getStetusname() { 40 return stetusname; 41 } 42 public void setStetusname(String stetusname) { 43 this.stetusname = stetusname; 44 } 45 public String getHokokuhiduke() { 46 return hokokuhiduke; 47 } 48 public void setHokokuhiduke(String hokokuhiduke) { 49 this.hokokuhiduke = hokokuhiduke; 50 } 51 public String getYotei() { 52 return yotei; 53 } 54 public void setYotei(String yotei) { 55 this.yotei = yotei; 56 } 57 public String getUserId() { 58 return userId; 59 } 60 public void setUserId(String userId) { 61 this.userId = userId; 62 } 63 public String getUsername() { 64 return username; 65 } 66 public void setUsername(String username) { 67 this.username = username; 68 } 69 public String getHokokusakiseq() { 70 return hokokusakiseq; 71 } 72 public void setHokokusakiseq(String hokokusakiseq) { 73 this.hokokusakiseq = hokokusakiseq; 74 } 75 public String getHokokusakiname() { 76 return hokokusakiname; 77 } 78 public void setHokokusakiname(String hokokusakiname) { 79 this.hokokusakiname = hokokusakiname; 80 } 81 public String getZisseki() { 82 return zisseki; 83 } 84 public void setZisseki(String zisseki) { 85 this.zisseki = zisseki; 86 } 87 public String getTokkizikou() { 88 return tokkizikou; 89 } 90 public void setTokkizikou(String tokkizikou) { 91 this.tokkizikou = tokkizikou; 92 } 93 public String getShokan() { 94 return shokan; 95 } 96 public void setShokan(String shokan) { 97 this.shokan = shokan; 98 } 99 public String getKakunosaki() { 100 return kakunosaki; 101 } 102 public void setKakunosaki(String kakunosaki) { 103 this.kakunosaki = kakunosaki; 104 } 105 public String getZikaiyotei() { 106 return zikaiyotei; 107 } 108 public void setZikaiyotei(String zikaiyotei) { 109 this.zikaiyotei = zikaiyotei; 110 } 111 public String getKomento() { 112 return komento; 113 } 114 public void setKomento(String komento) { 115 this.komento = komento; 116 } 117 public String getBiko() { 118 return biko; 119 } 120 public void setBiko(String biko) { 121 this.biko = biko; 122 } 123 public String getCreatedate() { 124 return createdate; 125 } 126 public void setCreatedate(String createdate) { 127 this.createdate = createdate; 128 } 129 public String getCreateuser() { 130 return createuser; 131 } 132 public void setCreateuser(String createuser) { 133 this.createuser = createuser; 134 } 135 public String getUpdatedate() { 136 return updatedate; 137 } 138 public void setUpdatedate(String updatedate) { 139 this.updatedate = updatedate; 140 } 141 public String getUpdateuser() { 142 return updateuser; 143 } 144 public void setUpdateuser(String updateuser) { 145 this.updateuser = updateuser; 146 } 147 148} 149 150
SQL
1 2CREATE TABLE "SYSTEM"."GYOMUHOKOKU" 3 ( "KANRINO" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 99999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE, 4 "HOKOKUHIDUKE" DATE, 5 "YOTEI" NVARCHAR2(1000), 6 "ZISSEKI" NVARCHAR2(1000), 7 "TOKKIZIKOU" NVARCHAR2(1000), 8 "SHOKAN" NVARCHAR2(1000), 9 "KAKUNOSAKI" NVARCHAR2(1000), 10 "ZIKAIYOTEI" NVARCHAR2(1000), 11 "KOMENTO" NVARCHAR2(1000), 12 "BIKO" NVARCHAR2(1000), 13 "CREATEDATE" DATE, 14 "CREATEUSER" NVARCHAR2(10), 15 "UPDATEDATE" DATE, 16 "UPDATEUSER" NVARCHAR2(10), 17 CONSTRAINT "GYOMUHOKOKU_PKC" PRIMARY KEY ("KANRINO") 18 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 19 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 20 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 21 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) 22 TABLESPACE "SYSTEM" ENABLE 23 ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 24 NOCOMPRESS LOGGING 25 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 26 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 27 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) 28 TABLESPACE "SYSTEM"
下記のような回答は推奨されていません。
このような回答には修正を依頼しましょう。
また依頼した内容が修正された場合は、修正依頼を取り消すようにしましょう。
退会済みユーザー
2018/07/27 15:31
2018/07/27 15:34
退会済みユーザー
2018/07/27 15:38
2018/07/27 15:45
退会済みユーザー
2018/07/27 15:48
2018/07/27 15:57
退会済みユーザー
2018/07/27 16:05
2018/07/27 17:16