近期在项目中出于性能考虑,需要将单据的数据使用SQL的方式直接写入数据库或修改数据,经过自己的捉摸,自己实现了一些工具方法,用于动态生成有关SQL,目前只有:insert、update,具体实现如下(后面有重构过,稍后会同步上来):
1 /** 2 * ksql工具类 3 * 4 */ 5 public class KSQLBuilder { 6 public static IMetaDataLoader metaDataLoader = null; 7 public static final String splitchar = ",";// 字段间 的分隔符 8 public static final String KEY_SELECT = "SELECT"; 9 public static final String KEY_FROM = "FROM"; 10 public static final String KEY_UPDATE = "UPDATE"; 11 public static final String KEY_INSERT = "INSERT INTO"; 12 public static final String KEY_VALUES = "VALUES"; 13 public static final String KEY_WHERE = "WHERE"; 14 public static final String KEY_AND = "AND"; 15 public static final String KEY_OR = "OR"; 16 17 public static void initInstance(Context ctx) { 18 if (metaDataLoader == null) { 19 if (ctx == null) { 20 metaDataLoader = MetaDataLoaderFactory.getRemoteMetaDataLoader(); 21 } else { 22 metaDataLoader = MetaDataLoaderFactory.getLocalMetaDataLoader(ctx); 23 } 24 } 25 } 26 27 /** 28 * 根据单个对象生成预编译语句使用的参数 29 * 30 * @param entity 31 * @param objVal 32 * @return 33 */ 34 public static List getParams(EntityObjectInfo entity, IObjectValue objVal) { 35 List params = new ArrayList(); 36 if (entity == null) { 37 entity = metaDataLoader.getEntity(objVal.getBOSType()); 38 } 39 PropertyInfo property = null; 40 String propName = ""; 41 PropertyCollection properties = entity.getInheritedNoDuplicatedPropertiesRuntime(); 42 for (int i = 0; i < properties.size(); i++) { 43 property = properties.get(i); 44 propName = property.getName(); 45 if (property instanceof LinkPropertyInfo) { 46 if (objVal.getObjectValue(propName) != null) { 47 params.add(objVal.getObjectValue(propName).getString("id"));// 联接属性取ID 48 } else { 49 params.add(null); 50 } 51 } else { 52 params.add(objVal.get(propName)); 53 } 54 } 55 return params; 56 } 57 58 /** 59 * 根据批量的数据,构建预编译参数值 60 * 61 * @param objCols 62 * @param list 63 * @return 64 */ 65 public static List getBatchInsertParams(IObjectCollection objCols) { 66 List list = new ArrayList(); 67 if (objCols == null || objCols.isEmpty()) { 68 return list; 69 } 70 BOSObjectType bosType = objCols.getObject(0).getBOSType(); 71 EntityObjectInfo entity = metaDataLoader.getEntity(bosType); 72 IObjectValue objVal = null; 73 for (int j = 0, k = objCols.size(); j < k; j++) { 74 objVal = objCols.getObject(j); 75 list.add(getParams(entity, objVal)); 76 } 77 return list; 78 } 79 80 /** 81 * 此方法适合单条数据插入使用 82 * 83 * @param objVal 84 * @param list 85 * @return 86 */ 87 public static String getInsertSQL(IObjectValue objVal, List list) { 88 BOSObjectType bosType = objVal.getBOSType(); 89 EntityObjectInfo entity = metaDataLoader.getEntity(bosType); 90 String ksql = getInsertSQL(entity); 91 list.addAll(getParams(entity, objVal)); 92 return ksql; 93 } 94 public static String getInsertSQL(BOSObjectType bosType) { 95 EntityObjectInfo entity = metaDataLoader.getEntity(bosType); 96 return getInsertSQL(entity); 97 } 98 protected static String getInsertSQL(EntityObjectInfo entity) { 99 StringBuffer ksql = new StringBuffer(); 100 StringBuffer params = new StringBuffer(); 101 PropertyCollection properties = entity.getInheritedNoDuplicatedPropertiesRuntime(); 102 String[] fields = getTableFields(properties); 103 ksql.append(KEY_INSERT).append(" ").append(entity.getTable().getName()).append("\n"); 104 ksql.append("("); 105 for (int i = 0; i < fields.length; i++) { 106 ksql.append(fields[i]).append(","); 107 if (fields[i].equals("FID")) { 108 params.append("newBosId('").append(entity.getBOSType().toString()).append("'),"); 109 } else { 110 params.append("?,"); 111 } 112 } 113 ksql = ksql.deleteCharAt(ksql.lastIndexOf(",")); 114 ksql.append(")"); 115 params = params.deleteCharAt(params.lastIndexOf(",")); 116 ksql.append(KEY_VALUES).append("(").append(params.toString()).append(")"); 117 return ksql.toString(); 118 } 119 120 /** 121 * 根据指定的bostype构建查询的SQL 122 * 123 * @param bosType 124 * 实体的BOSTYpe 125 * @param fields 126 * 返回查询的字段,方便在外部获取结果集数据,方法调用时传递空集合即可 127 * @param selectors 128 * 指定的查询字段,经营分析中暂时用不到,以后扩展可使用 129 * @return 130 */ 131 public static String getQuerySQL(BOSObjectType bosType, List fields, SelectorItemCollection selectors) { 132 StringBuffer ksql = new StringBuffer(); 133 if (fields == null) { 134 fields = new ArrayList(); 135 } 136 EntityObjectInfo entity = metaDataLoader.getEntity(bosType); 137 PropertyCollection properties = entity.getInheritedNoDuplicatedPropertiesRuntime(); 138 String tableName = entity.getTable().getName(); 139 PropertyInfo property = null; 140 String field = ""; // 表的字段 141 String propName = "";// 表字段的别名,例如 [ID] select FID AS ID 142 ksql.append(KEY_SELECT).append("\n"); 143 for (int i = 0, c = properties.size(); i < c; i++) { 144 property = properties.get(i); 145 propName = property.getName(); 146 if (property.getMappingField() == null || StringUtils.isEmpty(property.getMappingField().getName())) { 147 continue; 148 } 149 field = property.getMappingField().getName(); 150 ksql.append(field).append(" AS ").append(propName).append(",\n"); 151 fields.add(propName); 152 } 153 ksql = ksql.deleteCharAt(ksql.lastIndexOf(",")); 154 ksql.append(KEY_FROM).append("\n").append(tableName); 155 return ksql.toString(); 156 } 157 158 /** 159 * 获取更新数据的SQL 160 * 161 * @param objVal 162 * 被更新的业务对象,由于更新时需要用到主键字段,所以主键不能为空 163 * @param params 164 * 参数 165 * @param updateSelectors 166 * 更新的字段 167 * @throws InvalidDAOMetaDataException 168 */ 169 public static String getUpdateSQL(IObjectValue objVal, List params, SelectorItemCollection updateSelectors) throws InvalidDAOMetaDataException { 170 171 StringBuffer ksql = new StringBuffer(); 172 if (objVal == null || objVal.getString(objVal.getPKField()) == null) { 173 return ksql.toString(); 174 } 175 String keyField = objVal.getPKField(); 176 String pk = objVal.getString(keyField); 177 178 BOSObjectType bosType = objVal.getBOSType(); 179 EntityObjectInfo bo = metaDataLoader.getEntity(bosType); 180 String tableName = bo.getTable().getName(); 181 ksql.append(KEY_UPDATE).append("\n"); 182 ksql.append(tableName).append("\n").append("set ").append("\n"); 183 for (int i = 0, fldSize = updateSelectors.size(); i < fldSize; ++i) { 184 SelectorItemInfo item = updateSelectors.get(i); 185 String itemName = item.getPropertyName(); 186 PropertyInfo fld = bo.getPropertyByNameRuntime(getFirstName(itemName)); 187 if (fld == null || fld.getMappingField() == null) 188 continue; 189 String fldName = fld.getName(); 190 if(fldName.equals(keyField) || fldName.equals("name")){ 191 continue; 192 } 193 String mappFld = MetaDataUtils.getColumnInfoByPropFromEntity(bo,fld).getName(); 194 195 ksql.append(mappFld).append("= ? ,"); 196 if (fld instanceof LinkPropertyInfo) { 197 params.add(objVal.getObjectValue(fldName).getString("id")); 198 } else { 199 params.add(objVal.get(fldName)); 200 } 201 } 202 ksql = ksql.deleteCharAt(ksql.lastIndexOf(",")).append("\n"); 203 String keyFldName = MetaDataUtils.getColumnInfoByPropFromEntity(bo,bo.getPropertyByNameRuntime(keyField)).getName(); 204 ksql.append(KEY_WHERE).append(" ").append(keyFldName); 205 ksql.append("= ? \n"); 206 params.add(pk); 207 return ksql.toString(); 208 } 209 210 /** 211 * 联接属性的处理 212 * 213 * @param fullName 214 * @return 215 */ 216 private static String getFirstName(String fullName) { 217 if (StringUtils.isEmpty(fullName)) 218 return ""; 219 int pos = fullName.indexOf('.'); 220 if (pos > 0) 221 return fullName.substring(0, pos); 222 else 223 return fullName; 224 } 225 226 /** 227 * 获取字段 228 */ 229 protected static String[] getTableFields(PropertyCollection properties) { 230 int count = properties.size(); 231 List fieldsList = new ArrayList(); 232 PropertyInfo property = null; 233 String field = ""; 234 for (int i = 0; i < count; i++) { 235 property = properties.get(i); 236 // LinkPropertyInfo 237 if (property.getMappingField() == null || StringUtils.isEmpty(property.getMappingField().getName())) { 238 continue; 239 } 240 field = property.getMappingField().getName(); 241 fieldsList.add(field); 242 } 243 return (String[]) fieldsList.toArray(new String[fieldsList.size()]); 244 } 245 }
以上方法中需要注意对数据的处理,比如业务对象中的枚举类型、布尔类型、日期类型的值需要处理为正确的格式才能确保和数据库的交互。
日期类型的属性,需要是java.sql.Date类型写入数据库,在EAS BOS中通过Info.getDate获取的就是java.sql.Date类型,同时info.getUtilDate获取的是另一个类型;
布尔类型的属性值,需要将true、false转换为1、0;枚举类型就不多说了。