九、用 Sqlx 访问数据库

tech2024-11-02 10

九、用 Sqlx 访问数据库　　　　前一节，我们学习了如何在 Rust 项目中集成 Sqlx，本节我们继续完善该工程，以看看如何用 Sqlx 来具体的访问数据库。

1、目录结构

. ├── Cargo.lock ├── Cargo.toml ├── README.md ├── config.yaml └── src ├── boot │ ├── db.rs │ └── mod.rs ├── main.rs └── module ├── mod.rs └── user ├── api.rs ├── bs.rs ├── dao.rs ├── mod.rs └── model.rs

2、完善工程

1）module/user/model.rs

use chrono::NaiveDateTime; use serde::{Deserialize, Serialize}; use sqlx::FromRow; use validator::Validate; #[derive(Debug, Validate, Serialize, Deserialize, FromRow)] // 增加了 sqlx::FromRow pub struct User { pub id: Option<i32>, // 改为了 i32 #[validate(length(max = 50, message = "username must be less than 50 chars."))] pub username: String, #[validate(length(min = 6, message = "password must be more than 6 chars."))] pub password: String, #[validate(length(max = 255, message = "username must be less than 255 chars."))] pub avatar: Option<String>, #[validate(length(max = 80, message = "username must be less than 80 chars."))] pub email: Option<String>, pub created_at: Option<NaiveDateTime>, }

2）module/user/dao.rs

use std::vec::Vec; use sqlx::Done; use crate::boot::db; use crate::module::user::model::User; /** * 测试接口: 查列表 */ pub async fn list() -> Vec<User> { let pool = db::get_pool().unwrap(); sqlx::query_as::<_, User>("select id, email, username, password, avatar, created_at from users") .fetch_all(pool).await.unwrap() } /** * 测试接口: 增 */ pub async fn create(user: User) -> u64 { let pool = db::get_pool().unwrap(); sqlx::query("insert into users(email, username, password, avatar) values($1, $2, $3, $4)") .bind(&user.email).bind(&user.username).bind(&user.password) .bind(&user.avatar).execute(pool).await.unwrap().rows_affected() // TODO: 1.最后插入的主键值; 2.最终使用雪花多数据中心算法 } /** * 测试接口: 查 */ pub async fn show(id: i32) -> User { let pool = db::get_pool().unwrap(); sqlx::query_as::<_, User>("select * from users where id = $1") .bind(id).fetch_one(pool).await.unwrap() } /** * 测试接口: 改 */ pub async fn update(id: i32, user: User) -> u64 { let pool = db::get_pool().unwrap(); let pg_done = sqlx::query("update users set password = $1 where id = $2") .bind(&user.password).bind(id.clone()).execute(pool).await.unwrap(); println!("Hello {}! id: {}. result: {:?}", user.username, id, pg_done.rows_affected()); return pg_done.rows_affected(); } /** * 测试接口: 删 */ pub async fn delete(id: i32) -> u64 { let pool = db::get_pool().unwrap(); sqlx::query("delete from users where id = $1").bind(id).execute(pool).await.unwrap().rows_affected() }

　　关于 sql 中的占位符，PG 是用 $1，$2，$3 … ；而 MySQL 是用 ?，?，? … 。

3）module/user/bs.rs

use crate::module::user; use crate::module::user::model::User; // use chrono::Local; /** * 测试接口: 查列表 */ pub async fn list() -> Vec<User> { let user = user::dao::list().await; return user } /** * 测试接口: 增 */ pub async fn create(user: User) -> u64 { // user.created_at = Some(Local::now().naive_utc()); user::dao::create(user).await } /** * 测试接口: 查 */ pub async fn show(id: i32) -> User { user::dao::show(id).await } /** * 测试接口: 改 */ pub async fn update(id: i32, user: User) -> u64 { user::dao::update(id, user).await } /** * 测试接口: 删 */ pub async fn delete(id: i32) -> u64 { user::dao::delete(id).await }

4）module/user/mod.rs

pub mod api; pub mod bs; // 改了这里 pub mod dao; // 改了这里 pub mod model;

5）module/user/api.rs

use actix_web::{delete, get, HttpResponse, post, put, Responder, web}; use actix_web::web::Json; use crate::module::user::model::User; use crate::module::user; /** * 测试接口: 增 */ #[get("/user/list")] pub async fn list() -> impl Responder { let users = user::bs::list().await; HttpResponse::Ok().json(users) } /** * 测试接口: 增 */ #[post("/user")] pub async fn create(user: Json<User>) -> impl Responder { let rows = user::bs::create(user.0).await; HttpResponse::Ok().json(rows) } /** * 测试接口: 查 */ #[get("/user/{id}")] pub async fn show(web::Path(id): web::Path<i32>) -> impl Responder { let user = user::bs::show(id).await; HttpResponse::Ok().json(user) } /** * 测试接口: 改 */ #[put("/user/{id}")] pub async fn update(web::Path(id): web::Path<i32>, user: Json<User>) -> impl Responder { let rows = user::bs::update(id, user.into_inner()).await; HttpResponse::Ok().json(rows) } /** * 测试接口: 删 */ #[delete("/user/{id}")] pub async fn delete(web::Path(id): web::Path<i32>) -> impl Responder { let rows = user::bs::delete(id).await; HttpResponse::Ok().json(rows) }

6）module/mod.rs

pub mod user; pub mod handler { use actix_web::dev::HttpServiceFactory; use actix_web::web; use crate::module::user; pub fn api_routes() -> impl HttpServiceFactory { web::scope("") .service(user::api::list) // 这里增加了一项 .service(user::api::create) .service(user::api::show) .service(user::api::update) .service(user::api::delete) } }

　　补充以上源码文件后，可以尝试编译和启动项目：

$ cargo run Finished dev [unoptimized + debuginfo] target(s) in 0.25s Running `target/debug/rust-demo` datasource: postgres://postgres:postgres@192.168.24.251:5432/postgres min: 5 max: 15

3、建测试表

create table users ( id serial not null constraint users_pkey primary key, email text not null, username text not null, password text not null, avatar text default ''::text not null, created_at timestamp default CURRENT_TIMESTAMP not null, constraint users_email_username_key unique (email, username) ); alter table users owner to postgres;

4、访问接口

　　工程的运行和建表都没有问题后，我们来尝试访问接口。

Post => /user

curl --location --request POST 'http://127.0.0.1:8080/user' \ --header 'Content-Type: application/json' \ --data-raw '{ "email": "xugy3@126.com", "username": "xugy4", "password": "123456", "avatar": "https://avatars3.githubusercontent.com/u/13329376?s=460&v=4" }'

Get => /user/list

curl --location --request GET 'http://127.0.0.1:8080/user/list'

Get => /user/{id}

curl --location --request GET 'http://127.0.0.1:8080/user/1'

Put => /user/{id}

curl --location --request PUT 'http://127.0.0.1:8080/user/2' \ --header 'Content-Type: application/json' \ --data-raw '{ "username": "xugy", "password": "1234567" }'

Delete => /user/{id}

curl --location --request DELETE 'http://127.0.0.1:8080/user/2'

　　好的，Rust 用 Sqlx 访问数据库的 demo 也完活！～　　

最新回复(0)