From Natural Language to Type-Safe SQL in Java with Querier AI

From Natural Language to Type-Safe SQL in Java with Querier AI

What if users could ask questions about their application data in plain English instead of writing SQL or navigating complex filters?

For example:

Show me the top 10 customers by total revenue in the last 30 days.

I recently released Querier AI, an optional module of my open-source Java SQL query builder, Querier. It translates natural-language prompts into validated Querier Select objects, allowing you to use AI-powered queries without handing database access directly to an AI model.

In this tutorial, we'll integrate it into a Spring Boot application and execute the resulting query using Spring JDBC.

What we're building

The request flow is straightforward:

1. A user submits a natural-language question.
2. Querier AI generates SQL based on the schema you expose.
3. The SQL is validated against that schema and parsed into a Querier Select object.
4. Your application executes the resulting query through its existing database layer.
5. The application displays the results.

The important distinction is that the result of the AI operation is a Querier object that your Java application can inspect and use, rather than simply a SQL string to execute blindly.

1. Add the dependency

Querier AI is an optional module, so you don't need to add AI dependencies if you only want to use the core query builder.

For Maven, add querier-ai to your pom.xml:

<dependency>
    <groupId>com.aytmatech</groupId>
    <artifactId>querier-ai</artifactId>
    <version>2.0.0</version>
</dependency>

This example uses Querier 2.0.0. Check the GitHub releases for newer versions.

You'll need Java 17 or higher, a Spring application with database access, and entity classes that describe the tables you want to query.

2. Configure your AI endpoint

Querier AI follows a bring-your-own-key approach. It works with OpenAI-compatible endpoints, including OpenAI, Azure OpenAI, Mistral, and Ollama-compatible setups.

Configure the provider through environment variables rather than hardcoding credentials in your source code:

export QUERIER_AI_API_KEY="your-api-key"
export QUERIER_AI_BASE_URL="https://api.openai.com/v1"
export QUERIER_AI_MODEL="gpt-4o-mini"

Use the appropriate base URL, model or deployment identifier, and credentials for your provider.

Next, register the AI client and QuerierAi service as Spring beans:

import com.aytmatech.querier.ai.AiClient;
import com.aytmatech.querier.ai.QuerierAi;
import com.aytmatech.querier.ai.retry.RetryPolicy;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class QuerierAiConfig {

    @Bean
    public AiClient aiClient() {
        return AiClient.builder()
                .apiKey(System.getenv("QUERIER_AI_API_KEY"))
                .baseUrl(System.getenv("QUERIER_AI_BASE_URL"))
                .model(System.getenv("QUERIER_AI_MODEL"))
                .build();
    }

    @Bean
    public QuerierAi querierAi(AiClient aiClient) {
        return QuerierAi.builder()
                .client(aiClient)
                .retryPolicy(RetryPolicy.ofMaxAttempts(2))
                .build();
    }
}

The retry policy allows Querier AI to attempt corrections when generated SQL is invalid. You can adjust the policy to suit your application.

Keep API keys out of logs, source control, and client-side code. In production, use your deployment platform's secret-management facilities.

3. Choose which entities the AI can see

The schema you expose is an important part of this integration.

Suppose your application has Order, Customer, and Product entity classes. You can restrict the AI's schema context to those classes:

private static final Class<?>[] EXPOSED_ENTITIES = {
        Order.class,
        Customer.class,
        Product.class
};

The first class is treated as the primary entity by the inspector, so put your main entity first.

Querier AI can inspect the exposed classes and their mappings to build the schema context:

import com.aytmatech.querier.ai.model.AiSchemaContext;
import com.aytmatech.querier.ai.model.AiSchemaInspector;

AiSchemaContext schemaContext =
        AiSchemaInspector.inspect(EXPOSED_ENTITIES);

Make sure your entity mappings correctly describe your database schema. Querier supports its own table and column annotations, as well as supported JPA annotations.

You can also add semantic descriptions and synonyms to help the AI understand domain-specific terms such as "sales," "revenue," or "transactions." See the Querier documentation for more details.

4. Turn a prompt into a validated query

Let's implement the core of a Spring MVC controller.

The controller accepts a prompt, creates a QueryRequest, and sends it to QuerierAi.query().

import com.aytmatech.querier.Select;
import com.aytmatech.querier.ai.QuerierAi;
import com.aytmatech.querier.ai.model.AiSchemaContext;
import com.aytmatech.querier.ai.model.AiSchemaInspector;
import com.aytmatech.querier.ai.query.QueryRequest;
import com.aytmatech.querier.ai.query.QueryResult;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;

import java.util.ArrayList;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;

@Controller
@RequestMapping("/ai")
public class AiQueryController {

    private static final Class<?>[] EXPOSED_ENTITIES = {
            Order.class,
            Customer.class,
            Product.class
    };

    private final QuerierAi querierAi;
    private final NamedParameterJdbcTemplate jdbc;

    public AiQueryController(
            QuerierAi querierAi,
            NamedParameterJdbcTemplate jdbc) {
        this.querierAi = querierAi;
        this.jdbc = jdbc;
    }

    @PostMapping("/query")
    public String query(
            @RequestParam("prompt") String prompt,
            Model model) {

        model.addAttribute("queryPrompt", prompt);

        AiSchemaContext schemaContext =
                AiSchemaInspector.inspect(EXPOSED_ENTITIES);

        QueryRequest request = QueryRequest.builder()
                .prompt(prompt)
                .schemaContext(schemaContext)
                .temperature(0.0)
                .build();

        QueryResult result = querierAi.query(request);

        if (!result.isValid()) {
            model.addAttribute(
                    "queryError",
                    String.join("\n", result.validationIssues()));

            result.interpretationNote().ifPresent(
                    note -> model.addAttribute("queryNote", note));

            return "ai/index";
        }

        Select select = result.query().orElseThrow();
        Select.SqlAndParams sqlAndParams = select.toSqlAndParams();

        List<Map<String, Object>> rows = jdbc.queryForList(
                sqlAndParams.sql(),
                sqlAndParams.params());

        List<String> columns = new ArrayList<>();

        if (!rows.isEmpty()) {
            columns.addAll(
                    new LinkedHashSet<>(rows.get(0).keySet()));
        }

        result.interpretationNote().ifPresent(
                note -> model.addAttribute("queryNote", note));

        model.addAttribute("queryColumns", columns);
        model.addAttribute("queryRows", rows);

        return "ai/index";
    }
}

This is the query endpoint; you'll also need a GET handler or another route to display the initial page. The example assumes your application has the entity classes and a Thymeleaf template at ai/index.html.

A few details are worth highlighting:

• temperature(0.0) requests a low-variation response for this query-generation flow.
• result.isValid() lets you handle validation failures before execution.
• result.query() provides the validated Select object.
• toSqlAndParams() returns the SQL and named parameters in a form that works with NamedParameterJdbcTemplate.

The controller also makes an interpretation note available to the view when Querier AI provides one.

5. Add a simple form and results table

You don't need a special frontend framework to try this feature. A basic Thymeleaf form is enough:

<form method="post" th:action="@{/ai/query}">
    <textarea
        name="prompt"
        placeholder="Show the top 10 customers by revenue in the last 30 days"
        required></textarea>

    <button type="submit">Run query</button>
</form>

If Spring Security is enabled, make sure your form includes the appropriate CSRF protection.

To display validation errors:

<div th:if="${queryError}" class="alert alert-error">
    <strong>Could not generate a valid query:</strong>
    <pre th:text="${queryError}"></pre>
</div>

And to render the result rows dynamically:

<table th:if="${queryColumns != null and !queryColumns.isEmpty()}">
    <thead>
        <tr>
            <th th:each="column : ${queryColumns}"
                th:text="${column}"></th>
        </tr>
    </thead>
    <tbody>
        <tr th:each="row : ${queryRows}">
            <td th:each="column : ${queryColumns}"
                th:text="${row.get(column)}"></td>
        </tr>
    </tbody>
</table>

The column names and row values are derived from the query result, so the same template can display different query shapes. In a complete application, add a separate empty-state message for successful queries that return zero rows.

6. What about security?

Validating the generated query against the exposed schema is an important safeguard, but it shouldn't replace your application's normal security controls.

Before using this in production:

• Expose only the entities and columns that the feature should be able to query.
• Use a database account with the minimum privileges necessary.
• Apply user authorization, tenant isolation, query timeouts, and sensible result-size limits in your application.
• Don't assume that a valid query is automatically authorized for every user.
• Be careful about exposing generated SQL or database details to end users.

Your application remains responsible for deciding which data a user may access and for executing the query safely.

Try Querier AI

You can now take a natural-language question, convert it into a validated Querier Select, and execute it through the database layer you already use.

The AI module is optional, and the rest of Querier remains a Java query-building library.

Explore the project and source code:

• Querier on GitHub
• Querier documentation and quick start

In the next tutorial, we'll use the same AI integration to generate an entire dashboard from one natural-language description.

Comments

[Kent Bodrov]

I’d add a tenant-isolation test around an aggregate, not just a row lookup: ask for total revenue as tenant A while the database contains A and B. The query can be a valid SELECT yet return a total that includes B. Inspecting the displayed rows wouldn’t catch that. The tenant scope needs to come from the authenticated application context, regardless of what the prompt asks.