001 /* 002 * Copyright (c) 2009 The openGion Project. 003 * 004 * Licensed under the Apache License, Version 2.0 (the "License"); 005 * you may not use this file except in compliance with the License. 006 * You may obtain a copy of the License at 007 * 008 * http://www.apache.org/licenses/LICENSE-2.0 009 * 010 * Unless required by applicable law or agreed to in writing, software 011 * distributed under the License is distributed on an "AS IS" BASIS, 012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, 013 * either express or implied. See the License for the specific language 014 * governing permissions and limitations under the License. 015 */ 016 package org.opengion.fukurou.xml; 017 018 import org.opengion.fukurou.util.Closer; 019 import org.opengion.fukurou.util.LogWriter; 020 import org.opengion.fukurou.db.DBUtil; 021 022 import java.io.Reader; 023 import java.io.BufferedReader; 024 import java.io.InputStreamReader; 025 import java.io.FileInputStream; 026 import java.util.Map; 027 import java.util.List; 028 import java.util.ArrayList; 029 import java.util.regex.Pattern; 030 import java.util.regex.Matcher; 031 import java.util.Arrays; 032 import java.util.Locale; 033 034 import java.sql.DriverManager; 035 import java.sql.Connection; 036 import java.sql.Statement; 037 import java.sql.PreparedStatement; 038 import java.sql.ParameterMetaData; 039 import java.sql.DatabaseMetaData; 040 import java.sql.SQLException; 041 042 /** 043 * ã“ã?クラスã¯ã€ã‚ªãƒ©ã‚¯ãƒ« XDKã® oracle.xml.sql.dml.OracleXMLSave クラス㨠044 * ã»ã¼åŒæ§˜ã?ç›®çš?§ä½¿ç”¨ã§ãるクラスã§ã™ã? 045 * æ‹¡å¼µXDKå½¢å¼ã?XMLファイルをèªã¿è¾¼ã¿ã€ãƒ‡ãƒ¼ã‚¿ãƒ™ã?ス㫠INSERT ã—ã¾ã™ã? 046 * 047 * æ‹¡å¼µXDKå½¢å¼ã?å…?¨ãªã‚?オラクル XDK(Oracle XML Developer's Kit)ã«ã¤ã?¦ã¯ã€ä»¥ä¸‹ã? 048 * リンクをå‚照願ã„ã¾ã™ã? 049 * <a href="http://otn.oracle.co.jp/software/tech/xml/xdk/index.html" target="_blank" > 050 * XDK(Oracle XML Developer's Kit)</a> 051 * 052 * ã“ã?クラスã§ã¯ã€MAP を登録ã™ã‚‹[ setDefaultMap( Map ) ]ã“ã¨ã«ã‚ˆã‚Šã€? 053 * XMLファイルã«å˜åœ¨ã—ãªã?‚«ãƒ©ãƒ?‚’åˆæœŸå€¤ã¨ã—ã¦è¨å®šã™ã‚‹ã“ã¨ãŒå¯èƒ½ã«ãªã‚Šã¾ã™ã? 054 * 例ãˆã°ã€ç™»éŒ²æ—¥ã‚??登録è€??ã¾ãŸã?ã€ãƒ†ãƒ³ãƒ—レートよりå„シスãƒ?ƒ ID毎㫠055 * 登録ã™ã‚‹ãªã©ã§ã™ã? 056 * åŒæ§˜ã«ã€èªã¿å–ã£ã?XMLãƒ•ã‚¡ã‚¤ãƒ«ã®æƒ??ã‚’æ›¸ãæ›ãˆã‚‹æ©Ÿè?[ setAfterMap( Map ) ]メソãƒ?ƒ‰ 057 * ã«ã‚ˆã‚Šã€ã‚«ãƒ©ãƒ??値ã®ç½®ãæ›ãˆã‚‚å¯èƒ½ã§ã™ã? 058 * 059 * æ‹¡å¼µXDKå½¢å¼ã?å…?¨ãªã‚?オラクル XDK(Oracle XML Developer's Kit)ã«ã¤ã?¦ã¯ã€ä»¥ä¸‹ã? 060 * リンクをå‚照願ã„ã¾ã™ã? 061 * <a href="http://otn.oracle.co.jp/software/tech/xml/xdk/index.html" target="_blank" > 062 * XDK(Oracle XML Developer's Kit)</a> 063 * 064 * æ‹¡å¼µXDKå½¢å¼ã¨ã¯ã€ROW 以外ã«ã€SQL処ç?”¨ã‚¿ã‚°(EXEC_SQL)ã‚’æŒã¤ XML ファイルã§ã™ã? 065 * ã¾ãŸã?登録ã™ã‚‹ãƒ??ブル(table)ã‚?ROWSETã‚¿ã‚°ã®å±žæ?æƒ??ã¨ã—ã¦ä»˜ä¸Žã™ã‚‹ã“ã¨ãŒã§ãã¾ã™ã? 066 * (大æ–?—å°æ–‡å—ã«æ³¨æ„? 067 * ã“れã¯ã€ã‚ªãƒ©ã‚¯ãƒ«XDKã§å‡¦ç?™ã‚‹å?åˆã?無視ã•れã¾ã™ã?ã§ã€åŒæ§˜ã«æ‰±ã?“ã¨ãŒå?æ¥ã¾ã™ã? 068 * ã“ã?ã€EXEC_SQL ã¯ã€ãれãれã? XMLãƒ??タをデータベã?スã«ç™»éŒ²ã™ã‚‹éš›ã«ã€? 069 * SQL処ç?‚’è‡ªå‹•çš„ã«æµã™ç‚ºã®ã€SQLæ–?‚’記載ã—ã¾ã™ã? 070 * ã“ã?処ç??ã€ã‚¤ãƒ™ãƒ³ãƒˆæ¯Žã«å®Ÿè¡Œã•れる為ã€ãã®é…ç½®é ??é‡è¦ã§ã™ã? 071 * ã“ã?ã‚¿ã‚°ã¯ã€è¤?•°è¨˜è¿°ã™ã‚‹ã“ã¨ã‚‚å?æ¥ã¾ã™ãŒã€BODY部ã«ã¯ã€?¼‘ã¤ã®SQLæ–??ã¿è¨˜è¿°ã—ã¾ã™ã? 072 * 073 * <ROWSET tableName="XX" > 074 * <EXEC_SQL> æœ??ã«è¨˜è¼‰ã—ã¦ã€å?期å?ç?ãƒ??タクリアç?を実行ã•ã›ã‚‹ã€? 075 * delete from GEXX where YYYYY 076 * </EXEC_SQL> 077 * <MERGE_SQL> ã“ã?SQLæ–?§ UPDATEã—ã¦ã€çµæžœãŒï¼ä»¶ãªã‚‰INSERTを行ã„ã¾ã™ã? 078 * update GEXX set AA=[AA] , BB=[BB] where CC=[CC] 079 * </MERGE_SQL> 080 * <ROW num="1"> 081 * <カラãƒ?>値1</カラãƒ?> 082 * ?¥?¥?¥ 083 * <カラãƒ?>値n</カラãƒ?> 084 * </ROW> 085 * ?¥?¥?¥ 086 * <ROW num="n"> 087 * ?¥?¥?¥ 088 * </ROW> 089 * <EXEC_SQL> æœ?¾Œã«è¨˜è¼‰ã—ã¦ã€??ç›®ã®è¨å®?æ•´åˆæ?登録)を行ã†ã€? 090 * update GEXX set AA='XX' , BB='XX' where YYYYY 091 * </EXEC_SQL> 092 * <ROWSET> 093 * 094 * @version 4.0 095 * @author Kazuhiko Hasegawa 096 * @since JDK5.0, 097 */ 098 public class HybsXMLSave implements TagElementListener { 099 /** シスãƒ?ƒ ä¾å˜ã?改行記å·ã‚’ã‚»ãƒ?ƒˆã—ã¾ã™ã? */ 100 private static final String CR = System.getProperty("line.separator"); 101 102 private String tableName = null; 103 private String[] keyColumns = null; 104 private Connection connection = null; 105 private PreparedStatement insPstmt = null; // INSERT用㮠PreparedStatement 106 private PreparedStatement updPstmt = null; // UPDATE用㮠PreparedStatement 107 private ParameterMetaData insMeta = null; 108 private ParameterMetaData updMeta = null; 109 private int insCnt = 0; 110 private int updCnt = 0; 111 private int delCnt = 0; 112 private int ddlCnt = 0; // 5.6.7.0 (2013/07/27) DDLæ–??カウンター 113 private Map<String,String> defaultMap = null; 114 private Map<String,String> afterMap = null; 115 private List<String> updClms = null; 116 private String[] insClms = null; 117 private String lastSQL = null; // 5.6.6.1 (2013/07/12) ãƒ?ƒãƒ?‚°ç”¨ã€‚最後ã«ä½¿ç”¨ã—ãŸSQLæ–? 118 119 private final boolean useParamMetaData ; // 4.0.0.0 (2007/09/25) 120 121 // UPDATE時ã? [XXX] ã‚’å–りå?ã—ã¾ã™ã?\w ã¯ã€å˜èªžæ§‹æ?æ–?? [a-zA-Z_0-9]ã¨åŒã˜ 122 private static final Pattern pattern = Pattern.compile( "\\[\\w*\\]" ); 123 124 /** 125 * コãƒã‚¯ã‚·ãƒ§ãƒ³ã‚’指定ã—ã¦ã€ã‚ªãƒ–ジェクトを構築ã—ã¾ã™ã? 126 * ãƒ??ブルåã?ã€æ‹¡å¼µXDKå½¢å¼ã?ROWSETã‚¿ã‚°ã®tableName属æ?ã« 127 * 記述ã—ã¦ãŠãå¿?¦ãŒã‚りã¾ã™ã? 128 * 129 * @param conn ãƒ??タベã?ス接ç¶? 130 */ 131 public HybsXMLSave( final Connection conn ) { 132 this( conn,null ); 133 } 134 135 /** 136 * コãƒã‚¯ã‚·ãƒ§ãƒ³ã¨ãƒ??ブルåã‚’æŒ?®šã—ã¦ã€ã‚ªãƒ–ジェクトを構築ã—ã¾ã™ã? 137 * ã“ã“ã§æŒ?®šã™ã‚‹ãƒ†ãƒ¼ãƒ–ルåã?ã€ãƒ‡ãƒ•ォルトテーブルã¨ã?†æ‰±ã?§ã™ã? 138 * æ‹¡å¼µXDKå½¢å¼ã?ROWSETã‚¿ã‚°ã®tableName属æ?ã«ãƒ??ブルåãŒè¨˜è¿°ã•れã¦ã?‚‹å ´åˆã?ã€? 139 * ãã¡ã‚‰ãŒå„ªå…ˆã•れã¾ã™ã? 140 * 141 * @og.rev 4.0.0.0 (2007/09/25) ParameterMetaData を使用ã—ãŸãƒ‘ラメータè¨å®šè¿½åŠ?? 142 * @og.rev 5.3.8.0 (2011/08/01) useParamMetaData ã‚?ã“ã?クラスã§ç›´æŽ¥å–å¾—ã™ã‚‹ã?(PostgreSQL対å¿? 143 * 144 * @param conn ãƒ??タベã?ス接ç¶? 145 * @param table ãƒ??ブルå?ROWSETã‚¿ã‚°ã®table属æ?ãŒæœªè¨å®šæ™‚ã«ä½¿ç”¨) 146 */ 147 public HybsXMLSave( final Connection conn,final String table ) { 148 connection = conn; 149 tableName = table; 150 // useParamMetaData = ApplicationInfo.useParameterMetaData( conn ); 151 useParamMetaData = useParameterMetaData( connection ); // 5.3.8.0 (2011/08/01) 152 } 153 154 /** 155 * <ROWSET> ã‚¿ã‚°ã®ä¸?•ªæœ??ã«å‘¼ã³å‡ºã•れã¾ã™ã? 156 * ROWSET ã®å±žæ?ã§ã‚ã‚‹ã€table 属æ?ã¨ã€dbid 属æ? ã‚’ã?TagElement ã® 157 * get メソãƒ?ƒ‰ã§å–å¾—ã§ãã¾ã™ã? 158 * å–得時ã®ã‚ーã¯ã€ãれãžã‚Œã?"TABLE" 㨠"DBID" ã§ã™ã? 159 * 160 * @param tag タグエレメンãƒ? 161 * @see org.opengion.fukurou.xml.TagElement 162 * @see HybsXMLHandler#setTagElementListener( TagElementListener ) 163 */ 164 public void actionInit( final TagElement tag ) { 165 String table = tag.get( "tableName" ); 166 if( table != null ) { tableName = table; } 167 } 168 169 /** 170 * <ROW> ã‚¿ã‚°ã® endElement 処ç?¯Žã«å‘¼ã³å‡ºã•れã¾ã™ã? 171 * ã“ã? Listener ã‚’ã‚»ãƒ?ƒˆã™ã‚‹ã“ã¨ã«ã‚ˆã‚Šã€è¡Œãƒ‡ãƒ¼ã‚¿ã‚’å–å¾—é?度ã€? 172 * TagElement オブジェクトを作æ?ã—ã?ã“ã?メソãƒ?ƒ‰ãŒå‘¼ã³å‡ºã•れã¾ã™ã? 173 * 174 * @og.rev 4.0.0.0 (2007/05/09) ParameterMetaData を使用ã—ãŸãƒ‘ラメータè¨å®šè¿½åŠ?? 175 * @og.rev 4.0.0.0 (2007/09/25) isOracle ã‹ã‚‰ useParamMetaData ã«å¤‰æ›´ 176 * @og.rev 4.3.7.0 (2009/06/01) HSQLDB対å¿? 177 * @og.rev 5.3.8.0 (2011/08/01) useParamMetaData setNull 対å¿?PostgreSQL対å¿? 178 * @og.rev 5.6.6.1 (2013/07/12) lastSQL 対応ã?ãƒ?ƒãƒ?‚°ç”¨ã«ã€æœ€å¾Œã«ä½¿ç”¨ã—ãŸSQLæ–?‚’残ã—ã¾ã™ã? 179 * 180 * @param tag タグエレメンãƒ? 181 * @see org.opengion.fukurou.xml.TagElement 182 * @see HybsXMLHandler#setTagElementListener( TagElementListener ) 183 */ 184 public void actionRow( final TagElement tag ) { 185 tag.setAfterMap( afterMap ); 186 187 String[] vals = null; // 5.6.6.1 (2013/07/12) ãƒ?ƒãƒ?‚°ç”¨ 188 try { 189 // æ›´æ–°SQL(MERGE_SQLã‚¿ã‚°)ãŒå˜åœ¨ã™ã‚‹å ´åˆã?処ç? 190 int tempCnt = 0; 191 if( updPstmt != null ) { 192 // String[] vals = tag.getValues( updClms ); 193 vals = tag.getValues( updClms ); // 5.6.6.1 (2013/07/12) ãƒ?ƒãƒ?‚°ç”¨ 194 for( int j=0; j<vals.length; j++ ) { 195 // 4.3.7.0 (2009/06/01) HSQLDB対応ã?空æ–?—ã?å ´å?ullã«ç½®æ›ãˆ 196 if( vals[j] != null && vals[j].length() == 0 ){ 197 vals[j] = null; 198 } 199 200 // 4.0.0.0 (2007/09/25) ParameterMetaData を使用ã—ãŸãƒ‘ラメータè¨å®šè¿½åŠ? 201 if( useParamMetaData ) { 202 int type = updMeta.getParameterType( j+1 ); 203 // 5.3.8.0 (2011/08/01) setNull 対å¿? 204 // updPstmt.setObject( j+1,vals[j],type ); 205 String val = vals[j]; 206 if( val == null || val.isEmpty() ) { 207 updPstmt.setNull( j+1, type ); 208 } 209 else { 210 updPstmt.setObject( j+1, val, type ); 211 } 212 } 213 else { 214 updPstmt.setObject( j+1,vals[j] ); 215 } 216 217 } 218 tempCnt = updPstmt.executeUpdate(); 219 if( tempCnt > 1 ) { 220 String errMsg = "Update ã‚ーãŒé‡è¤?—ã¦ã?¾ã™ã?" 221 + "TABLE=[" + tableName + "] ROW=[" 222 + tag.getRowNo() + "]" + CR 223 + " SQL=[" + lastSQL + "]" + CR // 5.6.6.1 (2013/07/12) ãƒ?ƒãƒ?‚°ç”¨ 224 + tag.toString() + CR 225 + Arrays.toString( vals ) + CR ; // 5.6.6.1 (2013/07/12) ãƒ?ƒãƒ?‚°ç”¨ 226 throw new RuntimeException( errMsg ); 227 } 228 updCnt += tempCnt; 229 } 230 // æ›´æ–°ã?0ä»¶ã®å ´åˆã?ã€INSERT処ç?‚’行ã„ã¾ã™ã? 231 if( tempCnt == 0 ) { 232 // åˆå›žINSERT時ã?タグよりã€DB登録SQLæ–?‚’構築ã—ã¾ã™ã? 233 if( insPstmt == null ) { 234 insClms = tag.getKeys(); 235 // String sql = insertSQL( insClms,tableName ); 236 // insPstmt = connection.prepareStatement( sql ); 237 lastSQL = insertSQL( insClms,tableName ); // 5.6.6.1 (2013/07/12) ãƒ?ƒãƒ?‚°ç”¨ 238 insPstmt = connection.prepareStatement( lastSQL ); 239 // 4.0.0.0 (2007/09/25) ParameterMetaData を使用ã—ãŸãƒ‘ラメータè¨å®šè¿½åŠ? 240 if( useParamMetaData ) { insMeta = insPstmt.getParameterMetaData(); } 241 } 242 // String[] vals = tag.getValues( insClms ); 243 vals = tag.getValues( insClms ); // 5.6.6.1 (2013/07/12) ãƒ?ƒãƒ?‚°ç”¨ 244 for( int j=0; j<vals.length; j++ ) { 245 // 4.3.7.0 (2009/06/01) HSQLDB対応ã?空æ–?—ã?å ´å?ullã«ç½®æ›ãˆ 246 if( vals[j] != null && vals[j].length() == 0 ){ 247 vals[j] = null; 248 } 249 250 // 4.0.0.0 (2007/09/25) ParameterMetaData を使用ã—ãŸãƒ‘ラメータè¨å®šè¿½åŠ? 251 if( useParamMetaData ) { 252 int type = insMeta.getParameterType( j+1 ); 253 // 5.3.8.0 (2011/08/01) setNull 対å¿? 254 // insPstmt.setObject( j+1,vals[j],type ); 255 String val = vals[j]; 256 if( val == null || val.isEmpty() ) { 257 insPstmt.setNull( j+1, type ); 258 } 259 else { 260 insPstmt.setObject( j+1, val, type ); 261 } 262 } 263 else { 264 insPstmt.setObject( j+1,vals[j] ); 265 } 266 } 267 insCnt += insPstmt.executeUpdate(); 268 } 269 } 270 catch( SQLException ex ) { 271 String errMsg = "DB登録エラーãŒç™ºç”Ÿã—ã¾ã—ãŸã€? 272 + "TABLE=[" + tableName + "] ROW=[" 273 + tag.getRowNo() + "]" + CR 274 + " SQL=[" + lastSQL + "]" + CR // 5.6.6.1 (2013/07/12) ãƒ?ƒãƒ?‚°ç”¨ 275 + tag.toString() + CR 276 + Arrays.toString( vals ) + CR // 5.6.6.1 (2013/07/12) ãƒ?ƒãƒ?‚°ç”¨ 277 + ex.getMessage() + ":" + ex.getSQLState() + CR ; 278 throw new RuntimeException( errMsg,ex ); 279 } 280 } 281 282 /** 283 * <EXEC_SQL> ã‚¿ã‚°ã® endElement 処ç?¯Žã«å‘¼ã³å‡ºã•れã¾ã™ã? 284 * getBody メソãƒ?ƒ‰ã‚’使用ã—ã¦ã€ã“ã®ã‚¿ã‚°ã®BODYéƒ¨ã®æ–?—å?ã‚’å–å¾—ã—ã¾ã™ã? 285 * ã“ã? Listener ã‚’ã‚»ãƒ?ƒˆã™ã‚‹ã“ã¨ã«ã‚ˆã‚Šã€EXEC_SQL ãƒ??ã‚¿ã‚’å–å¾—é?度ã€? 286 * TagElement オブジェクトを作æ?ã—ã?ã“ã?メソãƒ?ƒ‰ãŒå‘¼ã³å‡ºã•れã¾ã™ã? 287 * EXEC_SQL ã‚¿ã‚°ã§ã¯ã€deleteæ–?‚„updateæ–?ªã©ã€ç‰¹æ®Šãªå‰å?ç?‚„後å?ç?”¨ã® SQL㨠288 * DDL?ˆãƒ‡ãƒ¼ã‚¿å®šç¾©è¨?ªžï¼šData Definition Language?‰ã?処ç?ªã©ã‚‚記述ã§ãã¾ã™ã? 289 * ã“ã“ã§ã¯ç°¡æ˜“çš„ã«ã€ä½•ã‹å®Ÿè¡Œã•れãŸå ´åˆã?ã€delete 処ç?¨è€?ˆã€å‰Šé™¤ã‚«ã‚¦ãƒ³ãƒˆã‚’åŠ?®—ã—ã€? 290 * 0ä»¶ã§å¸°ã£ã¦ããŸå ´åˆã«ã€DDLãŒå®Ÿè¡Œã•れãŸã¨è€?ˆã€DDLカウントを?‹ï¼‘ã—ã¾ã™ã? 291 * ãŸã ã—ã?0ä»¶ delete ã‚‚è?ãˆã‚‰ã‚Œã‚‹ãŸã‚ã€SQLæ–??å…ˆé?æ–?—ã«ã‚ˆã‚‹ãƒã‚§ãƒ?‚¯ã¯å…¥ã‚Œã¦ãŠãã¾ã™ã? 292 * 293 * @og.rev 5.6.6.1 (2013/07/12) lastSQL 対応ã?ãƒ?ƒãƒ?‚°ç”¨ã«ã€æœ€å¾Œã«ä½¿ç”¨ã—ãŸSQLæ–?‚’残ã—ã¾ã™ã? 294 * @og.rev 5.6.7.0 (2013/07/27) DDL?ˆãƒ‡ãƒ¼ã‚¿å®šç¾©è¨?ªžï¼šData Definition Language?‰ã?処ç?»¶æ•°è¿½åŠ? 295 * 296 * @param tag タグエレメンãƒ? 297 * @see org.opengion.fukurou.xml.TagElement 298 * @see HybsXMLHandler#setTagElementListener( TagElementListener ) 299 */ 300 public void actionExecSQL( final TagElement tag ) { 301 Statement execSQL = null ; 302 try { 303 // String sql = tag.getBody(); 304 lastSQL = tag.getBody(); // 5.6.6.1 (2013/07/12) ãƒ?ƒãƒ?‚°ç”¨ 305 execSQL = connection.createStatement(); 306 // delCnt += execSQL.executeUpdate( sql ) ; 307 308 // 5.6.7.0 (2013/07/27) DDL?ˆãƒ‡ãƒ¼ã‚¿å®šç¾©è¨?ªžï¼šData Definition Language?‰ã?処ç?»¶æ•°è¿½åŠ? 309 // delCnt += execSQL.executeUpdate( lastSQL ) ; 310 int cnt = execSQL.executeUpdate( lastSQL ) ; 311 if( cnt > 0 ) { delCnt += cnt; } // ä»¶æ•°ãŒè¿”れã°ã€DDLã§ãªã?Ÿã‚ã?å‰Šé™¤æ•°ã‚’åŠ ç®? 312 else { 313 String sql = lastSQL.trim().toUpperCase( Locale.JAPAN ); 314 if( !sql.startsWith( "DELETE" ) && !sql.startsWith( "INSERT" ) && !sql.startsWith( "UPDATE" ) ) { 315 ddlCnt ++ ; 316 } 317 } 318 } 319 catch( SQLException ex ) { 320 String errMsg = "DB登録エラーãŒç™ºç”Ÿã—ã¾ã—ãŸã€? 321 + "TABLE=[" + tableName + "] ROW=[" 322 + tag.getRowNo() + "]" + CR 323 + " SQL=[" + lastSQL + "]" + CR // 5.6.6.1 (2013/07/12) ãƒ?ƒãƒ?‚°ç”¨ 324 + tag.toString() + CR 325 + ex.getMessage() + ":" + ex.getSQLState() + CR ; 326 throw new RuntimeException( errMsg,ex ); 327 } 328 finally { 329 Closer.stmtClose( execSQL ); 330 } 331 } 332 333 /** 334 * <MERGE_SQL> ã‚¿ã‚°ã® endElement 処ç?™‚ã«å‘¼ã³å‡ºã•れã¾ã™ã? 335 * getBody メソãƒ?ƒ‰ã‚’使用ã—ã¦ã€ã“ã®ã‚¿ã‚°ã®BODYéƒ¨ã®æ–?—å?ã‚’å–å¾—ã—ã¾ã™ã? 336 * MERGE_SQLã‚¿ã‚°ã¯ã€ã?ージ処ç?—ãŸã„ãƒ??タ部よりも上ä½ã«è¨˜è¿°ã—ã¦ãŠã 337 * å¿?¦ãŒã‚りã¾ã™ãŒã€ä¸é–“部ã«è¤?•°å›žè¨˜è¿°ã—ã¦ã‚‚æ§‹ã„ã¾ã›ã‚“ã€? 338 * ã“ã?ã‚¿ã‚°ãŒç¾ã‚Œã‚‹ã¾ã§ã¯ã€INSERT ã®ã¿å®Ÿè¡Œã•れã¾ã™ã?ã“ã?タグ以é™ã?ã€? 339 * ä¸?—¦ UPDATE ã—ã?çµæžœã??ä»¶ã®å ´åˆã?ã€INSERTã™ã‚‹æµã‚Œã«ãªã‚Šã¾ã™ã? 340 * 完å?ã« INSERT ã®ã¿ã§ã‚ã‚‹ãƒ??ã‚¿ã‚’å‰åŠã«ã€UPDATE/INSERTを行ㆠ341 * ãƒ??タを後åŠã«ã€ãã®é–“ã«ã€MERGE_SQL ã‚¿ã‚°ã‚’å?れるã“ã¨ã§ã€ç„¡æ„味㪠342 * UPDATE ã‚’é¿ã‘ã‚‹ã“ã¨ãŒå¯èƒ½ã§ã™ã? 343 * ã“ã? Listener ã‚’ã‚»ãƒ?ƒˆã™ã‚‹ã“ã¨ã«ã‚ˆã‚Šã€MERGE_SQL ãƒ??ã‚¿ã‚’å–å¾—é?度ã€? 344 * TagElement オブジェクトを作æ?ã—ã?ã“ã?メソãƒ?ƒ‰ãŒå‘¼ã³å‡ºã•れã¾ã™ã? 345 * 346 * @og.rev 4.0.0.0 (2007/05/09) ParameterMetaData を使用ã—ãŸãƒ‘ラメータè¨å®šè¿½åŠ?? 347 * @og.rev 4.0.0.0 (2007/09/25) isOracle ã‹ã‚‰ useParamMetaData ã«å¤‰æ›´ 348 * @og.rev 5.6.6.1 (2013/07/12) lastSQL 対応ã?ãƒ?ƒãƒ?‚°ç”¨ã«ã€æœ€å¾Œã«ä½¿ç”¨ã—ãŸSQLæ–?‚’残ã—ã¾ã™ã? 349 * 350 * @param tag タグエレメンãƒ? 351 * @see org.opengion.fukurou.xml.TagElement 352 * @see HybsXMLHandler#setTagElementListener( TagElementListener ) 353 */ 354 public void actionMergeSQL( final TagElement tag ) { 355 if( updPstmt != null ) { 356 String errMsg = "MERGE_SQLã‚¿ã‚°ãŒã?è¤?•°å›žè¨˜è¿°ã•れã¦ã?¾ã™ã?" 357 + "TABLE=[" + tableName + "] ROW=[" 358 + tag.getRowNo() + "]" + CR 359 + " SQL=[" + lastSQL + "]" + CR // 5.6.6.1 (2013/07/12) ãƒ?ƒãƒ?‚°ç”¨ 360 + tag.toString() + CR; 361 throw new RuntimeException( errMsg ); 362 } 363 364 String orgSql = tag.getBody(); 365 Matcher matcher = pattern.matcher( orgSql ); 366 updClms = new ArrayList<String>(); 367 while( matcher.find() ) { 368 // ã“ã“ã§ã¯ã€[XXX]ã«ãƒžãƒƒãƒã™ã‚‹ç‚ºã€å‰å¾Œã?[]ã‚’å–り除ãã¾ã™ã? 369 updClms.add( orgSql.substring( matcher.start()+1,matcher.end()-1 ) ); 370 } 371 // String sql = matcher.replaceAll( "?" ); 372 lastSQL = matcher.replaceAll( "?" ); // 5.6.6.1 (2013/07/12) ãƒ?ƒãƒ?‚°ç”¨ 373 374 try { 375 // updPstmt = connection.prepareStatement( sql ); 376 updPstmt = connection.prepareStatement( lastSQL ); 377 // 4.0.0.0 (2007/09/25) ParameterMetaData を使用ã—ãŸãƒ‘ラメータè¨å®šè¿½åŠ? 378 if( useParamMetaData ) { updMeta = updPstmt.getParameterMetaData(); } 379 } 380 catch( SQLException ex ) { 381 String errMsg = "Statement作æ?時ã«ã‚¨ãƒ©ãƒ¼ãŒç™ºç”Ÿã—ã¾ã—ãŸã€? 382 + "TABLE=[" + tableName + "] ROW=[" 383 + tag.getRowNo() + "]" + CR 384 + " SQL=[" + lastSQL + "]" + CR // 5.6.6.1 (2013/07/12) ãƒ?ƒãƒ?‚°ç”¨ 385 + tag.toString() + CR 386 + ex.getMessage() + ":" + ex.getSQLState() + CR ; 387 throw new RuntimeException( errMsg,ex ); 388 } 389 } 390 391 /** 392 * UPDATE,DELETE を行ã†å ´åˆã? WHERE æ¡ä»¶ã«ãªã‚‹ã‚ーé…å? 393 * ã“ã?ã‚ー㮠AND æ¡ä»¶ã§ã‚«ãƒ©ãƒ?‚’特定ã—ã€UPDATE,DELETE ãªã©ã®å‡¦ç?‚’ 394 * 行ã„ã¾ã™ã? 395 * 396 * @param keyCols WHEREæ¡ä»¶ã«ãªã‚‹ã‚ーé…å? 397 */ 398 public void setKeyColumns( final String[] keyCols ) { 399 keyColumns = new String[keyCols.length]; 400 System.arraycopy( keyCols,0,keyColumns,0,keyColumns.length ); 401 } 402 403 /** 404 * XMLファイルをèªã¿å–ã‚‹å‰ã«æŒ?®šã™ã‚‹ã‚«ãƒ©ãƒ?¨å€¤ã®ãƒšã‚¢(マッãƒ?æƒ??ã‚’ã‚»ãƒ?ƒˆã—ã¾ã™ã? 405 * 406 * ã“ã?カラãƒ?¨å€¤ã®ãƒšã‚¢ã®ãƒžãƒƒãƒ—ã?ã€ã‚ªãƒ–ジェクト構築å‰ã«è¨å®šã•れる為ã€? 407 * XMLファイルã«ã‚ーãŒå˜åœ¨ã—ã¦ã?‚‹å ´åˆã?ã€å?ãŒæ›¸ã変ã‚りã¾ã™ã?(XML優å…? 408 * XMLファイルã«ã‚ーãŒå˜åœ¨ã—ã¦ã?ªã??åˆã?ã€ã“ã“ã§æŒ?®šã™ã‚‹Mapã®å€¤ã? 409 * åˆæœŸè¨å®šå?ã¨ã—ã¦ä½¿ç”¨ã•れã¾ã™ã? 410 * ã“ã“ã§æŒ?®šã™ã‚?Map ã« LinkedHashMap を使用ã™ã‚‹å ´åˆã?カラãƒ??ã‚? 411 * æŒ?®šã™ã‚‹ã“ã¨ãŒå?æ¥ã¾ã™ã? 412 * 413 * @param map åˆæœŸè¨å®šã™ã‚‹ã‚«ãƒ©ãƒ?ƒ‡ãƒ¼ã‚¿ãƒžãƒƒãƒ? 414 * @see #setAfterMap( Map ) 415 */ 416 public void setDefaultMap( final Map<String,String> map ) { defaultMap = map; } 417 418 /** 419 * XMLファイルをèªã¿å–ã£ãŸå¾Œã§æŒ?®šã™ã‚‹ã‚«ãƒ©ãƒ?¨å€¤ã®ãƒšã‚¢(マッãƒ?æƒ??ã‚’ã‚»ãƒ?ƒˆã—ã¾ã™ã? 420 * 421 * ã“ã?カラãƒ?¨å€¤ã®ãƒšã‚¢ã®ãƒžãƒƒãƒ—ã?ã€ã‚ªãƒ–ジェクト構築後ã«è¨å®šã•れる為ã€? 422 * XMLファイルã®ã‚ーã®å˜åœ¨ã«é–¢ä¿‚ãªãã?Mapã®ã‚ーã¨å€¤ãŒä½¿ç”¨ã•れã¾ã™ã?(Map優å…? 423 * null ã‚’è¨å®šã—ãŸå?åˆã?ã€ãªã«ã‚‚å?ç?•れã¾ã›ã‚“ã€? 424 * 425 * @param map 後è¨å®šã™ã‚‹ã‚«ãƒ©ãƒ?ƒ‡ãƒ¼ã‚¿ãƒžãƒƒãƒ? 426 * @see #setDefaultMap( Map ) 427 */ 428 public void setAfterMap( final Map<String,String> map ) { afterMap = map; } 429 430 /** 431 * ãƒ??タベã?スã«è¿½åŠ??ç?INSERT)を行ã„ã¾ã™ã? 432 * 433 * å…ˆã«æŒ?®šã•れãŸã‚³ãƒã‚¯ã‚·ãƒ§ãƒ³ã‚’用ã?¦ã€æŒ‡å®šã?ãƒ??ブル㫠INSERT ã—ã¾ã™ã? 434 * 引数ã«ã¯ã€XMLファイルを指定ã—ãŸãƒªãƒ¼ãƒ??ã‚’ã‚»ãƒ?ƒˆã—ã¾ã™ã? 435 * コãƒã‚¯ã‚·ãƒ§ãƒ³ã¯ã€çµ‚äº?¾Œã?コミットã•れã¾ã™ã?(close ã•れã¾ã›ã‚“ã€? 436 * リーãƒ??ã®ã‚¯ãƒãƒ¼ã‚ºã¯ã€ã“ã“ã§ã¯è¡Œã£ã¦ã?¾ã›ã‚“ã€? 437 * 438 * @og.rev 5.1.1.0 (2009/11/11) insMeta , updMeta ã®ã‚¯ãƒªã‚¢(気休ã‚) 439 * 440 * @param reader XMLファイルを指定ã™ã‚‹ãƒªãƒ¼ãƒ?? 441 */ 442 public void insertXML( final Reader reader ) { 443 try { 444 HybsXMLHandler handler = new HybsXMLHandler(); 445 handler.setTagElementListener( this ); 446 handler.setDefaultMap( defaultMap ); 447 448 handler.parse( reader ); 449 } 450 finally { 451 Closer.stmtClose( insPstmt ); 452 Closer.stmtClose( updPstmt ); 453 insPstmt = null; 454 updPstmt = null; 455 insMeta = null; // 5.1.1.0 (2009/11/11) 456 updMeta = null; // 5.1.1.0 (2009/11/11) 457 } 458 } 459 460 /** 461 * インサート用ã®SQLæ–?‚’作æ?ã—ã¾ã™ã? 462 * 463 * @param columns インサートã™ã‚‹ã‚«ãƒ©ãƒ? 464 * @param tableName インサートã™ã‚‹ãƒ†ãƒ¼ãƒ–ルå? 465 * 466 * @return インサート用ã®SQLæ–? 467 */ 468 private String insertSQL( final String[] columns,final String tableName ) { 469 if( tableName == null ) { 470 String errMsg = "tableName ãŒã‚»ãƒ?ƒˆã•れã¦ã?¾ã›ã‚“ã€? + CR 471 + "tableName ã¯ã€ã‚³ãƒ³ã‚¹ãƒˆãƒ©ã‚¯ã‚¿ã§æŒ?®šã™ã‚‹ã‹ã€ROWSETã®tableName属æ?ã§" 472 + "æŒ?®šã—ã¦ãŠãå¿?¦ãŒã‚りã¾ã? + CR ; 473 throw new RuntimeException( errMsg ); 474 } 475 476 StringBuilder sql = new StringBuilder(); 477 sql.append( "INSERT INTO " ).append( tableName ); 478 sql.append( " ( " ); 479 sql.append( columns[0] ); 480 for( int i=1; i<columns.length; i++ ) { 481 sql.append( "," ).append( columns[i] ); 482 } 483 sql.append( " ) VALUES ( " ); 484 sql.append( "?" ); 485 for( int i=1; i<columns.length; i++ ) { 486 sql.append( "," ).append( "?" ); 487 } 488 sql.append( " )" ); 489 490 return sql.toString(); 491 } 492 493 /** 494 * ãƒ??タベã?スã«è¿½åŠ?—ãŸä»¶æ•°ã‚’è¿”ã—ã¾ã™ã? 495 * 496 * @return 登録件数 497 */ 498 public int getInsertCount() { return insCnt; } 499 500 /** 501 * ãƒ??タベã?スを更新ã—ãŸä»¶æ•°ã‚’è¿”ã—ã¾ã™ã? 502 * ã“れã¯ã€æ‹¡å¼µXDKå½¢å¼ã§ã€MERGE_SQL タグを使用ã—ãŸå ´åˆã?更新処ç?»¶æ•°ã‚? 503 * åˆè¨ˆã—ãŸå?ã‚’è¿”ã—ã¾ã™ã? 504 * 505 * @return æ›´æ–°ä»¶æ•° 506 */ 507 public int getUpdateCount() { return updCnt; } 508 509 /** 510 * ãƒ??タベã?スã«å¤‰æ›´(æ›´æ–°ã€å‰Šé™¤ã‚’å«ã‚?ã—ãŸä»¶æ•°ã‚’è¿”ã—ã¾ã™ã? 511 * ã“れã¯ã€æ‹¡å¼µXDKå½¢å¼ã§ã€EXEC_SQL タグを使用ã—ãŸå ´åˆã?実行件数をåˆè¨ˆã—ã? 512 * 値を返ã—ã¾ã™ã? 513 * よã£ã¦ã€æ›´æ–°ã‹ã?追åŠ?‹ã€å‰Šé™¤ã‹ã?ã€åˆ¤ã‚Šã¾ã›ã‚“ãŒã?通常 登録å‰ã«å‰Šé™¤ã™ã‚‹ 514 * ケースã§ä½¿ã‚れるã“ã¨ã‹ã‚‰ã€deleteCount ã¨ã—ã¦ã?¾ã™ã? 515 * 516 * @return 変更件数(主ã«ã€å‰Šé™¤ä»¶æ•°) 517 */ 518 public int getDeleteCount() { return delCnt; } 519 520 /** 521 * ãƒ??タベã?スã«DDL?ˆãƒ‡ãƒ¼ã‚¿å®šç¾©è¨?ªžï¼šData Definition Language?‰å?ç?—ãŸä»¶æ•°ã‚’è¿”ã—ã¾ã™ã? 522 * ã“れã¯ã€æ‹¡å¼µXDKå½¢å¼ã§ã€EXEC_SQL タグを使用ã—ãŸå ´åˆã?実行件数をåˆè¨ˆã—ã? 523 * 値を返ã—ã¾ã™ã? 524 * EXEC_SQL ã§ã¯ã€ç™»éŒ²å‰ã«å‰Šé™¤ã™ã‚‹ delete 処ç?‚‚ã€EXEC_SQL タグを使用ã—ã¦å®Ÿè¡Œã—ã¾ã™ãŒ 525 * ãã?処ç?¨åˆ?‘ã¦ã‚«ã‚¦ãƒ³ãƒˆã—ã¾ã™ã? 526 * 527 * @og.rev 5.6.7.0 (2013/07/27) DDL?ˆãƒ‡ãƒ¼ã‚¿å®šç¾©è¨?ªžï¼šData Definition Language?‰ã?処ç?»¶æ•°è¿½åŠ? 528 * 529 * @return DDL?ˆãƒ‡ãƒ¼ã‚¿å®šç¾©è¨?ªžï¼šData Definition Language?‰å?ç?—ãŸä»¶æ•° 530 */ 531 public int getDDLCount() { return ddlCnt; } 532 533 /** 534 * 実際ã«ç™»éŒ²ã•れã?ãƒ??ブルåã‚’è¿”ã—ã¾ã™ã? 535 * 536 * ãƒ??ブルåã?ã€æ‹¡å¼µXDKå½¢å¼ã?ROWSETã‚¿ã‚°ã®tableName属æ?ã« 537 * 記述ã—ã¦ãŠãã‹ã?コンストラクターã§å¼•æ•°ã¨ã—ã¦æ¸¡ã—ã¾ã™ã? 538 * 両方æŒ?®šã•れãŸå ´åˆã?ã€ROWSETã‚¿ã‚°ã®tableName属æ?ãŒå„ªå…ˆã•れã¾ã™ã? 539 * ã“ã“ã§ã®è¿”り値ã¯ã€å®Ÿéš›ã«ä½¿ç”¨ã•れã?ãƒ??ブルåã§ã™ã? 540 * 541 * @return 変更件数(主ã«ã€å‰Šé™¤ä»¶æ•°) 542 */ 543 public String getTableName() { return tableName; } 544 545 /** 546 * ã“ã?接続ãŒã€PreparedStatement#getParameterMetaData() を使用ã™ã‚‹ã‹ã©ã?‹ã‚’判定ã—ã¾ã™ã? 547 * 本æ¥ã¯ã€ConnectionFactory#useParameterMetaData(String)を使ã?¹ãã ãŒã?dbid ãŒç„¡ã?Ÿã‚ã?直接å–å¾—ã—ã¾ã™ã? 548 * 549 * @og.rev 5.3.8.0 (2011/08/01) æ–°è¦ä½œæ? ( ApplicationInfo#useParameterMetaData(Connection) ã‹ã‚‰ã‚³ãƒ”ã? ) 550 * @og.rev 5.6.7.0 (2013/07/27) ProductName ã¯ã€DBUtil 経由ã§å–å¾—ã™ã‚‹ã? 551 * 552 * @param conn 接続å?(コãƒã‚¯ã‚·ãƒ§ãƒ³) 553 * 554 * @return 使用ã™ã‚‹å ´åˆï¼štrue / ãã?ä»?false 555 */ 556 private static boolean useParameterMetaData( final Connection conn ) { 557 558 String dbProductName = DBUtil.getProductName( conn ); 559 560 return "PostgreSQL".equalsIgnoreCase( dbProductName ) ; 561 562 // try { 563 // DatabaseMetaData meta = conn.getMetaData(); 564 // String dbProductName = meta.getDatabaseProductName(); 565 // 566 // if( "PostgreSQL".equalsIgnoreCase( dbProductName ) ) { return true; } 567 // 568 // return false ; 569 // } 570 // catch( SQLException ex ) { 571 // String errMsg = "DatabaseMetaData ã‹ã‚‰ã€getDatabaseProductName ã‚’å–å¾—ã§ãã¾ã›ã‚“ã§ã—ãŸã€? 572 // + ex.getMessage() + ":" + ex.getSQLState() ; 573 // throw new RuntimeException( errMsg,ex ); 574 // } 575 } 576 577 /** 578 * ãƒ?‚¹ãƒˆç”¨ã®ãƒ¡ã‚¤ãƒ³ãƒ¡ã‚½ãƒ?ƒ‰ 579 * 580 * java org.opengion.fukurou.xml.HybsXMLSave USER PASSWD URL TABLE FILE [ENCODE] [DRIVER] 581 * USER : DB接続ユーザー(GE) 582 * PASSWD: DB接続パスワーãƒ?GE) 583 * URL : DB接続JDBCドライãƒURL(jdbc:oracle:thin:@localhost:1521:HYBS 584 * TABLE : 登録ã™ã‚‹ãƒ??ブルID(GE21) 585 * FILE : 登録ã™ã‚‹ORACLE XDK å½¢å¼?XMLファイル(GE21.xml) 586 * ENCODE: ファイルã®ã‚¨ãƒ³ã‚³ãƒ¼ãƒ?åˆæœŸå€¤:UTF-8 587 * DRIVER: JDBCドライãƒã? åˆæœŸå€¤:oracle.jdbc.OracleDriver 588 * 589 * @og.rev 5.1.1.0 (2009/12/01) MySQL対å¿?明示çš?«ã€TRANSACTION_READ_COMMITTED を指定ã™ã‚‹ã? 590 * @og.rev 5.6.7.0 (2013/07/27) DDL?ˆãƒ‡ãƒ¼ã‚¿å®šç¾©è¨?ªžï¼šData Definition Language?‰ã?処ç?»¶æ•°è¿½åŠ? 591 * 592 * @param args コマンド引数é…å? 593 * @throws Exception 594 */ 595 public static void main( final String[] args ) 596 throws ClassNotFoundException , SQLException { 597 if( args.length < 5 ) { 598 LogWriter.log( "Usage: java org.opengion.fukurou.xml.HybsXMLSave USER PASSWD URL TABLE FILE [ENCODE] [DRIVER]" ); 599 LogWriter.log( " USER : DB接続ユーザー(GE)" ); 600 LogWriter.log( " PASSWD: DB接続パスワーãƒ?GE)" ); 601 LogWriter.log( " URL : DB接続JDBCドライãƒURL(jdbc:oracle:thin:@localhost:1521:HYBS)" ); 602 LogWriter.log( " TABLE : 登録ã™ã‚‹ãƒ??ブルID(GE21)" ); 603 LogWriter.log( " FILE : 登録ã™ã‚‹ORACLE XDK å½¢å¼?XMLファイル(GE21.xml)" ); 604 LogWriter.log( " [ ENCODE: ファイルã®ã‚¨ãƒ³ã‚³ãƒ¼ãƒ?åˆæœŸå€¤:UTF-8 ]" ); 605 LogWriter.log( " [ DRIVER: JDBCドライãƒã? åˆæœŸå€¤:oracle.jdbc.OracleDriver ]" ); 606 return ; 607 } 608 609 String user = args[0] ; 610 String passwd = args[1] ; 611 String url = args[2] ; 612 String table = args[3] ; 613 String file = args[4] ; 614 String encode = ( args.length == 6 ) ? args[5] : "UTF-8" ; 615 String driver = ( args.length == 7 ) ? args[6] : "oracle.jdbc.OracleDriver" ; 616 617 Class.forName(driver); 618 619 Connection conn = DriverManager.getConnection( url,user,passwd ); 620 Reader reader = null; 621 int insCnt; 622 int updCnt; 623 int delCnt; 624 int ddlCnt; // 5.6.7.0 (2013/07/27) DDL処ç?»¶æ•°è¿½åŠ? 625 try { 626 conn.setAutoCommit( false ); 627 conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); // 5.1.1.0 (2009/12/01) 628 HybsXMLSave save = new HybsXMLSave( conn,table ); 629 630 reader = new BufferedReader(new InputStreamReader( 631 new FileInputStream( file ) ,encode )); 632 save.insertXML( reader ); 633 insCnt = save.getInsertCount(); 634 updCnt = save.getUpdateCount(); 635 delCnt = save.getDeleteCount(); 636 ddlCnt = save.getDDLCount(); // 5.6.7.0 (2013/07/27) DDL処ç?»¶æ•°è¿½åŠ? 637 638 Closer.commit( conn ); 639 } 640 // FileNotFoundException , UnsupportedEncodingException 641 catch( java.io.FileNotFoundException ex ) { 642 String errMsg = "ファイルãŒå˜åœ¨ã—ã¾ã›ã‚“ã€? + ex.getMessage() 643 + CR + "Table=[" + table + "] File =[" + file + "]" ; 644 throw new RuntimeException( errMsg,ex ); 645 } 646 catch( java.io.UnsupportedEncodingException ex ) { 647 String errMsg = "æŒ?®šã?エンコードãŒå˜åœ¨ã—ã¾ã›ã‚“ã€? + ex.getMessage() 648 + CR + "Table=[" + table + "] Encode =[" + encode + "]" ; 649 throw new RuntimeException( errMsg,ex ); 650 } 651 finally { 652 Closer.ioClose( reader ); 653 Closer.connClose( conn ); 654 } 655 656 System.out.println( "XML File[" + file + "] Into [" + table + "] Table" ); 657 System.out.println( " Insert Count : [" + insCnt + "]" ); 658 System.out.println( " Update Count : [" + updCnt + "]" ); 659 System.out.println( " Delete Count : [" + delCnt + "]" ); 660 System.out.println( " DDL Count : [" + ddlCnt + "]" ); 661 } 662 }