一、了解CallableStatement接口
1.callablestatement接口提供了两种调用形式
{?= call <procedure-name>[(<arg1>,<arg2>, ...)]} //包含结果参数的调用形式 如:函数(funciton)
{call <procedure-name>[(<arg1>,<arg2>, ...)]} //不包含结果参数的调用形式 如:存储过程(procedure)
2.callablestatement接口提供的方法
- void registerOutParameter(int parameterIndex, int sqlType)
- throws SQLException;
void registerOutParameter(int parameterIndex, int sqlType)
throws SQLException; //在调用存储过程的时候设置输出参数的类型,用于接收输出结果
registerOutParameter接口中有四个该方法的重载实现,具体的可以查看源码了解
- setXXX(int parameterIndex,XXX x)
setXXX(int parameterIndex,XXX x) //主要用于设置过程调用时候需要的输入参数信息 其中XXX代表对应类型
getXXX(int x) //主要用于获取过程调用后返回的参数的信息
3.callablestatement接口产生的异常提示
如下源码:
-
-
-
-
-
-
-
- void registerOutParameter(int parameterIndex, int sqlType)
- ows SQLException;
/*
* @exception SQLFeatureNotSupportedException if <code>sqlType</code> is
* a <code>ARRAY</code>, <code>BLOB</code>, <code>CLOB</code>,
* <code>DATALINK</code>, <code>JAVA_OBJECT</code>, <code>NCHAR</code>,
* <code>NCLOB</code>, <code>NVARCHAR</code>, <code>LONGNVARCHAR</code>,
* <code>REF</code>, <code>ROWID</code>, <code>SQLXML</code>
* or <code>STRUCT</code> data type and the JDBC driver does not support
* this data type
* @see Types
*/
void registerOutParameter(int parameterIndex, int sqlType)
throws SQLException;
当我们使用registerOutParameter方法设置输出参数类型的时候,需要注意对于某一些类型是不能够
进行设置的如上中所以提到的类型都会引发SQLFeatureNotSupportedException异常,对于能够支持
的类型可以查看java.sql.Types和oracle.jdbc.OracleTypes
如下源码:
/*
* java.sql.SQLException: 不允许的操作: Ordinal binding and Named binding cannot be
* combined! at
* oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) at
* oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146) at
* oracle
* .jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java
* :4219) at
* org.viancent.call.procedure.CallProcedure.main(CallProcedure.java:36)
*/
当我们在给过程设置参数信息的时候,不能够同时使用下标和名称来指定对应参数的。
二、具体的Java代码实现
- package org.viancent.call.procedure;
-
- import java.sql.CallableStatement;
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Types;
- import com.sun.corba.se.spi.orbutil.fsm.Guard.Result;
- import oracle.jdbc.OracleTypes;
-
-
-
-
-
- @SuppressWarnings("unused")
- public class CallProcedure {
- private static Connection conn = null;
- private static CallableStatement cs = null;
- private static ResultSet rs = null;
-
- static {
- try {
-
- Class.forName("oracle.jdbc.driver.OracleDriver");
-
- conn = DriverManager.getConnection(
- "jdbc:oracle:thin:@0.0.0.0:1521:orcl", "scott", "tiger");
- } catch (Exception e) {
-
- e.printStackTrace();
- }
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- public void callProcedureY(Connection conn) throws Exception
- {
-
- cs = conn.prepareCall("{call updateName(?,?)}");
- cs.setInt(1, 7499);
- cs.setString(2, "www");
-
- cs.execute();
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- public void callProcedure(Connection conn) throws Exception
- {
-
- cs = conn.prepareCall("{call insertLine}");
-
- cs.execute();
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- public void callProcedureYY(Connection conn) throws Exception
- {
-
- cs = conn.prepareCall("{call deleteLine(?,?)}");
-
- cs.setInt(1, 7839);
-
- cs.registerOutParameter(2, OracleTypes.NUMBER);
-
- cs.execute();
-
- System.out.println(cs.getString(2));
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- public void callProcedureYYL(Connection conn) throws Exception
- {
-
- cs = conn.prepareCall("{call someUtils.selectRows(?)}");
-
- cs.registerOutParameter(1, OracleTypes.CURSOR);
-
- cs.execute();
-
- rs = (ResultSet) cs.getObject(1);
-
- while(rs.next())
- {
- System.out.println(rs.getInt(1)+"t"+rs.getString(2));
- }
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- public void callProcedureFY(Connection conn) throws Exception
- {
-
- cs = conn.prepareCall("{? = call useOther(?)}");
-
- cs.registerOutParameter(1, OracleTypes.CHAR);
-
- cs.setInt(2, 1111);
-
- cs.execute();
-
- System.out.println(cs.getString(1));
- }
-
-
-
-
-
-
-
- public void closeConn(Connection conn,ResultSet rs)
- {
- if (rs != null) {
- try {
- rs.close();
- } catch (SQLException e) {
-
- e.printStackTrace();
- }
- }
- if (conn != null) {
- try {
- conn.close();
- } catch (SQLException e) {
-
- e.printStackTrace();
- }
- }
- }
-
-
- public static void main(String[] args) {
- CallProcedure cp = new CallProcedure();
- try {
-
- conn.setAutoCommit(true);
- cp.callProcedure(conn);
- cp.callProcedureY(conn);
- cp.callProcedureFY(conn);
- cp.callProcedureYY(conn);
- cp.callProcedureYYL(conn);
- conn.commit();
- } catch (Exception e) {
- e.printStackTrace();
- try {
- conn.rollback();
- } catch (Exception e1) {
-
- e1.printStackTrace();
- }
-
- }finally
- {
- cp.closeConn(conn, rs);
- }
- }
- }
版权声明:本文来源CSDN,感谢博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
原文链接:https://blog.csdn.net/fjza1168/article/details/25148525
站方申明:本站部分内容来自社区用户分享,若涉及侵权,请联系站方删除。