在使用criteria api进行查询时 criteriaBuilder只提供了一个部分标准的sql函数，但当我们需要使用oracle特有的行转列函数wm_concat或
mysql特有的行转列函数group_concat时，就只能自己封装了，
criteriaBuilder提供了一个封装函数的方法：
/** * Create an expression for the execution of a database * function. * @param name function name * @param type expected result type * @param args function arguments * @return expression */ <T> Expression<T> function(String name, Class<T> type, Expression<?>... args);
如我们封装wm_concat函数，代码如下：
Expression<String> wmConcat = cb.function("wm_concat",
String.class, root.get("ID"));
生成的sql 如：select wm_concat(id)........
如果我们想生成 select wm_concat(distinct(id))这个的形式，来去id进行去重distinct。可以用下面的代码，对function进行嵌套：
Expression<String> distinct = cb.function("distinct",
String.class, root.get("ID"));
Expression<String> wmConcat = cb.function("wm_concat",
String.class, distinct);
当然这还没有完，生成这样的函数后，其实jpa是不认可wm_concat函数的,(distinct函数，lower函数等是认可的)，这是为什么呢，因为jpa认可的oracle函数都会在OracleDialect定义。
如我使用的OracleDialect版本是Oracle12cDialect，它本身没有注册自定义函数，但是它继承的类Oracle8iDialect注册了很多函数，如下：
protected void registerFunctions() { registerFunction( "abs", new StandardSQLFunction("abs") ); registerFunction( "sign", new StandardSQLFunction("sign", StandardBasicTypes.INTEGER) ); registerFunction( "acos", new StandardSQLFunction("acos", StandardBasicTypes.DOUBLE) ); registerFunction( "asin", new StandardSQLFunction("asin", StandardBasicTypes.DOUBLE) ); registerFunction( "atan", new StandardSQLFunction("atan", StandardBasicTypes.DOUBLE) ); registerFunction( "bitand", new StandardSQLFunction("bitand") ); registerFunction( "cos", new StandardSQLFunction("cos", StandardBasicTypes.DOUBLE) ); registerFunction( "cosh", new StandardSQLFunction("cosh", StandardBasicTypes.DOUBLE) ); registerFunction( "exp", new StandardSQLFunction("exp", StandardBasicTypes.DOUBLE) ); registerFunction( "ln", new StandardSQLFunction("ln", StandardBasicTypes.DOUBLE) ); registerFunction( "sin", new StandardSQLFunction("sin", StandardBasicTypes.DOUBLE) ); registerFunction( "sinh", new StandardSQLFunction("sinh", StandardBasicTypes.DOUBLE) ); registerFunction( "stddev", new StandardSQLFunction("stddev", StandardBasicTypes.DOUBLE) ); registerFunction( "sqrt", new StandardSQLFunction("sqrt", StandardBasicTypes.DOUBLE) ); registerFunction( "tan", new StandardSQLFunction("tan", StandardBasicTypes.DOUBLE) ); registerFunction( "tanh", new StandardSQLFunction("tanh", StandardBasicTypes.DOUBLE) ); registerFunction( "variance", new StandardSQLFunction("variance", StandardBasicTypes.DOUBLE) ); registerFunction( "round", new StandardSQLFunction("round") ); registerFunction( "trunc", new StandardSQLFunction("trunc") ); registerFunction( "ceil", new StandardSQLFunction("ceil") ); registerFunction( "floor", new StandardSQLFunction("floor") ); registerFunction( "chr", new StandardSQLFunction("chr", StandardBasicTypes.CHARACTER) ); registerFunction( "initcap", new StandardSQLFunction("initcap") ); registerFunction( "lower", new StandardSQLFunction("lower") ); registerFunction( "ltrim", new StandardSQLFunction("ltrim") ); registerFunction( "rtrim", new StandardSQLFunction("rtrim") ); registerFunction( "soundex", new StandardSQLFunction("soundex") ); registerFunction( "upper", new StandardSQLFunction("upper") ); registerFunction( "ascii", new StandardSQLFunction("ascii", StandardBasicTypes.INTEGER) ); registerFunction( "to_char", new StandardSQLFunction("to_char", StandardBasicTypes.STRING) ); registerFunction( "to_date", new StandardSQLFunction("to_date", StandardBasicTypes.TIMESTAMP) ); registerFunction( "current_date", new NoArgSQLFunction("current_date", StandardBasicTypes.DATE, false) ); registerFunction( "current_time", new NoArgSQLFunction("current_timestamp", StandardBasicTypes.TIME, false) ); registerFunction( "current_timestamp", new NoArgSQLFunction("current_timestamp", StandardBasicTypes.TIMESTAMP, false) ); registerFunction( "last_day", new StandardSQLFunction("last_day", StandardBasicTypes.DATE) ); registerFunction( "sysdate", new NoArgSQLFunction("sysdate", StandardBasicTypes.DATE, false) ); registerFunction( "systimestamp", new NoArgSQLFunction("systimestamp", StandardBasicTypes.TIMESTAMP, false) ); registerFunction( "uid", new NoArgSQLFunction("uid", StandardBasicTypes.INTEGER, false) ); registerFunction( "user", new NoArgSQLFunction("user", StandardBasicTypes.STRING, false) ); registerFunction( "rowid", new NoArgSQLFunction("rowid", StandardBasicTypes.LONG, false) ); registerFunction( "rownum", new NoArgSQLFunction("rownum", StandardBasicTypes.LONG, false) ); // Multi-param string dialect functions... registerFunction( "concat", new VarArgsSQLFunction(StandardBasicTypes.STRING, "", "||", "") ); registerFunction( "instr", new StandardSQLFunction("instr", StandardBasicTypes.INTEGER) ); registerFunction( "instrb", new StandardSQLFunction("instrb", StandardBasicTypes.INTEGER) ); registerFunction( "lpad", new StandardSQLFunction("lpad", StandardBasicTypes.STRING) ); registerFunction( "replace", new StandardSQLFunction("replace", StandardBasicTypes.STRING) ); registerFunction( "rpad", new StandardSQLFunction("rpad", StandardBasicTypes.STRING) ); registerFunction( "substr", new StandardSQLFunction("substr", StandardBasicTypes.STRING) ); registerFunction( "substrb", new StandardSQLFunction("substrb", StandardBasicTypes.STRING) ); registerFunction( "translate", new StandardSQLFunction("translate", StandardBasicTypes.STRING) ); registerFunction( "substring", new StandardSQLFunction( "substr", StandardBasicTypes.STRING ) ); registerFunction( "locate", new SQLFunctionTemplate( StandardBasicTypes.INTEGER, "instr(?2,?1)" ) ); registerFunction( "bit_length", new SQLFunctionTemplate( StandardBasicTypes.INTEGER, "vsize(?1)*8" ) ); registerFunction( "coalesce", new NvlFunction() ); // Multi-param numeric dialect functions... registerFunction( "atan2", new StandardSQLFunction("atan2", StandardBasicTypes.FLOAT) ); registerFunction( "log", new StandardSQLFunction("log", StandardBasicTypes.INTEGER) ); registerFunction( "mod", new StandardSQLFunction("mod", StandardBasicTypes.INTEGER) ); registerFunction( "nvl", new StandardSQLFunction("nvl") ); registerFunction( "nvl2", new StandardSQLFunction("nvl2") ); registerFunction( "power", new StandardSQLFunction("power", StandardBasicTypes.FLOAT) ); // Multi-param date dialect functions... registerFunction( "add_months", new StandardSQLFunction("add_months", StandardBasicTypes.DATE) ); registerFunction( "months_between", new StandardSQLFunction("months_between", StandardBasicTypes.FLOAT) ); registerFunction( "next_day", new StandardSQLFunction("next_day", StandardBasicTypes.DATE) ); registerFunction( "str", new StandardSQLFunction("to_char", StandardBasicTypes.STRING) ); }
通过调试代码，可以看到oracledialect共注册了80个函数。
同理，我们也可继承Oracle12cDialect，添加自己的函数：
public class MyOracleDialect extends Oracle12cDialect { /** * 添加oracler内置函数和自定义函数 */ @Override protected void registerFunctions() { super.registerFunctions(); registerFunction("wm_concat", new StandardSQLFunction("wm_concat", StandardBasicTypes.STRING)); } /** * 解决启动时数字溢出的错误 * @return */ @Override public String getQuerySequencesString() { return "select * from user_sequences"; } }
配置完成后，启动项目即可。