Generate SQL Queries Programmatically

This example shows you how to create an SQL select query programmatically using DataPipeline.  The Select class is used to compose the parts of your query along with any parameters.  If you've ever tried to create dynamic SQL with multiple, optional parts then you'll understand the pain this class and package solves. 

This example is based on a table named user with the following fields:

1. id
2. userName
3. firstName
4. lastName
5. role

Java code listing

package com.northconcepts.datapipeline.examples.cookbook;

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

public class GenerateSqlQueriesProgrammatically {

    public static void main(String[] args) {
        SqlPart select = new Select("user")
                .select("*")
                .where("id = ? AND role = ?", 12, "admin")
                .orderBy("firstName");
        
        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 by adding the table name, user as an argument.
2. select("*") specifies that all columns should be selected.
3. where("id = ? AND role = ?", 12, "admin") adds a WHERE clause to the query, specifying two conditions with placeholder values. The actual values 12 and admin are provided as arguments.
4. select.getSqlFragment() retrieves the SQL fragment of the select object, which represents the generated select query.
5. select.getParameterValues() returns an array of parameter values used in the query. 

Output

The output will be written to the console:

Select query: SELECT * FROM user WHERE id = ? AND role = ? ORDER BY firstName
Query parameters: 
12
admin