1 /**
2 * ksql工具类3 *4 */
5 public classKSQLBuilder {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 voidinitInstance(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 *@paramentity31 *@paramobjVal32 *@return
33 */
34 public staticList getParams(EntityObjectInfo entity, IObjectValue objVal) {35 List params = newArrayList();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 instanceofLinkPropertyInfo) {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 returnparams;56 }57
58 /**
59 * 根据批量的数据，构建预编译参数值60 *61 *@paramobjCols62 *@paramlist63 *@return
64 */
65 public staticList getBatchInsertParams(IObjectCollection objCols) {66 List list = newArrayList();67 if (objCols == null ||objCols.isEmpty()) {68 returnlist;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 returnlist;78 }79
80 /**
81 * 此方法适合单条数据插入使用82 *83 *@paramobjVal84 *@paramlist85 *@return
86 */
87 public staticString 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 returnksql;93 }94 public staticString getInsertSQL(BOSObjectType bosType) {95 EntityObjectInfo entity =metaDataLoader.getEntity(bosType);96 returngetInsertSQL(entity);97 }98 protected staticString getInsertSQL(EntityObjectInfo entity) {99 StringBuffer ksql = newStringBuffer();100 StringBuffer params = newStringBuffer();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 returnksql.toString();118 }119
120 /**
121 * 根据指定的bostype构建查询的SQL122 *123 *@parambosType124 * 实体的BOSTYpe125 *@paramfields126 * 返回查询的字段，方便在外部获取结果集数据,方法调用时传递空集合即可127 *@paramselectors128 * 指定的查询字段，经营分析中暂时用不到，以后扩展可使用129 *@return
130 */
131 public staticString getQuerySQL(BOSObjectType bosType, List fields, SelectorItemCollection selectors) {132 StringBuffer ksql = newStringBuffer();133 if (fields == null) {134 fields = newArrayList();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 returnksql.toString();156 }157
158 /**
159 * 获取更新数据的SQL160 *161 *@paramobjVal162 * 被更新的业务对象，由于更新时需要用到主键字段，所以主键不能为空163 *@paramparams164 * 参数165 *@paramupdateSelectors166 * 更新的字段167 *@throwsInvalidDAOMetaDataException168 */
169 public static String getUpdateSQL(IObjectValue objVal, List params, SelectorItemCollection updateSelectors) throwsInvalidDAOMetaDataException {170
171 StringBuffer ksql = newStringBuffer();172 if (objVal == null || objVal.getString(objVal.getPKField()) == null) {173 returnksql.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 instanceofLinkPropertyInfo) {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 returnksql.toString();208 }209
210 /**
211 * 联接属性的处理212 *213 *@paramfullName214 *@return
215 */
216 private staticString 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 returnfullName;224 }225
226 /**
227 * 获取字段228 */
229 protected staticString[] getTableFields(PropertyCollection properties) {230 int count =properties.size();231 List fieldsList = newArrayList();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(newString[fieldsList.size()]);244 }245 }