Freemaker introduction and demo of generating dynamic select sql

1. What is freemaker and what is its main function?
freemarker is a template engine, which is a general tool based on templates and data to be changed and used to generate output text (HTML web pages, e-mail, configuration files, source code, etc.). This is the official explanation. My personal understanding is that freemaker is a template engine tool. We first define the template text in a specific language and reserve the parameter position, and then dynamically pass in the parameters. In this way, freemaker can dynamically return different text data according to the input parameters. Therefore, its function is to dynamically generate text according to templates and data.

2. Freemaker function composition
Freemaker is mainly composed of template model and data model. Template model is generally a text file written in accordance with FreeMarker Template Language, or dynamically generate the corresponding text string in the program. Although it is more flexible to generate text strings directly, it is not intuitive to write text files. Data model generally refers to parameter data conforming to freemaker specification. When freemaker runs, it will replace the corresponding parameter data into the placeholder of the template model file.

Therefore, the core of freemaker is template model and data model. Because the template model is a text (or string), it has corresponding language and syntax rules, such as the use of if, list, include, etc. Try to understand the language rules of freemaker before using it for the first time, and then check it with you. Data model refers to the set of parameters that can be placed dynamically in the template model. These data sets also have some broad requirements and common methods. When they are used for the first time, they can also be summarized first, and then checked with use.

3. Dynamic select sql generate demo
3.1) add dependency first


3.2) Secondly, edit the template model file (the file name is IndicatorProdSql.sql)

select ${express}
from ${modelName}
<#if joinList??>
    <#list joinList as joinObj>
        ${joinObj.type} ${joinObj.modelName} on ${joinObj.criteria}
<#if whereList??>
    where 1=1
    <#list whereList as whereObj>
        ${whereObj.type} ${whereObj.criteria}
<#if groupList??>
    group by
    <#list groupList as criteria>
        ${criteria}<#if criteria_has_next>, </#if>
<#if orderList??>
    order by
    <#list orderList as criteria>
        ${criteria}<#if criteria_has_next>, </#if>

The model file does not limit the file name and type, as long as the internal text conforms to the freemaker language rules.
3.3) basic code

/************************************* Data model************************************/
public class FreemarkerSqlBean {
    public String express;
    public String modelName;
    public List<FreemarkerSqlJoin> joinList;
    public List<FreemarkerSqlWhere> whereList;
    public List<String> groupList;
    public List<String> orderList;
public class FreemarkerSqlJoin {
    public String type;
    public String modelName;
    public String criteria;
public class FreemarkerSqlWhere {
    public String type;
    public String criteria;

/************************************* Basic code************************************/

public class FreemarkerDemo{
    public final static int[] FREEMARKERVERSION = new int[]{2,3,31};
    public final static String FREEMARKERSQLFILE = "IndicatorProdSql.sql";
    public String getSql(FreemarkerSqlBean bean) throws Exception {
        // Step 1: create a Configuration object and directly create a new object. The parameter of the constructor is the version number of freemaker.
        Configuration configuration = new Configuration(new Version(Constant.FREEMARKERVERSION[0], Constant.FREEMARKERVERSION[1], Constant.FREEMARKERVERSION[2]));
        // Step 2: set the path where the template file is located.
        String rootPath = Thread.currentThread().getContextClassLoader().getResource("").getPath();
        configuration.setDirectoryForTemplateLoading(new File(rootPath));
        // Step 3: set the character set used in the template file. Usually utf-8
        // Step 4: load a template and create a template object.
        Template template = configuration.getTemplate(Constant.FREEMARKERSQLFILE);
        // Step 5: create a dataset used by the template, which can be pojo or map. Usually map. This is entered by the method

        // Step 6: create a Writer object. Generally, create a FileWriter object and specify the generated file name. You can also put the generated text into memory
        //Writer out = new FileWriter(new File("E:\\wyt01web\\src\\main\\resources\\table_ddl_create.sql"));
        String sql = null;
        try (Writer out = new StringWriter();) {
            // Step 7: call the process method of the template object to output the file.
            template.process(bean, out);
            sql = ((StringWriter) out).getBuffer().toString();
        } catch (Exception e) {
            logger.error("Request information{},Exception information:",JSONObject.toJSONString(bean),e);
        return sql;

There are several points to note in the above code, which can be used in more than one demo:
3.3.1) step 2: obtain the resource path
      String rootPath = Thread.currentThread().getContextClassLoader().getResource("").getPath();
3.3.2) the sixth step is to use try resource without managing the closure of the flow
       try (Writer out = new StringWriter();) {
        } catch (Exception e) {
3.3.3) use different Write implementation classes to output files or strings
Output file: Writer out = new FileWriter(new File("E:\wyt01web\src\main\resources\table_ddl_create.sql");
Output string: Writer out = new StringWriter()
3.3.4) line break and space adjustment
When directly outputting a string, you may encounter too many line breaks or spaces in the string. At this time, you can remove the line breaks and excess spaces in the following ways
     sql.replaceAll("\\r\\n"," ").replaceAll("\\s+"," ")
3.3.5) log print placeholder and print complete error message
    logger.error("request information {}, exception information:", JSONObject.toJSONString(bean),e);
That is, use curly braces {} for placeholders, and then append parameters. If there are multiple parameters, separate them with commas, and multiple placeholders can be inserted. In addition, when printing exception information, you do not need to add placeholders, but directly add e to output complete exception information at the end of this line's log.

4. Extended use
For example, when you add a mysql template or godb template, you can use the syntax of this template or other templates to edit the text directly. The former is generally recommended because it is more intuitive and the operation is relatively simple and not easy to make mistakes. Of course, if the dynamic requirements are relatively high and the text requirements are not too complex, the latter can be used.

Reference link:      

Added by Jami on Wed, 09 Feb 2022 22:29:25 +0200