Source code

001/*******************************************************************************
002The MIT License (MIT)
003
004Copyright (c) 2024 KILLCODING.COM
005
006Permission is hereby granted, free of charge, to any person obtaining a copy
007of this software and associated documentation files (the "Software"), to deal
008in the Software without restriction, including without limitation the rights
009to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
010copies of the Software, and to permit persons to whom the Software is
011furnished to do so, subject to the following conditions:
012
013The above copyright notice and this permission notice shall be included in
014all copies or substantial portions of the Software.
015
016THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
017IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
018FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
019AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
020LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
021OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
022THE SOFTWARE.
023*****************************************************************************/
024package com.killcoding.datasource;
025
026import java.sql.Connection;
027import java.sql.PreparedStatement;
028import java.util.List;
029import java.sql.SQLException;
030import java.sql.Types;
031import java.util.Map;
032import java.sql.ResultSet;
033import java.sql.ResultSetMetaData;
034import java.util.HashMap;
035import java.util.ArrayList;
036import java.util.regex.Pattern;
037import java.util.regex.Matcher;
038import java.util.Arrays;
039import com.killcoding.tool.ResultMap;
040import java.sql.DatabaseMetaData;
041import com.killcoding.log.LoggerFactory;
042import com.killcoding.log.Logger;
043import java.util.concurrent.Executors;
044import com.killcoding.datasource.DriverConnection;
045import com.killcoding.datasource.DriverDataSource;
046import java.util.concurrent.ConcurrentHashMap;
047import com.killcoding.tool.CommonTools;
048import com.killcoding.tool.ConfigProperties;
049import java.io.File;
050import java.text.DateFormat;
051import java.text.SimpleDateFormat;
052import com.killcoding.tool.FileTools;
053import java.io.IOException;
054import java.nio.file.Files;
055import java.util.Date;
056import java.nio.file.Paths;
057import java.nio.file.Path;
058import java.nio.file.StandardCopyOption;
059import com.killcoding.cache.CacheArray;
060import com.killcoding.cache.CacheArrayFilter;
061import com.killcoding.tool.CodeEscape;
062import java.sql.Blob;
063import java.util.stream.Collectors;
064import java.net.URI;
065import java.util.Comparator;
066import java.sql.Timestamp;
067import java.sql.Clob;
068import java.io.InputStream;
069
070/**
071 * This class is execute sql base class.
072 * Support database replication.
073 * Support database multi-activity.
074 * Support database CRUD
075 * */
076public class DriverExecutor {
077
078        protected final static Map<Integer, List<DriverExecutor>> SYNC_EXECUTOR_MARK = new ConcurrentHashMap<Integer, List<DriverExecutor>>();
079        private final static Map<Integer, Long> SYNC_CONN_ERROR_TIME = new ConcurrentHashMap<Integer, Long>();
080        
081        private final static Map<String,String> SQL_LOG_MSG_MAPPING = new ConcurrentHashMap<String,String>();
082        private final static Map<String,Boolean> SQL_LOG_OVERSPEND_MAPPING = new ConcurrentHashMap<String,Boolean>();
083
084        protected Logger log = null;
085
086        public final static String COLUMN_NAME_CASE_UPPER = "UPPER";
087        public final static String COLUMN_NAME_CASE_LOWER = "LOWER";
088        public final static String COLUMN_NAME_CASE_ORIGINAL = "ORIGINAL";
089        public static String COLUMN_NAME_CASE_MODE = COLUMN_NAME_CASE_ORIGINAL;
090
091        protected boolean closed = true;
092        protected Connection connection = null;
093        private static CacheArray sqlLogCacheArray = null;
094
095        /**
096         * New a DriverExecutor object
097         * @param connection - JDBC connection
098         * */
099        public DriverExecutor(Connection connection) {
100                super();
101                log = LoggerFactory.getLogger(this.getClass());
102                this.connection = connection;
103                writeSqlLog("open", 0, "open", "");
104                closed = (this.connection == null);
105        }
106
107        /**
108         * Get current Connection
109         * @return Connection
110         * */
111        public Connection getConnection() {
112                return connection;
113        }
114
115        /**
116         * Get column classes by table name
117         * @exception SQLException
118         * @return Map<String,Object> - column and java type class mapping
119         * @param tableName - table name
120         * */
121        public Map<String, Object> getColumnClasses(String tableName) throws SQLException {
122                Map<String, Object> types = null;
123                ResultSet result = null;
124                PreparedStatement statement = null;
125                String sql = String.format("SELECT * FROM %s WHERE 1 = 0", tableName);
126                statement = connection.prepareStatement(sql);
127                result = statement.executeQuery();
128                final ResultSetMetaData rsmd = result.getMetaData();
129                for (int i = 0; i < rsmd.getColumnCount(); i++) {
130                        if (types == null) {
131                                types = new ResultMap<String, Object>();
132                        }
133                        String cn = rsmd.getColumnClassName(i + 1);
134                        types.put(converCase(rsmd.getColumnLabel(i + 1)), cn);
135                }
136                return types;
137        }
138
139        /**
140         * Get column db data types by table name
141         * @exception SQLException
142         * @return Map<String, Object> - column and db data type mapping
143         * @param tableName - Table name
144         * */
145        public Map<String, Object> getColumnTypes(String tableName) throws SQLException {
146                ResultSet result = null;
147                PreparedStatement statement = null;
148                String sql = String.format("SELECT * FROM %s WHERE 1 = 0", tableName);
149                Map<String, Object> types = null;
150                statement = connection.prepareStatement(sql);
151                result = statement.executeQuery();
152                final ResultSetMetaData rsmd = result.getMetaData();
153                for (int i = 0; i < rsmd.getColumnCount(); i++) {
154                        if (types == null) {
155                                types = new ResultMap<String, Object>();
156                        }
157                        types.put(converCase(rsmd.getColumnLabel(i + 1)), rsmd.getColumnTypeName(i + 1));
158                }
159                return types;
160        }
161
162        /**
163         * Show column data type and java type mapping
164         * @exception SQLException
165         * @return List<Map<String, Object>> - Mapping list
166         * @param tableName - Table name
167         * */
168        public List<Map<String, Object>> desc(String tableName) throws SQLException {
169                List<String> primaryKeys = getPrimaryKeys(tableName);
170                List<Map<String, Object>> results = new ArrayList<Map<String, Object>>();
171                ResultSet result = null;
172                PreparedStatement statement = null;
173                String sql = String.format("SELECT * FROM %s WHERE 1 = 0", tableName);
174                statement = connection.prepareStatement(sql);
175                result = statement.executeQuery();
176                final ResultSetMetaData rsmd = result.getMetaData();
177                for (int i = 0; i < rsmd.getColumnCount(); i++) {
178                        Map<String, Object> types = new ResultMap<String, Object>();
179                        int ci = i + 1;
180                        boolean nullable = rsmd.isNullable(ci) == 1;
181                        String name = converCase(rsmd.getColumnLabel(ci));
182                        String isPk = "UNKNOWN";
183                        if (primaryKeys != null) {
184                                isPk = primaryKeys.contains(name) ? "Y" : "N";
185                        }
186                        types.put("NAME", name);
187                        types.put("PRIMARY_KEY", isPk);
188                        types.put("DATA_TYPE", rsmd.getColumnTypeName(ci));
189                        types.put("JAVA_TYPE", rsmd.getColumnClassName(ci));
190                        types.put("PRECISION", rsmd.getPrecision(ci));
191                        types.put("ALLOW_NULLABLE", nullable ? "Y" : 'N');
192                        results.add(types);
193                }
194                return results;
195        }
196
197        /**
198         * Get primary Keys by table name
199         * @return List<String> - Primary Keys
200         * @param _tableName - Table name
201         * */
202        public List<String> getPrimaryKeys(String _tableName) {
203                try {
204                        List<String> pks = new ArrayList<String>();
205                        DatabaseMetaData meta = connection.getMetaData();
206                        ResultSet tables = meta.getTables(null, null, "%", new String[] { "TABLE" });
207                        while (tables.next()) {
208                                String catalog = tables.getString("TABLE_CAT");
209                                String schema = tables.getString("TABLE_SCHEM");
210                                String tableName = tables.getString("TABLE_NAME");
211                                if (tableName.equalsIgnoreCase(_tableName)) {
212                                        ResultSet primaryKeys = meta.getPrimaryKeys(catalog, schema, tableName);
213                                        while (primaryKeys.next()) {
214                                                pks.add(primaryKeys.getString("COLUMN_NAME"));
215                                        }
216                                        break;
217                                }
218                        }
219                        return pks;
220                } catch (Exception e) {
221                        log.warn(e);
222                        return null;
223                }
224        }
225
226        /**
227         * Get all tables
228         * @return List<Map<String, Object>>
229         * @exception SQLException
230         * */
231        public List<Map<String, Object>> getAllTables() throws SQLException {
232                List<Map<String, Object>> tablesList = new ArrayList<Map<String, Object>>();
233                DatabaseMetaData meta = connection.getMetaData();
234                ResultSet tables = meta.getTables(null, null, "%", new String[] { "TABLE" });
235                while (tables.next()) {
236                        String catalog = tables.getString("TABLE_CAT");
237                        String schema = tables.getString("TABLE_SCHEM");
238                        String tableName = tables.getString("TABLE_NAME");
239                        Map<String, Object> t = new ResultMap<String, Object>();
240                        t.put("TABLE_SCHEMA", schema);
241                        t.put("TABLE_NAME", tableName);
242                        tablesList.add(t);
243                }
244                return tablesList;
245        }
246
247        /**
248         * Get all tables by schema
249         * @return List<Map<String, Object>>
250         * @exception SQLException
251         * */
252        public List<Map<String, Object>> getAllTables(String _schema) throws SQLException {
253                List<Map<String, Object>> tablesList = new ArrayList<Map<String, Object>>();
254                DatabaseMetaData meta = connection.getMetaData();
255                ResultSet tables = meta.getTables(null, null, "%", new String[] { "TABLE" });
256                while (tables.next()) {
257                        String schema = tables.getString("TABLE_SCHEM");
258                        if (schema != null && schema.equalsIgnoreCase(_schema)) {
259                                String catalog = tables.getString("TABLE_CAT");
260                                String tableName = tables.getString("TABLE_NAME");
261                                Map<String, Object> t = new ResultMap<String, Object>();
262                                t.put("TABLE_SCHEMA", schema);
263                                t.put("TABLE_NAME", tableName);
264                                tablesList.add(t);
265                        }
266                }
267                return tablesList;
268        }
269
270        /**
271         * Query first record
272         * @exception SQLException
273         * @return Map<String,Object> - First result
274         * @param sql - Condition use format ':column_name'
275         * @param params
276         * */
277        public Map<String, Object> first(String sql, Map<String, Object> params) throws SQLException {
278                List<Map<String, Object>> list = find(0, 1, sql, params);
279                if (list.size() > 0) {
280                        return list.get(0);
281                }
282                return null;
283        }
284
285        /**
286         * Query first record
287         * @exception SQLException
288         * @return Map<String,Object> - First result
289         * @param sql
290         * */
291        public Map<String, Object> first(String sql) throws SQLException {
292                return first(sql, Arrays.asList(new Object[] {}));
293        }
294
295        /**
296         * Query first record
297         * @exception SQLException
298         * @return Map<String,Object> - First result
299         * @param sql - Condition use format '?'
300         * @param params
301         * */
302        public Map<String, Object> first(String sql, List<Object> params) throws SQLException {
303                List<Map<String, Object>> list = find(0, 1, sql, params);
304                if (list.size() > 0) {
305                        return list.get(0);
306                }
307                return null;
308        }
309
310        /**
311         * Query all matched records
312         * @exception SQLException
313         * @return List<Map<String, Object>>
314         * @param sql 
315         * @param params
316         * */
317        public List<Map<String, Object>> find(String sql) throws SQLException {
318                return find(0, 0, sql, Arrays.asList(new Object[] {}));
319        }
320
321        /**
322         * Query all matched records
323         * @exception SQLException
324         * @return List<Map<String, Object>>
325         * @param cursorStart - JDBC result Cursor start index
326         * @param maxRows - JDBC result max rows (JDBC limited rows 50,000,000)
327         * @param sql 
328         * @param params
329         * */
330        public List<Map<String, Object>> find(int cursorStart, int maxRows, String sql) throws SQLException {
331                return find(cursorStart, maxRows, sql, Arrays.asList(new Object[] {}));
332        }
333
334        /**
335         * Query all matched records
336         * @exception SQLException
337         * @return List<Map<String, Object>>
338         * @param sql - Condition use format ':column_name'
339         * @param params
340         * */
341        public List<Map<String, Object>> find(String sql, Map<String, Object> params) throws SQLException {
342                return find(0, 0, sql, params);
343        }
344
345        /**
346         * Query all matched records
347         * @exception SQLException
348         * @return List<Map<String, Object>>
349         * @param cursorStart - JDBC result Cursor start index
350         * @param maxRows - JDBC result max rows (JDBC limited rows 50,000,000)
351         * @param sql - Condition use format ':column_name'
352         * @param params
353         * */
354        public List<Map<String, Object>> find(int cursorStart, int maxRows, String sql, Map<String, Object> params)
355                        throws SQLException {
356                String csql = converSql(sql);
357                List<Object> cparams = converParams(sql, params);
358                return find(cursorStart, maxRows, csql, cparams);
359        }
360
361        /**
362         * Query all matched records
363         * @exception SQLException
364         * @return List<Map<String, Object>>
365         * @param cursorStart - JDBC result Cursor start index
366         * @param maxRows - JDBC result max rows (JDBC limited rows 50,000,000)
367         * @param sql - Condition use format '?'
368         * @param params
369         * */
370        public List<Map<String, Object>> find(int cursorStart, int maxRows, String sql, List<Object> params)
371                        throws SQLException {
372                long begin = System.currentTimeMillis();
373                boolean allowedLog = writeSqlLog("find", begin,
374                                String.format("%s [cursorStart=%s,maxRows=%s]", sql, cursorStart, maxRows), params);
375
376                PreparedStatement statement = null;
377                Map<String, Object> row = null;
378                ResultSet result = null;
379                final List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>();
380                try {
381                        // ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE
382                        // ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY
383                        statement = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
384                        if (params != null) {
385                                int size = params.size();
386                                for (int i = 0; i < size; i++) {
387                                        int ci = i + 1;
388                                        Object param = params.get(i);
389                                        if (param == null) {
390                                                statement.setNull(ci, Types.VARCHAR);
391                                        } else {
392                                                statement.setObject(ci, param);
393                                        }
394                                }
395                        }
396                        if (maxRows > 0) {
397                                statement.setMaxRows(maxRows);
398                        }
399                        result = statement.executeQuery();
400                        result.absolute(cursorStart);
401                        final ResultSetMetaData rsmd = result.getMetaData();
402                        final int c = rsmd.getColumnCount();
403                        while (result.next()) {
404                                row = new ResultMap<String, Object>();
405                                for (int i = 0; i < c; i++) {
406                                        int ci = i + 1;
407                                        Object value = null;
408                                        Object originValue = result.getObject(ci);
409                                        if (originValue == null) {
410                                                value = originValue;
411                                        } else if (originValue instanceof Blob) {
412                                                Blob blobValue = (Blob) originValue;
413                                                InputStream is = null;
414                                                try {
415                                                        is = blobValue.getBinaryStream();
416                                                        if(is != null) value = is.readAllBytes();
417                                                } catch (IOException e) {
418                                                        throw new SQLException(e.getMessage(), e);
419                                                } finally {
420                                                        if (blobValue != null) {
421                                                                try {
422                                                                        blobValue.free();
423                                                                } catch (SQLException e) {
424                                                                        throw e;
425                                                                }
426                                                        }
427                                                        if (is != null) {
428                                                                try {
429                                                                        is.close();
430                                                                } catch (IOException e) {
431                                                                        throw new SQLException(e.getMessage(), e);
432                                                                }
433                                                        }
434                                                }
435                                        } else if (originValue instanceof Clob) {
436                                                Clob clobValue = (Clob) originValue;
437                                                InputStream is = null;
438                                                try {
439                                                        is = clobValue.getAsciiStream();
440                                                        if(is != null) value = is.readAllBytes();
441                                                } catch (IOException e) {
442                                                        throw new SQLException(e.getMessage(), e);
443                                                } finally {
444                                                        if (clobValue != null) {
445                                                                try {
446                                                                        clobValue.free();
447                                                                } catch (SQLException e) {
448                                                                        throw e;
449                                                                }
450                                                        }
451                                                        if (is != null) {
452                                                                try {
453                                                                        is.close();
454                                                                } catch (IOException e) {
455                                                                        throw new SQLException(e.getMessage(), e);
456                                                                }
457                                                        }
458                                                }
459                                        } else {
460                                                value = originValue;
461                                        }
462                                        row.put(converCase(rsmd.getColumnLabel(ci)), value);
463                                }
464                                rows.add(row);
465                        }
466                        if (allowedLog) {
467                                writeSqlLog("rows", begin, "rows", rows.size());
468                                long spend = System.currentTimeMillis() - begin;
469                                writeSqlLog("spend", begin, "spend", spend);
470                        }
471                        return rows;
472                } catch (SQLException e) {
473                        if (allowedLog)
474                                writeSqlLog("error", begin, "error", String.format("%s,%s", e.getErrorCode(), e.getMessage()));
475
476                        throw e;
477                } finally {
478                        if (result != null)
479                                result.close();
480
481                        if (statement != null)
482                                statement.close();
483                }
484        }
485
486        /**
487         * Execute stored proc (and return boolean) 
488         * @param sql - Query sql 
489         * @exception SQLException 
490         * @return boolean
491         * */
492        public boolean callAndReturnBoolean(String sql) throws SQLException {
493                return callAndReturnBoolean(sql, Arrays.asList(new Object[] {}));
494        }
495
496        /**
497         * Execute stored proc (and return boolean) 
498         * @param sql - Query sql, condition use format ':column_name'
499         * @param params
500         * @exception SQLException 
501         * @return boolean
502         * */
503        public boolean callAndReturnBoolean(String sql, Map<String, Object> params) throws SQLException {
504                String csql = converSql(sql);
505                List<Object> cparams = converParams(sql, params);
506                return callAndReturnBoolean(sql, params);
507        }
508
509        /**
510         * Execute stored proc (and return boolean) 
511         * @param sql - Query sql,condition use format '?'
512         * @param params
513         * @exception SQLException 
514         * @return boolean
515         * */
516        public boolean callAndReturnBoolean(String sql, List<Object> params) throws SQLException {
517
518                if (!checkSqlAvailable(sql))
519                        return false;
520
521                long begin = System.currentTimeMillis();
522                boolean allowedLog = writeSqlLog("call", begin, sql, params);
523
524                PreparedStatement statement = null;
525                try {
526                        statement = connection.prepareCall("{" + sql + "}");
527                        if (params != null) {
528                                int size = params.size();
529                                for (int i = 0; i < size; i++) {
530                                        Object param = params.get(i);
531                                        if (param == null) {
532                                                statement.setNull(i + 1, Types.VARCHAR);
533                                        } else {
534                                                statement.setObject(i + 1, param);
535                                        }
536                                }
537                        }
538
539                        boolean returnResult = statement.execute();
540
541                        if (allowedLog) {
542                                writeSqlLog("return", begin, "return", returnResult);
543                                long spend = System.currentTimeMillis() - begin;
544                                writeSqlLog("spend", begin, "spend", spend);
545                        }
546
547                        callAndReturnBooleanSync(connection, begin, sql, params, returnResult);
548
549                        return returnResult;
550                } catch (SQLException e) {
551                        if (allowedLog)
552                                writeSqlLog("error", begin, "error", String.format("%s,%s", e.getErrorCode(), e.getMessage()));
553
554                        throw e;
555                } finally {
556                        if (statement != null)
557                                statement.close();
558                }
559        }
560
561        /**
562         * Execute stored proc (and return rows) 
563         * @param sql - Query sql
564         * @exception SQLException 
565         * @return int
566         * */
567        public int callAndReturnRows(String sql) throws SQLException {
568                return callAndReturnRows(sql, Arrays.asList(new Object[] {}));
569        }
570
571        /**
572         * Execute stored proc (and return rows) 
573         * @param sql - Query sql,condition use format ':column_name'
574         * @param params
575         * @exception SQLException 
576         * @return int
577         * */
578        public int callAndReturnRows(String sql, Map<String, Object> params) throws SQLException {
579                String csql = converSql(sql);
580                List<Object> cparams = converParams(sql, params);
581                return callAndReturnRows(sql, params);
582        }
583
584        /**
585         * Execute stored proc (and return rows) 
586         * @param sql - Query sql,condition use format '?'
587         * @param params
588         * @exception SQLException 
589         * @return int
590         * */
591        public int callAndReturnRows(String sql, List<Object> params) throws SQLException {
592
593                if (!checkSqlAvailable(sql))
594                        return -1;
595
596                long begin = System.currentTimeMillis();
597                boolean allowedLog = writeSqlLog("call", begin, sql, params);
598
599                PreparedStatement statement = null;
600                try {
601                        statement = connection.prepareCall("{" + sql + "}");
602                        if (params != null) {
603                                int size = params.size();
604                                for (int i = 0; i < size; i++) {
605                                        Object param = params.get(i);
606                                        if (param == null) {
607                                                statement.setNull(i + 1, Types.VARCHAR);
608                                        } else {
609                                                statement.setObject(i + 1, param);
610                                        }
611                                }
612                        }
613                        int row = statement.executeUpdate();
614
615                        if (allowedLog) {
616                                writeSqlLog("return", begin, "return", row);
617                                long spend = System.currentTimeMillis() - begin;
618                                writeSqlLog("spend", begin, "spend", spend);
619                        }
620
621                        callAndReturnRowsSync(connection, begin, sql, params, row);
622
623                        return row;
624                } catch (SQLException e) {
625                        if (allowedLog)
626                                writeSqlLog("error", begin, "error", String.format("%s,%s", e.getErrorCode(), e.getMessage()));
627
628                        throw e;
629                } finally {
630                        if (statement != null)
631                                statement.close();
632                }
633        }
634
635        /**
636         * Execute stored proc (and return List) 
637         * @param sql - Query sql
638         * @exception SQLException 
639         * @return List<Map<String, Object>>
640         * */
641        public List<Map<String, Object>> callAndReturnList(String sql) throws SQLException {
642                return callAndReturnList(0, 0, sql, Arrays.asList(new Object[] {}));
643        }
644
645        /**
646         * Execute stored proc (and return List) 
647         * @param cursorStart - JDBC result Cursor start index
648         * @param maxRows - JDBC result max rows (JDBC limited rows 50,000,000) 
649         * @param sql - Query sql
650         * @exception SQLException 
651         * @return List<Map<String, Object>>
652         * */
653        public List<Map<String, Object>> callAndReturnList(int cursorStart, int maxRows, String sql) throws SQLException {
654                return callAndReturnList(cursorStart, maxRows, sql, Arrays.asList(new Object[] {}));
655        }
656
657        /**
658         * Execute stored proc (and return List) 
659         * @param cursorStart - JDBC result Cursor start index
660         * @param maxRows - JDBC result max rows (JDBC limited rows 50,000,000) 
661         * @param sql - Query sql, condition use format ':column_name'
662         * @exception SQLException 
663         * @return List<Map<String, Object>>
664         * */
665        public List<Map<String, Object>> callAndReturnList(int cursorStart, int maxRows, String sql,
666                        Map<String, Object> params) throws SQLException {
667                String csql = converSql(sql);
668                List<Object> cparams = converParams(sql, params);
669                return callAndReturnList(cursorStart, maxRows, sql, params);
670        }
671
672        /**
673         * Execute stored proc (and return List) 
674         * @param cursorStart - JDBC result Cursor start index
675         * @param maxRows - JDBC result max rows (JDBC limited rows 50,000,000) 
676         * @param sql - Query sql, condition use format '?'
677         * @exception SQLException 
678         * @return List<Map<String, Object>>
679         * */
680        public List<Map<String, Object>> callAndReturnList(int cursorStart, int maxRows, String sql, List<Object> params)
681                        throws SQLException {
682
683                if (!checkSqlAvailable(sql))
684                        return null;
685
686                long begin = System.currentTimeMillis();
687                boolean allowedLog = writeSqlLog("call", begin,
688                                String.format("%s [cursorStart=%s,maxRows=%s]", sql, cursorStart, maxRows), params);
689
690                PreparedStatement statement = null;
691                Map<String, Object> row = null;
692                ResultSet result = null;
693                final List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>();
694                try {
695                        statement = connection.prepareCall("{" + sql + "}");
696                        if (params != null) {
697                                int size = params.size();
698                                for (int i = 0; i < size; i++) {
699                                        int ci = i + 1;
700                                        Object param = params.get(i);
701                                        if (param == null) {
702                                                statement.setNull(ci, Types.VARCHAR);
703                                        } else {
704                                                statement.setObject(ci, param);
705                                        }
706                                }
707                        }
708                        if (maxRows > 0) {
709                                statement.setMaxRows(maxRows);
710                        }
711                        result = statement.executeQuery();
712                        final ResultSetMetaData rsmd = result.getMetaData();
713                        final int c = rsmd.getColumnCount();
714                        int rowIndex = 0;
715                        while (result.next()) {
716                                if (rowIndex >= cursorStart) {
717                                        row = new ResultMap<String, Object>();
718                                        for (int i = 0; i < c; i++) {
719                                                int ci = i + 1;
720                                                Object value = null;
721                                                Object originValue = result.getObject(ci);
722                                                if (originValue == null) {
723                                                        value = originValue;
724                                                } else if (originValue instanceof Blob) {
725                                                        Blob blobValue = (Blob) originValue;
726                                                        InputStream is = null;
727                                                        try {
728                                                                is = blobValue.getBinaryStream();
729                                                                if(is != null) value = is.readAllBytes();
730                                                        } catch (IOException e) {
731                                                                throw new SQLException(e.getMessage(), e);
732                                                        } finally {
733                                                                if (blobValue != null) {
734                                                                        try {
735                                                                                blobValue.free();
736                                                                        } catch (SQLException e) {
737                                                                                throw e;
738                                                                        }
739                                                                }
740                                                                if (is != null) {
741                                                                        try {
742                                                                                is.close();
743                                                                        } catch (IOException e) {
744                                                                                throw new SQLException(e.getMessage(), e);
745                                                                        }
746                                                                }
747                                                        }
748                                                } else if (originValue instanceof Clob) {
749                                                        Clob clobValue = (Clob) originValue;
750                                                        InputStream is = null;
751                                                        try {
752                                                                is = clobValue.getAsciiStream();
753                                                                if(is != null) value = is.readAllBytes();
754                                                        } catch (IOException e) {
755                                                                throw new SQLException(e.getMessage(), e);
756                                                        } finally {
757                                                                if (clobValue != null) {
758                                                                        try {
759                                                                                clobValue.free();
760                                                                        } catch (SQLException e) {
761                                                                                throw e;
762                                                                        }
763                                                                }
764                                                                if (is != null) {
765                                                                        try {
766                                                                                is.close();
767                                                                        } catch (IOException e) {
768                                                                                throw new SQLException(e.getMessage(), e);
769                                                                        }
770                                                                }
771                                                        }
772                                                } else {
773                                                        value = originValue;
774                                                }
775                                                row.put(converCase(rsmd.getColumnLabel(ci)), value);
776                                        }
777                                        rows.add(row);
778                                }
779                                rowIndex++;
780                        }
781
782                        if (allowedLog) {
783                                writeSqlLog("rows", begin, "rows", rows);
784                                long spend = System.currentTimeMillis() - begin;
785                                writeSqlLog("spend", begin, "spend", spend);
786                        }
787
788                        callAndReturnListSync(connection, cursorStart, maxRows, sql, params);
789
790                        return rows;
791                } catch (SQLException e) {
792                        if (allowedLog)
793                                writeSqlLog("error", begin, "error", String.format("%s,%s", e.getErrorCode(), e.getMessage()));
794                        throw e;
795                } finally {
796                        if (result != null)
797                                result.close();
798
799                        if (statement != null)
800                                statement.close();
801                }
802        }
803
804        /**
805         * Execute sql
806         * @exception SQLException
807         * @param sql
808         * @return int
809         * */
810        public int execute(String sql) throws SQLException {
811                return execute(sql, Arrays.asList(new Object[] {}));
812        }
813
814        /**
815         * Execute sql
816         * @exception SQLException
817         * @param sql - Condition use format ':column_name'
818         * @param params 
819         * @return int
820         * */
821        public int execute(String sql, Map<String, Object> params) throws SQLException {
822                String csql = converSql(sql);
823                List<Object> cparams = converParams(sql, params);
824                return execute(csql, cparams);
825        }
826
827        /**
828         * Execute sql
829         * @exception SQLException
830         * @param sql - Condition use format '?'
831         * @param params 
832         * @return int
833         * */
834        public int execute(String sql, List<Object> params) throws SQLException {
835                if (!checkSqlAvailable(sql))
836                        return -1;
837
838                long begin = System.currentTimeMillis();
839                boolean allowedLog = writeSqlLog("execute", begin, sql, params);
840
841                PreparedStatement statement = null;
842                try {
843                        statement = connection.prepareStatement(sql);
844                        if (params != null) {
845                                int size = params.size();
846                                for (int i = 0; i < size; i++) {
847                                        Object param = params.get(i);
848                                        if (param == null) {
849                                                statement.setNull(i + 1, Types.VARCHAR);
850                                        } else {
851                                                statement.setObject(i + 1, param);
852                                        }
853                                }
854                        }
855                        int row = statement.executeUpdate();
856
857                        if (allowedLog) {
858                                writeSqlLog("return", begin, "return", row);
859                                long spend = System.currentTimeMillis() - begin;
860                                writeSqlLog("spend", begin, "spend", spend);
861                        }
862
863                        executeSync(connection, begin, sql, params, row);
864
865                        return row;
866                } catch (SQLException e) {
867                        if (allowedLog)
868                                writeSqlLog("error", begin, "error", String.format("%s,%s", e.getErrorCode(), e.getMessage()));
869                        throw e;
870                } finally {
871                        if (statement != null)
872                                statement.close();
873                }
874        }
875
876        /**
877         * Execute batch
878         * @exception SQLException
879         * @param sql - Condition use format ':column_name'
880         * @param records
881         * @return int - Return rows
882         * */
883        public int executeBatch(String sql, List<Map<String, Object>> records) throws SQLException {
884                String csql = converSql(sql);
885                List<List<Object>> crecords = new ArrayList<List<Object>>();
886                for (Map<String, Object> record : records) {
887                        List<Object> crecord = converParams(sql, record);
888                        crecords.add(crecord);
889                }
890                return executeBatchList(csql, crecords);
891        }
892
893        /**
894         * Execute batch
895         * @exception SQLException
896         * @param sql - Condition use format '?'
897         * @param records
898         * @return int - Return rows
899         * */
900        public int executeBatchList(String sql, List<List<Object>> records) throws SQLException {
901                boolean allowedLog = false;
902                if (!checkSqlAvailable(sql))
903                        return -1;
904
905                long begin = System.currentTimeMillis();
906                if (records != null) {
907                        allowedLog = writeSqlLog("batch", begin, sql, String.format("[batchSize=%s]", records.size()));
908                }
909
910                boolean first = true;
911                PreparedStatement statement = null;
912                try {
913                        for (List<Object> params : records) {
914                                if (first) {
915                                        statement = connection.prepareStatement(sql);
916                                        first = false;
917                                }
918
919                                int size = params.size();
920                                for (int i = 0; i < size; i++) {
921                                        Object param = params.get(i);
922                                        if (param == null) {
923                                                statement.setNull(i + 1, Types.VARCHAR);
924                                        } else {
925                                                statement.setObject(i + 1, param);
926                                        }
927                                }
928
929                                statement.addBatch();
930                        }
931
932                        int sumRow = 0;
933                        if (records.size() > 0) {
934                                int[] rows = statement.executeBatch();
935
936                                for (int r : rows) {
937                                        sumRow += r;
938                                }
939
940                                if (allowedLog) {
941                                        long spend = System.currentTimeMillis() - begin;
942                                        writeSqlLog("return", begin, "return", sumRow);
943                                        writeSqlLog("spend", begin, "spend", spend);
944                                }
945
946                                executeBatchListSync(connection, begin, sql, records, sumRow);
947                        }
948
949                        return sumRow;
950                } catch (SQLException e) {
951                        if (allowedLog)
952                                writeSqlLog("error", begin, "error", String.format("%s,%s", e.getErrorCode(), e.getMessage()));
953
954                        throw e;
955                } finally {
956                        if (statement != null)
957                                statement.close();
958                }
959        }
960
961        /**
962         * Check connection is closed
963         * @return boolean
964         * */
965        public boolean isClosed() {
966                try {
967                    if(closed) return closed;
968                    
969                        return (connection == null || connection.isClosed());
970                } catch (Exception e) {
971                        log.warn(e);
972                        return true;
973                }
974        }
975
976        /**
977         * Abort connection
978         * @exception SQLException
979         * */
980        public void abort() throws SQLException {
981                writeSqlLog("aborting", 0, "aborting", "");
982                if (connection != null) {
983                        try {
984                                if (connection instanceof DriverConnection) {
985                                        abortSyncConnection(connection);
986                                        connection.abort(null);
987                                } else {
988                                        connection.abort(Executors.newFixedThreadPool(1));
989                                }
990                                closed = true;
991                        } catch (SQLException e) {
992                                writeSqlLog("error", 0, "error", String.format("%s,%s", e.getErrorCode(), e.getMessage()));
993                                throw e;
994                        }
995                }
996                writeSqlLog("aborted", 0, "aborted", "");
997        }
998
999        /**
1000         * Close connection
1001         * @exception SQLException
1002         * */
1003        public void close() throws SQLException {
1004                writeSqlLog("closing", 0, "closing", "");
1005                if (connection != null) {
1006                        try {
1007                                closeSyncConnection(connection);
1008                                connection.close();
1009                                closed = true;
1010                        } catch (SQLException e) {
1011                                writeSqlLog("error", 0, "error", String.format("%s,%s", e.getErrorCode(), e.getMessage()));
1012                                throw e;
1013                        }
1014                }
1015                writeSqlLog("closed", 0, "closed", "");
1016        }
1017
1018        /**
1019         * Commit connection
1020         * @exception SQLException
1021         * */
1022        public void commit() throws SQLException {
1023                writeSqlLog("committing", 0, "committing", "");
1024                if (connection != null) {
1025                        try {
1026                                commitSyncConnection(connection);
1027                                connection.commit();
1028                        } catch (SQLException e) {
1029                                writeSqlLog("error", 0, "", String.format("%s,%s", e.getErrorCode(), e.getMessage()));
1030                                throw e;
1031                        }
1032                }
1033                writeSqlLog("committed", 0, "committed", "");
1034        }
1035
1036        /**
1037         * Rollback connection
1038         * @exception SQLException
1039         * */
1040        public void rollback() throws SQLException {
1041                writeSqlLog("rollbacking", 0, "rollbacking", "");
1042                if (connection != null) {
1043                        try {
1044                                rollbackSyncConnection(connection);
1045                                connection.rollback();
1046                        } catch (SQLException e) {
1047                                writeSqlLog("error", 0, "error", String.format("%s,%s", e.getErrorCode(), e.getMessage()));
1048                                throw e;
1049                        }
1050                }
1051                writeSqlLog("rollbacked", 0, "rollbacked", "");
1052        }
1053
1054        /**
1055         * Get origin connection hash code
1056         * @return Integer - hash code
1057         * */
1058        private Integer getOriginConnectionHashCode() {
1059                if (connection instanceof DriverConnection) {
1060                        return ((DriverConnection) connection).getOriginConnectionHashCode();
1061                } else {
1062                        return connection.hashCode();
1063                }
1064        }
1065
1066        /**
1067         * Get data source thread name
1068         * @return String - DataSource thread name
1069         * */
1070        private String getDataSourceName() {
1071                if (connection instanceof DriverConnection) {
1072                        return ((DriverConnection) connection).getDriverDataSource().getName();
1073                } else {
1074                        return null;
1075                }
1076        }
1077
1078        /**
1079         * Conver sql from ':column_name' to '?'
1080         * @return String
1081         * */
1082        protected String converSql(String sql) {
1083                return sql.replaceAll(":[a-zA-Z0-9_]+", "?");
1084        }
1085
1086        /**
1087         * Conver param from map to list
1088         * @param sql
1089         * @param map
1090         * @return List<Object>
1091         * */
1092        protected List<Object> converParams(String sql, Map<String, Object> map) {
1093                List<String> paramKeys = new ArrayList<String>();
1094                Pattern pattern = Pattern.compile(":[a-zA-Z0-9_]+");
1095                Matcher matcher = pattern.matcher(sql);
1096                while (matcher.find()) {
1097                        String key = matcher.group().replaceFirst(":", "");
1098                        paramKeys.add(key);
1099                }
1100
1101                List<Object> params = new ArrayList<Object>();
1102                for (String pk : paramKeys) {
1103                        Object pv = map.get(pk);
1104                        if (pv == null) {
1105                                pv = map.get(pk.toLowerCase());
1106                        }
1107                        if (pv == null) {
1108                                pv = map.get(pk.toUpperCase());
1109                        }
1110                        if (pv instanceof java.util.Date) {
1111                                java.util.Date utilDate = (java.util.Date) pv;
1112                                java.sql.Timestamp sqlDate = new java.sql.Timestamp(utilDate.getTime());
1113                                params.add(sqlDate);
1114                        } else {
1115                                params.add(pv);
1116                        }
1117                }
1118
1119                return params;
1120        }
1121
1122        /**
1123         * Covner to upper or lower
1124         * @param s - Column name or table name
1125         * @return String
1126         * */
1127        protected String converCase(String s) {
1128                if (COLUMN_NAME_CASE_MODE.equals(COLUMN_NAME_CASE_UPPER)) {
1129                        return s.toUpperCase();
1130                } else if (COLUMN_NAME_CASE_MODE.equals(COLUMN_NAME_CASE_LOWER)) {
1131                        return s.toLowerCase();
1132                } else {
1133                        return s;
1134                }
1135        }
1136
1137        /**
1138         * Check available sql to write to log
1139         * @return boolean
1140         * @param connection - Connection
1141         * @param sql
1142         * */
1143        protected static boolean checkSqlLogAvailable(Connection connection, String sql) {
1144                if (connection instanceof DriverConnection) {
1145                        DriverConnection dc = (DriverConnection) connection;
1146                        DriverDataSource dds = dc.getDriverDataSource();
1147                        ConfigProperties configProperties = dds.getConfigProperties();
1148                        List<String> sqlAllowed = configProperties.getArray("SqlLogAllowed");
1149                        List<String> sqlIgnored = configProperties.getArray("SqlLogIgnored");
1150                        return checkSqlAvailable(sql, sqlAllowed, sqlIgnored);
1151                }
1152                return false;
1153        }
1154
1155        /**
1156         * Check available sql to execute
1157         * @return boolean
1158         * @param sql
1159         * */
1160        protected boolean checkSqlAvailable(String sql) {
1161                return checkSqlAvailable(connection, sql);
1162        }
1163
1164        /**
1165         * Check available sql to execute
1166         * @return boolean
1167         * @param connection
1168         * @param sql
1169         * */
1170        protected static boolean checkSqlAvailable(Connection connection, String sql) {
1171                if (connection instanceof DriverConnection) {
1172                        DriverConnection dc = (DriverConnection) connection;
1173                        DriverDataSource dds = dc.getDriverDataSource();
1174                        ConfigProperties configProperties = dds.getConfigProperties();
1175                        List<String> sqlAllowed = configProperties.getArray("SqlExecuteAllowed");
1176                        List<String> sqlIgnored = configProperties.getArray("SqlExecuteIgnored");
1177                        return checkSqlAvailable(sql, sqlAllowed, sqlIgnored);
1178                }
1179                return true;
1180        }
1181
1182        /**
1183         * Check available sql to execute
1184         * @return boolean
1185         * @param sql
1186         * @param sqlAllowed - From DataSources.properties
1187         * @param sqlIgnored - From DataSources.properties
1188         * */
1189        private static boolean checkSqlAvailable(String sql, List<String> sqlAllowed, List<String> sqlIgnored) {
1190                boolean matchedAllowed = true;
1191                boolean matchedIgnored = false;
1192                if (sqlAllowed != null) {
1193                        for (String regex : sqlAllowed) {
1194                                if (!CommonTools.isBlank(regex)) {
1195                                        matchedAllowed = sql.matches(regex);
1196
1197                                        if (matchedAllowed)
1198                                                break;
1199                                }
1200                        }
1201                }
1202                if (matchedAllowed && sqlIgnored != null) {
1203                        for (String regex : sqlIgnored) {
1204                                if (!CommonTools.isBlank(regex)) {
1205                                        matchedIgnored = sql.matches(regex);
1206
1207                                        if (matchedIgnored)
1208                                                break;
1209                                }
1210                        }
1211                }
1212
1213                boolean b = matchedAllowed && !matchedIgnored;
1214                if (!b) {
1215                        LoggerFactory.getLogger(DriverExecutor.class).debug("Not available - '{}'", sql);
1216                }
1217                return b;
1218        }
1219
1220        /**
1221         * Write sql log
1222         * @return boolean 
1223         * @param type
1224         * @param seq
1225         * @param sql
1226         * @param params
1227         * */
1228        protected synchronized boolean writeSqlLog(String type, long seq, String sql, Object params) {
1229                return writeSqlLog(this.hashCode(),connection, type, seq, sql, params);
1230        }
1231
1232        /**
1233         * Write sql log
1234         * @return boolean 
1235         * @param connection
1236         * @param type
1237         * @param seq
1238         * @param sql
1239         * @param params
1240         * */
1241        protected synchronized static boolean writeSqlLog(int deHashCode,Connection connection, String type, long seq, String sql,
1242                        Object params) {
1243                if (connection instanceof DriverConnection) {
1244                        if (!checkSqlLogAvailable(connection, sql))
1245                                return false;
1246
1247                        final String header = String.format(
1248                                        "LOG_DATE,LOG_HOUR,LOG_MI,LOG_SEC,LOG_MS,LOG_HOST,LOG_THREAD,LOG_DS,LOG_CONN_ID,LOG_EXEC_ID,LOG_TYPE,LOG_SEQ,LOG_SQL,LOG_PARAMS%s",
1249                                        System.lineSeparator());
1250                        final DateFormat df = new SimpleDateFormat("yyyyMMdd");
1251                        final DateFormat dtf = new SimpleDateFormat("yyyyMMdd,HH,mm,ss,SSS");
1252                        DriverConnection dc = (DriverConnection) connection;
1253                        Integer connHashCode = dc.getOriginConnectionHashCode();
1254                        DriverDataSource dds = dc.getDriverDataSource();
1255                        ConfigProperties configProperties = dds.getConfigProperties();
1256
1257                        boolean logEnable = configProperties.getBoolean("SqlLogEnable", false);
1258                        long overspend = configProperties.getMilliSeconds("SqlLogOverspend",0L);
1259
1260                        if (!logEnable)
1261                                return false;
1262
1263                        final String defaultLogFolderPath = String.format("%s/SqlLog/",
1264                                        CommonTools.getJarPath(DriverExecutor.class));
1265                        final String logFolderPath = configProperties.getString("SqlLogFolder", defaultLogFolderPath);
1266                        final long maxFileSize = configProperties.getFileSize("SqlLogMaxFileSize", 1024 * 1024 * 10L);
1267                        final int archiveDays = configProperties.getInteger("SqlLogArchiveDays", 31);
1268                        final int logParamMaxLength = configProperties.getInteger("SqlLogParamMaxLength", 20);
1269
1270                        if (sqlLogCacheArray == null) {
1271
1272                                sqlLogCacheArray = new CacheArray();
1273                                long sqlLogFilterTimer = configProperties.getLong("SqlLogTimer", 1000L);
1274                                sqlLogCacheArray.filter(new CacheArrayFilter(0L,sqlLogFilterTimer) {
1275                                        @Override
1276                                        public void executeBatch(Integer index, List batch) {
1277                                            final StringBuffer sbf = new StringBuffer();
1278                                            for(Object item : batch){
1279                                                sbf.append(item);
1280                                            }
1281                                                try {
1282                                                        String msg = sbf.toString();
1283                                                        String dateStr = df.format(new Timestamp(System.currentTimeMillis()));
1284                                                        File sqlLogFile = new File(String.format("%s/%s.csv", logFolderPath, dateStr));
1285                                                        File sqlLogFolder = new File(sqlLogFile.getParent());
1286
1287                                                        if (sqlLogFolder.exists()) {
1288                                                                if (!sqlLogFolder.canWrite())
1289                                                                        throw new IOException(String.format("Can not write to log folder '%s'",
1290                                                                                        sqlLogFolder.getAbsolutePath()));
1291                                                        } else {
1292                                                                sqlLogFolder.mkdirs();
1293                                                        }
1294
1295                                                        if (sqlLogFile.exists()) {
1296                                                                if (!sqlLogFile.canWrite())
1297                                                                        throw new IOException(String.format("Can not write to log file '%s'",
1298                                                                                        sqlLogFile.getAbsolutePath()));
1299                                                        }
1300
1301                                                        int suffixIndex = sqlLogFile.getName().lastIndexOf(".");
1302                                                        String logFileNamePrefix = sqlLogFile.getName().substring(0, suffixIndex);
1303                                                        long logSize = FileTools.size(sqlLogFile);
1304                                                        if (!sqlLogFile.exists()) {
1305                                                                FileTools.write(sqlLogFile, header, false);
1306                                                        }
1307                                                        if (logSize < maxFileSize) {
1308                                                                FileTools.write(sqlLogFile, msg, true);
1309                                                        } else {
1310                                                                int logIndex = getLogFileIndex(configProperties, sqlLogFolder, logFileNamePrefix,
1311                                                                                "csv");
1312                                                                if (logIndex == 0) {
1313                                                                        FileTools.write(sqlLogFile, String.format("%s%s", header, msg), false);
1314                                                                } else {
1315                                                                        backupLog(sqlLogFolder, sqlLogFile, logFileNamePrefix, logIndex, header, msg);
1316                                                                }
1317                                                        }
1318                                                        archiveLog(archiveDays, sqlLogFolder);
1319                                                } catch (Exception e) {
1320                                                        LoggerFactory.getLogger(DriverExecutor.class).warn(e.getMessage(), e);
1321                                                }
1322                                        }
1323                                });
1324                        }
1325
1326                        if (logEnable) {
1327                                String hostname = CommonTools.getHostname();
1328                                String dateTimeStr = dtf.format(new Timestamp(System.currentTimeMillis()));
1329                                boolean isOverspend = false;
1330                                boolean isSpendSeq = sql.equals("spend");
1331                                if(isSpendSeq && params != null){
1332                                    long spendValue = Long.parseLong(params + "");
1333                                    isOverspend = (spendValue >= overspend);
1334                                }
1335
1336                String threadId = Thread.currentThread().getName() + "-" + Thread.currentThread().getId();
1337                                String msg = String.format("%s,%s,%s,%s,%s,%s,%s,%s,\"%s\",\"%s\"%s", dateTimeStr, hostname,
1338                                                    threadId, dds.getName(), connHashCode,deHashCode, type, seq,
1339                                                    replaceToSigleLine(sql), handleParams(logParamMaxLength, params), System.lineSeparator());  
1340                                                    
1341                                String key = String.format("%s_%s",deHashCode,connHashCode);
1342                                if(seq <= 0 || overspend <= 0){
1343                                    sqlLogCacheArray.add(msg);
1344                                    SQL_LOG_MSG_MAPPING.remove(key);
1345                                SQL_LOG_OVERSPEND_MAPPING.remove(key);                              
1346                                }
1347                                if(seq > 0 && overspend > 0){
1348                                SQL_LOG_OVERSPEND_MAPPING.put(key,isOverspend);
1349                                String existsMsg = SQL_LOG_MSG_MAPPING.get(key);
1350                                existsMsg = existsMsg == null ? msg : (existsMsg + msg);
1351                                SQL_LOG_MSG_MAPPING.put(key, existsMsg);
1352                                
1353                                boolean seqOverspend = SQL_LOG_OVERSPEND_MAPPING.get(key);
1354                                if(isSpendSeq && seqOverspend){
1355                                    sqlLogCacheArray.add(SQL_LOG_MSG_MAPPING.get(key) + "");
1356                                    SQL_LOG_MSG_MAPPING.remove(key);
1357                                    SQL_LOG_OVERSPEND_MAPPING.remove(key);
1358                                }
1359                                if(isSpendSeq && !seqOverspend){
1360                                    SQL_LOG_MSG_MAPPING.remove(key);
1361                                    SQL_LOG_OVERSPEND_MAPPING.remove(key);
1362                                }
1363                                }
1364                                return true;
1365                        }
1366
1367                }
1368                return false;
1369        }
1370
1371        /**
1372         * Backup log file
1373         * */
1374        private static void backupLog(File logFolder, File logFile, String logFileNamePrefix, int logIndex, String header,
1375                        String msg) throws Exception {
1376                String backupLogFileName = String.format("%s/%s.%s.csv", logFolder.getAbsolutePath(), logFileNamePrefix,
1377                                logIndex);
1378                Path source = Paths.get(logFile.getAbsolutePath());
1379                Path target = Paths.get(backupLogFileName);
1380                Files.move(source, target, StandardCopyOption.REPLACE_EXISTING);
1381                FileTools.write(logFile, String.format("%s%s", header, msg), false);
1382        }
1383
1384        /**
1385         * Get log file index
1386         * For backup log file use
1387         * */
1388        private static int getLogFileIndex(ConfigProperties configProperties, File folder, String prefix, String suffix) {
1389                Integer maxBackupIndex = configProperties.getInteger("SqlLogMaxBackupIndex", 10);
1390                for (int i = 1; i <= maxBackupIndex; i++) {
1391                        String logFileName = String.format("%s/%s.%s.%s", folder.getAbsolutePath(), prefix, i, suffix);
1392                        File logFile = new File(logFileName);
1393                        if (!logFile.exists())
1394                                return i;
1395                }
1396                return 0;
1397        }
1398
1399        /**
1400         * Archive Log
1401         * */
1402        private static void archiveLog(int archiveDays, File sqlLogFolder) {
1403                if (archiveDays > 0) {
1404                        try {
1405                                long archiveDaysMs = new Date().getTime() - (archiveDays * 24 * 3600000L);
1406                                deleteFilesOlderThan(sqlLogFolder, archiveDaysMs);
1407                        } catch (Exception e) {
1408                                LoggerFactory.getLogger(DriverExecutor.class).warn(e);
1409                        }
1410                }
1411        }
1412
1413        /**
1414         * Delete old archive logs
1415         * */
1416        private static void deleteFilesOlderThan(File directory, long archiveDaysMs) throws IOException {
1417                if (directory.isDirectory()) {
1418                        File[] files = directory.listFiles();
1419                        if (files != null) {
1420                                for (File file : files) {
1421                                        if (file.isFile()) {
1422                                                boolean isLogFile = file.getName().toLowerCase().endsWith(".csv");
1423                                                if (isLogFile) {
1424                                                        boolean canWrite = file.canWrite();
1425                                                        if (canWrite) {
1426                                                                long lastModified = file.lastModified();
1427                                                                if (lastModified < archiveDaysMs) {
1428                                                                        Files.deleteIfExists(Paths.get(file.toURI()));
1429                                                                }
1430                                                        }
1431                                                }
1432                                        }
1433                                }
1434                        }
1435                }
1436        }
1437
1438        /**
1439         * Replace to sigle line
1440         * For write csv log
1441         * */
1442        private static String replaceToSigleLine(String msg) {
1443                return CodeEscape.escapeToSingleLineForCsv(msg);
1444        }
1445
1446        /**
1447         * Hahdle Params
1448         * For write csv log
1449         * */
1450        private static String handleParams(int paramMaxLength, Object params) {
1451
1452                if (params == null)
1453                        return "null";
1454
1455                StringBuffer paramSbf = new StringBuffer("");
1456                if (params instanceof List) {
1457                        List<Object> listParams = (List<Object>) params;
1458                        int size = listParams.size();
1459                        for (int i = 0; i < size; i++) {
1460                                Object param = listParams.get(i);
1461                                String str = param + "";
1462                                if (str.length() > paramMaxLength) {
1463                                        paramSbf.append(str.substring(0, paramMaxLength) + "...");
1464                                } else {
1465                                        paramSbf.append(str);
1466                                }
1467                                if (i < size - 1) {
1468                                        paramSbf.append(";");
1469                                }
1470                        }
1471                } else {
1472                        paramSbf.append(params);
1473                }
1474                return replaceToSigleLine(paramSbf.toString());
1475        }
1476
1477        /**For Sync DataSource**/
1478
1479        /**
1480         * For database replication
1481         * */
1482        protected static void callAndReturnBooleanSync(Connection masterConn, long seq, String sql, List<Object> params,
1483                        boolean returnResult) throws SQLException {
1484                if (masterConn instanceof DriverConnection) {
1485                        openSyncConnection(masterConn);
1486
1487                        DriverConnection masterDriverConn = (DriverConnection) masterConn;
1488                        Integer masterConnHashCode = masterDriverConn.getOriginConnectionHashCode();
1489                        ConfigProperties ddscp = masterDriverConn.getDriverDataSource().getConfigProperties();
1490                        boolean connCheck = true;
1491                        boolean returnCheck = true;
1492                        if (ddscp != null) {
1493                                connCheck = ddscp.getBoolean("SyncConnectionCheck", true);
1494                                returnCheck = ddscp.getBoolean("SyncReturnCheck", true);
1495                        }
1496                        List<DriverExecutor> deList = SYNC_EXECUTOR_MARK.get(masterConnHashCode);
1497                        if (deList != null) {
1498                                for (DriverExecutor de : deList) {
1499                                        try {
1500                                                boolean resultSync = de.callAndReturnBoolean(sql, params);
1501                                                if (returnCheck) {
1502                                                        if (resultSync != returnResult) {
1503                                                                writeSqlLog(de.hashCode(),masterDriverConn, "diffed", seq, "diffed", String.format("%s-%s",
1504                                                                                de.getDataSourceName(), de.getOriginConnectionHashCode()));
1505                                                                int errorCode = 99906;
1506                                                                String errorMsg = String.format("The returned results are inconsistent '%s-%s'.",
1507                                                                                de.getDataSourceName(), de.getOriginConnectionHashCode());
1508                                                                throw new SQLException(errorMsg) {
1509                                                                        @Override
1510                                                                        public int getErrorCode() {
1511                                                                                return errorCode;
1512                                                                        }
1513                                                                };
1514                                                        }
1515                                                }
1516                                        } catch (SQLException e) {
1517                                                LoggerFactory.getLogger(DriverExecutor.class)
1518                                                                .warn(String.format("ERROR CODE: (%s) %s", e.getErrorCode(), e.getMessage()));
1519                                                if (connCheck)
1520                                                        throw e;
1521                                        }
1522                                }
1523                        }
1524                }
1525        }
1526
1527        /**
1528         * For database replication
1529         * */
1530        protected static void callAndReturnListSync(Connection masterConn, int cursorStart, int maxRows, String sql,
1531                        List<Object> params) throws SQLException {
1532                if (masterConn instanceof DriverConnection) {
1533                        openSyncConnection(masterConn);
1534
1535                        DriverConnection masterDriverConn = (DriverConnection) masterConn;
1536                        Integer masterConnHashCode = masterDriverConn.getOriginConnectionHashCode();
1537
1538                        List<DriverExecutor> deList = SYNC_EXECUTOR_MARK.get(masterConnHashCode);
1539                        if (deList != null) {
1540                                for (DriverExecutor de : deList) {
1541                                        try {
1542                                                de.callAndReturnBoolean(sql, params);
1543                                        } catch (SQLException e) {
1544                                                LoggerFactory.getLogger(DriverExecutor.class)
1545                                                                .warn(String.format("ERROR CODE: (%s) %s", e.getErrorCode(), e.getMessage()));
1546                                                ConfigProperties ddscp = masterDriverConn.getDriverDataSource().getConfigProperties();
1547                                                boolean connCheck = true;
1548                                                if (ddscp != null) {
1549                                                        connCheck = ddscp.getBoolean("SyncConnectionCheck", true);
1550                                                }
1551                                                if (connCheck)
1552                                                        throw e;
1553                                        }
1554                                }
1555                        }
1556                }
1557        }
1558
1559        /**
1560         * For database replication
1561         * */
1562        protected static void callAndReturnRowsSync(Connection masterConn, long seq, String sql, List<Object> params,
1563                        int returnRows) throws SQLException {
1564                if (masterConn instanceof DriverConnection) {
1565                        openSyncConnection(masterConn);
1566
1567                        DriverConnection masterDriverConn = (DriverConnection) masterConn;
1568                        Integer masterConnHashCode = masterDriverConn.getOriginConnectionHashCode();
1569                        ConfigProperties ddscp = masterDriverConn.getDriverDataSource().getConfigProperties();
1570                        boolean connCheck = true;
1571                        boolean returnCheck = true;
1572                        if (ddscp != null) {
1573                                connCheck = ddscp.getBoolean("SyncConnectionCheck", true);
1574                                returnCheck = ddscp.getBoolean("SyncReturnCheck", true);
1575                        }
1576                        List<DriverExecutor> deList = SYNC_EXECUTOR_MARK.get(masterConnHashCode);
1577                        if (deList != null) {
1578                                for (DriverExecutor de : deList) {
1579                                        try {
1580                                                int rowSync = de.callAndReturnRows(sql, params);
1581                                                if (returnCheck) {
1582                                                        if (rowSync != returnRows) {
1583                                                                writeSqlLog(de.hashCode(),masterDriverConn, "diffed", seq, "diffed", String.format("%s-%s",
1584                                                                                de.getDataSourceName(), de.getOriginConnectionHashCode()));
1585                                                                int errorCode = 99906;
1586                                                                String errorMsg = String.format("The returned results are inconsistent '%s-%s'.",
1587                                                                                de.getDataSourceName(), de.getOriginConnectionHashCode());
1588                                                                throw new SQLException(errorMsg) {
1589                                                                        @Override
1590                                                                        public int getErrorCode() {
1591                                                                                return errorCode;
1592                                                                        }
1593                                                                };
1594                                                        }
1595                                                }
1596                                        } catch (SQLException e) {
1597                                                LoggerFactory.getLogger(DriverExecutor.class)
1598                                                                .warn(String.format("ERROR CODE: (%s) %s", e.getErrorCode(), e.getMessage()));
1599                                                if (connCheck)
1600                                                        throw e;
1601                                        }
1602                                }
1603                        }
1604                }
1605        }
1606
1607        /**
1608         * For database replication
1609         * */
1610        protected static void executeBatchListSync(Connection masterConn, long seq, String sql, List<List<Object>> records,
1611                        int returnRows) throws SQLException {
1612                if (masterConn instanceof DriverConnection) {
1613                        openSyncConnection(masterConn);
1614
1615                        DriverConnection masterDriverConn = (DriverConnection) masterConn;
1616                        Integer masterConnHashCode = masterDriverConn.getOriginConnectionHashCode();
1617                        ConfigProperties ddscp = masterDriverConn.getDriverDataSource().getConfigProperties();
1618                        boolean connCheck = true;
1619                        boolean returnCheck = true;
1620                        if (ddscp != null) {
1621                                connCheck = ddscp.getBoolean("SyncConnectionCheck", true);
1622                                returnCheck = ddscp.getBoolean("SyncReturnCheck", true);
1623                        }
1624                        List<DriverExecutor> deList = SYNC_EXECUTOR_MARK.get(masterConnHashCode);
1625                        if (deList != null) {
1626                                for (DriverExecutor de : deList) {
1627                                        try {
1628                                                int rowSync = de.executeBatchList(sql, records);
1629                                                if (returnCheck) {
1630                                                        if (rowSync != returnRows) {
1631                                                                writeSqlLog(de.hashCode(),masterDriverConn, "diffed", seq, "diffed", String.format("%s-%s",
1632                                                                                de.getDataSourceName(), de.getOriginConnectionHashCode()));
1633                                                                int errorCode = 99906;
1634                                                                String errorMsg = String.format("The returned results are inconsistent '%s-%s'.",
1635                                                                                de.getDataSourceName(), de.getOriginConnectionHashCode());
1636                                                                throw new SQLException(errorMsg) {
1637                                                                        @Override
1638                                                                        public int getErrorCode() {
1639                                                                                return errorCode;
1640                                                                        }
1641                                                                };
1642                                                        }
1643                                                }
1644                                        } catch (SQLException e) {
1645                                                LoggerFactory.getLogger(DriverExecutor.class)
1646                                                                .warn(String.format("ERROR CODE: (%s) %s", e.getErrorCode(), e.getMessage()));
1647                                                if (connCheck)
1648                                                        throw e;
1649                                        }
1650                                }
1651                        }
1652                }
1653        }
1654
1655        /**
1656         * For database replication
1657         * */
1658        protected static void executeSync(Connection masterConn, long seq, String sql, List<Object> params, int returnRows)
1659                        throws SQLException {
1660                if (masterConn instanceof DriverConnection) {
1661                        openSyncConnection(masterConn);
1662
1663                        DriverConnection masterDriverConn = (DriverConnection) masterConn;
1664                        Integer masterConnHashCode = masterDriverConn.getOriginConnectionHashCode();
1665                        ConfigProperties ddscp = masterDriverConn.getDriverDataSource().getConfigProperties();
1666                        boolean connCheck = true;
1667                        boolean returnCheck = true;
1668                        if (ddscp != null) {
1669                                connCheck = ddscp.getBoolean("SyncConnectionCheck", true);
1670                                returnCheck = ddscp.getBoolean("SyncReturnCheck", true);
1671                        }
1672                        List<DriverExecutor> deList = SYNC_EXECUTOR_MARK.get(masterConnHashCode);
1673                        if (deList != null) {
1674                                for (DriverExecutor de : deList) {
1675                                        try {
1676                                                int rowSync = de.execute(sql, params);
1677                                                if (returnCheck) {
1678                                                        if (rowSync != returnRows) {
1679                                                                writeSqlLog(de.hashCode(),masterDriverConn, "diffed", seq, "diffed", String.format("%s-%s",
1680                                                                                de.getDataSourceName(), de.getOriginConnectionHashCode()));
1681                                                                int errorCode = 99906;
1682                                                                String errorMsg = String.format("The returned results are inconsistent '%s-%s'.",
1683                                                                                de.getDataSourceName(), de.getOriginConnectionHashCode());
1684                                                                throw new SQLException(errorMsg) {
1685                                                                        @Override
1686                                                                        public int getErrorCode() {
1687                                                                                return errorCode;
1688                                                                        }
1689                                                                };
1690                                                        }
1691                                                }
1692                                        } catch (SQLException e) {
1693                                                LoggerFactory.getLogger(DriverExecutor.class)
1694                                                                .warn(String.format("ERROR CODE: (%s) %s", e.getErrorCode(), e.getMessage()));
1695                                                if (connCheck)
1696                                                        throw e;
1697                                        }
1698                                }
1699                        }
1700                }
1701        }
1702
1703        /**
1704         * For database replication
1705         * */
1706        protected synchronized static boolean openSyncConnection(Connection masterConn) throws SQLException {
1707                if (masterConn instanceof DriverConnection) {
1708
1709                        DriverConnection masterDriverConn = (DriverConnection) masterConn;
1710                        Integer masterConnHashCode = masterDriverConn.getOriginConnectionHashCode();
1711
1712                        boolean isExist = SYNC_EXECUTOR_MARK.containsKey(masterConnHashCode);
1713
1714                        if (isExist) {
1715                                return false;
1716                        } else {
1717                                SYNC_EXECUTOR_MARK.put(masterConnHashCode, new ArrayList<DriverExecutor>());
1718                        }
1719
1720                        DriverDataSource dds = masterDriverConn.getDriverDataSource();
1721                        if (dds != null) {
1722                                List<DriverDataSource> sdsl = dds.getSyncDataSourceList(dds.getName());
1723                                if (sdsl != null) {
1724
1725                                        if (sdsl.isEmpty())
1726                                                return false;
1727
1728                                        for (DriverDataSource sds : sdsl) {
1729
1730                                                Integer sdsHashCode = sds.hashCode();
1731                                                if (!SYNC_CONN_ERROR_TIME.containsKey(sdsHashCode)) {
1732                                                        SYNC_CONN_ERROR_TIME.put(sdsHashCode, 0L);
1733                                                }
1734
1735                                                Long syncConnErrorTime = SYNC_CONN_ERROR_TIME.get(sdsHashCode);
1736                                                if (syncConnErrorTime > 0) {
1737                                                        ConfigProperties ddscp = dds.getConfigProperties();
1738                                                        long connCheckMs = 10000L;
1739                                                        if (ddscp != null) {
1740                                                                connCheckMs = ddscp.getLong("SyncConnectionCheckTime", 10000L);
1741                                                        }
1742                                                        boolean isSkipConn = syncConnErrorTime > 0
1743                                                                        && (System.currentTimeMillis() - syncConnErrorTime) <= connCheckMs;
1744                                                        if (isSkipConn) {
1745                                                                continue;
1746                                                        }
1747                                                }
1748
1749                                                String masterFingerprint = dds.getFingerprint();
1750                                                String syncFingerprint = sds.getFingerprint();
1751                                                if (masterFingerprint.equalsIgnoreCase(syncFingerprint)) {
1752                                                        LoggerFactory.getLogger(DriverExecutor.class)
1753                                                                        .warn("Skip sync reason 'same connection fingerprint'.");
1754                                                        continue;
1755                                                }
1756
1757                                                try {
1758                                                        final Connection conn = sds.getConnection();
1759                                                        if (conn == null) {
1760                                                                int errorCode = 99904;
1761                                                                String errorMsg = "Connection is null.";
1762                                                                throw new SQLException(errorMsg) {
1763                                                                        @Override
1764                                                                        public int getErrorCode() {
1765                                                                                return errorCode;
1766                                                                        }
1767                                                                };
1768                                                        }
1769                                                        conn.setAutoCommit(masterConn.getAutoCommit());
1770                                                        SYNC_CONN_ERROR_TIME.put(sdsHashCode, 0L);
1771                                                        SYNC_EXECUTOR_MARK.get(masterConnHashCode).add(new DriverExecutor(conn));
1772                                                } catch (SQLException e) {
1773                                                        SYNC_CONN_ERROR_TIME.put(sdsHashCode, System.currentTimeMillis());
1774                                                        LoggerFactory.getLogger(DriverExecutor.class)
1775                                                                        .warn(String.format("ERROR CODE: (%s) %s", e.getErrorCode(), e.getMessage()));
1776                                                        ConfigProperties ddscp = dds.getConfigProperties();
1777                                                        boolean connCheck = true;
1778                                                        if (ddscp != null) {
1779                                                                connCheck = ddscp.getBoolean("SyncConnectionCheck", true);
1780                                                        }
1781                                                        if (connCheck)
1782                                                                throw e;
1783                                                }
1784
1785                                        }
1786
1787                                        return true;
1788                                }
1789                        }
1790                }
1791                return false;
1792        }
1793
1794        /**
1795         * For database replication
1796         * */
1797        protected static void closeSyncConnection(Connection masterConn) throws SQLException {
1798                if (masterConn instanceof DriverConnection) {
1799                        DriverConnection masterDriverConn = (DriverConnection) masterConn;
1800                        Integer masterConnHashCode = masterDriverConn.getOriginConnectionHashCode();
1801
1802                        List<DriverExecutor> deList = SYNC_EXECUTOR_MARK.get(masterConnHashCode);
1803                        if (deList != null) {
1804                                for (DriverExecutor de : deList) {
1805                                        try {
1806                                                de.close();
1807                                        } catch (SQLException e) {
1808                                                LoggerFactory.getLogger(DriverExecutor.class)
1809                                                                .warn(String.format("ERROR CODE: (%s) %s", e.getErrorCode(), e.getMessage()));
1810                                                ConfigProperties ddscp = masterDriverConn.getDriverDataSource().getConfigProperties();
1811                                                boolean connCheck = true;
1812                                                if (ddscp != null) {
1813                                                        connCheck = ddscp.getBoolean("SyncConnectionCheck", true);
1814                                                }
1815                                                if (connCheck)
1816                                                        throw e;
1817                                        }
1818                                }
1819                                SYNC_EXECUTOR_MARK.remove(masterConnHashCode);
1820                                LoggerFactory.getLogger(DriverExecutor.class).debug("CloseSyncConnection - masterConn '{}' finished.",
1821                                                masterConnHashCode);
1822                        }
1823                }
1824        }
1825
1826        /**
1827         * For database replication
1828         * */
1829        protected static void commitSyncConnection(Connection masterConn) throws SQLException {
1830                if (masterConn instanceof DriverConnection) {
1831                        DriverConnection masterDriverConn = (DriverConnection) masterConn;
1832                        Integer masterConnHashCode = masterDriverConn.getOriginConnectionHashCode();
1833
1834                        List<DriverExecutor> deList = SYNC_EXECUTOR_MARK.get(masterConnHashCode);
1835                        if (deList != null) {
1836                                for (DriverExecutor de : deList) {
1837                                        try {
1838                                                de.commit();
1839                                        } catch (SQLException e) {
1840                                                LoggerFactory.getLogger(DriverExecutor.class)
1841                                                                .warn(String.format("ERROR CODE: (%s) %s", e.getErrorCode(), e.getMessage()));
1842                                                ConfigProperties ddscp = masterDriverConn.getDriverDataSource().getConfigProperties();
1843                                                boolean connCheck = true;
1844                                                if (ddscp != null) {
1845                                                        connCheck = ddscp.getBoolean("SyncConnectionCheck", true);
1846                                                }
1847                                                if (connCheck)
1848                                                        throw e;
1849                                        }
1850                                }
1851                                LoggerFactory.getLogger(DriverExecutor.class).debug("CommitSyncConnection - masterConn '{}' finished.",
1852                                                masterConnHashCode);
1853                        }
1854                }
1855        }
1856
1857        /**
1858         * For database replication
1859         * */
1860        protected static void rollbackSyncConnection(Connection masterConn) throws SQLException {
1861                if (masterConn instanceof DriverConnection) {
1862                        DriverConnection masterDriverConn = (DriverConnection) masterConn;
1863                        Integer masterConnHashCode = masterDriverConn.getOriginConnectionHashCode();
1864
1865                        List<DriverExecutor> deList = SYNC_EXECUTOR_MARK.get(masterConnHashCode);
1866                        if (deList != null) {
1867                                for (DriverExecutor de : deList) {
1868                                        try {
1869                                                de.rollback();
1870                                        } catch (SQLException e) {
1871                                                LoggerFactory.getLogger(DriverExecutor.class)
1872                                                                .warn(String.format("ERROR CODE: (%s) %s", e.getErrorCode(), e.getMessage()));
1873                                                ConfigProperties ddscp = masterDriverConn.getDriverDataSource().getConfigProperties();
1874                                                boolean connCheck = true;
1875                                                if (ddscp != null) {
1876                                                        connCheck = ddscp.getBoolean("SyncConnectionCheck", true);
1877                                                }
1878                                                if (connCheck)
1879                                                        throw e;
1880                                        }
1881                                }
1882                                LoggerFactory.getLogger(DriverExecutor.class)
1883                                                .debug("RollbackSyncConnection - masterConn '{}' finished.", masterConnHashCode);
1884                        }
1885                }
1886        }
1887
1888        /**
1889         * For database replication
1890         * */
1891        protected static void abortSyncConnection(Connection masterConn) throws SQLException {
1892                if (masterConn instanceof DriverConnection) {
1893                        DriverConnection masterDriverConn = (DriverConnection) masterConn;
1894                        Integer masterConnHashCode = masterDriverConn.getOriginConnectionHashCode();
1895
1896                        List<DriverExecutor> deList = SYNC_EXECUTOR_MARK.get(masterConnHashCode);
1897                        if (deList != null) {
1898                                for (DriverExecutor de : deList) {
1899                                        try {
1900                                                de.abort();
1901                                        } catch (SQLException e) {
1902                                                LoggerFactory.getLogger(DriverExecutor.class)
1903                                                                .warn(String.format("ERROR CODE: (%s) %s", e.getErrorCode(), e.getMessage()));
1904                                                ConfigProperties ddscp = masterDriverConn.getDriverDataSource().getConfigProperties();
1905                                                boolean connCheck = true;
1906                                                if (ddscp != null) {
1907                                                        connCheck = ddscp.getBoolean("SyncConnectionCheck", true);
1908                                                }
1909                                                if (connCheck)
1910                                                        throw e;
1911                                        }
1912                                }
1913                                LoggerFactory.getLogger(DriverExecutor.class).debug("AbortSyncConnection - masterConn '{}' finished.",
1914                                                masterConnHashCode);
1915                        }
1916                }
1917        }
1918
1919}