使用JDBC调用存储过程

该用例分为4部分，分别实现了如下功能：  

callProcedureWhitNoParamByCallableStatement 调用没有参数的存储过程；  

callProcedureWhitINParamByCallableStatement 调用只有IN参数的存储过程；  

callProcedureWhitOUTParamByCallableStatement 调用只有OUT参数的存储过程；

callProcedureWhitInOutParamByCallableStatement 调用有IN、OUT参数的存储过程。

示例如下：

package com.gbase.jdbc.simple;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.sql.Types;

public class CallProcByJdbc {

private static final String URL =

"jdbc:gbase://192.168.XXX.XX:5258/test?user=sysdba&password=";

/**

* @param args

*/

public static void main(String[] args) {

//创建存储过程

prepareProc();

CallProcByJdbc callProcByJdbc = new CallProcByJdbc();

//调用没有参数的存储过程

callProcByJdbc.callProcedureWhitNoParamByCallableStatement();

//调用只有 IN 参数的存储过程

callProcByJdbc.callProcedureWhitINParamByCallableStatement();

//调用只有 OUT 参数的存储过程

callProcByJdbc.callProcedureWhitOUTParamByCallableStatement();

//调用有 IN/OUT 参数的存储过程

callProcByJdbc.callProcedureWhitInOutParamByCallableStatement();

}

/**

* 通过 CallableStatement 调用没有参数的

* 存储过程。

*/

public void callProcedureWhitNoParamByCallableStatement() {

Connection conn = null;

CallableStatement cstm = null;

ResultSet rs = null;

try {

Class.forName("com.gbase.jdbc.Driver");

conn = DriverManager.getConnection(URL);

cstm = conn.prepareCall("call procNoParam()");

rs = cstm.executeQuery();

rs.next();

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

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();   } finally {

try {

rs.close();

}catch (NullPointerException e) {

} catch (Exception e) {

cstm = null;

}

try {

cstm.close();

} catch (NullPointerException e) {

} catch (Exception e) {

cstm = null;

}

try {

conn.close();

} catch (NullPointerException e) {

} catch (Exception e) {

conn = null;

}}}

/**

* 通过 CallableStatement 调用 IN 参数的

* 存储过程。

*/

public void callProcedureWhitINParamByCallableStatement() {

Connection conn = null;

CallableStatement cstm = null;

ResultSet rs = null;

try {

Class.forName("com.gbase.jdbc.Driver");

conn = DriverManager.getConnection(URL);

cstm = conn.prepareCall("{call procInParam(?)}");

cstm.setString(1, "InParam Call Works!");

rs = cstm.executeQuery();

rs.next();

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

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

} finally {

try {

rs.close();

}catch (NullPointerException e) {

} catch (Exception e) {

cstm = null;

}

try {

cstm.close();

} catch (NullPointerException e) {

} catch (Exception e) {

cstm = null;

}

try {

conn.close();

} catch (NullPointerException e) {

} catch (Exception e) {

conn = null;

}}}

/**

* 通过 CallableStatement 调用 OUT 参数的

* 存储过程。

*/

public void callProcedureWhitOUTParamByCallableStatement() {

Connection conn = null;

CallableStatement cstm = null;

try {

Class.forName("com.gbase.jdbc.Driver");

conn = DriverManager.getConnection(URL);  

cstm = conn.prepareCall("call procOutParam(?)");

cstm.setString(1, "@outParam");

cstm.registerOutParameter(1, Types.VARCHAR);

cstm.execute();

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

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

} finally {

try {

cstm.close();

} catch (NullPointerException e) {

} catch (Exception e) {

cstm = null;

}

try {

conn.close();

} catch (NullPointerException e) {

} catch (Exception e) {

conn = null;

}}}

/**

* 通过 CallableStatement 调用 IN/OUT 参数的

* 存储过程。

*/

public void callProcedureWhitInOutParamByCallableStatement() {

Connection conn = null;

CallableStatement cstm = null;

try {

Class.forName("com.gbase.jdbc.Driver");

conn = DriverManager.getConnection(URL);

cstm = conn.prepareCall("{call procInOutParam(?,?)}");

cstm.setString(1, "aaaaa");  

cstm.setString(2, "@outParam");

cstm.registerOutParameter(2, Types.VARCHAR);

cstm.execute();

System.out.println(cstm.getString(2));

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

} finally {

try {

cstm.close();

} catch (NullPointerException e) {

} catch (Exception e) {

cstm = null;

}

try {

conn.close();

} catch (NullPointerException e) {

} catch (Exception e) {

conn = null;

}}}

/**

* 创建 4 个存储过程；

* 1、没有参数

* 2、只有 IN 参数

* 3、只有 OUT 参数

* 4、有 IN、OUT 参数

*/

private static void prepareProc() {

Connection conn = null;

Statement stm = null;

try {

Class.forName("com.gbase.jdbc.Driver");

conn = DriverManager.getConnection(URL);  

stm = conn.createStatement();

stm.executeUpdate("DROP PROCEDURE IF EXISTS `test`.`procNoParam`");

stm.executeUpdate("DROP PROCEDURE IF EXISTS `test`.`procInParam`");

stm.executeUpdate("DROP PROCEDURE IF EXISTS `test`.`procOutParam`");

stm.executeUpdate("DROP PROCEDURE IF EXISTS `test`.`procInOutParam`");

stm.executeUpdate("CREATE PROCEDURE `test`.`procNoParam` () begin select 'procNoParamTest works'; end");

stm.executeUpdate("CREATE PROCEDURE `test`.`procInParam` (IN inParam Varchar(100)) begin select inParam;

end");

stm.executeUpdate("CREATE PROCEDURE `test`.`procOutParam` (OUT outParam Varchar(100)) begin SET outParam = 'outParamTest works'; end");

stm.executeUpdate("CREATE PROCEDURE `test`.`procInOutParam` (IN inParam Varchar(100), OUT outParam Varchar(200)) begin set outParam = CONCAT(\'InOutParam \',inParam,\ '

works!\ '); end");

} catch (ClassNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} finally {

try {

stm.close();

} catch (NullPointerException e) {

} catch (Exception e) {

stm = null;

}

try {

conn.close();

} catch (NullPointerException e) {

} catch (Exception e) {

conn = null;

}}}}