zqlovejyc / sqlbuilder Goto Github PK
View Code? Open in Web Editor NEW.NET Framework4.5版本Expression表达式转换为SQL语句,支持SqlServer、MySql、Oracle、Sqlite、PostgreSql;基于Dapper实现了不同数据库对应的数据仓储Repository;
License: Apache License 2.0
.NET Framework4.5版本Expression表达式转换为SQL语句,支持SqlServer、MySql、Oracle、Sqlite、PostgreSql;基于Dapper实现了不同数据库对应的数据仓储Repository;
License: Apache License 2.0
Console.WriteLine("复杂的where:");
var core = SQLBuilder.SqlBuilder.Select<Models.AAA_TEST_A>().Where(x => x.Id > 1000 || (x.Id < 10 && x.Age.Equals(100)));
PrintSql(core);
Console.WriteLine("=====================");
输出的sql语句:
SELECT * FROM [AAA_TEST_A] AS A WHERE A.[Id] > @param0 OR A.[Id] < @param1 AND A.[Age] = @Param2
应该生成的sql语句:
SELECT * FROM [AAA_TEST_A] AS A WHERE A.[Id] > @param0 OR A.[Id] < (@param1 AND A.[Age] = @Param2)
支持Net Core么?
麻烦问下作者有没有相关说明文档,或者博客之类的 相关性参考文档
生成的Oracle数据库的问题:
1.【BUG】生成的sql语句的表名和列带有引号,放到Oracle连接工具中执行会报错,表名带引号没问题,列不能带引号。例如生成的oracle SQL语句如下:
SELECT * FROM "Base_UserInfo" A WHERE A."userId" > :Param0 OR A."Email" <> :Param1
其中userId,Email都不能带引号,sql语句才能正确执行
2.【BUG】Console测试group by第一个的代码:
Print(
SqlBuilder.Select<UserInfo>()
.Where(o => o.Name == "张强")
.GroupBy(u => u.Id),
"GroupBy分组查询 用法1",
"GroupBy"
);
生成的SQL语句:SELECT * FROM AAA_TEST_A_COPY A WHERE A.Name = :Param0 GROUP BY A.Id,
最后多了一个逗号,执行会报错,测试Oracle,Mysql都会有这个问题。
3.【可能不是BUG】group by语句,oracle数据库的group by语句要求比较奇怪,要求select多少个字段,group by 后面必须跟多少个字段。否则会报错。示例:SQLBuilder设置数据库为Oralce生成的group by语句如下:
GroupBy分组查询 用法1
SELECT * FROM AAA_TEST_A_COPY A WHERE A.Name = :Param0 GROUP BY A.Id,A.C_Nvarchar2
[:Param0, 张强]
将这个语句放到oracle连接工具中执行,会报这个错误:ORA-00936: 缺失表达式。把SQL语句修改为:
SELECT A.Id,A.C_Nvarchar2 FROM AAA_TEST_A_COPY A WHERE A.Name = :Param0 GROUP BY A.Id,A.C_Nvarchar2
然后就不报错了
4.【BUG】oracle的insert语句,当插入的对象是数组时,生成的sql语句:
插入数据 用法6
INSERT INTO AAA_TEST_A_COPY (Name,Age,CreatedTime,Price,C_Number,C_Image,C_Char,C_Nvarchar2,C_TimeStamp) SELECT :Param0,:Param1,NULL,NULL,NULL,NULL,NULL,NULL,NULL FROM DUAL UNION ALL SELECT :Param2,:Param3,NULL,NULL,NULL,NULL,NULL,NULL,NULL FROM DUAL
[:Param0, 张三]
[:Param1, 2]
[:Param2, 张三]
[:Param3, 2]
dapper检测sql语句NG,原因:ORA-00918: 未明确定义列
将sql语句放到Oracle连接工具中执行也一样报这个错误:
生成的Mysql数据库的问题:
【可能不是BUG】
Mysql数据库没有full join关键字,所以用full join生成的mysql sql语句执行会报错。
core = LambdaToSql.SqlBuilder.Select<Models.AAA_TEST_A>().Where(x => (x.Id > 1000 && x.Id < 10) || x.Age.Equals(100))
.AndWhere(x => x.Name == "li").OrWhere(x => x.Price == 999f&&x.Id==1000000);
PrintSql(core);
这样,得出来的sql语句还是有问题,得出来的sql语句如下:
SELECT * FROM AAA_TEST_A
AS A WHERE (A.Id
> ?Param0 AND A.Id
< ?Param1) OR A.Age
= ?Param2 AND A.Name
= ?Param3 OR A.Price
= ?Param4 AND A.Id
= ?Param5
正确的sql语句如下:
SELECT * FROM AAA_TEST_A
AS A WHERE (A.Id
> ?Param0 AND A.Id
< ?Param1) OR A.Age
= ?Param2 AND A.Name
= ?Param3 OR( A.Price
= ?Param4 AND A.Id
= ?Param5)
我把BinaryExpressionResolve.cs的where函数开头的修改如下,测试貌似都OK,不知道会不会造成其他BUG,会多点括号,但是对于执行sql语句没有影响:
sqlPack += "(";
var startIndex = sqlPack.Length;
SqlBuilderProvider.Where(expression.Left, sqlPack);
var signIndex = sqlPack.Length;
SqlBuilderProvider.Where(expression.Right, sqlPack);
sqlPack += ")";
//表达式左侧为bool类型常量且为true时,不进行Sql拼接
if (!(expression.Left.NodeType == ExpressionType.Constant && expression.Left.ToObject() is bool b && b))
{
//其他代码,和之前一样
}
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.