将查询sql中的中文字段和中文表名转成拼音

背景：数据装载时，有时候需要别人的数据表中读取数据，插入到自己的表中。如果别人的数据表是中文的，需要将表名称和表字段转成拼音首字母，此时可以直接用PinyinHelper.getShortPinyin()方法直接转换。但如果语句中有case when、where表达式时，是不能直接转出拼音首字母的。如：

case 学生表.学校 when 1 then '第一中学' when 1 then '第二中学'  end as school

where 学生表.姓名 not like '%测试%'

类似这些，需要特殊处理的。

依赖

<dependency>
    <groupId>com.github.stuxuhai</groupId>
    <artifactId>jpinyin</artifactId>
    <version>1.1.8</version>
</dependency>

<dependency>
    <artifactId>jsqlparser</artifactId>
    <groupId>com.github.jsqlparser</groupId>
    <version>4.4</version>
</dependency>

代码

import com.alibaba.druid.sql.SQLUtils;
import com.github.stuxuhai.jpinyin.PinyinException;
import com.github.stuxuhai.jpinyin.PinyinHelper;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.*;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.relational.*;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.select.*;
import org.apache.commons.collections.CollectionUtils;
import java.util.*;

/**
 * Description:将中文表名和列名转成拼音
 *
 * @Date: 2024/05/07
 */
public class Test {
    public static void main(String[] args) {

        String sql = "SELECT 字段1, 字段2 FROM 表1 WHERE 字段3 IN (SELECT 字段4 FROM 表2 WHERE 条件 = '1') "
                + "UNION ALL "
                + "SELECT 字段5, 字段6 FROM 表3 WHERE 字段7 = '特定值'";
        String transformedSql = analysisSql(sql);
        String formattedSql = SQLUtils.formatMySql(transformedSql);
        System.out.println(formattedSql);
    }

    /**
     * 解析sql
     *
     * @param sql 输入的sql语句
     * @return
     * @throws JSQLParserException
     */
    private static String analysisSql(String sql) {
        Select select = null;
        try {
            select = (Select) CCJSqlParserUtil.parse(sql);
        } catch (JSQLParserException e) {
            throw new RuntimeException(e);
        }
        SelectBody selectBody = select.getSelectBody();
        if (selectBody instanceof SetOperationList) {
            SetOperationList operationList = (SetOperationList) selectBody;
            List<SelectBody> modifiedSelectBodies = new ArrayList<>();
            for (SelectBody plainSelect : operationList.getSelects()) {
                // 递归分析并修改，同时收集修改后的SelectBody
                String modifiedSql = analysisSql(plainSelect.toString());
                try {
                    modifiedSelectBodies.add(((Select)CCJSqlParserUtil.parse(modifiedSql)).getSelectBody());
                } catch (JSQLParserException e) {
                    throw new RuntimeException(e);
                }
            }
            // 替换原始的SelectBody列表为修改后的
            operationList.setSelects(modifiedSelectBodies);
        } else if (selectBody instanceof PlainSelect) {
            analysisSelectBody((PlainSelect) selectBody);
        }
        // 将最终修改后的Select对象转换为SQL字符串
        return select.toString();
    }

    /**
     * 解析查询语句
     *
     * @param plainSelect
     * @throws JSQLParserException
     */
    private static void analysisSelectBody(PlainSelect plainSelect) {
        // 转换select里的字段
        transformSelectItems(plainSelect);
        // 转换from的中文表名（可能含有子查询）
        transformFromItem(plainSelect.getFromItem());
        // 转换join里的中文表名和on表达式里的中文字段
        transformJoinItem(plainSelect.getJoins());
        // 转换where表达式里的中文字段字段
        transformWhereField(plainSelect.getWhere());
        // 转换group by里的中文字段
        transformGroupByItem(plainSelect.getGroupBy());
        // 转换having里的中文字段
        transformHavingField(plainSelect.getHaving());
    }

    private static void transformGroupByItem(GroupByElement groupByElement) {
        if (groupByElement == null) {
            return;
        }
        ExpressionList groupByExpressionList = groupByElement.getGroupByExpressionList();
        for (Expression expression : groupByExpressionList.getExpressions()) {
            if (expression instanceof Column) {
                Column column = (Column) expression;
                String pinyinColumnName = null;
                try {
                    pinyinColumnName = PinyinHelper.getShortPinyin(column.getColumnName());
                } catch (PinyinException e) {
                    throw new RuntimeException(e);
                }
                column.setColumnName(pinyinColumnName);
            } else {
                throw new RuntimeException("不支持的表达式类型：" + expression.toString());
            }
        }
    }

    private static void transformHavingField(Expression havingExpression) {
        if (havingExpression == null) {
            return;
        }
        if (havingExpression instanceof BinaryExpression) {
            BinaryExpression andExpression = (BinaryExpression) havingExpression;
            Expression leftExpression = andExpression.getLeftExpression();
            if (leftExpression instanceof Column) {
                Column column = (Column) leftExpression;
                String pinyinColumnName = null;
                try {
                    pinyinColumnName = PinyinHelper.getShortPinyin(column.getColumnName());
                } catch (PinyinException e) {
                    throw new RuntimeException(e);
                }
                column.setColumnName(pinyinColumnName);
                andExpression.setLeftExpression(column);
            } else if (leftExpression instanceof Function) {
                Function function = (Function) leftExpression;
                function.getParameters().getExpressions().forEach(expression -> {
                    if (expression instanceof Column) {
                        Column column = (Column) expression;
                        String pinyinColumnName = null;
                        try {
                            pinyinColumnName = PinyinHelper.getShortPinyin(column.getColumnName());
                        } catch (PinyinException e) {
                            throw new RuntimeException(e);
                        }
                        column.setColumnName(pinyinColumnName);
                        function.getParameters().getExpressions().set(function.getParameters().getExpressions().indexOf(expression), column);
                    } else {
                        throw new RuntimeException("不支持的表达式类型:" + expression.toString());
                    }
                });
            } else {
                throw new RuntimeException("不支持的表达式类型:" + leftExpression.toString());
            }
        }
    }

    /**
     * 将where语句中的中文列转成拼音
     *
     * @param whereExpression where表达式
     */
    public static void transformWhereField(Expression whereExpression) {
        if (whereExpression == null) {
            return;
        }
        if (whereExpression instanceof BinaryExpression) {
            //转成二进制基本表达式，遍历左、右表达式
            BinaryExpression andExpression = (BinaryExpression) whereExpression;
            if (andExpression.getLeftExpression() instanceof Column) {
                //说明已经是最终的表达式了，例如：学校 = '中学'，只需将字段名转成拼音即可
                Column column = (Column) andExpression.getLeftExpression();
                try {
                    String pinyinColumnName = PinyinHelper.getShortPinyin(column.getColumnName());
                    column.setColumnName(pinyinColumnName);
                } catch (PinyinException e) {
                    throw new RuntimeException(e);
                }
            } else if (andExpression.getLeftExpression() instanceof BinaryExpression) {
                //还不是最小的表达式，继续递归
                transformWhereField(andExpression.getLeftExpression());
            } else {
                throw new RuntimeException("暂不支持该表达式类型: " + whereExpression);
            }
            //处理右表达式
            if (andExpression.getRightExpression() instanceof Column) {
                Column column = (Column) andExpression.getLeftExpression();
                try {
                    String pinyinColumnName = PinyinHelper.getShortPinyin(column.getColumnName());
                    column.setColumnName(pinyinColumnName);
                } catch (PinyinException e) {
                    throw new RuntimeException(e);
                }
            } else if (andExpression.getRightExpression() instanceof BinaryExpression) {
                transformWhereField(andExpression.getRightExpression());
            } else if (andExpression.getRightExpression() instanceof StringValue
                    || andExpression.getRightExpression() instanceof LongValue
                    || andExpression.getRightExpression() instanceof DateValue
                    || andExpression.getRightExpression() instanceof DoubleValue) {
                //如果是字符串，如 学校 = ‘中学’ 中的右表达式，不需要处理
            } else if (andExpression.getRightExpression() instanceof Parenthesis) {
                Parenthesis parenthesis = (Parenthesis) andExpression.getRightExpression();
                transformWhereField(parenthesis.getExpression());
            } else {
                throw new RuntimeException("暂不支持该表达式类型: " + whereExpression);
            }
        } else if (whereExpression instanceof InExpression) {
            InExpression inExpression = (InExpression) whereExpression;
            if (inExpression.getLeftExpression() instanceof Column) {
                Column column = (Column) inExpression.getLeftExpression();
                String pinyinColumnName = null;
                try {
                    pinyinColumnName = PinyinHelper.getShortPinyin(column.getColumnName());
                } catch (PinyinException e) {
                    throw new RuntimeException(e);
                }
                column.setColumnName(pinyinColumnName);
                inExpression.setLeftExpression(column);
            } else {
                throw new RuntimeException("暂不支持该表达式类型: " + inExpression);
            }
            if (inExpression.getRightExpression() instanceof SubSelect) {
                SubSelect subSelect = (SubSelect) inExpression.getRightExpression();
                analysisSelectBody((PlainSelect) subSelect.getSelectBody());
            } else {
                throw new RuntimeException("暂不支持该表达式类型: " + inExpression);
            }

        } else if (whereExpression instanceof Between) {
            Between between = (Between) whereExpression;
            if (between.getLeftExpression() instanceof Column) {
                Column column = (Column) between.getLeftExpression();
                String pinyinColumnName = null;
                try {
                    pinyinColumnName = PinyinHelper.getShortPinyin(column.getColumnName());
                } catch (PinyinException e) {
                    throw new RuntimeException(e);
                }
                column.setColumnName(pinyinColumnName);
                between.setLeftExpression(column);
            }
        } else {
            throw new RuntimeException("暂不支持该where表达式类型: " + whereExpression);
        }
    }

    /**
     * 将from中join的的中文表名和on表达式的中文列表段转成拼音
     *
     * @param joinList
     * @return
     */
    private static void transformJoinItem(List<Join> joinList) {
        if (joinList != null) {
            for (int i = 0; i < joinList.size(); i++) {
                Join join = joinList.get(i);
                FromItem rightItem = join.getRightItem();
                //处理join的中文表名
                if (rightItem instanceof Table) {
                    Table table = (Table) rightItem;
                    String pinyinTableName = null;
                    try {
                        pinyinTableName = PinyinHelper.getShortPinyin(table.getName());
                    } catch (PinyinException e) {
                        throw new RuntimeException(e);
                    }
                    table.setName(pinyinTableName);
                    join.setRightItem(table);
                } else {
                    throw new RuntimeException("暂不支持该join表达式类型: " + rightItem);
                }
                //遍历on表达式，处理中文列名
                join.getOnExpressions().forEach(expression -> {
                    transformLeftExpression(expression);
                });
            }
        }
    }

    /**
     * 将from中on表达式里的中文列名转成拼音
     *
     * @param expression
     */
    private static void transformLeftExpression(Expression expression) {
        //如果等于表达式，说明是最细的了，直接转成拼音即可
        if (expression instanceof EqualsTo) {
            EqualsTo equalsTo = (EqualsTo) expression;
            //将左右列表转成拼音
            Expression leftExpression = equalsTo.getLeftExpression();
            if (leftExpression instanceof  Column) {
                Column column = (Column) leftExpression;
                String columnName = column.getColumnName();
                String pinyinColumnName = null;
                try {
                    pinyinColumnName = PinyinHelper.getShortPinyin(columnName);
                } catch (PinyinException e) {
                    throw new RuntimeException(e);
                }
                column.setColumnName(pinyinColumnName);
            }
            Expression rigthExpression = equalsTo.getRightExpression();
            if (rigthExpression instanceof  Column) {
                Column column = (Column) rigthExpression;
                String columnName = column.getColumnName();
                String pinyinColumnName = null;
                try {
                    pinyinColumnName = PinyinHelper.getShortPinyin(columnName);
                } catch (PinyinException e) {
                    throw new RuntimeException(e);
                }
                column.setColumnName(pinyinColumnName);
            }
        } else if (expression instanceof AndExpression) {
            //如果是and表达式，继续递归
            AndExpression andExpression = (AndExpression) expression;
            transformLeftExpression(andExpression.getLeftExpression());
            transformLeftExpression(andExpression.getRightExpression());
        } else if (expression instanceof IsNullExpression) {
            //如果是is null表达式，只需处理左表达式即可
            IsNullExpression isNullExpression = (IsNullExpression) expression;
            Expression leftExpression = isNullExpression.getLeftExpression();
            if (leftExpression instanceof Column) {
                Column column = (Column) leftExpression;
                String pinyinColumnName = null;
                try {
                    pinyinColumnName = PinyinHelper.getShortPinyin(column.getColumnName());
                } catch (PinyinException e) {
                    throw new RuntimeException(e);
                }
                column.setColumnName(pinyinColumnName);
            }
        }
    }

    /**
     * 将from里的中文字段转成拼音
     *
     * @param fromItem
     * @return
     * @throws JSQLParserException 解析异常
     */
    private static void transformFromItem(FromItem fromItem) {
        // 判断fromItem属于哪种类型，如果是subSelect类型就是子查询
        if (fromItem instanceof SubSelect) {
            SelectBody selectBody = ((SubSelect) fromItem).getSelectBody();
            analysisSql(selectBody.toString());
        }
        try {
            Table table = (Table) fromItem;
            String tableName = PinyinHelper.getShortPinyin(table.getName());
            table.setName(tableName);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 将select中的列名转成拼音
     *
     * @param plainSelect
     */
    private static void transformSelectItems(PlainSelect plainSelect) {
        List<SelectItem> selectItems = plainSelect.getSelectItems();
        if (!CollectionUtils.isEmpty(selectItems)) {
            for (SelectItem selectItem : selectItems) {
                try {
                    if (selectItem instanceof SelectExpressionItem) {
                        SelectExpressionItem selectExpressionItem = (SelectExpressionItem) selectItem;
                        Expression expression = selectExpressionItem.getExpression();
                        if (expression instanceof Column) {
                            //表达式是列名，直接转
                            Column column = ((Column) expression);
                            String pinyinColumnName = PinyinHelper.getShortPinyin(column.getColumnName());
                            column.setColumnName(pinyinColumnName);
                        } else if (expression instanceof CaseExpression) {
                            //case表达式，只处理列名
                            CaseExpression caseExpression = (CaseExpression) expression;
                            Expression switchExpression = caseExpression.getSwitchExpression();
                            if (switchExpression instanceof Column) {
                                Column column = (Column) switchExpression;
                                String pinyinColumnName = PinyinHelper.getShortPinyin(column.getColumnName());
                                column.setColumnName(pinyinColumnName);
                            }
                        } else if (expression instanceof LongValue || expression instanceof StringValue || expression instanceof DateValue || expression instanceof DoubleValue) {
                            //如果是常量，忽略
                        } else if (expression instanceof TimeKeyExpression) {
                            throw new RuntimeException("暂不支持此类型的表达式：" + expression.toString());
                        } else if (expression instanceof NullValue) {
                            //如果是null，如null as updateTime，忽略
                        } else if (expression instanceof AnalyticExpression) {
                            AnalyticExpression analyticExpression = (AnalyticExpression) expression;
                            //处理avg里面的列名
                            Expression avgExpression = analyticExpression.getExpression();
                            if (avgExpression instanceof Column) {
                                //表达式是列名，直接转
                                Column column = ((Column) avgExpression);
                                String pinyinColumnName = PinyinHelper.getShortPinyin(column.getColumnName());
                                column.setColumnName(pinyinColumnName);
                            } else {
                                throw new RuntimeException("未知类型的表达式：" + avgExpression.toString());
                            }
                            //处理over里面的列名
                            ExpressionList partitionExpressionList = analyticExpression.getPartitionExpressionList();
                            if (partitionExpressionList != null) {
                                List<Expression> expressions = partitionExpressionList.getExpressions();
                                for (Expression partitionExpression : expressions) {
                                    if (partitionExpression instanceof Column) {
                                        //表达式是列名，直接转
                                        Column column = ((Column) partitionExpression);
                                        String pinyinColumnName = PinyinHelper.getShortPinyin(column.getColumnName());
                                        column.setColumnName(pinyinColumnName);
                                    }else {
                                        throw new RuntimeException("未知类型的表达式：" + selectItem.toString());
                                    }
                                }
                            }
                        } else {
                            throw new RuntimeException("未知类型的表达式：" + expression.toString());
                        }
                    } else if (selectItem instanceof AllTableColumns) {
                        //table.*类型的表达式，不处理
                    } else if (selectItem.toString().equals("*")) {
                        //*类型的表达式，不处理
                    } else if (selectItem instanceof NullValue) {
                        //如果是null，如null as updateTime，忽略
                    } else {
                        throw new RuntimeException("未知类型的表达式：" + selectItem.toString());
                    }
                } catch (Exception e) {
                    throw new RuntimeException(e);
                }
            }
        }
    }
}

 测试用例：

String sql = "SELECT 字段1, 字段2 FROM 表1 WHERE 字段3 IN (SELECT 字段4 FROM 表2 WHERE 条件 = '1') "
        + "UNION ALL "
        + "SELECT 字段5, 字段6 FROM 表3 WHERE 字段7 = '特定值'";

String sql1 = "SELECT * \n" +
        "FROM 商品表 \n" +
        "WHERE 类别 = '电子产品' \n" +
        "AND 价格 > 2000 \n" +
        "AND (品牌 = '华为' OR 品牌 = '小米') \n" +
        "AND (上市日期 BETWEEN '2020-01-01' AND '2022-12-31')";

String sql2 = "SELECT 中文列1, 中文列2 FROM 中文表1 LEFT JOIN 中文表2 ON 中文列1 = 中文列2";

String sql3 ="SELECT 列1, 列2 FROM 表3 GROUP BY 列1 HAVING sum(列3) > 100";

String sql4 ="SELECT 中文列 FROM 中文表1 INTERSECT SELECT 中文列 FROM 中文表2";

String sql5 ="SELECT 中文列, AVG(中文列2) OVER (PARTITION BY 中文列1) FROM 中文表";

对应的结果：

SELECT zd1, zd2
FROM b1
WHERE zd3 IN (
    SELECT zd4
    FROM b2
    WHERE tj = '1'
)
UNION ALL
SELECT zd5, zd6
FROM b3
WHERE zd7 = '特定值';


SELECT *
FROM spb
WHERE lb = '电子产品'
    AND jg > 2000
    AND (pp = '华为'
        OR pp = '小米')
    AND ssrq BETWEEN '2020-01-01' AND '2022-12-31';


SELECT zwl1, zwl2
FROM zwb1
    LEFT JOIN zwb2 ON zwl1 = zwl2;


SELECT l1, l2
FROM b3
GROUP BY l1
HAVING sum(l3) > 100;


SELECT zwl
FROM zwb1
INTERSECT
SELECT zwl
FROM zwb2;


SELECT zwl, AVG(zwl2) OVER (PARTITION BY zwl1 )
FROM zwb;

 可以处理case when、like、left join on、union all、intersect、avg、group by、having等情况。

对于不能处理的情况，会抛运行时异常。

不过有的测试例子无法覆盖复杂的情况，如需使用还需要继续完善。