通过GBase8a的JDBC调用GBase8a MPP的存储过程

本文介绍了如何通过GBase8a的JDBC调用GBase8a MPP的存储过程，GBase8a的JDBC驱动，可以通过如下连接下载：

链接: https://pan.baidu.com/s/1FdliWx7P9QICLDdp-8UEtA?pwd=qp7a 提取码: qp7a

本文以如下四个实例，来说明调用的方式：

• callProcedureWhitNoParamByCallableStatement 调用没有参数的存储过程；
• callProcedureWhitINParamByCallableStatement 调用只有 IN 参数的存储过程；
• callProcedureWhitOUTParamByCallableStatement 调用只有 OUT 参数的存储过程；
• callProcedureWhitInOutParamByCallableStatement 调用有 IN、OUT参数的存储过程；

调用的示例代码如下：

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/testuser=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) {
			e.printStackTrace();
		} catch (SQLException e) {
			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;
			}
		}
	}
}