我有一个PL / SQL函数,该函数返回ROWTYPE。我需要从Java调用此函数并检索数据(无论是数组,映射,结构,类,无论如何,我真的不在乎,我只需要它,最好不必拧PL / SQL代码,例如更改函数的返回类型)。我研究了多个“解决方案”和论坛,但没有找到答案。
我已经尝试将out参数注册为struct和class,但都没有用。
这是我的PL / SQL函数:
FUNCTION DAR_CLIENTE(cedula VARCHAR2) RETURN CLIENTE%ROWTYPE AS
RET CLIENTE%ROWTYPE;
BEGIN
-- TAREA: Se necesita implantación para FUNCTION P_CLIENTE.DAR_CLIENTE
SELECT * INTO RET FROM CLIENTE WHERE Persona_cedula=cedula;
RETURN RET;
END DAR_CLIENTE;
这是我的Java代码:
public static void main(String args[]) throws SQLException {
Properties info = new Properties();
info.put(OracleConnection.CONNECTION_PROPERTY_USER_NAME, DB_USER);
info.put(OracleConnection.CONNECTION_PROPERTY_PASSWORD, DB_PASSWORD);
info.put(OracleConnection.CONNECTION_PROPERTY_DEFAULT_ROW_PREFETCH, "20");
OracleDataSource ods = new OracleDataSource();
ods.setURL(DB_URL);
ods.setConnectionProperties(info);
// With AutoCloseable, the connection is closed automatically.
try (OracleConnection connection = (OracleConnection) ods.getConnection()) {
// Get the JDBC driver name and version
DatabaseMetaData dbmd = connection.getMetaData();
System.out.println("Driver Name: " + dbmd.getDriverName());
System.out.println("Driver Version: " + dbmd.getDriverVersion());
// Print some connection properties
System.out.println("Default Row Prefetch Value is: " + connection.getDefaultRowPrefetch());
System.out.println("Database Username is: " + connection.getUserName());
System.out.println();
System.out.println(connection.getSchema());
// Perform a database operation
Map<String, Class<?>> myMap = new HashMap<String, Class<?>>();
myMap.put("P09551_1_5.CLIENTE", Cliente.class);
connection.setTypeMap(myMap);
CallableStatement storedProc = connection
.prepareCall("{? = call P09551_1_5.p_cliente.dar_cliente('1144102435')}");
storedProc.registerOutParameter(1, oracle.jdbc.OracleTypes.JAVA_STRUCT);
storedProc.execute();
}
}
我正在使用ojdbc8.jar。
该信息在程序开始时打印到控制台:
Driver Name: Oracle JDBC driver
Driver Version: 18.3.0.0.0
最佳答案
我已通过将返回类型更改为SYS_REFCURSOR来解决了该问题,如下所示:
FUNCTION DAR_CLIENTE(cedula VARCHAR2) RETURN SYS_REFCURSOR AS
RET SYS_REFCURSOR;
BEGIN
-- TAREA: Se necesita implantación para FUNCTION P_CLIENTE.DAR_CLIENTE
OPEN RET FOR
SELECT * FROM CLIENTE WHERE Persona_cedula=cedula;
RETURN RET;
END DAR_CLIENTE;
Java代码如下:
public static void main(String args[]) throws SQLException {
Properties info = new Properties();
info.put(OracleConnection.CONNECTION_PROPERTY_USER_NAME, DB_USER);
info.put(OracleConnection.CONNECTION_PROPERTY_PASSWORD, DB_PASSWORD);
info.put(OracleConnection.CONNECTION_PROPERTY_DEFAULT_ROW_PREFETCH, "20");
OracleDataSource ods = new OracleDataSource();
ods.setURL(DB_URL);
ods.setConnectionProperties(info);
// With AutoCloseable, the connection is closed automatically.
try (OracleConnection connection = (OracleConnection) ods.getConnection()) {
// Get the JDBC driver name and version
DatabaseMetaData dbmd = connection.getMetaData();
System.out.println("Driver Name: " + dbmd.getDriverName());
System.out.println("Driver Version: " + dbmd.getDriverVersion());
// Print some connection properties
System.out.println("Default Row Prefetch Value is: " + connection.getDefaultRowPrefetch());
System.out.println("Database Username is: " + connection.getUserName());
System.out.println("Schema: "+connection.getSchema());
System.out.println();
// Perform a database operation
Map<String, Class<?>> myMap = new HashMap<String, Class<?>>();
myMap.put("P09551_1_5.CLIENTE", Cliente.class);
connection.setTypeMap(myMap);
CallableStatement storedProc = connection
.prepareCall("{? = call P09551_1_5.p_cliente.dar_cliente('1144102435')}");
storedProc.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
storedProc.execute();
ResultSet resultSet = (ResultSet) storedProc.getObject(1);
ResultSetMetaData meta = resultSet.getMetaData();
int columnCount = meta.getColumnCount();
while (resultSet.next()) {
for (int i = 1; i <= columnCount; i++) {
System.out.println(meta.getColumnLabel(i)+":"+resultSet.getObject(i).toString());
}
//System.out.println(resultSet.getString(1));
}
}
}
在控制台上获得此响应(我这样做是为了使所有字段都打印有其列标签,尽管此特定表只有一列):
Driver Name: Oracle JDBC driver
Driver Version: 18.3.0.0.0
Default Row Prefetch Value is: 20
Database Username is: P09551_1_5
Schema: P09551_1_5
PERSONA_CEDULA:1144102435
这是解决方案的来源:
https://www.mkyong.com/jdbc/jdbc-callablestatement-stored-procedure-cursor-example/