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}