In this article, we explore a fascinating project that demonstrates how AI agents can simplify database management tasks. This AI agent is capable of performing various database operations such as creating databases, creating tables, inserting data, and retrieving data, all through simple English statements. The agent interacts with a Derby in-memory database but is designed to be extendable to support other relational database systems.
👉 Code for this article is here
Core Implementation
The core of this implementation utilizes the Tools4AI framework, leveraging annotations like Agent and Action to define and execute database operations in response to natural English language queries. This approach abstracts the complexities of SQL into easy-to-understand English commands, allowing non-technical users to interact with databases effortlessly.
The solution uses a flexible and generic approach where English prompts are mapped to a TableData class. This class handles the details of tables, columns, and rows for various database actions. This mechanism allows the AI agent to understand, interpret, and execute complex database operations based on simple textual instructions.
Key Features
👉Source code of Java Agent is here
Dynamic Action Mapping: Actions like createDatabase(), createTables(), and insertDataInTable() are dynamically mapped to corresponding SQL queries based on English prompts. For instance:
Action: @Action(description = "Create tables")
English Prompt: “Hey, I need to maintain a record of employees with title and name.”
Mapped SQL Query:
CREATE TABLE employees (name VARCHAR(255), title VARCHAR(255));
Similarly, when inserting data:
Action: @Action(description = "Insert new data in database table")
*English Prompt: *“Insert a record for Sanjay Kapoor, who joined today as a Chef.”
Mapped SQL Query:
INSERT INTO employees (name, title) VALUES ('Sanjay Kapoor', 'Chef');
Annotations for Action Definition: The @agent and **@Action **annotations are central to this approach.
@agent: Groups a collection of related actions. In the DerbyService class, the @agent annotation is applied with groupName = "Database related actions" to indicate that all actions related to database management fall under this agent.
@Action: Defines a specific action that the AI agent will perform, such as creating tables or inserting data. Each action is annotated with a description that guides the agent to interpret and map the English query to SQL commands.
TableData Class: The TableData class is a core component, representing the structure of database tables and their data. It contains:
tableName: The name of the table.
headerList: A list of column data, represented by ColumnData.
rowDataList: A list of row data, represented by RowData.
Here’s a sample structure for the TableData class:
package io.github.vishalmysore.data;
import com.t4a.annotations.ListType;
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;
import java.util.ArrayList;
import java.util.List;
@Getter
@Setter
@ToString
public class TableData {
private String tableName;
@ListType(ColumnData.class)
private List<ColumnData> headerList;
@ListType(RowData.class)
private List<RowData> rowDataList;
}
Example Code Implementation
Here’s a simplified version of the DerbyService class that leverages the @agent and @Action annotations to dynamically execute database actions based on natural language queries:
package io.github.vishalmysore.service;
import com.t4a.annotations.Action;
import com.t4a.annotations.Agent;
import io.github.vishalmysore.data.ColumnData;
import io.github.vishalmysore.data.RowData;
import io.github.vishalmysore.data.TableData;
import io.github.vishalmysore.data.User;
import lombok.extern.java.Log;
import org.springframework.stereotype.Service;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@Log
@Service
@Agent(groupName = "Database related actions")
public class DerbyService {
private static final String JDBC_URL = "jdbc:derby:memory:myDB;create=true";
private static final String JDBC_DRIVER = "org.apache.derby.jdbc.ClientDriver";
// this will be dynamically mapped by AI
@Action(description = "Create tables")
public String createTables(TableData tableData) {
..
}
}
Technologies Used
Java: The core programming language used to implement the solution.
Spring Boot: The framework that facilitates building the application and providing database services.
Apache Derby: An in-memory relational database used for demonstration purposes.
Tools4AI: A framework that enables the creation of AI-based actions for various tasks, including database management.
Conclusion
This project showcases the power of AI agents in automating database management tasks. By mapping simple English queries to complex SQL operations, the system allows non-technical users to interact with databases intuitively. This is just the beginning, as the flexibility of this system can be extended to other domains beyond database management, such as processing text, generating reports, or handling various types of unstructured data.
While the current implementation is a proof-of-concept and not production-ready, it highlights the potential of AI agents in streamlining tasks that traditionally require specialized knowledge. As AI technologies continue to evolve, we can expect such systems to become more robust, extending their capabilities to a broader range of business applications.
Top comments (0)