public void ConstructStatement <T1>(T1 id, Type type, ConstructedStatementType statementType)
{
if (statementType == ConstructedStatementType.GetList)
{
ConstructGetListStatement(id, type);
return;
}
DbTableAttribute dbTable = type.GetCustomAttributes(false)?.OfType <DbTableAttribute>()?.FirstOrDefault();
Debug.Assert(dbTable != null, $"Statement Construction failed for type {type}");
string query = "";
string tableName = dbTable.DbTableName;
List <MySqlDbType> types = new List <MySqlDbType>();
List <MySqlDbType> criteriaTypes = new List <MySqlDbType>();
string updateList = null;
string insertList = null;
string insertValues = null;
string selectList = null;
string whereList = null;
var properties = GetPropertyCache(type);
foreach (var p in properties)
{
if (p.Item2.Get)
{
if (selectList != null)
{
selectList += ", ";
}
selectList += p.Item2.DbFieldName;
}
if (p.Item2.Update && statementType == ConstructedStatementType.Update)
{
if (updateList != null)
{
updateList += ", ";
}
updateList += "`" + p.Item2.DbFieldName + "` = ?";
types.Add((MySqlDbType)p.Item2.DbFieldType);
}
if (p.Item2.Insert && statementType == ConstructedStatementType.Insert)
{
if (insertList != null)
{
insertList += ", ";
}
insertList += p.Item2.DbFieldName;
if (insertValues != null)
{
insertValues += ", ";
}
insertValues += "?";
types.Add((MySqlDbType)p.Item2.DbFieldType);
}
if (p.Item2.IsCriteria)
{
if (whereList != null)
{
whereList += " AND ";
}
whereList += "`" + p.Item2.DbFieldName + "` = ?";
if (statementType == ConstructedStatementType.Get || statementType == ConstructedStatementType.Update)
{
criteriaTypes.Add((MySqlDbType)p.Item2.DbFieldType);
}
}
}
types.AddRange(criteriaTypes);
switch (statementType)
{
case ConstructedStatementType.Get:
query = $"SELECT {selectList} FROM `{tableName}` WHERE {whereList}";
break;
case ConstructedStatementType.Insert:
query = $"INSERT INTO `{tableName}` ({selectList}) VALUES ({insertValues})";
break;
case ConstructedStatementType.Update:
query = $"UPDATE `{tableName}` SET {updateList} WHERE {whereList}";
break;
}
PrepareStatement(Convert.ToUInt32(id), query, types);
}
/// <summary>
/// 使用提供的条件查询数据库（表由entityType指定），返回满足条件的记录（分页）
/// </summary>
/// <typeparam name="T">要查询的实体类型，标记了DbTable和DbField特性的类，并且具有空白构造函数</typeparam>
/// <param name="connector"></param>
/// <param name="criteria">查询条件字典，键是PropertyName，条件仅支持=比较，条件间仅支持AND运算</param>
/// <param name="pageIndex">从0开始的返回页索引</param>
/// <param name="pageSize">每页几行记录</param>
/// <param name="sortProperty">用于排序的PropertyName(如为空则使用关键字)</param>
/// <param name="descending">是否倒序排列</param>
/// <param name="total">符合条件的记录总数</param>
/// <returns></returns>
public static async Task <PagedQuery <T> > SearchEntities <T>(DbConnector connector, Dictionary <string, object> criteria,
int pageIndex, int pageSize, string sortProperty, bool descending) where T : new()
{
Type entityType = typeof(T);
if (!(DbTableAttribute.GetCustomAttribute(entityType, typeof(DbTableAttribute)) is DbTableAttribute tableAttr))
{
throw new Exception("类型" + entityType.FullName + "没有标记DbTableAttribute特性");
}
string selectClause = "SELECT * ";
string fromClause = "FROM " + tableAttr.TableName;
string orderBy;
#region 正确处理orderBy
DbFieldAttribute sortFieldAttr = null;
if (string.IsNullOrWhiteSpace(sortProperty))
{
//如果没有传递sortProperty参数, 则使用PrimaryKey
foreach (PropertyInfo property in entityType.GetProperties())
{
DbFieldAttribute fldAttr = DbFieldAttribute.GetCustomAttribute(property, typeof(DbFieldAttribute)) as DbFieldAttribute;
if (fldAttr != null && fldAttr.IsPrimaryKey)
{
sortFieldAttr = fldAttr;
break;
}
}
}
else
{
PropertyInfo property = entityType.GetProperty(sortProperty);
if (property == null)
{
throw new Exception(string.Format("类型{0}中没有找到{1}属性用于排序", entityType.FullName, sortProperty));
}
sortFieldAttr = DbFieldAttribute.GetCustomAttribute(property, typeof(DbFieldAttribute)) as DbFieldAttribute;
}
if (sortFieldAttr == null)
{
throw new Exception("类型" + entityType.FullName + "没有标记IsPrimaryKey=true的DbField特性");
}
if (descending)
{
orderBy = sortFieldAttr.FieldName + " DESC";
}
else
{
orderBy = sortFieldAttr.FieldName;
}
#endregion
StringBuilder whereBuilder = new StringBuilder();
List <SqlParameter> sqlParameters = new List <SqlParameter>();
if (criteria != null)
{
foreach (string conditionName in criteria.Keys)
{
if (criteria[conditionName] == null || criteria[conditionName].ToString().Length == 0)
{
continue;
}
PropertyInfo conditionProperty = entityType.GetProperty(conditionName);
if (conditionProperty == null)
{
throw new Exception(string.Format("类型{0}中没有找到{1}属性用于查询条件", entityType.FullName, conditionName));
}
DbFieldAttribute conditionAttr = DbFieldAttribute.GetCustomAttribute(conditionProperty, typeof(DbFieldAttribute)) as DbFieldAttribute;
if (conditionAttr == null)
{
throw new Exception(string.Format("类型{0}的{1}属性没有标记DbFieldAttribute特性, 无法用于数据库查询",
entityType.FullName, conditionName));
}
SqlParameter parameter = GenerateSqlParameter(conditionAttr, conditionProperty.PropertyType, criteria[conditionName]);
sqlParameters.Add(parameter);
whereBuilder.AppendFormat("[{0}]=@{0} ", conditionAttr.FieldName);
whereBuilder.Append("AND ");
}
}
if (whereBuilder.Length > 0)
{
whereBuilder.Remove(whereBuilder.Length - 4, 4);
}
string sql = BuildPagedSelectSql(selectClause, fromClause, whereBuilder.ToString(), orderBy, pageIndex, pageSize);
DataSet ds = await connector.ExecuteSqlQuerySet(sql, sqlParameters.ToArray());
PagedQuery <T> result = new PagedQuery <T>();
if (ds != null && ds.Tables.Count == 2)
{
result.Total = (int)ds.Tables[0].Rows[0][0];
ds.Tables[1].TableName = "result";
result.Records = await Task.Run(() => ValueHelper.WrapEntities <T>(ds.Tables[1]));
}
else
{
result.Total = 0;
result.Records = new T[0];
}
ds.Dispose();
return(result);
}
private static void BuildDomainObjects(System.Type t, DomainModel.Spi.DomainModel domainModel,
DomainObjectAttribute rootDomainObjectAttr, DbTableAttribute rootDbTableAttr)
{
var rootDomainObject = new DomainObject
{
ID = Guid.NewGuid().ToString(),
Name = rootDomainObjectAttr.Name,
ClazzReflectType = t.FullName,
DomainModel = domainModel,
IsLazyLoad = rootDomainObjectAttr.IsLazyload,
};
var dataObject = new Sharding.Database.DataObject()
{
Name = rootDbTableAttr.Name,
IsView = rootDbTableAttr.IsView,
ID = Guid.NewGuid().ToString(),
TableShardingStrategyID = rootDbTableAttr.TableShardingStrategyID,
DatabaseShardingStrategyID = rootDbTableAttr.DBShardingStrategyID,
LogicTableName = rootDbTableAttr.Name,
DataSourceName = rootDbTableAttr.DataSourceName
};
if (string.IsNullOrEmpty(rootDbTableAttr.ID) != false)
{
dataObject = new Sharding.Database.DataObject()
{
ID = rootDbTableAttr.ID
};
}
rootDomainObject.DataObject = dataObject;
domainModel.RootDomainObject = rootDomainObject;
}
