This code processes BLOB data retrieved from a SQL database...

September 3, 2025 at 02:01 PM

try (PreparedStatement psBlob = conn2.prepareStatement(
               "SELECT BUSINESS_OBJECT FROM BO_EXTN_DETAIL WHERE BANCS_CORRELATION_ID = ?")) {
               psBlob.setString(1, uuid);
               try (ResultSet rs = psBlob.executeQuery()) {
                   if (rs.next()) {
                       String[] lines = readBlob(rs.getBlob(1)).split("\\r?\\n");
                       for (int i = 0; i < lines.length; i++) {
                           if (lines[i].contains("\"MessageHeader\"")) {
                               String table = null;
                               for (int j = i - 1; j >= 0; j--) {
                                   String cand = lines[j].trim();
                                   if (cand.matches("\"[^\"]+\"\\s*:\\s*\\{")) {
                                       table = cleanPrefixSuffix(cand.substring(cand.indexOf('"')+1, cand.indexOf('"',1))).toUpperCase();
                                       break;
                                   }
                               }
                               if (table == null) continue;
                               blobTables.add(table);
                               System.out.println("Parsed table: " + table);
                               // Dynamic keySpec detection
                               /* String keySpecRaw = "";
                               for (int j = i; j < Math.min(lines.length, i+30); j++) {
                                   if (lines[j].contains("\"KeyVal\"")) {
                                       for (int k = j; k < Math.min(lines.length, j+10); k++) {
                                           if (lines[k].contains("\"#text\"")) {
                                               //keySpec = extractBetweenQuotes(lines[k]); 
											   keySpecRaw = extractBetweenQuotes(lines[k]);
                                               break;
                                           }
                                       }
                                       break;
                                   }
                               } */
							   String keySpec = null;
								for (int j = i + 1; j < i + 20 && j < lines.length; j++) {
								   if (lines[j].contains("\"KeyVal\"")) {
								       for (int k = j + 1; k < j + 10 && k < lines.length; k++) {
								           if (lines[k].contains("\"#text\"")) {
								               keySpec = extractBetweenQuotes(lines[k]);
								               break;
								           }
								       }
								       break;
								   }
								}
							   
                               Map<String, String> keys = extractKeyValues(lines, keySpec);
                               blobKeys.put(table, keys);
                               if (!keys.isEmpty()) {
                                   System.out.println("Keys for " + table + ":");
                                   keys.forEach((k,v)->System.out.println("   - " + k + " = " + v));
                               } else {
                                   System.out.println("No keys found for " + table);
                               }
                           }
                       }
                   }
               }
           }

This code processes BLOB data retrieved from a SQL database and extracts relevant information based on certain conditions. Here's a step-by-step explanation of what the code does:

Establishing a PreparedStatement:
- A PreparedStatement is created that queries the BO_EXTN_DETAIL table for the column BUSINESS_OBJECT, but only for rows where BANCS_CORRELATION_ID matches a given uuid value.
Executing the Query:
- The SQL query is executed, and the result is assigned to a ResultSet.
Iterating Through ResultSet:
- If the query returns a result (via rs.next()), the first BUSINESS_OBJECT column value is extracted as a BLOB (binary data).
Reading and Splitting the BLOB:
- The BLOB data is processed using the readBlob function and split into individual lines using a newline (\r?\n) delimiter.
Processing Each Line:
- The code iterates through the lines of the BLOB text and searches for lines containing the string "MessageHeader".
Extracting Parent Table Name:
- For every line containing "MessageHeader", the code looks backwards (up to i - 1) to find a parent JSON-like object label that matches the pattern "some_name" : {.
- If such a label is found, it's cleaned using cleanPrefixSuffix and converted to upper case. This label is assumed to represent a table name.
Adding the Table Name:
- The identified table name is added to the collection blobTables, and the parsed table's name is printed.
Dynamic Key Extraction:
- After locating a "MessageHeader", the code searches the following lines (up to 20 lines ahead) for a related "KeyVal" section. If "KeyVal" is found, it further searches for a specific subfield, "#text".
- The "#text" value is extracted using extractBetweenQuotes.
Key-Value Extraction:
- Using the extractKeyValues function, key-value pairs associated with the parsed keySpec are extracted from the lines.
- These extracted keys are stored in the blobKeys map, classified under the corresponding table name.
Printing Results:
- For each table, the extracted keys are printed to the console. If no keys are found, this is also noted.

Key Concepts:

SQL Interaction:
- The code interacts with a database to fetch BLOB data, which is then processed.
JSON-Like Data Parsing:
- The BLOB data appears to contain a JSON-like structure. Specific fields, like "MessageHeader" and "KeyVal", are targeted to extract meaningful data.
Dynamic Key Extraction:
- Based on the "KeyVal" object and its child "#text", the code dynamically determines keys needed for further processing.
Data Organization:
- The results are categorized by table names (extracted parent keys) and stored in a blobKeys dictionary.
Error Handling and Resource Management:
- The try-with-resources blocks ensure proper resource handling for database and blob operations.

Example Use Case:

This processing could be part of a data integration pipeline where:

JSON or JSON-like data is embedded in a database.
Specific tables or keys need to be extracted dynamically from the data.
The tables and their associated keys might be used for further processing or logging.

The code provides a robust framework for parsing semi-structured data stored as BLOBs.

Generate your own explanations

Download our vscode extension

Read other generated explanations

Built by @thebuilderjr