java调用gbase存储过程,在java中调用GBase 8s的函数示例

测试在java程序中调用GBase 8s的函数调用。

1, OUT及RETURN返回值

1)、首先创建需要调用的函数或者存储过程-- out value 是 out变量值

-- return value 是函数返回值

create function myfunc2(f1 int,out f2 varchar(128)) returns varchar(128);

let f2 = "out value: myfunc2";

return "return value: myfunc2 and f1: " || f1;

end function;

2)、java程序调用函数myfunc2示例package cn.gbase.spl;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import cn.gbase.util.DB;

public class CallSpl {

public static Connection connection = DB.getConn(); //调用数据库连接

public static void main(String[] args) {

callSplout();

}

public static void callSplout() {

CallableStatement cs = null;

ResultSet rSet = null;

String splSQL = "{call myfunc2(?,?)}"; //第一个参数为in输入，第二个参数为out参数

try {

cs=connection.prepareCall(splSQL);

cs.setInt(1, 123);

cs.registerOutParameter(2, java.sql.Types.VARCHAR);

rSet = cs.executeQuery();

System.out.println(cs.getString(2)); // 输出OUT的值

while(rSet.next()) { // 输出RETURN的值

System.out.println(rSet.getString(1));

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

try {

rSet.close();

cs.close();

} catch (SQLException e) {

}

}

}

}

返回结果：out value: myfunc2

return value: myfunc2 and f1: 123

OUT游标

1)、创建需要调用的函数或者存储过程，参数是out类型的sys_refcursordrop procedure if exists proc_outcursor;

CREATE PROCEDURE proc_outcursor(out p SYS_REFCURSOR)

OPEN p FOR 'select skip 10 first 10 tabid,tabname,ustlowts from systables';

end procedure;

2)、java程序调用存储过程package com.gbasedbt.jdbc.Test;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import com.gbasedbt.DBConn.DBC;

import com.gbasedbt.lang.IfxTypes;

public class OutCursor {

public static Connection connection = DBC.getConn(); //调用数据库连接

public static void main(String[] args) {

callSplout();

}

public static void callSplout() {

CallableStatement cs = null;

ResultSet cursorSet = null;

String splSQL = "{call proc_outcursor(?)}"; // 参数为out参数

try {

cs=connection.prepareCall(splSQL);

cs.registerOutParameter(1, IfxTypes.IFX_TYPE_CURSOR);

cs.executeQuery();

cursorSet = (ResultSet) cs.getObject(1); // 获取out

while(cursorSet.next()) { // 输出out 的值

System.out.println(cursorSet.getString("tabid") + "\t" + cursorSet.getString("tabname"));

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

try {

cursorSet.close();

cs.close();

} catch (SQLException e) {

}

}

}

}

返回结果：11 sysconstraints

12 sysreferences

13 syschecks

14 sysdefaults

15 syscoldepend

16 sysprocedures

17 sysprocbody

18 sysprocplan

19 sysprocauth

20 sysblobs

2， RETURN游标参数

1)、创建需要调用的函数或者存储过程drop procedure if exists proc_cursor;

CREATE PROCEDURE proc_cursor() returns SYS_REFCURSOR;

define my_cursor SYS_REFCURSOR;

OPEN my_cursor FOR 'select skip 10 first 10 tabid,tabname,ustlowts from systables';

return my_cursor;

end procedure;

2)、java程序调用存储过程proc_cursor示例package testGBasedbt;

import java.sql.Connection;

import java.sql.DriverManager;

import com.gbasedbt.jdbc.IfxCallableStatement;

import com.gbasedbt.jdbc.IfxResultSet;

public class TestSPL {

public static void main(String[] args) {

String url = "jdbc:gbasedbt-sqli://192.168.1.71:9088/testdb:GBASEDBTSERVER=gbase01;DB_LOCALE=zh_CN.utf8;CLIENT_LOCALE=zh_CN.utf8;NEWCODESET=UTF-8,utf8,57372";

String sql = "{call proc_cursor()}";

try {

Class.forName("com.gbasedbt.jdbc.IfxDriver");

Connection connection = DriverManager.getConnection(url,"gbasedbt","GBase123");

IfxCallableStatement cs = (IfxCallableStatement) connection.prepareCall(sql);

IfxResultSet resultSet = (IfxResultSet) cs.executeQuery();

while (resultSet.next()) { // 输出RETURN的值

System.out.println(resultSet.getInt(1) + "\t" + resultSet.getString(2) + "\t" + resultSet.getTimestamp(3));

}

} catch (Exception e) {

e.printStackTrace();

}

}

}

返回结果11 sysconstraints 2019-06-08 16:28:02.0

12 sysreferences 2019-06-08 16:28:02.0

13 syschecks 2019-06-08 16:28:02.0

14 sysdefaults 2019-06-08 16:28:02.0

15 syscoldepend 2019-06-08 16:28:02.0

16 sysprocedures 2019-06-08 16:28:02.0

17 sysprocbody 2019-06-08 16:28:02.0

18 sysprocplan 2019-06-08 16:28:03.0

19 sysprocauth 2019-06-08 16:28:03.0

20 sysblobs 2019-06-08 16:28:03.0