001/* 002MIT License 003 004Copyright (c) 2020 FBSQL Team 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 all 014copies 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 THE 022SOFTWARE. 023 024Home: https://fbsql.github.io 025E-Mail: fbsql.team@gmail.com 026*/ 027 028package org.fbsql.servlet; 029 030import java.io.ByteArrayOutputStream; 031import java.io.IOException; 032import java.nio.charset.StandardCharsets; 033import java.sql.Blob; 034import java.sql.Date; 035import java.sql.ResultSet; 036import java.sql.ResultSetMetaData; 037import java.sql.SQLException; 038import java.sql.Time; 039import java.sql.Timestamp; 040import java.sql.Types; 041import java.util.ArrayList; 042import java.util.Base64; 043import java.util.LinkedHashMap; 044import java.util.List; 045import java.util.Map; 046import java.util.zip.Deflater; 047 048import org.apache.hive.common.util.Murmur3; 049import org.fbsql.json.parser.JsonUtils; 050 051/** 052 * Provides utility methods for convert ResutlSet to JSON. 053*/ 054public class QueryUtils { 055 // 056 // The format constants 057 // This constants used in client side JavaScript 058 // 059 060 /** 061 * Format results as JSON array of objects 062 * Default format 063 */ 064 public static final int RESULT_FORMAT_ARRAY_OF_OBJECTS = 0; // the default 065 066 /** 067 * Format results as JSON array of arrays 068 * 2D-array (this format may be useful in case you want to hide ResultSet column names from client) 069 */ 070 public static final int RESULT_FORMAT_ARRAY_OF_ARRAYS = 1; // 2D-array (this format may be useful in case you want to hide ResultSet column names from client) 071 072 /** 073 * Format results as JSON object of arrays (pivot) 074 * 075 * WARNING! This format supported by client side JavaScript 076 * Server side receive it as RESULT_FORMAT_ARRAY_OF_OBJECTS, 077 * so this constant is never used on server side 078 * We preserve this comment for client and server code consistency 079 */ 080 //public static final int RESULT_FORMAT_OBJECT_OF_ARRAYS = 2; // pivot (processed on client side) 081 082 /** 083 * Parse JSON array of objects 084 * 085 * @param jsonArrayOfObjects 086 * @return 087 */ 088 public static List<Map<String /* column name */, String /* JSON value */>> convertJsonArrayOfObjectsToListOfMaps(String jsonArrayOfObjects) { 089 List<String> listOfJsonStrings = JsonUtils.parseJsonArray(jsonArrayOfObjects); 090 List<Map<String /* column name */, String /* JSON value */>> list = new ArrayList<>(listOfJsonStrings.size()); 091 for (String jsonString : listOfJsonStrings) { 092 Map<String, String> map = JsonUtils.parseJsonObject(jsonString); 093 list.add(map); 094 } 095 return list; 096 } 097 098 /** 099 * Converts list of results to ReadyResult object 100 * 101 * @param list - result list to convert 102 * @param resultSetFormat - result set format 103 * @param encoder - Base64 encoder 104 * @return - ReadyResult object 105 * 106 * @throws SQLException 107 * @throws IOException 108 */ 109 public static ReadyResult createReadyResult(List<Map<String /* column name */, String /* JSON value */>> list, int resultSetFormat, int compressionLevel, Base64.Encoder encoder) throws SQLException, IOException { 110 String json = convertToJsonArray(list, resultSetFormat); 111 112 // 113 // Compression optimization for "too short" results. 114 // We need prevent compression in case empty result set, because output 115 // compressed array (even in best compression level) will have length = 10 116 // and no compressed array have length = 2 for string "[]", 117 // so, we override user-declared compression level with NO COMPRESSION value 118 // 119 if (json.length() == 2) // empty result set case: [] 120 compressionLevel = CompressionLevel.NO_COMPRESSION; 121 122 ReadyResult readyResult = new ReadyResult(); 123 124 readyResult.bs = json.getBytes(StandardCharsets.UTF_8); 125 if (compressionLevel == CompressionLevel.BEST_SPEED) { 126 readyResult.compressed = true; 127 readyResult.bs = compress(readyResult.bs, Deflater.BEST_SPEED); 128 } else if (compressionLevel == CompressionLevel.BEST_COMPRESSION) { 129 readyResult.compressed = true; 130 readyResult.bs = compress(readyResult.bs, Deflater.BEST_COMPRESSION); 131 } else if (compressionLevel == CompressionLevel.NO_COMPRESSION) 132 readyResult.compressed = false; 133 else 134 throw new IllegalArgumentException(Integer.toString(compressionLevel)); 135 readyResult.etag = "\"" + Integer.toHexString(Murmur3.hash32(readyResult.bs)) + "\""; // Murmur3 32-bit variant 136 137 return readyResult; 138 } 139 140 /** 141 * Converts list of maps to list of maps Json strings 142 * 143 * @param rs - ResultSet to convert 144 * @param encoder - Base64 encoder 145 * @return - list of maps 146 * 147 * @throws SQLException 148 * @throws IOException 149 */ 150 public static List<Map<String /* column name */, String /* JSON value */>> listOfMapsToListOfMapsJsonValues(List<Map<String, Object>> list, Base64.Encoder encoder) throws SQLException, IOException { 151 List<Map<String /* column name */, String /* JSON value */>> jlist = new ArrayList<>(list.size()); 152 for (Map<String, Object> map : list) { 153 Map<String /* column name */, String /* JSON value */> jmap = new LinkedHashMap<>(); 154 for (Map.Entry<String, Object> entry : map.entrySet()) { 155 String columnName = entry.getKey(); 156 Object value = entry.getValue(); 157 158 String svalue = valueToJsonString(value, encoder); 159 jmap.put(columnName, svalue); 160 } 161 jlist.add(jmap); 162 } 163 return jlist; 164 } 165 166 public static List<Map<String /* column name */, String /* JSON value */>> resutlSetToListOfMapsJsonValues(ResultSet rs, Base64.Encoder encoder) throws SQLException, IOException { 167 try (rs) { 168 List<Map<String /* column name */, Object /* column value */>> resultsListOfMaps = QueryUtils.resutlSetToListOfMaps(rs); 169 return QueryUtils.listOfMapsToListOfMapsJsonValues(resultsListOfMaps, encoder); 170 } 171 } 172 173 /** 174 * 175 * @param value 176 * @param encoder 177 * @return 178 * @throws SQLException 179 */ 180 public static String /* JSON value */ valueToJsonString(Object value, Base64.Encoder encoder) throws SQLException { 181 String svalue = null; 182 /* NULL */ 183 if (value == null) // NULL 184 svalue = "null"; 185 else if (value instanceof Blob) { 186 Blob blob = (Blob) value; 187 byte[] bs = blob.getBytes(1L, (int) blob.length()); 188 svalue = "\"" + encoder.encodeToString(bs) + "\""; 189 } 190 /* BINARY, VARBINARY, LONGVARBINARY, BLOB */ 191 else if (value instanceof byte[]) { // value returned as Base64 encoded string 192 byte[] bs = (byte[]) value; 193 svalue = "\"" + encoder.encodeToString(bs) + "\""; 194 /* CHAR, VARCHAR, CLOB && JSON */ 195 } else if (value instanceof String) { 196 String strValue = ((String) value).trim(); 197 /* JSON Types */ 198 if ( // 199 (strValue.startsWith("{") && strValue.endsWith("}")) || // JSON Objecty 200 (strValue.startsWith("[") && strValue.endsWith("]")) || // JSON Array 201 (strValue.startsWith("\"") && strValue.endsWith("\"")) || // JSON String 202 (strValue.equals("null")) || // JSON null 203 (strValue.equals("true")) || // JSON true 204 (strValue.equals("false")) // JSON false 205 ) 206 svalue = strValue; 207 /* CHAR, VARCHAR, CLOB etc. */ 208 else 209 svalue = "\"" + value + "\""; 210 /* DATE, TIME, TIMESTAMP */ 211 } else if (value instanceof Date || value instanceof Time || value instanceof Timestamp) 212 svalue = "\"" + value + "\""; 213 /* NUMBER, BOOLEAN, etc */ 214 else if (value instanceof Number || value instanceof Boolean) 215 svalue = value.toString(); 216 /* Other types */ 217 else 218 svalue = "\"" + value.toString() + "\""; 219 return svalue; 220 } 221 222 /** 223 * Converts ResutlSet to list of maps 224 * 225 * @param rs - ResultSet to convert 226 * @param encoder - Base64 encoder 227 * @return - list of maps 228 * 229 * @throws SQLException 230 * @throws IOException 231 */ 232 public static List<Map<String /* column name */, Object /* value */>> resutlSetToListOfMaps(ResultSet rs) throws SQLException, IOException { 233 List<Map<String /* column name */, Object /* column value */>> list = new ArrayList<>(); 234 ResultSetMetaData md = rs.getMetaData(); 235 int columnCount = md.getColumnCount(); 236 while (rs.next()) { 237 Map<String /* column name */, Object /* column value */> map = new LinkedHashMap<>(); 238 for (int i = 0; i < columnCount; i++) { 239 int n = i + 1; 240 String columnName = md.getColumnName(n); 241 int columnType = md.getColumnType(n); 242 Object value; 243 if (columnType == Types.BINARY || columnType == Types.VARBINARY || columnType == Types.LONGVARBINARY) 244 value = rs.getBytes(n); 245 else if (columnType == Types.BLOB) 246 value = rs.getBlob(n); 247 else 248 value = rs.getObject(n); 249 map.put(columnName, value); 250 } 251 list.add(map); 252 } 253 return list; 254 } 255 256 public static String convertToJsonArray(List<String /* JSON object */> paramJsons) { 257 StringBuilder sb = new StringBuilder(); 258 sb.append('['); 259 for (int i = 0; i < paramJsons.size(); i++) { 260 if (i != 0) 261 sb.append(','); 262 sb.append(paramJsons.get(i)); 263 } 264 sb.append(']'); 265 return sb.toString(); 266 } 267 268 /** 269 * Converts list of results to JSON array 270 * 271 * @param list - result list to convert 272 * @param resultSetFormat - result set format 273 * @return - JSON array 274 */ 275 public static String convertToJsonArray(List<Map<String /* column name */, String /* JSON value */>> list, int resultSetFormat) { 276 StringBuilder sb = new StringBuilder(); 277 sb.append('['); 278 for (int i = 0; i < list.size(); i++) { 279 if (i != 0) 280 sb.append(','); 281 Map<String /* column name */, String /* JSON value */> row = list.get(i); 282 if (resultSetFormat == RESULT_FORMAT_ARRAY_OF_OBJECTS) 283 map2JsonObject(row, sb); 284 else if (resultSetFormat == RESULT_FORMAT_ARRAY_OF_ARRAYS) 285 map2JsonArray(row, sb); 286 } 287 sb.append(']'); 288 return sb.toString(); 289 } 290 291 /** 292 * Converts list with only one element (list.size() == 1) to JSON object 293 * 294 * @param list - result list with only one element (list.size() == 1) to convert 295 * @param resultSetFormat - result set format 296 * @return - JSON object 297 */ 298 public static String convertToJsonObject(List<Map<String /* column name */, String /* JSON value */>> list, int resultSetFormat) { 299 String jsonArray = convertToJsonArray(list, resultSetFormat); 300 return jsonArray.substring(1, jsonArray.length() - 1); 301 } 302 303 /** 304 * Converts map to JSON array 305 * 306 * @param map - map to convert 307 * @param sb - StringBuilder to output results 308 */ 309 private static void map2JsonArray(Map<String /* column name */, String /* JSON value */> map, StringBuilder sb) { 310 boolean first = true; 311 sb.append('['); 312 for (String /* JSON value */ value : map.values()) { 313 if (first) 314 first = false; 315 else 316 sb.append(','); 317 sb.append(value); 318 } 319 sb.append(']'); 320 } 321 322 /** 323 * Converts map to JSON object 324 * 325 * @param map - map to convert 326 * @param sb - StringBuilder to output results 327 */ 328 private static void map2JsonObject(Map<String /* column name */, String /* JSON value */> map, StringBuilder sb) { 329 boolean first = true; 330 sb.append('{'); 331 for (Map.Entry<String /* column name */, String /* JSON value */> entry : map.entrySet()) { 332 if (first) 333 first = false; 334 else 335 sb.append(','); 336 sb.append("\"" + entry.getKey() + "\":" + entry.getValue()); 337 } 338 sb.append('}'); 339 } 340 341 /** 342 * Compress byte array using ZLIB compression algorithm 343 * 344 * @param bs - source byte array 345 * @return - compressed byte array 346 * 347 * @throws IOException 348 */ 349 public static byte[] compress(byte[] bs, int compressionLevel) throws IOException { 350 byte[] tmp = new byte[bs.length]; 351 Deflater deflater = new Deflater(); 352 deflater.setLevel(compressionLevel); 353 deflater.setInput(bs); 354 deflater.finish(); 355 try (ByteArrayOutputStream baos = new ByteArrayOutputStream()) { 356 while (!deflater.finished()) { 357 int size = deflater.deflate(tmp); 358 baos.write(tmp, 0, size); 359 } 360 deflater.end(); 361 return baos.toByteArray(); 362 } 363 } 364 365} 366 367/* 368Please contact FBSQL Team by E-Mail fbsql.team@gmail.com 369or visit https://fbsql.github.io if you need additional 370information or have any questions. 371*/ 372 373/* EOF */