# sql-helper

**Repository Path**: mylovextt/sql-helper

## Basic Information

- **Project Name**: sql-helper
- **Description**: No description available
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: main
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2026-01-07
- **Last Updated**: 2026-01-07

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# SQL-Helper: A Java Library for SQL Validation and JSON Conversion

**SQL-Helper** is a lightweight, powerful Java library designed to parse, validate, and convert SQL `SELECT` statements. Built on top of **Apache Calcite**, it provides a robust way to handle SQL programmatically, making it ideal for applications that dynamically generate or process SQL queries.

## ✨ Features

- **SQL Syntax Validation**: Checks if a SQL string conforms to standard SQL grammar (with a MySQL flavor).
- **Schema-Aware Semantic Validation**: Connects to your database via JDBC to validate that tables, columns, and functions in a query actually exist.
- **JSON to SQL Conversion**: Define complex `SELECT` queries using a structured JSON format and convert them into executable SQL strings.
- **SQL to JSON Conversion**: Parse existing SQL `SELECT` statements into a structured, easy-to-process JSON format.
- **Extensible Design**: Easily adaptable to other SQL dialects supported by Apache Calcite.
- **Lightweight and Self-Contained**: Packaged as a single JAR with minimal dependencies.

## 🚀 Getting Started

### Prerequisites

- Java 17 or higher
- Maven 3.6+

### How to Use

#### 1. Initialize the Validator

To perform semantic validation, you need to connect to your database. The library uses JDBC to fetch schema metadata.

```java
import com.sqlhelper.mysql.MySqlValidator;
import com.sqlhelper.entities.ValidationResult;

// Replace with your database connection details
String jdbcUrl = "jdbc:mysql://your-host:3306/your_db";
String username = "your_user";
String password = "your_password";
String defaultSchema = "your_db";

MySqlValidator validator = new MySqlValidator(jdbcUrl, username, password, defaultSchema);
```

#### 2. Validate a SQL Query

Use the `validateSql` method to check for both syntax and semantic errors.

```java
String sql = "SELECT name, email FROM users WHERE id = 1";
ValidationResult result = validator.validateSql(sql);

if (result.isValid()) {
    System.out.println("SQL is valid!");
} else {
    System.err.println("SQL is invalid: " + result.getErrorMessage());
}
```

**Example of an invalid query:**

```java
String invalidSql = "SELECT name, non_existent_column FROM users";
ValidationResult result = validator.validateSql(invalidSql);
// result.getErrorMessage() will contain:
// "SQL Semantic Error: Column 'NON_EXISTENT_COLUMN' not found in any table"
```

#### 3. Convert JSON to SQL

Define your query using a structured JSON. This is useful for building dynamic queries from a UI or an API.

```java
import com.sqlhelper.entities.SelectQuery;
import com.sqlhelper.util.JsonUtil;
import com.sqlhelper.util.SqlGenerator;

String jsonQuery = """
{
  "select": [{"expr": "id"}, {"expr": "name"}],
  "from": {"table": "users", "alias": "u"},
  "where": {"expr": "age > 30"},
  "limit": 10
}
""";

// Convert JSON to a Java object
SelectQuery query = JsonUtil.fromJson(jsonQuery);

// Generate the SQL string
String sql = SqlGenerator.toSql(query);
// Output: SELECT id, name FROM users u WHERE age > 30 LIMIT 10
```

#### 4. Convert SQL to JSON

Parse an existing SQL string to get a structured `SelectQuery` object, which can then be easily converted to JSON.

```java
import com.sqlhelper.util.SqlParserUtil;
import com.sqlhelper.util.JsonUtil;

String sql = "SELECT id, name FROM users u WHERE age > 30 LIMIT 10";

// Parse SQL to a SelectQuery object
SelectQuery query = SqlParserUtil.parseSqlToQuery(sql);

// Convert the object to a JSON string
String json = JsonUtil.toJson(query);
System.out.println(json);
```

**Output JSON:**
```json
{
  "distinct": false,
  "select": [
    { "expr": "id" },
    { "expr": "name" }
  ],
  "from": {
    "table": "users",
    "alias": "u"
  },
  "where": {
    "expr": "`u`.`age` > 30"
  },
  "limit": 10
}
```

## JSON Structure Reference

The library uses the following JSON structure to represent a `SELECT` query:

| Field      | Type             | Description                                                                 |
|------------|------------------|-----------------------------------------------------------------------------|
| `distinct` | boolean          | `true` if the query uses `SELECT DISTINCT`. Default is `false`.             |
| `select`   | Array \<Object\> | A list of columns to select. Each object has `expr` (e.g., "id", "COUNT(*)") and an optional `alias`. |
| `from`     | Object           | The main table. Has `table` (name or subquery) and an optional `alias`.     |
| `joins`    | Array \<Object\> | A list of joins. Each join has `type` (LEFT, RIGHT, INNER), `table`, `alias`, and an `on` condition. |
| `where`    | Object           | A condition object. Can be nested with `operator` ("AND", "OR") and `conditions` array, or a simple `expr`. |
| `groupBy`  | Array \<String\> | A list of columns to group by.                                              |
|  `having`  | Object           | A condition object for filtering after grouping (same structure as `where`).|
| `orderBy`  | Array \<Object\> | A list of columns to order by. Each object has `expr` and `direction` ("ASC", "DESC"). |
| `limit`    | number           | The maximum number of rows to return.                                       |
| `offset`   | number           | The number of rows to skip before starting to return rows.                  |