查詢在應用程式中很重要,花樣也特别多,不同得業務需求需要不同的查詢條件,還要支援and、or ……事實上也确實如此,程式中有N多個查詢類,并且很可能其中有多個類查詢同一張表,是以特别想弄一個通用的查詢類。
前幾天也是因為讨論有關查詢的問題,想到了一個點子覺得可行,最近就抓緊實作了一下來驗證想法的可行性……
思路:其實查詢類很簡單,無非就是你要查詢哪個字段—字段名稱(Key)、你想搜尋的值—字段值(Value)、以及如何進行比較—查詢類型(QueryType),這是單個查詢條件(之後都叫做查詢因子,不知道合适不合适,也是突然間想起來的),如果是多個條件,弄了一個集合就是好了,問題就在于這些查詢因子之間的關系(and、or)……既然叫做查詢因子,這個集合我們不管他們之間的關系,隻是簡單的查詢因子的集合,我們在弄一個字段來存儲他們之間的關系,這裡暫時叫做邏輯表達式,例如:((a|b)&c)|((a&b&d)|e),最後我就解析這個表達式就可以了,a、b、c、d、e隻要在集合中找到具體的哪個查詢因子就可以了,就是這樣了。說通用查詢類有點慚愧,目前隻是在Mongodb下弄了一個簡單的實作(重點是思路了,嘿嘿),因為項目上用的是Mongodb是以先實作的肯定是他了,其他的資料庫同理……
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLrN2bsJEZlR3YhJHdu92Qvw1cy9GdhNWak5WSn5WaulGb0V3TvwVbvNmLzd2bsJmbj5ycldWYtl2Lc9CX6MHc0RHaiojIsJye.gif)
/// <summary>
/// 通用查詢類
/// </summary>
public class QueryModel
{
/// <summary>
/// 邏輯表達式
/// </summary>
public string FilterStr { get; set; }
/// <summary>
/// 查詢因子字典集合
/// </summary>
public Dictionary<string, QueryFactor> DCQueryFactor { get; set; }
}
/// <summary>
/// 查詢因子類
/// </summary>
public class QueryFactor
{
/// <summary>
/// 查詢字段的名稱
/// </summary>
public string Key { get; set; }
/// <summary>
/// 查詢字段的值
/// </summary>
public object Value { get; set; }
/// <summary>
/// 比較類型,支援的類型有:
/// eq:等于,
/// ne:不等于
/// gt:大于
/// lt:小于
/// gte:大于等于
/// lte:小于等于
/// in:範圍查詢
/// like:模糊查詢
/// </summary>
public string QueryType { get; set; } = "eq";
}
查詢類
這個倒是沒有什麼,關鍵是這個所謂的邏輯表達式不知道如何解析,真是廢了半天勁兒……什麼類似的堆棧實作電腦、逆波蘭式等弄了一大堆,感覺都沒有用上,最後對一個例子做了一些改進,才完成的……
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiZpdmLrN2bsJEZlR3YhJHdu92Qvw1cy9GdhNWak5WSn5WaulGb0V3TvwVbvNmLzd2bsJmbj5ycldWYtl2Lc9CX6MHc0RHaiojIsJye.gif)
public class QueryModelForMongodb
{
private Dictionary<string, FilterDefinition<BsonDocument>> ParenthesesExpressionDic = new Dictionary<string, FilterDefinition<BsonDocument>>();
/// <summary>
/// 入口方法
/// </summary>
/// <param name="logicalExpression">邏輯表達式</param>
/// <param name="queryModel">查詢類</param>
/// <returns></returns>
public FilterDefinition<BsonDocument> ToMongodbFilter(string logicalExpression, QueryModel queryModel)
{
int startIndex = logicalExpression.LastIndexOf("(");
if (startIndex != -1)
{
// 截取括号中的表達式
int endIndex = logicalExpression.IndexOf(")", startIndex);
int len = endIndex - startIndex - 1;
string simpleExpress = logicalExpression.Substring(startIndex + 1, len);
// 處理簡單的表達式并結果儲存到字典中
string tempGuid = Guid.NewGuid().ToString();
FilterDefinition<BsonDocument> fd1 = ToMongodbFilterSimpleLogicalExpression(simpleExpress, queryModel);
ParenthesesExpressionDic.Add(tempGuid, fd1);
// 繼續處理剩餘表達式
string leftStr = logicalExpression.Substring(0, startIndex);
string rightStr = logicalExpression.Substring(endIndex + 1);
return ToMongodbFilter($"{leftStr}{tempGuid}{rightStr}", queryModel);
}
return ToMongodbFilterSimpleLogicalExpression(logicalExpression, queryModel);
}
/// <summary>
/// 處理簡單的邏輯表達式(不包含圓括号)
/// </summary>
/// <param name="logicalExpression"></param>
/// <param name="queryModel"></param>
/// <returns></returns>
private FilterDefinition<BsonDocument> ToMongodbFilterSimpleLogicalExpression(string logicalExpression, QueryModel queryModel)
{
// 1、篩選出操作符:&、|
Queue<char> qOperator = new Queue<char>();
//Regex regexOperator = new Regex("[&|]");
//foreach (Match item in regexOperator.Matches(logicalExpression))
//{
// qOperator.Enqueue(item.Value);
//}
foreach (char c in logicalExpression)
{
if (c == '&' || c == '|')
{
qOperator.Enqueue(c);
}
}
// 2、篩選出所有的變量
Queue<string> qVariable = new Queue<string>();
string[] tempVariables = logicalExpression.Replace("&", ",").Replace("|", ",").Split(",");
foreach (string v in tempVariables)
{
qVariable.Enqueue(v);
}
// 3、傳回結果組裝
FilterDefinition<BsonDocument> filter = null;
if (qVariable.Count >= 1)
{
string tempV = qVariable.Dequeue();
filter = ParenthesesExpressionDic.ContainsKey(tempV) ? ParenthesesExpressionDic[tempV] : QueryFactorToMogodbFilter(queryModel.DCQueryFactor[tempV]);
while (qVariable.Count > 0)
{
string rightV = qVariable.Dequeue();
var tempFilter = ParenthesesExpressionDic.ContainsKey(rightV) ? ParenthesesExpressionDic[rightV] : QueryFactorToMogodbFilter(queryModel.DCQueryFactor[rightV]);
char tempOperator = qOperator.Dequeue();
switch (tempOperator)
{
case '&':
{
filter = filter & tempFilter;
break;
}
case '|':
{
filter = filter | tempFilter;
break;
}
}
}
filter = Builders<BsonDocument>.Filter.Empty & (filter);
}
return filter ?? Builders<BsonDocument>.Filter.Empty;
}
/// <summary>
/// 将查詢因子轉換成Mongodb的Filter
/// </summary>
/// <param name="queryFactor"></param>
/// <returns></returns>
private FilterDefinition<BsonDocument> QueryFactorToMogodbFilter(QueryFactor queryFactor)
{
/// <summary>
/// 比較類型,支援的類型有:
/// eq:等于,
/// ne:不等于
/// gt:大于
/// lt:小于
/// gte:大于等于
/// lte:小于等于
/// in:範圍查詢
/// like:模糊查詢
/// </summary>
if (queryFactor == null) return Builders<BsonDocument>.Filter.Empty;
FilterDefinition<BsonDocument> filter = null;
switch (queryFactor.QueryType.ToLower())
{
case "ne":
{
filter = Builders<BsonDocument>.Filter.Ne(queryFactor.Key, queryFactor.Value);
break;
}
case "gt":
{
filter = Builders<BsonDocument>.Filter.Gt(queryFactor.Key, queryFactor.Value);
break;
}
case "gte":
{
filter = Builders<BsonDocument>.Filter.Gte(queryFactor.Key, queryFactor.Value);
break;
}
case "lt":
{
filter = Builders<BsonDocument>.Filter.Lt(queryFactor.Key, queryFactor.Value);
break;
}
case "lte":
{
filter = Builders<BsonDocument>.Filter.Lte(queryFactor.Key, queryFactor.Value);
break;
}
case "in":
{
filter = Builders<BsonDocument>.Filter.In(queryFactor.Key, JsonConvert.DeserializeObject<IList<String>>(JsonConvert.SerializeObject(queryFactor.Value)));
break;
}
case "like":
{
//filter = filter & Builders<BsonDocument>.Filter.Regex(queryFactor.Key, new BsonRegularExpression(new Regex(Regex.Escape(queryFactor.Value.ToString()), RegexOptions.IgnoreCase)));
filter = Builders<BsonDocument>.Filter.Regex(queryFactor.Key, new BsonRegularExpression(new Regex(".*" + Regex.Escape(queryFactor.Value.ToString()) + ".*", RegexOptions.IgnoreCase)));
break;
}
case "eq":
default:
{
filter = Builders<BsonDocument>.Filter.Eq(queryFactor.Key, queryFactor.Value);
break;
}
}
return filter ?? Builders<BsonDocument>.Filter.Empty;
}
}
Mongodb實作
具體的實作思路是這樣的,就是逐個的消除表達式中的括号,直到表達式中不包含圓括号,就用上面的表達式來舉個例子,((a|b)&c)|((a&b&d)|e)
1、找到最後一個“(”,之後尋找與之比對的“)”,處理這對圓括号中的簡單表達式,這裡是a&b&d,處理完之後将結果放在一個字典之中<guid,filter>,記作<1,filter1>,之後字元串變為((a|b)&c)|(1|e)
2、參照1的順序再次處理表達式((a|b)&c)|(1|e),這次處理1|e,字典中添加一項<2,filter2>,字元串變為((a|b)&c)|2
3、處理a|b,字典中添加一項<3,filter3>,字元串變為(3&c)|2
4、處理3&c,字典中添加一項<4,filter4>,字元串變為4|2
5、至此,圓括号已不再,隻是簡單的表達式,這就簡單了
結束了,歡迎大家提供更好的辦法來處理這個問題,共同努力,哈哈!