mydb_sqlx/
base_mapper.rs

1use std::marker::PhantomData;
2use sqlx::mysql::MySqlRow;
3use serde_derive::{ Serialize, Deserialize };
4use sqlx::{ query, query_as, query_scalar };
5use sqlx::{ Error, FromRow };
6use sqlx::MySqlPool;
7use sqlx_core::mysql::MySqlQueryResult;
8use crate::transform::{ struct_to_hashmap, struct_to_btreemap };
9use crate::BaseEntity;
10use core::fmt::Debug;
11
12pub static SELECT_FROM: &'static str = "SELECT * FROM";
13pub static INSERT_INTO: &'static str = "INSERT INTO";
14pub static UPDATE: &'static str = "UPDATE";
15pub static DELETE_FROM: &'static str = "DELETE FROM";
16
17pub fn mapper<T> (db: MySqlPool) -> BaseMapper<T> 
18where 
19T: for<'a> FromRow<'a, MySqlRow> + Send + Unpin + Debug + serde::Serialize + BaseEntity
20{
21  BaseMapper::<T>::new(db)
22}
23
24#[derive(Debug, Default, Serialize, Deserialize)]
25pub struct PageData<T: BaseEntity> {
26  pub total: i64,
27  pub data: Vec<T>,
28}
29
30#[derive(Debug, Clone)]
31pub struct BaseMapper<T: BaseEntity> {
32  db: MySqlPool,
33  table_name: &'static str,
34  _marker: PhantomData<T>,
35}
36
37impl<T: for<'a> FromRow<'a, MySqlRow> + Send + Unpin + Debug + serde::Serialize + BaseEntity>
38BaseMapper<T>
39{
40  pub fn new(db: MySqlPool) -> Self {
41    Self {
42      db,
43      table_name: T::table_name(),
44      _marker: PhantomData,
45     }
46  }
47
48/******************************************** INSERT MAPPER ********************************************/
49  pub async fn insert(self, entity: T) -> Result<MySqlQueryResult, Error> {
50    let mut key: Vec<String>= Vec::new();
51    let mut value: Vec<String> = Vec::new();
52
53    for (k, v) in struct_to_hashmap(entity) {
54      if v == "null" {
55        continue;
56      }
57      key.push(k);
58      value.push(format!("{}", v));
59    };
60
61    let sql_str = format!(
62      "{} {} ( {} ) VALUES ( {} )",
63      INSERT_INTO, self.table_name, key.join(", "), value.join(", ").replace('\"', "'")
64    );
65
66    println!("insert sql: {}", &sql_str);
67    let mut pool = self.db.acquire().await?;
68    match query(&sql_str).execute(&mut pool).await {
69      Ok(res) => Ok(res),
70      Err(err) => Err(err),
71    }
72  }
73
74  pub async fn insert_batch_some_column(self, entity_vec: Vec<T>) -> Result<MySqlQueryResult, Error> {
75    let mut keys: Vec<String> = Vec::new();
76    let mut values: Vec<String> = Vec::new();
77    let mut key_lock: bool = false;
78
79    entity_vec
80    .iter()
81    .for_each(|entity| {
82      let mut value: Vec<String> = Vec::new();
83      for (k, v) in struct_to_btreemap(entity) {
84        value.push(format!("{}", v));
85        if key_lock == false {
86          keys.push(k);
87        }
88      };
89      values.push(format!("({})", value.join(", ").replace('\"', "'")));
90      key_lock = true;
91    });
92
93    let sql_str = format!(
94      "{} {} ({}) VALUES {}",
95      INSERT_INTO, self.table_name, keys.join(", "), values.join(", ")
96    );
97
98    println!("insert_batch_some_column sql:{}", &sql_str);
99
100    let mut pool = self.db.acquire().await?;
101    match query(&sql_str).execute(&mut pool).await {
102      Ok(res) => Ok(res),
103      Err(err) => Err(err),
104    }
105  }
106  /******************************************** INSERT MAPPER ********************************************/
107
108  /******************************************** UPDATE MAPPER ********************************************/
109  pub async fn update_by_id(self, entity: T) -> Result<MySqlQueryResult, Error> {
110    let mut id: String = format!("");
111    let mut set_value = Vec::new();
112
113    for (k, v) in struct_to_hashmap(entity) {
114      if k == "id" {
115        id = format!("{}", v);
116        continue;
117      }
118
119      if k == "create_time" || k == "update_time" || v.is_null() {
120        continue;
121      }
122      
123      set_value.push(format!("{} = {}", k, v));
124    }
125
126    let sql_str = format!(
127      "{} {} SET {} WHERE id = {}",
128      UPDATE, self.table_name, set_value.join(", ").replace('\"', "'"), id,
129    );
130
131    println!("update_by_id sql:{}", &sql_str);
132
133    let mut pool = self.db.acquire().await?;
134    match query(&sql_str).execute(&mut pool).await {
135      Ok(res) => Ok(res),
136      Err(err) => Err(err),
137    }
138  }
139
140  pub async fn update(self, entity: T, wrapper: String) -> Result<MySqlQueryResult, Error> {
141    let mut set_value = Vec::new();
142
143    for (k, v) in struct_to_hashmap(entity) {
144      if k == "id" || k == "create_time" || k == "update_time" || v.is_null() {
145        continue;
146      }
147      set_value.push(format!("{} = {}", k, v));
148    }
149
150    let sql_str = format!(
151      "{} {} SET {} WHERE is_deleted = '0' {}",
152      UPDATE, self.table_name, set_value.join(", ").replace('\"', "'"), h_wrapper(wrapper),
153    );
154
155    println!("update sql:{}", &sql_str);
156
157    let mut pool = self.db.acquire().await?;
158    match query(&sql_str).execute(&mut pool).await {
159      Ok(res) => Ok(res),
160      Err(err) => Err(err),
161    }
162  }
163
164  /******************************************** UPDATE MAPPER ********************************************/
165
166  /******************************************** SELECT MAPPER ********************************************/
167
168  // 分页条件查询
169  pub async fn select_page(self, page: String, wrapper: String) -> Result<PageData<T>, Error> {
170    let mut err: Option<Error> = None;
171    
172    let sql_str = format!(
173      "{} {} WHERE is_deleted = '0' {}{}",
174      SELECT_FROM,
175      self.table_name,
176      h_wrapper(wrapper.clone()),
177      page,
178    );
179
180    println!("select_page sql: {}", &sql_str);
181    
182    let mut page_data: PageData<T> = PageData {
183      total: 0,
184      data: vec![],
185    };
186
187    let mut pool = self.db.acquire().await?;
188
189    match self.total(wrapper).await {
190      Ok(res) => page_data.total = res,
191      Err(e) => err = Some(e),
192    };
193    
194    match query_as::<_, T>(&sql_str).fetch_all(&mut pool).await {
195      Ok(res) => page_data.data = res,
196      Err(e) => err = Some(e),
197    };
198
199    if let Some(e) = err { return Err(e); };
200    Ok(page_data)
201  }
202
203  // 通过条件查询所有数据
204  pub async fn select_list(self, wrapper: String) -> Result<Vec<T>, Error> {
205    let sql_str = format!(
206      "{} {} WHERE is_deleted = '0' {}",
207      SELECT_FROM, self.table_name, h_wrapper(wrapper),
208    );
209    println!("select_list sql: {}", &sql_str);
210    let mut pool = self.db.acquire().await?;
211    match query_as::<_, T>(&sql_str).fetch_all(&mut pool).await {
212      Ok(res) => Ok(res),
213      Err(err) => Err(err),
214    }
215  }
216
217  // 自定义sql 查询列表
218  pub async fn select_list_custom(self, sql_str: String) -> Result<Vec<T>, Error> {
219    let mut pool = self.db.acquire().await?;
220    match query_as::<_, T>(&sql_str).fetch_all(&mut pool).await {
221      Ok(res) => Ok(res),
222      Err(err) => Err(err),
223    }
224  }
225
226  // 根据条件 查询一次
227  pub async fn select_one(self, wrapper: String) -> Result<Option<T>, Error> {
228    let sql_str = format!(
229      "{} {} WHERE is_deleted = '0' {} LIMIT 1",
230      SELECT_FROM, self.table_name, h_wrapper(wrapper),
231    );
232    println!("select_list sql: {}", &sql_str);
233    let mut pool = self.db.acquire().await?;
234    match query_as::<_, T>(&sql_str).fetch_optional(&mut pool).await {
235      Ok(res) => Ok(res),
236      Err(err) => Err(err),
237    }
238  }
239
240  // 根据条件查询一条数据
241  pub async fn select_one_custom(self, sql_str: String) -> Result<Option<T>, Error> {
242    let mut pool = self.db.acquire().await?;
243    match query_as::<_, T>(&sql_str).fetch_optional(&mut pool).await {
244      Ok(res) => Ok(res),
245      Err(err) => Err(err),
246    }
247  }
248
249  // ids 批量查询
250  pub async fn select_batch_ids(self, ids: Vec<u64>) -> Result<Vec<T>, Error> {
251    let ids = ids.iter().map(|&n| format!("{}", n)).collect::<Vec<String>>().join(",");
252    let sql_str = format!(
253      "{} {} WHERE id IN ({})",
254      SELECT_FROM, self.table_name, ids
255    );
256    println!("select_batch_ids sql: {}", &sql_str);
257    let mut pool = self.db.acquire().await?;
258    match query_as::<_, T>(&sql_str).fetch_all(&mut pool).await {
259      Ok(res) => Ok(res),
260      Err(err) => Err(err),
261    }
262  }
263
264  // 通过包装器查询单个数据
265  pub async fn select_by_map(self, wrapper: String) -> Result<Option<T>, Error> {
266
267    let sql_str = format!(
268      "{} {} WHERE is_deleted = '0' {}",
269      SELECT_FROM, self.table_name, h_wrapper(wrapper),
270    );
271    println!("select_by_map sql: {}", &sql_str);
272    let mut pool = self.db.acquire().await?;
273    match query_as::<_, T>(&sql_str).fetch_optional(&mut pool).await {
274      Ok(res) => Ok(res),
275      Err(err) => Err(err),
276    }
277  }
278
279  // id 查询
280  pub async fn select_by_id(self, id: u64) -> Result<Option<T>, Error> {
281    let sql_str = format!(
282      "{} {} WHERE id = {} AND is_deleted = 0",
283      SELECT_FROM, self.table_name, id
284    );
285    println!("select_by_id sql: {}", &sql_str);
286    let mut pool = self.db.acquire().await?;
287    match query_as::<_, T>(&sql_str).fetch_optional(&mut pool).await {
288      Ok(res) => Ok(res),
289      Err(err) => Err(err),
290    }
291  }
292
293  // 查询总数
294  pub async fn total(self, wrapper: String) -> Result<i64, Error> {
295    let sql_str = format!("SELECT COUNT(*) FROM {} WHERE is_deleted = 0 {}", self.table_name ,h_wrapper(wrapper));
296    println!("total sql: {}", &sql_str);
297    let mut pool = self.db.acquire().await?;
298    match query_scalar(&sql_str).fetch_one(&mut pool).await {
299      Ok(res) => Ok(res),
300      Err(err) => Err(err),
301    }
302  }
303
304  /******************************************** SELECT MAPPER ********************************************/
305
306  /******************************************** DELETE MAPPER ********************************************/
307
308
309  pub async fn delete_by_id(self, id: u64) -> Result<MySqlQueryResult, Error> {
310    let sql_str = format!(
311      "{} {} WHERE id = {}",
312      DELETE_FROM, self.table_name, id
313    );
314    println!("delete_by_id sql: {}", &sql_str);
315    let mut pool = self.db.acquire().await?;
316    match query(&sql_str).execute(&mut pool).await {
317      Ok(res) => Ok(res),
318      Err(err) => Err(err),
319    }
320  }
321
322  pub async fn delete_batch_ids(self, ids: Vec<u64>) -> Result<MySqlQueryResult, Error> {
323    let ids = ids.iter().map(|&n| format!("{}", n)).collect::<Vec<String>>().join(",");
324    let sql_str = format!(
325      "{} {} WHERE id IN ({})",
326      DELETE_FROM, self.table_name, ids
327    );
328    println!("delete_batch_ids sql: {}", &sql_str);
329    let mut pool = self.db.acquire().await?;
330    match query(&sql_str).execute(&mut pool).await {
331      Ok(res) => Ok(res),
332      Err(err) => Err(err),
333    }
334  }
335
336  pub async fn delete(self, wrapper: String) -> Result<MySqlQueryResult, Error> {
337    let sql_str = format!(
338      "{} {} WHERE is_deleted = '0' {}",
339      DELETE_FROM, self.table_name, h_wrapper(wrapper),
340    );
341    println!("delete sql: {}", &sql_str);
342    let mut pool = self.db.acquire().await?;
343    match query(&sql_str).execute(&mut pool).await {
344      Ok(res) => Ok(res),
345      Err(err) => Err(err),
346    }
347  }
348
349  /******************************************** DELETE MAPPER ********************************************/
350
351}
352
353fn h_wrapper(mut wrapper: String) -> String{
354  if wrapper.is_empty() == false {
355    wrapper = format!("AND {}", wrapper);
356  };
357  wrapper
358}