Java根據(jù)實(shí)體生成SQL數(shù)據(jù)庫表的示例代碼
本文主要給大家分享了兩個(gè)版本的Java代碼,都能實(shí)現(xiàn)一鍵生成實(shí)體類建表SQL,大家可以根據(jù)自己的需求選擇
1.0版本的完整代碼
這是一個(gè)自動(dòng)生成 SQL 語句的類,只需要修改變量 CLS 的參數(shù)為對(duì)應(yīng)類(如User.class),然后執(zhí)行main方法,就可以得到該類的建表SQL語句,以及對(duì)應(yīng)的插入模擬SQL。
package com.zyq.util; import java.lang.reflect.Field; import java.util.Date; import java.util.LinkedHashMap; import java.util.Map; import java.util.Objects; import com.zyq.entity.Command; import io.swagger.annotations.ApiModel; import io.swagger.annotations.ApiModelProperty; /** * 這是一個(gè)自動(dòng)生成 SQL 語句的類,只需要修改變量 CLS 的參數(shù)為對(duì)應(yīng)類(如User.class)<br> * 然后執(zhí)行main方法,就可以得到該類的建表SQL語句,以及對(duì)應(yīng)的插入模擬SQL。 * * @author ZhangYuanqiang * @since 2021年4月13日 */ public class CreateTableSqlUtil { private static final Class<?> CLS = Command.class; private static final String LINE = "\r\n"; private static Map<String, String> map = new LinkedHashMap<String, String>(); public static void main(String[] args) throws Exception { // 獲取建表 SQL createSQL(); // 獲取(參數(shù)條)測(cè)試數(shù)據(jù) SQL createInsertData(2); } /** * 獲取建表SQL(執(zhí)行后控制臺(tái)會(huì)自動(dòng)輸出建表語句SQL) * * @throws Exception */ private static void createSQL() throws Exception { StringBuilder sb = new StringBuilder(); sb.append("CREATE TABLE `").append(getSqlAttrName(CLS.getSimpleName())).append("` (").append(LINE); sb.append(" `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',").append(LINE); sb.append(" `create_time` datetime DEFAULT NULL COMMENT '創(chuàng)建時(shí)間',").append(LINE); sb.append(" `update_time` datetime DEFAULT NULL COMMENT '創(chuàng)建時(shí)間',").append(LINE); sb.append(" `deleted` smallint DEFAULT 0 COMMENT '是否已刪除:0-未刪除,1-已刪除',").append(LINE); sb.append(" `version` smallint DEFAULT 0 COMMENT '版本號(hào)',").append(LINE); // 獲取其他參數(shù) Field[] fields = CLS.getDeclaredFields(); for (Field field : fields) { String type = field.getType().getSimpleName(); String key = getSqlAttrName(field.getName()); String comment = getComment(field, false); switch (type) { case "String": sb.append(" `").append(key).append("` varchar(255) DEFAULT NULL COMMENT ").append(comment).append(",") .append(LINE); break; case "int": case "Integer": comment = getComment(field, true); if (isStatusAndType(field.getName())) { sb.append(" `").append(key).append("` smallint(2) DEFAULT 0 COMMENT ").append(comment).append(",") .append(LINE); } else { sb.append(" `").append(key).append("` int(11) DEFAULT 0 COMMENT ").append(comment).append(",") .append(LINE); } break; case "long": case "Long": comment = getComment(field, true); sb.append(" `").append(key).append("` bigint(20) DEFAULT 0 COMMENT ").append(comment).append(",") .append(LINE); break; case "Date": sb.append(" `").append(key).append("` datetime DEFAULT NULL COMMENT ").append(comment).append(",") .append(LINE); break; default: throw new Exception("未知屬性類型:" + type); } } sb.append(" PRIMARY KEY (`id`)").append(LINE); sb.append(") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT'").append(getApiModelName()) .append("表';").append(LINE); System.out.println("-- 創(chuàng)建表SQL"); System.out.println(sb.toString()); } /** * 創(chuàng)建測(cè)試數(shù)據(jù) SQL(控制臺(tái)會(huì)自動(dòng)打印SQL) * * @param size_需要生成多少條測(cè)試數(shù)據(jù) */ private static void createInsertData(int size) { if (size <= 0) { return; } System.out.println("-- 測(cè)試數(shù)據(jù) " + size + " 條"); long id = 1; String date = "'" + DateUtil.formatDateTime(new Date()) + "'"; for (int i = 0; i < size; i++) { String attrs = ""; String params = ""; for (String attr : map.keySet()) { attrs += ", " + attr; params += ", " + map.get(attr); } String sql = String.format( "INSERT INTO " + getSqlAttrName(CLS.getSimpleName()) + " (id, create_time, update_time %s) VALUES (%s, %s, %s %s);", attrs, id, date, date, params); id++; System.out.println(sql); } } private static String getSqlAttrName(String attr) { char[] array = attr.toCharArray(); StringBuilder sb = new StringBuilder(); int idx = 0; for (char c : array) { if (c >= 'a' && c <= 'z') { sb.append(c); } else { if (idx > 0) { sb.append("_"); } sb.append(String.valueOf(c).toLowerCase()); } idx++; } return sb.toString(); } private static String getApiModelName() { ApiModel model = CLS.getAnnotation(ApiModel.class); if (Objects.isNull(model)) { return ""; } return BaseUtil.getString(model.value()); } private static String getComment(Field field, boolean isNumber) { String sqlFiledName = getSqlAttrName(field.getName()); ApiModelProperty property = field.getAnnotation(ApiModelProperty.class); if (Objects.isNull(property)) { if (isNumber) { map.put(sqlFiledName, "0"); } else { map.put(sqlFiledName, "NULL"); } return "''"; } // 是否有默認(rèn)值 String expmale = BaseUtil.getString(property.example()); if (isNumber) { map.put(sqlFiledName, expmale); } else { map.put(sqlFiledName, "'" + expmale + "'"); } return "'" + BaseUtil.getString(property.value()) + "'"; } private static boolean isStatusAndType(String s) { s = s.toLowerCase(); return s.endsWith("status") || s.endsWith("type"); } }
2.0版實(shí)體生成建表SQL的完整代碼
package com.zyq.ok; import java.io.*; import java.lang.invoke.SerializedLambda; import java.lang.reflect.Field; import java.lang.reflect.Method; import java.lang.reflect.Modifier; import java.math.BigDecimal; import java.time.LocalDate; import java.util.*; import java.util.function.Function; /** * @author Yuanqiang.Zhang * @since 2023/7/5 */ public class ClassUtils { public static void main(String[] args) throws Exception { Table table = getTable(User.class); System.out.println(table.sql()); } /** * Java類轉(zhuǎn)化為數(shù)據(jù)庫表對(duì)象 * * @param c java實(shí)體類 * @param <T> 泛型 * @return 數(shù)據(jù)庫表對(duì)象 */ private static <T> Table getTable(Class<T> c) { List<String> codes = getClassSourceCodes(c); Map<String, Column> columnMap = getFieldColumnMap(c, codes); Class<? super T> superclass = c.getSuperclass(); if (Objects.nonNull(superclass)) { if (superclass != Object.class) { List<String> sourceCodes = getClassSourceCodes(c); Map<String, Column> superColumnMap = getFieldColumnMap(superclass, sourceCodes); columnMap.putAll(superColumnMap); } } if (columnMap.isEmpty()) { return null; } // 獲取主鍵(如果包含名為 id 的屬性,則以 id 為主鍵,否則,則取類中第一個(gè)屬性為主鍵) Column key; if (columnMap.containsKey("id")) { key = columnMap.get("id"); } else { key = columnMap.entrySet().stream().findFirst().get().getValue(); } // 剩下的則是其他屬性 LinkedList<Column> columns = new LinkedList<>(columnMap.values()); if (!columns.get(0).getName().equals(key.getName())) { Column copyKey = key.copy(); columns.remove(key); columns.addFirst(copyKey); } Table table = new Table(); table.setName(getSqlName(c.getSimpleName())); table.setComment(getTableComment(codes)); table.setColumns(columns); return table; } private static <T> Map<String, Column> getFieldColumnMap(Class<T> c, List<String> codes) { Map<String, String> fieldCommentMap = getClassFieldComment(codes, c.getSimpleName()); Field[] fields = c.getDeclaredFields(); Map<String, Column> columnMap = new LinkedHashMap<>(); for (Field field : fields) { if (!Modifier.isStatic(field.getModifiers())) { String fieldName = field.getName(); String columnName = getSqlName(fieldName); Column column = new Column(); column.setName(columnName); column.setType(getSqlType(field)); column.setComment(fieldCommentMap.get(fieldName)); columnMap.put(fieldName, column); } } return columnMap; } /** * 獲取 mysql 數(shù)據(jù)類型 * * @param field java 屬性 * @return mysql的 */ private static String getSqlType(Field field) { Class<?> type = field.getType(); String name = getSqlName(field.getName()); if (type == String.class) { return "varchar(255)"; } else if (type == int.class || type == Integer.class) { if (Constant.TINYINT_WORDS.contains(name)) { return "tinyint(4)"; } if (name.startsWith("is_") || name.endsWith("_type") || name.endsWith("_status")) { return "tinyint(4)"; } return "int(11)"; } else if (type == long.class || type == Long.class) { return "bigint(20)"; } else if (type == double.class || type == Double.class) { // 最大為 (53, 15) return "double(20,2)"; } else if (type == boolean.class || type == Boolean.class) { return "boolean"; } else if (type == Date.class || type == LocalDate.class) { return "datetime"; } else if (type == BigDecimal.class) { // 最大為 decimal(65, 30) return "decimal(20,2)"; } else { // 其他類型則不處理 return null; } } /** * 獲取類文件源碼內(nèi)容 * * @param clazz 類對(duì)象 * @return 類源碼內(nèi)容 */ private static List<String> getClassSourceCodes(Class<?> clazz) { String classPath = clazz.getName().replace(Constant.POINT, Constant.LEFT_LINE) + Constant.POINT_JAVA; String filePath; if (new File(Constant.POM_XML).exists()) { filePath = Constant.SRC_MAIN_JAVA + classPath; } else { filePath = Constant.SRC + classPath; } File file = new File(filePath); List<String> lines = new ArrayList<>(); try (BufferedReader reader = new BufferedReader(new FileReader(file))) { String line; while ((line = reader.readLine()) != null) { lines.add(line); } } catch (IOException e) { e.printStackTrace(); } return lines; } /** * 獲取表備注(默認(rèn)取類文檔注釋第一行文字) * * @param codes 類源碼內(nèi)容 * @return 表備注 */ private static String getTableComment(List<String> codes) { if (codes.isEmpty()) { return null; } // 按規(guī)范來說,第一個(gè)格式為 [ * xxx] 的行,即為表的文檔注釋 String comment = null; for (String line : codes) { String trimLine = line.trim(); if (trimLine.startsWith(Constant.STAR) && line.length() > 1) { comment = trimLine.substring(1).trim(); break; } } if (Objects.isNull(comment)) { return null; } // 按SQL表備注習(xí)慣來說,一般稱為[xxx表],因此如果備注不以"表"字結(jié)尾,我們這里補(bǔ)加上 if (comment.endsWith(Constant.TABLE)) { return comment; } return comment + Constant.TABLE; } /** * 獲取類屬性注釋(文檔注釋、多行注釋、單行注釋、行尾注釋) * * @param codes 類源碼內(nèi)容 * @return 表備注 */ private static Map<String, String> getClassFieldComment(List<String> codes, String className) { boolean fieldLineStart = false; Map<String, String> filedCommentMap = new LinkedHashMap<>(); String comment = null; boolean commentStart = false; for (int i = 0; i < codes.size(); i++) { String code = codes.get(i).trim(); if (!fieldLineStart) { if (code.contains(String.format("class %s", className))) { fieldLineStart = true; } continue; } if (commentStart) { String str = trim(code, Constant.CHAR_STAR).trim(); if (!str.isEmpty()) { comment = str; commentStart = false; continue; } } else { if (code.startsWith("/*")) { commentStart = true; if (code.endsWith("*/")) { comment = getSingleLineDocComment(code); commentStart = false; continue; } } if (code.startsWith("http://")) { comment = code.substring(2).trim(); commentStart = false; continue; } } // 如果進(jìn)入方法區(qū)域,則停止解析 if (code.contains("(") && code.contains(")") && code.endsWith("{")) { return filedCommentMap; } // 屬性判定 if (code.contains(";") && !code.startsWith("http://")) { String fileLine = code; if (code.contains("http://")) { String[] arr = code.split("http://"); if (arr.length > 1 && Objects.isNull(comment)) { comment = arr[1].trim(); } fileLine = arr[0]; } String fieldName = getFieldName(fileLine); filedCommentMap.put(fieldName, comment); comment = null; commentStart = false; } } return filedCommentMap; } private static String getFieldName(String s) { String[] arr = s.split(" "); for (int i = arr.length - 1; i >= 0; i--) { String str = arr[i]; if (!str.isEmpty() && !str.equals(";")) { return str.replace(";", ""); } } return ""; } /** * 獲取單行文檔注釋 * * @param code 單行注釋 * @return 注釋內(nèi)容 */ private static String getSingleLineDocComment(String code) { // code 應(yīng)該長(zhǎng)這樣(/*xxx*/),我們可以先取中間的xxx String xxx = code.substring(2, code.length() - 2); // xxx 首尾可能還包含 *,需要進(jìn)一步將 xxx 首尾多余的 * 去掉,剩下的就是注釋 return trim(xxx, Constant.CHAR_STAR).trim(); } /** * 去掉首尾指定的符號(hào)(該方法參考的是String類中的trim()方法) * * @param s 字符串 * @param c 去掉的指定符號(hào) * @return 不包含首尾指定字符 */ public static String trim(String s, char c) { int var1 = s.length(); int var2 = 0; char[] var3; for (var3 = s.toCharArray(); var2 < var1 && var3[var2] <= c; ++var2) { } while (var2 < var1 && var3[var1 - 1] <= c) { --var1; } return var2 <= 0 && var1 >= s.length() ? s : s.substring(var2, var1); } /** * 根據(jù)駝峰命名獲取下劃線命名 * * @param javaName java的駝峰命名 * @return sql中的下劃線命名 */ private static String getSqlName(String javaName) { StringBuilder sqlName = new StringBuilder(); for (int i = 0; i < javaName.length(); i++) { char c = javaName.charAt(i); if (Character.isUpperCase(c)) { if (i > 0) { sqlName.append(Constant.UNDERLINE); } sqlName.append(Character.toLowerCase(c)); } else { sqlName.append(c); } } return sqlName.toString(); } /** * 常量類 */ static class Constant { private static final String POM_XML = "pom.xml"; private static final String POINT = "."; private static final String LEFT_LINE = "/"; private static final String POINT_JAVA = ".java"; private static final String SRC = "src/"; private static final String SRC_MAIN_JAVA = "src/main/java/"; private static final String STAR = "*"; private static final String TABLE = "表"; private static final String UNDERLINE = "_"; private static final char CHAR_STAR = '*'; private static final List<String> TINYINT_WORDS = Arrays.asList("deleted", "sex", "age", "status", "type", "state"); } /** * SQL表 */ static class Table { private String tablePrefix; private String columnPrefix; private String name; private String comment; private LinkedList<Column> columns; public String getName() { return name; } public void setName(String name) { this.name = name; } public String getComment() { return comment; } public void setComment(String comment) { this.comment = comment; } public List<Column> getColumns() { return columns; } public void setColumns(LinkedList<Column> columns) { this.columns = columns; } public String getTablePrefix() { return tablePrefix; } public void setTablePrefix(String tablePrefix) { this.tablePrefix = tablePrefix; } public String getColumnPrefix() { return columnPrefix; } public void setColumnPrefix(String columnPrefix) { this.columnPrefix = columnPrefix; } public String sql() { if (Objects.isNull(columns)) { columns = new LinkedList<>(); } List<String> lines = new ArrayList<>(); String tableName; if (Objects.nonNull(tablePrefix)) { tableName = tablePrefix + name; } else { tableName = name; } lines.add(String.format("CREATE TABLE `%s` (", tableName)); Column key = columns.get(0); String type = key.getType(); if (type.startsWith("tinyint") || type.startsWith("int") || type.startsWith("bigint")) { key.setIncreased(true); String comment = key.getComment(); if (Objects.isNull(comment) || comment.isEmpty()) { key.setComment("主鍵"); } } for (int i = 0; i < columns.size(); i++) { if (columns.get(i).isEffective()) { lines.add(columns.get(i).sql(columnPrefix)); } } if (key.isEffective()) { lines.add(String.format(" PRIMARY KEY (`%s`)", key.getName())); } StringBuffer sb = new StringBuffer(); sb.append(") ENGINE=InnoDB"); if (key.isEffective() && key.isIncreased()) { sb.append(" AUTO_INCREMENT=1"); } sb.append(" DEFAULT CHARSET=utf8"); if (Objects.isNull(comment)) { sb.append(" COMMENT='';"); } else { sb.append(" COMMENT='").append(comment).append("';"); } lines.add(sb.toString()); return String.join("\n", lines); } public <T, R> Table key(SerializableFunction<T, R> fn) { String newKeyName = getSqlName(getFieldName(fn)); Column find = null; for (Column column : columns) { if (column.getName().equals(newKeyName)) { find = column; } } if (Objects.nonNull(find)) { Column copy = find.copy(); columns.remove(find); columns.addFirst(copy); } return this; } public <T, R> Table column(SerializableFunction<T, R> fn, String type, String comment, String defaultValue) { String fieldName = getFieldName(fn); String columnName = getSqlName(fieldName); for (Column column : columns) { if (column.getName().equals(columnName)) { if (Objects.nonNull(type)) { column.setType(type); } if (Objects.nonNull(comment)) { column.setComment(comment); } if (Objects.nonNull(defaultValue)) { column.setDefaultValue(defaultValue); } break; } } return this; } public <T, R> Table invalid(SerializableFunction<T, R>... fns) { if (Objects.nonNull(fns)) { for (SerializableFunction<T, R> fn : fns) { String fieldName = getFieldName(fn); String columnName = getSqlName(fieldName); for (Column column : columns) { if (column.getName().equals(columnName)) { column.setEffective(false); break; } } } } return this; } private static <T> String getFieldName(SerializableFunction<T, ?> func) { // 通過獲取對(duì)象方法,判斷是否存在該方法 String getter = null; try { Method method = func.getClass().getDeclaredMethod("writeReplace"); method.setAccessible(Boolean.TRUE); // 利用jdk的SerializedLambda 解析方法引用 SerializedLambda serializedLambda = (SerializedLambda) method.invoke(func); getter = serializedLambda.getImplMethodName(); } catch (Exception e) { e.printStackTrace(); } if (Objects.isNull(getter)) { return null; } String fieldName; if (getter.startsWith("get")) { fieldName = getter.substring(3); } else if (getter.startsWith("is")) { fieldName = getter.substring(2); } else { fieldName = getter; } return Character.toLowerCase(fieldName.charAt(0)) + fieldName.substring(1); } } /** * SQL表字段 */ static class Column { private String name; private String type; private String comment; private String defaultValue; private boolean effective = true; private boolean increased; public String getName() { return name; } public void setName(String name) { this.name = name; } public String getType() { return type; } public void setType(String type) { this.type = type; } public String getComment() { return comment; } public void setComment(String comment) { this.comment = comment; } public String getDefaultValue() { return defaultValue; } public void setDefaultValue(String defaultValue) { this.defaultValue = defaultValue; } public boolean isEffective() { return effective; } public void setEffective(boolean effective) { this.effective = effective; } public boolean isIncreased() { return increased; } public void setIncreased(boolean increased) { this.increased = increased; } public Column copy() { Column copy = new Column(); copy.setName(this.name); copy.setType(this.getType()); copy.setComment(this.comment); copy.setDefaultValue(this.defaultValue); copy.setEffective(this.effective); copy.setIncreased(this.increased); return copy; } public String sql(String columnPrefix) { StringBuffer sb = new StringBuffer(); // 字段名稱 sb.append("`"); if (Objects.nonNull(columnPrefix)) { sb.append(columnPrefix); } sb.append(name).append("`"); // 字段類型 sb.append(" ").append(type); // 是否自增 if (increased) { sb.append(" NOT NULL AUTO_INCREMENT"); } else { // 默認(rèn)值 if (Objects.isNull(defaultValue)) { if (type.startsWith("tinyint") || type.startsWith("int") || type.startsWith("bigint")) { sb.append(" DEFAULT 0"); } else if (type.startsWith("varchar")) { sb.append(" DEFAULT ''"); } else { sb.append(" DEFAULT NULL"); } } else { sb.append(" DEFAULT '").append(defaultValue).append("'"); } } // 備注 if (Objects.isNull(comment)) { sb.append(" COMMENT ''"); } else { sb.append(" COMMENT '").append(comment).append("'"); } return " " + sb + ","; } } @FunctionalInterface public interface SerializableFunction<T, R> extends Function<T, R>, Serializable { } }
到此這篇關(guān)于Java根據(jù)實(shí)體生成SQL數(shù)據(jù)庫表的示例代碼的文章就介紹到這了,更多相關(guān)Java實(shí)體生成數(shù)據(jù)庫表內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
SpringBoot與MyBatis-Plus的高效集成方式
本文詳細(xì)介紹了如何在SpringBoot項(xiàng)目中整合MyBatis-Plus,包括環(huán)境準(zhǔn)備、實(shí)體類與Mapper接口定義、CRUD操作、條件構(gòu)造器、事務(wù)管理、安全性考慮、性能優(yōu)化、版本兼容性與遷移、實(shí)際應(yīng)用場(chǎng)景和監(jiān)控與日志等內(nèi)容,通過這些步驟,讀者可以掌握MyBatis-Plus的高級(jí)特性和最佳實(shí)踐2024-11-11Java 判斷一個(gè)時(shí)間是否在另一個(gè)時(shí)間段內(nèi)
這篇文章主要介紹了Java 判斷一個(gè)時(shí)間是否在另一個(gè)時(shí)間段內(nèi)的相關(guān)資料,需要的朋友可以參考下2016-10-10java:程序包c(diǎn)om.xxx.xxx不存在報(bào)錯(cuò)萬能解決辦法
這篇文章主要給大家介紹了關(guān)于java:程序包c(diǎn)om.xxx.xxx不存在報(bào)錯(cuò)萬能解決辦法,這個(gè)問題曾逼瘋初學(xué)者的我,不過弄清楚原理后就很簡(jiǎn)單了,文中通過圖文介紹的非常詳細(xì),需要的朋友可以參考下2023-12-12Mybatis中#{}和${}傳參的區(qū)別及#和$的區(qū)別小結(jié)
這篇文章主要介紹了Mybatis中#{}和${}傳參的區(qū)別及#和$的區(qū)別小結(jié) 的相關(guān)資料,非常不錯(cuò),具有參考借鑒價(jià)值,需要的朋友可以參考下2016-07-07打開.properties中文顯示unicode編碼問題以及解決
這篇文章主要介紹了打開.properties中文顯示unicode編碼問題以及解決方案,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2024-01-01Java實(shí)現(xiàn)高并發(fā)秒殺的幾種方式
高并發(fā)場(chǎng)景在現(xiàn)場(chǎng)的日常工作中很常見,本文主要介紹了Java實(shí)現(xiàn)高并發(fā)秒殺的幾種方式,文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-05-05Maven基礎(chǔ)之如何修改本地倉庫的默認(rèn)路徑
這篇文章主要介紹了Maven基礎(chǔ)之如何修改本地倉庫的默認(rèn)路徑問題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2024-05-05