前言:
此时我们对“oraclec获取表结构”大体比较注意,我们都想要分析一些“oraclec获取表结构”的相关知识。那么小编在网络上搜集了一些关于“oraclec获取表结构””的相关资讯,希望咱们能喜欢,姐妹们快快来了解一下吧!前言
逆向工程从数据库表直接生成代码,是日常开发中常用的敏捷开发手段,常见的例如:mybatis-plus的代码生成器等
为什么要自己写代码生成器呢?MP的生成器不香吗?香!
但是自己写的工具用起来最顺手,可以随意扩展,想怎么玩就怎么玩,只要自己有想法,玩出花来都没问题,当然了,能力有限,现在还只能实现简单版本,更多骚操作自己发挥!
思路:
1、建立jdbc连接,执行查询sql,获取表结构信息。
2、在指定的路径上创建文件。
3、按照我们的布局排版要求,根据表结构信息拼接文件的内容。
4、将字符输出到文件中。
以上即可完成一个文件的自动生成
编码 通用部分
几个内部工具类
file工具类:创建、读取文件
字符串工具类:驼峰标识、下划线互转,首字母大写,数据库字段类型转java类型等
jdbc连接:连接数据库
表注释、表结构信息实体类、执行sql获取表结构信息的方法
表结构信息
private String columnName;//字段名private String dataType;//字段类型private String columnComment;//字段注释private String columnKey;//主键private String extra;//主键类型
mysql查询表注释、表字段信息使用的是
表字段信息
SELECT column_name, data_type, column_comment, column_key, extra FROM information_schema.COLUMNS WHERE table_schema = ( SELECT DATABASE ()) AND table_name =?
表注释
SELECT table_comment FROM information_schema.TABLES WHERE table_schema = ( SELECT DATABASE ()) AND table_name =?
需要支持其他数据库类型的,自己调整就好了,例如oracle获取表注释、表结构sql如下:
-- 表、表注释SELECT t.table_name, t1.comments FROM user_tables t JOIN user_tab_comments t1 ON t.table_name = t1.table_name;-- 表字段、字段注释SELECT t.table_name, c.column_name, c.data_type, cc.comments FROM USER_TAB_COLUMNS c JOIN user_tables t ON c.table_name = t.table_name JOIN user_col_comments cc ON cc.table_name = t.table_name WHERE cc.column_name = c.column_name;
另外,数据连接、基础路径的配置也是一样
/** * 数据连接相关,需要手动设置 */private static final String URL = "jdbc:mysql://localhost:3306/test?serverTimezone=GMT%2B8&characterEncoding=utf-8";private static final String USERNAME = "root";private static final String PASSWORD = "123456";private static final String DRIVER_CLASSNAME = "com.mysql.cj.jdbc.Driver";
/** * 基础路径,需要手动设置 */private String basePackage = "cn\\huanzi\\qch\\baseadmin\\";//根包位置private String filePackage = basePackage + "sys\\";//文件所在包位置
2.0版本多一个模板文件路径
private String tlfPath = System.getProperty("user.dir") + "\\src\\main\\resources\\tlf\\";//模板文件位置
main函数也一样,调用构造参数,传入表名,调用入口函数
public static void main(String[] args) {// String[] tables = {"sys_user","sys_menu","sys_authority","sys_user_menu","sys_user_authority","sys_shortcut_menu","sys_setting"}; String[] tables = {"tb_user"}; for (String table : tables) { String msg = new AutoGenerator(table).create(); System.out.println(msg); }}
V1.0版本
AutoGenerator,1.0版本采用原始的在代码拼接字符串,然后创建文件将字符串输出的方法,比较原始但个人觉得可玩性较高
几个创建方法,就拿实体类来举例
/** * 创建pojo实体类 */private void createPojo(List<TableInfo> tableInfos) { //创建文件 File file = FileUtil.createFile(filePath + "pojo\\" + StringUtil.captureName(StringUtil.camelCaseName(tableName)) + ".java"); //拼接文件内容 StringBuffer stringBuffer = new StringBuffer(); stringBuffer.append( "package " + filePackage.replaceAll("\\\\", ".") + "pojo;\n" + "\n" + "import lombok.Data;\n" + "import javax.persistence.*;\n" + "import java.io.Serializable;\n" + "import java.util.Date;\n" + "\n" + "@Entity\n" + "@Table(name = \"" + tableName + "\")\n" + "@Data\n" + "public class " + StringUtil.captureName(StringUtil.camelCaseName(tableName)) + " implements Serializable {\n" ); //遍历设置属性 for (TableInfo tableInfo : tableInfos) { //主键 if ("PRI".equals(tableInfo.getColumnKey())) { stringBuffer.append(" @Id\n"); } //自增 if ("auto_increment".equals(tableInfo.getExtra())) { stringBuffer.append(" @GeneratedValue(strategy= GenerationType.IDENTITY)\n"); } stringBuffer.append(" private ").append(StringUtil.typeMapping(tableInfo.getDataType())).append(" ").append(StringUtil.camelCaseName(tableInfo.getColumnName())).append(";//").append(tableInfo.getColumnComment()).append("\n\n"); } stringBuffer.append("}"); //写入文件内容 FileUtil.fileWriter(file, stringBuffer);}
其他的也一样,无非就:创建文件、拼接文件内容、输出文件内容
入口函数,供main函数直接调用
/** * 快速创建,供外部调用,调用之前先设置一下项目的基础路径 */private String create() { System.out.println("生成路径位置:" + filePath); //获取表信息 List<TableInfo> tableInfo = getTableInfo(); //开始生成代码 createPojo(tableInfo); createVo(tableInfo); createRepository(tableInfo); createService(tableInfo); createController(tableInfo); return tableName + " 后台代码生成完毕!";}
V2.0版本
AutoGeneratorPlus,2.0版本升级了,设置了模板文件、文件内容的字符串从模板读取,再根据关键字替换参数,最后再输出到创建的文件中,这个版本就比较好理解,大部分的代码生成器也都这样干
需要先定义模板文件(文件名后缀无所谓,自己随便定义),拿entity来举例
package cn.huanzi.qch.baseadmin.sys.${entityToLowerCase}.pojo;import lombok.Data;import javax.persistence.*;import java.io.Serializable;import java.util.Date;/** * ${tableComment} 实体类 * * ${author} * ${date} */@Entity@Table(name = "${tableName}")@Datapublic class ${entity} implements Serializable { #for #ifPri #ifAutoIncrement private ${tableInfo.dataType} ${tableInfo.columnName};//${tableInfo.columnComment} #end}
${},用于取参数,替换成我们的值
#for、#if,循环遍历表字段以及判断是否为主键、是否主键自增
各种关键字随便定义,我们在读取模板文件处理时能对上就行
文件内容处理
/** * 读取模板,设置内容,生成文件 * @param templatePath 模板文件路径 * @param outputFile 文件生成路径 * @param tableInfos 表字段信息 * @param customParameter 自定义参数 */private void writer(String templatePath, String outputFile,List<TableInfo> tableInfos,Map<String,String> customParameter){ //主键 TableInfo prikey = new TableInfo(); //for循环标识 boolean forFlag = false; StringBuilder forContent = new StringBuilder(); //驼峰标识映射后的表名 String replacement = StringUtil.captureName(StringUtil.camelCaseName(tableName)); //遍历属性 for (TableInfo tableInfo : tableInfos) { //主键 if ("PRI".equals(tableInfo.getColumnKey())) { prikey = tableInfo; break; } } try(FileReader fileReader = new FileReader(templatePath); BufferedReader reader = new BufferedReader(fileReader)) { //生成文件 File file = FileUtil.createFile(outputFile); StringBuffer stringBuffer = new StringBuffer(); //读取模板文件,拼接文件内容 Object[] lines = reader.lines().toArray(); for (Object o : lines) { String line = String.valueOf(o); /* 设置值 */ //${tableName} 表名称,例如:tb_user if(line.contains("${tableName}")){ line = line.replaceAll("\\$\\{tableName}", tableName); } //${tableComment} 表注释,例如:tb_user if(line.contains("${tableComment}")){ line = line.replaceAll("\\$\\{tableComment}", tableComment); } //${entity} 实体类名称,例如:TbUser if(line.contains("${entity}")){ line = line.replaceAll("\\$\\{entity}", replacement); } //${entityFirstToLowerCase} 实体类名称首字母小写,例如:tbUser if(line.contains("${entityFirstToLowerCase}")){ line = line.replaceAll("\\$\\{entityFirstToLowerCase}", StringUtil.camelCaseName(tableName)); } //${entityToLowerCase} 实体类名称全小写,例如:tbuser if(line.contains("${entityToLowerCase}")){ line = line.replaceAll("\\$\\{entityToLowerCase}", replacement.toLowerCase()); } //${priDataType} 实体类主键类型,例如:String if(line.contains("${priDataType}")){ line = line.replaceAll("\\$\\{priDataType}", StringUtil.typeMapping(prikey.getDataType())); } //处理自定义参数 line = customParameter(line,customParameter); //先取得循环体的内容 if(forFlag){ forContent.append(line).append("\n"); } //是否为for循环遍历表字段 if(line.contains("#for")){ forFlag = true; } if(line.contains("#end")){ forFlag = false; line = line.replaceAll("#end", ""); } //遍历循环体的内容,并设置值 if(!forFlag && forContent.length() > 0){ //遍历表字段 for (TableInfo tableInfo : tableInfos) { String tableColumns = forContent.toString() //表字段信息:类型、名称、注释 .replaceAll("\\$\\{tableInfo.dataType}", StringUtil.typeMapping(tableInfo.getDataType())) .replaceAll("\\$\\{tableInfo.columnName}", StringUtil.camelCaseName(tableInfo.getColumnName())) .replaceAll("\\$\\{tableInfo.columnComment}", tableInfo.getColumnComment()); //清除多余#end,以及换行符 tableColumns = tableColumns.replaceAll("#end", "").replaceAll("\n", ""); //设置是否主键、是否自增 String pri = "",autoIncrement=""; //主键 if ("PRI".equals(tableInfo.getColumnKey())) { pri = " @Id\n"; //自增id if ("auto_increment".equals(tableInfo.getExtra())){ autoIncrement = "@GeneratedValue(strategy= GenerationType.IDENTITY)\n"; } } tableColumns = tableColumns .replaceAll("#ifPri", pri) .replaceAll("#ifAutoIncrement", autoIncrement); //处理自定义参数 tableColumns = customParameter(tableColumns,customParameter); //前补tab,后补换行符 stringBuffer.append(" ").append(tableColumns.trim()).append("\n\n"); } //置空 forContent.setLength(0); } if(!forFlag){ stringBuffer.append(line).append("\n"); } } //写入数据到到文件中 FileUtil.fileWriter(file, stringBuffer); }catch (Exception e){ e.printStackTrace(); }}
内置了几个重要参数
${tableName} 表名称,例如:tb_user${tableComment} 表注释,例如:tb_user${entity} 实体类名称,例如:TbUser${entityFirstToLowerCase} 实体类名称首字母小写,例如:tbUser${entityToLowerCase} 实体类名称全小写,例如:tbuser${priDataType} 实体类主键类型,例如:String还有三个表字段信息:类型、名称、注释${tableInfo.dataType}${tableInfo.columnName}${tableInfo.columnComment}
支持自定义参数Map<String,String> customParameter,例如模板文件中的注释:
/** * ${author} * ${date} */
入口函数
/** * 快速创建,供外部调用,调用之前先设置一下项目的基础路径 */private String create() { System.out.println("生成路径位置:" + filePath); //获取表信息 List<TableInfo> tableInfo = getTableInfo(); //驼峰标识映射后的表名 String captureName = StringUtil.captureName(StringUtil.camelCaseName(tableName)); //自定义参数 HashMap<String, String> customParameter = new HashMap<>(); customParameter.put("author","作者:Auto Generator By 'huanzi-qch'"); customParameter.put("date","生成日期:"+new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())); //读取模板、生成代码 writer(tlfPath+"controller.tlf", filePath + "controller\\" + captureName + "Controller.java", tableInfo,customParameter); writer(tlfPath+"entity.tlf", filePath + "pojo\\" + captureName + ".java", tableInfo,customParameter); writer(tlfPath+"entityvo.tlf", filePath + "vo\\" + captureName + "Vo.java", tableInfo,customParameter); writer(tlfPath+"repository.tlf", filePath + "repository\\" + captureName + "Repository.java", tableInfo,customParameter); writer(tlfPath+"service.tlf", filePath + "service\\" + captureName + "Service.java", tableInfo,customParameter); writer(tlfPath+"serviceimpl.tlf", filePath + "service\\" + captureName + "ServiceImpl.java", tableInfo,customParameter); return tableName + " 后台代码生成完毕!";}
比较复杂的就是#for、#if的处理,我这里只是简单实现,不过也完全够我们用了
效果 V1.0版本
V2.0版本 后记
大部分项目的代码都是可以复用的,特别是像我们这种封装了一套通用代码,单表直接继承实现CRUD、分页等功能,每个模块高度相似的代码,代码生成器就成了敏捷开发中重要的一步,直接根据数据库表生成我们想要的代码,省去了一步步创建文件、复制粘贴文件内容的繁琐步骤,实现快速开发!
自己写的代码生成器,扩展性更强,满足每个业务模块的代码要求不成问题
开源
在这里贴出完整代码,全都在一个类里面,并且没有其他依赖包,很纯!
v1.0 AutoGenerator
package cn.huanzi.qch.baseadmin.autogenerator;import java.io.File;import java.io.FileWriter;import java.io.PrintWriter;import java.sql.*;import java.util.ArrayList;import java.util.List;/** * 代码生成工具 V1.0 */public class AutoGenerator { /** * 程序自动设置 */ private String tableName;//表名 private String tableComment;//表注释 private String filePath;//最终文件生成位置 /** * 数据连接相关,需要手动设置 */ private static final String URL = "jdbc:mysql://localhost:3306/test?serverTimezone=GMT%2B8&characterEncoding=utf-8"; private static final String USERNAME = "root"; private static final String PASSWORD = "123456"; private static final String DRIVER_CLASSNAME = "com.mysql.cj.jdbc.Driver"; /** * 基础路径,需要手动设置 */ private String basePackage = "cn\\huanzi\\qch\\baseadmin\\";//根包位置 private String filePackage = basePackage + "sys\\";//文件所在包位置 /** * 构造参数,设置表名 */ private AutoGenerator(String tableName) { //设置表名 this.tableName = tableName; //文件所在包位置 filePackage = filePackage + StringUtil.camelCaseName(tableName).toLowerCase() + "\\"; //拼接完整最终位置 System.getProperty("user.dir") 获取的是项目所在路径,如果我们是子项目,则需要添加一层路径 filePath = System.getProperty("user.dir") + "\\src\\main\\java\\" + filePackage; } /** * 创建pojo实体类 */ private void createPojo(List<TableInfo> tableInfos) { //创建文件 File file = FileUtil.createFile(filePath + "pojo\\" + StringUtil.captureName(StringUtil.camelCaseName(tableName)) + ".java"); //拼接文件内容 StringBuffer stringBuffer = new StringBuffer(); stringBuffer.append( "package " + filePackage.replaceAll("\\\\", ".") + "pojo;\n" + "\n" + "import lombok.Data;\n" + "import javax.persistence.*;\n" + "import java.io.Serializable;\n" + "import java.util.Date;\n" + "\n" + "@Entity\n" + "@Table(name = \"" + tableName + "\")\n" + "@Data\n" + "public class " + StringUtil.captureName(StringUtil.camelCaseName(tableName)) + " implements Serializable {\n" ); //遍历设置属性 for (TableInfo tableInfo : tableInfos) { //主键 if ("PRI".equals(tableInfo.getColumnKey())) { stringBuffer.append(" @Id\n"); } //自增 if ("auto_increment".equals(tableInfo.getExtra())) { stringBuffer.append(" @GeneratedValue(strategy= GenerationType.IDENTITY)\n"); } stringBuffer.append(" private ").append(StringUtil.typeMapping(tableInfo.getDataType())).append(" ").append(StringUtil.camelCaseName(tableInfo.getColumnName())).append(";//").append(tableInfo.getColumnComment()).append("\n\n"); } stringBuffer.append("}"); //写入文件内容 FileUtil.fileWriter(file, stringBuffer); } /** * 创建vo类 */ private void createVo(List<TableInfo> tableInfos) { File file = FileUtil.createFile(filePath + "vo\\" + StringUtil.captureName(StringUtil.camelCaseName(tableName)) + "Vo.java"); StringBuffer stringBuffer = new StringBuffer(); stringBuffer.append( "package " + filePackage.replaceAll("\\\\", ".") + "vo;\n" + "\n" + "import "+ basePackage.replaceAll("\\\\", ".") +" common.pojo.PageCondition;"+ "import lombok.Data;\n" + "import java.io.Serializable;\n" + "import java.util.Date;\n" + "\n" + "@Data\n" + "public class " + StringUtil.captureName(StringUtil.camelCaseName(tableName)) + "Vo extends PageCondition implements Serializable {\n" ); //遍历设置属性 for (TableInfo tableInfo : tableInfos) { stringBuffer.append(" private ").append(StringUtil.typeMapping(tableInfo.getDataType())).append(" ").append(StringUtil.camelCaseName(tableInfo.getColumnName())).append(";//").append(tableInfo.getColumnComment()).append("\n\n"); } stringBuffer.append("}"); FileUtil.fileWriter(file, stringBuffer); } /** * 创建repository类 */ private void createRepository(List<TableInfo> tableInfos) { File file = FileUtil.createFile(filePath + "repository\\" + StringUtil.captureName(StringUtil.camelCaseName(tableName)) + "Repository.java"); StringBuffer stringBuffer = new StringBuffer(); String t = "String"; //遍历属性 for (TableInfo tableInfo : tableInfos) { //主键 if ("PRI".equals(tableInfo.getColumnKey())) { t = StringUtil.typeMapping(tableInfo.getDataType()); } } stringBuffer.append( "package " + filePackage.replaceAll("\\\\", ".") + "repository;\n" + "\n" + "import " + basePackage.replaceAll("\\\\", ".") + "common.repository.*;\n" + "import " + filePackage.replaceAll("\\\\", ".") + "pojo." + StringUtil.captureName(StringUtil.camelCaseName(tableName)) + ";\n" + "import org.springframework.stereotype.Repository;\n" + "\n" + "@Repository\n" + "public interface " + StringUtil.captureName(StringUtil.camelCaseName(tableName)) + "Repository extends CommonRepository<" + StringUtil.captureName(StringUtil.camelCaseName(tableName)) + ", " + t + "> {" ); stringBuffer.append("\n"); stringBuffer.append("}"); FileUtil.fileWriter(file, stringBuffer); } /** * 创建service类 */ private void createService(List<TableInfo> tableInfos) { File file = FileUtil.createFile(filePath + "service\\" + StringUtil.captureName(StringUtil.camelCaseName(tableName)) + "Service.java"); StringBuffer stringBuffer = new StringBuffer(); String t = "String"; //遍历属性 for (TableInfo tableInfo : tableInfos) { //主键 if ("PRI".equals(tableInfo.getColumnKey())) { t = StringUtil.typeMapping(tableInfo.getDataType()); } } stringBuffer.append( "package " + filePackage.replaceAll("\\\\", ".") + "service;\n" + "\n" + "import " + basePackage.replaceAll("\\\\", ".") + "common.service.*;\n" + "import " + filePackage.replaceAll("\\\\", ".") + "pojo." + StringUtil.captureName(StringUtil.camelCaseName(tableName)) + ";\n" + "import " + filePackage.replaceAll("\\\\", ".") + "vo." + StringUtil.captureName(StringUtil.camelCaseName(tableName)) + "Vo;\n" + "\n" + "public interface " + StringUtil.captureName(StringUtil.camelCaseName(tableName)) + "Service extends CommonService<" + StringUtil.captureName(StringUtil.camelCaseName(tableName)) + "Vo, " + StringUtil.captureName(StringUtil.camelCaseName(tableName)) + ", " + t + "> {" ); stringBuffer.append("\n"); stringBuffer.append("}"); FileUtil.fileWriter(file, stringBuffer); //Impl File file1 = FileUtil.createFile(filePath + "service\\" + StringUtil.captureName(StringUtil.camelCaseName(tableName)) + "ServiceImpl.java"); StringBuffer stringBuffer1 = new StringBuffer(); stringBuffer1.append( "package " + filePackage.replaceAll("\\\\", ".") + "service;\n" + "\n" + "import " + basePackage.replaceAll("\\\\", ".") + "common.service.*;\n" + "import " + filePackage.replaceAll("\\\\", ".") + "pojo." + StringUtil.captureName(StringUtil.camelCaseName(tableName)) + ";\n" + "import " + filePackage.replaceAll("\\\\", ".") + "vo." + StringUtil.captureName(StringUtil.camelCaseName(tableName)) + "Vo;\n" + "import " + filePackage.replaceAll("\\\\", ".") + "repository." + StringUtil.captureName(StringUtil.camelCaseName(tableName)) + "Repository;\n" + "import org.springframework.beans.factory.annotation.Autowired;\n" + "import org.springframework.stereotype.Service;\n" + "import org.springframework.transaction.annotation.Transactional;\n" + "import javax.persistence.EntityManager;\n" + "import javax.persistence.PersistenceContext;\n" + "\n" + "@Service\n" + "@Transactional\n" + "public class " + StringUtil.captureName(StringUtil.camelCaseName(tableName)) + "ServiceImpl extends CommonServiceImpl<" + StringUtil.captureName(StringUtil.camelCaseName(tableName)) + "Vo, " + StringUtil.captureName(StringUtil.camelCaseName(tableName)) + ", " + t + "> implements " + StringUtil.captureName(StringUtil.camelCaseName(tableName)) + "Service{" ); stringBuffer1.append("\n\n"); stringBuffer1.append( " @PersistenceContext\n" + " private EntityManager em;\n"); stringBuffer1.append("" + " @Autowired\n" + " private " + StringUtil.captureName(StringUtil.camelCaseName(tableName)) + "Repository " + StringUtil.camelCaseName(tableName) + "Repository;\n"); stringBuffer1.append("}"); FileUtil.fileWriter(file1, stringBuffer1); } /** * 创建controller类 */ private void createController(List<TableInfo> tableInfos) { File file = FileUtil.createFile(filePath + "controller\\" + StringUtil.captureName(StringUtil.camelCaseName(tableName)) + "Controller.java"); StringBuffer stringBuffer = new StringBuffer(); String t = "String"; //遍历属性 for (TableInfo tableInfo : tableInfos) { //主键 if ("PRI".equals(tableInfo.getColumnKey())) { t = StringUtil.typeMapping(tableInfo.getDataType()); } } stringBuffer.append( "package " + filePackage.replaceAll("\\\\", ".") + "controller;\n" + "\n" + "import " + basePackage.replaceAll("\\\\", ".") + "common.controller.*;\n" + "import " + filePackage.replaceAll("\\\\", ".") + "pojo." + StringUtil.captureName(StringUtil.camelCaseName(tableName)) + ";\n" + "import " + filePackage.replaceAll("\\\\", ".") + "vo." + StringUtil.captureName(StringUtil.camelCaseName(tableName)) + "Vo;\n" + "import " + filePackage.replaceAll("\\\\", ".") + "service." + StringUtil.captureName(StringUtil.camelCaseName(tableName)) + "Service;\n" + "import org.springframework.beans.factory.annotation.Autowired;\n" + "import org.springframework.web.bind.annotation.*;\n" + "\n" + "@RestController\n" + "@RequestMapping(\"/sys/" + StringUtil.camelCaseName(tableName) + "/\")\n" + "public class " + StringUtil.captureName(StringUtil.camelCaseName(tableName)) + "Controller extends CommonController<" + StringUtil.captureName(StringUtil.camelCaseName(tableName)) + "Vo, " + StringUtil.captureName(StringUtil.camelCaseName(tableName)) + ", " + t + "> {" ); stringBuffer.append("\n"); stringBuffer.append("" + " @Autowired\n" + " private " + StringUtil.captureName(StringUtil.camelCaseName(tableName)) + "Service " + StringUtil.camelCaseName(tableName) + "Service;\n"); stringBuffer.append("}"); FileUtil.fileWriter(file, stringBuffer); } /** * file工具类 */ private static class FileUtil { /** * 创建文件 * * @param pathNameAndFileName 路径跟文件名 * @return File对象 */ private static File createFile(String pathNameAndFileName) { File file = new File(pathNameAndFileName); try { //获取父目录 File fileParent = file.getParentFile(); if (!fileParent.exists()) { fileParent.mkdirs(); } //创建文件 if (!file.exists()) { file.createNewFile(); } } catch (Exception e) { file = null; System.err.println("新建文件操作出错"); e.printStackTrace(); } return file; } /** * 字符流写入文件 * * @param file file对象 * @param stringBuffer 要写入的数据 */ private static void fileWriter(File file, StringBuffer stringBuffer) { //字符流 try { FileWriter resultFile = new FileWriter(file, false);//true,则追加写入 false,则覆盖写入 PrintWriter myFile = new PrintWriter(resultFile); //写入 myFile.println(stringBuffer.toString()); myFile.close(); resultFile.close(); } catch (Exception e) { System.err.println("写入操作出错"); e.printStackTrace(); } } } /** * 字符串处理工具类 */ private static class StringUtil { /** * 数据库类型->JAVA类型 * * @param dbType 数据库类型 * @return JAVA类型 */ private static String typeMapping(String dbType) { String javaType; if ("int|integer".contains(dbType)) { javaType = "Integer"; } else if ("float|double|decimal|real".contains(dbType)) { javaType = "Double"; } else if ("date|time|datetime|timestamp".contains(dbType)) { javaType = "Date"; } else { javaType = "String"; } return javaType; } /** * 驼峰转换为下划线 */ private static String underscoreName(String camelCaseName) { StringBuilder result = new StringBuilder(); if (camelCaseName != null && camelCaseName.length() > 0) { result.append(camelCaseName.substring(0, 1).toLowerCase()); for (int i = 1; i < camelCaseName.length(); i++) { char ch = camelCaseName.charAt(i); if (Character.isUpperCase(ch)) { result.append("_"); result.append(Character.toLowerCase(ch)); } else { result.append(ch); } } } return result.toString(); } /** * 首字母大写 */ private static String captureName(String name) { char[] cs = name.toCharArray(); cs[0] -= 32; return String.valueOf(cs); } /** * 下划线转换为驼峰 */ private static String camelCaseName(String underscoreName) { StringBuilder result = new StringBuilder(); if (underscoreName != null && underscoreName.length() > 0) { boolean flag = false; for (int i = 0; i < underscoreName.length(); i++) { char ch = underscoreName.charAt(i); if ("_".charAt(0) == ch) { flag = true; } else { if (flag) { result.append(Character.toUpperCase(ch)); flag = false; } else { result.append(ch); } } } } return result.toString(); } } /** * JDBC连接数据库工具类 */ private static class DBConnectionUtil { static { // 1、加载驱动 try { Class.forName(DRIVER_CLASSNAME); } catch (ClassNotFoundException e) { e.printStackTrace(); } } /** * 返回一个Connection连接 */ static Connection getConnection() { Connection conn = null; // 2、连接数据库 try { conn = DriverManager.getConnection(URL, USERNAME, PASSWORD); } catch (SQLException e) { e.printStackTrace(); } return conn; } /** * 关闭Connection,Statement连接 */ public static void close(Connection conn, Statement stmt) { try { conn.close(); stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } /** * 关闭Connection,Statement,ResultSet连接 */ public static void close(Connection conn, Statement stmt, ResultSet rs) { try { close(conn, stmt); rs.close(); } catch (SQLException e) { e.printStackTrace(); } } } /** * 表结构信息实体类 */ private class TableInfo { private String columnName;//字段名 private String dataType;//字段类型 private String columnComment;//字段注释 private String columnKey;//主键 private String extra;//主键类型 public String getColumnName() { return columnName; } public void setColumnName(String columnName) { this.columnName = columnName; } public String getDataType() { return dataType; } public void setDataType(String dataType) { this.dataType = dataType; } public String getColumnComment() { return columnComment; } public void setColumnComment(String columnComment) { this.columnComment = columnComment; } public String getColumnKey() { return columnKey; } public void setColumnKey(String columnKey) { this.columnKey = columnKey; } public String getExtra() { return extra; } public void setExtra(String extra) { this.extra = extra; } } /** * 获取表结构信息 * 目前仅支持mysql */ private List<TableInfo> getTableInfo() { Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; ArrayList<TableInfo> list = new ArrayList<>(); try { conn = DBConnectionUtil.getConnection(); //表字段信息 String sql = "select column_name,data_type,column_comment,column_key,extra from information_schema.columns where table_schema = (select database()) and table_name=?"; ps = conn.prepareStatement(sql); ps.setString(1, tableName); rs = ps.executeQuery(); while (rs.next()) { TableInfo tableInfo = new TableInfo(); //列名,全部转为小写 tableInfo.setColumnName(rs.getString("column_name").toLowerCase()); //列类型 tableInfo.setDataType(rs.getString("data_type")); //列注释 tableInfo.setColumnComment(rs.getString("column_comment")); //主键 tableInfo.setColumnKey(rs.getString("column_key")); //主键类型 tableInfo.setExtra(rs.getString("extra")); list.add(tableInfo); } //表注释 sql = "select table_comment from information_schema.tables where table_schema = (select database()) and table_name=?"; ps = conn.prepareStatement(sql); ps.setString(1, tableName); rs = ps.executeQuery(); while (rs.next()) { //表注释 tableComment = rs.getString("table_comment"); } } catch (SQLException e) { e.printStackTrace(); } finally { if(rs != null){ DBConnectionUtil.close(conn, ps, rs); } } return list; } /** * 快速创建,供外部调用,调用之前先设置一下项目的基础路径 */ private String create() { System.out.println("生成路径位置:" + filePath); //获取表信息 List<TableInfo> tableInfo = getTableInfo(); //开始生成代码 createPojo(tableInfo); createVo(tableInfo); createRepository(tableInfo); createService(tableInfo); createController(tableInfo); return tableName + " 后台代码生成完毕!"; } public static void main(String[] args) {// String[] tables = {"sys_user","sys_menu","sys_authority","sys_user_menu","sys_user_authority","sys_shortcut_menu","sys_setting"}; String[] tables = {"tb_user"}; for (String table : tables) { String msg = new AutoGenerator(table).create(); System.out.println(msg); } }}
v2.0 AutoGeneratorPlus
package cn.huanzi.qch.baseadmin.autogenerator;import java.io.*;import java.sql.*;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.HashMap;import java.util.List;import java.util.Map;/** * 代码生成工具 V2.0 */public class AutoGeneratorPlus { /** * 程序自动设置 */ private String tableName;//表名 private String tableComment;//表注释 private String filePath;//最终文件生成位置 /** * 数据连接相关,需要手动设置 */ private static final String URL = "jdbc:mysql://localhost:3306/test?serverTimezone=GMT%2B8&characterEncoding=utf-8"; private static final String USERNAME = "root"; private static final String PASSWORD = "123456"; private static final String DRIVER_CLASSNAME = "com.mysql.cj.jdbc.Driver"; /** * 基础路径,需要手动设置 */ private String tlfPath = System.getProperty("user.dir") + "\\src\\main\\resources\\tlf\\";//模板文件位置 private String basePackage = "cn\\huanzi\\qch\\baseadmin\\";//根包位置 private String filePackage = basePackage + "sys\\";//文件所在包位置 /** * 构造参数,设置表名 */ private AutoGeneratorPlus(String tableName) { //设置表名 this.tableName = tableName; //文件所在包位置 filePackage = filePackage + StringUtil.camelCaseName(tableName).toLowerCase() + "\\"; //拼接完整最终位置 System.getProperty("user.dir") 获取的是项目所在路径,如果我们是子项目,则需要添加一层路径 filePath = System.getProperty("user.dir") + "\\src\\main\\java\\" + filePackage; } /** * 读取模板,设置内容,生成文件 * @param templatePath 模板文件路径 * @param outputFile 文件生成路径 * @param tableInfos 表字段信息 * @param customParameter 自定义参数 */ private void writer(String templatePath, String outputFile,List<TableInfo> tableInfos,Map<String,String> customParameter){ //主键 TableInfo prikey = new TableInfo(); //for循环标识 boolean forFlag = false; StringBuilder forContent = new StringBuilder(); //驼峰标识映射后的表名 String replacement = StringUtil.captureName(StringUtil.camelCaseName(tableName)); //遍历属性 for (TableInfo tableInfo : tableInfos) { //主键 if ("PRI".equals(tableInfo.getColumnKey())) { prikey = tableInfo; break; } } try(FileReader fileReader = new FileReader(templatePath); BufferedReader reader = new BufferedReader(fileReader)) { //生成文件 File file = FileUtil.createFile(outputFile); StringBuffer stringBuffer = new StringBuffer(); //读取模板文件,拼接文件内容 Object[] lines = reader.lines().toArray(); for (Object o : lines) { String line = String.valueOf(o); /* 设置值 */ //${tableName} 表名称,例如:tb_user if(line.contains("${tableName}")){ line = line.replaceAll("\\$\\{tableName}", tableName); } //${tableComment} 表注释,例如:tb_user if(line.contains("${tableComment}")){ line = line.replaceAll("\\$\\{tableComment}", tableComment); } //${entity} 实体类名称,例如:TbUser if(line.contains("${entity}")){ line = line.replaceAll("\\$\\{entity}", replacement); } //${entityFirstToLowerCase} 实体类名称首字母小写,例如:tbUser if(line.contains("${entityFirstToLowerCase}")){ line = line.replaceAll("\\$\\{entityFirstToLowerCase}", StringUtil.camelCaseName(tableName)); } //${entityToLowerCase} 实体类名称全小写,例如:tbuser if(line.contains("${entityToLowerCase}")){ line = line.replaceAll("\\$\\{entityToLowerCase}", replacement.toLowerCase()); } //${priDataType} 实体类主键类型,例如:String if(line.contains("${priDataType}")){ line = line.replaceAll("\\$\\{priDataType}", StringUtil.typeMapping(prikey.getDataType())); } //处理自定义参数 line = customParameter(line,customParameter); //先取得循环体的内容 if(forFlag){ forContent.append(line).append("\n"); } //是否为for循环遍历表字段 if(line.contains("#for")){ forFlag = true; } if(line.contains("#end")){ forFlag = false; line = line.replaceAll("#end", ""); } //遍历循环体的内容,并设置值 if(!forFlag && forContent.length() > 0){ //遍历表字段 for (TableInfo tableInfo : tableInfos) { String tableColumns = forContent.toString() //表字段信息:类型、名称、注释 .replaceAll("\\$\\{tableInfo.dataType}", StringUtil.typeMapping(tableInfo.getDataType())) .replaceAll("\\$\\{tableInfo.columnName}", StringUtil.camelCaseName(tableInfo.getColumnName())) .replaceAll("\\$\\{tableInfo.columnComment}", tableInfo.getColumnComment()); //清除多余#end,以及换行符 tableColumns = tableColumns.replaceAll("#end", "").replaceAll("\n", ""); //设置是否主键、是否自增 String pri = "",autoIncrement=""; //主键 if ("PRI".equals(tableInfo.getColumnKey())) { pri = " @Id\n"; //自增id if ("auto_increment".equals(tableInfo.getExtra())){ autoIncrement = "@GeneratedValue(strategy= GenerationType.IDENTITY)\n"; } } tableColumns = tableColumns .replaceAll("#ifPri", pri) .replaceAll("#ifAutoIncrement", autoIncrement); //处理自定义参数 tableColumns = customParameter(tableColumns,customParameter); //前补tab,后补换行符 stringBuffer.append(" ").append(tableColumns.trim()).append("\n\n"); } //置空 forContent.setLength(0); } if(!forFlag){ stringBuffer.append(line).append("\n"); } } //写入数据到到文件中 FileUtil.fileWriter(file, stringBuffer); }catch (Exception e){ e.printStackTrace(); } } private void writer(String templatePath, String outputFile,List<TableInfo> tableInfos){ writer(templatePath,outputFile,tableInfos,new HashMap<>()); } /** * 处理自定义参数 */ private String customParameter(String str,Map<String,String> customParameter){ for (String key : customParameter.keySet()) { str = str.replaceAll("\\$\\{"+key+"}",customParameter.get(key)); } return str; } /** * file工具类 */ private static class FileUtil { /** * 创建文件 * * @param pathNameAndFileName 路径跟文件名 * @return File对象 */ private static File createFile(String pathNameAndFileName) { File file = new File(pathNameAndFileName); try { //获取父目录 File fileParent = file.getParentFile(); if (!fileParent.exists()) { fileParent.mkdirs(); } //创建文件 if (!file.exists()) { file.createNewFile(); } } catch (Exception e) { file = null; System.err.println("新建文件操作出错"); e.printStackTrace(); } return file; } /** * 字符流写入文件 * * @param file file对象 * @param stringBuffer 要写入的数据 */ private static void fileWriter(File file, StringBuffer stringBuffer) { //字符流 try { FileWriter resultFile = new FileWriter(file, false);//true,则追加写入 false,则覆盖写入 PrintWriter myFile = new PrintWriter(resultFile); //写入 myFile.println(stringBuffer.toString()); myFile.close(); resultFile.close(); } catch (Exception e) { System.err.println("写入操作出错"); e.printStackTrace(); } } } /** * 字符串处理工具类 */ private static class StringUtil { /** * 数据库类型->JAVA类型 * * @param dbType 数据库类型 * @return JAVA类型 */ private static String typeMapping(String dbType) { String javaType; if ("int|integer".contains(dbType)) { javaType = "Integer"; } else if ("float|double|decimal|real".contains(dbType)) { javaType = "Double"; } else if ("date|time|datetime|timestamp".contains(dbType)) { javaType = "Date"; } else { javaType = "String"; } return javaType; } /** * 驼峰转换为下划线 */ private static String underscoreName(String camelCaseName) { StringBuilder result = new StringBuilder(); if (camelCaseName != null && camelCaseName.length() > 0) { result.append(camelCaseName.substring(0, 1).toLowerCase()); for (int i = 1; i < camelCaseName.length(); i++) { char ch = camelCaseName.charAt(i); if (Character.isUpperCase(ch)) { result.append("_"); result.append(Character.toLowerCase(ch)); } else { result.append(ch); } } } return result.toString(); } /** * 首字母大写 */ private static String captureName(String name) { char[] cs = name.toCharArray(); cs[0] -= 32; return String.valueOf(cs); } /** * 下划线转换为驼峰 */ private static String camelCaseName(String underscoreName) { StringBuilder result = new StringBuilder(); if (underscoreName != null && underscoreName.length() > 0) { boolean flag = false; for (int i = 0; i < underscoreName.length(); i++) { char ch = underscoreName.charAt(i); if ("_".charAt(0) == ch) { flag = true; } else { if (flag) { result.append(Character.toUpperCase(ch)); flag = false; } else { result.append(ch); } } } } return result.toString(); } } /** * JDBC连接数据库工具类 */ private static class DBConnectionUtil { static { // 1、加载驱动 try { Class.forName(DRIVER_CLASSNAME); } catch (ClassNotFoundException e) { e.printStackTrace(); } } /** * 返回一个Connection连接 */ static Connection getConnection() { Connection conn = null; // 2、连接数据库 try { conn = DriverManager.getConnection(URL, USERNAME, PASSWORD); } catch (SQLException e) { e.printStackTrace(); } return conn; } /** * 关闭Connection,Statement连接 */ public static void close(Connection conn, Statement stmt) { try { conn.close(); stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } /** * 关闭Connection,Statement,ResultSet连接 */ public static void close(Connection conn, Statement stmt, ResultSet rs) { try { close(conn, stmt); rs.close(); } catch (SQLException e) { e.printStackTrace(); } } } /** * 表结构信息实体类 */ private class TableInfo { private String columnName;//字段名 private String dataType;//字段类型 private String columnComment;//字段注释 private String columnKey;//主键 private String extra;//主键类型 public String getColumnName() { return columnName; } public void setColumnName(String columnName) { this.columnName = columnName; } public String getDataType() { return dataType; } public void setDataType(String dataType) { this.dataType = dataType; } public String getColumnComment() { return columnComment; } public void setColumnComment(String columnComment) { this.columnComment = columnComment; } public String getColumnKey() { return columnKey; } public void setColumnKey(String columnKey) { this.columnKey = columnKey; } public String getExtra() { return extra; } public void setExtra(String extra) { this.extra = extra; } } /** * 获取表结构信息 * 目前仅支持mysql */ private List<TableInfo> getTableInfo() { Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; ArrayList<TableInfo> list = new ArrayList<>(); try { conn = DBConnectionUtil.getConnection(); //表字段信息 String sql = "select column_name,data_type,column_comment,column_key,extra from information_schema.columns where table_schema = (select database()) and table_name=?"; ps = conn.prepareStatement(sql); ps.setString(1, tableName); rs = ps.executeQuery(); while (rs.next()) { TableInfo tableInfo = new TableInfo(); //列名,全部转为小写 tableInfo.setColumnName(rs.getString("column_name").toLowerCase()); //列类型 tableInfo.setDataType(rs.getString("data_type")); //列注释 tableInfo.setColumnComment(rs.getString("column_comment")); //主键 tableInfo.setColumnKey(rs.getString("column_key")); //主键类型 tableInfo.setExtra(rs.getString("extra")); list.add(tableInfo); } //表注释 sql = "select table_comment from information_schema.tables where table_schema = (select database()) and table_name=?"; ps = conn.prepareStatement(sql); ps.setString(1, tableName); rs = ps.executeQuery(); while (rs.next()) { //表注释 tableComment = rs.getString("table_comment"); } } catch (SQLException e) { e.printStackTrace(); } finally { if(rs != null){ DBConnectionUtil.close(conn, ps, rs); } } return list; } /** * 快速创建,供外部调用,调用之前先设置一下项目的基础路径 */ private String create() { System.out.println("生成路径位置:" + filePath); //获取表信息 List<TableInfo> tableInfo = getTableInfo(); //驼峰标识映射后的表名 String captureName = StringUtil.captureName(StringUtil.camelCaseName(tableName)); //自定义参数 HashMap<String, String> customParameter = new HashMap<>(); customParameter.put("author","作者:Auto Generator By 'huanzi-qch'"); customParameter.put("date","生成日期:"+new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())); //读取模板、生成代码 writer(tlfPath+"controller.tlf", filePath + "controller\\" + captureName + "Controller.java", tableInfo,customParameter); writer(tlfPath+"entity.tlf", filePath + "pojo\\" + captureName + ".java", tableInfo,customParameter); writer(tlfPath+"entityvo.tlf", filePath + "vo\\" + captureName + "Vo.java", tableInfo,customParameter); writer(tlfPath+"repository.tlf", filePath + "repository\\" + captureName + "Repository.java", tableInfo,customParameter); writer(tlfPath+"service.tlf", filePath + "service\\" + captureName + "Service.java", tableInfo,customParameter); writer(tlfPath+"serviceimpl.tlf", filePath + "service\\" + captureName + "ServiceImpl.java", tableInfo,customParameter); return tableName + " 后台代码生成完毕!"; } public static void main(String[] args) {// String[] tables = {"sys_user","sys_menu","sys_authority","sys_user_menu","sys_user_authority","sys_shortcut_menu","sys_setting"}; String[] tables = {"tb_user"}; for (String table : tables) { String msg = new AutoGeneratorPlus(table).create(); System.out.println(msg); } }}
同时,所有代码都在base admin项目里,代码已经开源、托管到我的GitHub、码云:
GitHub:
码云:
版权声明
作者:huanzi-qch
出处:
若标题中有“转载”字样,则本文版权归原作者所有。若无转载字样,本文版权归作者所有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文链接,否则保留追究法律责任的权利.
标签: #oraclec获取表结构