public static CmdParameter CreatePagingSql(ISessionImplementor session, IEntityPersister entityPersister, int pageSize, int dataStart, Criteria criteria, Order order)
{
//TODO 需要修改-开始
#region MyRegion
var classMetadata = entityPersister.ClassMetadata;
var result = new CmdParameter();
//if (string.IsNullOrEmpty(obj.Conditaion))
//{
// var columnInfo = classMetadata.ColumnInfos.Find(t => t.IsPrimaryKey);
// if (columnInfo != null)
// {
// var v = columnInfo.PropertyInfo.FastGetValue(obj);
// if (!IsNull(v)) result = CreateSelectSql(session, entityPersister, v);
// }
//}
var parameters = new List <DbParameter>();
//entityPersister.Driver.FormatNameForSql(columnInfo.Name)
// string rConditaion = GetConditaion(session, entityPersister, obj, out parameters);
var sql = new StringBuilder("SELECT ");
//sql.Append(string.IsNullOrEmpty(obj.ResultColumns) ? "*" : obj.ResultColumns);
sql.Append("*");
sql.AppendFormat(" FROM {0} ", classMetadata.TableName);
var whereStr = "";
if (criteria != null && criteria.CriterionList != null && criteria.CriterionList.Count > 0)
{
criteria.Driver = entityPersister.Driver;
criteria.DicParameter = entityPersister.GetParameter(session);
criteria.DbParameter = parameters;
whereStr = criteria.ToString(classMetadata);
sql.Append(" WHERE ").Append(whereStr);
result.Parameters = parameters.ToArray();
}
// entityPersister.SetDbParamter(session, parameters, columnInfo.Name, value);
//var where = GetWhereCaluse(rConditaion);
//if (where.Length > 0)
//{
// sql.AppendFormat(" WHERE {0} ", where);
// result.Parameters = parameters.ToArray();
//}
//sql.Append(obj.OrderBy ?? "" );
var orderByStr = order != null?order.ToString(classMetadata) : "";
sql.Append(orderByStr);
sql.Append(entityPersister.Driver.MultipleQueriesSeparator);
//result.CommandText = sql.ToString();
#endregion
//TODO 需要修改-结束
result.CommandText = entityPersister.Driver.CreatePagingSql(pageSize, dataStart, entityPersister.ClassMetadata.TableName, entityPersister.ClassMetadata.PrimaryKey, whereStr, orderByStr);
log.Info(result.CommandText);
return(result);
}
public static CmdParameter CreateUpdateSql(ISessionImplementor session, IEntityPersister entityPersister, IEntity obj)
{
var classMetadata = entityPersister.ClassMetadata;
var rConditaion = obj.Conditaion;
var columns = new StringBuilder();
List <DbParameter> parameters = new List <DbParameter>();
foreach (ColumnInfo columnInfo in classMetadata.ColumnInfos)
{
var value = columnInfo.PropertyInfo.FastGetValue(obj);
if (IsNull(value))
{
continue;
}
if (columnInfo.IsPrimaryKey && string.IsNullOrEmpty(obj.Conditaion))
{
rConditaion = string.Format("{0}={1}", columnInfo.Name, entityPersister.Driver.FormatNameForSql(columnInfo.Name));
entityPersister.SetDbParamter(session, parameters, columnInfo.Name, value);
}
else
{
var index = Contains(obj.Conditaion, columnInfo.Name);
if (index == 0)
{
rConditaion = rConditaion.Replace(string.Format(LikeString[0], columnInfo.Name), entityPersister.Driver.FormatNameForSql(columnInfo.Name));
entityPersister.SetDbParamter(session, parameters, classMetadata.PKColumnName, value);
}
else if (index > 0)
{
rConditaion = rConditaion.Replace(string.Format(LikeString[index], columnInfo.Name), string.Format(LikeValueString[index - 1], Escape(value.ToString(), "")));
}
else if (index == -1)
{
columns.AppendFormat("{0}={1},", columnInfo.Name, entityPersister.Driver.FormatNameForSql(columnInfo.Name));
if (columnInfo.IsNullable && IsNullValue(value))
{
entityPersister.SetDbParamter(session, parameters, columnInfo.Name, DBNull.Value);
}
else
{
entityPersister.SetDbParamter(session, parameters, columnInfo.Name, value);
}
}
}
}
var where = GetWhereCaluse(rConditaion);
var result = new CmdParameter();
if (where.Length > 0 && columns.Length > 0)
{
result.Parameters = parameters.ToArray();
result.CommandText = string.Format("UPDATE {0} SET {1} WHERE {2}{3}", classMetadata.TableName, columns.Remove(columns.Length - 1, 1), where, entityPersister.Driver.MultipleQueriesSeparator);
}
return(result);
}
public static CmdParameter CreateSelectSql(ISessionImplementor session, IEntityPersister entityPersister, object pKey)
{
var result = new CmdParameter();
if (pKey != null)
{
var classMetadata = entityPersister.ClassMetadata;
var driver = entityPersister.Driver;
result.CommandText = string.Format("SELECT * FROM {0} WHERE {1}={2}", classMetadata.TableName, classMetadata.PrimaryKey, driver.FormatNameForSql(classMetadata.PrimaryKey));
var parameters = new List <DbParameter>();
entityPersister.SetDbParamter(session, parameters, classMetadata.PKColumnName, pKey);
result.Parameters = parameters.ToArray();
}
return(result);
}
public static CmdParameter CreateDeleteWithAssociateSql(ISessionImplementor session, IEntityPersister entityPersister, params object[] pKeys)
{
var result = new CmdParameter();
var classMetadata = entityPersister.ClassMetadata;
var driver = entityPersister.Driver;
if (pKeys.Length == 1)
{
result.CommandText = string.Format("DELETE FROM {0} WHERE {1}={2}{5}DELETE FROM {3} WHERE {4}={2}{5}", classMetadata.AssociateTable, classMetadata.ForeignKey, driver.FormatNameForSql(classMetadata.PrimaryKey), classMetadata.TableName, classMetadata.PrimaryKey, driver.MultipleQueriesSeparator);
var parameters = new List <DbParameter>();
entityPersister.SetDbParamter(session, parameters, classMetadata.PrimaryKey, pKeys[0]);
result.Parameters = parameters.ToArray();
}
else if (pKeys.Length > 1)
{
result.CommandText = string.Format("DELETE FROM {0} WHERE {1} {2}{5}DELETE FROM {3} WHERE {4} {2}{5}", classMetadata.AssociateTable, classMetadata.ForeignKey, GetInExpression(pKeys), classMetadata.TableName, classMetadata.PrimaryKey, driver.MultipleQueriesSeparator);
}
return(result);
}
public static CmdParameter CreateDataCountSql(ISessionImplementor session, IEntityPersister entityPersister, Criteria criteria)
{
var classMetadata = entityPersister.ClassMetadata;
var result = new CmdParameter();
var parameters = new List<DbParameter>();
var sql = new StringBuilder();
sql.AppendFormat("SELECT COUNT(1) FROM {0} ", classMetadata.TableName);
if (criteria != null && criteria.CriterionList != null && criteria.CriterionList.Count > 0)
{
criteria.Driver = entityPersister.Driver;
criteria.DicParameter = entityPersister.GetParameter(session);
criteria.DbParameter = parameters;
sql.Append(" WHERE ").Append(criteria.ToString(classMetadata));
result.Parameters = parameters.ToArray();
}
sql.Append(entityPersister.Driver.MultipleQueriesSeparator);
result.CommandText = sql.ToString();
log.Info(sql);
return result;
}
public static CmdParameter CreateDataCountSql(ISessionImplementor session, IEntityPersister entityPersister, Criteria criteria)
{
var classMetadata = entityPersister.ClassMetadata;
var result = new CmdParameter();
var parameters = new List <DbParameter>();
var sql = new StringBuilder();
sql.AppendFormat("SELECT COUNT(1) FROM {0} ", classMetadata.TableName);
if (criteria != null && criteria.CriterionList != null && criteria.CriterionList.Count > 0)
{
criteria.Driver = entityPersister.Driver;
criteria.DicParameter = entityPersister.GetParameter(session);
criteria.DbParameter = parameters;
sql.Append(" WHERE ").Append(criteria.ToString(classMetadata));
result.Parameters = parameters.ToArray();
}
sql.Append(entityPersister.Driver.MultipleQueriesSeparator);
result.CommandText = sql.ToString();
log.Info(sql);
return(result);
}
public static CmdParameter CreateInsertSql(ISessionImplementor session, IEntityPersister entityPersister, IEntity obj)
{
var result = new CmdParameter();
var classMetadata = entityPersister.ClassMetadata;
var driver = entityPersister.Driver;
var sql = new StringBuilder("INSERT INTO");
sql.AppendFormat(" {0} (", classMetadata.TableName);
var values = new StringBuilder();
var tParameters = new List <DbParameter>();
var columnInfos = classMetadata.ColumnInfos.FindAll(t => t.IsColumn);
foreach (var columnInfo in columnInfos)
{
var v = columnInfo.PropertyInfo.FastGetValue(obj);
if (IsNull(v))
{
continue;
}
if (columnInfo.IsNullable && IsNullValue(v))
{
continue;
}
sql.AppendFormat("{0},", columnInfo.Name);
values.AppendFormat("{0},", driver.FormatNameForSql(columnInfo.Name));
entityPersister.SetDbParamter(session, tParameters, columnInfo.Name, v);
}
if (values.Length > 0)
{
sql.Replace(',', ')', sql.Length - 1, 1);
sql.AppendFormat(" VALUES ({0}){1}", values.Remove(values.Length - 1, 1), driver.MultipleQueriesSeparator);
result.CommandText = sql.ToString();
result.Parameters = tParameters.ToArray();
}
return(result);
}
public static CmdParameter CreateDeleteSql(ISessionImplementor session, IEntityPersister entityPersister, IEntity obj)
{
var classMetadata = entityPersister.ClassMetadata;
if (string.IsNullOrEmpty(obj.Conditaion))
{
var v = classMetadata.ColumnInfos.Find(t => t.IsPrimaryKey).PropertyInfo.FastGetValue(obj);
return(!IsNull(v) ? CreateDeleteSql(session, entityPersister, (int)v) : null);
}
List <DbParameter> parameters;
string rConditaion = GetConditaion(session, entityPersister, obj, out parameters);
var where = GetWhereCaluse(rConditaion);
var result = new CmdParameter();
if (where.Length > 0)
{
result.Parameters = parameters.ToArray();
result.CommandText = string.Format("DELETE FROM {0} WHERE {1}{2}", classMetadata.TableName, where, entityPersister.Driver.MultipleQueriesSeparator);
}
return(result);
}
public static CmdParameter CreateSelectSql(ISessionImplementor session, IEntityPersister entityPersister, IEntity obj)
{
var classMetadata = entityPersister.ClassMetadata;
var result = new CmdParameter();
if (string.IsNullOrEmpty(obj.Conditaion))
{
var columnInfo = classMetadata.ColumnInfos.Find(t => t.IsPrimaryKey);
if (columnInfo != null)
{
var v = columnInfo.PropertyInfo.FastGetValue(obj);
if (!IsNull(v))
{
result = CreateSelectSql(session, entityPersister, v);
}
}
}
List <DbParameter> parameters;
string rConditaion = GetConditaion(session, entityPersister, obj, out parameters);
var sql = new StringBuilder("SELECT ");
sql.Append(string.IsNullOrEmpty(obj.ResultColumns) ? "*" : obj.ResultColumns);
//sql.Append("*"); //delete
sql.AppendFormat(" FROM {0} ", classMetadata.TableName);
var where = GetWhereCaluse(rConditaion);
if (where.Length > 0)
{
sql.AppendFormat(" WHERE {0} ", where);
result.Parameters = parameters.ToArray();
}
sql.Append(obj.OrderBy ?? "");
sql.Append(entityPersister.Driver.MultipleQueriesSeparator);
result.CommandText = sql.ToString();
return(result);
}
public static CmdParameter CreateDeleteWithValidateSql(ISessionImplementor session, IEntityPersister entityPersister, params object[] pKeys)
{
var result = new CmdParameter();
var classMetadata = entityPersister.ClassMetadata;
var driver = entityPersister.Driver;
if (pKeys.Length == 1)
{
result.CommandText = string.Format("Delete FROM {0} WHERE {1} IN ( SELECT {1} FROM (SELECT M.{1} FROM {0} AS M LEFT JOIN {2} AS S ON M.ID=S.{3} WHERE M.{1}={4} AND S.{3} IS NULL )AS MS ){5}", classMetadata.TableName, classMetadata.PrimaryKey, classMetadata.AssociateTable, classMetadata.ForeignKey, driver.FormatNameForSql(classMetadata.PrimaryKey), driver.MultipleQueriesSeparator);
var parameters = new List<DbParameter>();
entityPersister.SetDbParamter(session, parameters, classMetadata.PrimaryKey, pKeys[0]);
result.Parameters = parameters.ToArray();
}
else if (pKeys.Length > 1)
{
result.CommandText = string.Format("Delete FROM {0} WHERE {1} IN ( SELECT {1} FROM (SELECT M.{1} FROM {0} AS M LEFT JOIN {2} AS S ON M.ID=S.{3} WHERE M.{1} {4} AND S.{3} IS NULL )AS MS ){5}", classMetadata.TableName, classMetadata.PrimaryKey, classMetadata.AssociateTable, classMetadata.ForeignKey, GetInExpression(pKeys), driver.MultipleQueriesSeparator);
}
return result;
}
public static CmdParameter CreateUpdateSql(ISessionImplementor session, IEntityPersister entityPersister, IEntity obj)
{
var classMetadata = entityPersister.ClassMetadata;
var rConditaion = obj.Conditaion;
var columns = new StringBuilder();
List<DbParameter> parameters = new List<DbParameter>();
foreach (ColumnInfo columnInfo in classMetadata.ColumnInfos)
{
var value = columnInfo.PropertyInfo.FastGetValue(obj);
if (IsNull(value)) continue;
if (columnInfo.IsPrimaryKey && string.IsNullOrEmpty(obj.Conditaion))
{
rConditaion = string.Format("{0}={1}", columnInfo.Name, entityPersister.Driver.FormatNameForSql(columnInfo.Name));
entityPersister.SetDbParamter(session, parameters, columnInfo.Name, value);
}
else
{
var index = Contains(obj.Conditaion, columnInfo.Name);
if (index == 0)
{
rConditaion = rConditaion.Replace(string.Format(LikeString[0], columnInfo.Name), entityPersister.Driver.FormatNameForSql(columnInfo.Name));
entityPersister.SetDbParamter(session, parameters, classMetadata.PKColumnName, value);
}
else if (index > 0)
{
rConditaion = rConditaion.Replace(string.Format(LikeString[index], columnInfo.Name), string.Format(LikeValueString[index - 1], Escape(value.ToString(), "")));
}
else if (index == -1)
{
columns.AppendFormat("{0}={1},", columnInfo.Name, entityPersister.Driver.FormatNameForSql(columnInfo.Name));
if (columnInfo.IsNullable && IsNullValue(value))
{
entityPersister.SetDbParamter(session, parameters, columnInfo.Name, DBNull.Value);
}
else
{
entityPersister.SetDbParamter(session, parameters, columnInfo.Name, value);
}
}
}
}
var where = GetWhereCaluse(rConditaion);
var result = new CmdParameter();
if (where.Length > 0 && columns.Length > 0)
{
result.Parameters = parameters.ToArray();
result.CommandText = string.Format("UPDATE {0} SET {1} WHERE {2}{3}", classMetadata.TableName, columns.Remove(columns.Length - 1, 1), where, entityPersister.Driver.MultipleQueriesSeparator);
}
return result;
}
public static CmdParameter CreateSelectSql2(ISessionImplementor session, IEntityPersister entityPersister, Criteria criteria, Order order)
{
var classMetadata = entityPersister.ClassMetadata;
var result = new CmdParameter();
//if (string.IsNullOrEmpty(obj.Conditaion))
//{
// var columnInfo = classMetadata.ColumnInfos.Find(t => t.IsPrimaryKey);
// if (columnInfo != null)
// {
// var v = columnInfo.PropertyInfo.FastGetValue(obj);
// if (!IsNull(v)) result = CreateSelectSql(session, entityPersister, v);
// }
//}
var parameters = new List<DbParameter>();
//entityPersister.Driver.FormatNameForSql(columnInfo.Name)
// string rConditaion = GetConditaion(session, entityPersister, obj, out parameters);
var sql = new StringBuilder("SELECT ");
//sql.Append(string.IsNullOrEmpty(obj.ResultColumns) ? "*" : obj.ResultColumns);
sql.Append("*");
sql.AppendFormat(" FROM {0} ", classMetadata.TableName);
if (criteria != null && criteria.CriterionList != null && criteria.CriterionList.Count > 0)
{
criteria.Driver = entityPersister.Driver;
criteria.DicParameter = entityPersister.GetParameter(session);
criteria.DbParameter = parameters;
sql.Append(" WHERE ").Append(criteria.ToString(classMetadata));
result.Parameters = parameters.ToArray();
}
// entityPersister.SetDbParamter(session, parameters, columnInfo.Name, value);
//var where = GetWhereCaluse(rConditaion);
//if (where.Length > 0)
//{
// sql.AppendFormat(" WHERE {0} ", where);
// result.Parameters = parameters.ToArray();
//}
//sql.Append(obj.OrderBy ?? "" );
sql.Append(order != null ? order.ToString(classMetadata) : "");
sql.Append(entityPersister.Driver.MultipleQueriesSeparator);
result.CommandText = sql.ToString();
log.Info(sql);
return result;
}
public static CmdParameter CreateSelectSql(ISessionImplementor session, IEntityPersister entityPersister, IEntity obj)
{
var classMetadata = entityPersister.ClassMetadata;
var result = new CmdParameter();
if (string.IsNullOrEmpty(obj.Conditaion))
{
var columnInfo = classMetadata.ColumnInfos.Find(t => t.IsPrimaryKey);
if (columnInfo != null)
{
var v = columnInfo.PropertyInfo.FastGetValue(obj);
if (!IsNull(v)) result = CreateSelectSql(session, entityPersister, v);
}
}
List<DbParameter> parameters;
string rConditaion = GetConditaion(session, entityPersister, obj, out parameters);
var sql = new StringBuilder("SELECT ");
sql.Append(string.IsNullOrEmpty(obj.ResultColumns) ? "*" : obj.ResultColumns);
//sql.Append("*"); //delete
sql.AppendFormat(" FROM {0} ", classMetadata.TableName);
var where = GetWhereCaluse(rConditaion);
if (where.Length > 0)
{
sql.AppendFormat(" WHERE {0} ", where);
result.Parameters = parameters.ToArray();
}
sql.Append(obj.OrderBy ?? "");
sql.Append(entityPersister.Driver.MultipleQueriesSeparator);
result.CommandText = sql.ToString();
return result;
}
public static CmdParameter CreateSelectSql(ISessionImplementor session, IEntityPersister entityPersister, object pKey)
{
var result = new CmdParameter();
if (pKey != null)
{
var classMetadata = entityPersister.ClassMetadata;
var driver = entityPersister.Driver;
result.CommandText = string.Format("SELECT * FROM {0} WHERE {1}={2}", classMetadata.TableName, classMetadata.PrimaryKey, driver.FormatNameForSql(classMetadata.PrimaryKey));
var parameters = new List<DbParameter>();
entityPersister.SetDbParamter(session, parameters, classMetadata.PKColumnName, pKey);
result.Parameters = parameters.ToArray();
}
return result;
}
public static CmdParameter CreateInsertSql(ISessionImplementor session, IEntityPersister entityPersister, IEntity obj)
{
var result = new CmdParameter();
var classMetadata = entityPersister.ClassMetadata;
var driver = entityPersister.Driver;
var sql = new StringBuilder("INSERT INTO");
sql.AppendFormat(" {0} (", classMetadata.TableName);
var values = new StringBuilder();
var tParameters = new List<DbParameter>();
var columnInfos = classMetadata.ColumnInfos.FindAll(t => t.IsColumn);
foreach (var columnInfo in columnInfos)
{
var v = columnInfo.PropertyInfo.FastGetValue(obj);
if (IsNull(v)) continue;
if (columnInfo.IsNullable && IsNullValue(v)) continue;
sql.AppendFormat("{0},", columnInfo.Name);
values.AppendFormat("{0},", driver.FormatNameForSql(columnInfo.Name));
entityPersister.SetDbParamter(session, tParameters, columnInfo.Name, v);
}
if (values.Length > 0)
{
sql.Replace(',', ')', sql.Length - 1, 1);
sql.AppendFormat(" VALUES ({0}){1}", values.Remove(values.Length - 1, 1), driver.MultipleQueriesSeparator);
result.CommandText = sql.ToString();
result.Parameters = tParameters.ToArray();
}
return result;
}
public static CmdParameter CreateDeleteSql(ISessionImplementor session, IEntityPersister entityPersister, IEntity obj)
{
var classMetadata = entityPersister.ClassMetadata;
if (string.IsNullOrEmpty(obj.Conditaion))
{
var v = classMetadata.ColumnInfos.Find(t => t.IsPrimaryKey).PropertyInfo.FastGetValue(obj);
return !IsNull(v) ? CreateDeleteSql(session, entityPersister, (int)v) : null;
}
List<DbParameter> parameters;
string rConditaion = GetConditaion(session, entityPersister, obj, out parameters);
var where = GetWhereCaluse(rConditaion);
var result = new CmdParameter();
if (where.Length > 0)
{
result.Parameters = parameters.ToArray();
result.CommandText = string.Format("DELETE FROM {0} WHERE {1}{2}", classMetadata.TableName, where, entityPersister.Driver.MultipleQueriesSeparator);
}
return result;
}
public static CmdParameter CreateDeleteSql(ISessionImplementor session, IEntityPersister entityPersister, params object[] pKeys)
{
var result = new CmdParameter();
var classMetadata = entityPersister.ClassMetadata;
var driver = entityPersister.Driver;
if (pKeys.Length == 1)
{
result.CommandText = string.Format("DELETE FROM {0} WHERE {1}={2}{3}", classMetadata.TableName, classMetadata.PKColumnName, driver.FormatNameForSql(classMetadata.PKColumnName), driver.MultipleQueriesSeparator);
var parameters = new List<DbParameter>();
entityPersister.SetDbParamter(session, parameters, classMetadata.PKColumnName, pKeys[0]);
result.Parameters = parameters.ToArray();
}
else if (pKeys.Length > 1)
{
result.CommandText = string.Format("DELETE FROM {0} WHERE {1} {2}{3}", classMetadata.TableName, classMetadata.PKColumnName, GetInExpression(pKeys), driver.MultipleQueriesSeparator);
}
return result;
}