九、用 Sqlx 访问数据库
齐文林
九、用 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 也完活 ！～
类似资料：