如何使用H2数据库创建存储过程？ [英] How to create stored procedure using H2 database?

问题描述

有没有人尝试使用H2数据库创建存储过程？

解决方案

要在Java函数中访问数据库做需要连接。对于H2，有两种方式来获得这样的连接：

解决方案1 ​​：如果Java函数的第一个参数是 java.sql.Connection ，那么数据库提供连接。对于SQL，这是一个隐藏参数，这意味着您不能并且不需要明确地设置它。此文档记录在案：用户定义的函数和存储过程，需要连接。示例：

  CREATE ALIAS QUERY AS $$ 
 ResultSet查询（Connection conn，String sql）throws SQLException {
 return conn.createStatement（）。executeQuery（sql）; 
} $$; 
 CALL QUERY（'SELECT * FROM DUAL'）;               使用 DriverManager.getConnection（jdbc：default：connection）在Java函数中创建一个新连接。此功能在H2版本1.3.151及更高版本中可用，默认情况下禁用。要启用它，请附加; DEFAULT_CONNECTION = TRUE  到数据库网址。这是一个有问题的功能，因为如果在H2驱动程序之前加载，Oracle JDBC驱动程序将尝试解析此数据库URL。所以基本上你不能使用的功能，如果Oracle驱动程序加载（我认为这是一个错误在Oracle驱动程序）。
 
Has anyone tried to create stored procedures using the H2 database?
解决方案
To access the database within a Java function, you do need a connection. For H2, there are two ways to get such a connection:

Solution 1: If the first parameter of the Java function is a java.sql.Connection, then the database provides the connection. For SQL, this is a 'hidden' parameter, meaning you can't and don't need to set it explicitly. This is documented: User-Defined Functions and Stored Procedures, "Functions That Require a Connection". Example:
CREATE ALIAS QUERY AS $$
ResultSet query(Connection conn, String sql) throws SQLException {
    return conn.createStatement().executeQuery(sql);
} $$;
CALL QUERY('SELECT * FROM DUAL');
Solution 2: For compatibility with Apache Derby and Oracle, You can open a new connection within the Java function using DriverManager.getConnection("jdbc:default:connection"). This feature is available in H2 version 1.3.151 and newer, and it it disabled by default. To enable it, append ;DEFAULT_CONNECTION=TRUE to the database URL. It's a problematic feature because the Oracle JDBC driver will try to resolve this database URL if it is loaded before the H2 driver. So basically you can't use the feature if the Oracle driver is loaded (I consider this a bug in the Oracle driver).

                        
这篇关于如何使用H2数据库创建存储过程？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！