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 */ 016package org.opengion.fukurou.fileexec; 017 018import java.sql.Connection; 019import java.sql.ResultSet; 020import java.sql.PreparedStatement; 021import java.sql.ParameterMetaData; 022import java.sql.SQLException; 023 024import java.util.Map; 025import java.util.List; 026import java.util.ArrayList; 027import java.util.Arrays; 028 029import org.apache.tomcat.jdbc.pool.DataSource; 030import org.apache.tomcat.jdbc.pool.PoolProperties; 031 032/** 033 * データベース処理を行う、簡易的なユーティリティークラスです。 034 * staticメソッドしか持っていません。 035 * sql文を execute( query ) する事により,データベースに書き込みます。 036 * 037 * このクラスは、マルチスレッドに対して、安全です。 038 * 039 * @version 4.0 040 * @author Kazuhiko Hasegawa 041 * @since JDK5.0, 042 */ 043public final class DBUtil { 044 private static final XLogger LOGGER= XLogger.getLogger( DBUtil.class.getName() ); // ログ出力 045 046 /** データベースのキーワード {@value} */ 047 public static final String DATABASE_KEY = "DATABASE"; 048 049 /** 接続先URL {@value} */ 050 public static final String URL_KEY = "REALM_URL"; 051 /** ドライバー {@value} */ 052 public static final String DRIVER_KEY = "REALM_DRIVER"; 053 /** ユーザーID {@value} */ 054 public static final String NAME_KEY = "REALM_NAME"; 055 /** パスワード {@value} */ 056 public static final String PASSWORD_KEY = "REALM_PASSWORD"; 057 058 /** データベースリトライの待ち時間(ミリ秒) {@value} */ 059 public static final int CONN_SLEEP_TIME = 2000 ; // 6.8.2.2 (2017/11/02) コネクションの獲得まで、2秒待つ 060 /** データベースリトライ回数 {@value} */ 061 public static final int CONN_RETRY_COUNT = 10 ; // 6.8.2.2 (2017/11/02) コネクションの獲得まで、10回リトライする。 062 /** データベースValid タイムアウト時間(秒) {@value} */ 063 public static final int CONN_VALID_TIMEOUT = 10 ; // 6.8.2.2 (2017/11/02) コネクションのValidチェックのタイムアウト時間。 064 065 private static final DataSource DATA_SOURCE = new DataSource(); 066 067 private static boolean readyFlag ; // 準備が出来た場合は、true 068 069 private static boolean oracleFlag ; // 接続先がORACLEの場合は、true 070 071 private static final int BUFFER_MIDDLE = 200 ; 072 073 /** 074 * インスタンスを作成させないため、private 化します。 075 */ 076 private DBUtil() {} 077 /** 078 * 引数を指定せず、オブジェクトを作成します。 079 * 080 * System.getProperty より取得し、さらに、そこから取得できなかった 081 * 場合は、環境変数から、取得します。 082 * 083 * @see #URL_KEY 084 */ 085 public static void init() { 086 init( System.getProperty( URL_KEY , System.getenv( URL_KEY ) ) , 087 System.getProperty( DRIVER_KEY , System.getenv( DRIVER_KEY ) ) , 088 System.getProperty( NAME_KEY , System.getenv( NAME_KEY ) ) , 089 System.getProperty( PASSWORD_KEY , System.getenv( PASSWORD_KEY ) ) 090 ); 091 } 092 093 /** 094 * 接続先URL、ドライバー、ユーザーID、パスワードなどを含んだMapを指定して、オブジェクトを作成します。 095 * 096 * Mapに指定のキーが含まれない場合は、System.getProperty より取得し、さらに、そこから取得できなかった 097 * 場合は、環境変数から、取得します。 098 * 099 * @param prmMap 必要情報を含んだMapオブジェクト 100 * @see #URL_KEY 101 */ 102 public static void init( final Map<String,String> prmMap ) { 103 init( prmMap.getOrDefault( URL_KEY , System.getProperty( URL_KEY , System.getenv( URL_KEY ) ) ) , 104 prmMap.getOrDefault( DRIVER_KEY , System.getProperty( DRIVER_KEY , System.getenv( DRIVER_KEY ) ) ) , 105 prmMap.getOrDefault( NAME_KEY , System.getProperty( NAME_KEY , System.getenv( NAME_KEY ) ) ) , 106 prmMap.getOrDefault( PASSWORD_KEY , System.getProperty( PASSWORD_KEY , System.getenv( PASSWORD_KEY ) ) ) 107 ); 108 } 109 110 /** 111 * 接続先URL、ドライバー、ユーザーID、パスワードを指定して、オブジェクトを作成します。 112 * 113 * params は、必ず、4つ必要です。 114 * 115 * @param params 接続先URL、ドライバー、ユーザーID、パスワード 116 * @see #isReady() 117 */ 118 public static void init( final String... params ) { 119 if( readyFlag ) { 120 // MSG0024 = すでに、接続先設定は完了しています。[{0}] 121 throw MsgUtil.throwException( "MSG0024" , DATA_SOURCE ); 122 } 123 124 if( params == null || params.length != 4 ) { 125 // MSG0027 = 接続先設定情報が不足しています。[{0}] 126 throw MsgUtil.throwException( "MSG0027" , Arrays.toString( params ) ); 127 } 128 129 final PoolProperties pp = new PoolProperties(); 130 pp.setUrl( params[0] ); 131 pp.setDriverClassName( params[1] ); 132 pp.setUsername( params[2] ); 133 pp.setPassword( params[3] ); 134 135 DATA_SOURCE.setPoolProperties( pp ); 136 readyFlag = true; 137 138 oracleFlag = params[0] != null && params[0].startsWith( "jdbc:oracle" ); 139 } 140 141 /** 142 * DataSourceの初期化が完了していれば、true を返します。 143 * 144 * 初期化は、#init(String...) メソッドの呼び出して、完了します。 145 * #crear() で、未完了に戻ります。 146 * 147 * @return 初期化が完了しているかどうか 148 * @see #init(String...) 149 */ 150 public static boolean isReady() { return readyFlag; } 151 152 /** 153 * 接続先がORACLEかどうかを返します。 154 * 155 * ORACLE の場合は、true を返します。 156 * 157 * @return 接続先がORACLEかどうか[true:ORACLE false:その他] 158 */ 159 public static boolean isOracle() { return oracleFlag; } 160 161 /** 162 * DataSource から、Connectionを取得して、返します。 163 * 164 * @og.rev 6.8.2.2 (2017/11/02) コネクションの再取得をリトライします。 165 * 166 * @return DataSourceから、Connectionを取得して、返します。 167 * @throws SQLException SQLエラーが発生した場合 168 */ 169 public static Connection getConnection() throws SQLException { 170 if( !readyFlag ) { 171 // // MSG0025 = 接続先設定が完了していません。 172 // throw MsgUtil.throwException( "MSG0025" , "getConnection() Error!!" ); 173 init(); 174 } 175 176 SQLException errEX = null; 177 for( int i=0; i<CONN_RETRY_COUNT; i++ ) { 178 try { 179 final Connection conn = DATA_SOURCE.getConnection(); 180 conn.setAutoCommit( false ); 181 182 if( conn.isValid( CONN_VALID_TIMEOUT ) ) { return conn; } 183 } 184 catch( SQLException ex ) { 185 // MSG0019 = DB処理の実行に失敗しました。メッセージ=[{0}]。\n\tquery=[{1}]\n\tvalues={2} 186 MsgUtil.errPrintln( "MSG0019" , ex.getMessage() ); 187 188 errEX = ex ; 189 try{ Thread.sleep( CONN_SLEEP_TIME ); } catch( final InterruptedException ex2 ){} 190 } 191 } 192 193 final String errMsg = errEX == null ? "COUNT Over" : errEX.getMessage() ; 194 // MSG0019 = DB処理の実行に失敗しました。メッセージ=[{0}]。\n\tquery=[{1}]\n\tvalues={2} 195 throw MsgUtil.throwException( errEX , "MSG0019" , errMsg , "getConnection" , "" ); 196 } 197 198 /** 199 * データ配列を渡して実際のDB処理を実行します。 200 * 201 * ここでは、1行だけ処理するための簡易メソッドを提供します。 202 * 203 * @param query 実行するSQL文 204 * @param values ?に割り当てる設定値 205 * @return ここでの処理件数 206 * 207 * @throws RuntimeException Connection DB処理の実行に失敗した場合 208 */ 209 public static int execute( final String query , final String... values ) { 210 final List<String[]> list = new ArrayList<>(); 211 list.add( values ); 212 213 return execute( query,list ); 214 } 215 216 /** 217 * データ配列のListを渡して実際のDB処理を実行します。 218 * 219 * データ配列は、1行分のデータに対する設定値の配列です。 220 * これは、keys で指定した並び順と一致している必要があります。 221 * 222 * @og.rev 6.8.1.5 (2017/09/08) LOGGER.debug 情報の追加 223 * 224 * @param query 実行するSQL文 225 * @param list ?に割り当てる設定値 226 * @return ここでの処理件数 227 * 228 * @throws RuntimeException Connection DB処理の実行に失敗した場合 229 */ 230 public static int execute( final String query , final List<String[]> list ) { 231 LOGGER.debug( () -> "execute query=" + query ); 232 233 String[] debugLine = null; 234 int execCnt = 0; 235 236 // try-with-resources 文 (AutoCloseable) 237 try( final Connection conn = getConnection(); 238 final PreparedStatement pstmt = conn.prepareStatement( query ) ) { // 更新系なので、setFetchSize は不要。 239 240 // ORACLE では、ParameterMetaDataは、使わない。 241 final ParameterMetaData pMeta = oracleFlag ? null : pstmt.getParameterMetaData(); 242 243 for( final String[] values : list ) { 244 debugLine = values; 245 LOGGER.debug( () -> "execute values=" + Arrays.toString( values ) ); 246 setObject( pstmt , values , pMeta ); 247 execCnt += pstmt.executeUpdate(); 248 } 249 250 conn.commit(); 251 } 252 catch( final SQLException ex ) { 253 // MSG0019 = DB処理の実行に失敗しました。メッセージ=[{0}]。\n\tquery=[{1}]\n\tvalues={2} 254 throw MsgUtil.throwException( ex , "MSG0019" , ex.getMessage() , query , Arrays.toString( debugLine ) ); 255 } 256 257 return execCnt; 258 } 259 260 /** 261 * データ配列を渡してPreparedStatementの引数に、値をセットします。 262 * 263 * オラクル系の場合は、そのまま、setObject を行えば、自動変換しますが、 264 * それ以外のDBでは、java.sql.Types を渡す必要があります。さらに、null 値も、setNullを使用します。 265 * 今は、pMeta が、null かどうかで、オラクル系か、どうかを判定するようにしています。 266 * 267 * @param pstmt PreparedStatementオブジェクト 268 * @param values ?に割り当てる設定値 269 * @param pMeta オラクル系以外のDBに対して、type指定する場合に使用する ParameterMetaDataオブジェクト 270 * 271 * @throws SQLException DB処理の実行に失敗した場合 272 */ 273 private static void setObject( final PreparedStatement pstmt , final String[] values , final ParameterMetaData pMeta ) throws SQLException { 274 if( values != null && values.length > 0 ) { 275 // ORACLE では、ParameterMetaDataは、使わない。 276 if( pMeta == null ) { 277 int clmNo = 1; // JDBC のカラム番号は、1から始まる。 278 for( int i=0; i<values.length; i++ ) { 279 final String val = values[i]; 280 pstmt.setObject( clmNo++,val ); 281 } 282 } 283 else { 284 int clmNo = 1; // JDBC のカラム番号は、1から始まる。 285 for( int i=0; i<values.length; i++ ) { 286 final int type = pMeta.getParameterType( clmNo ); 287 final String val = values[i]; 288 if( val == null || val.isEmpty() ) { 289 pstmt.setNull( clmNo++, type ); 290 } 291 else { 292 pstmt.setObject( clmNo++,val,type ); 293 } 294 } 295 } 296 } 297 } 298 299 /** 300 * データ配列のListを渡して実際のDB処理を実行します。(暫定メソッド) 301 * 302 * これは、updQueryで、更新してみて、0件の場合、insQuery で追加処理を行います。 303 * 304 * データ配列は、1行分のデータに対する設定値の配列です。 305 * これは、keys で指定した並び順と一致している必要があります。 306 * 307 * @og.rev 6.8.1.5 (2017/09/08) LOGGER.debug 情報の追加 308 * 309 * @param insQuery 追加するSQL文 310 * @param updQuery 更新するSQL文 311 * @param insList ?に割り当てる設定値 312 * @param updList ?に割り当てる設定値 313 * @return ここでの処理件数 314 * 315 * @throws RuntimeException Connection DB処理の実行に失敗した場合 316 */ 317 public static int execute( final String insQuery , final String updQuery , final List<String[]> insList , final List<String[]> updList ) { 318 LOGGER.debug( () -> "execute insQuery=" + insQuery + " , updQuery=" + updQuery ); 319 320 String[] debugLine = null; 321 String query = null; 322 323 int execCnt = 0; 324 325 // try-with-resources 文 (AutoCloseable) 326 try( final Connection conn = getConnection(); 327 final PreparedStatement inPstmt = conn.prepareStatement( insQuery ); 328 final PreparedStatement upPstmt = conn.prepareStatement( updQuery ) ) { 329 330 // ORACLE では、ParameterMetaDataは、使わない。 331 final ParameterMetaData inpMeta = oracleFlag ? null : inPstmt.getParameterMetaData(); 332 final ParameterMetaData uppMeta = oracleFlag ? null : upPstmt.getParameterMetaData(); 333 334 for( int i=0; i<updList.size(); i++ ) { // 更新処理と、挿入処理は、同じ数のListを用意する。 335 query = updQuery; 336 // 更新処理を行う。 337 final String[] upVals = updList.get(i); 338 debugLine = upVals; 339 setObject( upPstmt , upVals , uppMeta ); 340 341 int cnt = upPstmt.executeUpdate(); 342 343 if( cnt <= 0 ) { // 更新が無い、つまり、追加対象 344 query = insQuery; 345 // 挿入処理を行う。 346 final String[] inVals = insList.get(i); 347 debugLine = inVals; 348 setObject( inPstmt , inVals , inpMeta ); 349 350 LOGGER.debug( () -> "execute INSERT=" + Arrays.toString( inVals ) ); 351 352 cnt = inPstmt.executeUpdate(); 353 } 354 else { // 元々、このelse は必要ない。UPDATE は、先に処理済 355 LOGGER.debug( () -> "execute UPDATE=" + Arrays.toString( upVals ) ); 356 } 357 358 execCnt += cnt; 359 } 360 conn.commit(); 361 } 362 catch( final SQLException ex ) { 363 // try-with-resources文では、すでに、close() 処理済で、例外に渡される。 364 // JDBCの仕様上、close時に内部でrollbackされる約束になっているので、書かなくても大丈夫。 365 366 // MSG0019 = DB処理の実行に失敗しました。メッセージ=[{0}]。\n\tquery=[{1}]\n\tvalues={2} 367 throw MsgUtil.throwException( ex , "MSG0019" , ex.getMessage() , query , Arrays.toString( debugLine ) ); 368 } 369 370 return execCnt; 371 } 372 373 /** 374 * 検索するデータベースを指定して、Queryを実行します(Transaction 対応)。 375 * 376 * ステートメントと引数により、Prepared クエリーの検索のみ実行します。 377 * 結果は,すべて文字列に変換されて格納されます。 378 * 379 * @param query ステートメント文字列 380 * @param args オブジェクトの引数配列 381 * 382 * @return 検索結果のリスト配列(結果が無ければ、サイズゼロのリスト) 383 * @throws RuntimeException DB検索処理の実行に失敗した場合 384 * @og.rtnNotNull 385 */ 386 public static List<String[]> dbQuery( final String query , final String... args ) { 387 // try-with-resources 文 (AutoCloseable) 388 try( final Connection conn = getConnection(); 389 final PreparedStatement pstmt = conn.prepareStatement( query ) ) { 390 391 // ORACLE では、ParameterMetaDataは、使わない。 392 final ParameterMetaData pMeta = oracleFlag ? null : pstmt.getParameterMetaData(); 393 // 6.4.3.2 (2016/02/19) args が null でなく、length==0 でない場合のみ、処理する。 394 setObject( pstmt , args , pMeta ); 395 396 if( pstmt.execute() ) { 397 try( final ResultSet resultSet = pstmt.getResultSet() ) { 398 return resultToArray( resultSet ); 399 } 400 } 401 conn.commit(); 402 } 403 catch ( final SQLException ex ) { 404 // try-with-resources文では、すでに、close() 処理済で、例外に渡される。 405 // JDBCの仕様上、close時に内部でrollbackされる約束になっているので、書かなくても大丈夫。 406 407 // MSG0019 = DB処理の実行に失敗しました。メッセージ=[{0}]。\n\tquery=[{1}]\n\tvalues={2} 408 throw MsgUtil.throwException( ex , "MSG0019" , ex.getMessage() , query , Arrays.toString( args ) ); 409 } 410 411 return new ArrayList<String[]>(); 412 } 413 414 /** 415 * ResultSet より、結果の文字列配列を作成します。 416 * 417 * 結果は,すべて文字列に変換されて格納されます。 418 * 移動したメソッドで使われているのでこれも移動 419 * 420 * @param resultSet ResultSetオブジェクト 421 * 422 * @return ResultSetの検索結果リスト配列 423 * @throws java.sql.SQLException データベース・アクセス・エラーが発生した場合 424 * @og.rtnNotNull 425 */ 426 public static List<String[]> resultToArray( final ResultSet resultSet ) throws SQLException { 427 final ArrayList<String[]> data = new ArrayList<>(); 428 429 final ResultSetValue rsv = new ResultSetValue( resultSet ); 430 431 while( rsv.next() ) { 432 data.add( rsv.getValues() ); 433 } 434 435 return data; 436 } 437 438 /** 439 * データをインサートする場合に使用するSQL文を作成します。 440 * 441 * これは、key に対応した ? 文字列で、SQL文を作成します。 442 * 実際の値設定は、この、キーの並び順に応じた値を設定することになります。 443 * conKeysとconValsは、固定値のキーと値です。 444 * conKeys,conVals がnullの場合は、これらの値を使用しません。 445 * 446 * @param table テーブルID 447 * @param keys 設定値に対応するキー配列 448 * @param conKeys 固定値の設定値に対応するキー配列 449 * @param conVals 固定値に対応する値配列 450 * @return インサートSQL 451 * @og.rtnNotNull 452 */ 453 public static String getInsertSQL( final String table , final String[] keys , final String[] conKeys , final String[] conVals ) { 454 final String[] vals = new String[keys.length]; 455 Arrays.fill( vals , "?" ); 456 457 final boolean useConst = conKeys != null && conVals != null && conKeys.length == conVals.length && conKeys.length > 0 ; 458 459 final StringBuilder sql = new StringBuilder( BUFFER_MIDDLE ) 460 .append( "INSERT INTO " ).append( table ) 461 .append( " ( " ) 462 .append( String.join( "," , keys ) ); 463 464 if( useConst ) { 465 sql.append( ',' ).append( String.join( "," , conKeys ) ); 466 } 467 468 sql.append( " ) VALUES ( " ) 469 .append( String.join( "," , vals ) ); 470 471 if( useConst ) { 472 sql.append( ",'" ).append( String.join( "','" , conVals ) ).append( '\'' ); 473 } 474 475 return sql.append( " )" ).toString(); 476 } 477 478 /** 479 * データをアップデートする場合に使用するSQL文を作成します。 480 * 481 * これは、key に対応した ? 文字列で、SQL文を作成します。 482 * 実際の値設定は、この、キーの並び順に応じた値を設定することになります。 483 * WHERE 文字列は、この、? も含めたWHERE条件の文字列を渡します。 484 * WHERE条件の場合は、この、?に、関数を設定したり、条件を指定したり、 485 * 色々なケースがあるため、単純にキーだけ指定する方法では、対応範囲が 486 * 限られるためです。 487 * conKeysとconValsは、固定値のキーと値です。 488 * conKeys,conVals,where がnullの場合は、これらの値を使用しません。 489 * 490 * @param table テーブルID 491 * @param keys 設定値に対応するキー配列 492 * @param conKeys 固定値の設定値に対応するキー配列 493 * @param conVals 固定値に対応する値配列(VARCHARのみ) 494 * @param where WHERE条件式 495 * @return アップデートSQL 496 * @og.rtnNotNull 497 */ 498 public static String getUpdateSQL( final String table , final String[] keys , final String[] conKeys , final String[] conVals , final String where ) { 499 final boolean useConst = conKeys != null && conVals != null && conKeys.length == conVals.length && conKeys.length > 0 ; 500 501 final StringBuilder sql = new StringBuilder( BUFFER_MIDDLE ) 502 .append( "UPDATE " ).append( table ).append( " SET " ) 503 .append( String.join( " = ? ," , keys ) ) // key[0] = ? , ・・・ = ? , key[n-1] という文字列が作成されます。 504 .append( " = ? " ); // 最後の key[n-1] の後ろに、 = ? という文字列を追加します。 505 506 if( useConst ) { 507 for( int i=0; i<conKeys.length; i++ ) { 508 sql.append( ',' ).append( conKeys[i] ).append( " = '" ).append( conVals[i] ).append( "' " ); 509 } 510 } 511 512 if( where != null && !where.isEmpty() ) { 513 sql.append( " WHERE " ).append( where ); // WHERE条件は、? に関数が入ったりするため、予め文字列を作成しておいてもらう。 514 } 515 516 return sql.toString(); 517 } 518 519 /** 520 * データをデリートする場合に使用するSQL文を作成します。 521 * 522 * これは、key に対応した ? 文字列で、SQL文を作成します。 523 * 実際の値設定は、この、キーの並び順に応じた値を設定することになります。 524 * WHERE 文字列は、この、? も含めたWHERE条件の文字列を渡します。 525 * WHERE条件の場合は、この、?に、関数を設定したり、条件を指定したり、 526 * 色々なケースがあるため、単純にキーだけ指定する方法では、対応範囲が 527 * 限られるためです。 528 * 529 * @param table テーブルID 530 * @param where 設定値に対応するキー配列(可変長引数) 531 * @return デリートSQL 532 * @og.rtnNotNull 533 */ 534 public static String getDeleteSQL( final String table , final String where ) { 535 final StringBuilder sql = new StringBuilder( BUFFER_MIDDLE ) 536 .append( "DELETE FROM " ).append( table ); 537 538 if( where != null && !where.isEmpty() ) { 539 sql.append( " WHERE " ).append( where ); // WHERE条件は、? に関数が入ったりするため、予め文字列を作成しておいてもらう。 540 } 541 542 return sql.toString(); 543 } 544}