关于动态生成SQL语句的简单实现
遇到一个场景，新旧系统使用维护类似的表结构和规范，需要将新的数据迁移到旧的系统中，当时采取的方案就是在新系统开发，将数据生成新增/更新的sql语句，在导入到旧库中，所以简单做了一个符合当时场景的动态生成SQL语句的实现。
主要考虑到问题：
1.解决无自增外键生成INSERT SQL脚本和UPDATE-INSERT SQL脚本的实现
2.解决有一个自增外键生成INSERT SQL脚本和UPDATE-INSERT SQL脚本的实现
具体用法和使用场景已在代码中注释，代码如下：
1 using System; 2 using System.Collections.Generic; 3 using System.IO; 4 using System.Linq; 5 using System.Linq.Expressions; 6 using System.Reflection; 7 using System.Text; 8 using System.Text.RegularExpressions; 9 10 namespace PostgreSqlBuilder 11 { 12 public class PostgreSqlBuilder 13 { 14 /// <summary> 15 /// 模式 16 /// </summary> 17 public string Schema { get; set; } 18 19 public PostgreSqlBuilder() { } 20 public PostgreSqlBuilder(string schema) 21 { 22 this.Schema = schema; 23 } 24 25 /// <summary> 26 /// 批量生成INSERT SQL脚本-无自增外键 27 /// </summary> 28 /// <typeparam name="T">目标对象类型</typeparam> 29 /// <param name="collection">目标对象集合</param> 30 /// <param name="key">目标对象主键名</param> 31 /// <returns></returns> 32 public string BatchInsertSql<T>(IEnumerable<T> collection, string key = "Id") where T : class 33 { 34 var sbBatch = new StringBuilder(); 35 foreach (var obj in collection) 36 { 37 sbBatch.AppendLine(InsertSqlNoIncForeignKey(obj, key)); 38 } 39 return sbBatch.ToString(); 40 } 41 /// <summary> 42 /// 批量生成INSERT SQL脚本-有一个自增外键； 43 /// 需先生成主键表sql脚本插入成功后，再使用该方法； 44 /// 要求参数constraint为主外键表共有字段； 45 /// </summary> 46 /// <typeparam name="T">目标对象类型</typeparam> 47 /// <typeparam name="TOuter">外键关联对象类型</typeparam> 48 /// <param name="collection">目标对象集合</param> 49 /// <param name="outKey">目标对象外键名</param> 50 /// <param name="constraint">外键关联对象唯一约束属性名(非主键)</param> 51 /// <returns>INSERT SQL脚本</returns> 52 public string BatchInsertSqlWithOuterKeyId<T,TOuter>(IEnumerable<T> collection 53 , string outKey, string constraint) where T : class where TOuter : class 54 { 55 var sbBatch = new StringBuilder(); 56 foreach (var obj in collection) 57 { 58 sbBatch.AppendLine(InsertSqlWithOuterKeyId<T,TOuter>(obj, outKey, constraint)); 59 } 60 return sbBatch.ToString(); 61 } 62 /// <summary> 63 /// 批量生成UPDATE-INSERT SQL脚本 64 /// </summary> 65 /// <typeparam name="T">目标对象类型</typeparam> 66 /// <param name="collection">目标对象集合</param> 67 /// <param name="key">目标对象主键名</param> 68 /// <param name="conflicts">冲突</param> 69 /// <returns></returns> 70 public string BatchUpSertSql<T>(IEnumerable<T> collection, string key = "Id", params string[] conflicts) where T : class 71 { 72 var sbBatch = new StringBuilder(); 73 foreach (var obj in collection) 74 { 75 sbBatch.AppendLine(UpSertSql(obj, key, conflicts)); 76 } 77 return sbBatch.ToString(); 78 } 79 80 #region 生成INSERT SQL脚本-无自增外键 81 82 /// <summary> 83 /// 生成INSERT SQL脚本-无自增外键 84 /// </summary> 85 /// <typeparam name="T">目标对象类型</typeparam> 86 /// <param name="targetObj">目标对象</param> 87 /// <param name="targetObjKey">目标对象主键名</param> 88 /// <returns>INSERT SQL脚本</returns> 89 public string InsertSqlNoIncForeignKey<T>(T targetObj, string targetObjKey = "Id") where T : class 90 { 91 var type = typeof(T); 92 var tableName = GetTableName(type); 93 var propertyInfos = type.GetProperties(BindingFlags.Instance | BindingFlags.Public) 94 .Where(p => IsFundamental(p.PropertyType)); 95 var sbColumn = new StringBuilder(100); 96 var sbValue = new StringBuilder(200); 97 sbColumn.Append("INSERT INTO " + tableName + " ("); 98 sbValue.Append(" VALUES ("); 99 foreach (var propertyInfo in propertyInfos) 100 { 101 //自增主键(默认int类型),过滤 102 if (propertyInfo.Name.Equals(targetObjKey) && propertyInfo.PropertyType == typeof(int)) continue; 103 sbColumn.Append($" {GetFieldName(propertyInfo)}"); 104 sbColumn.Append(" ,"); 105 //获取属性值 106 var attribute = 107 propertyInfo.GetCustomAttribute(typeof(SetPostgreSqlValueAttribute), false); 108 var propertyValue = propertyInfo.GetValue(targetObj); 109 if (attribute != null) 110 { 111 if (attribute is SetPostgreSqlValueAttribute setSqlValueAttribute) 112 sbValue.Append($"{setSqlValueAttribute.Value} ,"); 113 } 114 else if (propertyValue == null) 115 sbValue.Append(" null,"); 116 else 117 { 118 sbValue.Append(" '"); 119 sbValue.Append($"{propertyValue?.ToString()}"); 120 sbValue.Append("' ,"); 121 } 122 } 123 sbColumn.Replace(',', ')', sbColumn.Length - 1, 1); 124 sbValue.Replace(',', ')', sbValue.Length - 1, 1); 125 sbColumn.Append(sbValue).Append(';'); 126 return sbColumn.ToString(); 127 } 128 129 /// <summary> 130 /// 生成INSERT SQL脚本-无自增外键 131 /// </summary> 132 /// <typeparam name="T">目标对象类型</typeparam> 133 /// <param name="targetObj">目标对象</param> 134 /// <param name="targetObjKeyExpr">目标对象属性表达式-主键</param> 135 /// <returns>INSERT SQL脚本</returns> 136 public string InsertSqlNoIncForeignKey<T, TK>(T targetObj, Expression<Func<T, TK>> targetObjKeyExpr) where T : class 137 { 138 var type = typeof(T); 139 var tableName = GetTableName(type); 140 var propertyInfos = type.GetProperties(BindingFlags.Instance | BindingFlags.Public) 141 .Where(p => IsFundamental(p.PropertyType)); 142 143 if (targetObjKeyExpr.Body is not MemberExpression body) 144 throw new ArgumentException($"'targetObjKeyExpr'不是MemberExpression, 表达式：{targetObjKeyExpr}"); 145 var keyPropertyInfo = (PropertyInfo)body.Member; 146 147 var sbColumn = new StringBuilder(100); 148 var sbValue = new StringBuilder(200); 149 sbColumn.Append("INSERT INTO " + tableName + " ("); 150 sbValue.Append(" VALUES ("); 151 foreach (var propertyInfo in propertyInfos) 152 { 153 //自增主键(默认int类型),过滤 154 if (propertyInfo.Name.Equals(keyPropertyInfo.Name) && 155 keyPropertyInfo.PropertyType == typeof(int)) continue; 156 sbColumn.Append($" {GetFieldName(propertyInfo)}"); 157 sbColumn.Append(" ,"); 158 //获取属性值 159 var attribute = 160 propertyInfo.GetCustomAttribute(typeof(SetPostgreSqlValueAttribute), false); 161 var propertyValue = propertyInfo.GetValue(targetObj); 162 if (attribute != null) 163 { 164 if (attribute is SetPostgreSqlValueAttribute setSqlValueAttribute) 165 sbValue.Append($"{setSqlValueAttribute.Value} ,"); 166 } 167 else if (propertyValue == null) 168 sbValue.Append(" null,"); 169 else 170 { 171 sbValue.Append(" '"); 172 sbValue.Append($"{propertyValue?.ToString()}"); 173 sbValue.Append("' ,"); 174 } 175 } 176 sbColumn.Replace(',', ')', sbColumn.Length - 1, 1); 177 sbValue.Replace(',', ')', sbValue.Length - 1, 1); 178 sbColumn.Append(sbValue).Append(';'); 179 return sbColumn.ToString(); 180 } 181 182 #endregion 183 184 /// <summary> 185 /// 生成INSERT SQL脚本-有一个自增外键； 186 /// 需先生成主键表sql脚本插入成功后，再使用该方法； 187 /// 要求参数constraint为主外键表共有字段； 188 /// </summary> 189 /// <typeparam name="T">目标对象类型</typeparam> 190 /// <typeparam name="TForeign">外键关联对象类型</typeparam> 191 /// <param name="targetObj">目标对象</param> 192 /// <param name="foreignKey">目标对象外键名</param> 193 /// <param name="constraint">外键关联对象唯一约束属性名(非主键)</param> 194 /// <param name="targetObjKey">目标对象主键名</param> 195 /// <param name="foreignObjKey">外键关联对象主键名</param> 196 /// <returns>INSERT SQL脚本</returns> 197 public string InsertSqlWithOuterKeyId<T, TForeign>(T targetObj, string foreignKey, string constraint, string targetObjKey = "Id", string foreignObjKey = "Id") where T : class where TForeign : class 198 { 199 var type = typeof(T); 200 var tableName = GetTableName(typeof(T)); 201 var propertyInfos = type.GetProperties(BindingFlags.Instance | BindingFlags.Public) 202 .Where(p => IsFundamental(p.PropertyType)); 203 var sbColumn = new StringBuilder(100); 204 var sbValue = new StringBuilder(200); 205 sbColumn.Append("INSERT INTO " + tableName + " ("); 206 sbValue.Append(" VALUES ("); 207 foreach (var propertyInfo in propertyInfos) 208 { 209 //自增主键(默认int类型),过滤 210 if (propertyInfo.Name.Equals(targetObjKey) && propertyInfo.PropertyType == typeof(int)) continue; 211 sbColumn.Append($" {GetFieldName(propertyInfo)}"); 212 sbColumn.Append(" ,"); 213 //获取属性值 214 if (propertyInfo.Name.Equals(foreignKey)) 215 { 216 sbValue.Append( 217 @$" (SELECT {ObjectTableNameConvention(foreignObjKey)} FROM {GetTableName(typeof(TForeign))} WHERE {ObjectTableNameConvention(constraint)}='{typeof(T).GetProperty(constraint)?.GetValue(targetObj)}'),"); 218 continue; 219 } 220 221 var setSqlValueAttributes = 222 propertyInfo.GetCustomAttributes(typeof(SetPostgreSqlValueAttribute), false); 223 if (setSqlValueAttributes.Length > 0) 224 { 225 var setSqlValueAttribute = setSqlValueAttributes[0] as SetPostgreSqlValueAttribute; 226 sbValue.Append($"{setSqlValueAttribute?.Value} ,"); 227 } 228 else if (propertyInfo.GetValue(targetObj) == null) 229 { 230 sbValue.Append(" null,"); 231 } 232 else 233 { 234 sbValue.Append(" '"); 235 sbValue.Append($"{propertyInfo.GetValue(targetObj)}"); 236 sbValue.Append("' ,"); 237 } 238 } 239 sbColumn.Replace(',', ')', sbColumn.Length - 1, 1); 240 sbValue.Replace(',', ')', sbValue.Length - 1, 1); 241 sbColumn.Append(sbValue).Append(';'); 242 return sbColumn.ToString(); 243 } 244 /// <summary> 245 /// 生成UPDATE-INSERT SQL脚本 246 /// </summary> 247 /// <typeparam name="T">目标对象类型</typeparam> 248 /// <param name="targetObj">目标对象</param> 249 /// <param name="targetObjKey">目标对象主键名</param> 250 /// <param name="conflicts">冲突</param> 251 /// <returns>UPDATE-INSERT SQL脚本</returns> 252 public string UpSertSql<T>(T targetObj, string targetObjKey = "Id", params string[] conflicts) where T : class 253 { 254 var type = typeof(T); 255 var tableName = GetTableName(typeof(T)); 256 var propertyInfos = type.GetProperties(BindingFlags.Instance | BindingFlags.Public) 257 .Where(p => IsFundamental(p.PropertyType)); 258 var sbColumn = new StringBuilder(100); 259 var sbValue = new StringBuilder(200); 260 var sbConflict = new StringBuilder(100); 261 sbColumn.Append("INSERT INTO " + tableName + " ("); 262 sbValue.Append(" VALUES ("); 263 sbConflict.Append(" ON CONFLICT("); 264 foreach (var conflict in conflicts) 265 { 266 if (!propertyInfos.Select(p => p.Name).Contains(conflict)) 267 { 268 throw new ArgumentNullException($"{typeof(T).Name}不存在字段名{conflict}"); 269 } 270 sbConflict.Append($" {ObjectTableNameConvention(conflict)},"); 271 } 272 sbConflict.Replace(',', ')', sbConflict.Length - 1, 1).Append(" DO UPDATE SET"); 273 foreach (var propertyInfo in propertyInfos) 274 { 275 //自增主键(默认int类型),过滤 276 if (propertyInfo.Name.Equals(targetObjKey) && propertyInfo.PropertyType == typeof(int)) continue; 277 sbColumn.Append($" {GetFieldName(propertyInfo)}"); 278 sbColumn.Append(" ,"); 279 //获取属性值 280 var value = string.Empty; 281 var setSqlValueAttributes = 282 propertyInfo.GetCustomAttributes(typeof(SetPostgreSqlValueAttribute), false); 283 if (setSqlValueAttributes.Length > 0) 284 { 285 if (setSqlValueAttributes[0] is SetPostgreSqlValueAttribute setSqlValueAttribute) 286 { 287 value = $"{setSqlValueAttribute.Value} ,"; 288 sbValue.Append(value); 289 } 290 } 291 else if (propertyInfo.GetValue(targetObj) == null) 292 { 293 value = " null,"; 294 sbValue.Append(value); 295 } 296 else 297 { 298 value = " '" + $"{propertyInfo.GetValue(targetObj)}" + "' ,"; 299 sbValue.Append(value); 300 } 301 sbConflict.Append($" {GetFieldName(propertyInfo)}={value}"); 302 } 303 sbColumn.Replace(',', ')', sbColumn.Length - 1, 1); 304 sbValue.Replace(',', ')', sbValue.Length - 1, 1); 305 sbConflict.Remove(sbConflict.Length - 1, 1); 306 sbColumn.Append(sbValue).Append(sbConflict).Append(';'); 307 return sbColumn.ToString(); 308 } 309 310 #region 内部方法 311 312 /// <summary> 313 /// 获取表名 314 /// </summary> 315 /// <param name="type">类型对象</param> 316 /// <returns>表名</returns> 317 protected string GetTableName(Type type) 318 { 319 var className = type.Name; 320 return string.IsNullOrWhiteSpace(this.Schema) 321 ? ObjectTableNameConvention(className) 322 : ObjectTableNameConvention(this.Schema) + "." + ObjectTableNameConvention(className); 323 } 324 325 /// <summary> 326 /// 获取表字段名 327 /// </summary> 328 /// <param name="propertyInfo">对象属性信息</param> 329 /// <returns>表字段名</returns> 330 protected string GetFieldName(PropertyInfo propertyInfo) 331 { 332 return ObjectTableNameConvention(propertyInfo.Name); 333 } 334 335 /// <summary> 336 /// 对象-表 名称转换 337 /// </summary> 338 /// <param name="objectName">对象中名称</param> 339 /// <returns>表中名称</returns> 340 protected string ObjectTableNameConvention(string objectName) 341 { 342 var pattern = 343 new Regex(@"[A-Z]{2,}(?=[A-Z][a-z]+[0-9]*|\b)|[A-Z]?[a-z]+[0-9]*|[A-Z]|[0-9]+"); 344 var snakeCaseName = objectName == null 345 ? null 346 : string 347 .Join("_", pattern.Matches(objectName).Cast<Match>().Select(m => m.Value)) 348 .ToLower(); 349 return snakeCaseName; 350 } 351 352 /// <summary> 353 /// 是否基础类型(值类型，枚举，字符串及可空类型) 354 /// </summary> 355 /// <param name="type"></param> 356 /// <returns></returns> 357 protected bool IsFundamental(Type type) 358 { 359 return type.IsPrimitive || type.IsEnum || type == typeof(string) || type == typeof(DateTime) || 360 (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>)); 361 } 362 363 #endregion 364 365 } 366 }
基本功能已经实现，后面会又碰到一种新问题，就是存在生成空字段默认值的问题：即当时生成sql是全属性生成，空值为null而不是空值就直接过滤不参加生成sql语句。为了达到这一目的，改造的方法就是在反射的时候获取具有默认值的字段，能在元数据加点标记用特性来实现（上面的代码是已经改造后的）。特性定义如下：
1 using System; 2 using System.Collections.Generic; 3 using System.Text; 4 5 namespace PostgreSqlBuilder 6 { 7 [AttributeUsage(AttributeTargets.Property, AllowMultiple = false, Inherited = false)] 8 public class SetPostgreSqlValueAttribute : Attribute 9 { 10 private readonly SqlValueType _type; 11 private readonly string _value; 12 13 public SetPostgreSqlValueAttribute(SqlValueType type, string value) 14 { 15 _type = type; 16 _value = value; 17 } 18 public string Value 19 { 20 get 21 { 22 return _type switch 23 { 24 SqlValueType.Constant => "'" + _value + "'", 25 SqlValueType.Function or SqlValueType.KeyWord => _value, 26 _ => throw new NotSupportedException($"_type值错误{_type}"), 27 }; 28 } 29 } 30 } 31 }
定义的枚举：
1 using System; 2 using System.Collections.Generic; 3 using System.Text; 4 5 namespace PostgreSqlBuilder 6 { 7 public enum SqlValueType 8 { 9 /// <summary> 10 /// slq常量 11 /// </summary> 12 Constant = 0, 13 /// <summary> 14 /// sql方法 15 /// </summary> 16 Function = 1, 17 /// <summary> 18 /// sql关键字 19 /// </summary> 20 KeyWord = 2 21 } 22 }
以上就是完整地代码实现，测试一下（实例用的是新增无自增外键sql语句生成）：
1 using PostgreSqlBuilder; 2 using System; 3 using System.Collections.Generic; 4 using System.Linq; 5 using System.Text; 6 using System.Threading.Tasks; 7 8 namespace Dtos 9 { 10 public class Person 11 { 12 public int Id { get; set; } 13 public string Name { get; set; } 14 public int? Age { get; set; } 15 16 [SetPostgreSqlValue(SqlValueType.Function,"now()")] 17 public DateTime? DateTime { get; set; } 18 19 public List<Pet> Pets { get; set; } = new List<Pet>(); 20 21 public override string ToString() 22 { 23 return $"name:{Name},age:{Age},dateTime:{DateTime},pets:{string.Join(';',Pets)}"; 24 } 25 } 26 }
1 using Dtos; 2 using System; 3 using System.Collections.Generic; 4 using System.Linq; 5 6 namespace PostgreSqlBuilder.ConsoleTest 7 8 { 9 class Program 10 { 11 static void Main(string[] args) 12 { 13 14 Person person = new (){ Age = 0, Name = "张三"}; 15 16 PostgreSqlBuilder sqlHelper = new ("scale"); 17 var sql1 = sqlHelper.InsertSqlNoIncForeignKey(person, p => p.Id); 18 19 Console.WriteLine(sql1); 20 Console.ReadKey(); 21 } 22 } 23 }
测试后果：
