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( final 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 try( 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 conn.rollback(); 254 conn.setAutoCommit(true); 255 throw ex; 256 } 257 } 258 catch( final SQLException ex ) { 259 // MSG0019 = DB処理の実行に失敗しました。メッセージ=[{0}]。\n\tquery=[{1}]\n\tvalues={2} 260 throw MsgUtil.throwException( ex , "MSG0019" , ex.getMessage() , query , Arrays.toString( debugLine ) ); 261 } 262 263 return execCnt; 264 } 265 266 /** 267 * データ配列を渡してPreparedStatementの引数に、値をセットします。 268 * 269 * オラクル系の場合は、そのまま、setObject を行えば、自動変換しますが、 270 * それ以外のDBでは、java.sql.Types を渡す必要があります。さらに、null 値も、setNullを使用します。 271 * 今は、pMeta が、null かどうかで、オラクル系か、どうかを判定するようにしています。 272 * 273 * @param pstmt PreparedStatementオブジェクト 274 * @param values ?に割り当てる設定値 275 * @param pMeta オラクル系以外のDBに対して、type指定する場合に使用する ParameterMetaDataオブジェクト 276 * 277 * @throws SQLException DB処理の実行に失敗した場合 278 */ 279 private static void setObject( final PreparedStatement pstmt , final String[] values , final ParameterMetaData pMeta ) throws SQLException { 280 if( values != null && values.length > 0 ) { 281 // ORACLE では、ParameterMetaDataは、使わない。 282 if( pMeta == null ) { 283 int clmNo = 1; // JDBC のカラム番号は、1から始まる。 284 for( int i=0; i<values.length; i++ ) { 285 final String val = values[i]; 286 pstmt.setObject( clmNo++,val ); 287 } 288 } 289 else { 290 int clmNo = 1; // JDBC のカラム番号は、1から始まる。 291 for( int i=0; i<values.length; i++ ) { 292 final int type = pMeta.getParameterType( clmNo ); 293 final String val = values[i]; 294 if( val == null || val.isEmpty() ) { 295 pstmt.setNull( clmNo++, type ); 296 } 297 else { 298 pstmt.setObject( clmNo++,val,type ); 299 } 300 } 301 } 302 } 303 } 304 305 /** 306 * データ配列のListを渡して実際のDB処理を実行します。(暫定メソッド) 307 * 308 * これは、updQueryで、更新してみて、0件の場合、insQuery で追加処理を行います。 309 * 310 * データ配列は、1行分のデータに対する設定値の配列です。 311 * これは、keys で指定した並び順と一致している必要があります。 312 * 313 * @og.rev 6.8.1.5 (2017/09/08) LOGGER.debug 情報の追加 314 * 315 * @param insQuery 追加するSQL文 316 * @param updQuery 更新するSQL文 317 * @param insList ?に割り当てる設定値 318 * @param updList ?に割り当てる設定値 319 * @return ここでの処理件数 320 * 321 * @throws RuntimeException Connection DB処理の実行に失敗した場合 322 */ 323 public static int execute( final String insQuery , final String updQuery , final List<String[]> insList , final List<String[]> updList ) { 324 LOGGER.debug( () -> "execute insQuery=" + insQuery + " , updQuery=" + updQuery ); 325 326 String[] debugLine = null; 327 String query = null; 328 329 int execCnt = 0; 330 331 // try-with-resources 文 (AutoCloseable) 332 try( final Connection conn = getConnection() ) { 333 try ( final PreparedStatement inPstmt = conn.prepareStatement( insQuery ); 334 final PreparedStatement upPstmt = conn.prepareStatement( updQuery ) ) { 335 336 // ORACLE では、ParameterMetaDataは、使わない。 337 final ParameterMetaData inpMeta = oracleFlag ? null : inPstmt.getParameterMetaData(); 338 final ParameterMetaData uppMeta = oracleFlag ? null : upPstmt.getParameterMetaData(); 339 340 for( int i=0; i<updList.size(); i++ ) { // 更新処理と、挿入処理は、同じ数のListを用意する。 341 query = updQuery; 342 // 更新処理を行う。 343 final String[] upVals = updList.get(i); 344 debugLine = upVals; 345 setObject( upPstmt , upVals , uppMeta ); 346 347 int cnt = upPstmt.executeUpdate(); 348 349 if( cnt <= 0 ) { // 更新が無い、つまり、追加対象 350 query = insQuery; 351 // 挿入処理を行う。 352 final String[] inVals = insList.get(i); 353 debugLine = inVals; 354 setObject( inPstmt , inVals , inpMeta ); 355 356 LOGGER.debug( () -> "execute INSERT=" + Arrays.toString( inVals ) ); 357 358 cnt = inPstmt.executeUpdate(); 359 } 360 else { // 元々、このelse は必要ない。UPDATE は、先に処理済 361 LOGGER.debug( () -> "execute UPDATE=" + Arrays.toString( upVals ) ); 362 } 363 364 execCnt += cnt; 365 } 366 conn.commit(); 367 } 368 catch( final SQLException ex ) { 369 conn.rollback(); 370 conn.setAutoCommit(true); 371 throw ex; 372 } 373 } 374 catch( final SQLException ex ) { 375 // MSG0019 = DB処理の実行に失敗しました。メッセージ=[{0}]。\n\tquery=[{1}]\n\tvalues={2} 376 throw MsgUtil.throwException( ex , "MSG0019" , ex.getMessage() , query , Arrays.toString( debugLine ) ); 377 } 378 379 return execCnt; 380 } 381 382 /** 383 * 検索するデータベースを指定して、Queryを実行します(Transaction 対応)。 384 * 385 * ステートメントと引数により、Prepared クエリーの検索のみ実行します。 386 * 結果は,すべて文字列に変換されて格納されます。 387 * 388 * @param query ステートメント文字列 389 * @param args オブジェクトの引数配列 390 * 391 * @return 検索結果のリスト配列(結果が無ければ、サイズゼロのリスト) 392 * @throws RuntimeException DB検索処理の実行に失敗した場合 393 * @og.rtnNotNull 394 */ 395 public static List<String[]> dbQuery( final String query , final String... args ) { 396 // try-with-resources 文 (AutoCloseable) 397 try( final Connection conn = getConnection() ) { 398 try ( final PreparedStatement pstmt = conn.prepareStatement( query ) ) { 399 // ORACLE では、ParameterMetaDataは、使わない。 400 final ParameterMetaData pMeta = oracleFlag ? null : pstmt.getParameterMetaData(); 401 // 6.4.3.2 (2016/02/19) args が null でなく、length==0 でない場合のみ、処理する。 402 setObject( pstmt , args , pMeta ); 403 404 if( pstmt.execute() ) { 405 try( final ResultSet resultSet = pstmt.getResultSet() ) { 406 return resultToArray( resultSet ); 407 } 408 } 409 conn.commit(); 410 } 411 catch ( final SQLException ex ) { 412 conn.rollback(); 413 conn.setAutoCommit(true); 414 throw ex; 415 } 416 } 417 catch ( final SQLException ex ) { 418 // MSG0019 = DB処理の実行に失敗しました。メッセージ=[{0}]。\n\tquery=[{1}]\n\tvalues={2} 419 throw MsgUtil.throwException( ex , "MSG0019" , ex.getMessage() , query , Arrays.toString( args ) ); 420 } 421 422 return new ArrayList<String[]>(); 423 } 424 425 /** 426 * ResultSet より、結果の文字列配列を作成します。 427 * 428 * 結果は,すべて文字列に変換されて格納されます。 429 * 移動したメソッドで使われているのでこれも移動 430 * 431 * @param resultSet ResultSetオブジェクト 432 * 433 * @return ResultSetの検索結果リスト配列 434 * @throws java.sql.SQLException データベース・アクセス・エラーが発生した場合 435 * @og.rtnNotNull 436 */ 437 public static List<String[]> resultToArray( final ResultSet resultSet ) throws SQLException { 438 final ArrayList<String[]> data = new ArrayList<>(); 439 440 final ResultSetValue rsv = new ResultSetValue( resultSet ); 441 442 while( rsv.next() ) { 443 data.add( rsv.getValues() ); 444 } 445 446 return data; 447 } 448 449 /** 450 * データをインサートする場合に使用するSQL文を作成します。 451 * 452 * これは、key に対応した ? 文字列で、SQL文を作成します。 453 * 実際の値設定は、この、キーの並び順に応じた値を設定することになります。 454 * conKeysとconValsは、固定値のキーと値です。 455 * conKeys,conVals がnullの場合は、これらの値を使用しません。 456 * 457 * @param table テーブルID 458 * @param keys 設定値に対応するキー配列 459 * @param conKeys 固定値の設定値に対応するキー配列 460 * @param conVals 固定値に対応する値配列 461 * @return インサートSQL 462 * @og.rtnNotNull 463 */ 464 public static String getInsertSQL( final String table , final String[] keys , final String[] conKeys , final String[] conVals ) { 465 final String[] vals = new String[keys.length]; 466 Arrays.fill( vals , "?" ); 467 468 final boolean useConst = conKeys != null && conVals != null && conKeys.length == conVals.length && conKeys.length > 0 ; 469 470 final StringBuilder sql = new StringBuilder( BUFFER_MIDDLE ) 471 .append( "INSERT INTO " ).append( table ) 472 .append( " ( " ) 473 .append( String.join( "," , keys ) ); 474 475 if( useConst ) { 476 sql.append( ',' ).append( String.join( "," , conKeys ) ); 477 } 478 479 sql.append( " ) VALUES ( " ) 480 .append( String.join( "," , vals ) ); 481 482 if( useConst ) { 483 sql.append( ",'" ).append( String.join( "','" , conVals ) ).append( '\'' ); 484 } 485 486 return sql.append( " )" ).toString(); 487 } 488 489 /** 490 * データをアップデートする場合に使用するSQL文を作成します。 491 * 492 * これは、key に対応した ? 文字列で、SQL文を作成します。 493 * 実際の値設定は、この、キーの並び順に応じた値を設定することになります。 494 * WHERE 文字列は、この、? も含めたWHERE条件の文字列を渡します。 495 * WHERE条件の場合は、この、?に、関数を設定したり、条件を指定したり、 496 * 色々なケースがあるため、単純にキーだけ指定する方法では、対応範囲が 497 * 限られるためです。 498 * conKeysとconValsは、固定値のキーと値です。 499 * conKeys,conVals,where がnullの場合は、これらの値を使用しません。 500 * 501 * @param table テーブルID 502 * @param keys 設定値に対応するキー配列 503 * @param conKeys 固定値の設定値に対応するキー配列 504 * @param conVals 固定値に対応する値配列(VARCHARのみ) 505 * @param where WHERE条件式 506 * @return アップデートSQL 507 * @og.rtnNotNull 508 */ 509 public static String getUpdateSQL( final String table , final String[] keys , final String[] conKeys , final String[] conVals , final String where ) { 510 final boolean useConst = conKeys != null && conVals != null && conKeys.length == conVals.length && conKeys.length > 0 ; 511 512 final StringBuilder sql = new StringBuilder( BUFFER_MIDDLE ) 513 .append( "UPDATE " ).append( table ).append( " SET " ) 514 .append( String.join( " = ? ," , keys ) ) // key[0] = ? , ・・・ = ? , key[n-1] という文字列が作成されます。 515 .append( " = ? " ); // 最後の key[n-1] の後ろに、 = ? という文字列を追加します。 516 517 if( useConst ) { 518 for( int i=0; i<conKeys.length; i++ ) { 519 sql.append( ',' ).append( conKeys[i] ).append( " = '" ).append( conVals[i] ).append( "' " ); 520 } 521 } 522 523 if( where != null && !where.isEmpty() ) { 524 sql.append( " WHERE " ).append( where ); // WHERE条件は、? に関数が入ったりするため、予め文字列を作成しておいてもらう。 525 } 526 527 return sql.toString(); 528 } 529 530 /** 531 * データをデリートする場合に使用するSQL文を作成します。 532 * 533 * これは、key に対応した ? 文字列で、SQL文を作成します。 534 * 実際の値設定は、この、キーの並び順に応じた値を設定することになります。 535 * WHERE 文字列は、この、? も含めたWHERE条件の文字列を渡します。 536 * WHERE条件の場合は、この、?に、関数を設定したり、条件を指定したり、 537 * 色々なケースがあるため、単純にキーだけ指定する方法では、対応範囲が 538 * 限られるためです。 539 * 540 * @param table テーブルID 541 * @param where 設定値に対応するキー配列(可変長引数) 542 * @return デリートSQL 543 * @og.rtnNotNull 544 */ 545 public static String getDeleteSQL( final String table , final String where ) { 546 final StringBuilder sql = new StringBuilder( BUFFER_MIDDLE ) 547 .append( "DELETE FROM " ).append( table ); 548 549 if( where != null && !where.isEmpty() ) { 550 sql.append( " WHERE " ).append( where ); // WHERE条件は、? に関数が入ったりするため、予め文字列を作成しておいてもらう。 551 } 552 553 return sql.toString(); 554 } 555}