FreeMarker模板引擎

　　前言

　　FreeMarker是一款模板引擎,即一種基於模板和要改變的資料，並用來生成輸出文字的通用工具。

　　官方文件：http://freemarker.foofun.cn

　　

　　日常開發中，我們會有這樣的需求：

　　1、在頁面上填寫資料並匯出word文件、後臺批量設定資料並匯出Excel文件（例如我們之前的部落格記錄：html頁面轉PDF、圖片操作記錄）

　　2、寫一個程式碼生成工具（例如我們之前的部落格記錄：寫一個簡單的程式碼生成器）

 

　　這種情況下我們可以用FreeMarker模板引擎來實現，本文記錄FreeMarker簡單操作。

 

　　程式碼編寫

　　maven引入依賴

<dependency>
    <groupId>org.freemarker</groupId>
    <artifactId>freemarker</artifactId>
    <version>2.3.31</version>
</dependency>

 

　　Word/Excel操作

　　Word操作

　　例如，要匯出員工檔案，格式如下：

 

 

 　　我們先按照格式要求，寫好word文件，然後另存為xml，得到模板檔案

 

 

 　　用idea開啟word.xml，格式化一下，然後根據FreeMarker的語法表示式讀取、設定值

 

 

 　　封裝一個方法

    //Word文件操作
    private static void createWord(){
        System.out.println("開始Word文件操作...");

        //指定TemplateLoading模板根路徑
        String rootPath = "E:\\Java\\test\\";
        //模板檔案
        String templatePath = "word.xml";
        //最終輸出檔案路徑
        String outFilePath = rootPath+"word_by_freemarker.docx";
        //資料
        Map<String, Object> data = new HashMap<>();
        data.put("company","某某公司");
        data.put("number","0001");
        data.put("name","huanzi-qch");
        data.put("phone","15600000000");
        data.put("department","軟體開發部");
        data.put("post","開發工程師");
        ArrayList<Map<String, String>> works = new ArrayList<>();
        Map<String, String> work1 = new HashMap<>();
        work1.put("company","某某單位1");
        work1.put("time","2018-01 - 2019-01");
        work1.put("department","研發1部");
        work1.put("post","開發工程師");
        works.add(work1);
        Map<String, String> work2 = new HashMap<>();
        work2.put("company","某某單位2");
        work2.put("time","2019-01 - 2020-01");
        work2.put("department","研發2部");
        work2.put("post","開發工程師");
        works.add(work2);
        data.put("works",works);

        try (BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outFilePath), StandardCharsets.UTF_8));){
            //初始化，指定版本與pom檔案相同
            Configuration configuration = new Configuration(new Version("2.3.31"));
            configuration.setDefaultEncoding(StandardCharsets.UTF_8.toString());
            configuration.setDirectoryForTemplateLoading(new File(rootPath));
            Template template = configuration.getTemplate(templatePath);

            //處理（資料）
            template.process(data, writer);
        } catch (IOException | TemplateException e) {
            e.printStackTrace();
        }

        System.out.println("Word文件操作結束！");
    }

 

　　效果

 

 

 

 

 

　　Excel操作

　　Excel操作同理，也是先建立一個例子，另存為xml格式，再根據FreeMarker的語法表示式設定值

　　按照欄位名，建Excel例子時直接把取值程式碼寫進去，轉成xml後就可能省一點時間

 

 

 

 

 

 

 　　同樣，idea開啟後格式化一下，遍歷設定值

 

 

 　　封裝一個方法

    //Excel文件操作
    private static void createExcel(){
        System.out.println("開始Excel文件操作...");

        //指定TemplateLoading模板根路徑
        String rootPath = "E:\\Java\\test\\";
        //模板檔案
        String templatePath = "excel.xml";
        //最終輸出檔案路徑
        String outFilePath = rootPath+"excel_by_freemarker.xlsx";
        //資料
        Map<String, Object> data = new HashMap<>();
        ArrayList<Map<String, String>> persons = new ArrayList<>();
        Map<String, String> person1 = new HashMap<>();
        person1.put("name","huanzi-qch1");
        person1.put("phone","15600000000");
        person1.put("company","某某單位1");
        person1.put("time","2018-01 - 2019-01");
        person1.put("department","研發1部");
        person1.put("post","開發工程師");
        persons.add(person1);
        Map<String, String> person2 = new HashMap<>();
        person2.put("name","huanzi-qch2");
        person2.put("phone","15600000000");
        person2.put("company","某某單位2");
        person2.put("time","2019-01 - 2020-01");
        person2.put("department","研發2部");
        person2.put("post","開發工程師");
        persons.add(person2);
        data.put("persons",persons);

        try (BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outFilePath), StandardCharsets.UTF_8));){
            //初始化，指定版本與pom檔案相同
            Configuration configuration = new Configuration(new Version("2.3.31"));
            configuration.setDefaultEncoding(StandardCharsets.UTF_8.toString());
            configuration.setDirectoryForTemplateLoading(new File(rootPath));
            Template template = configuration.getTemplate(templatePath);

            //處理（資料）
            template.process(data, writer);
        } catch (IOException | TemplateException e) {
            e.printStackTrace();
        }

        System.out.println("Excel文件操作結束！");
    }

 

　　效果

 

 

 

 

 

 

　　程式碼生成器

　　程式碼生成器，先把我們之前寫好的JDBC連線資料庫工具類、字串處理工具類、表結構資訊實體類以及獲取表結構資訊的方法先拿過來

    /**
     * 程式自動設定
     */
    private static String tableName;//表名
    private static String tableComment;//表註釋

    /**
     * 資料連線相關，需要手動設定
     */
    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 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 static 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 static 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;
    }

　　從原來的程式碼生成器那裡拿一個entity.tlf模板，小改動一下（主要是迴圈、if判斷那裡不一樣）

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}")
@Data
public class ${entity} implements Serializable {
  <#list tableInfos as tableInfo>
    <#if tableInfo.columnKey == "PRI">@Id</#if>
    <#if tableInfo.extra == "auto_increment">@GeneratedValue(strategy= GenerationType.IDENTITY)</#if>
    private ${tableInfo.dataType} ${tableInfo.columnName};//${tableInfo.columnComment}
  </#list>
}

　　封裝一個方法

    //程式碼生成
    private static void autoGenerator(String tName){
        System.out.println("開始程式碼生成操作...");
        tableName = tName;

        //指定TemplateLoading模板根路徑
        String rootPath = "E:\\Java\\test\\";
        //模板檔案
        String templatePath = "entity.tlf";
        //最終輸出檔案路徑
        String outFilePath = rootPath+"entity.java";
        //資料
        Map<String, Object> data = new HashMap<>();
        //駝峰標識對映後的表名
        String captureName = StringUtil.captureName(StringUtil.camelCaseName(tableName));
        //獲取表資訊，並進行處理
        List<TableInfo> tableInfoList = getTableInfo();
        ArrayList<Map<String, String>> tableInfos = new ArrayList<>();
        for (TableInfo info : tableInfoList) {
            HashMap<String, String> hashMap = new HashMap<>();
            hashMap.put("columnName", StringUtil.camelCaseName(info.getColumnName()));
            hashMap.put("dataType", StringUtil.typeMapping(info.getDataType()));
            hashMap.put("columnComment", info.getColumnComment());
            hashMap.put("columnKey", info.getColumnKey());
            hashMap.put("extra", info.getExtra());
            tableInfos.add(hashMap);
        }
        data.put("entityToLowerCase",captureName.toLowerCase());
        data.put("tableComment",tableComment);
        data.put("author","作者：Auto Generator By 'huanzi-qch'");
        data.put("date","生成日期："+new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
        data.put("tableName",tableName);
        data.put("entity",captureName);
        data.put("tableInfos",tableInfos);

        try (BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outFilePath), StandardCharsets.UTF_8));){
            //初始化，指定版本與pom檔案相同
            Configuration configuration = new Configuration(new Version("2.3.31"));
            configuration.setDefaultEncoding(StandardCharsets.UTF_8.toString());
            configuration.setDirectoryForTemplateLoading(new File(rootPath));
            Template template = configuration.getTemplate(templatePath);

            //處理（資料）
            template.process(data, writer);
        } catch (IOException | TemplateException e) {
            e.printStackTrace();
        }

        System.out.println("程式碼生成操作結束！");
    }

 

　　效果

 

 

 

 

 

 

　　完整程式碼

import freemarker.template.Configuration;
import freemarker.template.Template;
import freemarker.template.TemplateException;
import freemarker.template.Version;

import java.io.*;
import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Date;

public class Test {

    //Word文件操作
    private static void createWord(){
        System.out.println("開始Word文件操作...");

        //指定TemplateLoading模板根路徑
        String rootPath = "E:\\Java\\test\\";
        //模板檔案
        String templatePath = "word.xml";
        //最終輸出檔案路徑
        String outFilePath = rootPath+"word_by_freemarker.docx";
        //資料
        Map<String, Object> data = new HashMap<>();
        data.put("company","某某公司");
        data.put("number","0001");
        data.put("name","huanzi-qch");
        data.put("phone","15600000000");
        data.put("department","軟體開發部");
        data.put("post","開發工程師");
        ArrayList<Map<String, String>> works = new ArrayList<>();
        Map<String, String> work1 = new HashMap<>();
        work1.put("company","某某單位1");
        work1.put("time","2018-01 - 2019-01");
        work1.put("department","研發1部");
        work1.put("post","開發工程師");
        works.add(work1);
        Map<String, String> work2 = new HashMap<>();
        work2.put("company","某某單位2");
        work2.put("time","2019-01 - 2020-01");
        work2.put("department","研發2部");
        work2.put("post","開發工程師");
        works.add(work2);
        data.put("works",works);

        try (BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outFilePath), StandardCharsets.UTF_8));){
            //初始化，指定版本與pom檔案相同
            Configuration configuration = new Configuration(new Version("2.3.31"));
            configuration.setDefaultEncoding(StandardCharsets.UTF_8.toString());
            configuration.setDirectoryForTemplateLoading(new File(rootPath));
            Template template = configuration.getTemplate(templatePath);

            //處理（資料）
            template.process(data, writer);
        } catch (IOException | TemplateException e) {
            e.printStackTrace();
        }

        System.out.println("Word文件操作結束！");
    }

    //Excel文件操作
    private static void createExcel(){
        System.out.println("開始Excel文件操作...");

        //指定TemplateLoading模板根路徑
        String rootPath = "E:\\Java\\test\\";
        //模板檔案
        String templatePath = "excel.xml";
        //最終輸出檔案路徑
        String outFilePath = rootPath+"excel_by_freemarker.xlsx";
        //資料
        Map<String, Object> data = new HashMap<>();
        ArrayList<Map<String, String>> persons = new ArrayList<>();
        Map<String, String> person1 = new HashMap<>();
        person1.put("name","huanzi-qch1");
        person1.put("phone","15600000000");
        person1.put("company","某某單位1");
        person1.put("time","2018-01 - 2019-01");
        person1.put("department","研發1部");
        person1.put("post","開發工程師");
        persons.add(person1);
        Map<String, String> person2 = new HashMap<>();
        person2.put("name","huanzi-qch2");
        person2.put("phone","15600000000");
        person2.put("company","某某單位2");
        person2.put("time","2019-01 - 2020-01");
        person2.put("department","研發2部");
        person2.put("post","開發工程師");
        persons.add(person2);
        data.put("persons",persons);

        try (BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outFilePath), StandardCharsets.UTF_8));){
            //初始化，指定版本與pom檔案相同
            Configuration configuration = new Configuration(new Version("2.3.31"));
            configuration.setDefaultEncoding(StandardCharsets.UTF_8.toString());
            configuration.setDirectoryForTemplateLoading(new File(rootPath));
            Template template = configuration.getTemplate(templatePath);

            //處理（資料）
            template.process(data, writer);
        } catch (IOException | TemplateException e) {
            e.printStackTrace();
        }

        System.out.println("Excel文件操作結束！");
    }

    //程式碼生成
    private static void autoGenerator(String tName){
        System.out.println("開始程式碼生成操作...");
        tableName = tName;

        //指定TemplateLoading模板根路徑
        String rootPath = "E:\\Java\\test\\";
        //模板檔案
        String templatePath = "entity.tlf";
        //最終輸出檔案路徑
        String outFilePath = rootPath+"entity.java";
        //資料
        Map<String, Object> data = new HashMap<>();
        //駝峰標識對映後的表名
        String captureName = StringUtil.captureName(StringUtil.camelCaseName(tableName));
        //獲取表資訊，並進行處理
        List<TableInfo> tableInfoList = getTableInfo();
        ArrayList<Map<String, String>> tableInfos = new ArrayList<>();
        for (TableInfo info : tableInfoList) {
            HashMap<String, String> hashMap = new HashMap<>();
            hashMap.put("columnName", StringUtil.camelCaseName(info.getColumnName()));
            hashMap.put("dataType", StringUtil.typeMapping(info.getDataType()));
            hashMap.put("columnComment", info.getColumnComment());
            hashMap.put("columnKey", info.getColumnKey());
            hashMap.put("extra", info.getExtra());
            tableInfos.add(hashMap);
        }
        data.put("entityToLowerCase",captureName.toLowerCase());
        data.put("tableComment",tableComment);
        data.put("author","作者：Auto Generator By 'huanzi-qch'");
        data.put("date","生成日期："+new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
        data.put("tableName",tableName);
        data.put("entity",captureName);
        data.put("tableInfos",tableInfos);

        try (BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(outFilePath), StandardCharsets.UTF_8));){
            //初始化，指定版本與pom檔案相同
            Configuration configuration = new Configuration(new Version("2.3.31"));
            configuration.setDefaultEncoding(StandardCharsets.UTF_8.toString());
            configuration.setDirectoryForTemplateLoading(new File(rootPath));
            Template template = configuration.getTemplate(templatePath);

            //處理（資料）
            template.process(data, writer);
        } catch (IOException | TemplateException e) {
            e.printStackTrace();
        }

        System.out.println("程式碼生成操作結束！");
    }

    /**
     * 程式自動設定
     */
    private static String tableName;//表名
    private static String tableComment;//表註釋

    /**
     * 資料連線相關，需要手動設定
     */
    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 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 static 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 static 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;
    }

    public static void main(String[] args) {
//        createWord();
//        createExcel();
//        autoGenerator("tb_user");
    }
}

 

　　後記

　　通過FreeMarker，按照固定格式，快速生成Word、Excel文件，或者生成程式碼，簡單高效。

　　生成文件，資料來源可能是直接讀庫獲取，也有可能是要使用者在頁面上填寫，再傳入後臺，這時候就可以將我們的模板檔案，另存為html格式，小調整之後就可以展示給使用者，最大程度保證了使用者看到的文件頁面跟生成、匯出的文件格式是一致的。