第四章：数据库 CRUD 操作与分页查询

4.1 文章服务层

// src/services/article_service.rs
use sqlx::PgPool;
use crate::models::entity::Article;
use crate::models::dto::{ArticleDto, CreateArticleDto, PageResult};

pub struct ArticleService {
    pool: PgPool,
}

impl ArticleService {
    pub fn new(pool: PgPool) -> Self {
        Self { pool }
    }

    /// 分页查询文章
    pub async fn list_articles(
        &self,
        page: u64,
        page_size: u64,
        category: Option<&str>,
        keyword: Option<&str>,
    ) -> Result<PageResult<Article>, sqlx::Error> {
        let offset = (page - 1) * page_size;

        // 构建动态查询
        let mut count_sql = String::from("SELECT COUNT(*) FROM article_models WHERE 1=1");
        let mut query_sql = String::from(
            "SELECT * FROM article_models WHERE 1=1"
        );

        let mut params: Vec<String> = Vec::new();

        if let Some(cat) = category {
            let idx = params.len() + 1;
            count_sql.push_str(&format!(" AND category = ${}", idx));
            query_sql.push_str(&format!(" AND category = ${}", idx));
            params.push(cat.to_string());
        }

        if let Some(kw) = keyword {
            let idx = params.len() + 1;
            count_sql.push_str(&format!(" AND title ILIKE ${}", idx));
            query_sql.push_str(&format!(" AND title ILIKE ${}", idx));
            params.push(format!("%{}%", kw));
        }

        query_sql.push_str(" ORDER BY create_time DESC");
        query_sql.push_str(&format!(" LIMIT ${} OFFSET ${}", params.len() + 1, params.len() + 2));

        // 构建查询
        let mut count_query = sqlx::query_scalar::<_, i64>(&count_sql);
        let mut data_query = sqlx::query_as::<_, Article>(&query_sql);

        for param in &params {
            count_query = count_query.bind(param);
            data_query = data_query.bind(param);
        }

        let total: i64 = count_query
            .bind(&params)
            .fetch_one(&self.pool)
            .await?;

        let items = data_query
            .bind(page_size as i64)
            .bind(offset as i64)
            .fetch_all(&self.pool)
            .await?;

        Ok(PageResult {
            items,
            total: total as u64,
            page,
            page_size,
        })
    }
}

4.2 数据传输对象

// src/models/dto.rs
use serde::{Deserialize, Serialize};

#[derive(Debug, Deserialize)]
pub struct CreateArticleDto {
    pub title: String,
    pub content: String,
    pub category: Option<String>,
    pub tags: Option<Vec<String>>,
}

#[derive(Debug, Deserialize)]
pub struct ArticleQuery {
    pub page: Option<u64>,
    pub page_size: Option<u64>,
    pub category: Option<String>,
    pub keyword: Option<String>,
}

#[derive(Debug, Serialize)]
pub struct PageResult<T: Serialize> {
    pub items: Vec<T>,
    pub total: u64,
    pub page: u64,
    pub page_size: u64,
}

#[derive(Debug, Serialize)]
pub struct ApiResult<T: Serialize> {
    pub code: i32,
    pub message: String,
    pub data: Option<T>,
}

impl<T: Serialize> ApiResult<T> {
    pub fn success(data: T) -> Self {
        ApiResult {
            code: 0,
            message: "success".into(),
            data: Some(data),
        }
    }

    pub fn error(code: i32, message: &str) -> Self {
        ApiResult {
            code,
            message: message.into(),
            data: None,
        }
    }
}

4.3 事务操作

pub async fn create_article_with_tags(
    &self,
    dto: CreateArticleDto,
) -> Result<Article, sqlx::Error> {
    let mut tx = self.pool.begin().await?;

    let article = sqlx::query_as::<_, Article>(
        "INSERT INTO article_models (title, content, category, tags)
         VALUES ($1, $2, $3, $4)
         RETURNING *"
    )
    .bind(&dto.title)
    .bind(&dto.content)
    .bind(&dto.category)
    .bind(&dto.tags.map(|t| t.join(",")))
    .fetch_one(&mut *tx)
    .await?;

    tx.commit().await?;
    Ok(article)
}

4.4 批量操作

pub async fn batch_update_category(
    &self,
    old_category: &str,
    new_category: &str,
) -> Result<u64, sqlx::Error> {
    let result = sqlx::query(
        "UPDATE article_models SET category = $1 WHERE category = $2"
    )
    .bind(new_category)
    .bind(old_category)
    .execute(&self.pool)
    .await?;

    Ok(result.rows_affected())
}

下一章将实现 JWT 认证中间件。