通过 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.111.95:5258/test?user=sysdba&password="; /** * @param args */ public static void main(String[] args) { //创建存储过程 prepareProc(); CallProcByJdbc callProcByJdbc = new CallProcByJdbc(); //调用没有参数的存储过程 callProcByJdbc.callProcedureWhitNoParamByCallableStateme nt(); //调用只有 IN 参数的存储过程 callProcByJdbc.callProcedureWhitINParamByCallableStateme nt(); //调用只有 OUT 参数的存储过程 callProcByJdbc.callProcedureWhitOUTParamByCallableStatem ent(); //调用有 IN/OUT 参数的存储过程 callProcByJdbc.callProcedureWhitInOutParamByCallableStat ement(); } /** * 通过 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 outParamVarchar(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; } } } }
