利用模板动态生成sql 模板示例

 

 

 

import org.stringtemplate.v4.ST;

import org.stringtemplate.v4.STGroup;

import org.stringtemplate.v4.STGroupFile;

 

stringtemplate，可以用来通过实体类操作的方式转化sql---从而实现sql的页面拖拽配置拼接

 

1,准备ST可识别的模板

sqlTemplate.stg

2，配置常量模板文件位置

Constants

3，代码使用

 

BuriedPointsServiceImpl

  private String getInsertSql(Class clz, Set<QueryColumn> headers){

        String tableName = getTableName4Info(clz);

 

        STGroup stg = new STGroupFile(Constants.SQL_TEMPLATE);

        ST st = stg.getInstanceOf("insertData");

        st.add("tableName", tableName);

        st.add("columns", headers);

        String sql = st.render();

 

        return sql;

    }

 

 

模板示例：

 

内置sql模板 ，从模板看filter中的字段都是and

groups--实体key对应的值

<groups:{group(遍历这个key的值)|<group>(取这个key的值)>

 

 

 

<if(!groups && !aggregators && !filters && !orders)>

<sql>

<else>

SELECT

<if(!groups && !aggregators)>

 *

<endif>

<if(groups)>

 <groups:{group|<if(keywordPrefix && keywordSuffix)><keywordPrefix><group><keywordSuffix><else><group><endif>}; separator=",">

<endif>

<if(groups && aggregators)>,<endif>

<if(aggregators)>

<if(nativeQuery)>

 <aggregators:{agg|<if(agg)><if(keywordPrefix && keywordSuffix)><keywordPrefix><agg.column><keywordSuffix><else><agg.column><endif><endif>}; separator=",">

<else>

 <aggregators:{agg|<if(agg)><agg><endif>}; separator=",">

<endif>

<endif>

FROM (<sql>) T

<if(filters)>

WHERE <filters:{filter|<if(filter)><filter><endif>}; separator=" AND ">

<endif>

<if(groups)>

<if(!nativeQuery)>

GROUP BY <groups:{group|<if(keywordPrefix && keywordSuffix)><keywordPrefix><group><keywordSuffix><else><group><endif>}; separator=",">

<endif>

<endif>

<if(orders)>

ORDER BY <orders:{order|<if(order)><order.column> <order.direction><endif>}; separator=", ">

<endif>

<endif>

 

 

ViewServiceImpl:

 

public void buildQuerySql(List<String> querySqlList, Source source, ViewExecuteParam executeParam) {

        if (null != executeParam) {

            //构造参数， 原有的被传入的替换

            STGroup stg = new STGroupFile(Constants.SQL_TEMPLATE);

            ST st = stg.getInstanceOf("querySql");

            st.add("nativeQuery", executeParam.isNativeQuery());

            st.add("groups", executeParam.getGroups());

 

            if (executeParam.isNativeQuery()) {

                st.add("aggregators", executeParam.getAggregators());

            } else {

                st.add("aggregators", executeParam.getAggregators(source.getJdbcUrl(), source.getDbVersion()));

            }

            st.add("orders", executeParam.getOrders(source.getJdbcUrl(), source.getDbVersion()));

            st.add("filters", convertFilters(executeParam.getFilters(), source));

            st.add("keywordPrefix", sqlUtils.getKeywordPrefix(source.getJdbcUrl(), source.getDbVersion()));

            st.add("keywordSuffix", sqlUtils.getKeywordSuffix(source.getJdbcUrl(), source.getDbVersion()));

 

            for (int i = 0; i < querySqlList.size(); i++) {

                st.add("sql", querySqlList.get(i));

                querySqlList.set(i, st.render());---------------------实体类型的sql，结合框架模板变成sql

            }

 

        }

    }

 

 

 

解析结果：

 

SELECT

 `update_time`

,

 sum(`data_value`) AS 'sum(data_value)'

FROM (SElECT * FROM fact_mysteel_data LIMIT 1,20) T

WHERE `data_date` in ('2019-08-12','2019-08-09')

GROUP BY `update_time`

 

 改造之后：

 

 

querySqlAndOr(nativeQuery, groups, aggregators, filters, orders, sql, keywordPrefix, keywordSuffix,filterAndFlag)

::=<<

<if(!groups && !aggregators && !filters && !orders)>

<sql>

<else>

SELECT

<if(!groups && !aggregators)>

 *

<endif>

<if(groups)>

 <groups:{group|<if(keywordPrefix && keywordSuffix)><keywordPrefix><group><keywordSuffix><else><group><endif>}; separator=",">

<endif>

<if(groups && aggregators)>,<endif>

<if(aggregators)>

<if(nativeQuery)>

 <aggregators:{agg|<if(agg)><if(keywordPrefix && keywordSuffix)><keywordPrefix><agg.column><keywordSuffix><else><agg.column><endif><endif>}; separator=",">

<else>

 <aggregators:{agg|<if(agg)><agg><endif>}; separator=",">

<endif>

<endif>

FROM (<sql>) T

<if(filters && filterAndFlag)>

WHERE <filters:{filter|<if(filter)><filter><endif>}; separator=" AND ">

<endif>

<if(filters && !filterAndFlag)>

WHERE <filters:{filter|<if(filter)><filter><endif>}; separator=" OR ">

<endif>

<if(groups)>

<if(!nativeQuery)>

GROUP BY <groups:{group|<if(keywordPrefix && keywordSuffix)><keywordPrefix><group><keywordSuffix><else><group><endif>}; separator=",">

<endif>

<endif>

<if(orders)>

ORDER BY <orders:{order|<if(order)><order.column> <order.direction><endif>}; separator=", ">

<endif>

<endif>

>>