根据java已经定义好的Class生成DDL SQL
1 package com.yjcmp.exchange.generator; 2 3 import cn.hutool.core.collection.CollectionUtil; 4 import cn.hutool.core.lang.ClassScanner; 5 import com.baomidou.mybatisplus.annotation.IdType; 6 import com.baomidou.mybatisplus.annotation.TableField; 7 import com.baomidou.mybatisplus.annotation.TableId; 8 import com.baomidou.mybatisplus.annotation.TableName; 9 import com.baomidou.mybatisplus.core.toolkit.StringUtils; 10 import io.swagger.annotations.ApiModel; 11 import io.swagger.annotations.ApiModelProperty; 12 import lombok.extern.slf4j.Slf4j; 13 import java.io.File; 14 import java.io.FileOutputStream; 15 import java.io.IOException; 16 import java.lang.reflect.Field; 17 import java.net.JarURLConnection; 18 import java.net.URL; 19 import java.util.*; 20 import java.util.jar.JarEntry; 21 import java.util.jar.JarFile; 22 23 /** * @Title SqlGenerator * @Description 根据JAVA实体生成SQL建表语句工具 * @Copyright: 版权所有 (c) 2018 - 2019 * @Company: wt * @Author root * @Version 1.0.0 * @Create 19-4-1 下午4:22 */ 24 @Slf4j 25 public class JavaObject2SqlDDLGenerator { 26 27 /** 28 * 常见MYSQL字段类型 VS java对象属性类型 映射集合 29 */ 30 public static Map<String, String> javaFiled2TableColumnMappingMap = new HashMap<>(); 31 static { 32 javaFiled2TableColumnMappingMap.put("integer", "int(10)"); 33 javaFiled2TableColumnMappingMap.put("short", "tinyint"); 34 javaFiled2TableColumnMappingMap.put("long", "bigint"); 35 javaFiled2TableColumnMappingMap.put("bigdecimal", "decimal(19,2)"); 36 javaFiled2TableColumnMappingMap.put("double", "double(10,2)"); 37 javaFiled2TableColumnMappingMap.put("float", "float"); 38 javaFiled2TableColumnMappingMap.put("boolean", "bit"); 39 javaFiled2TableColumnMappingMap.put("timestamp", "datetime"); 40 javaFiled2TableColumnMappingMap.put("date", "datetime"); 41 javaFiled2TableColumnMappingMap.put("string", "varchar(200)"); 42 javaFiled2TableColumnMappingMap.put("localdatetime", "datetime"); 43 } 44 45 /** 46 * 批量生成 47 * @param classNamePath 48 * @param filePath 49 * @throws IOException 50 */ 51 public static void batchDdlSqlGenerate2File(String classNamePath, String filePath) throws IOException { 52 List<String> classFromPackage = getClassFromPackage(classNamePath); 53 System.out.println(classFromPackage); 54 StringBuilder sub = new StringBuilder(); 55 if(CollectionUtil.isNotEmpty(classFromPackage)){ 56 for (String s : classFromPackage) { 57 String s1 = ddlSqlGenerate(s, null, null, null); 58 sub.append(s1).append("\n"); 59 } 60 } 61 if(StringUtils.isNotBlank(filePath)){ 62 sqlSave2File(sub.toString(),filePath); 63 } 64 65 } 66 67 /** 68 * 获取指定包下面的java 69 * @param packageName 70 * @return 71 */ 72 public static List<String> getClassFromPackage(String packageName) { 73 List<String> classNames = new ArrayList<>(); 74 ClassLoader classLoader = Thread.currentThread().getContextClassLoader(); 75 String path = packageName.replace(".", "/"); 76 try { 77 Enumeration<URL> resources = classLoader.getResources(path); 78 while (resources.hasMoreElements()) { 79 URL resource = resources.nextElement(); 80 if (resource.getProtocol().equals("file")) { 81 File file = new File(resource.getFile()); 82 File[] files = file.listFiles(); 83 if (files != null) { 84 for (File f : files) { 85 String className = f.getName().replace(".class", ""); 86 classNames.add(packageName + "." + className); 87 } 88 } 89 } else if (resource.getProtocol().equals("jar")) { 90 JarURLConnection jarURLConnection = (JarURLConnection) resource.openConnection(); 91 JarFile jarFile = jarURLConnection.getJarFile(); 92 Enumeration<JarEntry> entries = jarFile.entries(); 93 while (entries.hasMoreElements()) { 94 JarEntry jarEntry = entries.nextElement(); 95 String className = jarEntry.getName().replace("/", "."); 96 if (className.startsWith(packageName) && className.endsWith(".class")) { 97 classNames.add(className.substring(0, className.length() - 6)); 98 } 99 } 100 } 101 } 102 } catch (IOException e) { 103 e.printStackTrace(); 104 } 105 return classNames; 106 } 107 108 /** 109 * 生成SQL 110 * @param className 111 * @param tableName 112 * @param primaryKey 113 * @param filePath 114 * @return 115 */ 116 public static String ddlSqlGenerate(String className,String tableName,String primaryKey,String filePath){ 117 try { 118 Class<?> clz = Class.forName(className); 119 String clzName = clz.getName(); 120 Field[] fields = clz.getDeclaredFields(); 121 // 获取该类的父类下的字段 122 Class<?> superclass = clz.getSuperclass(); 123 Field[] declaredFields = superclass.getDeclaredFields(); 124 List<Field> fieldList = new ArrayList<>(); 125 for (Field declaredField : declaredFields) { 126 fieldList.add(declaredField); 127 } 128 for (Field declaredField : fields) { 129 fieldList.add(declaredField); 130 } 131 132 // 查询主键字段 133 String sqlPrimaryKey = null; 134 Field keyField = null; 135 StringBuffer column = new StringBuffer(); 136 for (int i = 0; i < fieldList.size(); i++) { 137 Field f = fieldList.get(i); 138 if (StringUtils.isNotBlank(primaryKey) && f.getName().equals(primaryKey)){ 139 // 根据TableId 注释获取逐渐信息 140 TableId annotation = f.getAnnotation(TableId.class); 141 if(null != annotation){ 142 IdType type = annotation.type(); 143 if(IdType.AUTO == type){ 144 sqlPrimaryKey = " \n `"+primaryKey+"` int(16) not null auto_increment primary key,"; 145 }else{ 146 sqlPrimaryKey = " \n `"+primaryKey+"` varchar(100),"; 147 } 148 }else{ 149 sqlPrimaryKey = " \n `"+primaryKey+"` varchar(100),"; 150 } 151 continue; 152 } 153 154 // 剔除序列化自动生成的字段 155 if(f.getName().equalsIgnoreCase("serialVersionUID")){ 156 continue; 157 } 158 TableId tableId = f.getAnnotation(TableId.class); 159 if(null != tableId){ 160 keyField = f; 161 continue; 162 } 163 // 跳过不是表字段的属性@TableField(exist=false) 164 TableField annotation = f.getAnnotation(TableField.class); 165 if(null != annotation){ 166 boolean exist = annotation.exist(); 167 if(!exist){ 168 continue; 169 } 170 } 171 column.append(wrapperTableColumnSql(f)); 172 } 173 // 如果没参数中指定主键,则根据注释@TableId自动捕获 174 if(StringUtils.isBlank(primaryKey)){ 175 if(null != keyField){ 176 TableId tableId = keyField.getAnnotation(TableId.class); 177 IdType type = tableId.type(); 178 String columnName = StringUtils.camelToUnderline(keyField.getName()).toLowerCase(); 179 if(IdType.AUTO == type){ 180 sqlPrimaryKey = " \n `"+columnName+"` int(16) not null auto_increment primary key,"; 181 }else{ 182 sqlPrimaryKey = " \n `"+columnName+"` varchar(100),"; 183 } 184 } 185 } 186 187 // 从注解@ApiModel获取表名称(description、value) 188 ApiModel annotation = clz.getAnnotation(ApiModel.class); 189 String tableNameCn = null; 190 if(null != annotation){ 191 tableNameCn = annotation.description(); 192 if(StringUtils.isNotBlank(tableNameCn)){ 193 if(!tableNameCn.endsWith("表")){ 194 tableNameCn += "表"; 195 } 196 }else{ 197 tableNameCn = annotation.value(); 198 if(!tableNameCn.endsWith("表")){ 199 tableNameCn += "表"; 200 } 201 } 202 } 203 204 // 从注解@TableName获取表名称 205 if(StringUtils.isBlank(tableName)){ 206 TableName tName = clz.getAnnotation(TableName.class); 207 if(null == tName){ 208 tableName = StringUtils.camelToUnderline(clzName).toLowerCase(); 209 }else{ 210 String value = tName.value(); 211 tableName = value; 212 } 213 } 214 215 StringBuffer sql = new StringBuffer(); 216 String toString = column.toString(); 217 String columnStr = toString.substring(0, column.lastIndexOf(",")); 218 sql.append("\ndrop table if exists `"+tableName+"`; ") 219 .append("\ncreate table `"+tableName+"` (") 220 .append(StringUtils.isNotBlank(sqlPrimaryKey) ? sqlPrimaryKey : "") 221 .append(" \n "+columnStr) 222 .append(" \n ) engine = innodb character set = utf8 collate = utf8_general_ci") 223 .append(StringUtils.isNotBlank(tableName) ? " comment='"+tableNameCn+"'" : "") 224 .append(";"); 225 String sqlText = sql.toString(); 226 if(StringUtils.isNotBlank(filePath)){ 227 sqlSave2File(sqlText,filePath); 228 } 229 return sqlText; 230 } catch (ClassNotFoundException e) { 231 log.debug("SQL生成异常:",e); 232 return null; 233 } 234 } 235 236 /** 237 * 构建字段部分 238 * @param field 239 * @return 240 */ 241 private static String wrapperTableColumnSql(Field field){ 242 String tpl = "`%s` %s default null comment '%s', \n"; 243 String typeName = field.getType().getSimpleName().toLowerCase(); 244 String sqlType = javaFiled2TableColumnMappingMap.get(typeName); 245 if (sqlType == null || sqlType.isEmpty()){ 246 log.info(field.getName() + ":"+field.getType().getName()+" 需要单独创建表"); 247 return ""; 248 } 249 String comment = ""; 250 // 从注解@ApiModelProperty获取字段信息 251 ApiModelProperty annotation = field.getAnnotation(ApiModelProperty.class); 252 if(null != annotation){ 253 comment = annotation.value(); 254 if("".equalsIgnoreCase(comment)){ 255 comment = annotation.name(); 256 } 257 if("".equalsIgnoreCase(comment)){ 258 comment = annotation.notes(); 259 } 260 } 261 // 将java对象属性值的驼峰写法转换为下划线模式 262 String column = StringUtils.camelToUnderline(field.getName()).toLowerCase(); 263 String sql = String.format(tpl,column,sqlType.toLowerCase(),comment); 264 return sql; 265 } 266 267 /** 268 * 生成的SQL保存指定文件 269 * @param str 270 * @param path 271 */ 272 private static void sqlSave2File(String str,String path){ 273 byte[] sourceByte = str.getBytes(); 274 if(null != sourceByte){ 275 try { 276 File file = new File(path); 277 if (!file.exists()) { 278 File dir = new File(file.getParent()); 279 dir.mkdirs(); 280 file.createNewFile(); 281 } 282 FileOutputStream outStream = new FileOutputStream(file); 283 outStream.write(sourceByte); 284 outStream.flush(); 285 outStream.close(); 286 System.out.println("生成成功"); 287 } catch (Exception e) { 288 log.debug("保存SQL文件异常:",e); 289 } 290 } 291 } 292 293 294 295 public static void main(String[] args) throws Exception { 296 String str = ddlSqlGenerate("com.yjcmp.exchange.entity.ExApiBuckets", "ex_api_buckets", null, null); 297 log.error(str); 298 // batchDdlSqlGenerate2File("com.yjcmp.exchange.entity","./ddl.sql"); 299 } 300 }