没有通用码表的体系是不完美的,当年我用C#能实现的通用码表,现在在java一样的实现了,通用码表对提高开发效率和降低开发成本的作用巨大,开发可以专注写业务,而不必被太多的维护界面束缚。进而体现在产品竞争力上面,别人还是花大量时间做维护界面,我们建表了就有码表维护界面,只需要做特殊的维护界面(还是基于代码生成的基础组装)
通用码表原理看这里
通用码表离不开自己实现ORM,通过ORM解析实体的外键参照信息组装带外键列的查询,这样子表界面参照的父表名称才能显示名称而不是主键。
FK实现实例,没有实体的外键特性和ORM的底层支持,码表都是无稽之谈
/*** 码表查询,不分页** @param modelName 实体名称* @param param 查询条件参数,数据列名和值的键对* @param orderField 排序字段,如RowID Desc* @param returnCount 是否输出数据总行数* @param fields 显示字段,为空显示所有列,字段名称以英文','隔开,如:RowID,Code,Name* @param joiner 连接符,为空或不给则查询条件以且连接,给的话长度比参数少1* @param operators 操作符,为空或不给的话条件以等来判断,给的话与参数长度一致。如!=,<,>* @return*/@Overridepublic String QueryAllWithFKByName(String modelName, List<ParamDto> param, String orderField, boolean returnCount, String fields, List<String> joiner, List<String> operators) throws Exception {return QueryAllWithFKByName(modelName, param, orderField, returnCount, -1, -1, fields, joiner, operators);}/*** 根据条件+字段查询,查询结果按指定的页面把数据按JSON返回;* 该方法会把外键关联的字段查出来,用来取缔试图的查询** @param model 实体对象* @param param 查询条件参数,数据列名和值的键对* @param orderFields 排序字段,如RowID Desc* @param returnCount 是否输出数据总行数* @param pageSize 页大小。为-1,无条件查所有数据* @param pageIndex 第几页。为-1,无条件查询所有数据* @param fields 显示字段,为空显示所有列,字段名称以英文','隔开,如:RowID,Code,Name* @param joiner 连接符,为空或不给则查询条件以且连接,给的话长度比参数少1* @param operators 操作符,为空或不给的话条件以等来判断,给的话与参数长度一致。如!=,<,>* @param <T> 限定实体类型* @return 查询json串*/@Overridepublic <T> String QueryAllWithFK(T model, HashParam param, String orderFields, boolean returnCount, int pageSize, int pageIndex, String fields, List<String> joiner, List<String> operators) throws Exception {List<ParamDto> pdto = null;if (param != null) {pdto = param.GetParam();}return QueryAllWithFK(model, pdto, orderFields, returnCount, pageSize, pageIndex, fields, joiner, operators);}/*** 根据条件+字段查询,查询结果按指定的页面把数据按JSON返回;* 该方法会把外键关联的字段查出来,用来取缔试图的查询* 不分页** @param model 实体对象* @param param 查询条件参数,数据列名和值的键对* @param orderFields 排序字段,如RowID Desc* @param returnCount 是否输出数据总行数* @param fields 显示字段,为空显示所有列,字段名称以英文','隔开,如:RowID,Code,Name* @param joiner 连接符,为空或不给则查询条件以且连接,给的话长度比参数少1* @param operators 操作符,为空或不给的话条件以等来判断,给的话与参数长度一致。如!=,<,>* @param <T> 限定实体类型* @return 查询json串*/@Overridepublic <T> String QueryAllWithFK(T model, HashParam param, String orderFields, boolean returnCount, String fields, List<String> joiner, List<String> operators) throws Exception {List<ParamDto> pdto = null;if (param != null) {pdto = param.GetParam();}return QueryAllWithFK(model, pdto, orderFields, returnCount, fields, joiner, operators);}/*** 根据条件+字段查询,查询结果按指定的页面把数据按JSON返回;* 该方法会把外键关联的字段查出来,用来取缔试图的查询* 不分页** @param model 实体对象* @param param 查询条件参数,数据列名和值的键对* @param orderFields 排序字段,如RowID Desc* @param returnCount 是否输出数据总行数* @param fields 显示字段,为空显示所有列,字段名称以英文','隔开,如:RowID,Code,Name* @param joiner 连接符,为空或不给则查询条件以且连接,给的话长度比参数少1* @param operators 操作符,为空或不给的话条件以等来判断,给的话与参数长度一致。如!=,<,>* @param <T> 限定实体类型* @return 查询json串*/@Overridepublic <T> String QueryAllWithFK(T model, List<ParamDto> param, String orderFields, boolean returnCount, String fields, List<String> joiner, List<String> operators) throws Exception {return QueryAllWithFK(model, param, orderFields, returnCount, -1, -1, fields, joiner, operators);}/*** 根据条件+字段查询,查询结果按指定的页面把数据按JSON返回;* 该方法会把外键关联的字段查出来,用来取缔试图的查询** @param model 实体对象* @param param 查询条件参数,数据列名和值的键对* @param orderFields 排序字段,如RowID Desc* @param returnCount 是否输出数据总行数* @param pageSize 页大小。为-1,无条件查所有数据* @param pageIndex 第几页。为-1,无条件查询所有数据* @param fields 显示字段,为空显示所有列,字段名称以英文','隔开,如:RowID,Code,Name* @param joiner 连接符,为空或不给则查询条件以且连接,给的话长度比参数少1* @param operators 操作符,为空或不给的话条件以等来判断,给的话与参数长度一致。如!=,<,>* @param <T> 限定实体类型* @return 查询json串*/@Overridepublic <T> String QueryAllWithFK(T model, List<ParamDto> param, String orderFields, boolean returnCount, int pageSize, int pageIndex, String fields, List<String> joiner, List<String> operators) throws Exception {//json数据组装容器StringBuilder jsonsb = new StringBuilder();//查询起始行数int fromRow = -1;//查询结束行数int toRow = -1;//是否查询全部数据boolean findAll = false;//记录总行数int rowCount = 0;if (fields != null && !fields.isEmpty()) {fields = "," + fields + ",";}//如果未传入分页数据其中一个未-1,则认为部分页而查询所有数据if (pageIndex == -1 || pageSize == -1) {findAll = true;}//计算查询起始和结束行数else {fromRow = (pageIndex - 1) * pageSize;toRow = pageIndex * pageSize;}PreparedStatement pstat = null;ResultSet rst = null;LIS.DAL.ORM.Common.TableInfo tableInfo = LIS.DAL.ORM.Common.ModelToSqlUtil.GetTypeInfo(model);//根据表信息将查询参数组装成Select SQLString sql = LIS.DAL.ORM.Common.ModelToSqlUtil.GetSelectSqlByTableInfo(Manager().GetIDbFactory(factoryName), tableInfo, param, operators, joiner, orderFields, true, -1);//写SQL日志LIS.Core.Util.LogUtils.WriteSqlLog("执行QueryAllWithFK返回String查询SQL:" + sql);//如果返回总行数,返回总行数写法if (returnCount) {jsonsb.append("{");jsonsb.append("\"rows\":[");}//否则采用普通数组写法else {jsonsb.append("[");}StringBuilder rowAllsb = new StringBuilder();try {pstat = Manager().Connection().prepareStatement(sql);String paraSql = DBParaUtil.SetDBPara(pstat, param);rst = pstat.executeQuery();LIS.Core.Util.LogUtils.WriteSqlLog("参数:" + paraSql);//标识是否第一行boolean isFirstRow = true;while (rst.next()) {rowCount++; //总行数加一//查询全部,或者取分页范围内的记录if (findAll || (rowCount > fromRow && rowCount <= toRow)) {ResultSetMetaData metaData = rst.getMetaData();//获取列数int colCount = metaData.getColumnCount();//单行数据容器StringBuilder rowsb = new StringBuilder();rowsb.append("{");//标识是否第一列boolean isFirstCol = true;for (int coli = 1; coli <= colCount; coli++) {//获取列名String colName = metaData.getColumnName(coli);//获取列值Object colValue = rst.getObject(coli);if (colValue == null) colValue = "";//如果传了显示的字段,过滤不包含的字段if (fields != null && !fields.isEmpty() && fields.indexOf("," + colName + ",") < 0) {continue;}if (isFirstCol) {rowsb.append("\"" + colName + "\":");rowsb.append("\"" + JsonHelper.DealForJsonString(colValue.toString()).toString() + "\"");isFirstCol = false;} else {rowsb.append(",");rowsb.append("\"" + colName + "\":");rowsb.append("\"" + JsonHelper.DealForJsonString(colValue.toString()).toString() + "\"");}}rowsb.append("}");if (isFirstRow) {rowAllsb.append(rowsb.toString());isFirstRow = false;} else {rowAllsb.append(",");rowAllsb.append(rowsb.toString());}}}} catch (Exception ex) {//查询异常清空数据记录容器rowAllsb.delete(0, rowAllsb.length());}//操作结束释放资源,但是不断连接,不然没法连续做其他数据库操作了finally {if (rst != null) {rst.close();}if (pstat != null) {pstat.close();}//如果上层调用未开启事务,则调用结束释放数据库连接if (!Manager().Hastransaction) {manager.Close();}}//组装数据记录jsonsb.append(rowAllsb.toString());//补充数组结尾符jsonsb.append("]");if (returnCount) {jsonsb.append(",");jsonsb.append("\"total\":");jsonsb.append(rowCount);jsonsb.append("}");}return jsonsb.toString();}
然后反射得到实体jar包的所有实体类供码表管理器展示表
package LIS.DAL.ORM.DBUtility;import LIS.DAL.ORM.Common.ModelInfo;
import LIS.DAL.ORM.Common.ModelToSqlUtil;
import LIS.DAL.ORM.Common.TableInfo;
import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.jar.JarEntry;
import java.util.jar.JarInputStream;/*** 给码表取实体信息工具类*/
public class ModelInfoUtil {/*** 得到所有的实体信息* @return*/public static String GetAllModelJson() throws Exception{List<ModelInfo> retList=new ArrayList<>();//得到实体的所有类List<Class> list=LIS.Core.Util.ReflectUtil.GetAllType("LIS.Model","LIS.Model.Entity");if(list!=null&&list.size()>0){for(int i=0;i<list.size();i++){Class c=list.get(i);Object m=c.getConstructor().newInstance();ModelInfo model=new ModelInfo();TableInfo tableInfo=ModelToSqlUtil.GetTypeInfo(m);model.Name=c.getSimpleName();if(tableInfo.TableInfo!=null){model.Remark=tableInfo.TableInfo.Remark();model.TableName=tableInfo.TableInfo.Name();model.PropNames=new ArrayList<>();for(int j=0;j<tableInfo.ColList.size();j++){model.PropNames.add(tableInfo.ColList.get(j).Name);}}retList.add(model);}}return LIS.Core.Util.JsonUtil.Object2Json(retList);}}
反射得到所有实体
package LIS.Core.Util;import java.io.File;
import java.lang.reflect.Field;
import java.lang.reflect.Type;
import java.net.URL;
import java.net.URLClassLoader;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.List;
import java.util.jar.JarEntry;
import java.util.jar.JarFile;/*** 反射工具类*/
public class ReflectUtil {/*** 设置对象指定属性名字的值* @param obj 对象* @param name 属性名称* @param val 属性值*/public static void SetObjValue(Object obj,String name,Object val){try {Class c = obj.getClass();//得到列信息Field declaredField = c.getDeclaredField(name);//布尔的处理if(declaredField.getType()==Boolean.class) {if(val.toString().equals("1")){val=Boolean.TRUE;}else if(val.toString().equals("0")){val=Boolean.FALSE;}else{val=null;}}//布尔的处理else if(declaredField.getType()==boolean.class) {if(val.toString().equals("1")){val=true;}else if(val.toString().equals("0")){val=false;}else{val=true;}}//int的处理else if(declaredField.getType()==int.class) {if(val==null){val=0;}}//数字的处理else if(declaredField.getType()==Integer.class||declaredField.getType()==Double.class||declaredField.getType()==Float.class) {if(val.toString().isEmpty()){val=null;}}declaredField.set(obj, val);}catch (Exception ex){ex.printStackTrace();}}/*** 用类型全名和程序集全名获得类型* @param typeName 类型全面* @param assemblyName 程序集名* @return 类型*/public static Class GetType(String typeName, String assemblyName){try {//得到根路径Class<?> clazz = ReflectUtil.class;ClassLoader classLoader = clazz.getClassLoader();URL resourceURL1 = classLoader.getResource("");String bashePath = resourceURL1.getFile();//组装成jar包路径String jarPath=bashePath+assemblyName+".jar";File file = new File(jarPath);if (!file.exists()) {throw new Exception("未能找到"+jarPath+"的文件");}//反射得到类型//自己生成jar包路径URL url = file.toURI().toURL();URL[] urls = new URL[]{url};//加载程序集URLClassLoader loader = new URLClassLoader(urls, ReflectUtil.class.getClassLoader());//加载类Class c = loader.loadClass(typeName);if(c!=null){return c;}else{throw new Exception("未能构建类型"+typeName);}}catch (Exception ex){ex.printStackTrace();}return null;}/*** 得到jar包下所有类* @param assemblyName jar包名称* @param packageName 包名* @return*/public static List<Class> GetAllType(String assemblyName,String packageName) throws Exception{List<Class> classes = new ArrayList<>();try {//得到根路径Class<?> clazz = ReflectUtil.class;ClassLoader classLoader = clazz.getClassLoader();URL resourceURL1 = classLoader.getResource("");String bashePath = resourceURL1.getFile();//组装成jar包路径String jarPath=bashePath+assemblyName+".jar";File file = new File(jarPath);if (!file.exists()) {throw new Exception("未能找到"+jarPath+"的文件");}//反射得到类型//自己生成jar包路径URL url = file.toURI().toURL();URL[] urls = new URL[]{url};//加载程序集URLClassLoader loader = new URLClassLoader(urls, ReflectUtil.class.getClassLoader());try (JarFile jarFile = new JarFile(jarPath)) {Enumeration<JarEntry> entries = jarFile.entries();while (entries.hasMoreElements()) {JarEntry entry = entries.nextElement();if (entry.getName().endsWith(".class")) {String className = entry.getName().replace(".class", "").replace("/", ".");Class c = loader.loadClass(className);classes.add(c);}}}}catch (Exception ex){ex.printStackTrace();}return classes;}
}
码表后台实现,按传入的实体名称反射查询带外键的数据供界面展示
解析表的信息供前端渲染界面
package LIS.DAL.ORM.Common;
import LIS.Core.CustomAttributes.FrekeyAttribute;
import LIS.Core.CustomAttributes.IdAttribute;
import LIS.Core.CustomAttributes.NotNullAttribute;
import LIS.Core.CustomAttributes.UniqueAttribute;
import jdk.jshell.execution.Util;import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.List;/*** 通用码表的界面配置实体*/
public class ModelConfig{/*** 实体名称*/public String ModelName;/*** 实体显示名称*/public String ShowModelName;/*** 是否分页*/public Boolean Pagination;/*** 页面显示行数*/public int PageSize;/*** 是否适应屏幕显示*/public Boolean fitColumns;/*** 是否显示行号*/public Boolean rowNumber;/*** 实体属性*/public List<ModelProperty> ModelPropertys;/*** 编辑窗口的宽度*/public int Width;/*** 编辑窗口的高度*/public int Height;/*** 编辑窗口的标签宽度*/public int LabelWidth;/*** 最大行*/public int MaxRowLen = 8;/*** 唯一列要求*/public List<String> UniqueColumns;/*** 默认排序串* @return*/public String SortNames(){String ret = "";for(int i=0;i<ModelPropertys.size();i++){ModelProperty pro=ModelPropertys.get(i);if (pro.PropertyName == "Sequence" && ModelName != "BTHRStaff"){ret = "Sequence";}else if (pro.PropertyName == "SeqNum" && ModelName != "BTHRStaff"){ret = "SeqNum";}}if (ret == ""){ret = "RowID";}return ret;}/*** 停靠*/public enum DisplayPosition{CENTER, LEFT, RIGHT}/*** 输入框类型*/public enum InputType{TEXT, SELECT, FR_SELECT, DETAIL_TABLE}/*** 列的数据类型*/public enum ColumnType{INT, STRING, BOOL, FLOAT, DOUBLE, OTHER};/*** 属性类*/public class ModelProperty{/*** 属性名称*/public String PropertyName;/*** 属性显示名称*/public String ShowPropertyName;/*** 是否显示*/public Boolean IsShow;/*** 显示宽度*/public int ShowWidth;/*** 是否是表的详细列*/public boolean IsDtTable = false;/*** 是否禁止选择*/public boolean IsDisable = false;/*** 禁止选择的制定值*/public String DisableVal;/*** 停靠CENTER, LEFT, RIGHT*/public DisplayPosition DisplayPosition;/*** 显示位置*/public DisplayPosition ShowPosition;/*** 输入框类型TEXT, SELECT, FR_SELECT, DETAIL_TABLE*/public InputType InputType;/*** 编辑类型*/public InputType EditType;/*** 编辑样式*/public String EditStyle;/*** 数据类型*/public ColumnType DataType;/*** 最大长度*/public int MaxLength;/*** 限选数据,用于枚举*/public Hashtable Selects;/*** 限选数据串*/public String SelectsStr;/*** 序号*/public int Sequence;/*** 是否为主键*/public boolean IsId;/*** 是否为主键*/public boolean IsFK;/*** 是否是系统强制要求必填*/public boolean IsMustRequire;/*** 是否必填*/public boolean IsRequire;/*** 是否关联外键*/public ModelPropertyFK PropertyFK;/*** 外键内部类*/public class ModelPropertyFK{/*** 外键实体名称*/public String FkModelName;/*** 外键关联字段*/public String FefColumnName;/*** 外键拉取知道*/public String AssociaField;/*** 外键选择数据,8.4废弃*/public Hashtable FkSelects; //外键相关的所有数据/*** 构造*/public ModelPropertyFK(){}/*** 构造* @param FkModelName 参照的表实体* @param FefColumnName 参照列* @param AssociaField 拉取列*/public ModelPropertyFK(String FkModelName, String FefColumnName, String AssociaField){this.FkModelName = FkModelName;this.FefColumnName = FefColumnName;this.AssociaField = AssociaField;}}/*** 构造*/public ModelProperty(){}/*** 构造* @param pi*/public ModelProperty(Field pi){this.PropertyName = pi.getName();this.ShowPropertyName = pi.getName();this.IsShow = true;this.ShowWidth = 100;this.ShowPosition = DisplayPosition.CENTER;this.EditType = InputType.TEXT;this.EditStyle = "";this.MaxLength = 80;if (pi.getType() == int.class || pi.getType() == Integer.class){this.DataType = ColumnType.INT;}else if (pi.getType() == float.class || pi.getType() == Float.class){this.DataType = ColumnType.FLOAT;}else if (pi.getType() == double.class || pi.getType() == Double.class){this.DataType = ColumnType.DOUBLE;}else if (pi.getType() == boolean.class || pi.getType() == Boolean.class){this.DataType = ColumnType.BOOL;}else if (pi.getType() == String.class){this.DataType = ColumnType.STRING;}else{this.DataType = ColumnType.OTHER;}if (pi.getName().endsWith("Date") || pi.getName().endsWith("Time")){//给定默认的宽度this.EditStyle = "width:146px;";}this.Selects = new Hashtable();this.SelectsStr = LIS.Core.Util.JsonUtil.Object2Json(this.Selects);if (pi.getType() == boolean.class || pi.getType() == Boolean.class){this.EditType = InputType.SELECT;this.EditStyle = "width:146px;";this.DataType = ColumnType.BOOL;this.Selects.put("1", "true");this.Selects.put("0", "false");this.SelectsStr = "{\"1\":\"true\",\"0\":\"false\"}";}//返回所有自定义特性Annotation[] propertyAttrs = pi.getAnnotations();//遍历所有自定义特性for (int i = 0; i < propertyAttrs.length; i++){//获取当前的自定义特性Annotation propertyAttr = propertyAttrs[i];//如果是主键特性if (propertyAttr instanceof IdAttribute){this.IsId = true;}//如果是外键特性else if (propertyAttr instanceof FrekeyAttribute){this.IsFK = true;FrekeyAttribute fkAttr = (FrekeyAttribute)propertyAttr;this.PropertyFK = new ModelPropertyFK(fkAttr.Name(), fkAttr.RefColumnName(), fkAttr.AssociaField());//给定默认的宽度this.EditStyle = "width:146px;";}//是否必填else if (propertyAttr instanceof NotNullAttribute){this.IsRequire = true;this.IsMustRequire = true;}}}//将字符串形式的选择数据转换为Map(hashtable)public void SetSelects(){this.Selects = (Hashtable) LIS.Core.Util.JsonUtil.Json2Object(this.SelectsStr,Hashtable.class);}}/*** 空构造函数*/public ModelConfig(){}/*** 根据类型初始化配置文件* @param type*/public ModelConfig(Class type){this.ModelName = type.getSimpleName();this.ShowModelName = type.getSimpleName();this.Pagination = true;this.PageSize = 20;this.rowNumber = false;this.Width = 600;this.Height = 400;this.LabelWidth = 240;this.UniqueColumns = new ArrayList<>();this.ModelPropertys = new ArrayList<>();Field[] propertyInfos = type.getFields();if (propertyInfos.length > 10){this.fitColumns = false;}else{this.fitColumns = true;}if (propertyInfos.length == 0){return;}ModelProperty mp = null;for(int i=0;i<propertyInfos.length;i++){Field pi=propertyInfos[i];mp = new ModelProperty(pi);this.ModelPropertys.add(mp);}//读取唯一组合键Annotation[] attrs = type.getAnnotations();for(int i=0;i<attrs.length;i++){Annotation attr=attrs[i];if(attr instanceof UniqueAttribute){//as转换类型UniqueAttribute attr_ = (UniqueAttribute)attr;if (attr_.ColNames().toLowerCase().equals("RowID".toLowerCase())){continue;}//得到w唯一组合键this.UniqueColumns.add(attr_.ColNames().replace(',', '+'));}}}}
基于此,只要实力足够强劲,即可实现通用码表,我带来的独创设计。框架实现到检验目前同水准或者超越的时候,借助脚本化、码表、代码生成、打印导出、模板设计器、虚拟M脚本这些实现、足以搅局整个需求型软件行业,我熟悉的是医疗;效率和发布型的架构相比就是高很多。
框架计划