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