mcp-sql

#### Project Introduction: This project is based on Spring AI and locally deployed deepseek-r1:8b, and develops an AI Agent that supports natural language query database and automatically generates statistical charts, realizing one-sentence generation of SQL, execution and return of visual results (including executed SQL and base64 encoded images), which significantly improves data statistics efficiency. #### Project Highlights: - End-to-end automation: Automatically convert natural language queries into SQL and executable scripts, and generate visual results, reducing manual intervention; - Auditable output: The front end returns and displays the executed SQL at the same time to ensure that the query is verifiable and secure; - Multi-database support: Seamlessly supports MySQL / Oracle / SQL Server through JDBC, and obtains complete table structure information as part of the Prompt; - Visualization delivery: Generate charts through process calls to Python and return them in base64, compatible with front-end instant rendering. - Support for concurrency: Implemented query isolation for multiple browsers through concurrentHashMap, without affecting each other. #### Interface as follows <img width="2560" height="1272" alt="Snipaste_2025-08-25_17-45-50" src="https://github.com/user-attachments/assets/82726c32-593d-4de4-a66b-06117c20c2ac" /> #### Save the table structure of the connection ```sql create table tb_mysql_conn ( id int auto_increment primary key, type enum ('MYSQL', 'POSTGRES', 'ORACLE', 'SQLSERVER') default 'MYSQL' not null comment '数据库类型', username varchar(100) not null comment '登录用户名', password varchar(100) not null comment '登录密码（建议加密存储）', host varchar(255) not null comment '数据库主机地址或 IP', port int unsigned default '3306' not null comment '端口号，MySQL 默认 3306', database_name varchar(100) not null comment '数据库名称', constraint uk_connection unique (type, host, port, database_name, username) ) comment 'mysql数据库' collate = utf8mb4_unicode_ci; create table tb_oracle_conn ( id int auto_increment primary key, type enum ('MYSQL', 'POSTGRES', 'ORACLE', 'SQLSERVER') default 'ORACLE' not null comment '数据库类型', username varchar(100) not null comment '登录用户名', password varchar(100) not null comment '登录密码', host varchar(255) not null comment '数据库主机地址或 IP', port int unsigned default '1521' not null comment '端口号，Oracle 默认 1521', service_name varchar(100) not null comment '数据库名称', constraint uk_connection unique (type, host, port, service_name, username) ) comment 'oracle数据库' collate = utf8mb4_unicode_ci; create table tb_sqlserver_conn ( id int auto_increment primary key, type enum ('MYSQL', 'POSTGRES', 'ORACLE', 'SQLSERVER') default 'SQLSERVER' not null comment '数据库类型', username varchar(100) not null comment '登录用户名', password varchar(100) not null comment '登录密码（建议加密存储）', host varchar(255) not null comment '数据库主机地址或 IP', port int unsigned default '1433' not null comment '端口号，sqlserver 默认 1433', database_name varchar(100) not null comment '数据库名称', schema_name varchar(50) default 'dbo' null, constraint uk_connection unique (type, host, port, database_name, username, schema_name) ) comment 'sqlserver数据库' collate = utf8mb4_unicode_ci; ``` #### Overall architecture diagram <img width="1738" height="601" alt="智能数据库查询与可视化系统总体架构" src="https://github.com/user-attachments/assets/e5e09fe8-fae1-4d6b-bda5-1b9e97029fff" /> #### Execution flow diagram <img width="1021" height="744" alt="自然语言查询到图表生成的核心流程" src="https://github.com/user-attachments/assets/a627889c-2391-4232-84a8-b2b98a987a16" /> <img width="279" height="1481" alt="执行流程图" src="https://github.com/user-attachments/assets/075b34ad-7988-42e8-85b8-00d9a1ecf0f6" />