好吧，廢話不多說，鄙人造輪子了。 如題所示,生成自定義模板的資料庫三件套 javabean/javaclient/sqlMap 使用到Lombok、freemarker 上述是讀取資料庫的表信息跟欄位信息,接下來是表名跟欄位名的命名轉換規則介面 再然後就是資料庫拿表跟欄位信息了 代碼中有註釋信息,相 ...
好吧，廢話不多說，鄙人造輪子了。
如題所示,生成自定義模板的資料庫三件套 javabean/javaclient/sqlMap
使用到Lombok、freemarker
1 package com.generator.db.domain; 2 3 import java.util.List; 4 import java.util.Set; 5 6 import lombok.Data; 7 8 @Data 9 public class Table { 10 /** 包名 */ 11 private String packageName; 12 13 /** 表說明 */ 14 private String common; 15 /** 表名稱 */ 16 private String tableName; 17 /** 類名 */ 18 private String className; 19 /** 表欄位(普通欄位) */ 20 private List<Column> columns; 21 /** 表欄位(主鍵欄位) */ 22 private List<Column> primaryKeyColumns; 23 24 /** 主鍵類型 */ 25 private String primaryKeysType; 26 /** 主鍵類包名 */ 27 private String primaryKeysPackageName; 28 /** 需要導入的包 */ 29 private Set<String> importPackages; 30 }
package com.generator.db.domain; import lombok.Data; @Data public class Column { /** 所在表 */ private String tableName; /** 欄位說明 */ private String common; /** 列名 */ private String columnName; /** java屬性名 */ private String propertyName; /** 資料庫類型 */ private String jdbcType; /** java類型 全限定名 */ private String javaType; /** java類型 類名 */ private String simpleJavaType; /** java類型是否需要導包 即 是否不在java.lang包下 */ private String needImportPackage; /** 數據長度 */ private int length; /** 是否可以為空 */ private String isNull; /** 是否是主鍵 */ private String isPrimaryKey; }
上述是讀取資料庫的表信息跟欄位信息,接下來是表名跟欄位名的命名轉換規則介面
1 package com.generator.convert; 2 3 @FunctionalInterface 4 public interface DBNameConvert { 5 String convert(String name); 6 }
再然後就是資料庫拿表跟欄位信息了
1 package com.generator.db; 2 3 import java.sql.Connection; 4 import java.sql.DatabaseMetaData; 5 import java.sql.DriverManager; 6 import java.sql.ResultSet; 7 import java.sql.SQLException; 8 import java.util.ArrayList; 9 import java.util.HashMap; 10 import java.util.List; 11 import java.util.Map; 12 import java.util.stream.Collectors; 13 14 import com.generator.convert.DBNameConvert; 15 import com.generator.db.domain.Column; 16 import com.generator.db.domain.Table; 17 18 public final class DBUtils { 19 public static String URL; 20 public static String USERNAME; 21 public static String PASSWORD; 22 private static final String BOOLEAN_STRING_TRUE = "true"; 23 private static final String BOOLEAN_STRING_FALSE = "false"; 24 25 /** 表名 */ 26 public static final String TABLE_NAME = "TABLE_NAME"; 27 /** 列名 */ 28 public static final String COLUMN_NAME = "COLUMN_NAME"; 29 /** 備註 */ 30 public static final String REMARKS = "REMARKS"; 31 /** 數據類型 */ 32 public static final String DATA_TYPE = "DATA_TYPE"; 33 /** 列長度 */ 34 public static final String COLUMN_SIZE = "COLUMN_SIZE"; 35 /** 是否可以為NULL */ 36 public static final String NULLABLE = "NULLABLE"; 37 38 public static void init(String url, String username, String password) { 39 URL = url; 40 USERNAME = username; 41 PASSWORD = password; 42 } 43 44 public static Connection getConn() { 45 Connection connection = null; 46 try { 47 connection = DriverManager.getConnection(URL, USERNAME, PASSWORD); 48 } catch (Exception e) { 49 e.printStackTrace(); 50 } 51 return connection; 52 } 53 54 public static List<Table> getTablesInfo(String packageName, DBNameConvert tableConvert, 55 DBNameConvert columnConvert) { 56 List<Table> tables = getTables(packageName, tableConvert); 57 List<Column> columns = getColumns(columnConvert); 58 59 Map<String, List<Column>> columnsMap = columns.stream().collect(Collectors.groupingBy(Column::getTableName)); 60 List<Table> primaryKeysClasses = new ArrayList<>(); 61 tables.forEach(t -> { 62 List<Column> allColumns = columnsMap.get(t.getTableName()); 63 List<Column> tablePrimaryKeys = t.getPrimaryKeyColumns(); 64 Map<String, Column> tablePrimaryKeysMap = tablePrimaryKeys.stream() 65 .collect(Collectors.toMap(Column::getColumnName, table -> table)); 66 67 /** 對集合中的列設置是否主鍵標識 */ 68 allColumns.forEach(c -> { 69 if (tablePrimaryKeysMap.get(c.getColumnName()) != null) { 70 c.setIsPrimaryKey(BOOLEAN_STRING_TRUE); 71 } else { 72 c.setIsPrimaryKey(BOOLEAN_STRING_FALSE); 73 } 74 }); 75 76 List<Column> pkColumns = allColumns.stream().filter(c -> BOOLEAN_STRING_TRUE.equals(c.getIsPrimaryKey())) 77 .collect(Collectors.toList()); 78 /** 將多個主鍵移除普通列 */ 79 Map<String, Column> removePKColumns = pkColumns.stream() 80 .collect(Collectors.toMap(Column::getColumnName, table -> table)); 81 allColumns = allColumns.stream().filter(c -> removePKColumns.get(c.getColumnName()) == null) 82 .collect(Collectors.toList()); 83 84 if (pkColumns.size() > 1) { 85 Table primaryKeysClass = new Table(); 86 primaryKeysClass.setColumns(pkColumns); 87 primaryKeysClass.setPackageName(t.getPackageName() + ".base"); 88 primaryKeysClass.setClassName(t.getClassName() + "Key"); 89 primaryKeysClasses.add(primaryKeysClass); 90 91 t.setPrimaryKeysPackageName(primaryKeysClass.getPackageName()); 92 t.setPrimaryKeysType(primaryKeysClass.getClassName()); 93 } else { 94 t.setPrimaryKeysType(pkColumns.get(0).getJavaType()); 95 } 96 t.setPrimaryKeyColumns(pkColumns); 97 t.setColumns(allColumns); 98 99 t.setImportPackages( 100 t.getColumns().stream().filter(c -> c.getNeedImportPackage().equals(BOOLEAN_STRING_TRUE)) 101 .map(Column::getJavaType).collect(Collectors.toSet())); 102 }); 103 104 tables.addAll(primaryKeysClasses); 105 106 return tables; 107 } 108 109 private static List<Table> getTables(String packageName, DBNameConvert tableConvert) { 110 List<Table> tables = new ArrayList<>(); 111 Connection conn = getConn(); 112 try { 113 DatabaseMetaData metaData = conn.getMetaData(); 114 ResultSet tablesResultSet = metaData.getTables(conn.getCatalog(), "%", "%", new String[] { "TABLE" }); 115 Table table = null; 116 Column primaryKeysColumn = null; 117 List<Column> primaryKeysColumns = null; 118 while (tablesResultSet.next()) { 119 table = new Table(); 120 primaryKeysColumns = new ArrayList<>(); 121 table.setPackageName(packageName); 122 table.setTableName(tablesResultSet.getString(TABLE_NAME)); 123 table.setCommon(tablesResultSet.getString(REMARKS)); 124 table.setClassName(tableConvert.convert(table.getTableName())); 125 ResultSet primaryKeysResultSet = metaData.getPrimaryKeys(conn.getCatalog(), "%", table.getTableName()); 126 while (primaryKeysResultSet.next()) { 127 primaryKeysColumn = new Column(); 128 primaryKeysColumn.setColumnName(primaryKeysResultSet.getString(COLUMN_NAME)); 129 primaryKeysColumns.add(primaryKeysColumn); 130 } 131 132 table.setPrimaryKeyColumns(primaryKeysColumns); 133 tables.add(table); 134 } 135 } catch (SQLException e) { 136 e.printStackTrace(); 137 } 138 return tables; 139 } 140 141 private static List<Column> getColumns(DBNameConvert columnConvert) { 142 List<Column> columns = new ArrayList<>(); 143 Connection conn = getConn(); 144 try { 145 DatabaseMetaData metaData = conn.getMetaData(); 146 ResultSet columnsResultSet = metaData.getColumns(conn.getCatalog(), "%", "%", "%"); 147 Column column = null; 148 Map<Integer, String> intTypeMapper = intTypeMapper(); 149 Map<String, String> jdbcJavaTypeMapper = jdbcJavaTypeMapper(); 150 while (columnsResultSet.next()) { 151 column = new Column(); 152 column.setTableName(columnsResultSet.getString(TABLE_NAME)); 153 column.setColumnName(columnsResultSet.getString(COLUMN_NAME)); 154 column.setCommon(columnsResultSet.getString(REMARKS)); 155 /** jdbcType從數字映射到字元串類型 */ 156 column.setJdbcType(intTypeMapper.get(columnsResultSet.getInt(DATA_TYPE))); 157 /** javaType從資料庫類型映射到java類型 */ 158 column.setJavaType(jdbcJavaTypeMapper.get(column.getJdbcType())); 159 column.setNeedImportPackage(BOOLEAN_STRING_TRUE); 160 if(column.getJavaType().indexOf(".")<0||column.getJavaType().startsWith("java.lang.")) { 161 column.setNeedImportPackage(BOOLEAN_STRING_FALSE); 162 } 163 column.setSimpleJavaType(column.getJavaType()); 164 column.setLength(columnsResultSet.getInt(COLUMN_SIZE)); 165 column.setIsNull(String.valueOf(columnsResultSet.getInt(NULLABLE) > 0)); 166 167 column.setSimpleJavaType(column.getJavaType().substring(column.getJavaType().lastIndexOf(".") + 1)); 168 /** 屬性名轉換 */ 169 column.setPropertyName(columnConvert.convert(column.getColumnName())); 170 columns.add(column); 171 } 172 } catch (SQLException e) { 173 e.printStackTrace(); 174 } 175 return columns; 176 177 } 178 179 public static Map<Integer, String> intTypeMapper() { 180 Map<Integer, String> map = new HashMap<>(); 181 map.put(-7, "BIT"); 182 map.put(-6, "TINYINT"); 183 map.put(5, "SMALLINT"); 184 map.put(4, "INTEGER"); 185 map.put(-5, "BIGINT"); 186 map.put(6, "FLOAT"); 187 map.put(7, "REAL"); 188 map.put(8, "DOUBLE"); 189 map.put(2, "NUMERIC"); 190 map.put(3, "DECIMAL"); 191 map.put(1, "CHAR"); 192 map.put(12, "VARCHAR"); 193 map.put(-1, "LONGVARCHAR"); 194 map.put(91, "DATE"); 195 map.put(92, "TIME"); 196 map.put(93, "TIMESTAMP"); 197 map.put(-2, "BINARY"); 198 map.put(-3, "VARBINARY"); 199 map.put(-4, "LONGVARBINARY"); 200 map.put(0, "NULL"); 201 map.put(1111, "OTHER"); 202 map.put(2000, "JAVA_OBJECT"); 203 map.put(2001, "DISTINCT"); 204 map.put(2002, "STRUCT"); 205 map.put(2003, "ARRAY"); 206 map.put(2004, "BLOB"); 207 map.put(2005, "CLOB"); 208 map.put(2006, "REF"); 209 map.put(70, "DATALINK"); 210 map.put(16, "BOOLEAN"); 211 map.put(-8, "ROWID"); 212 map.put(-15, "NCHAR"); 213 map.put(-9, "NVARCHAR"); 214 map.put(-16, "LONGNVARCHAR"); 215 map.put(2011, "NCLOB"); 216 map.put(2009, "SQLXML"); 217 map.put(2012, "REF_CURSOR"); 218 map.put(2013, "TIME_WITH_TIMEZONE"); 219 map.put(2014, "TIMESTAMP_WITH_TIMEZONE"); 220 return map; 221 } 222 223 public static Map<String, String> jdbcJavaTypeMapper() { 224 Map<String, String> map = new HashMap<>(); 225 map.put("CHAR", "java.lang.String"); 226 map.put("VARCHAR", "java.lang.String"); 227 map.put("LONGVARCHAR", "java.lang.String"); 228 map.put("NUMERIC", "java.math.BigDecimal"); 229 map.put("DECIMAL", "java.math.BigDecimal"); 230 map.put("BIT", "boolean"); 231 map.put("BOOLEAN", "boolean"); 232 map.put("TINYINT", "byte"); 233 map.put("SMALLINT", "short"); 234 map.put("INTEGER", "int"); 235 map.put("BIGINT", "long"); 236 map.put("REAL", "float"); 237 map.put("FLOAT", "double"); 238 map.put("DOUBLE", "double"); 239 map.put("BINARY", "byte[]"); 240 map.put("VARBINARY", "byte[]"); 241 map.put("LONGVARBINARY", "byte[]"); 242 map.put("DATE", "java.time.LocalDate"); 243 map.put("TIME", "java.time.LocalTime"); 244 map.put("TIMESTAMP", "java.time.LocalDateTime"); 245 map.put("CLOB", "com.mysql.jdbc.Clob"); 246 map.put("BLOB", "com.mysql.jdbc.Blob"); 247 map.put("ARRAY", "Array"); 248 map.put("STRUCT", "Struct"); 249 map.put("REF", "Ref"); 250 map.put("DATALINK", "java.net.URL"); 251 return map; 252 } 253 }
代碼中有註釋信息,相信應該不難理解,接下來就是生成代碼的動作了
1 package com.generator.convert; 2 3 import java.io.File; 4 import java.io.FileOutputStream; 5 import java.io.IOException; 6 import java.io.OutputStreamWriter; 7 import java.io.Writer; 8 import java.util.List; 9 10 import com.generator.db.DBUtils; 11 import com.generator.db.domain.Table; 12 13 import freemarker.template.Configuration; 14 import freemarker.template.Template; 15 import freemarker.template.TemplateException; 16 import freemarker.template.TemplateExceptionHandler; 17 import lombok.AllArgsConstructor; 18 import lombok.Data; 19 import lombok.NoArgsConstructor; 20 21 @Data 22 @AllArgsConstructor 23 @NoArgsConstructor 24 public class Generator { 25 /** 資料庫配置 */ 26 private DBSetting dbSetting; 27 /** 模板配置 */ 28 private TemplateSetting templateSetting; 29 /** javaModel配置 */ 30 private JavaModelSetting javaModelSetting; 31 /** sqlMap配置 */ 32 private SqlMapSetting sqlMapSetting; 33 /** 生成文件目錄配置 */ 34 private FileSetting fileSetting; 35 36 /** javabean包名稱 */ 37 private String javaBeanPackageName; 38 /** javaClient包名稱 */ 39 private String javaClientPackageName; 40 41 public void generator() throws IOException, TemplateException { 42 Configuration conf = getConf(); 43 DBUtils.init(dbSetting.getDbUrl(), dbSetting.getDbUserName(), dbSetting.getDbPassWord()); 44 List<Table> tables = DBUtils.getTablesInfo(javaBeanPackageName, javaModelSetting.tableConvert, 45 javaModelSetting.columnConvert); 46 47 /** 獲取模板 */ 48 Template javaModelTemplate = conf.getTemplate(templateSetting.getJavaModelTemplate()); 49 Template sqlMapTemplate = conf.getTemplate(templateSetting.getSqlMapTemplate()); 50 Template javaClientTemplate = conf.getTemplate(templateSetting.getJavaClientTemplate()); 51 Template sqlMapExtendTemplate = conf.getTemplate(templateSetting.getSqlMapExtendTemplate()); 52 53 File javaModelPath = initDir(fileSetting.getJavaModel()); 54 File javaClientPath = initDir(fileSetting.getJavaClient()); 55 File sqlMapPath = initDir(fileSetting.getSqlMap()); 56 tables.forEach(t -> { 57 58 try (Writer javaModelWriter = new OutputStreamWriter( 59 new FileOutputStream(new File(javaModelPath, t.getClassName() + ".java")))) { 60 javaModelTemplate.process(t, javaModelWriter); 61 } catch (Exception e) { 62 e.printStackTrace(); 63 } 64 65 /** 即該table實際上是一個表的符合主鍵類,無需生成javabean以外的client及sqlMap */ 66 if (t.getTableName() != null) { 67 try (Writer javaClientWriter = new OutputStreamWriter( 68 new FileOutputStream(new File(javaClientPath, t.getClassName() + "Mapper.java"))); 69 Writer sqlMapWriter = new OutputStreamWriter( 70 new FileOutputStream(new File(sqlMapPath, t.getClassName() + "Mapper.xml"))); 71 Writer sqlMapExtendWriter = new OutputStreamWriter( 72 new FileOutputStream(new File(sqlMapPath, t.getClassName() + "ExtendMapper.xml")))) { 73 javaClientTemplate.process(t, javaClientWriter); 74 75 /** 因為生成sqlMap要用到不止table對象的欄位,所以添加sqlMapSetting來存放table屬性,傳入模板的對象是Generator自己,省的再要定義bean而且又嵌套屬性 */ 76 sqlMapSetting.setTable(t); 77 sqlMapTemplate.process(this, sqlMapWriter); 78 sqlMapExtendTemplate.process(this, sqlMapExtendWriter); 79 } catch (Exception e) { 80 e.printStackTrace(); 81 } 82 } 83 }); 84 } 85 86 private static File initDir(String path) { 87 File file = new File(path); 88 if (!file.exists()) { 89 file.mkdirs(); 90 } 91 return file; 92 } 93 94 private Configuration getConf() throws IOException { 95 Configuration cfg = new Configuration(Configuration.VERSION_2_3_22); 96 cfg.setDirectoryForTemplateLoading(new File(templateSetting.getTemplateDir())); 97 cfg.setDefaultEncoding("UTF-8"); 98 cfg.setTemplateExceptionHandler(TemplateExceptionHandler.RETHROW_HANDLER); 99 return cfg; 100 } 101 102 @Data 103 @AllArgsConstructor 104 @NoArgsConstructor 105 public static class FileSetting { 106 /** 生成文件根路徑(不要也罷,這個無所謂,只要下麵3個值OK就好) */ 107 private String baseDir; 108 /** javaBean生成目錄 */ 109 private String javaModel; 110 /** javaClient生成目錄 */ 111 private String javaClient; 112 /** sqlMap生成目錄 */ 113 private String sqlMap; 114 } 115 116 @Data 117 @AllArgsConstructor 118 @NoArgsConstructor 119 public static class DBSetting { 120 /** 資料庫鏈接 */ 121 private String dbUrl; 122 /** 資料庫用戶名 */ 123 private String dbUserName; 124 /** 資料庫密碼 */ 125 private String dbPassWord; 126 } 127 128 @Data 129 @AllArgsConstructor 130 @NoArgsConstructor 131 public static class TemplateSetting { 132 /** 模板目錄 */ 133 private String templateDir; 134 /** javabean模板文件 */ 135 private String javaModelTemplate; 136 /** javaClient模板文件 */ 137 private String javaClientTemplate; 138 /** sqlMap模板文件 */ 139 private String sqlMapTemplate; 140 /** sqlMap拓展模板文件 */ 141 private String sqlMapExtendTemplate; 142 } 143 144 @Data 145 @AllArgsConstructor 146 @NoArgsConstructor 147 public static class JavaModelSetting { 148 /** 表名轉類名實現 */ 149 private DBNameConvert tableConvert; 150 /** 列名轉屬性名實現 */ 151 private DBNameConvert columnConvert; 152 } 153 154 @Data 155 @AllArgsConstructor 156 @NoArgsConstructor 157 public static class SqlMapSetting { 158 /** 表信息 */ 159 private Table table; 160 } 161 }
之所以把相關配置項都重新定義了一個內部類,是方便區分哪個屬性是跟那塊內容相關的,接下來是我個人的4個自定義模板
每個人可以根據Table跟Column的屬性說明去定義自己的模板
1 package ${packageName}; 2 3 <#if importPackages?exists> 4 <#list importPackages as package> 5 import ${package}; 6 </#list> 7 </#if> 8 <#if primaryKeysPackageName?exists > 9 import ${primaryKeysPackageName}.${primaryKeysType}; 10 </#if>
