Rust SQL查询构建器sql-builder的使用

介绍

sql-builder是一个Rust库，用于高效生成安全的动态SQL语句。它提供了类型安全的SQL构建方式，可以帮助开发者避免SQL注入攻击，同时简化复杂SQL语句的构建过程。

主要特点：

• 类型安全的SQL构建
• 支持动态条件拼接
• 自动参数化查询，防止SQL注入
• 支持多种SQL方言
• 轻量级且无运行时开销

安装

在Cargo.toml中添加依赖：

[dependencies]
sql-builder = "3.0"

基本使用方法

1. 构建简单查询

use sql_builder::SqlBuilder;

let sql = SqlBuilder::select_from("books")
    .field("title")
    .field("author")
    .and_where("price > 100")
    .order_by("published_at", false) // false表示降序
    .limit(10)
    .offset(5)
    .sql()
    .unwrap();

println!("{}", sql);
// 输出: SELECT title, author FROM books WHERE price > 100 ORDER BY published_at DESC LIMIT 10 OFFSET 5

2. 使用参数化查询

use sql_builder::SqlBuilder;

let sql = SqlBuilder::select_from("users")
    .field("id")
    .field("name")
    .and_where("age > ?".bind(&30))
    .and_where("status = ?".bind(&"active"))
    .sql()
    .unwrap();

println!("{}", sql);
// 输出: SELECT id, name FROM users WHERE age > 30 AND status = 'active'

3. 构建复杂查询

use sql_builder::SqlBuilder;

let min_price = 50;
let max_price = 200;
let category = "programming";

let sql = SqlBuilder::select_from("books")
    .field("title")
    .field("author")
    .field("price")
    .join("categories", "books.category_id = categories.id")
    .and_where("price BETWEEN ? AND ?".bind(&min_price).bind(&max_price))
    .and_where("categories.name = ?".bind(&category))
    .group_by("author")
    .having("COUNT(*) > 1")
    .sql()
    .unwrap();

println!("{}", sql);
// 输出: SELECT title, author, price FROM books JOIN categories ON books.category_id = categories.id 
// WHERE price BETWEEN 50 AND 200 AND categories.name = 'programming' GROUP BY author HAVING COUNT(*) > 1

4. 构建INSERT语句

use sql_builder::SqlBuilder;

let sql = SqlBuilder::insert_into("users")
    .field("name")
    .field("age")
    .field("email")
    .values(&["'John Doe'".into(), "30".into(), "'john@example.com'".into()])
    .sql()
    .unwrap();

println!("{}", sql);
// 输出: INSERT INTO users (name, age, email) VALUES ('John Doe', 30, 'john@example.com')

5. 构建UPDATE语句

use sql_builder::SqlBuilder;

let sql = SqlBuilder::update_table("users")
    .set("name", "'Jane Doe'")
    .set("age", "31")
    .and_where("id = 1")
    .sql()
    .unwrap();

println!("{}", sql);
// 输出: UPDATE users SET name = 'Jane Doe', age = 31 WHERE id = 1

高级用法

动态条件拼接

use sql_builder::SqlBuilder;

fn build_query(name_filter: Option<&str>, min_age: Option<i32>) -> String {
    let mut builder = SqlBuilder::select_from("users")
        .field("*");
    
    if let Some(name) = name_filter {
        builder.and_where(format!("name LIKE '%{}%'", name));
    }
    
    if let Some(age) = min_age {
        builder.and_where(format!("age >= {}", age));
    }
    
    builder.sql().unwrap()
}

println!("{}", build_query(Some("John"), Some(30)));
// 输出: SELECT * FROM users WHERE name LIKE '%John%' AND age >= 30

使用不同的SQL方言

use sql_builder::{SqlBuilder, quote};

let sql = SqlBuilder::select_from("books")
    .field("title")
    .field("author")
    .and_where(format!("category = {}", quote("fiction")))
    .sql()
    .unwrap();

println!("{}", sql);
// 输出: SELECT title, author FROM books WHERE category = 'fiction'

完整示例

下面是一个完整的示例，展示了如何使用sql-builder构建各种SQL语句：

use sql_builder::{SqlBuilder, quote};

fn main() {
    // 1. 简单查询示例
    let simple_query = SqlBuilder::select_from("products")
        .field("id")
        .field("name")
        .field("price")
        .and_where("stock > 0")
        .order_by("price", true)  // true表示升序
        .sql()
        .unwrap();
    println!("简单查询:\n{}\n", simple_query);

    // 2. 参数化查询示例
    let min_price = 100;
    let category = "electronics";
    let param_query = SqlBuilder::select_from("products")
        .field("*")
        .and_where("price > ?".bind(&min_price))
        .and_where("category = ?".bind(&category))
        .sql()
        .unwrap();
    println!("参数化查询:\n{}\n", param_query);

    // 3. 复杂查询示例
    let complex_query = SqlBuilder::select_from("orders")
        .field("orders.id")
        .field("customers.name")
        .field("SUM(order_items.quantity * order_items.price) AS total")
        .join("customers", "orders.customer_id = customers.id")
        .join("order_items", "orders.id = order_items.order_id")
        .and_where("orders.date > '2023-01-01'")
        .group_by("orders.id, customers.name")
        .having("total > 1000")
        .sql()
        .unwrap();
    println!("复杂查询:\n{}\n", complex_query);

    // 4. INSERT语句示例
    let insert_query = SqlBuilder::insert_into("users")
        .field("username")
        .field("email")
        .field("created_at")
        .values(&["'testuser'".into(), "'test@example.com'".into(), "CURRENT_TIMESTAMP".into()])
        .sql()
        .unwrap();
    println!("INSERT语句:\n{}\n", insert_query);

    // 5. UPDATE语句示例
    let update_query = SqlBuilder::update_table("products")
        .set("price", "price * 1.1")  // 涨价10%
        .and_where("category = 'premium'")
        .sql()
        .unwrap();
    println!("UPDATE语句:\n{}\n", update_query);

    // 6. 动态条件拼接示例
    let dynamic_query = build_dynamic_query(Some("apple"), Some(2.5), None);
    println!("动态查询:\n{}\n", dynamic_query);
}

fn build_dynamic_query(
    name_filter: Option<&str>,
    min_price: Option<f64>,
    max_price: Option<f64>,
) -> String {
    let mut builder = SqlBuilder::select_from("products")
        .field("*");

    if let Some(name) = name_filter {
        builder.and_where(format!("name LIKE '%{}%'", name));
    }

    if let Some(price) = min_price {
        builder.and_where(format!("price >= {}", price));
    }

    if let Some(price) = max_price {
        builder.and_where(format!("price <= {}", price));
    }

    builder.sql().unwrap()
}

注意事项

1. 虽然sql-builder可以帮助防止SQL注入，但仍需谨慎处理用户输入
2. 复杂的SQL可能需要手动编写
3. 对于大型项目，可能需要考虑使用ORM如Diesel或SQLx

sql-builder特别适合需要动态构建SQL查询的场景，它提供了比原始字符串拼接更安全、更易维护的方式。