Jpa 重写方言dialect 使用oracle / mysql 数据库自定义函数
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Jpa 重写方言dialect 使用oracle / mysql 数据库自定义函数，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含7425字，纯文字阅读大概需要11分钟。
内容图文
* 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"; } }
配置完成后，启动项目即可。
Jpa 重写方言dialect 使用oracle / mysql 数据库自定义函数
标签：har oca 继承 enc ceil protected exec rgs build
本文系统来源：https://www.cnblogs.com/hankuikui/p/11734194.html
内容总结
以上是互联网集市为您收集整理的Jpa 重写方言dialect 使用oracle / mysql 数据库自定义函数全部内容，希望文章能够帮你解决Jpa 重写方言dialect 使用oracle / mysql 数据库自定义函数所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】