Generate Union and Sub-Select SQL Queries Programmatically

DataPipeline contains a set of SQL builder classes to help in the construction of complex, dynamic queries.  They enable you to specify query using Java objects and methods. This example shows how to generate queries involving `UNION` and subselect statement..

You can also view Generate SQL Queries Programmatically to see how selecting, filtering and sorting can be implemented using the other SQL builder classes.

Java Code Listing

package com.northconcepts.datapipeline.examples.cookbook;

import com.northconcepts.datapipeline.jdbc.sql.SqlPart;
import com.northconcepts.datapipeline.jdbc.sql.select.Select;
import com.northconcepts.datapipeline.jdbc.sql.select.Union;

public class GenerateUnionAndSubSelectSqlQueriesProgrammatically {
    public static void main(String[] args) {
        SqlPart select = new Select(
                new Union(
                        new Select("employees").select("employee_id", "first_name"),
                        new Select("contractors").select("employee_id", "first_name")
                ).setNestedAlias("employee_details"))
                .where("first_name = ?", "John");

        System.out.println("Select query: " + select.getSqlFragment());

        System.out.println("Query parameters: ");
        for (Object value : select.getParameterValues()) {
            System.out.println(value.toString());
        }
    }
}

Code Walkthrough

1. Create a Select object containing another Union object as an argument.
2. The Union combines the result set of two SELECT statements referenced by corresponding instances.
3. Each Select instance has a table name as an argument. select("employee_id", "first_name") specifies the columns that are being selected.
4. The result of the union statement is given a table alias employee_details using setNestedAlias() method.
5. where("first_name = ?", "John") adds a WHERE clause to the query, specifying one condition with a placeholder value.
6. select.getSqlFragment() retrieves the SQL fragment of the Select object, which represents the generated query. It is printed on the console.
7. select.getParameterValues() returns an array of parameter values used in the query.  In our case, John parameter is then printed on the console.

Console Output

Select query: SELECT * FROM ( SELECT employee_id, first_name FROM employees 
UNION SELECT employee_id, first_name FROM contractors ) employee_details WHERE first_name = ?
Query parameters: 
John