huiyu-db-mapper-sqlite 0.1.1

Database Query Tool with Deadpool. Query Wrapper Tool. Orm Tool
Documentation

持久层软件

支持数据库

  • Sqlite
  • Mysql
  • PostgreSql
  • Oracle(>12)

功能

  • 多数据源切换
  • 事务
  • 分页
  • 查询
  • 更新
  • 删除
  • 插入
  • 自定义查询

引入

[workspace.dependencies]
huiyu-db-mapper = {version = "0.1.0", features = ["sqlite"]}

注册数据源

fn init_postgres(){
    println!("init postgres");
//     postgresql://user:pass@localhost:5432/mydb?sslmode=require
    let db_config_postgres = DbConfig::new(DbType::Postgres,
                                           "postgres".to_string(),
                                           Some("postgresql://user:pass@localhost:5432/dbname".to_string()),
                                           None,
                                           None,
    );
    DbTypeWrapper::register_dbs(vec![db_config_postgres]).expect("Failed to register db");
}
fn init_mysql(){
    println!("init mysql");
    // "mysql://root:password@localhost:3306/mydb"
    let db_config_mysql = DbConfig::new(DbType::Mysql,
                                        "mysql".to_string(),
                                        Some("mysql://root:123456@10.150.6.7:3306/dbname".to_string()),
                                        None,None
    );
    DbTypeWrapper::register_dbs(vec![db_config_mysql]).expect("Failed to register db");
}
fn init_sqlite(){
    println!("init sqlite");
    let db_config_sqlite = DbConfig::new(
        DbType::Sqlite,
        "sqlite".to_string(),
        Some("E:\\test\\dbname.db".to_string()),
        None,
        None
    );
    DbTypeWrapper::register_dbs(vec![db_config_sqlite]).expect("Failed to register db");
}

fn init_oracle(){
    println!("init oracle");
    let db_config_oracle = DbConfig::new(DbType::Oracle,
                                        "oracle".to_string(),
                                        Some("localhost:1521/orcl".to_string()),
                                        Some("user".to_string()),Some("password".to_string()),
    );
    DbTypeWrapper::register_dbs(vec![db_config_oracle]).expect("Failed to register db");
}

其中url格式为：

名称 url格式
mysql mysql://localhost:3306/mydb
postgres postgresql://localhost:5432/mydb?sslmode=require
oracle host:port/service_name
oracle host/service_name
oracle host:port:sid
oracle //host:port/service_name

查询功能

QueryWrapper 查询条件构造器

名称 用法
eq 等于 (=)
ne 不等于 (<>)
lt 小于 (<)
le 小于等于 (<=)
gt 大于 (>)
ge 大于等于 (>=)
between 在两者之间 (between ... and ...)
like 模糊匹配，支持通配符 (like '%...%')
likeLeft 左模糊匹配 (like '%...')
likeRight 右模糊匹配 (like '...%')
notLike 不匹配指定模式 (not like)
notLikeLeft 不匹配左模糊模式 (not like '%...')
notLikeRight 不匹配右模糊模式 (not like '...%')
in 在指定集合中 (in (...))
inSql 使用子查询作为 in 的条件 (in (select ...))
notIn 不在指定集合中 (not in (...))
notInSql 使用子查询作为 not in 的条件 (not in (select ...))
isNotNull 不为空 (is not null)
isNull 为空 (is null)
applySql 直接拼接自定义 SQL 片段
existsSql 使用 exists 子查询 (exists (select ...))
notExistsSql 使用 not exists 子查询 (not exists (select ...))
or_wrapper 以 OR 逻辑包裹一组条件
and_wrapper 以 AND 逻辑包裹一组条件 
    let mut query_wrapper = QueryWrapper::<RoleEntity>::new();
    query_wrapper = query_wrapper.eq("id", 1);
    query_wrapper = query_wrapper.ne(RoleEntity::ROLE_NAME, "role_001");
    query_wrapper = query_wrapper.gt(RoleEntity::STATUS, 0);
    query_wrapper = query_wrapper.lt(RoleEntity::STATUS, 1);
    query_wrapper = query_wrapper.order_by(RoleEntity::STATUS, false);
    
    query_wrapper = query_wrapper.or_wrapper(|mut query_wrapper1| {
        query_wrapper1 = query_wrapper1.eq(RoleEntity::ID, 1);
        query_wrapper1 = query_wrapper1.eq(RoleEntity::ROLE_NAME, 1);
        query_wrapper1
    });

生成sql:

select id,role_name,role_code,description,sort_order,status,is_system,create_time,update_time from 
t_role where id = ?  AND  role_name != ?  AND  status > ?  AND  status < ?  AND  (id = ?  OR  role_name = ?) order by status DESC

宏

mapper宏

#[mapper(RoleEntity)]
pub struct RoleMapper;

这样该mapper就具有以下方法,其中RoleEntity是实体类，由Entity宏标注

// select * from $table_name where $id = ?
async fn select_by_key(key: &E::K) -> Result<Option<E>, DatabaseError>;

// select * from $table_name where $id in (?,...)
async fn select_by_keys(keys: &Vec<E::K>) -> Result<Vec<E>, DatabaseError>;

// delete from $table_name where $id = ?
async fn delete_by_key(key: &E::K) -> Result<u64, DatabaseError>;

// delete from $table_name where $id in (?,...)
async fn delete_by_keys(keys: &Vec<E::K>) -> Result<u64, DatabaseError>;

// update $table_name set $column_name = ? where id = ?
async fn update_by_key(e: &E) -> Result<u64, DatabaseError>;

// insert $table_name into ($id,$column,...) values (?,?,...)
async fn insert(e: &mut E) -> Result<Option<E::K>, DatabaseError>;

// insert $table_name into ($id,$column,...) values (?,?,...),(?,?,...)
async fn insert_batch(entities: Vec<E>) -> Result<u64, DatabaseError>;

// select count(*) from (select * from $table_name where $column = ? ...)
// select * from $table_name where $column = ? ... limit ?,?
async fn select_page<'a>(page: Page,query_wrapper: &QueryWrapper<'a, E>,) -> Result<PageRes<E>, DatabaseError>;

// select * from $table_name where $column = ? ...
async fn select<'a>(query_wrapper: &QueryWrapper<'a, E>,) -> Result<Vec<E>, DatabaseError>;

// select * from $table_name where $column = ? ... limit 1
async fn select_one<'a>(query_wrapper: &QueryWrapper<'a, E>,) -> Result<Option<E>, DatabaseError>;

// update $table_name set $column_name = ? where $column = ? ...
async fn update<'a>(e: &E, query_wrapper: &QueryWrapper<'a, E>,) -> Result<u64, DatabaseError>;

// update $table_name set $column_name = ? where $column = ? ...
async fn update_with_null<'a>(&self, e: &E, query_wrapper: &QueryWrapper<'a, E>,) -> Result<u64, DatabaseError>;

// delete from $table_name where $column = ? ...
async fn delete<'a>(query_wrapper: &QueryWrapper<'a, E>) -> Result<u64, DatabaseError>;

transactional 事务宏


    let res = transactional!({
        UserRoleMapper::insert(&mut user_role1).await?;
        UserRoleMapper::insert(&mut user_role2).await?;
        Ok(())
    })?;

datasource 数据源宏

#[datasource("sqlite")]
async fn queries()->Result<(),DatabaseError>{
}

queries方法里面的数据库操作都会使用sqlite名称的数据源

Mapping 查询返回映射宏

use huiyu_db_mapper::huiyu_db_mapper_macros::Mapping;

#[derive(Default,Mapping,Serialize,Deserialize)]
pub struct RoleDTO{
    pub id: Option<String>,
    pub username: Option<String>,
}

必须是struct，成员必须Option,搭配select_impl使用

Entity 实体宏

use huiyu_db_mapper::huiyu_db_mapper_macros::Entity;
#[derive(Clone, Debug, Default, Serialize, Deserialize, Entity)]
#[table(name = "t_role")]
pub struct RoleEntity {
    #[id(column = "id")]
    pub id: Option<String>,  // 角色ID，varchar主键

    #[field(column = "role_name")]
    pub role_name: Option<String>,  // 角色名称

    #[field(column = "role_code")]
    pub role_code: Option<String>,  // 角色编码
}

指定表名，指定id和列名

select_impl 自定义查询宏

impl RoleMapper {
    select_impl! {

        #[select("select * from t_role where id = ?")]
        async fn query_role_dtos(id: String) -> Result<Vec<RoleDTO>, DatabaseError>;

        #[select("select * from t_role where id = ?")]
        async fn query_role_page(page: Page, name: String) -> Result<PageRes<RoleDTO>, DatabaseError>;
        
        #[select("select * from t_role where role_code = ? and status = ?")]
        async fn query_role_first(name: String, status: i8) -> Result<Option<RoleDTO>, DatabaseError>;
        
        #[select("select role_name from t_role where role_code = ? and status = ?")]
        #[value]   // 标记为简单值类型
        async fn query_role_name(name: String, status: i8) -> Result<Option<String>, DatabaseError>;
        
        #[select("select * from t_role where 1=1  #{qw}")]
        async fn query_role_dtos_by_query_wrapper<'a>(name:String,query_wrapper: &OccupyQueryMapper<'a>) -> Result<Vec<RoleDTO>, DatabaseError>;
        
        #[select("select * from t_role  where 1=1  #{qw}")]
        async fn query_role_page_query_wrapper<'a>(page: Page,name:String,name1:String,   query_wrapper: &OccupyQueryMapper<'a>) -> Result<PageRes<RoleDTO>, DatabaseError>;
        
        #[select("select * from t_role where name like concat('%',?@,'%')  #{qw}")]
        async fn query_role_first_query_wrapper<'a>(name:String,query_wrapper: &OccupyQueryMapper<'a>) -> Result<Option<RoleDTO>, DatabaseError>;
        
        #[select("select role_name from t_user u left join t_user_role ur on ur.user_id = u.id left join t_role r on r.id = ur.role_id where 1=1 and #{qw}")]
        #[value]   // 标记为简单值类型
        async fn query_role_name_query_wrapper<'a>(name:String,query_wrapper: &OccupyQueryMapper<'a>) -> Result<Option<String>, DatabaseError>;
        
        // 支持多个 OccupyQueryMapper 的示例
        #[select("select * from t_role where 1=1 and role_code =?#  #{qw}  #{qw}")]
        async fn query_role_by_multiple_wrappers<'a>(code:String,wrapper1: &OccupyQueryMapper<'a>, wrapper2: &OccupyQueryMapper<'a>) -> Result<Vec<RoleDTO>, DatabaseError>;
    }
    
    execute_impl!{
        #[sql("update t_role set role_code = ? where id = ?  #{qw}  #{qw}")]
        async fn update_role_code(role_code: String, role_code1: String,query_wrapper: &OccupyQueryMapper<'_>,query_wrapper1: &OccupyQueryMapper<'_>) -> Result<u64, DatabaseError>;
        #[sql("create table t_test(id: int)")]
        async fn create_table_test(id: i64) -> Result<u64, DatabaseError>;
        #[sql("CREATE TABLE Employees_?@ (
                EmployeeID INTEGER PRIMARY KEY,
                Name TEXT NOT NULL,
                Age INTEGER
            );
        ")]
        async fn create_table_employees(idx:i64) -> Result<u64, DatabaseError>;
    }
}

例子地址

https://github.com/nihility23/huiyu-db-mapper/tree/master/huiyu-db-mapper-example

参数值支持：

1.普通值.可以多个与？搭配，按顺序替换

2.OccupyQueryMapper，可以多个，通过#{qw}按顺序替换

3.普通值？搭配规则

[select(select *from where id = ?)]
?:参与预编译，后以参数传入
sql:select *from where id = ?  
sql执行参数：abc

[select(select *from t_user where id = ?#)]
?#：不参与预编译，先替换后执行,替换时候加单引号,传入参数("abc")
sql:select *from where id = 'abc'
sql执行参数：无

[select(select *from ?@ where id = ?)]
?@：不参与预编译，先替换后执行,替换时候不加单引号,传入参数("t_user","1")
sql:select *from t_user where id = ?
sql执行参数：1

[select(select name from ?& where id = ?)]
?&：不参与预编译，先替换后执行,替换是增加双引号,传入参数("t_user","1")
sql:select *from "t_user" where id = ?
sql执行参数：1

返回值支持：

1.查询所有

返回值必须Result<PageRes<T>, DatabaseError>, T必须实现Mapping

2.查询分页

第一个参数必须为Page,返回值必须Result<PageRes<T>, DatabaseError>, T必须实现Mapping

3.查询单个

返回值必须为Result<Option<T>, DatabaseError>, T必须实现Mapping

4.查询简单值

返回值必须为Result<Option<String>, DatabaseError>，必须标记为value

未来支持

  • 直接读取配置文件并注册数据源
  • 优化默认数据源

注意事项

1.实体及映射成员必须Option包裹

2.事务宏必须返回Result<T,DatasourceError>，以确定是否回滚

3.必须有个名称为default的数据源，后期考虑优化，找不到对应名称使用第一个注册的数据源