Dynamic SQL: Java Examples and SQL Comparisons

Mastering Dynamic SQL is essential when you need database queries that adapt on the fly based on user input or runtime conditions. Unlike traditional static queries, constructing SQL dynamically allows your applications to build flexible, highly responsive database commands.

In this tutorial, you will learn what dynamic SQL is, explore real-world use cases, see how runtime query generation works in Java with secure parameter binding, and compare it against static and embedded SQL approaches.

What is dynamic SQL?

Dynamic SQL is a programming technique that lets you construct and run database queries on the fly like building a customized sentence out of individual words while your software is actually running. Instead of using a fixed, pre-written query that never changes, dynamic SQL creates the exact command you need based on changing user choices, such as dynamic search filters on a shopping site.

While it gives your applications incredible flexibility to handle complex and unpredictable requests, it requires careful handling (using parameterized inputs) to prevent security risks like SQL injection.

What is the use of dynamic SQL?

Dynamic SQL is used when you need a database query to automatically adapt to changing conditions or unpredictable user inputs while an application is running. Instead of writing dozens of rigid, fixed queries for every possible scenario, dynamic SQL lets software build the exact query it needs on the spot such as filtering a product search by a unique combination of price, size, and brand selected by a shopper.

Dynamic SQL in Java

In Java, Dynamic SQL refers to constructing and executing database queries at runtime, meaning, the exact SQL statement isn’t hardcoded in your application ahead of time, but is instead built dynamically based on changing variables or user input.

How It Works in Java

When building applications, user choices often dictate what data needs to be fetched. Instead of writing dozens of hardcoded queries, Java builds the SQL string conditionally as the application runs.

There are different ways to create dynamic SQL queries in Java:

• String Manipulation / StringBuilder: You concatenate SQL fragments in plain Java using conditions (if statements) to build a query string, which you then pass to Java’s PreparedStatement.
• Frameworks (e.g., MyBatis, Hibernate / JPA Criteria API): Modern Java frameworks provide built-in tools or XML tags (<if>, <choose>, <where>) so you can construct flexible queries cleanly without manually gluing raw text strings together.

Real-World Example: A Filtered Search

Imagine an e-commerce search bar where a user can search by Name, Category, or Max Price or any combination of the three.

1. Base Query: SELECT * FROM products WHERE 1=1
2. If user enters a name: Append AND name LIKE ?
3. If user selects a category: Append AND category = ?
4. If user sets a max price: Append AND price <= ?

Depending on what the user fills out, Java constructs the exact SQL needed on the fly.

Note: When constructing dynamic SQL in plain Java, never concatenate raw user input directly into the SQL string (e.g., “WHERE name = ‘” + userInput + “‘”). Always build the query string with placeholder parameters (?) and pass the actual values through a PreparedStatement to keep your application secure against SQL injection attacks.

Example of dynamic SQL in Java

The following Java program performs a dynamic search across the university database to find students based on flexible criteria.

Program:

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;


public class SimpleDynamicSQL {

    // Update with your actual MySQL database credentials

    private static final String URL = "jdbc:mysql://localhost:3306/university_db";

    private static final String USER = "root";

    private static final String PASSWORD = "password";


    public static void main(String[] args) {

        // Example filters: pass null to ignore a filter

        Integer deptIdFilter = 101;     // Computer Science

        Integer courseIdFilter = 201;   // Database Systems

        String nameFilter = null;       // No name search


        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {

            searchStudents(conn, deptIdFilter, courseIdFilter, nameFilter);

        } catch (SQLException e) {

            e.printStackTrace();

        }

    }


    public static void searchStudents(Connection conn, Integer deptId, Integer courseId, String nameKeyword) throws SQLException {

        // Base Query

        StringBuilder sql = new StringBuilder(

            "SELECT s.Student_ID, s.Student_Name, d.Dept_Name, c.Course_Name " +

            "FROM Student s " +

            "JOIN Department d ON s.Dept_ID = d.Dept_ID " +

            "JOIN Enrollment e ON s.Student_ID = e.Student_ID " +

            "JOIN Course c ON e.Course_ID = c.Course_ID " +

            "WHERE 1=1 "

        );


        // Dynamically build conditions

        if (deptId != null) sql.append("AND s.Dept_ID = ? ");

        if (courseId != null) sql.append("AND e.Course_ID = ? ");

        if (nameKeyword != null) sql.append("AND s.Student_Name LIKE ? ");


        // Bind parameters safely

        try (PreparedStatement pstmt = conn.prepareStatement(sql.toString())) {

            int paramIndex = 1;


            if (deptId != null) pstmt.setInt(paramIndex++, deptId);

            if (courseId != null) pstmt.setInt(paramIndex++, courseId);

            if (nameKeyword != null) pstmt.setString(paramIndex++, "%" + nameKeyword + "%");


            // Execute & Print Results

            try (ResultSet rs = pstmt.executeQuery()) {

                System.out.printf("%-12s | %-15s | %-20s | %-18s%n", "Student ID", "Student Name", "Dept Name", "Course Name");

                System.out.println("------------------------------------------------------------------");


                while (rs.next()) {

                    System.out.printf("%-12d | %-15s | %-20s | %-18s%n",

                            rs.getInt("Student_ID"),

                            rs.getString("Student_Name"),

                            rs.getString("Dept_Name"),

                            rs.getString("Course_Name"));

                }

            }

        }

    }

}

Output:

Program breakdown:

• Database Connection (DriverManager.getConnection): Connects the Java application to your existing MySQL database (university_db) using your credentials.
• Filter Setup (Integer & String variables): Uses nullable wrapper objects (Integer instead of int) so that null can represent an unselected filter. Setting a variable to null tells the program to ignore that search criterion.
• Base Query & WHERE 1=1 Trick: Initializes a StringBuilder with the basic SELECT and JOIN clauses. The WHERE 1=1 statement is a common SQL pattern—it is always true, allowing additional filter conditions (AND …) to be appended dynamically without worrying about whether it is the first or second condition.
• Conditional String Appending (if statements): Checks each filter variable. If a variable is not null, the program appends the corresponding AND condition to the StringBuilder with a parameter placeholder (?).
• Safe Parameter Binding (PreparedStatement): Iterates through the non-null filters a second time to assign values to the ? placeholders in exact order using paramIndex++. Using ? placeholders protects the application against SQL injection.
• Query Execution & Display: Runs executeQuery() to retrieve the matching rows from MySQL and formats the output into a clean table in the console.

Differences between embedded SQL and dynamic SQL

Think of Embedded SQL like ordering from a fixed menu at a restaurant, the options are predetermined and fast to prepare. Dynamic SQL is like building your own custom meal at a buffet, you decide on the spot what ingredients to combine based on what you need at that exact moment.

The key differences between embedded SQL and dynamic SQL are given in the table below:

Differences between static SQL and dynamic SQL

Think of Static SQL like a pre-printed form, you fill in the blank spaces (parameters), but the overall structure and questions never change. Dynamic SQL is like drafting a custom letter from scratch, you decide which sentences and sections to include based on what you need at that exact moment.

The key differences between static SQL and dynamic SQL are given in the table below:

Suryateja Pericherla

Mr. P.S.Suryateja, also known as Suryateja Pericherla, is at present a Research Scholar (full-time Ph.D.) in the Dept. of Computer Science & Systems Engineering at Andhra University, Visakhapatnam. Previously worked as an Associate Professor in the Dept. of CSE at Vishnu Institute of Technology, India.

He has 14+ years of teaching experience and is an individual researcher whose research interests are Cloud Computing, Internet of Things, Computer Security, Network Security and Blockchain.

He is a member of professional societies like IEEE, ACM, CSI and ISCA. He published several research papers which are indexed by SCIE, WoS, Scopus, Springer and others.