hisql ORM 查詢語句使用教程

tansar發表於2022-01-08
SQLORM

HiSql 提供一個可以適合多種資料庫的中間查詢語法，不需要關注各個資料庫的語法特性，通過HiSql寫語句可以在常用的不同型別資料庫中執行，且語法與Sql語境類似一看就懂一學就會

hisql.net 官網(文件編寫中)

HiSql 原始碼(github) https://github.com/tansar/HiSql

git clone https://github.com/tansar/HiSql.git

::: tip
HiSql 查詢方式提供兩種查詢方式

  1. 鏈式寫法
  2. HiSql 語句 (非原生資料庫的語句,但類似於SQL的語法可以跨庫執行) 一套中間SQL語句 詳請見HiSql語法 文件

HiSql主要的方向是低程式碼平臺(低程式碼平臺的特點是使用者可以建表,如果建一張表要對應一個實體是行不通的) 所以暫時不支援Lambda表示式的寫法,寫也是HiSql與其它ORM框架的區別之一

:::

單表查詢

HiSql生成的語句

通過ToSql可以檢視當前操作生成的底層Sql語句 注：不同的資料庫生成的語句會有差別
::: tip
HiSql底層對於SQL語句進行了AST語法,語義解析能智慧識別語法錯誤
:::

//返回當前連線資料庫型別的SQL查詢語句
string sql1=sqlClient.Query("Hi_TabModel").Field("*").ToSql();

//返回當前連線資料庫型別的SQL查詢語句
// 注意這不是原生的sql語句 是HiSql定義的一套跨資料庫的中間SQL語句 詳請見[HiSql語法] 文件
string sql2=sqlClient.HiSql("select * from Hi_TabModel").ToSql();

在執行ToSql() HiSql會檢測查詢的表及欄位的合法性,只要檢測通過了才會編譯成對應資料庫的Sql查詢語句

生成的SQL語句如下(SqlServer為例)


select [Hi_TabModel].[TabName],[Hi_TabModel].[TabReName],[Hi_TabModel].[TabDescript],[Hi_TabModel].[TabStoreType],[Hi_TabModel].[TabType],[Hi_TabModel].[TabCacheType],[Hi_TabModel].[TabStatus],[Hi_TabModel].[IsSys],[Hi_TabModel].[IsEdit],[Hi_TabModel].[IsLog],[Hi_TabModel].[LogTable],[Hi_TabModel].[LogExprireDay],[Hi_TabModel].[CreateTime],[Hi_TabModel].[CreateName],[Hi_TabModel].[ModiTime],[Hi_TabModel].[ModiName] from [Hi_TabModel] as [Hi_TabModel]

可能有人會問為什麼*號會帶出來所有的欄位是不是生成的語句有問題？
::: tip
當欄位使用*號時會查詢表中所有的欄位,如果欄位的順序要調整請修改系統表Hi_FieldModel中的SortNum的值,值越小排在越前面(當物理表中的欄位順序不是想要的但又不想修改物理表可以通過該方式調整順序)
為了可以自定義排序所以會帶出欄位，不過沒有關係這裡不影響效能
:::

單表查詢結果

hisql查詢的查詢語法與sql類似
提供了四種查詢結果的返回型別

  1. ToTable() 返回一個DataTable表結果
  2. ToJson() 返回一個Json結果集
  3. ToList<T> 返回一個實體類集合 T 的類的屬性必須與返回的結果集有欄位相匹配
  4. ToDynamic() 返回一個動態類集 HiSql.TDynamic 可以檢視文件
  5. ToEObjectAsync() 返回ExpandObject類集 (1.0.2.9及以上版本才支援)
//查詢表Hi_TabModel的所有欄位 並返回一個 DataTable的結果集
DataTable dt1= sqlClient.Query("Hi_TabModel").Field("*").ToTable();

//查詢表Hi_TabModel的所有欄位 並返回一個 DataTable的結果集
// 注意這不是原生的sql語句 是HiSql定義的一套跨資料庫的中間SQL語句 詳請見[HiSql語法] 文件
DataTable dt2= sqlClient.HiSql("select * from Hi_TabModel").ToTable();

查詢指定欄位

Field()方法支援多引數(params string[] )的欄位 注意不要帶資料庫底層的符號如sqlserver [欄位名] ,hisql在生成sql時將會自動處理

//僅查詢表Hi_TabModel的部分欄位 並返回一個 DataTable的結果集
DataTable dt= sqlClient.Query("Hi_TabModel").Field("TabName","TabReName").ToTable();



//僅查詢表Hi_TabModel的部分欄位 並返回一個 DataTable的結果集
// 注意這不是原生的sql語句 是HiSql定義的一套跨資料庫的中間SQL語句 詳請見[HiSql語法] 文件
DataTable dt= sqlClient.HiSql("select TabName,TabReName from Hi_TabModel").ToTable();

生成的SQL語句如下(SqlServer為例)

select [Hi_TabModel].[TabName],[Hi_TabModel].[TabReName] from [Hi_TabModel] as [Hi_TabModel]

單表分頁查詢

  1. Skip 表示取的頁碼數
  2. Take 表示每頁獲取數量
  3. Sort 可以支援多種方式如
    1. Sort("createtime desc") 注意這個適用於Hisql支援的所有庫（不要用底層庫的語法不然Hisql檢測時會丟擲異常）
    2. Sort("createtime desc","TabName asc") 多個排序條件時可以這樣寫
    3. Sort(new SortBy { { "createtime",SortType.DESC} }) 也支援這種寫法
    4. Sort(new SortBy { { "createtime",SortType.DESC} , { "TabName", SortType.ASC } }) 多個引數寫法
//方式1
DataTable dt1= sqlClient.Query("Hi_TabModel").Field("*")
.Sort("createtime desc"). Skip(1).Take(100).ToTable();

//方式2
DataTable dt2 = sqlClient.Query("Hi_TabModel").Field("*")
.Sort(new SortBy { { "createtime",SortType.DESC} }).Skip(1).Take(100).ToTable();


//如果需要返回當前查詢條件的記錄總數
int _total=0;
DataTable dt3 = sqlClient.Query("Hi_TabModel").Field("*")
.Sort(new SortBy { { "createtime",SortType.DESC} }).Skip(1).Take(100).ToTable(ref _total);



DataTable dt4= sqlClient.HiSql("select * from Hi_TabModel").Skip(1).Take(100).ToTable();

//如果需要返回當前查詢條件的記錄總數
int _total=0;
DataTable dt5 = sqlClient.HiSql("select * from Hi_TabModel").Skip(1).Take(100).ToTable(ref _total);

::: tip

在HiSql底層會根據頁碼生在不同的分頁方式以追求效能極致

:::

Where條件查詢

HiSql提供了多種查詢語句的寫法總有一款適合你

HiSql查詢條件的特點是可以動態拼接(非生成資料庫SQL),這是其它ORM框架完全不具備的用Lambda表示式的方式如果要拼動態條件試過的人都知道多痛苦,如果用原生SQL是無法跨庫支援的且完全性不可控(sql注入),HiSql在底層編譯器解析成不同的資料庫語句。有興趣的可以試一下ToSql()方法檢視一下編譯出來的SQL

可能有些人會問？用表示式這種強型別的不好嗎？ 當然好還是那句話如果你的專案不涉及到動態的或與低程式碼相關的建議你用傳統的ORM工具，如果你的專案想做成高度配置型的HiSql將是不錯的選擇

//單值
DataTable dt1 = sqlClient.Query("Hi_FieldModel").Field("*").Where(new Filter { { "TabName", OperType.EQ, "HTest01" } }).ToTable();
//多值查詢 或查詢
DataTable dt2 = sqlClient.Query("Hi_FieldModel").Field("*").Where(new Filter { 
    { "TabName", OperType.EQ, "HTest01" },
    { LogiType.OR},
    { "FieldName", OperType.EQ, "UName" }
}).ToTable();
DataTable dt3 = sqlClient.Query("Hi_FieldModel").Field("*").Where(new Filter { { "TabName", OperType.EQ, "HTest01" } }).ToTable();

//注意這不是原生的sql語句 是HiSql定義的一套跨資料庫的中間SQL語句 詳請見[HiSql語法] 文件
//hisql語法
DataTable dt4 = sqlClient.HiSql("select * from Hi_FieldModel as a where a.TabName='HTest01'").ToTable();
DataTable dt5 = sqlClient.HiSql("select * from Hi_FieldModel  where  TabName='HTest01'").ToTable();
DataTable dt6 = sqlClient.HiSql("select * from Hi_FieldModel  where  (TabName='HTest01' or FieldName='UName')").ToTable();

DataTable dt7 = sqlClient.Query("Hi_FieldModel").Field("*").Where("TabName='HTest01'").ToTable();
DataTable dt8 = sqlClient.Query("Hi_FieldModel").Field("*").Where("(TabName='HTest01' or FieldName='UName')").ToTable();

子查詢

上面介紹的查詢方式大家可能都看得出來查詢是比較簡單的,如果涉及到子查詢怎麼樣寫呢？下來就來演示一下

DataTable dt1= sqlClient.Query("Hi_FieldModel").Field("*").Where(new Filter { { "TabName", OperType.IN,
              sqlClient.Query("Hi_TabModel").Field("TabName").Where(new Filter { {"TabName",OperType.IN,new List<string> { "HTest01", "Hi_TabModel" } } })
          } }).ToTable();

//注意這不是原生的sql語句 是HiSql定義的一套跨資料庫的中間SQL語句 詳請見[HiSql語法] 文件
DataTable dt2 = sqlClient.HiSql("select * from Hi_FieldModel where TabName in (select TabName from Hi_TabModel where  TabName in ('HTest01','Hi_TabModel'))").ToTable();

生成的SQL語句如下(SqlServer為例)

select [Hi_FieldModel].[TabName],[Hi_FieldModel].[FieldName],[Hi_FieldModel].[FieldDesc],[Hi_FieldModel].[IsIdentity],[Hi_FieldModel].[IsPrimary],[Hi_FieldModel].[IsBllKey],[Hi_FieldModel].[FieldType],[Hi_FieldModel].[SortNum],[Hi_FieldModel].[Regex],[Hi_FieldModel].[DBDefault],[Hi_FieldModel].[DefaultValue],[Hi_FieldModel].[FieldLen],[Hi_FieldModel].[FieldDec],[Hi_FieldModel].[SNO],[Hi_FieldModel].[SNO_NUM],[Hi_FieldModel].[IsSys],[Hi_FieldModel].[IsNull],[Hi_FieldModel].[IsRequire],[Hi_FieldModel].[IsIgnore],[Hi_FieldModel].[IsObsolete],[Hi_FieldModel].[IsShow],[Hi_FieldModel].[IsSearch],[Hi_FieldModel].[SrchMode],[Hi_FieldModel].[IsRefTab],[Hi_FieldModel].[RefTab],[Hi_FieldModel].[RefField],[Hi_FieldModel].[RefFields],[Hi_FieldModel].[RefFieldDesc],[Hi_FieldModel].[RefWhere],[Hi_FieldModel].[CreateTime],[Hi_FieldModel].[CreateName],[Hi_FieldModel].[ModiTime],[Hi_FieldModel].[ModiName] from [Hi_FieldModel] as [Hi_FieldModel]
 where [Hi_FieldModel].[TabName] in (select [Hi_TabModel].[TabName] from [Hi_TabModel] as [Hi_TabModel]
 where [Hi_TabModel].[TabName] in ('HTest01','Hi_TabModel')
)

雖然兩種不同的寫法，但是生成的語句是一模一樣的，HiSql內建了HiSql語句編譯器有興趣的可以看一下原始碼，實現原理與其它ORM框架有著本質匹別解析效能比Lambda表示式方式要強

分類彙總查詢

HiSql支援max,min,avg,sum,count 五種常用聚合函式

DataTable dt = sqlClient.Query("Hi_FieldModel").Field("FieldName", "count(FieldName) as CHARG_COUNT").Group(new GroupBy { { "FieldName"} }).ToTable();

DataTable dt2 = sqlClient.HiSql("select FieldName,count(FieldName) as CHARG_COUNT from  Hi_FieldModel group by FieldName").ToTable();

生成的sql如下（以sqlserver為例）

select [Hi_FieldModel].[FieldName],count(*) as CHARG_COUNT from [Hi_FieldModel] as [Hi_FieldModel]
 group by [Hi_FieldModel].[FieldName]

對分類彙總不跳號排名

相有多個值同時則並列排名

DataTable dt_dens= sqlClient.Query("Hi_FieldModel").Field("FieldName", "count(FieldName) as CHARG_COUNT")
   .Group(new GroupBy { { "FieldName" } })
   .WithRank(DbRank.DENSERANK, DbFunction.COUNT, "FieldName", "rowidx1", SortType.DESC).ToTable();

生成的sql如下 （sqlserver）

 select [Hi_FieldModel].[FieldName],count(*) as CHARG_COUNT,
 dense_rank() over( order by COUNT([FieldName]) DESC) as rowidx1 
 from [Hi_FieldModel] as [Hi_FieldModel]
 group by [Hi_FieldModel].[FieldName]

查詢結果如下

並列第一的都是第1名 但第二名這個號沒有被佔用，這種叫不跳號排名

分類彙總跳號排名

DataTable dt_rank = sqlClient.Query("Hi_FieldModel").Field("FieldName", "count(FieldName) as CHARG_COUNT")
   .Group(new GroupBy { { "FieldName" } })
   .WithRank(DbRank.RANK, DbFunction.COUNT, "FieldName", "rowidx2", SortType.DESC).ToTable();

生成的sql如下（sqlserver)


 select [Hi_FieldModel].[FieldName],count(*) as CHARG_COUNT,
 rank() over( order by COUNT([FieldName]) DESC) as rowidx2 
 from [Hi_FieldModel] as [Hi_FieldModel]
 group by [Hi_FieldModel].[FieldName]

查詢結果如下
當有幾個並列第一時則跳過這此號 稱為跳號排名

分類彙總結果增加行號

var dt1 = sqlClient.Query(
                sqlClient.Query("Hi_FieldModel").Field("*").WithLock(LockMode.ROWLOCK).Where(new Filter { { "TabName", OperType.IN,
                        sqlClient.Query("Hi_TabModel").Field("TabName").Where(new Filter { {"TabName",OperType.IN,new List<string> { "Hone_Test", "H_TEST" } } })
                    } }),
                sqlClient.Query("Hi_FieldModel").WithLock(LockMode.ROWLOCK).Field("*").Where(new Filter { { "TabName", OperType.EQ, "DataDomain" } }),
                sqlClient.Query("Hi_FieldModel").Field("*").Where(new Filter { { "TabName", OperType.EQ, "Hi_FieldModel" } })
            )
                .Field("TabName", "count(*) as CHARG_COUNT")
                .WithRank(DbRank.DENSERANK, DbFunction.NONE, "TabName", "rowidx1", SortType.ASC)
                //.WithRank(DbRank.ROWNUMBER, DbFunction.COUNT, "*", "rowidx2", SortType.ASC)
                //以下實現組合排名
                .WithRank(DbRank.ROWNUMBER, new Ranks { { DbFunction.COUNT, "*" }, { DbFunction.COUNT, "*", SortType.DESC } }, "rowidx2")
                .WithRank(DbRank.RANK, DbFunction.COUNT, "*", "rowidx3", SortType.ASC)
                
                .Group(new GroupBy { { "TabName" } }).ToTable();

多表查詢

join 查詢

大家可以對比與其它框架的寫法，HiSql的語法更貼近於資料庫的SQL語句，學習成本更低，且其它ORM框架由於受泛型類的限制,對Join表的數量是有限制的。

DataTable dt1 = sqlClient.Query("Hi_FieldModel", "A").Field("A.FieldName as Fname")
    .Join("Hi_TabModel").As("B").On(new JoinOn { { "A.TabName", "B.TabName" } })
    .Where(new Filter { { "A.TabName",OperType.EQ, "HTest01" } })
    .ToTable();

DataTable dt2 = sqlClient.HiSql("select  A.FieldName as Fname from Hi_FieldModel as A inner join Hi_TabModel as B on A.TabName = B.TabName where A.TabName='HTest01'").ToTable();

生成的sql如下 （sqlserver）


select [A].[FieldName] as [Fname] from [Hi_FieldModel] as [A]
  inner join [Hi_TabModel] as [B] on [A].[TabName]=[B].[TabName]
 where [A].[TabName] = 'HTest01'

相關文章