gu.sql2java.phoenix.PhoenixNormalizer Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of sql2java-manager Show documentation
sql2java manager class package for accessing database
There is a newer version: 5.2.0
package gu.sql2java.phoenix;

import net.sf.jsqlparser.expression.CastExpression;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.Function;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.statement.select.SelectExpressionItem;

import java.util.List;
import java.util.TimeZone;
import java.util.function.Consumer;

import gu.sql2java.parser.SqlSyntaxNormalizer;
/**
 * 基于SQL语法对象实现对SQL的修改

 * 对PHOENIX支持有问题的CAST日期函数转换为使用PHOENIX的Native函数TO_DATE,TO_TIME,TO_TIMESTAMP
 * @author guyadong
 * @since 3.17.0
 *
 */
public class PhoenixNormalizer  extends SqlSyntaxNormalizer{
    public PhoenixNormalizer() {
	}
	
	private Expression castToFunction(Expression castLeftExpression,String format,String targetFunctionName){
		Function toChar = new Function()
				.withName("TO_CHAR")
				.withParameters(new ExpressionList().addExpressions(castLeftExpression));
		Function targetFunction = new Function()
				.withName(targetFunctionName)
				.withParameters(new ExpressionList().addExpressions(toChar,new StringValue(format)));
		return targetFunction;
	}
	private void onCastExpression(CastExpression castExpression,Consumerconsumer){
	        Expression newExp = null;
	        switch (castExpression.getType().toString().toLowerCase()) {
	            case "date":{
	                newExp = castToFunction(castExpression.getLeftExpression(),"yyyy-MM-dd","TO_DATE");
	                changed.set(true);
	                break;
	            }
	            case "time":{
	                newExp = castToFunction(castExpression.getLeftExpression(),"yyyy-MM-dd HH:mm:ss","TO_TIME");
	                changed.set(true);
	                break;
	            }
	            case "timestamp":{
	                newExp = castToFunction(castExpression.getLeftExpression(),"yyyy-MM-dd'T'HH:mm:ss.SSSZ","TO_TIMESTAMP");
	                changed.set(true);
	                break;
	            }
	            default:
	                break;
	        }
	        if(null != newExp){
	            consumer.accept(newExp);
	        }
	}
    @Override
    public void visit(SelectExpressionItem item) {
        if(item.getExpression() instanceof CastExpression){
            onCastExpression((CastExpression)item.getExpression(),item::setExpression);
        }
        super.visit(item);
    }
    
    /**
     * 将MySQL的 DATE_FORMAT格式转为Java 日期格式
     
     * 参见 function_date-format,
     * 因为不能实现完全转换，所以当有不支持的格式时返回原字符串 
     * @param format
     * @see java.text.SimpleDateFormat
     */
    String castFormat(String format){
        if(null != format){
            String fmt=format.replace("%Y", "yyyy").replace("%y", "yy").replace("%m", "MM").replaceAll("%[de]", "dd")
            .replaceAll("%[Hk]", "HH").replace("%i", "mm").replaceAll("%[sS]", "ss")
            .replace("%W", "E")
            .replace("%j", "D")
            .replace("%T", "hh:mm:ss")
            .replace("%r", "hh:mm:ss a")
            .replaceAll("%[Uu]", "w")
            .replace("%%", "%");
            // 当有不支持的格式时返回原字符串 
            if(fmt.indexOf('%') < 0){
                return fmt;
            }
        }
        return format;
    }
    
    @Override
    public void visit(Function function){
        if("DATE_FORMAT".equalsIgnoreCase(function.getName())){
            /** DATE_FORMAT 改为 TO_CHAR */
            List expressions = function.getParameters().getExpressions();
            if(null != expressions && 2 == expressions.size() ){
            	Function convertTz = createConvertTzFunction(expressions.get(0));
            	expressions.set(0, convertTz);
                function.setName("TO_CHAR");
                Expression formatExp = expressions.get(1);
                if(formatExp instanceof StringValue){
                    StringValue stringValue = (StringValue)formatExp;
                    stringValue.setValue(castFormat(stringValue.getValue()));
                }
            }
            
        }
        super.visit(function);
    }
    /**
     * 创建 {@code CONVERT_TZ('$exp','UTC','$zone')}函数,将时间转为当前时区
     * @param exp
     */
    private Function createConvertTzFunction(Expression exp) {
    	/** 当前时区 */
    	String zone = TimeZone.getDefault().getID();
    	Function convertTz = new Function();
    	convertTz.setName("CONVERT_TZ");
    	ExpressionList exps = new ExpressionList();
    	exps.addExpressions(exp,new StringValue("UTC"),new StringValue(zone));
    	convertTz.setParameters(exps);
    	return convertTz;
    }
}