Elastic管理平台开发记录一：代码生成

需求

• 将多个数据库连接的多张表的数据同步到es中

1.问题分析

　　现在数据库类型有 MySQL 和 SQL server， 数据库有很多，表也有很多，所以如果把每个表都用 JavaBean 写一遍，那得写成千上万个表，所以不可取。然后我想到用 Freemarker 模板引擎来自动生成 JavaBean，用到哪张表就生成哪个。前台提供三个 select 选择框， 分别选择数据库 url， database， table， 后端拿到参数后查询到该表的所有字段，生成对应的 model， dao， mapper，为数据同步做准备

2.具体实现

　　维护一张 datasource 的表来存储所有的数据库连接相关信息，在前台用户选了某个连接后 select 加载对应的数据库信息， table同理

2.1 pom文件

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.sci99.generator</groupId>
    <artifactId>EsAutoGenerator</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.6.RELEASE</version>
    </parent>

    <dependencies>
        <!--Spring Boot依赖-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-freemarker</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger2</artifactId>
            <version>2.9.2</version>
        </dependency>
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-swagger-ui</artifactId>
            <version>2.9.2</version>
        </dependency>

        <!-- 导入Mysql数据库链接jar包 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.30</version>
        </dependency>

        <!-- Spring Boot Mybatis 依赖 -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.5</version>
        </dependency>
<!--         Druid 数据连接池依赖-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.18</version>
        </dependency>



        <!-- freemarker依赖 -->
        <dependency>
            <groupId>org.freemarker</groupId>
            <artifactId>freemarker</artifactId>
            <version>2.3.23</version>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus</artifactId>
            <version>3.0.7</version>
        </dependency>

    <!-- 导入SQLServer数据库链接jar包 -->
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>sqljdbc4</artifactId>
            <version>4.0</version>
        </dependency>

        <!--常用库依赖-->
        <dependency>
            <groupId>commons-codec</groupId>
            <artifactId>commons-codec</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.6</version>
        </dependency>
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.4</version>
        </dependency>

        <dependency>
            <groupId>org.elasticsearch.client</groupId>
            <artifactId>transport</artifactId>
            <version>5.2.0</version>
            <exclusions>
                <exclusion>
                    <groupId>org.elasticsearch</groupId>
                    <artifactId>elasticsearch</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.elasticsearch</groupId>
            <artifactId>elasticsearch</artifactId>
            <version>5.2.0</version>
        </dependency>

        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-core</artifactId>
            <version>2.7</version>
        </dependency>

<!--        工具集-->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.3.8</version>
        </dependency>
        <!-- shiro -->
        <dependency>
            <groupId>org.apache.shiro</groupId>
            <artifactId>shiro-spring</artifactId>
            <version>1.4.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shiro</groupId>
            <artifactId>shiro-ehcache</artifactId>
            <version>1.3.2</version>
        </dependency>

        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-annotations</artifactId>
            <version>2.8.8</version>
        </dependency>

        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-databind</artifactId>
            <version>2.8.8</version>
        </dependency>

        <dependency>
            <groupId>org.codehaus.jackson</groupId>
            <artifactId>jackson-mapper-asl</artifactId>
            <version>1.9.13</version>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <!-- 不配置热加载也没效果 -->
                <configuration>
                    <fork>true</fork>
                </configuration>
            </plugin>
            <plugin>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>${java.version}</source>
                    <target>${java.version}</target>
                    <encoding>UTF-8</encoding>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

2.2 配置文件

server:
  port: 8800

spring:
  datasource:
    username: xx
    password: xx
    url: jdbc:mysql://xx:3306/xx?characterEncoding=utf8
    driver-class-name: com.mysql.jdbc.Driver
    #配置初始化大小/最小/最大
    initialSize: 25
    minIdle: 25
    maxActive: 100
    #获取连接等待超时时间
    maxWait: 3600000
    #间隔多久进行一次检测，检测需要关闭的空闲连接
    timeBetweenEvictionRunsMillis: 60000
    #一个连接在池中最小生存的时间
    minEvictableIdleTimeMillis: 300000
    validationQuery: SELECT 1
    testWhileIdle: true
    testOnBorrow: true
    testOnReturn: true

mybatis:
  mapper-locations: classpath:mapping/**Mapper.xml
  type-aliases-package: com.xx.xx.model

2.3 查询语句

    private final String MySQLDataBase = "select schema_name as name from information_schema.schemata order by name";
    private final String MySQLTable = "select table_name as name from information_schema.tables where table_schema=? order by name;";
    private final String MySQLField = "select * from information_schema.COLUMNS where table_name = ? and table_schema = ?";

    private final String SQLServerDataBase = "select name from sysdatabases order by name";
    private final String SQLServerTable = "select name from sysobjects where xtype='U' order by name";
    private final String SQLServerField = "select c.* from syscolumns c, sysobjects t where c.id = t.id and t.type = 'U' and t.name = ? order by colid";

2.4 前端 select

　　以其中一个为例，其中 form.on 是 layui 的语法，影响不大

            //监听数据库连接select
            form.on('select(urls)', function (data) {
                var url = data.value;
                if (url != 0) {
                    $.ajax({
                        url: path + 'database/list/' + url,
                        async: false,//这得注意是同步
                        method: 'get',
                        success: function (data) {
                            if (data.code == 200) {
                                var list = data.data;
                                var htmls = '<option value="0">请选择</option>';
                                for (var x in list) {
                                    htmls += '<option value = "' + list[x] + '">' + list[x] + '</option>'
                                }
                                $("#databases").html(htmls);
                                $("#tables").empty();
                            } else {
                                var htmls = '<option value="0">获取数据失败</option>';
                                $("#databases").html(htmls);
                            }
                        }
                    });
                    form.render('select', 'databases');
                    form.render();
                } else {
                    $("#form")[0].reset();
                    $("#databases").empty();
                    $("#tables").empty();
                }
                $("#tableField").hide()
            });

2.5 代码生成

　　service部分

            Connection connection = JdbcUtil.getConnection(datasource, params.getDatabases());
            // 根据数据库类型选择查询语句
            if ("mysql".equals(datasource.getType())) {
                PreparedStatement statement = connection.prepareStatement(MySQLField);
                statement.setString(1, params.getTables());
                statement.setString(2, params.getDatabases());
                ResultSet resultSet = statement.executeQuery();
                CodeGenerateUtils.generateMySQLModelFile(resultSet, params.getTables());
                CodeGenerateUtils.generateDaoFile(params.getTables());
                CodeGenerateUtils.generateMapperFile(resultSet, params.getTables(), true);
                CodeGenerateUtils.compilerClasses(params.getTables());
                JdbcUtil.close(connection, statement, resultSet);
                return true;
            } else {
                PreparedStatement statement = connection.prepareStatement(SQLServerField);
                statement.setString(1, params.getTables());
                ResultSet resultSet = statement.executeQuery();
                CodeGenerateUtils.generateSQLServerModelFile(resultSet, params.getTables());
                CodeGenerateUtils.generateDaoFile(params.getTables());
                CodeGenerateUtils.generateMapperFile(resultSet, params.getTables(), false);
                CodeGenerateUtils.compilerClasses(params.getTables());
                JdbcUtil.close(connection, statement, resultSet);
                return true;
            }

CodeGenerateUtils.java

public class CodeGenerateUtils {

    private static final String AUTHOR = "xx";
    private static final String packageName = "xx.xx.xx";
    private static final String diskPath = System.getProperty("user.dir")+"/src/main/java/xx/xx/xx";
    private static String primaryKey;
    private static String primaryKeyOrigin;
    private static String primaryType;
    private static List<String> fields;
    private static List<Column> list;

    public static Integer num = 0;

    /**
     * 生成model文件
     * @param resultSet
     * @param tableName
     * @throws Exception
     */
    public static void generateMySQLModelFile(ResultSet resultSet, String tableName) throws Exception{
        list = new ArrayList<>();
        fields = new ArrayList<>();
        final String suffix = ".java";
        final String path = diskPath + "/model/generator/"+replaceUnderLineAndUpperCase(tableName)+suffix;
        final String templateName = "Model.ftl";
        File mapperFile = new File(path);
        ColumnClass columnClass = null;
        List<ColumnClass> columnClassList = new ArrayList<>();
        while(resultSet.next()){
            columnClass = new ColumnClass();
            String name = resultSet.getString("COLUMN_NAME");
            list.add(new Column(name, replaceFieldName(name)));

            // 获取该表的主键
            if(resultSet.getString("COLUMN_KEY").equals("PRI")) {
                primaryKey = replaceFieldName(name);
                primaryKeyOrigin = name;
                primaryType = replaceFieldName(resultSet.getString("DATA_TYPE"));
            }
            fields.add(name);
            //获取字段名称
            columnClass.setColumnName(replaceFieldName(name));
            //获取字段类型
            columnClass.setColumnType(resultSet.getString("DATA_TYPE").toUpperCase());
            //转换字段名称，如 sys_name 变成 SysName
            columnClass.setChangeColumnName(replaceUnderLineAndUpperCase(replaceFieldName(name)));
            columnClassList.add(columnClass);
        }
        Map<String,Object> dataMap = new HashMap<>();
        dataMap.put("model_column",columnClassList);
        dataMap.put("primaryKey", primaryKey);
        dataMap.put("primaryType", primaryType.toUpperCase());
        generateFileByTemplate(templateName,mapperFile,dataMap, tableName);

    }

    /**
     * 生成model文件
     * @param resultSet
     * @param tableName
     * @throws Exception
     */
    public static void generateSQLServerModelFile(ResultSet resultSet, String tableName) throws Exception{
        list = new ArrayList<>();
        fields = new ArrayList<>();
        final String suffix = ".java";
        final String path = diskPath + "/model/generator/"+replaceUnderLineAndUpperCase(tableName)+suffix;
        final String templateName = "Model2.ftl";
        File mapperFile = new File(path);
        List<ColumnClass> columnClassList = new ArrayList<>();
        ColumnClass columnClass = null;
        boolean flag = true;
        while(resultSet.next()){
            columnClass = new ColumnClass();
            String name = resultSet.getString("name");
            list.add(new Column(name, replaceFieldName(name)));
            //获取字段名称
            columnClass.setColumnName(replaceFieldName(name));
            fields.add(name);
            //获取字段类型
            String xtype = resultSet.getString("xtype");
            columnClass.setColumnType(JdbcUtil.getType(xtype));
            //转换字段名称，如 sys_name 变成 SysName
            columnClass.setChangeColumnName(replaceUnderLineAndUpperCase(replaceFieldName(name)));
            if(flag) {
                primaryKey = replaceFieldName(name);
                primaryKeyOrigin = name;
                primaryType = JdbcUtil.getType(resultSet.getString("xtype"));
                flag = false;
            }
            columnClassList.add(columnClass);
        }
        Map<String,Object> dataMap = new HashMap<>();
        dataMap.put("model_column",columnClassList);
        dataMap.put("primaryKey", primaryKey);
        dataMap.put("primaryType", primaryType);
        generateFileByTemplate(templateName,mapperFile,dataMap, tableName);
    }

    /**
     * 生成dao层文件
     * @param tableName
     * @throws Exception
     */
    public static void generateDaoFile(String tableName) throws Exception{
        final String suffix = "Mapper.java";
        final String path = diskPath+ "/dao/generator/" + replaceUnderLineAndUpperCase(tableName) + suffix;
        final String templateName = "Dao.ftl";
        File mapperFile = new File(path);
        Map<String,Object> dataMap = new HashMap<>();
        dataMap.put("model", replaceUnderLineAndUpperCase(tableName));
        generateFileByTemplate(templateName,mapperFile,dataMap, tableName);

    }

    /**
     * 生成Mapper.xml文件
     * @param tableName
     * @throws Exception
     */
    public static void generateMapperFile(ResultSet resultSet, String tableName, boolean ifMySQL) throws Exception{
        final String suffix = "Mapper.xml";
//        final String path = System.getProperty("user.dir")+"/src/main/resources/mapping/generator/"+ replaceUnderLineAndUpperCase(tableName) + suffix;
        final String path = "C:/H/ESCodeGenerator/src/main/resources/mapping/generator/"+ replaceUnderLineAndUpperCase(tableName) + suffix;

        final String templateName = "Mapper.ftl";
        File mapperFile = new File(path);
        Map<String,Object> dataMap = new HashMap<>();
        dataMap.put("primaryKey", primaryKeyOrigin);
        dataMap.put("primaryName", primaryKey);
        dataMap.put("list", list);
        dataMap.put("fields", fields);
        generateFileByTemplate(templateName,mapperFile,dataMap, tableName);
    }

    /**
     * 根据freemarker模板文件生成对应的代码
     * @param templateName
     * @param file
     * @param dataMap
     * @param tableName
     * @throws Exception
     */
    public static void generateFileByTemplate(final String templateName,File file,Map<String,Object> dataMap, String tableName) throws Exception{
        Template template = FreeMarkerTemplateUtils.getTemplate(templateName);
        FileOutputStream fos = new FileOutputStream(file);
        dataMap.put("table_name_small",tableName);
        dataMap.put("class_name",replaceUnderLineAndUpperCase(tableName));
        dataMap.put("author",AUTHOR);
        dataMap.put("date",new Timestamp(System.currentTimeMillis()));
        dataMap.put("package_name",packageName);
        Writer out = new BufferedWriter(new OutputStreamWriter(fos, "utf-8"),10240);
        template.process(dataMap,out);
        out.close();
    }

    /**
     * 将传入的字符串进行修改，去除下划线_并将首字母大写
     * @param str
     * @return
     */
    public static String replaceUnderLineAndUpperCase(String str){
        StringBuffer sb = new StringBuffer();
        sb.append(str);
        int count = sb.indexOf("_");
        while(count!=0){
            int num = sb.indexOf("_",count);
            count = num + 1;
            if(num != -1){
                char ss = sb.charAt(count);
                char ia = ss;
                if(ss >= 97) {
                     ia = (char) (ss - 32);
                }
                sb.replace(count , count + 1,ia + "");
            }
        }
        String result = sb.toString().replaceAll("_","");
        return StringUtils.capitalize(result);
    }

    /**
     * 将传入的字符串进行修改为字段类型
     * @param str
     * @return
     */
    public static String replaceFieldName(String str) {
        StringBuffer sb = new StringBuffer();
        sb.append(str);
        int count = sb.indexOf("_");
        while(count!=0){
            int num = sb.indexOf("_",count);
            count = num + 1;
            if(num != -1){
                char ss = sb.charAt(count);
                char ia = ss;
                if(ss >= 97) {
                    ia = (char) (ss - 32);
                }
                sb.replace(count , count + 1,ia + "");
            }
        }
        char c = sb.charAt(0);
        if(c >= 65 && c <= 90) {
            c += 32;
            sb.replace(0, 1,c + "");
        }
        return sb.toString().replaceAll("_","").toLowerCase();
    }
}

2.6 模板文件

• Dao.ftl

package ${package_name}.dao.generator;

import com.xx.xx.model.generator.${class_name};
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

/**
* @author 自动生成
* @date ${date}
*/
@Mapper
public interface ${class_name}Mapper{
    List<${class_name}> list();
}

• Mapper.ftl

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.xx.xx.dao.generator.${class_name}Mapper">
    <resultMap id="result" type="com.sci99.generator.model.generator.${class_name}">
        <id column="${primaryKey}" property="${primaryName}"/>
    <#list list as item>
        <result column="${item.column}" property="${item.name}"/>
    </#list>
    </resultMap>

    <select id="list" resultType="com.xx.xx.model.generator.${class_name}" fetchSize="1000">
        select
        <#list fields as field>
        <#if field_index == 0>
            ${field}
        <#else>
            ,${field}
        </#if>
        </#list>
          from ${table_name_small} order by ${primaryKey} asc
    </select>
</mapper>

• Model.ftl

package ${package_name}.model.generator;

import com.fasterxml.jackson.annotation.JsonFormat;
import com.xx.xx.model.Data;
import java.math.BigDecimal;
import java.util.Date;

/**
* 描述：${table_name_small}模型
* @author 自动生成
* @date ${date}
*/
public class ${class_name} implements Data {

    <#if model_column?exists>
        <#list model_column as model>
    <#if (model.columnType = 'VARCHAR' || model.columnType = 'TEXT' || model.columnType = 'CHAR')>
    private String ${model.columnName};
    </#if>
    <#if model.columnType = 'TIMESTAMP' || model.columnType = 'DATETIME' || model.columnType = 'DATE'>
    @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private Date ${model.columnName};
    </#if>
    <#if model.columnType = 'DECIMAL' >
    private BigDecimal ${model.columnName};
    </#if>
    <#if model.columnType = 'INT' || model.columnType = 'BIGINT' || model.columnType = 'SMALLINT' || model.columnType = 'TINYINT' || model.columnType = 'BIT'>
    private Integer ${model.columnName};
    </#if>
        </#list>
    </#if>

<#if model_column?exists>
<#list model_column as model>
<#if (model.columnType = 'VARCHAR' || model.columnType = 'TEXT' || model.columnType = 'CHAR')>
    public String get${model.changeColumnName}() {
        return this.${model.columnName?uncap_first};
    }

    public void set${model.changeColumnName}(String ${model.columnName?uncap_first}) {
        this.${model.columnName?uncap_first} = ${model.columnName?uncap_first};
    }

</#if>
<#if model.columnType = 'TIMESTAMP' || model.columnType = 'DATETIME' || model.columnType = 'DATE'>
    public Date get${model.changeColumnName}() {
        return this.${model.columnName?uncap_first};
    }

    public void set${model.changeColumnName}(Date ${model.columnName?uncap_first}) {
        this.${model.columnName?uncap_first} = ${model.columnName?uncap_first};
    }

</#if>
<#if model.columnType = 'DECIMAL' >
    public BigDecimal get${model.changeColumnName}() {
        return this.${model.columnName?uncap_first};
    }

    public void set${model.changeColumnName}(BigDecimal ${model.columnName?uncap_first}) {
        this.${model.columnName?uncap_first} = ${model.columnName?uncap_first};
    }

</#if>
<#if model.columnType = 'INT' || model.columnType = 'BIGINT' || model.columnType = 'SMALLINT' || model.columnType = 'TINYINT' || model.columnType = 'BIT' >
    public Integer get${model.changeColumnName}() {
        return this.${model.columnName?uncap_first};
    }

    public void set${model.changeColumnName}(Integer ${model.columnName?uncap_first}) {
        this.${model.columnName?uncap_first} = ${model.columnName?uncap_first};
    }

</#if>
</#list>

<#if primaryKey?exists>
        <#if primaryType = 'VARCHAR' || primaryType = 'NVARCHAR' || primaryType = 'TEXT' || primaryType = 'CHAR' || primaryType = 'TIMESTAMP' >
   @Override
   public String getPrimaryKey() {
        return this.${primaryKey};
   }
        </#if>
        <#if primaryType = 'INT' || primaryType = 'BIGINT' || primaryType = 'SMALLINT' || primaryType = 'TINYINT' || primaryType = 'BIT' >
   @Override
   public Integer getPrimaryKey() {
        return this.${primaryKey};
   }
        </#if>
    </#if>


</#if>

}

参考链接

参考博客Java之利用Freemarker模板引擎实现代码生成器，提高效率　－阿毅