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 com.killcoding.log.LoggerFactory;
041import com.killcoding.log.Logger;
042import com.killcoding.cache.CacheArray;
043import java.io.IOException;
044import java.sql.Blob;
045import java.sql.Clob;
046import java.io.InputStream;
047
048/**
049 * This class uses a 'CacheArray' to process the asynchronous processing method of the data query result set.
050 * If you choose to use the mode 'DiskCache', temporary files will be generated. 
051 * If you choose to use the mode 'MemoryCache', temporary files will not be generated but the physical memory size must be considered.
052 * */
053public final class CacheDriverExecutor extends DriverExecutor {
054
055        public static long READ_TIMER = 10L;
056
057        /**
058         * New a object CacheDriverExecutor
059         * @param connection - it is jdbc connection
060         * */
061        public CacheDriverExecutor(Connection connection) {
062                super(connection);
063        }
064
065        /**
066         * This is full table query function
067         * @param sql - Query sql 
068         * @param params - Query params 
069         * @param rows - CacheArray object
070         * @exception SQLException - if query failed
071         * */
072        public void find(String sql, List<Object> params, final CacheArray<Map<String, Object>> rows) throws SQLException {
073                find(0, 0, sql, params, rows);
074        }
075
076        /**
077         * This is full table query function
078         * @param sql - Query sql 
079         * @param params - Query params 
080         * @param rows - CacheArray object
081         * @exception SQLException - if query failed
082         * */
083        public void find(String sql, Map<String, Object> params, final CacheArray<Map<String, Object>> rows)
084                        throws SQLException {
085                String csql = converSql(sql);
086                List<Object> cparams = converParams(sql, params);
087                find(0, 0, csql, cparams, rows);
088        }
089
090        /**
091         * This is full table query function
092         * @param sql - Query sql 
093         * @param rows - CacheArray object
094         * @exception SQLException - if query failed
095         * */
096        public void find(String sql, final CacheArray<Map<String, Object>> rows) throws SQLException {
097                find(0, 0, sql, Arrays.asList(new Object[] {}), rows);
098        }
099
100        /**
101         * This is limited rows query function
102         * @param cursorStart - JDBC result Cursor start index
103         * @param maxRows - JDBC result max rows (JDBC limited rows 50,000,000)
104         * @param sql - Query sql 
105         * @param rows - CacheArray object
106         * @exception SQLException - if query failed
107         * */
108        public void find(int cursorStart, int maxRows, String sql, final CacheArray<Map<String, Object>> rows)
109                        throws SQLException {
110                find(cursorStart, maxRows, sql, Arrays.asList(new Object[] {}), rows);
111        }
112
113        /**
114         * This is limited rows query function (use Map param mode)
115         * @param cursorStart - JDBC result Cursor start index
116         * @param maxRows - JDBC result max rows (JDBC limited rows 50,000,000)
117         * @param sql - Query sql 
118         * @param rows - CacheArray object
119         * @exception SQLException - if query failed
120         * */
121        public void find(int cursorStart, int maxRows, String sql, Map<String, Object> params,
122                        final CacheArray<Map<String, Object>> rows) throws SQLException {
123                String csql = converSql(sql);
124                List<Object> cparams = converParams(sql, params);
125                find(cursorStart, maxRows, csql, cparams, rows);
126        }
127
128        /**
129         * This is limited rows query function (use List param mode)
130         * @param cursorStart - JDBC result Cursor start index
131         * @param maxRows - JDBC result max rows (JDBC limited rows 50,000,000)
132         * @param sql - Query sql 
133         * @param rows - CacheArray object
134         * @exception SQLException - if query failed
135         * */
136        public void find(int cursorStart, int maxRows, String sql, List<Object> params,
137                        final CacheArray<Map<String, Object>> rows) throws SQLException {
138                long begin = System.currentTimeMillis();
139                boolean allowedLog = writeSqlLog("find", begin,
140                                String.format("%s [cursorStart=%s,maxRows=%s]", sql, cursorStart, maxRows), params);
141                PreparedStatement statement = null;
142                Map<String, Object> row = null;
143
144                ResultSet result = null;
145                try {
146                        // ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE
147                        // ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY
148                        statement = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
149                        if (params != null) {
150                                int size = params.size();
151                                for (int i = 0; i < size; i++) {
152                                        int ci = i + 1;
153                                        Object param = params.get(i);
154                                        if (param == null) {
155                                                statement.setNull(ci, Types.VARCHAR);
156                                        } else {
157                                                statement.setObject(ci, param);
158                                        }
159                                }
160                        }
161                        if (maxRows > 0) {
162                                statement.setMaxRows(maxRows);
163                        }
164                        result = statement.executeQuery();
165                        result.absolute(cursorStart);
166                        final ResultSetMetaData rsmd = result.getMetaData();
167                        final int c = rsmd.getColumnCount();
168                        while (result.next()) {
169                                row = new ResultMap<String, Object>();
170                                for (int i = 0; i < c; i++) {
171                                        int ci = i + 1;
172                                        Object value = null;
173                                        Object originValue = result.getObject(ci);
174                                        if (originValue == null) {
175                                                value = originValue;
176                                        } else if (originValue instanceof Blob) {
177                                                Blob blobValue = (Blob) originValue;
178                                                InputStream is = null;
179                                                try {
180                                                        is = blobValue.getBinaryStream();
181                                                        if(is != null) value = is.readAllBytes();
182                                                } catch (IOException e) {
183                                                        throw new SQLException(e.getMessage(), e);
184                                                } finally {
185                                                        if (blobValue != null) {
186                                                                try {
187                                                                        blobValue.free();
188                                                                } catch (SQLException e) {
189                                                                        throw e;
190                                                                }
191                                                        }
192                                                        if (is != null) {
193                                                                try {
194                                                                        is.close();
195                                                                } catch (IOException e) {
196                                                                        throw new SQLException(e.getMessage(), e);
197                                                                }
198                                                        }
199                                                }
200                                        } else if (originValue instanceof Clob) {
201                                                Clob clobValue = (Clob) originValue;
202                                                InputStream is = null;
203                                                try {
204                                                        is = clobValue.getAsciiStream();
205                            if(is != null) value = is.readAllBytes();
206                                                } catch (IOException e) {
207                                                        throw new SQLException(e.getMessage(), e);
208                                                } finally {
209                                                        if (clobValue != null) {
210                                                                try {
211                                                                        clobValue.free();
212                                                                } catch (SQLException e) {
213                                                                        throw e;
214                                                                }
215                                                        }
216                                                        if (is != null) {
217                                                                try {
218                                                                        is.close();
219                                                                } catch (IOException e) {
220                                                                        throw new SQLException(e.getMessage(), e);
221                                                                }
222                                                        }
223                                                }
224                                        } else {
225                                                value = originValue;
226                                        }
227                                        row.put(converCase(rsmd.getColumnLabel(ci)), value);
228                                }
229                                rows.add(row);
230                                try {
231                                        Thread.sleep(getReadTimer());
232                                } catch (InterruptedException e) {
233                                        log.debug(e);
234                                        continue;
235                                }
236                        }
237                        rows.add(null);
238
239                        if (allowedLog) {
240                                writeSqlLog("rows", begin, "rows", rows.size() - 1);
241                                long spend = System.currentTimeMillis() - begin;
242                                writeSqlLog("spend", begin, "spend", spend);
243                        }
244                } catch (SQLException e) {
245                        if (allowedLog)
246                                writeSqlLog("error", begin, "error", String.format("%s,%s", e.getErrorCode(), e.getMessage()));
247                        throw e;
248                } finally {
249                        if (result != null)
250                                result.close();
251
252                        if (statement != null)
253                                statement.close();
254
255                }
256        }
257
258        /**
259         * Execute stored proc(and return result to CacheArray) 
260         * @param cursorStart - JDBC result Cursor start index
261         * @param maxRows - JDBC result max rows (JDBC limited rows 50,000,000)
262         * @param sql - Query sql 
263         * @param rows - CacheArray object
264         * @exception SQLException - if query failed 
265         * */
266        public void callAndReturnList(int cursorStart, int maxRows, String sql, final CacheArray<Map<String, Object>> rows)
267                        throws SQLException {
268                callAndReturnList(cursorStart, maxRows, sql, Arrays.asList(new Object[] {}), rows);
269        }
270
271        /**
272         * Execute stored proc(and return result) 
273         * @param cursorStart - JDBC result Cursor start index
274         * @param maxRows - JDBC result max rows (JDBC limited rows 50,000,000)
275         * @param sql - Query sql 
276         * @param params - Use Map param mode (use ':column_name' to mapping)
277         * @param rows - CacheArray object
278         * @exception SQLException - if query failed 
279         * */
280        public void callAndReturnList(int cursorStart, int maxRows, String sql, Map<String, Object> params,
281                        final CacheArray<Map<String, Object>> rows) throws SQLException {
282                String csql = converSql(sql);
283                List<Object> cparams = converParams(sql, params);
284                callAndReturnList(cursorStart, maxRows, csql, cparams, rows);
285        }
286
287        /**
288         * Execute stored proc(and return result) 
289         * @param cursorStart - JDBC result Cursor start index
290         * @param maxRows - JDBC result max rows (JDBC limited rows 50,000,000)
291         * @param sql - Query sql  (use '?' to mapping)
292         * @param params - Use List param mode
293         * @param rows - CacheArray object
294         * @exception SQLException - if query failed 
295         * */
296        public void callAndReturnList(int cursorStart, int maxRows, String sql, List<Object> params,
297                        final CacheArray<Map<String, Object>> rows) throws SQLException {
298
299                if (!checkSqlAvailable(sql))
300                        return;
301
302                long begin = System.currentTimeMillis();
303                boolean allowedLog = writeSqlLog("call", begin,
304                                String.format("%s [cursorStart=%s,maxRows=%s]", sql, cursorStart, maxRows), params);
305
306                PreparedStatement statement = null;
307                Map<String, Object> row = null;
308                ResultSet result = null;
309                try {
310                        statement = connection.prepareCall("{" + sql + "}");
311                        if (params != null) {
312                                int size = params.size();
313                                for (int i = 0; i < size; i++) {
314                                        int ci = i + 1;
315                                        Object param = params.get(i);
316                                        if (param == null) {
317                                                statement.setNull(ci, Types.VARCHAR);
318                                        } else {
319                                                statement.setObject(ci, param);
320                                        }
321                                }
322                        }
323                        if (maxRows > 0) {
324                                statement.setMaxRows(maxRows);
325                        }
326                        result = statement.executeQuery();
327                        callAndReturnListSync(connection, cursorStart, maxRows, sql, params);
328                        final ResultSetMetaData rsmd = result.getMetaData();
329                        final int c = rsmd.getColumnCount();
330                        int rowIndex = 0;
331                        while (result.next()) {
332                                if (rowIndex >= cursorStart) {
333                                        row = new ResultMap<String, Object>();
334                                        for (int i = 0; i < c; i++) {
335                                                int ci = i + 1;
336                                                Object value = null;
337                                                Object originValue = result.getObject(ci);
338                                                if (originValue == null) {
339                                                        value = originValue;
340                                                } else if (originValue instanceof Blob) {
341                                                        Blob blobValue = (Blob) originValue;
342                                                        InputStream is = null;
343                                                        try {
344                                                                is = blobValue.getBinaryStream();
345                                                                if(is != null) value = is.readAllBytes();
346                                                        } catch (IOException e) {
347                                                                throw new SQLException(e.getMessage(), e);
348                                                        } finally {
349                                                                if (blobValue != null) {
350                                                                        try {
351                                                                                blobValue.free();
352                                                                        } catch (SQLException e) {
353                                                                                throw e;
354                                                                        }
355                                                                }
356                                                                if (is != null) {
357                                                                        try {
358                                                                                is.close();
359                                                                        } catch (IOException e) {
360                                                                                throw new SQLException(e.getMessage(), e);
361                                                                        }
362                                                                }
363                                                        }
364                                                } else if (originValue instanceof Clob) {
365                                                        Clob clobValue = (Clob) originValue;
366                                                        InputStream is = null;
367                                                        try {
368                                                                is = clobValue.getAsciiStream();
369                                                                if(is != null) value = is.readAllBytes();
370                                                        } catch (IOException e) {
371                                                                throw new SQLException(e.getMessage(), e);
372                                                        } finally {
373                                                                if (clobValue != null) {
374                                                                        try {
375                                                                                clobValue.free();
376                                                                        } catch (SQLException e) {
377                                                                                throw e;
378                                                                        }
379                                                                }
380                                                                if (is != null) {
381                                                                        try {
382                                                                                is.close();
383                                                                        } catch (IOException e) {
384                                                                                throw new SQLException(e.getMessage(), e);
385                                                                        }
386                                                                }
387                                                        }
388                                                } else {
389                                                        value = originValue;
390                                                }
391                                                row.put(converCase(rsmd.getColumnLabel(ci)), value);
392                                        }
393                                        rows.add(row);
394                                        try {
395                                                Thread.sleep(getReadTimer());
396                                        } catch (InterruptedException e) {
397                                                log.debug(e);
398                                                continue;
399                                        }
400                                }
401                                rowIndex++;
402                        }
403                        rows.add(null);
404
405                        if (allowedLog) {
406                                writeSqlLog("rows", begin, "rows", rows.size() - 1);
407                                long spend = System.currentTimeMillis() - begin;
408                                writeSqlLog("spend", begin, "spend", spend);
409                        }
410                } catch (SQLException e) {
411                        if (allowedLog)
412                                writeSqlLog("error", begin, "error", e.getErrorCode());
413                        throw e;
414                } finally {
415                        if (result != null)
416                                result.close();
417
418                        if (statement != null)
419                                statement.close();
420                
421                }
422        }
423        
424        private long getReadTimer(){
425            return READ_TIMER;
426        }
427
428}