This code constructs a dynamic SQL query using a `StringBuilder`...

September 1, 2025 at 06:26 AM

StringBuilder query = new StringBuilder("SELECT * FROM ").append(table).append(" WHERE 1=1");
            List<Object> params = new ArrayList<>();
            for (Map.Entry<String, Object> entry : keys.entrySet()) {
                String key = entry.getKey().toUpperCase();
                Object val = entry.getValue();
                int sqlType = columnTypes.getOrDefault(key, Types.VARCHAR);
                if (sqlType == Types.DATE) {

                    // Inline substitution for DATE
                    query.append(" AND TRUNC(").append(key).append(") = TO_DATE('")
                         .append(val.toString()).append("','YYYY-MM-DD')");
                } else {
                    query.append(" AND ").append(key).append(" = ?");
                    params.add(val);
                }
            }
            System.out.println("Executing Query: " + query);

This code constructs a dynamic SQL query using a StringBuilder and prepares it to execute against a database. It adapts to the contents of a keys map and the column data types specified in columnTypes. Below is a detailed explanation of what the code does:

Steps performed by the code:

Start building the SQL query:
- The query starts as: SELECT * FROM .
- The table variable is appended to specify the table name.
- The WHERE 1=1 clause is added to simplify appending additional AND conditions later.
Prepare to handle parameters:
- A List<Object> params is initialized to hold the values for the query placeholders (?).
Iterate over the keys map:
- The keys map is expected to contain column names as keys and corresponding filter values as the map values.
- For each entry:
  - The key (column name) is converted to uppercase.
  - The value is examined for its type using the columnTypes map to retrieve the SQL data type (or default to Types.VARCHAR if not found).
Add conditions to the query for each key-value pair:
- If the data type is DATE:
  - The key (column name) is treated as a date column.
  - A condition like TRUNC(column_name) = TO_DATE('value', 'YYYY-MM-DD') is appended to the query. This is typically used to compare only the "date" part of a date-time field in databases.
- For any other data type:
  - A placeholder (?) is added to the query for the column name (e.g., AND column_name = ?).
  - The value is added to the params list, as it will be bound later when running the query.
Print the constructed query:
- Finally, the resulting SQL query (after appending all conditions) is printed to the console using System.out.println.

Example Behavior:

Input:

table = "EMPLOYEES"
keys = { "name": "John", "hire_date": "2023-10-12" }
columnTypes = { "NAME": Types.VARCHAR, "HIRE_DATE": Types.DATE }

Output Query:

SELECT * FROM EMPLOYEES WHERE 1=1 AND NAME = ? AND TRUNC(HIRE_DATE) = TO_DATE('2023-10-12','YYYY-MM-DD')

`params` List:

["John"]

Key Notes:

The SQL query is generated dynamically based on the structure of keys and columnTypes.
Parameters for non-DATE columns are prepared as placeholders (?) for secure query execution (preventing SQL injection).
Date-specific formatting (TRUNC and TO_DATE) is handled inline within the query for DATE columns.
The resulting query construction and parameter list would typically be used to execute a prepared statement against a database.

Generate your own explanations

Download our vscode extension

Read other generated explanations

Built by @thebuilderjr